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

扩展 IDbConnection 并从 Dapper 获取帮助处理繁琐任务

Darek Danielewski
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.53/5 (7投票s)

2014年3月27日

CPOL

8分钟阅读

viewsIcon

42380

借助C#扩展方法和Dapper，可以轻松地简化代码化的数据库交互，即使是加密的列。

引言

我偶尔会遇到编写重复代码的情况。由于我喜欢遵循DRY原则，我很快就会为各种任务创建（或复制粘贴）一些扩展。本文将演示如何创建一个通用的log4net扩展，以及通用且安全的数据库扩展，以简化和优化与数据库的代码化交互。

背景

读者应具备对C#扩展方法、log4net以及Dapper微ORM库的良好理解。

SQL 脚本

为了准备本次练习，请创建所需的证书和包含安全数据的表。

证书

--If there is no master key, create one now. 
IF NOT EXISTS 
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY 
    PASSWORD = '383927hxJKL969#gif0%34467GRkjl5k3fd117r$$#1946kcj$n44nhdlj'
GO
CREATE CERTIFICATE HumanResources037
   WITH SUBJECT = 'Employee Social Security Numbers';
GO

加密密钥

CREATE SYMMETRIC KEY SSN_Key_01
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE HumanResources037;
GO

表格

CREATE TABLE [dbo].[Employees](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
    [SSN] [varbinary](128) NOT NULL
) ON [PRIMARY]

使用代码

当我第一次发现Dapper时，我很快就意识到微ORM在处理中等复杂度的基本任务时的强大功能。对于更复杂的设计，我仍然使用Entity Framework，但最近我对其新版本中持续的性能下降感到失望。Dapper易于使用，开发速度快，但仍然需要一定程度的样板代码来实例化和释放连接。我非常赞成分离关注点，由于我不想一遍又一遍地重复相同的代码，所以我决定为我经常需要执行的工作编写一些扩展。

让我们先从基础扩展开始。

public static TReturn Using<T, TReturn>(this T client, Func<T, TReturn> work)
    where T : IDbConnection
{ 
    try
    {
        client.Open();
        TReturn o = work(client);
        client.Close();
        return o;
    }
    catch (TimeoutException e)
    {
        client.Log().Warn(e);
        throw;
    }
    catch (SqlException e)
    {
        client.Log().Error(e);
        throw;
    }
    catch (Exception e)
    {
        client.Log().Fatal(e);
        throw;
    }
}

该扩展的建模方式与我用于WCF客户端的类似概念。在这种情况下，依赖于客户端可能处于的状态不是一个好主意。同样，对于IDbConnection，我通常从Parallel Extensions Extras提供的ConnectionPool<SqlConnection>中获取它。

static readonly SqlConnectionStringBuilder Scsb = 
  new SqlConnectionStringBuilder(){
    DataSource = "localhost", 
    InitialCatalog = "Scratch", 
    IntegratedSecurity = true};

static readonly ObjectPool<SqlConnection> ConnectionPool = 
  new ObjectPool<SqlConnection>(
    ()=>new SqlConnection(Scsb.ConnectionString));

此扩展方法允许我在实现IDbConnection接口的任何数据库连接对象上执行任何Func<TReturn>。它将负责在执行工作之前打开连接，并在Func<>体中的每一行完成后立即关闭连接。这允许我使用一个SqlConnection组合多个Dapper调用，但这并不意味着此类调用将由事务管理（尽管很容易实现）。它还将使用Log4Net库记录任何警告和错误消息，并为T类型（即SqlConnection）配置记录器。

由于我的数据库交互由另一个try/catch块管理，因此只有在调用成功并且连接处于关闭状态时，我才会将连接返回到池中。否则，失败的对象将被丢弃。

try {                
    var connection = ConnectionPool.GetObject();
    connection.Execute( /* ... */ );
    ConnectionPool.PutObject(connection);
} catch (Exception ex) {
    typeof(Program).Log().Error("While populating data", ex);
}

我意识到这可能有点小题大做，因为与ICommunicationObject相比，IDbConnection永远不应该处于故障状态（技术上讲，它没有状态）。

Log()方法是另一个扩展，它返回一个实现ILog接口的对象，并且可以用于任何对象，以自动选择为特定类型配置的记录器。

