中级开发 SQL Server Windows .NET Visual Basic

DbToolbox：数据访问和管理实用程序

Gregory Gadow

4.81/5 (19投票s)

2014年1月10日

CPOL

6分钟阅读

35880

691

在 .Net 中进行数据库访问和管理可能是一项繁琐的工作。这个工具箱可以使它变得更容易。

引言

说实话：在 .Net 中访问数据库可能是一件很头疼的事情。但是，有了专用的工具箱和一些基本的对象，它可以大大减轻这种痛苦。为什么要尝试维护 20 个这样的实例

Dim Sql As New StringBuilder
Dim Conn As New SqlConnection
Dim Reader As SqlDataReader = Nothing
Dim Param As SqlParameter = Nothing

Sql.Append("SELECT * FROM Inventory ")
Sql.Append("WHERE Author=@Author AND Title=@Title ")
Sql.Append("ORDER BY Author")

Try
    Conn.ConnectionString = ConnStr
    Dim Cmd As New SqlCommand(Sql.ToString, Conn)
    Cmd.CommandType = CommandType.Text

    Param = New SqlParameter("@Author", SqlDbType.VarChar)
    Param.Value = "Rowling, J. K."
    Cmd.Parameters.Add(Param)

    Param = New SqlParameter("@Title", SqlDbType.VarChar)
    Param.Value = "Harry Potter and the Philosopher's Stone"
    Cmd.Parameters.Add(Param)

    Conn.Open()
    Reader = Cmd.ExecuteReader
    If Reader.HasRows Then
        Do While Reader.Read
            'Do something with the data
        Loop
    Else
        'Do something if there is no data
    End If
    Reader.Close()
Catch ex As Exception
    'Handle exception
Finally
    Conn.Close()
End Try

而你可以拥有 20 个这样的实例呢？

Dim Sql As New StringBuilder
Dim Params As New SqlParameterList
Dim RS As SqlRecordset = Nothing

Sql.Append("SELECT * FROM Inventory ")
Sql.Append("WHERE Author=@Author AND Title=@Title ")
Sql.Append("ORDER BY Author")

Params.Add("@Author", SqlDbType.VarChar, "Rowling, J. K.")
Params.Add("@Title", SqlDbType.VarChar, "Harry Potter and the Philosopher's Stone")
RS = SqlLib.OpenRecordset(Sql.ToString, Params, ConnStr)
For Each DR As DataRow In RS.Rows
    'Do something with the data
Next

背景

我大约六年前为我公司网站的用途编写了这些方法和对象，它们已被证明相当健壮。SqlTransaction 对象的使用频率不高，但我到目前为止没有遇到任何问题。当然，您的使用情况可能会有所不同。

那些想将此代码移植到 C# 的人会注意到一些 VB 特定的方法的使用，例如将一个对象转换为 Integer 的 CInt 函数。这些方法可以从任何 .Net 语言中轻松调用：您只需要引用 Microsoft.VisualBasic 程序集即可。这可能比使用像 Convert.ToInt32 这样的语言无关版本更容易。

此代码最初是用 Visual Studio 2005 编写的，后来迁移到 VS 2008，并在其中得到了最多的使用。基本概念在 .Net Framework 的更高版本中仍然有用。

总体组织结构

由源代码生成的 DLL 称为 DbToolbox。我使用“工具箱”一词来表示一个不可继承的类，其中仅包含常量和共享（在 C# 中为 static）方法。

在 DbToolbox 命名空间内是 DbLib 工具箱，其中包含许多有用的方法，主要是将数据库值（可能包含 DbNull 值）转换为值类型。

还有一个 SqlClient 命名空间，其中包含访问 SQL Server 所需的对象和工具箱。如果您想扩展 DLL 以包含 OLE、ODBC 或其他特定技术，您可能想将它们收集到自己的命名空间中。

SqlParameterList

