SqlProcedure - 提升数据库性能，消除错误并减少代码

• 下载工具包源代码 - 17.0 KB
• 下载源代码 - 114.1 KB
• 下载演示项目 - 330.3 KB

目录

• 摘要
• 背景
  • 数据访问层 (DAL) 的陷阱
• 解决方案
• 工具包
• 流程
1. 创建存储过程
2. 生成包装器
• 使用 GUI
• 命令行
3. 将 SqlProcedure 包含在您的数据访问层中
• 包装类
• DAL 代码
4. 单元测试

摘要

本文包含一个实用程序，用于生成存储过程的包装器，这些包装器可以提高性能并消除某些运行时错误，以及一个用于包装器单元测试的框架。

背景

如果您正在使用 ORM 框架来管理数据持久性（例如 NHibernate），那么您就不会遇到本文要解决的问题，但如果您的代码拥有手工编写的数据访问层并使用 SqlDataReader 执行存储过程，那么请继续阅读。

数据访问层 (DAL) 的陷阱

手动编写的数据访问层代码是重复且乏味的，因此仅凭这一点就足以自动生成它。但是，如果您遵循了微软的建议，您可能在使用运行时发现存储过程参数（例如，使用他们的 SqlHelper 类）之后再执行每个命令。好吧，参数被缓存了，但是在每个存储过程的第一次使用之前，会有一个额外的数据库查询来获取参数数组。这很可能会对性能产生明显的影响，尤其是在启动时，因为每个存储过程在第一次执行时都会被执行。一些测试表明，去除参数发现查询可以将存储过程首次执行的整体时间缩短约三分之一。

此外，如果存储过程使用的参数与数据访问层准备的参数不匹配，您可能会遇到在编译时找不到的运行时错误。

同样，当存储过程的输出列的数量、名称或类型与数据访问层期望的列不匹配时，也会发生运行时错误。

解决方案

本文提供了一个实用程序，该实用程序可以为存储过程生成包装器，这些包装器可以：

1. 预先编码正确的参数数组（无需运行时发现参数，无需因存储过程/数据访问层不匹配而导致的运行时错误）
2. 预先编码正确的输出列集（无需因存储过程/数据访问层不匹配而导致的运行时错误）
3. 提供强类型属性来设置输入参数和获取输出值
4. 通过封装存储过程执行的基本机制来减少数据访问层代码行数

本文还包括一个框架，用于在生成时使用单元测试来验证包装器是否仍然正确，这些测试通过重新派生包装器属性来验证每个包装器是否与其关联的存储过程匹配。

通过这种方式，带有单元测试的自动化构建可以对您的数据访问层代码相对于给定数据库进行端到端验证。因此，如果您的存储过程包装器通过了单元测试，并且您的数据访问层代码能够针对存储过程包装器进行构建，那么您就知道它将无错误地运行。

工具包

1. SqlProcedureWriter 实用程序 - 使用此 EXE 来编写包装器类
2. SqlProcedure 基类 - 将其包含在您的数据访问层代码中
3. SqlOutputField 类 - 将其包含在您的数据访问层代码中
4. SqlTypeMapper 帮助程序类，用于将 SQL 类型映射到 C# 类型 - 将其包含在您的数据访问层代码中
5. SqlHelper 微软提供的帮助程序类 - 将其包含在您的数据访问层代码中
6. SqlProcedureTestHelper 单元测试帮助程序 - 将其包含在您的单元测试套件中

流程

将 SqlProcedure 集成到您的数据访问层中遵循以下过程：

1. 在数据库中创建存储过程
2. 使用 SqlProcedureWriter 为存储过程创建包装器类
3. 将 SqlProcedure 包装器包含在您的数据访问层源文件中并在您的数据访问层方法中使用它
4. 包含单元测试以通过数据库验证包装器

1. 创建存储过程

随意以您喜欢的方式进行。

2. 生成包装器

我们需要为您选择的存储过程生成包装器。

使用 GUI

1. 运行 SqlProcedureWriter.exe
2. 单击按钮连接到包含您要为其创建包装器的存储过程的数据库
3. 选择包含输出包装器类的文件夹
4. 选择要处理的存储过程，可以是数据库中的所有过程，也可以是手动选择的若干个过程
5. 无论哪种情况，您都可以应用正则表达式过滤器来筛选从数据库读取的存储过程
6. 指定输出类所属的命名空间（这很可能是您数据访问层代码的命名空间
7. 根据需要添加一些 using 命名空间
8. 单击“生成”

