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

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

发表于 2010-01-26 21:19 xp44m 阅读(195) 评论(0) 编辑收藏举报