让我们从 SqlParameterList 对象开始。顾名思义，它是一个 Framework 的 SqlParameter 对象的列表，并提供额外的方法，允许用户按名称引用成员对象。这是完整的代码

Public Class SqlParameterList
    Inherits List(Of SqlParameter)

    Public Function ContainsParamName(ByVal ParameterName As String) As Boolean
        If Me.Count = 0 Then Return False

        Dim RetVal As Boolean = False

        For Each SQP As SqlParameter In Me
            If String.Equals(SQP.ParameterName, ParameterName, _
                    StringComparison.CurrentCultureIgnoreCase) Then 
                RetVal = True
                Exit For
            End If
        Next

        Return RetVal
    End Function

    Public Function Copy() As SqlParameterList
        Dim PL As New SqlParameterList

        For Each SP As SqlParameter In Me
            PL.Add(CType(SP, ICloneable).Clone)
        Next

        Return PL
    End Function

    Public Shadows Function Find(ByVal ParameterName As String) As SqlParameter
        If Me.Count = 0 Then Return Nothing

        Dim Result As SqlParameter = Nothing

        For Each P As SqlParameter In Me
            If String.Equals(P.ParameterName, ParameterName, _
                    StringComparison.CurrentCultureIgnoreCase) = 0 Then 
                Result = P
                Exit For
            End If
        Next

        Return Result
    End Function

    Default Overloads Property Item(ByVal ParameterName As String) As SqlParameter
        Get
            If Find(ParameterName) Is Nothing Then
                Throw New ArgumentException( _
                    String.Format("Collection does not contain an element '{0}'.", ParameterName))
            End If

            Dim I As Integer = 0
            For I = 0 To Me.Count - 1
                If String.Compare(Me.Item(I).ParameterName, ParameterName, ignoreCase:=True) = 0 Then
                    Exit For
                End If
            Next

            Return Me(I)
        End Get
        Set(ByVal value As SqlParameter)
            Throw New InvalidOperationException("List items cannot be set using this property overload.")
        End Set
    End Property

    Public Overloads Function Add(ByVal Parameter As SqlParameter) As SqlParameter
        Remove(Parameter.ParameterName) 
        MyBase.Add(Parameter)
        Return Parameter
    End Function

    Public Overloads Function Add(ByVal ParameterName As String, ByVal ParameterType As SqlDbType, _
    ByVal Value As Object) As SqlParameter
        Dim Param As New SqlParameter(ParameterName, ParameterType)

        If Value Is Nothing Then
            Param.Value = DBNull.Value
        Else
            Param.Value = Value
        End If

        Return Me.Add(Param)
    End Function

    Public Overloads Sub Remove(ByVal ParameterName As String)
        If Me.Count = 0 Then Exit Sub
        Dim ThisParam As SqlParameter = Nothing

        For Each Param As SqlParameter In Me
            If String.Equals(Param.ParameterName, ParameterName, _
                    StringComparison.CurrentCultureIgnoreCase) Then
                ThisParam = Param
                Exit For
            End If
        Next

        If ThisParam IsNot Nothing Then MyBase.Remove(ThisParam)
    End Sub

    Public Overrides Function ToString() As String
        Dim Retval As New StringBuilder

        If Me.Count = 0 Then
            Retval.Append("[Parameter list is empty]")
        Else
            For Each Param As SqlParameter In Me
                Retval.AppendFormat("Parameter {0}: {1} ({2}, {3})" + vbCrLf, _
                    Param.ParameterName, Param.Value, Param.SqlDbType.ToString, Param.Size)
            Next
        End If

        Return Retval.ToString
    End Function

    Public Function ToStringHtml() As String
        Dim Retval As New StringBuilder

        If Me.Count = 0 Then
            Retval.Append("[Parameter list is empty]")
        Else
            If Me.Count > 0 Then
                Retval.Append("")
                For Each Param As SqlParameter In Me
                    Retval.AppendFormat("Parameter {0}: {1} ({2}, {3})" + vbCrLf, _
                        Param.ParameterName, Param.Value, Param.SqlDbType.ToString, Param.Size)
                Next
                Retval.AppendLine("")
            End If
        End If

        Return Retval.ToString
    End Function

