4 连接到数据存储区(高级主题)

最新推荐文章于 2024-10-16 11:14:53 发布

weixin_34087503

最新推荐文章于 2024-10-16 11:14:53 发布

阅读量44

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/csl-office-vb-sql-net/archive/2010/01/26/1657027.h

版权

4.1 建立精确的连接字符串

代码

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

与提供者无关的数据访问

代码

     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

4.2 连接池

4.2.1 创建和打开连接

4.2.2 连接池在哪里

4.2.3 何时创建连接池

4.2.4 连接在池中的生存时间是多少

4.2.5 超出连接池容量

4.2.5.1 是否因连接泄露造成超出连接池容量

代码

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

4.2.5.2 别了，Close方法;欢迎使用Using块

代码

     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

4.2.6 何时关闭连接池

4.2.7 清除连接池

如果重启数据库服务，下面代码将抛出异常

代码

解决方案：

代码

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

4.2.8 使用容错恢复技术

代码

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

4.3 异步数据访问

4.3.1 同步访问对比异步访问

同步访问

代码

异步访问

代码

     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

4.3.2 使用SQL Server提供者的统计功能

代码

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

转载于:https://www.cnblogs.com/csl-office-vb-sql-net/archive/2010/01/26/1657027.html