using log4net;
public static class LogExtensions
{
    #region Public Methods and Operators
    public static ILog Log<T>(this T t) { return LogManager.GetLogger(typeof(T)); }
    #endregion
}

要将新的数据库扩展与Dapper一起使用，我只需为其传递一个新的连接对象，并使用Dapper的Query<T>扩展方法。在下面的示例中，我从数据库中检索多个Person记录。

var scsb = new SqlConnectionStringBuilder
           {
               DataSource = "SERVER",
               InitialCatalog = "DATABASE",
               IntegratedSecurity = true
           };
const string SelectEmployees = "SELECT TOP 10 [Id], CONVERT(NCHAR(11), DECRYPTBYKEY(SSN)) AS [SSN] FROM dbo.Employees;";
List<Person> result1 = new SqlConnection(scsb.ConnectionString).Using(c => c.Query<Person>(SelectEmployees)).ToList();

Dapper会将从数据库检索到的所有列自动映射到名称匹配的类属性。还可以使用属性进行映射，这超出了本文的范围。Dapper会将没有对应列的属性保留其默认值。

此时，值得一提的是，Microsoft的SQL Server支持几种保护数据的方法。

  • 对称密钥
  • 非对称密钥
  • 证书
  • 透明数据加密 (TDE)

但是，您必须认识到数据仅在数据库中加密，并且在到达传输层之前会被解密。换句话说，传输中的数据以明文模式发送。如果您需要更高的安全性，则必须诉诸使用SSL证书的通信通道加密。具有加密列可以防止数据库或其备份被脱机到独立服务器，以及其他类似的攻击。

没有一种算法对所有情况都是理想的，并且有关每种算法优缺点的指导超出了本文或SQL Server联机丛书的范围。但是，以下一般原则适用：

  • 强加密通常比弱加密消耗更多的CPU资源。
  • 长密钥通常比短密钥提供更强的加密。
  • 非对称加密比相同密钥长度的对称加密弱，但相对较慢。
  • 具有长密钥的分组密码比流密码更强。
  • 长而复杂的密码比短密码更强。
  • 如果您要加密大量数据，则应使用对称密钥加密数据，然后使用非对称密钥加密对称密钥。
  • 加密数据无法压缩，但压缩数据可以加密。如果使用压缩，则应在加密数据之前对其进行压缩。

鸣谢：Microsoft SQL Server联机丛书

在上面的示例中，我检索的是ID列，这是一个普通的Identity列，以及一个加密列，社会安全号码，它已通过数据库生成的对称密钥进行保护。如果您尝试按原样执行此代码，则ID列将按请求返回，但对于所有记录，SSN列将为NULL。原因是在解密列之前，必须打开对其进行保护的证书。

使用Microsoft在此主题上的最佳实践中的示例，命令将如下所示：

OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037;

完成查询后，建议使用此命令关闭证书。

CLOSE SYMMETRIC KEY SSN_KEY_01;

这个要求促使我创建了另一个扩展方法，该方法可用于检索安全记录，而无需为每个安全操作指定SQL前缀和后缀。

public static TReturn UsingSecure<T, TReturn>(this T client, Func<T, TReturn> work, string keyName, string certificateName)
    where T : IDbConnection
{
    try
    {
        client.Open();
        using (IDbCommand c = client.CreateCommand())
        {
            c.CommandText = String.Format("OPEN SYMMETRIC KEY {0} DECRYPTION BY CERTIFICATE {1};", keyName, certificateName);
            c.ExecuteNonQuery();
        }
        TReturn o = work(client);
        using (IDbCommand c = client.CreateCommand())
        {
            c.CommandText = String.Format("CLOSE SYMMETRIC KEY {0};", keyName);
            c.ExecuteNonQuery();
        }
        client.Close();
        return o;
    }
    catch (TimeoutException e)
    {
        client.Log().Warn(e);
        throw;
    }
    catch (SqlException e)
    {
        client.Log().Error(e);
        throw;
    }
    catch (Exception e)
    {
        client.Log().Fatal(e);
        throw;
    }
}

第一个扩展方法与其安全版本之间的区别在于增加了用于安全交互的密钥和证书的名称。扩展方法创建IDbCommand对象，在调用实际SQL语句之前和之后构造必要的SQL命令并执行它们。现在，我可以请求执行安全查询，同时正确地打开和关闭用于加密的对称密钥。