End Class

默认属性 Item 的重载允许您通过名称引用参数

Params("@Author").Value = "George R. R. Martin"

基类的 Add 方法被重载，首先删除同名参数，从而保证列表中的名称是唯一的。另一个 Add 方法使添加新参数更容易；我的代码中没有一个需要方向，而且我从未使用过不指定字段大小的问题，但如果这些对您来说是问题，那么制作额外的重载将非常容易。

ToString 方法被重写，以一种清晰、有序的方式呈现所有成员参数。因为这最初是为网站使用而编写的，所以我添加了 ToHtmlString，它以 HTML 格式提供相同的输出，用于调试目的。

SqlRecordset

我所做的大部分工作都涉及从数据库中提取数据并呈现给用户，因此 SqlRecordset 被大量使用。

Imports System.Data
Imports System.Data.SqlClient
Imports System.Text

Public Class SqlRecordset

    Private pSelectParameters As SqlParameterList
    Private pSelectCommand As String
    Private pTable As DataTable

    Public ReadOnly Property Columns() As DataColumnCollection
        Get
            Return pTable.Columns
        End Get
    End Property

    Default Public ReadOnly Property Row(ByVal Index As Integer) As DataRow
        Get
            If Index < 0 OrElse Index > pTable.Rows.Count - 1 Then
                Throw New ArgumentOutOfRangeException
            Else
                Return pTable.Rows(Index)
            End If
        End Get
    End Property

    Public ReadOnly Property Rows() As DataRowCollection
        Get
            Return pTable.Rows
        End Get
    End Property

    Public Property SelectParameters() As SqlParameterList
        Get
            Return pSelectParameters
        End Get
        Friend Set(ByVal value As SqlParameterList)
            If value Is Nothing Then
                pSelectParameters = Nothing
            Else
                pSelectParameters = value.Copy
            End If
        End Set
    End Property

    Public Property SelectCommand() As String
        Get
            Return pSelectCommand
        End Get
        Set(ByVal value As String)
            pSelectCommand = value
        End Set
    End Property

    Public Property Table() As DataTable
        Get
            Return pTable
        End Get
        Friend Set(ByVal value As DataTable)
            pTable = value.Copy
        End Set
    End Property

    Public Sub New()
        pSelectCommand = ""
        pSelectParameters = Nothing
        pTable = Nothing
    End Sub

End Class

这基本上是 Framework 的 DataTable 对象的一个包装器，同时跟踪用于填充它的命令和参数。默认属性 Row 使访问特定行更容易

If RS.Rows.Count > 5 Then DR = RS(4)

这个片段确保对象有第五行，然后检索它。另请注意，属性 Table 中的 Set 方法使用了 Friend 修饰符：这允许在程序集内部设置该属性，但在程序集外部使用时，该属性是只读的。

SqlDbException

这个对象是 System.Exception 类的扩展。它有两个目的：它允许编码器在 Catch 块中过滤掉由工具箱引发的异常，并封装生成错误的调用的命令字符串和参数列表。代码相当基础；如果您想查看它，请参阅附带的源代码文件。

SqlLib

SqlLib 工具箱的核心包含三个函数

Execute - 执行 SQL 命令并返回受影响的行数。
ExecuteScalar - 执行 SQL select 语句并返回第一行第一列的值，如果返回值为空，则返回 Nothing。
OpenRecordset - 执行 SQL select 语句并将结果返回到 SqlRecordset 对象中。

OpenRecordset 的代码对于这三个函数的工作方式非常典型。

