VB.Net/Database ADO.net/SqlConnection

Материал из VB Эксперт
Версия от 15:44, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Connection state change event

<source lang="vbnet"> 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


 </source>


Display the information about the connection.

<source lang="vbnet"> 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
  
   
 </source>


Execute Non Query Example

<source lang="vbnet"> 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
  
   
 </source>


Info Message Event

<source lang="vbnet"> 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




 </source>


Obtain a pooled connection

<source lang="vbnet"> 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
  
   
 </source>


Parameterized Command Example

<source lang="vbnet"> 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
  
   
 </source>


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

<source lang="vbnet"> 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
  
   
 </source>


SQL Server Online Check

<source lang="vbnet"> 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


 </source>