List<Person> result2 = new SqlConnection(scsb.ConnectionString).UsingSecure(c => c.Query<Person>(SelectEmployees), "SSN_Key_01", "HumanResources037").ToList();

这次，ID列和SSN列将包含数据。

为了支持执行不返回数据的SQL命令（例如UPDATE或存储过程），我创建了另外两个数据库扩展方法。

public static void Using<T>(this T client, Action<T> work) where T : IDbConnection
{
    try
    {
        client.Open();
        work(client);
        client.Close();
    }
    catch (TimeoutException e)
    {
        client.Log().Warn(e);
        throw;
    }
    catch (SqlException e)
    {
        client.Log().Error(e);
        throw;
    }
    catch (Exception e)
    {
        client.Log().Fatal(e);
        throw;
    }
}

public static void UsingSecure<T>(this T client, Action<T> work, string keyName, string certificateName) where T : IDbConnection
{
    try
    {
        client.Open();
        using (IDbCommand c = client.CreateCommand())
        {
            c.CommandText = String.Format("OPEN SYMMETRIC KEY {0} DECRYPTION BY CERTIFICATE {1};", keyName, certificateName);
            c.ExecuteNonQuery();
        }
        work(client);
        using (IDbCommand c = client.CreateCommand())
        {
            c.CommandText = String.Format("CLOSE SYMMETRIC KEY {0};", keyName);
            c.ExecuteNonQuery();
        }
        client.Close();
    }
    catch (TimeoutException e)
    {
        client.Log().Warn(e);
        throw;
    }
    catch (SqlException e)
    {
        client.Log().Error(e);
        throw;
    }
    catch (Exception e)
    {
        client.Log().Fatal(e);
        throw;
    }
}

现在，我可以轻松地查询和更新安全数据，只需很少的精力。

const string selectWithId = "SELECT CONVERT(NCHAR(11), DECRYPTBYKEY(SSN)) AS [SSN] FROM dbo.Employees WHERE [Id]=@id";

var r = new Random();
string newSSN = String.Format("{0:000}-{1:00}-{2:0000}", r.Next(100, 999), r.Next(99), r.Next(9999));

string oldSSN =
    new SqlConnection(scsb.ConnectionString).UsingSecure(
        c => c.Query<string>(
            selectWithId,
            new
            {
                id = 1
            }),
        "SSN_Key_01",
        "HumanResources037").First();

new SqlConnection(scsb.ConnectionString).UsingSecure(
    c => c.Execute(
        "UPDATE dbo.Employees SET SSN = EncryptByKey(Key_GUID('SSN_Key_01'),@ssn) WHERE [Id]=@id;",
        new
        {
            id = 1,
            ssn = newSSN
        }),
    "SSN_Key_01",
    "HumanResources037");

string updSSN =
    new SqlConnection(scsb.ConnectionString).UsingSecure(
        c => c.Query<string>(
            selectWithId,
            new
            {
                id = 1
            }),
        "SSN_Key_01",
        "HumanResources037").First();

我首先创建一个随机的SSN，以便测试更新是否会正确执行。然后，我检索ID为1的记录的旧值，以便比较新旧值。接下来，我执行一个更新语句，最后检索同一条记录的新值。

当然，您不必每次要使用Dapper或我的数据库扩展时都创建一个新连接。相同的代码示例可以重写如下：

new SqlConnection(scsb.ConnectionString).UsingSecure(
        c => 
  {

    string oldSSN = c.Query<string>(
            selectWithId,
            new
            {
                id = 1
            }).First();

    c.Execute(
        "UPDATE dbo.Employees SET SSN = EncryptByKey(Key_GUID('SSN_Key_01'),@ssn) WHERE [Id]=@id;",
        new
        {
            id = 1,
            ssn = newSSN
        });

    string updSSN = c.Query<string>(
            selectWithId,
            new
            {
                id = 1
            }).First();

  }, "SSN_Key_01", "HumanResources037");