Public Shared Function OpenRecordset( _
ByVal SelectCommand As String, _
ByVal Params As SqlParameterList, _
ByVal ConnectionString As String) As SqlRecordset

    Dim Conn As SqlConnection = Nothing
    Dim Cmd As SqlCommand = Nothing
    Dim Adapter As SqlDataAdapter = Nothing
    Dim DS As DataSet = Nothing
    Dim RS As SqlRecordset = Nothing

    Try
        Conn = New SqlConnection(ConnectionString)
        Cmd = New SqlCommand(SelectCommand, Conn)
        Cmd.CommandTimeout = 300
        If Params IsNot Nothing Then Cmd.Parameters.AddRange(Params.ToArray)
        Adapter = New SqlDataAdapter(Cmd)
        DS = New DataSet
        Adapter.Fill(DS)
        RS = New SqlRecordset
        RS.Table = DS.Tables(0)
        RS.SelectParameters = Params
        RS.SelectCommand = SelectCommand
    Catch ex As Exception
        Dim Msg As New StringBuilder
        Msg.AppendFormat("Error opening recordset: {0}" + vbCrLf, Command)
        For Each p As SqlParameter In Params
            Msg.AppendFormat("{0} = {1}" + vbCrLf, p.ParameterName, p.Value)
        Next
        Throw New SqlDbException(Msg.ToString, ex, Command, Params)
    Finally
        If DS IsNot Nothing Then DS.Dispose()
        If Adapter IsNot Nothing Then Adapter.Dispose()
        If Conn IsNot Nothing Then
            If Conn.State <> ConnectionState.Closed Then Conn.Close()
            Conn.Dispose()
        End If
        If Cmd IsNot Nothing Then
            Cmd.Parameters.Clear()
            Cmd.Dispose()
        End If
    End Try

    Return RS
End Function

这对大多数读者来说应该很熟悉。传入一个 SQL 选择命令，参数列表（如果命令不使用参数，则可以为 Nothing），以及连接字符串。打开连接，将命令执行到数据适配器中，将第一个（通常是唯一的）表与参数和命令一起放入返回值中，关闭并处理，然后返回结果。

Execute 和 ExecuteScalar 方法非常相似，只是它们分别返回 SqlCommand.ExecuteNonQuery 和 SqlCommand.ExecuteScalar 返回的值。

工具箱还有另一个我偶尔发现很有用的方法

Public Shared Function GetTableIdentity(ByVal TableName As String, ByVal ConnectionString As String) _
As Object
    Dim Sql As New StringBuilder
    Dim Params As New SqlParameterList

    Sql.Append("SELECT IDENT_CURRENT(@TableName)")
    Params.Add("@TableName", SqlDbType.VarChar, TableName)

    Return ExecuteScalar(Sql.ToString, Params, ConnectionString)
End Function

这会查看表并返回其当前的标识值，如果表没有标识，则返回 DB.Null。

事务

有时，您需要将一系列命令作为一个整体执行，要么全部成功，要么全部失败。SQL 通过其事务模型支持这一点。

SqlTransaction 相当长，所以我将引导您参考可下载的源代码。基本上，它包含 Framework 的 SqlConnection 和 SqlCommand 对象的实例，再加上连接字符串的副本。它实现了允许编码器执行命令、提交操作和回滚操作的方法。

构造函数被标记为 Friend，因此编码器无法使用它。要打开事务，您需要调用 SqlLib.BeginTransaction，它接受连接字符串并返回一个新的事务。通过事务对象上的 Execute 方法执行事务命令。最终处理是通过调用 Commit 或 Rollback 来完成的。例如

