65.9K
CodeProject 正在变化。 阅读更多。
Home

流畅的数据访问

Jörgen Andersson
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.88/5 (33投票s)

2014 年 10 月 6 日

CPOL

5分钟阅读

viewsIcon

71217

downloadIcon

3455

流畅的数据访问。

引言

我一直觉得连接数据库非常啰嗦。
如果我们看一个教科书式的例子

SqlConnection Connection = default(SqlConnection);
SqlCommand Command = default(SqlCommand);
try
{
    Connection = new SqlConnection("MyConnectionString");
    Command = Connection.CreateCommand();
    Command.CommandText = "Select * From MyTable";
    Connection.Open();
    return Command.ExecuteReader();
}
finally
{
    if (Command != null)
        Command.Dispose();
    if (Connection != null)
        Connection.Dispose();
}
Dim Connection As SqlConnection
Dim Command As SqlCommand
Try
    Connection = New SqlConnection("MyConnectionString")
    Command = Connection.CreateCommand
    Command.CommandText = "Select * From MyTable"
    Connection.Open()
    Return Command.ExecuteReader
Finally
    If Not IsNothing(Command) Then Command.Dispose()
    If Not IsNothing(Connection) Then Connection.Dispose()
End Try

每次我想对数据库做些什么，都需要写大量的重复代码。
我几乎总是使用同一个连接，相同的代码来创建命令和打开连接。
在 99% 的情况下，我都会在之后释放命令并关闭连接。

我也喜欢流畅的接口，那么如果相同的代码可以这样写呢？

DbAccess.GetDbConnection("MyConnection").GetCommand("Select * From MyTable").GetDataReader();

 GetDbConnection("MyConnection").GetCommand("Select * From MyTable").GetDataReader

甚至更好

return MyDB.GetConnection.GetCommand("Select * From MyTable").GetDataReader();
Return MyDB.GetCommand("Select * From MyTable").GetDataReader

所以我决定创建这段代码。

连接

那么除了流畅之外，我应该有哪些设计要求呢？
嗯，首先，我希望它是数据库无关的，其次，我想在配置文件中使用Connectionstrings部分，这样我就可以在不重新编译的情况下更改数据库。
而且我也想使用 ADO.NET，这样我就可以相当确定它会得到许多数据库供应商的支持，并且在框架下一次更新时仍然有效。
就是这样。

所以为了避免再次发明轮子，我决定使用DbProviderFactories类。

所以让我们从一个共享类开始，并添加一个返回DbProviderFactory的函数（如果已安装）。

private readonly static DataTable FactoriesTable = DbProviderFactories.GetFactoryClasses();

private static DbProviderFactory GetFactory(ConnectionStringSettings ConnectionStringSetting)
{
    foreach (DataRow row in FactoriesTable.Rows)
    {
        if (Convert.ToString(row["InvariantName"]) == ConnectionStringSetting.ProviderName)
        {
            return DbProviderFactories.GetFactory(ConnectionStringSetting.ProviderName);
        }
    }
    return null;
}
Private ReadOnly FactoriesTable As DataTable = DbProviderFactories.GetFactoryClasses()

Private Function GetFactory(ConnectionStringSetting As ConnectionStringSettings) As DbProviderFactory
    For Each row As DataRow In FactoriesTable.Rows
        If CStr(row("InvariantName")) = ConnectionStringSetting.ProviderName Then
            Return DbProviderFactories.GetFactory(ConnectionStringSetting.ProviderName)
        End If
    Next
    Return Nothing
End Function

并使用此函数创建DbConnection

private static DbConnection CreateDbConnection(ConnectionStringSettings ConnectionStringSetting)
{
    DbProviderFactory Factory = GetFactory(ConnectionStringSetting);
    if ((Factory != null))
    {
        DbConnection Connection = Factory.CreateConnection();
        if ((Connection != null))
        {
            Connection.ConnectionString = ConnectionStringSetting.ConnectionString;
            return Connection;
        }
    }
    return null;
}
Private Function CreateDbConnection(ConnectionStringSetting As ConnectionStringSettings) As DbConnection
    Dim Factory As DbProviderFactory = GetFactory(ConnectionStringSetting)
    If Not IsNothing(Factory) Then
        Dim Connection As DbConnection = Factory.CreateConnection()
        If Not IsNothing(Connection) Then
            Connection.ConnectionString = ConnectionStringSetting.ConnectionString
            Return Connection
        End If
    End If
    Return Nothing
