Category Archives: Tutorials

How to bind datatable with all Sql Instance names within Network and Local in .Net

By the help of following code you can get all Sql instance with in Local or Network in your Datatable Object so that you can easily display these instances to user.

First you need to add following references-
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo

[vb.net]
import Microsoft.SqlServer.Management.Smo with the help of ‘Imports’ keyword in code file

Imports Microsoft.SqlServer.Management.Smo

and use code:

    Private Sub Getdatatables()
        'Get all Network Instance
        Dim dataTable1 As System.Data.DataTable = SmoApplication.EnumAvailableSqlServers(False)
 
        'Get all Local Instance
        Dim dataTable1 As System.Data.DataTable = SmoApplication.EnumAvailableSqlServers(True)
    End Sub

[C#]

import Microsoft.SqlServer.Management.Smo with the help of ‘Using’ keyword in code file

Using Microsoft.SqlServer.Management.Smo;

and use this code:

private void Getdatatables()
{
	//Get all Network Instance
	System.Data.DataTable dataTable1 = SmoApplication.EnumAvailableSqlServers(false);
 
	//Get all Local Instance
	System.Data.DataTable dataTable1 = SmoApplication.EnumAvailableSqlServers(true);
}

Code sample for getting all Sql Database names in the sql instance in .Net

By the help of following code you can get all Sql Database of any sql instance Data table Object so that you can easily display these Database names to user. In the following code we are binding all database in drop down control.

First you need to add following references-
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo

[VB.Net]

    Private Sub BindDatabaseNames(ByVal SqlAuth As Boolean, ByVal cmbDataBase1 As ComboBox)
 
        Dim serverName As String = "Put your server name"
 
        Dim serverConnection As Microsoft.SqlServer.Management.Common.ServerConnection = New Microsoft.SqlServer.Management.Common.ServerConnection()
        serverConnection.ServerInstance = serverName
        serverConnection.LoginSecure = True
        If SqlAuth Then
            serverConnection.LoginSecure = False
            serverConnection.Login = "Put Login name"
            serverConnection.Password = "Put password"
        End If
        Dim server As Server = New Server(serverConnection)
        Try
            For Each db As Database In server.Databases
                cmbDataBase1.Items.Add(db.Name)
            Next
        Catch ex As Exception
            Dim exception As String = ex.Message
        End Try
 
    End Sub

[C#]

private void BindDatabaseNames(bool SqlAuth, ComboBox cmbDataBase1)
{
	string serverName = "Put your server name";
 
	Microsoft.SqlServer.Management.Common.ServerConnection serverConnection = new Microsoft.SqlServer.Management.Common.ServerConnection();
	serverConnection.ServerInstance = serverName;
	serverConnection.LoginSecure = true;
	if (SqlAuth) {
		serverConnection.LoginSecure = false;
		serverConnection.Login = "Put Login name";
		serverConnection.Password = "Put password";
	}
	Server server = new Server(serverConnection);
	try {
		foreach (Database db in server.Databases) {
			cmbDataBase1.Items.Add(db.Name);
		}
	} catch (Exception ex) {
		string exception = ex.Message;
	}
 
}

Find suspended state and unlock table in Sql Server

Some time when SQL query process goes in suspended state, it automatically locks the table and we cannot do any other operation on this table. If this process take much time and the result is not valuable then we need to wait until it finished.

So in following example we can learn how to find suspended process and how to kill that and unblock table.

Continue reading Find suspended state and unlock table in Sql Server

Code for saving image in Sql Table in Vb.net

The following code describe how to save an image file into SQL Server database. In the first code So, first take the image from the file in FileStream then convert into byte array. After that this byte array passes to the sql query as a parameter and then executes the SQL Command that insert the byte array in the image type SQL column.

Function for converting image into Byte array
Continue reading Code for saving image in Sql Table in Vb.net

Code Snippet for detaching SQL database programmatically in vb.net

Following code is for detaching sql database programmatically

VB.Net Code :

Dim conn As SqlClient.SqlConnection

        Dim cmd As New SqlClient.SqlCommand()

        Dim ConnectionString As String = “Data Source=” & Server name & “;Initial Catalog=master;Integrated Security=True”  ‘ Connection string

        conn = New SqlClient.SqlConnection

        conn.ConnectionString = ConnectionString

        conn.Open()   ‘Open connection

        Dim sqlDetech As String = “sp_detach_db ‘”& database name &”‘, ‘true'”  ‘ Sql query for detaching database

        cmd.CommandText = sqlDetech

        cmd.Connection = conn

        cmd.ExecuteNonQuery()  ‘execute query