Public Sub AddNewBook(ByVal Title As String, ByVal Author As String, ByVal ISBN As String, _
ByVal PublisherId As Integer, ByVal DistributorId As Integer)
    Dim Trans As SqlTransaction = Nothing
    Dim Sql As New StringBuilder
    Dim Params As New SqlParameterList

    'First, we create the transaction
    Trans = SqlLib.BeginTransaction(ConnStr)

    'We will be using all of the method parameters, so set them up.
    'We can pass the whole list into SqlLib, even when the associated
    'command or query uses only some of the parameters.
    Params.Add("@Title", SqlDbType.VarChar, Title)
    Params.Add("@Author", SqlDbType.VarChar, Author)
    Params.Add("@ISBN", SqlDbType.VarChar, ISBN)
    Params.Add("@PublisherId", SqlDbType.Int, PublisherId)
    Params.Add("@DistributorId", SqlDbType.Int, DistributorId)

    Try
        'All actions are done using the Transaction object, not SqlLib
        Sql.Append("INSERT BookPublishers (PublisherId, ISBN) VALUES (@PublisherId, @ISBN)")
        Trans.Execute(Sql.ToString, Params, ConnStr)

        'Reset Sql for reuse
        Sql.Length = 0
        Sql.Append("INSERT BookDistributors (DistributorId, ISBN) VALUES (@DistributorId, @ISBN)")
        Trans.Execute(Sql.ToString, Params, ConnStr)

        Sql.Length = 0
        Sql.Append("INSERT Inventory (Title, Author, ISBN, BookCount) ")
        Sql.Append("VALUES (@Title, @Author, @ISBN, 0)")
        Trans.Execute(Sql.ToString, Params, ConnStr)

        'If we haven't blown up yet, we can try to commit the transaction
        Trans.Commit()
    Catch ex As Exception
        Try
            Trans.Rollback()
        Catch ex1 As Exception
            Dim Bad As New SqlDbException("Exception thrown in AddNewBook.", ex, Sql.ToString, Params)
            Throw New SqlDbException("Unable to roll back transaction.", Bad, Sql.ToString, Params)
        End Try
        Throw New SqlDbException("Exception thrown in AddNewBook.", ex, Sql.ToString, Params)
    End Try

    'Trans is disposed when it goes out of scope.
End Sub

SqlTransaction 实现 IDisposable，因此您也可以在 Using 块中编写此代码。另外，请注意 Framework 在 System.Data.SqlClient 命名空间中拥有自己的 SqlTransaction 对象。如果您引用该命名空间，则可能需要使用限定符来指示您正在使用哪个对象。

实用方法

最后一部分是 DbLib 工具箱，其中包含一些通用的方法，可以应用于任何数据库技术。其中大多数用于将 Framework 返回的 Object 转换为预期的数据类型。例如，假设您期望一个整数

Public Shared Function ToInteger(ByVal Value As Object) As Integer
    If IsNumeric(Value) Then
        Return CInt(Value)
    Else
        Return Integer.MinValue
    End If
End Function

Public Shared Function ToIntegerNull(ByVal Value As Object) As Nullable(Of Integer)
    Dim Result As New Nullable(Of Integer)

    If IsNumeric(Value) Then
        Result = CInt(Value)
    ElseIf Value Is DBNull.Value OrElse Value Is Nothing Then
        Result = Nothing
    Else
        Throw New ArgumentException("Value is neither a number nor DBNull.")
    End If
End Function

第一个方法 ToInteger，如果 Value 是数字，则返回其整数值，否则返回 Integer.MinValue。由于值类型不能设置为 Nothing，因此无法捕获列包含 null 值的可能性。这时 ToIntegerNull 就派上用场了：如果可能，它将返回一个整数值，如果值为 null，则返回 Nothing，否则抛出异常。

示例

至于实际*使用*工具箱，我在源代码中包含了一些示例。

飞向无限，超越无限

使用 DbToolbox.SqlClient 的模型，您可以轻松编写 OLE、ODBC、Access、Oracle 或任何其他可以在 .Net 中实现的数据库技术的版本。如果您有什么有趣的想法，或者对改进这个工具箱有建议，请在下面的评论中告诉我。

更新

2014-01-15: 请注意！ 我已更新源代码以修复一个错误，该错误会导致在传递 Nothing 而非 SqlParameterList 对象时工具箱崩溃。如果您之前已下载代码，请确保您拥有 DbToolbox_VB_v2.zip。

DbToolbox： 数据访问和管理实用程序

引言

背景