End Function

现在我们有了返回DbConnection的几个方法的基类。

public static DbConnection GetDbConnection()
{
    DbConnection Connection = null;
    foreach (ConnectionStringSettings ConnectionStringSetting in ConfigurationManager.ConnectionStrings)
    {
        Connection = CreateDbConnection(ConnectionStringSetting);
        if ((Connection != null))
        {
            return Connection;
        }
    }
    throw new DataException("Check the ConnectionString Section in the ConfigFile");
}

public static DbConnection GetDbConnection(string ConnectionName)
{
    foreach (ConnectionStringSettings ConnectionStringSetting in ConfigurationManager.ConnectionStrings)
    {
        if (ConnectionName == ConnectionStringSetting.Name)
        {
            return CreateDbConnection(ConnectionStringSetting);
        }
    }
    throw new DataException(string.Format("Check the ConnectionString Section 
            in the ConfigFile if a connectionString named {0} exists", ConnectionName));
}
Public Function GetDbConnection() As DbConnection
    Dim Connection As DbConnection = Nothing
    For Each ConnectionStringSetting As ConnectionStringSettings _
                 In ConfigurationManager.ConnectionStrings
        Connection = CreateDbConnection(ConnectionStringSetting)
        If Not IsNothing(Connection) Then
            Return Connection
        End If
    Next
    Throw New DataException("Check the ConnectionString Section in the ConfigFile")
End Function

Public Function GetDbConnection(ConnectionName As String) As DbConnection
    For Each ConnectionStringSetting As ConnectionStringSettings _
                         In ConfigurationManager.ConnectionStrings
        If ConnectionName = ConnectionStringSetting.Name Then
            Return CreateDbConnection(ConnectionStringSetting)
        End If
    Next
    Throw New DataException(string.Format("Check the ConnectionString Section in the _
                 ConfigFile if a connectionString named {0} exists", ConnectionName))
End Function

在大多数情况下，我只使用一个数据库，所以该函数简单地返回第一个已安装提供程序的ConnectionString的DbConnection。
但足够频繁地，您想命名连接，所以也有一个重载。

命令

DbCommand的创建者不接受任何参数，所以我们也需要将它包装在一个函数中。

public static DbCommand GetCommand(this DbConnection Connection,
    string CommandText,
    IEnumerable<DbParameter> Parameters = null,
    CommandType CommandType = System.Data.CommandType.Text)
{
    if (Connection == null) { throw new ArgumentNullException("Connection is null"); }
    if (string.IsNullOrWhiteSpace(CommandText)) 
             { throw new ArgumentNullException("CommandText is null"); }

    DbCommand Command = Connection.CreateCommand();
    Command.CommandText = CommandText;
    Command.CommandType = CommandType;
    if ((Parameters != null))
    {
        foreach (DbParameter Parameter in Parameters)
        {
            Command.Parameters.Add(Parameter);
        }
    }
    return Command;
}
<Extension> _
Public Function GetCommand(Connection As DbConnection,
                           CommandText As String,
                           Optional Parameters As IEnumerable(Of DbParameter) = Nothing,
                           Optional CommandType As CommandType = System.Data.CommandType.Text) _
                                         As DbCommand

    If Connection Is Nothing Then Throw New ArgumentNullException("Connection is null")
    If String.IsNullOrWhiteSpace(CommandText) _
               Then Throw New ArgumentNullException("CommandText is null")

    Dim Command As DbCommand = Connection.CreateCommand()
    Command.CommandText = CommandText
    Command.CommandType = CommandType
    If (Parameters IsNot Nothing) Then
        For Each Parameter As DbParameter In Parameters
            Command.Parameters.Add(Parameter)
        Next
    End If
    Return Command
End Function

执行命令

通常，您只会获取Command并执行ExecuteReader或ExecuteNonQuery，但我们需要清理自己，所以这些也需要被包装。

public static DbDataReader GetDataReader(this DbCommand Command, 
CommandBehavior CommandBehavior = CommandBehavior.CloseConnection | CommandBehavior.KeyInfo)
{
    if (Command == null) { throw new ArgumentNullException("Command is null"); }
    if (Command.Connection == null) { throw new ArgumentNullException("Connection is null"); }

    using (Command)
    {
        DbConnection Connection = Command.Connection;
        if (Connection.State != ConnectionState.Open)
        {
            Connection.Open();
        }
        return Command.ExecuteReader(CommandBehavior);
    }
}

public static DataTable GetDataTable(this DbCommand Command, String TableName = "")
{
    if (Command == null) { throw new ArgumentNullException("Command is null"); }
    if (Command.Connection == null) { throw new ArgumentNullException("Command.Connection is null"); }

    using (DbConnection Connection = Command.Connection)
    using (Command)
    {
        DbProviderFactory Factory = DbProviderFactories.GetFactory(Connection);
        using (DbDataAdapter Adapter = Factory.CreateDataAdapter())
        {
            Adapter.SelectCommand = Command;
            DataTable dt = new DataTable(TableName);
            Adapter.Fill(dt);
            return dt;
        }
    }
}

public static object GetScalar(this DbCommand Command)
{
    if (Command == null) { throw new ArgumentNullException("Command is null"); }
    if (Command.Connection == null) { throw new ArgumentNullException("Connection is null"); }

    using (Command)
    {
         using (DbConnection Connection = Command.Connection)
        {
            if (Connection.State != ConnectionState.Open)
            {
                Connection.Open();
            }
            return Command.ExecuteScalar();
        }
    }
}

public static T GetScalar<T>(this DbCommand Command)
{
    if (Command == null) { throw new ArgumentNullException("Command is null"); }
    if (Command.Connection == null) { throw new ArgumentNullException("Connection is null"); }

    using (Command)
    {
        using (DbConnection Connection = Command.Connection)
        {
            if (Connection.State != ConnectionState.Open)
            {
                Connection.Open();
            }
            object Value = Command.ExecuteScalar();
            if ((object.ReferenceEquals(Value, DBNull.Value)) || (Value == null))
            {
                return default(T);
            }
            else if (object.ReferenceEquals(typeof(T), Value.GetType()) || 
                               typeof(T).IsAssignableFrom(Value.GetType()))
            {
                return (T)Value;
            }
            else if (typeof(T).IsGenericType && 
                   typeof(T).GetGenericTypeDefinition() == typeof(Nullable<>))
            {
                return (T)Convert.ChangeType(Value, typeof(T).GetGenericArguments()[0]);
            }
            else
            {
                return (T)Convert.ChangeType(Value, typeof(T));
            }
        }
    }
}

public static int GetNonQuery(this DbCommand Command)
{
    if (Command == null) { throw new ArgumentNullException("Command is null"); }
    if (Command.Connection == null) { throw new ArgumentNullException("Connection is null"); }

    using (Command)
    {
        using (DbConnection Connection = Command.Connection)
        {
            if (Connection.State != ConnectionState.Open)
            {
                Connection.Open();
            }
            return Command.ExecuteNonQuery();
        }
    }
}
<Extension>
    Public Function GetDataReader(Command As DbCommand, _
          Optional CommandBehavior As CommandBehavior = DirectCast(CommandBehavior.CloseConnection + _
          CommandBehavior.KeyInfo, CommandBehavior)) As DbDataReader
    If Command Is Nothing Then Throw New ArgumentNullException("Command is null")
    If Command.Connection Is Nothing Then Throw New ArgumentNullException("Connection is null")

    Using Command
        Dim Connection As DbConnection = Command.Connection
    
        If Connection.State <> ConnectionState.Open Then
            Connection.Open()
        End If
        Return Command.ExecuteReader(CommandBehavior)
    End Using
End Function

<Extension>
Public Function GetDataTable(Command As DbCommand, Optional TableName As [String] = "") As DataTable
    If Command Is Nothing Then Throw New ArgumentNullException("Command is null")
    If Command.Connection Is Nothing Then Throw New ArgumentNullException("Command.Connection is null")

    Using Connection As DbConnection = Command.Connection
        Using Command
            Dim Factory As DbProviderFactory = DbProviderFactories.GetFactory(Connection)
            Using Adapter As DbDataAdapter = Factory.CreateDataAdapter()
                Adapter.SelectCommand = Command
                Dim dt As New DataTable(TableName)
                Adapter.Fill(dt)
                Return dt
            End Using
        End Using
    End Using
End Function

<Extension>
Public Function GetScalar(Command As DbCommand) As Object
    If Command Is Nothing Then Throw New ArgumentNullException("Command is null")
    If Command.Connection Is Nothing Then Throw New ArgumentNullException("Connection is null")

    Using Command
        Using Connection As DbConnection = Command.Connection
            If Connection.State <> ConnectionState.Open Then
                Connection.Open()
            End If
            Return Command.ExecuteScalar()
        End Using
    End Using
End Function

<Extension>
Public Function GetScalar(Of T)(Command As DbCommand) As T
    If Command Is Nothing Then Throw New ArgumentNullException("Command is null")
    If Command.Connection Is Nothing Then Throw New ArgumentNullException("Connection is null")

    Using Command
        Using Connection As DbConnection = Command.Connection
            If Connection.State <> ConnectionState.Open Then Connection.Open()

            Dim Value As Object = Command.ExecuteScalar()
            If (Object.ReferenceEquals(Value, DBNull.Value)) OrElse (Value Is Nothing) Then
                Return Nothing
            ElseIf Object.ReferenceEquals(GetType(T), Value.[GetType]()) _
                   OrElse GetType(T).IsAssignableFrom(Value.[GetType]()) Then
                Return DirectCast(Value, T)
            ElseIf GetType(T).IsGenericType AndAlso _
               GetType(T).GetGenericTypeDefinition() = GetType(Nullable(Of )) Then
                Return DirectCast(Convert.ChangeType(Value, GetType(T).GetGenericArguments()(0)), T)
            Else
                Return DirectCast(Convert.ChangeType(Value, GetType(T)), T)
            End If
        End Using
    End Using
End Function

<Extension>
Public Function GetNonQuery(Command As DbCommand) As Integer
    If Command Is Nothing Then Throw New ArgumentNullException("Command is null")
    If Command.Connection Is Nothing Then Throw New ArgumentNullException("Connection is null")

    Using Command
        Using Connection As DbConnection = Command.Connection
            If Connection.State <> ConnectionState.Open Then
                Connection.Open()
            End If
            Return Command.ExecuteNonQuery()
        End Using
    End Using
End Function

GetNonQuery？这个名字是不是很傻？
是的，但是到目前为止，我为所有其他方法使用了“Get”前缀，我喜欢保持一致。而且ExecuteNonQuery确实返回一个值，所以也不是完全不好。
请随时重命名这些方法，如果您愿意，但“ExecuteNonQueryAndTerminateResourcesAndConnections”也显得有点傻。

总之，我们现在已经到了可以在一行代码中执行datareader，同时释放所有资源的地步了。

Return DbAccess.GetDbConnection("MyConnection").GetCommand("Select * From MyTable").GetDataReader();
Return GetDbConnection("MyConnection").GetCommand("Select * From MyTable").GetDataReader

但如果我不想使用字符串参数来连接我的数据库呢？

那么，我们需要在这个库中添加另一个函数

private static DbConnection CreateDbConnection(ConnectionStringSettings ConnectionStringSetting)
{
    DbProviderFactory Factory = GetFactory(ConnectionStringSetting);
    if ((Factory != null))
    {
        DbConnection Connection = Factory.CreateConnection();
        if ((Connection != null))
        {
            Connection.ConnectionString = ConnectionStringSetting.ConnectionString;
            return Connection;
        }
    }
    return null;
}
Public Function GetDbConnection(ConnectionStringSetting As ConnectionStringSettings) As DbConnection
    Dim Connection As DbConnection = CreateDbConnection(ConnectionStringSetting)
    If Connection Is Nothing Then
        Throw New DataException("Provider not installed")
    Else
        Return Connection
    End If
End Function

现在我们可以添加一个小函数，为Connectionstrings添加智能感知。但请注意，这需要为每个项目单独完成。

public static class NorthWind
{
    public static SqlConnection GetConnection()
    {
        return SqlAccess.GetSQLConnection("Northwnd");
    }
}
Public Module MyConnections
    Function Northwind() As SqlConnection
        Return GetSQLConnection("Northwnd")
    End Function
End Module

这看起来不错。

return Northwnd.GetDbConnection.GetCommand("Select * From MyTable").GetDataReader();
Return Northwnd.GetCommand("Select * From MyTable").GetDataReader

那事务呢？

事务旨在在一个块中执行，这就是它们的全部意义。
但让我们添加一些方法来让生活更轻松一点。

public static DbCommand GetCommand(this DbTransaction Transaction, 
    string CommandText, 
    IEnumerable<DbParameter> Parameters = null, 
    CommandType CommandType = System.Data.CommandType.Text)
{
    if (Transaction == null) { throw new ArgumentNullException("Transaction is null"); }
    if (string.IsNullOrWhiteSpace(CommandText)) 
            { throw new ArgumentNullException("CommandText is null"); }

    DbCommand Command = Transaction.Connection.CreateCommand();
    Command.CommandText = CommandText;
    Command.CommandType = CommandType;
    if ((Parameters != null))
    {
        foreach (DbParameter Parameter in Parameters)
        {
            Command.Parameters.Add(Parameter);
        }
    }
    return Command;
}
Public Function GetCommand(Transaction As DbTransaction,
                           CommandText As String,
                           Optional Parameters As IEnumerable(Of DbParameter) = Nothing,
                           Optional CommandType As CommandType = System.Data.CommandType.Text) As DbCommand
    If Transaction Is Nothing Then Throw New ArgumentNullException("Transaction is null")
    If String.IsNullOrWhiteSpace(CommandText) Then Throw New ArgumentNullException("CommandText is null")

    Dim Command As DbCommand = Transaction.Connection.CreateCommand()
    Command.CommandText = CommandText
    Command.CommandType = CommandType
    If (Parameters IsNot Nothing) Then
        For Each Parameter As DbParameter In Parameters
            Command.Parameters.Add(Parameter)
        Next
    End If
    Return Command
End Function

现在我们可以像这样一起执行一系列命令

using (DbConnection Connection = GetDbConnection())
{
    Connection.Open();
    using (DbTransaction Transaction = Connection.BeginTransaction())
    {
        try
        {
            using (DbCommand FirstCommand = Transaction.GetCommand_
                 ("INSERT INTO MyTable1 (Column1,Column2) VALUES (1,2)"))
            {
                FirstCommand.ExecuteNonQuery();
            }

            using (DbCommand LastCommand = Transaction.GetCommand_
                 ("INSERT INTO MyTable2 (ColumnA,ColumnB) VALUES ('X','Y')"))
            {
                LastCommand.ExecuteNonQuery();
            }

            Transaction.Commit();
        }
        catch (Exception ex)
        {
            Transaction.Rollback();
            throw;
        }
    }
}
Using Connection As DbConnection = GetDbConnection()
    Connection.Open()
    Using Transaction As DbTransaction = Connection.BeginTransaction()

        Try
            Using FirstCommand As DbCommand = Transaction.GetCommand_
                    ("INSERT INTO MyTable1 (Column1,Column2) VALUES (1,2)")
                FirstCommand.ExecuteNonQuery()
            End Using

            Using LastCommand As DbCommand = Transaction.GetCommand_
                    ("INSERT INTO MyTable2 (ColumnA,ColumnB) VALUES ('X','Y')")
                LastCommand.ExecuteNonQuery()
            End Using

            Transaction.Commit()
        Catch ex As Exception
            Transaction.Rollback()
            Throw
        End Try
    End Using
End Using

如果你想释放所有资源，我看不出还有什么方法可以减少冗余。
另请注意，我没有使用GetNonQuery，因为它会过早关闭连接。
但仍然稍微干净一些。

那DbParameter呢？

该死。
使用DbCommand这里有一个大问题。DbCommand和DbParameter都是abstract类。
因此，由于无法从用法推断出DbParameter实例的类型，我们必须指定它，或者使用Command.CreateParameter。

DbCommand Command = GetDbConnection().GetCommand("MyCommandText");
DbParameter Parameter = Command.CreateParameter();
Parameter.ParameterName = "PName";
Parameter.Value = 5;
Command.Parameters.Add(Parameter);
Command.GetNonQuery();
Dim Command as DbCommand = GetDbConnection.GetCommand("MyCommandText")
Dim Parameter as DbParameter = Command.CreateParameter
Parameter.ParameterName = "PName"
Parameter.Value = 5
Command.Parameters.Add(Parameter)
Command.GetNonQuery()

这完全不是我想要的。非常啰嗦。

那么指定类型呢？让我们为GetCommand添加一个重载。

SqlParameter[] Parameters = { new SqlParameter { ParameterName = "P1",Value = 2 } };
DbCommand Command = GetSQLConnection().GetCommand("MyCommandText", Parameters);
Dim Parameters As SqlParameter() = {New SqlParameter With {.ParameterName = "P1", .Value = 2}}
Dim Command As DbCommand = GetSQLConnection.GetCommand("MyCommandText", Parameters)

这可以正常工作，但仅限于DbCommand确实是SqlCommand类型。
因此，使用此方法，很容易产生在应用程序生命周期后期可能导致错误的 bug。这也很不利于使用DbProviderFactory的初衷。

因此，我不得不为参数也创建一个包装器。
这里有一个例子，有几个重载。

public static DbCommand AddDbParameter
  (this DbCommand Command, string ParameterName, DbType DbType, object Value)
{
    DbParameter Parameter = Command.CreateParameter();
    Parameter.ParameterName = ParameterName;
    Parameter.DbType = DbType;
    Parameter.Value = Value;
    Command.Parameters.Add(Parameter);
    return Command;
}
<extension>
Function AddDbParameter(Command As DbCommand, ParameterName As String, DbType As DbType, Value As Object) As DbCommand
    Dim Parameter As DbParameter = Command.CreateParameter()
    Parameter.ParameterName = ParameterName
    Parameter.DbType = DbType
    Parameter.Value = Value
    Command.Parameters.Add(Parameter)
    Return Command
End Function</extension>

当然，这种方法有一些缺点。
在我看来，最大的缺点是参数只是位置参数，而不是命名参数。
您也不能使用数据库特定的类型。

因此，在DbProviderFactory不足以满足需求的情况下，我还创建了SqlAccess和OracleAccess类。它们也可以下载。
而且很容易将其扩展到其他提供程序。

我们还能做什么来简化？

是的，为什么不添加一些函数来跳过GetCommand。
像这样

    public static DbDataReader GetDataReader(this DbConnection Connection, 
        string CommandText, 
        CommandType CommandType = System.Data.CommandType.Text, 
        IEnumerable<DbParameter> Parameters = null)
    {
        DbCommand Command = GetCommand(Connection, CommandText, CommandType, Parameters);
        return GetDataReader(Command);
    }
<Extension>Public Function GetDataReader(Connection As DbConnection,
                              CommandText As String,
                              Optional CommandType As CommandType = System.Data.CommandType.Text,
                              Optional Parameters As IEnumerable(Of DbParameter) = Nothing) _
                                     As DbDataReader
    Dim Command As DbCommand = GetCommand(Connection, CommandText, CommandType, Parameters)
    Return GetDataReader(Command)
End Function

可以使用像这样

MyDB.GetConnection.GetDataReader("Select * From MyTable");
MyDB.GetDataReader("Select * From MyTable")

GetScalar和GetNonQuery也添加了类似的函数。

关注点

请记住，GetConnection()会获取它在配置文件中找到的第一个可用ConnectionString。
如果您有本地安装的 SqlServer，第一个connectionstring可能是Machine.Config中的“LocalSqlServer”。

本文中的代码也与我另一篇文章中的代码结合使用效果很好，用于 DataReaders 的属性映射扩展[^] 。
像这样一起使用它们

MyDB.GetConnection.GetDataReader("Select * From MyTable").AsEnumerable<MyClass>();
MyDB.GetDataReader("Select * From MyTable").AsEnumerable(of MyClass)

或

MyDB.GetConnection.GetDataReader("Select * From MyTable").ToList<MyClass>();
MyDB.GetDataReader("Select * From MyTable").ToList(of MyClass)

历史

  • 2014 年 10 月 6 日：v1.0 首次发布
  • 2014 年 2 月 18 日：v1.1 将大量重载整理为可选参数的方法。添加了GetScalar<T>
  • 2016 年 2 月 12 日：v1.2 添加了参数处理和GetDataTable
© . All rights reserved.