в 16:40, 26 мая 2010

2010-05-26T16:40:06Z

Admin: 1 версия

2010-05-26T12:44:49Z

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>

← Предыдущая	Версия 16:40, 26 мая 2010
(нет различий)

VB.Net/Database ADO.net/SqlConnection - История изменений

в 16:40, 26 мая 2010

Admin: 1 версия