VB.Net/Database ADO.net/SqlConnection

Connection state change event

 
Imports System
Imports System.Collections
Imports System.Data
Imports System.IO
Imports System.Xml.Serialization
Imports System.Xml
Imports System.Windows.Forms
Imports System.Data.SqlClient

Public Class MainClass
    Shared Dim WithEvents con As SqlConnection
    Shared Sub Main()
        con = New SqlConnection("Server=(local)\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=SSPI") 
        Dim cmd As New SqlCommand()
        cmd.rumandText = "SELECT TOP 5 FirstName, LastName FROM Employee"
        cmd.Connection = con
        Try
            con.Open()
            Dim reader As SqlDataReader = cmd.ExecuteReader()
            While reader.Read()
                Console.WriteLine( _
                       "{0} - {1}", reader.GetString(0), reader.GetString(1))
            End While
            reader.Close()
        Finally
            con.Close()
        End Try
    End Sub
    Shared Sub con_StateChange(ByVal Sender As Object, _
                 ByVal E As StateChangeEventArgs) Handles con.StateChange
        Console.WriteLine( _
                   "{0} - {1}", "ConnectionState", E.CurrentState.ToString())
    End Sub
End Class

Display the information about the connection.

  
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb
    Public Class MainClass
        Public Shared Sub SqlConnectionExample()
            Using con As New SqlConnection
                con.ConnectionString = "Data Source=.\sqlexpress;Database=AdventureWorks;Integrated Security=SSPI;"
                con.Open()
                If con.State = ConnectionState.Open Then
                    Console.WriteLine("SqlConnection Information:")
                    Console.WriteLine("  Connection State = " & con.State)
                    Console.WriteLine("  Connection String = " & con.ConnectionString)
                    Console.WriteLine("  Database Source = " & con.DataSource)
                    Console.WriteLine("  Database = " & con.Database)
                    Console.WriteLine("  Server Version = " & con.ServerVersion)
                    Console.WriteLine("  Workstation Id = " & con.WorkstationId)
                    Console.WriteLine("  Timeout = " & con.ConnectionTimeout)
                    Console.WriteLine("  Packet Size = " & con.PacketSize)
                Else
                    Console.WriteLine("SqlConenction failed to open.")
                    Console.WriteLine("  Connection State = " & con.State)
                End If
                con.Close()
            End Using
        End Sub
        Public Shared Sub OleDbConnectionExample()
            Using con As New OleDbConnection
                con.ConnectionString = "Provider=SQLOLEDB;Data Source=.\sqlexpress;Initial Catalog=AdventureWorks;Integrated Security=SSPI;"
                con.Open()
                If con.State = ConnectionState.Open Then
                    Console.WriteLine("OleDbConnection Information:")
                    Console.WriteLine("  Connection State = " & con.State)
                    Console.WriteLine("  Connection String = " & con.ConnectionString)
                    Console.WriteLine("  Database Source = " & con.DataSource)
                    Console.WriteLine("  Database = " & con.Database)
                    Console.WriteLine("  Server Version = " & con.ServerVersion)
                    Console.WriteLine("  Timeout = " & con.ConnectionTimeout)
                Else
                    Console.WriteLine("OleDbConnection failed to open.")
                    Console.WriteLine("  Connection State = " & con.State)
                End If
                con.Close()
            End Using
        End Sub
        Public Shared Sub Main()
            SqlConnectionExample()
            OleDbConnectionExample()
        End Sub
    End Class

Execute Non Query Example

  
Imports System
Imports System.Data
Imports System.Data.SqlClient
    Public Class MainClass
        Public Shared Sub ExecuteNonQueryExample(ByVal con As IDbConnection)
            Dim com As IDbCommand = con.CreateCommand
            com.rumandType = CommandType.Text
            com.rumandText = "UPDATE HumanResources.Employee SET Title = "Production Supervisor" WHERE EmployeeID = 24;"
            Dim result As Integer = com.ExecuteNonQuery
            Console.WriteLine(result)
        End Sub
        Public Shared Sub Main()
            Using con As New SqlConnection
                con.ConnectionString = "Data Source=.\sqlexpress;Database=AdventureWorks;Integrated Security=SSPI;"
                con.Open()
                ExecuteNonQueryExample(con)
                con.Close()
            End Using
        End Sub
    End Class

Info Message Event

 
Imports System
Imports System.Collections
Imports System.Data
Imports System.IO
Imports System.Xml.Serialization
Imports System.Windows.Forms
Imports System.Data.SqlClient

