使用 .NET 数据提供程序的信息
4.80/5 (7投票s)
使用 .NET 数据提供程序的信息来改进动态 SQL 并支持使用多个数据库
引言
.NET 数据提供程序提供了大量关于数据库的信息，因此我想展示一些巧妙的技巧并分享一些代码。
背景
我看到很多使用动态 SQL 的代码，分布在多个语言、多个项目中，并且经常会遇到一些问题：
- 未使用参数，这会影响性能，并且无法防止 SQL 注入。
- 未能正确地转义列名和表名标识符。
- 硬编码数据库服务器。这可能表现为硬编码提供程序，或者依赖于特定数据库服务器的特殊功能。
如果您幸运地成为了数据库的设计者，您可能会简单地避免使用任何看起来像 SQL 关键字的内容，并希望数据库的升级或迁移到新数据库服务器不会破坏您的代码。这种情况非常普遍，因此人们害怕使用显而易见的关键字，例如 user、default、table 和 column。
在某些环境中，您只能“尽力猜测”。因此，如果您正在针对 SQL Server 或 MySQL 进行编码，并选择转义您的 SQL，您最终会像这样转义您的标识符：
SELECT [user], [default] from [table]
这里显而易见的问题是，如果您的数据库迁移到不同的服务器（例如迁移到 Oracle 或 PostgreSQL），此语句将无法正常工作。第一反应是查看标准。SQL92 标准（现已存在 17 年）规定，双引号括起来的标识符应该可以适用于所有符合标准的数据库。
SELECT "user", "default" from "table"
不幸的是，SQL Server 不支持此功能，除非您设置了 Quoted Identifier 选项（在连接、数据库或服务器上）。Microsoft Access 完全不支持引号，而其他数据库的情况则难以确定。
对于打包软件的程序员来说，这些问题通常归结为几个选择：
- 将应用程序硬编码到单个数据库服务器。
- 限制支持的数据库数量，通常少于 3 个。
- 使用类似 (N)Hibernate 的工具，它将 SQL 隐藏在其 自己的语言 之后，因此需要该工具支持您的数据库。（希望您没有使用旧的、全新的或晦涩的数据库，例如 TimesTen。您可以点击 此处 查看支持的数据库列表。）
- 将所有内容都放在存储过程中，这仍然会限制数据库选项。
- 将所有 SQL 放入某个外部资源文件中，可以针对不同数据库进行更改。
这些选项各有优缺点。然而，.NET 数据提供程序可以使其中一些选择更容易一些。因此，就像选择使用 XHTML 而不是 HTML 来使您的网页对更多浏览器可用一样，如果您希望使您的代码对更多数据库开放，您就必须做出一些选择。
就从一个数据库迁移到另一个数据库而言，最灵活的是经过精心编码的动态 SQL。这**不是**说这种方法没有问题，其中一个问题是某些 SQL 更改需要重新部署代码。它确实为我们提供了最多的选项，**如果**操作得当的话。我将在未来的文章中探讨一些最小化此方法问题的方法。因此，编写规则相当简单：
- 尽可能使您的 SQL 接近 SQL92 标准。
- 避免使用特定于服务器的功能。
- 在设计数据库、表等时，假设服务器不区分大小写，即给名称起得唯一。
- 在编写 SQL 时，假设数据库区分大小写；这与上一条规则结合可以避免奇怪的问题。
- 不要硬编码到单个提供程序。
- 妥善转义所有数据库、架构、表和列名。
其中许多只需要自律，但最后两个可能比较困难。因此，我们先来处理第一个。首先，一个硬编码的示例：
string connectionString = ConfigurationManager.AppSettings.Get("DSN");
using(var conn = new System.Data.SqlClient.SqlConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
using(var cmd = new System.Data.SqlClient.SqlCommand())
{
cmd.CommandText = "delete from table1";
cmd.CommandType = System.Data.CommandType.Text;
cmd.ExecuteNonQuery();
}
}
这里遇到的第一个问题是我们从 appSettings 而不是 connectionStrings 区域读取连接字符串。将连接字符串放在 connectionStrings 区域的最大优点是我们现在拥有了一个 providerName。通过获取该提供程序名称，我们可以创建相应类型的对象。
ConnectionStringSettings css = ConfigurationManager.ConnectionStrings["DSN"];
string connectionString = css.ConnectionString;
string providerName = css.ProviderName;
var factory = System.Data.Common.DbProviderFactories.GetFactory(providerName);
using(var conn = factory.CreateConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
// use the connection to create the command, that way its
//already associated properly, though could have used
//factory.CreateCommand()
using(var cmd = conn.CreateCommand())
{
cmd.CommandText = "delete from table1";
cmd.CommandType = System.Data.CommandType.Text;
cmd.ExecuteNonQuery();
}
}
通过以这种方式更改代码，我们消除了大多数代码从一个数据库迁移到另一个数据库时遇到的最大问题：硬编码数据库提供程序。
接下来，正确获取数据源的转义字符。为此，我们需要 CommandBuilder 对象或 DataSourceInformation 表。由于 DataSourceInformation 表包含正则表达式而不是简单字符，因此它更适合验证而不是转义。所以，让我们获取 CommandBuilder，并使用它来正确转义一些 SQL。
DbCommandBuilder commandBuilder = factory.CreateCommandBuilder();
string prefix = commandBuilder.QuotePrefix;
string suffix = commandBuilder.QuoteSuffix;
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("SELECT ");
stringBuilder.AppendFormat("{0}{1}{2}", prefix, "ID", suffix);
stringBuilder.AppendLine(",");
stringBuilder.AppendFormat("{0}{1}{2}", prefix, "user", suffix);
stringBuilder.AppendLine(",");
stringBuilder.AppendFormat("{0}{1}{2}", prefix, "password", suffix);
stringBuilder.AppendLine(",");
stringBuilder.AppendFormat("{0}{1}{2}", prefix, "default", suffix);
stringBuilder.AppendLine("");
stringBuilder.Append("FROM ");
stringBuilder.AppendFormat("{0}{1}{2}", prefix, "table", suffix);
Debug.WriteLine(stringBuilder.ToString());
当使用 System.Data.SqlClient 提供程序连接到 SQL Server 时，此代码的输出是：
SELECT [ID],
[user],
[password],
[default]
FROM [table]
使用 Oracle.DataAccess.Client 或 Npgsql 时，它会生成：
SELECT "ID",
"user",
"password",
"default"
FROM "table"
这方面的主要问题是使用 OLEDB 或 ODBC 提供程序时。因为 DbCommandBuilder 不知道您正在连接哪个数据库，所以您必须查询该提供程序。恰巧，获取该信息的最佳地点与我们需要获取参数功能的地方相同。所以，让我们去获取那些数据。
System.Data.Common.DbMetaDataCollectionNames 类提供了一些常量字符串，我们可以将它们传递给 DataTable 以获取提供信息的数据表。
using (var conn = factory.CreateConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
DataTable dt = conn.GetSchema(
System.Data.Common.DbMetaDataCollectionNames.DataSourceInformation);
return dt;
}
我个人不喜欢编写解析代码，并且因为我计划使用这些数据，所以我决定创建一个类会更有帮助。我传入我们刚刚检索到的 DataTable，然后下面的类会自行填充，使得数据更容易获取。
internal class DataSourceInformation
{
private static readonly Type _Type = typeof(DataSourceInformation);
private static readonly Type _IdentifierCaseType =
Enum.GetUnderlyingType(typeof(IdentifierCase));
private static readonly Type _GroupByBehaviorType =
Enum.GetUnderlyingType(typeof(GroupByBehavior));
private static readonly Type _SupportedJoinOperatorsType =
Enum.GetUnderlyingType(typeof(SupportedJoinOperators));
//These are filled within the "switch/case"
//statement, either directly, or thru reflection.
//since Resharper can't tell they are being filled
//thru reflection, it suggests to convert them to
//constants. DO NOT do that!!!!!
// ReSharper disable ConvertToConstant.Local
private readonly string _compositeIdentifierSeparatorPattern = string.Empty;
private readonly string _dataSourceProductName = string.Empty;
private readonly string _dataSourceProductVersion = string.Empty;
private readonly string _dataSourceProductVersionNormalized = string.Empty;
private readonly GroupByBehavior _groupByBehavior;
private readonly string _identifierPattern = string.Empty;
private readonly IdentifierCase _identifierCase;
private readonly bool _orderByColumnsInSelect = false;
private readonly string _parameterMarkerFormat = string.Empty;
private readonly string _parameterMarkerPattern = string.Empty;
private readonly Int32 _parameterNameMaxLength = 0;
private readonly string _parameterNamePattern = string.Empty;
private readonly string _quotedIdentifierPattern = string.Empty;
private readonly Regex _quotedIdentifierCase;
private readonly string _statementSeparatorPattern = string.Empty;
private readonly Regex _stringLiteralPattern;
private readonly SupportedJoinOperators _supportedJoinOperators;
// ReSharper restore ConvertToConstant.Local
public DataSourceInformation(DataTable dt)
{
//DataTable dt = Connection.GetSchema(
// DbMetaDataCollectionNames.DataSourceInformation);
foreach (DataRow r in dt.Rows)
{
foreach (DataColumn c in dt.Columns)
{
string s = c.ColumnName;
object o = r[c.ColumnName];
//just for safety
if (o == DBNull.Value)
{
o = null;
}
if (!string.IsNullOrEmpty(s) && o != null)
{
switch (s)
{
case "QuotedIdentifierCase":
_quotedIdentifierCase = new Regex(o.ToString());
break;
case "StringLiteralPattern":
_stringLiteralPattern = new Regex(o.ToString());
break;
case "GroupByBehavior":
o = Convert.ChangeType(o, _GroupByBehaviorType);
_groupByBehavior = (GroupByBehavior)o;
break;
case "IdentifierCase":
o = Convert.ChangeType(o, _IdentifierCaseType);
_identifierCase = (IdentifierCase)o;
break;
case "SupportedJoinOperators":
o = Convert.ChangeType(o, _SupportedJoinOperatorsType);
_supportedJoinOperators = (SupportedJoinOperators)o;
// (o as SupportedJoinOperators?) ??
// SupportedJoinOperators.None;
break;
default:
FieldInfo fi = _Type.GetField("_" + s,
BindingFlags.IgnoreCase | BindingFlags.NonPublic |
BindingFlags.Instance);
if (fi != null)
{
fi.SetValue(this, o);
}
break;
}
}
}
//there should only ever be a single row.
break;
}
}
public string CompositeIdentifierSeparatorPattern
{
get { return _compositeIdentifierSeparatorPattern; }
}
public string DataSourceProductName
{
get { return _dataSourceProductName; }
}
public string DataSourceProductVersion
{
get { return _dataSourceProductVersion; }
}
public string DataSourceProductVersionNormalized
{
get { return _dataSourceProductVersionNormalized; }
}
public GroupByBehavior GroupByBehavior
{
get { return _groupByBehavior; }
}
public string IdentifierPattern
{
get { return _identifierPattern; }
}
public IdentifierCase IdentifierCase
{
get { return _identifierCase; }
}
public bool OrderByColumnsInSelect
{
get { return _orderByColumnsInSelect; }
}
public string ParameterMarkerFormat
{
get { return _parameterMarkerFormat; }
}
public string ParameterMarkerPattern
{
get { return _parameterMarkerPattern; }
}
public int ParameterNameMaxLength
{
get { return _parameterNameMaxLength; }
}
public string ParameterNamePattern
{
get { return _parameterNamePattern; }
}
public string QuotedIdentifierPattern
{
get { return _quotedIdentifierPattern; }
}
public Regex QuotedIdentifierCase
{
get { return _quotedIdentifierCase; }
}
public string StatementSeparatorPattern
{
get { return _statementSeparatorPattern; }
}
public Regex StringLiteralPattern
{
get { return _stringLiteralPattern; }
}
public SupportedJoinOperators SupportedJoinOperators
{
get { return _supportedJoinOperators; }
}
}
不进行过多解释，这个类会获取 DataTable 并通过反射自行填充。有一些例外情况，在 switch 语句中进行了展示。如果我们填充这个类并保留它，我们就有很大一部分帮助来为每个单独的数据库构建 SQL。这些数据包括但不限于：
- 用于转义标识符的字符
- 数据库是否支持命名参数
- 表示命名参数的字符是什么
- 命名参数的最大长度
命名参数非常棒。它们使您能够编写如下 SQL：
DELETE FROM table1 WHERE ID=@ID and column2=@column2
这比处理起来要容易得多，例如：
DELETE FROM table1 WHERE ID=? and column2=?
对许多人来说，后者根本不清楚发生了什么。命名参数显然使事情更清晰，并且您可以重用这些值。
SELECT a.* FROM table1 a
INNER JOIN table2 b
ON a.ID = b.A_ID
WHERE a.NAME = @name
AND b.SOMEOTHERCOLUMN = @name
这里有一些问题，除了我没有转义任何表或列名之外。通过使用命名参数，我已经大大减少了我的应用程序可以支持的数据库数量。我知道的只有三个：MS SQL Server、Oracle 和 PostgreSQL。我不知道 MySQL 是否可以；如果现在不行，将来可能会。这意味着使用命名参数仅仅是一种便利。这也意味着我们无法利用重用参数的能力。另一个需要注意的事情是名称的长度差异很大；SQL Server 支持最多 128 个字符的名称，而 Oracle 只支持最多 15 个字符的名称。我将在下一篇文章中回到这一点。
Using the Code
此时，我们已经具备了所有必要的组件，因此让我们看看这些代码如何组合成一个块，生成一个可能适用于 .NET 中任何数据库提供程序的语句。在另一篇文章中，我将讨论如何使代码更简洁，并解决 OLEDB 和 ODBC 提供程序的功能不足的问题。
ConnectionStringSettings css =
ConfigurationManager.ConnectionStrings["DSN"];
string connectionString = css.ConnectionString;
string providerName = css.ProviderName;
DataSourceInformation dsi = null;
DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
using (var conn = factory.CreateConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
dsi = new DataSourceInformation(conn.GetSchema(
DbMetaDataCollectionNames.DataSourceInformation));
}
DbCommandBuilder commandBuilder = factory.CreateCommandBuilder();
string prefix = commandBuilder.QuotePrefix;
string suffix = commandBuilder.QuoteSuffix;
var parameter = factory.CreateParameter();
parameter.DbType = DbType.Int32;
parameter.Value = 10;
parameter.ParameterName = "ID";
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("SELECT ");
stringBuilder.AppendFormat("{0}{1}{2}", prefix,
"ID", suffix);
stringBuilder.AppendLine(",");
stringBuilder.AppendFormat("{0}{1}{2}", prefix,
"user", suffix);
stringBuilder.AppendLine(",");
stringBuilder.AppendFormat("{0}{1}{2}", prefix,
"password", suffix);
stringBuilder.AppendLine(",");
stringBuilder.AppendFormat("{0}{1}{2}", prefix,
"default", suffix);
stringBuilder.AppendLine("");
stringBuilder.Append("FROM ");
stringBuilder.AppendFormat("{0}{1}{2}", prefix,
"table", suffix);
stringBuilder.AppendLine();
stringBuilder.Append("WHERE ID=");
if (dsi.ParameterNameMaxLength < 1)
{
stringBuilder.Append("?");
}
else
{
stringBuilder.AppendFormat("{0}{1}",
dsi.ParameterMarkerPattern[0], parameter.ParameterName);
}
Debug.WriteLine(stringBuilder.ToString());
using (DbConnection conn = factory.CreateConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = stringBuilder.ToString();
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(parameter);
using (DbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
//do something with the data
}
}
}
}
请注意，我使用的是默认的 DbType 枚举来指定参数的数据类型；**如果**要使代码更具可移植性，这一点**非常**重要。
关注点
这段代码现在完全实现了它的目标。它以一种方式生成 SQL：
- 可用于所有提供程序，即使在使用关键字作为表名和列名时。
- 使用参数，使 SQL 的执行既安全又快速。
如果您查看其他可用的集合，您甚至可以找到一个表，将数据库类型映射到 System.Type 和 DbType。
这种类型的编码可以防止您的代码被绑定到特定版本的驱动程序，这是直接引用提供程序 DLL 的副作用。
无论多么仔细的编码都无法完全处理所有数据库；差异太多了。然而，这种细致的工作可以使您的应用程序能够支持更多的数据库，从而吸引更多的客户。
历史
- 2010 年 1 月 14 日：初稿