4.1 建立精确的连接字符串
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Imports System.Data.SqlClient
Public Class BuildConnString
Function GetConnectionString() As String
Return String.Format( _
"AttachDbFilename=|DataDirectory|PUBS.mdf;" _
& "Integrated Security=true;user instance=true;" _
& "Data Source={0};", txtDataSource.Text)
End Function
Private Sub btnConnectionStringTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles btnConnectionStringTest.Click
MessageBox.Show(GetConnectionString)
Dim cn As New SqlConnection(GetConnectionString)
Try
cn.Open()
Dim cmd As New SqlCommand("SELECT DB_NAME()", cn)
MessageBox.Show(cmd.ExecuteScalar.ToString)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
cn.Close()
End Try
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim bld As New SqlConnectionStringBuilder( _
"AttachDbFilename=|DataDirectory|PUBS.mdf;" _
& "Integrated Security=true;user instance=true;" )
bld.DataSource = txtDataSource.Text
MessageBox.Show(bld.ConnectionString)
Dim cn As New SqlConnection(bld.ConnectionString)
Try
cn.Open()
Dim cmd As New SqlCommand("SELECT DB_NAME()", cn)
MessageBox.Show(cmd.ExecuteScalar.ToString)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
cn.Close()
End Try
End Sub
End Class
Public Class BuildConnString
Function GetConnectionString() As String
Return String.Format( _
"AttachDbFilename=|DataDirectory|PUBS.mdf;" _
& "Integrated Security=true;user instance=true;" _
& "Data Source={0};", txtDataSource.Text)
End Function
Private Sub btnConnectionStringTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles btnConnectionStringTest.Click
MessageBox.Show(GetConnectionString)
Dim cn As New SqlConnection(GetConnectionString)
Try
cn.Open()
Dim cmd As New SqlCommand("SELECT DB_NAME()", cn)
MessageBox.Show(cmd.ExecuteScalar.ToString)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
cn.Close()
End Try
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim bld As New SqlConnectionStringBuilder( _
"AttachDbFilename=|DataDirectory|PUBS.mdf;" _
& "Integrated Security=true;user instance=true;" )
bld.DataSource = txtDataSource.Text
MessageBox.Show(bld.ConnectionString)
Dim cn As New SqlConnection(bld.ConnectionString)
Try
cn.Open()
Dim cmd As New SqlCommand("SELECT DB_NAME()", cn)
MessageBox.Show(cmd.ExecuteScalar.ToString)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
cn.Close()
End Try
End Sub
End Class
与提供者无关的数据访问
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Private Sub btnGenericDatabaseAccess_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles btnGenericDatabaseAccess.Click
Dim pubs As ConnectionStringSettings
pubs = ConfigurationManager.ConnectionStrings("PubsData")
Dim factory As DbProviderFactory = DbProviderFactories.GetFactory(pubs.ProviderName)
Dim bld As DbConnectionStringBuilder = factory.CreateConnectionStringBuilder
Dim cn As DbConnection = factory.CreateConnection
cn.ConnectionString = bld.ConnectionString
Dim da As DbDataAdapter = factory.CreateDataAdapter
Dim cmd As DbCommand = factory.CreateCommand
cmd.CommandText = "Select * from authors"
cmd.CommandType = CommandType.Text
cmd.Connection = cn
da.SelectCommand = cmd
Dim cmdbld As DbCommandBuilder = factory.CreateCommandBuilder
cmdbld.DataAdapter = da
Dim ds As DataSet = New DataSet
da.Fill(ds, "authors")
DataGridView1.DataSource = ds
DataGridView1.DataMember = "authors"
End Sub
Handles btnGenericDatabaseAccess.Click
Dim pubs As ConnectionStringSettings
pubs = ConfigurationManager.ConnectionStrings("PubsData")
Dim factory As DbProviderFactory = DbProviderFactories.GetFactory(pubs.ProviderName)
Dim bld As DbConnectionStringBuilder = factory.CreateConnectionStringBuilder
Dim cn As DbConnection = factory.CreateConnection
cn.ConnectionString = bld.ConnectionString
Dim da As DbDataAdapter = factory.CreateDataAdapter
Dim cmd As DbCommand = factory.CreateCommand
cmd.CommandText = "Select * from authors"
cmd.CommandType = CommandType.Text
cmd.Connection = cn
da.SelectCommand = cmd
Dim cmdbld As DbCommandBuilder = factory.CreateCommandBuilder
cmdbld.DataAdapter = da
Dim ds As DataSet = New DataSet
da.Fill(ds, "authors")
DataGridView1.DataSource = ds
DataGridView1.DataMember = "authors"
End Sub
4.2 连接池
4.2.1 创建和打开连接
4.2.2 连接池在哪里
4.2.3 何时创建连接池
4.2.4 连接在池中的生存时间是多少
4.2.5 超出连接池容量
4.2.5.1 是否因连接泄露造成超出连接池容量
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
ConnectionLeak()
Catch ex As Exception
'do something cool to recover
End Try
MessageBox.Show("Done")
End Sub
Sub ConnectionLeak()
Dim cnSettings As SqlConnectionStringBuilder
cnSettings = New SqlConnectionStringBuilder("Data Source=.;" & _
"Database=pubs;" & _
"Integrated Security=true;" & _
"Min Pool Size=3;Max Pool Size=5")
Dim cn As SqlConnection = New SqlConnection(cnSettings.ConnectionString)
cn.Open()
Dim cmd As SqlCommand = cn.CreateCommand
cmd.CommandText = "Raiserror('simulate an error in sql',17,1)"
cmd.ExecuteNonQuery()
cn.Close()
End Sub
End Class
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
ConnectionLeak()
Catch ex As Exception
'do something cool to recover
End Try
MessageBox.Show("Done")
End Sub
Sub ConnectionLeak()
Dim cnSettings As SqlConnectionStringBuilder
cnSettings = New SqlConnectionStringBuilder("Data Source=.;" & _
"Database=pubs;" & _
"Integrated Security=true;" & _
"Min Pool Size=3;Max Pool Size=5")
Dim cn As SqlConnection = New SqlConnection(cnSettings.ConnectionString)
cn.Open()
Dim cmd As SqlCommand = cn.CreateCommand
cmd.CommandText = "Raiserror('simulate an error in sql',17,1)"
cmd.ExecuteNonQuery()
cn.Close()
End Sub
End Class
4.2.5.2 别了,Close方法;欢迎使用Using块
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Sub NoConnectionLeak()
Dim cnSettings As SqlConnectionStringBuilder
cnSettings = New SqlConnectionStringBuilder("Data Source=.;" & _
"Database=pubs;" & _
"Integrated Security=true;" & _
"Min Pool Size=3;Max Pool Size=5")
Using cn As SqlConnection = New SqlConnection(cnSettings.ConnectionString)
cn.Open()
Using cmd As SqlCommand = cn.CreateCommand
cmd.CommandText = "Raiserror('simulate an error in sql',17,1)"
cmd.ExecuteNonQuery()
End Using
End Using
End Sub
Dim cnSettings As SqlConnectionStringBuilder
cnSettings = New SqlConnectionStringBuilder("Data Source=.;" & _
"Database=pubs;" & _
"Integrated Security=true;" & _
"Min Pool Size=3;Max Pool Size=5")
Using cn As SqlConnection = New SqlConnection(cnSettings.ConnectionString)
cn.Open()
Using cmd As SqlCommand = cn.CreateCommand
cmd.CommandText = "Raiserror('simulate an error in sql',17,1)"
cmd.ExecuteNonQuery()
End Using
End Using
End Sub
4.2.6 何时关闭连接池
4.2.7 清除连接池
如果重启数据库服务,下面代码将抛出异常
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim ver As String = Nothing
Dim cnSettings As SqlConnectionStringBuilder
cnSettings = New SqlConnectionStringBuilder("Data Source=.;" & _
"Database=pubs;" & _
"Integrated Security=true;" & _
"Max Pool Size=5")
Using cn As SqlConnection = New SqlConnection(cnSettings.ConnectionString)
cn.Open()
Using cmd As SqlCommand = cn.CreateCommand
cmd.CommandText = "SELECT @@Version"
ver = CType(cmd.ExecuteScalar, String)
End Using
End Using
MessageBox.Show(ver)
End Sub
Dim ver As String = Nothing
Dim cnSettings As SqlConnectionStringBuilder
cnSettings = New SqlConnectionStringBuilder("Data Source=.;" & _
"Database=pubs;" & _
"Integrated Security=true;" & _
"Max Pool Size=5")
Using cn As SqlConnection = New SqlConnection(cnSettings.ConnectionString)
cn.Open()
Using cmd As SqlCommand = cn.CreateCommand
cmd.CommandText = "SELECT @@Version"
ver = CType(cmd.ExecuteScalar, String)
End Using
End Using
MessageBox.Show(ver)
End Sub
解决方案:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Try
DisplayVersion()
Catch xcp As SqlException
If xcp.Number <> 1236 Then Throw xcp
System.Diagnostics.Debug.WriteLine("Clearing Pools")
SqlConnection.ClearAllPools()
DisplayVersion()
End Try
End Sub
Sub DisplayVersion()
Dim ver As String = Nothing
Dim cnSettings As SqlConnectionStringBuilder
cnSettings = New SqlConnectionStringBuilder("Data Source=.;" & _
"Database=pubs;" & _
"Integrated Security=true;" & _
"Max Pool Size=5")
Using cn As SqlConnection = New SqlConnection(cnSettings.ConnectionString)
cn.Open()
Using cmd As SqlCommand = cn.CreateCommand
cmd.CommandText = "SELECT @@Version"
ver = CType(cmd.ExecuteScalar, String)
End Using
End Using
MessageBox.Show(ver)
End Sub
Try
DisplayVersion()
Catch xcp As SqlException
If xcp.Number <> 1236 Then Throw xcp
System.Diagnostics.Debug.WriteLine("Clearing Pools")
SqlConnection.ClearAllPools()
DisplayVersion()
End Try
End Sub
Sub DisplayVersion()
Dim ver As String = Nothing
Dim cnSettings As SqlConnectionStringBuilder
cnSettings = New SqlConnectionStringBuilder("Data Source=.;" & _
"Database=pubs;" & _
"Integrated Security=true;" & _
"Max Pool Size=5")
Using cn As SqlConnection = New SqlConnection(cnSettings.ConnectionString)
cn.Open()
Using cmd As SqlCommand = cn.CreateCommand
cmd.CommandText = "SELECT @@Version"
ver = CType(cmd.ExecuteScalar, String)
End Using
End Using
MessageBox.Show(ver)
End Sub
4.2.8 使用容错恢复技术
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Dim cnSettings As SqlConnectionStringBuilder
cnSettings = New SqlConnectionStringBuilder("Data Source=.;" & _
"Database=FailTest;" & _
"Integrated Security=true;" & _
"Max Pool Size=5" & _
"Failover Partner=.\Partner")
Using cn As SqlConnection = New SqlConnection(cnSettings.ConnectionString)
cn.Open()
Using cmd As SqlCommand = cn.CreateCommand
cmd.CommandText = String.Format( _
"INSERT INTO TestTable(Id,Name) VALUES('{0}','{1}'", Guid.NewGuid, DateTime.Now.ToLongTimeString)
cmd.ExecuteNonQuery()
End Using
MessageBox.Show("Data entered into server: " & cn.DataSource)
End Using
End Sub
Dim cnSettings As SqlConnectionStringBuilder
cnSettings = New SqlConnectionStringBuilder("Data Source=.;" & _
"Database=FailTest;" & _
"Integrated Security=true;" & _
"Max Pool Size=5" & _
"Failover Partner=.\Partner")
Using cn As SqlConnection = New SqlConnection(cnSettings.ConnectionString)
cn.Open()
Using cmd As SqlCommand = cn.CreateCommand
cmd.CommandText = String.Format( _
"INSERT INTO TestTable(Id,Name) VALUES('{0}','{1}'", Guid.NewGuid, DateTime.Now.ToLongTimeString)
cmd.ExecuteNonQuery()
End Using
MessageBox.Show("Data entered into server: " & cn.DataSource)
End Using
End Sub
4.3 异步数据访问
4.3.1 同步访问对比异步访问
同步访问
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim ver As String = Nothing
Dim cnSettings As SqlConnectionStringBuilder
cnSettings = New SqlConnectionStringBuilder( _
"Data Source=.;" & _
"Database=pubs;" & _
"Integrated Security=true;" & _
"Max Pool Size=5")
Using cn As SqlConnection = New SqlConnection(cnSettings.ConnectionString)
cn.Open()
Using cmd As SqlCommand = cn.CreateCommand
cmd.CommandText = "WaitFor Delay '00:00:15' SELECT @@Version"
ver = CType(cmd.ExecuteScalar, String)
End Using
End Using
Label1.Text = ver
End Sub
Dim ver As String = Nothing
Dim cnSettings As SqlConnectionStringBuilder
cnSettings = New SqlConnectionStringBuilder( _
"Data Source=.;" & _
"Database=pubs;" & _
"Integrated Security=true;" & _
"Max Pool Size=5")
Using cn As SqlConnection = New SqlConnection(cnSettings.ConnectionString)
cn.Open()
Using cmd As SqlCommand = cn.CreateCommand
cmd.CommandText = "WaitFor Delay '00:00:15' SELECT @@Version"
ver = CType(cmd.ExecuteScalar, String)
End Using
End Using
Label1.Text = ver
End Sub
异步访问
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim ver As String = Nothing
Dim cnSettings As SqlConnectionStringBuilder
cnSettings = New SqlConnectionStringBuilder( _
"Data Source=.;" & _
"Database=pubs;" & _
"Integrated Security=true;" & _
"Max Pool Size=5" & _
"async=true")
Dim cn As New SqlConnection(cnSettings.ConnectionString)
cn.Open()
Dim cmd As SqlCommand = cn.CreateCommand
cmd.CommandText = "WaitFor Delay '00:00:15' SELECT @@Version"
cmd.BeginExecuteReader(New AsyncCallback(AddressOf ProcessResult), cmd)
End Sub
Sub ProcessResult(ByVal ar As IAsyncResult)
Dim cmd As SqlCommand = CType(ar.AsyncState, SqlCommand)
Using cmd.Connection
Using cmd
Dim ver As String = Nothing
Dim rdr As SqlDataReader = cmd.EndExecuteReader(ar)
If rdr.Read Then
ver = CType(rdr(0), String)
Label1.BeginInvoke(New LabelHandler(AddressOf UpdateLabel), ver)
End If
End Using
End Using
End Sub
Public Delegate Sub LabelHandler(ByVal text As String)
Sub UpdateLabel(ByVal text As String)
Label1.Text = text
End Sub
Dim ver As String = Nothing
Dim cnSettings As SqlConnectionStringBuilder
cnSettings = New SqlConnectionStringBuilder( _
"Data Source=.;" & _
"Database=pubs;" & _
"Integrated Security=true;" & _
"Max Pool Size=5" & _
"async=true")
Dim cn As New SqlConnection(cnSettings.ConnectionString)
cn.Open()
Dim cmd As SqlCommand = cn.CreateCommand
cmd.CommandText = "WaitFor Delay '00:00:15' SELECT @@Version"
cmd.BeginExecuteReader(New AsyncCallback(AddressOf ProcessResult), cmd)
End Sub
Sub ProcessResult(ByVal ar As IAsyncResult)
Dim cmd As SqlCommand = CType(ar.AsyncState, SqlCommand)
Using cmd.Connection
Using cmd
Dim ver As String = Nothing
Dim rdr As SqlDataReader = cmd.EndExecuteReader(ar)
If rdr.Read Then
ver = CType(rdr(0), String)
Label1.BeginInvoke(New LabelHandler(AddressOf UpdateLabel), ver)
End If
End Using
End Using
End Sub
Public Delegate Sub LabelHandler(ByVal text As String)
Sub UpdateLabel(ByVal text As String)
Label1.Text = text
End Sub
4.3.2 使用SQL Server提供者的统计功能
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim authors As New DataTable
Dim pubSettings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("PubsString")
Using cn As New SqlConnection
cn.ConnectionString = pubSettings.ConnectionString
cn.StatisticsEnabled = True
Using cmd As SqlCommand = cn.CreateCommand
cn.Open()
cmd.CommandText = "SELECT * FROM AUTHORS"
authors.Load(cmd.ExecuteReader)
DataGridView1.DataSource = authors
End Using
Dim stats As New ArrayList(cn.RetrieveStatistics)
DataGridView2.DataSource = stats
End Using
End Sub
Dim authors As New DataTable
Dim pubSettings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("PubsString")
Using cn As New SqlConnection
cn.ConnectionString = pubSettings.ConnectionString
cn.StatisticsEnabled = True
Using cmd As SqlCommand = cn.CreateCommand
cn.Open()
cmd.CommandText = "SELECT * FROM AUTHORS"
authors.Load(cmd.ExecuteReader)
DataGridView1.DataSource = authors
End Using
Dim stats As New ArrayList(cn.RetrieveStatistics)
DataGridView2.DataSource = stats
End Using
End Sub