命令行

您可以使用以下命令行参数调用 SqlProcedureWriter.exe

-f="folder"	输出文件夹
-n="namespace"	输出类的命名空间
-u="using list"	要在 using 块中包含的命名空间列表（用 ; 分隔）
-r="regex"	应用于过程名称的正则表达式过滤器
-s="server"	要登录的 SQL 服务器名称
-d="database"	要使用的数据库名称
-i	集成身份验证
-user="user"	数据库用户名
-pass="pwd"	数据库密码

例如

SqlProcedureWriter.exe -f="c:\tmp\wrappers" -ns="MyAppNS" 
        -u="System.Data" -s=(local) -d="MyDB" -i 

注意

请注意，为了确定输出字段，会执行存储过程。但是，它是使用所有 NULL 参数执行的，并且在一个永不提交的事务中执行。因此，它不会对您的数据库进行任何更改，但该过程将会被执行。

3. 将 SqlProcedure 集成到您的数据访问层中

现在需要将 SqlProcedure 包装器集成到数据访问层中，并编写数据访问层代码来使用它。

包装类

自动生成的包装器具有输入参数的属性（以 Param_ 开头），以及保存返回字段序数值的属性（以 Ordinal_ 开头）。

还会创建一个轻量级的嵌套类来保存返回记录的数据。它为每个输出字段提供强类型属性。

ToParameterString() 方法对于记录日志或报告异常很有用，因为它提供了存储过程调用的完整详细信息，包括输入参数值。

还有许多其他方法在后台工作，但如果您有兴趣，查看代码可能会更容易。

DAL 代码

典型的数据访问层方法可能如下所示：

public AuthorInfo GetAuthor_OldSkool(string forename, string surname)
{
    AuthorInfo author = null;

    using (SqlConnection connection = 
        new SqlConnection(_ConnectionString))
    {
        SqlParameter[] parameters = 
            SQLHelperParameterCache.GetAndCacheSPParameters
            (connection, "spAuthor_SEL_NAMES");

        parameters[0].Value = forename;
        parameters[1].Value = surname;

        using (SqlDataReader reader = SQLHelper.ExecuteReader
            (connection, CommandType.StoredProcedure,
            "spAuthor_SEL_NAMES", parameters))
        {
            if (reader.HasRows)
            {
                #region Get column information
                int colid       = reader.GetOrdinal("id");
                int colForename = reader.GetOrdinal("Forename");
                int colSurname  = reader.GetOrdinal("Surname");
                #endregion

                if (reader.Read())
                {
                    #region Get data from each row in the database
                    int vid         = reader.GetInt32(colid);
                    string vforename    = reader.GetString(colForename);
                    string vsurname = reader.GetString(colSurname);
                    #endregion

                    author = new AuthorInfo();
                    author.id = vid;
                    author.Forename = vforename;
                    author.Surname = vsurname;
                }
            }
        }
    }

    return author;
}

一旦使用了 SqlProcedure 包装器，它看起来就会像这样：

public AuthorInfo GetAuthor_UsingSqlProcedure
    (string forename, string surname)
{
    AuthorInfo author = null;

    // Create an instance of our SqlProcedure wrapper

    // Use the constructor to provide the input parameters

    Author_SEL_NAMES procedure = new Author_SEL_NAMES(forename, surname);

    // This call sets the values of Value.Forename etc. 

    // to the values returned by the SP

    if (procedure.ExecuteReadValue(connectionString))
    {
        author = new AuthorInfo();
        author.id = procedure.Value.id;
        author.Forename = procedure.Value.Forename;
        author.Surname = procedure.Value.Surname;
    }

    return author;
}

细分一下，使用 SqlProcedure 包装器所需的步骤是：

包含类

您需要将 SqlProcedure 基类、SqlOutputField 和 SqlHelper 包含在您的数据访问层中。

创建实例

每个存储过程都有自己的类，其名称基于存储过程的名称。所有必需的输入参数都可以使用强类型值在类构造函数中提供。