需要注意的是，Dapper会根据名称将匿名类属性映射到SQL参数。Dapper还会缓存它执行的所有查询，因此映射过程（无论是将参数还是结果映射到POCO类）仅在第一次执行时会产生性能开销。IMHO，这个微ORM在我的日常工作中节省了大量工作，这给我留下了深刻的印象。

您可能会发现其他有用的扩展：

public static IDbTransaction NewTransaction(this SqlConnection connection)
{
    var transactionId = Guid.NewGuid().ToString().Replace("-", string.Empty);
    return connection.BeginTransaction(transactionId);
}

此扩展在using块内的任何位置提供唯一命名的事务。

try
{
    var connection = ConnectionPool.GetObject();
    connection.UsingSecure(c =>
    {
        var transaction = c.NewTransaction();
        try
        {
            var randomSSN = String.Format("{0:000}-{1:00}-{2:0000}", random.Next(100, 1000),
            random.Next(100),
            random.Next(10000));
        c.Execute(
            "INSERT INTO dbo.Employees ([FirstName],[LastName],[SSN]) VALUES (@firstName, @lastName, EncryptByKey(Key_GUID('SSN_Key_01'),@ssn) )",
            new
            {
                firstName = "John",
                lastName = "Smith",
                ssn = randomSSN                                    
            }, transaction);
        transaction.Commit();
        }
        catch(Exception te)
        {
            transaction.Rollback();
        }
    }, "SSN_Key_01",
        "HumanResources037");
    ConnectionPool.PutObject(connection);
}
catch (Exception ex)
{
    typeof(Program).Log().Error("While populating data", ex);
}

我可以根据逻辑需要自由提交和回滚事务。

如果您想要更多地控制事务隔离级别，可以使用此扩展代替。

public static IDbTransaction NewTransaction(this SqlConnection connection, IsolationLevel isolationLevel = IsolationLevel.Serializable)
{
    var transactionId = Guid.NewGuid().ToString().Replace("-", string.Empty);
    return connection.BeginTransaction(isolationLevel,transactionId);
}

或者，如果您始终偏好快照隔离，它将大大减少阻塞和死锁。

public static IDbTransaction NewSnapshotTransaction(this SqlConnection connection)
{
    var transactionId = Guid.NewGuid().ToString().Replace("-", string.Empty);
    return connection.BeginTransaction(IsolationLevel.Snapshot, transactionId);
}

另一个扩展，我只在最简单的情况下使用，它返回@@IDENTITY值。

public static T GetIdentity<T>(this IDbConnection connection, IDbTransaction transaction = null)
{
    using (var command = connection.CreateCommand())
    {
        command.Transaction = transaction;
        command.CommandText = "SELECT @@IDENTITY";
        var retval = command.ExecuteScalar();
        return (T)Convert.ChangeType(retval,typeof(T));
    }
}

在您急于使用它之前，我强烈建议您查阅Microsoft关于@@IDENTITY、SCOPE_IDENTITY和IDENT_SCOPE之间差异的文档。根据您的具体场景和数据库内容，您可能不会获得符合逻辑预期的结果……

但是不用担心，Dapper和SQL Server会再次为您提供帮助。您只需要在语句中包含OUTPUT子句。让我们创建一个新表来试验。

CREATE TABLE [dbo].[T1](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [C1] [nchar](10) NULL
) ON [PRIMARY]

然后，为了插入一条新记录并获取其标识列值，我将SQL修改如下：

var identity = connection.Query<long>(
    "INSERT INTO dbo.T1 ([C1]) OUTPUT INSERTED.Id VALUES('a')").First();

这使我能够100%保证返回的值是新插入记录的标识，即使我从多个线程和位置执行它，而其他方法则无法提供这种保证。

  • @@IDENTIY可以被触发器更改。
  • SCOPE_IDENTITY仅在“模块”（例如，存储过程、批处理等）内正确工作。
  • IDENT_SCOPE不受范围或会话的限制。

摘要

Dapper结合一些扩展方法，真正节省了时间和精力。您可以将本文提供的代码作为基础，创建高度安全的SQL Server或其他实现IDbConnection接口的数据库服务器的代码化交互。由于证书和密钥的名称可以由任何ASCII文本组成，并且存储在加密的配置文件中，因此这将形成一个环境，在该环境中，试图访问高度敏感数据的攻击者将难以渗透。

© . All rights reserved.