架构最佳实践 - 自动化 SQL 参数创建和赋值

• 下载源代码 - 11.8 KB

引言

大多数使用存储过程的系统都会利用 SQL 参数来发送和接收来自数据库的信息。创建这些 SQL 参数然后填充它们的代码在系统中会重复出现。本文将介绍如何自动化这个对开发者来说看似枯燥但又十分重要的编码任务。

背景

大多数数据驱动的应用程序都利用存储过程及其优势。随着表数量的增加，存储过程的数量也会随之增加。有些过程甚至可能有超过 10 个参数。由于创建和填充它们是始终需要的，所以我们最终会得到大量只做这件事的代码。更糟糕的是，通过将一组参数复制到另一组中，却忘记更改数据类型等，从而导致了复制粘贴错误。开发者应该从执行这种代码重复中解放出来。

本文解决了这个问题，主要面向开发应用程序框架的架构师或技术负责人。

本文使用 SQL Server 的 SqlParameters，示例是一个 Web 应用程序，但这些类也可以用于 Windows 应用程序。SqlParameters 的等价物是 OleDbParameter，并且可以轻松地移植到其他数据源。

Using the Code

这些代码可以方便地用于实现 N 层架构的系统的业务逻辑层。SqlParametersCodeGen 类与 AppConfiguration 类（已附加源代码）一起，可以用于获取参数，或获取参数并为其赋值。这些应该成为框架核心模块的一部分。

参数配置

为了实现这一点，我们需要创建一个 XML 配置文件，它将告诉我们存在的不同参数数组集。我们将配置命名为 .config，以利用 ASP.NET 处理程序不提供此类文件请求的优势。

<SqlParameterItems>
    <!-- Start of Exception Manager -->
    <SqlParameterItem id="ExceptionManager_ExceptionLog_Retrive_ParametersArray">
        <SqlParameters>
            <SqlParameter name="@ExceptionID" type="Int" />
            <SqlParameter name="@ExceptionSource" type="VarChar" size="15" />
        </SqlParameters>
    </SqlParameterItem>
    <!-- End of Exception Manager -->
    <!-- Start of Account Manager -->
    <SqlParameterItem id="AccountManager_Users_Create_ParametersArray">
        <SqlParameters>
            <SqlParameter name="@Username" type="VarChar" size="50" />
            <SqlParameter name="@Password" type="VarChar" size="50" />
        </SqlParameters>
    </SqlParameterItem>
    <!-- End of Account Manager -->
</SqlParameterItems>

ID 通常是存储过程中使用的名称，后面加上 ParametersArray。这可以取决于您的命名约定。对于 VarChar 和 Char 等，大小是一个必需字段，但对于不需要指定大小的数据类型则不是必需的。类型名称应与 SqlDBType 中的名称相同。有关其他类型的命名约定，请参阅 SqlDBType。

生成此配置的可能性很多。您可以手动添加它，或者让一个生成器在启动时读取数据库中的所有存储过程并生成它，等等。您也可以在输入配置文件中的值后生成简单的存储过程。

可以使用参数数组来处理具有相同签名的多个存储过程，但要小心，原始作者不要更改签名，否则会破坏重用。

关于 OUTPUT 参数，我很抱歉，我并不特别喜欢它们，我更倾向于在行中选择值。如果需要，您可以对此进行自定义。

Web.Config 或 App.Config 条目

既然我们已经声明了不同的参数数组集，现在需要让应用程序能够访问此文件。我们通过在上述任一文件中添加一个条目来实现此操作。在此，我使用了 Web.Config。

<appSettings>
    <!-- Start of SqlParameter Items -->
    <!-- live mode value -->
    <add key="SqlParameterItems_SettingsFile" 
      value="C:\Inetpub\wwwroot\YourWebSitePath\Config\SqlParameters.config"/>
    <!-- quality assurance mode-->
    <add key="SqlParameterItems_SettingsFile_QA" 
      value="C:\Inetpub\wwwroot\YourWebSitePath\Config\SqlParameters.config"/>
    <!-- dev mode value -->
    <add key="SqlParameterItems_SettingsFile_Dev" 
      value="C:\YourWebSitePath\Config\SqlParameters.config"/>
    <!-- End of SqlParameter Items -->
</appSettings>

AppConfiguration 处理此类条目，有关说明可以在我之前的 文章 中找到。

SQL 参数代码生成器

设置完成后，就可以使用 SqlParametersCodeGen。此类基本上会加载 XML 配置，并将其缓存到 ASP.NET 缓存中，如果文件存在，则具有文件依赖关系（以便也可用于 Windows 应用程序；我们可以自定义此缓存逻辑）。当请求由其键标识的参数数组时，它会加载那些节点并返回参数集合。如果还需要赋值，它会创建参数数组，然后从 Hashtable 或 DataRow 输入中赋值。上述 AccountManager_Users_Create_ParametersArray 的各种用法包括：

用于获取 SQL 参数数组集合

SqlParameter[] parUser = 
  SqlParametersCodeGen.GetParameters(
  "AccountManager_Users_Create_ParametersArray");

parUser[0].Value = username;
parUser[1].Value = password;

用于使用 Hashtable 为 SQL 参数数组赋值。Hashtable 键应与参数名称匹配，不带 @ 符号。

Hashtable data = null;

data = new Hashtable();
data.Add("Username", txtUsername.Text);
data.Add("Password", txtPassword.Text);

SqlParameter[] parUser = 
  SqlParametersCodeGen.AssignParameters(
  "AccountManager_Users_Create_ParametersArray", data);

用于使用 DataRow 为 SQL 参数数组赋值

SqlParameter[] parUser = null;
DataRow userDataRow = null;

userDataRow = data.Tables["User"].Rows[0];

parUser = SqlParametersCodeGen.AssignParameters(
          "AccountManager_Users_Create_ParametersArray", userDataRow);

注意事项

• 目前，我们只缓存了配置文件以减少文件 IO。SqlParameter[] 的缓存是可能的，前提是缓存能够提供现有集合的深拷贝。ASP.NET 自带的缓存提供的是实时副本。由于单个 SqlParameter[] 不能分配给两个 SqlCommand 对象，因此无法使用此缓存。您需要实现一个缓存框架，您可以在尝试缓存 SqlParameter[] 之前指定是否需要引用的副本。这需要另写一篇文章。
• 我已为 SQL Server 中常用的数据类型提供了支持。如果您需要其他数据类型，请编辑 SqlParametersCodeGen.cs 以添加您的类型。您需要添加两个方法：private static SqlParameter[] GenerateParameters(XmlNode sqlParameterItemRoot) 和 private static SqlDbType GetSqlDBType(string columnType)。
• 方法被设为 static 是因为它们始终由整个应用程序使用，并提供更快、线程安全的访问。

历史

• 2007 年 7 月 18 日：初始版本。