Author_SEL_NAMES procedure = new Author_SEL_NAMES(forename, surname);

设置输入参数

这可以通过构造函数（如上所示）或包装器的属性（两者都是强类型的）来完成。

// An alternative to the above example

Author_SEL_NAMES procedure = new Author_SEL_NAMES();
procedure.Forename = forename;
procedure.Surname = surname;

执行存储过程

您可以调用 SqlProcedure 基类提供的三种方法之一来执行过程，具体取决于您期望返回什么，就像您在使用 SqlHelper 一样。这些方法是：

• ExecuteNonQuery - 无返回数据
• ExecuteScalar - 返回一个标量
• ExecuteReader - 返回一个结果集

ExecuteNonQuery

以通常的方式创建您的连接/事务，然后调用 SqlProcedure 包装器上的 ExecuteNonQuery 方法。没有返回值。

using (SqlConnection connection = new SqlConnection(_ConnectionString))
{
    proceudre.ExecuteNonQuery(connection);
}

ExecuteScalar

如果您期望返回一个标量，那么您可以调用各种 ExecuteScalar 方法之一。如果您期望返回一个 int（例如，返回 @@IDENTITY），则调用 ExecuteScalarInt，否则调用 ExecuteScalar 并自行处理返回的 object。两者都有重载，可以传入 SqlConnection 或 SqlTransaction。

using (SqlConnection connection = new SqlConnection(_ConnectionString))
{
    int newId = procedure.ExecuteScalarInt(connection);
    ...
}

ExecuteReader

如果存储过程执行的是具有多个列的 SELECT ，则调用 ExecuteReadValue 或 ExecuteReadValues，分别取决于您期望返回单行还是多行。

这两种方法都有重载，可以传入连接字符串或连接对象。我假设没有人想在事务中执行 SELECT 。

如果没有返回行，则 ExecuteReadValue(s) 只返回 false。

如果存在数据，则每个返回的记录都由 SqlProcedureWriter 创建的嵌套数据类的实例表示。此类为每个输出字段提供强类型属性。

public class Author_SEL_NAMES_Values
{
    public int id;
    public string Forename;
    public string Surname;
}

对于单行返回，您可以使用包装器的 Value 属性，该属性始终保存最后一条记录

if (procedure.ExecuteReadValue(_ConnectionString))
{
    author = new AuthorInfo();
    author.id = procedure.Value.id;
    author.Forename = procedure.Value.Forename;
    author.Surname = procedure.Value.Surname;
    ...
}

但对于多行，您可以使用 Values() 方法像这样迭代记录数组：

if (procedure.ExecuteReadValues(_ConnectionString))
{
    foreach(Author_SEL_NAMES.Author_SEL_NAMES_Values v in 
        procedure.Values())
    {
        AuthorInfo author = new AuthorInfo();
        author.id = v.id;
        author.Forename = v.Forename;
        author.Surname = v.Surname;
        ...
    }
}

4. 单元测试

您的数据访问层现在将完全可用，但在您的构建过程中包含一个检查包装器是否与数据库中定义的存储过程保持同步的测试是明智的。如果输入参数或输出字段在包装器和数据库中定义的存储过程之间存在任何差异，测试将失败。

包含类

您需要将 SqlProcedureTestHelper 类和 SqlHelper 包含在您的数据访问层中。

编写测试

然后，您可以编写一个 NUnit 测试，该测试会将每个 SqlProcedure 包装器与数据库进行比较。

使用 SqlProcedureTestHelper.GetAllProcedures 方法创建给定程序集中所有继承自 SqlProcedure 的类的实例，然后对于每个实例，调用 SqlProcedureTestHelper.TestSqlProcedure 来执行比较并对输入参数和输出字段的各种属性执行 NUnit 断言。

[Test]
public void TestAllProcedures()
{
    SqlProcedure[] procedures = SqlProcedureTestHelper.GetAllProcedures
        (Assembly.GetAssembly(typeof(SqlProcedure)),
        "MyAssem", typeof(SqlProcedure));
    foreach(SqlProcedure sp in procedures)
    {
        SqlProcedureTestHelper.TestSqlProcedure(myConnectionString, sp);
    }
}

历史

• 2007年11月23日 - 初始发布 (1.0.0)