VB.Net/Database ADO.net/SqlConnection
Содержание
- 1 Connection state change event
- 2 Display the information about the connection.
- 3 Execute Non Query Example
- 4 Info Message Event
- 5 Obtain a pooled connection
- 6 Parameterized Command Example
- 7 Parse the SQL Server connection string and display the component configuration parameters.
- 8 SQL Server Online Check
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>