Public Class MainClass
    Shared Dim WithEvents con As SqlConnection
    Shared Sub Main()
        con = New SqlConnection("Server=(local)\SQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI") 
        Dim cmd As New SqlCommand()
        cmd.rumandText = "PRINT("This raises an InfoMessage event")"
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Finally
            con.Close()
        End Try
    End Sub
    Shared Sub con_InfoMessage(ByVal Sender As Object, _
                 ByVal E As SqlInfoMessageEventArgs) Handles con.InfoMessage
        Console.WriteLine( _
                   "{0} - {1}", "InfoMessage", E.Errors.Item(0).ToString())
    End Sub
End Class

Obtain a pooled connection

  
Imports System
Imports System.Data.SqlClient
    Public Class MainClass
        Public Shared Sub Main()
            Using con As New SqlConnection
                con.ConnectionString = "Data Source=.\sqlexpress;Database=AdventureWorks;Integrated Security=SSPI;Min Pool Size=5;Max Pool Size=15;Connection Reset=True;Connection Lifetime=600;"
                con.Open()
                con.Close()
            End Using
            Using con As New SqlConnection
                con.ConnectionString = "Data Source=.\sqlexpress;Database=AdventureWorks;Integrated Security=SSPI;Pooling=False;"
                con.Open()
                con.Close()
            End Using
        End Sub
    End Class

Parameterized Command Example

  
Imports System
Imports System.Data
Imports System.Data.SqlClient
    Public Class MainClass
        Public Shared Sub ParameterizedCommandExample(ByVal con As SqlConnection, ByVal employeeID As Integer, ByVal title As String)
            Using com As SqlCommand = con.CreateCommand
                com.rumandType = CommandType.Text
                com.rumandText = "UPDATE HumanResources.Employee SET Title = @title WHERE EmployeeID = @id;"
                Dim p1 As SqlParameter = com.CreateParameter
                p1.ParameterName = "@title"
                p1.SqlDbType = SqlDbType.VarChar
                p1.Value = title
                com.Parameters.Add(p1)
                com.Parameters.Add("@id", SqlDbType.Int).Value = employeeID
                Dim result As Integer = com.ExecuteNonQuery
                Console.WriteLine(result)
            End Using
        End Sub
        Public Shared Sub Main()
            Using con As New SqlConnection
                con.ConnectionString = "Data Source=.\sqlexpress;Database=AdventureWorks;Integrated Security=SSPI;"
                con.Open()
                ParameterizedCommandExample(con, 16, "Production Technician")
                con.Close()
            End Using
        End Sub
    End Class

Parse the SQL Server connection string and display the component configuration parameters.

  
Imports System
Imports System.Data.SqlClient
    Public Class MainClass
        Public Shared Sub Main()
            Dim conString As String = "Data Source=.\sqlexpress;Database=AdventureWorks;Integrated Security=SSPI;Min Pool Size=5;Max Pool Size=15;Connection Lifetime=600;"
            Dim sb1 As New SqlConnectionStringBuilder(conString)
            Console.WriteLine("  Database Source = " & sb1.DataSource)
            Console.WriteLine("  Database = " & sb1.InitialCatalog)
            Console.WriteLine("  Use Integrated Security = " & sb1.IntegratedSecurity)
            Console.WriteLine("  Min Pool Size = " & sb1.MinPoolSize)
            Console.WriteLine("  Max Pool Size = " & sb1.MaxPoolSize)
            Console.WriteLine("  Lifetime = " & sb1.LoadBalanceTimeout)
            Dim sb2 As New SqlConnectionStringBuilder(conString)
            sb2.DataSource = ".\sqlexpress"
            sb2.InitialCatalog = "AdventureWorks"
            sb2.IntegratedSecurity = True
            sb2.MinPoolSize = 5
            sb2.MaxPoolSize = 15
            sb2.LoadBalanceTimeout = 600
            Console.WriteLine(sb2.ConnectionString)
        End Sub
    End Class

SQL Server Online Check

  
Imports System.Windows.Forms
Imports System.Data.SqlClient
Imports System.Data
Public Class MainClass
    Public Shared Function IsSQLServerOnline(ByVal ServerAddress As String) As Boolean
        Try
            Dim objIPHost As New System.Net.IPHostEntry()
            objIPHost = System.Net.Dns.Resolve(ServerAddress)
            Dim objAddress As System.Net.IPAddress
            objAddress = objIPHost.AddressList(0)
            Dim objTCP As System.Net.Sockets.TcpClient = New System.Net.Sockets.TcpClient()
            objTCP.Connect(objAddress, 1433)
            objTCP.Close()
            objTCP = Nothing
            objAddress = Nothing
            objIPHost = Nothing
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function
    Public Shared Sub Main()
        If IsSQLServerOnline("127.0.0.1") Then
            System.Console.WriteLine("Can connect to server!")
        Else
            System.Console.WriteLine("Server not available!")
        End If
    End Sub
End Class