更轻松地处理数据库连接
4.82/5 (18投票s)
构建O/R映射器：第3步。
引言
请记得为本文投票。
在本文中，我将汇集几段代码，以帮助透明地处理数据库连接。我将引用我其他文章中介绍的代码和思路，包括
到本文结尾，我将展示一组允许简单使用数据库的对象，将绝大多数复杂的代码压缩到几行简单的代码中。例如，使用这些对象，从app.config/web.config加载连接字符串，获取提供程序字符串，加载提供程序工厂将隐藏在一个简单的调用中。
var sc = new SQLCommand("dsn");
此外，处理用于简单查询的众多对象的全部繁琐工作都将被消除。以下块与上述内容结合起来，将是获取强类型行计数所需的所有内容。
sc.CommandText.Append("SELECT count(ID) from MyTable");
var count = sc.ExecuteScalar<int>();
正确使用时，这组对象还可以极大地提高应用程序使用任何数据库作为后端的 istability。
背景
可重用代码是许多库和许多程序员的目标。有时，为了分离代码并使其更灵活，代码片段会被一次又一次地编写。一个完美的例子就是使用ExecuteScalar执行SQL语句并返回单个值的代码。让我们看一个典型的例子。
ppublic const string connectionString =
"Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI";
public static SqlConnection _connection = null;
public static void Main(string[] args)
{
try
{
_connection = new SqlConnection(connectionString);
_connection.Open();
int count;
using (var cmd = new SqlCommand("select count(*) from [Order Details]",
_connection))
{
count = (int)cmd.ExecuteScalar();
}
Debug.WriteLine("Number of records in Order Details: " + count);
//nested datareaders will fail
using (var outerCommand = _connection.CreateCommand())
{
outerCommand.CommandText = "select OrderID from Orders";
using (var outerDataReader = outerCommand.ExecuteReader())
{
while (outerDataReader.Read())
{
int orderid = outerDataReader.GetInt32(0);
using (var innerCommand = _connection.CreateCommand())
{
innerCommand.CommandText =
"select * from [Order Details] where OrderID=" + orderid;
//under most providers, this will throw.
using (var innerDataReader = innerCommand.ExecuteReader())
{
while (innerDataReader.Read())
{
//print the order details
}
}
}
}
}
}
// run the rest of the program
}
catch (Exception ex)
{
Debug.WriteLine(ex);
}
finally
{
if (_connection != null)
{
_connection.Dispose();
}
}
}
此块中的错误
- 数据提供程序是硬编码的；这将使得更改为另一个提供程序**非常**困难，特别是当此块使用
SqlClient提供程序的特定功能时。 - 连接字符串是硬编码的，哎呀。即使它没有硬编码，人们也把连接字符串存储在任何地方，而不是它们应该在的地方，即app.config的
ConnectionStrings区域。 - 将连接保持打开状态供整个应用程序重复使用。这使得在网络出现故障时很难恢复。有些提供程序（目前我想到的是Npgsql）不支持会告诉您连接状态何时改变的事件。那么使用
StateChange事件作为修复该问题的方法也就无从谈起。我还提供了一个关于在重用同一连接时嵌套数据读取器的示例。最后，它会阻止您利用连接池。 - 更令人烦恼的是，这种方法意味着
CommandBuilder代码必须到处复制，使代码混乱。 - SQL没有利用参数。
我在之前的文章中已经涵盖了很多这方面的内容。在我关于安全密码身份验证的文章中，我提供了一个简单的静态数据库类，它自动处理了**一些**这方面的内容。然而，收到的批评之一是它太“老派”了。这段代码本不应是生产代码，尽管我过去曾使用过一个功能更强的版本。当我重构我为文章写的原始代码时，我意识到由于我处理一些事情的方式存在一些不必要的开销。所以这里的主要目标是
- 隐藏连接和命令的不断构建和释放
- 实现连接池的透明使用
- 能够加载和连接到任何已安装提供程序的任何数据库
- 存储每个连接的数据源信息
构建类
在我关于使用.NET数据提供程序信息的文章中，我介绍了如何从数据中填充数据源信息类。在我关于安全密码身份验证的简要解释文章中，我创建了一个静态数据库类，但它不支持连接到多个数据库，但这**并非**意味着它不支持多个连接。
此时，我想指出，静态数据库类**不是**一个包含静态变量的类。静态变量的问题在于它们本质上是全局变量，所以当您不期望它们被更改时，它们就会被更改。当引入多线程时，这个问题会加剧。我之所以提到这一点，是因为.NET代码的主要用途之一是ASP.NET Web应用程序，而Web应用程序确实是一个多线程应用程序，每次页面请求都会从线程池中获得一个线程，运行页面，然后将线程返回给池。这意味着静态变量对于“一次写入，多次读取”的变量非常有用，特别适合字符串，尤其是经过适当锁定的字符串。但是，如果您犯了让您的ASP.NET应用程序使用静态数据库连接变量的错误，您将遇到许多问题
- 正确锁定连接意味着在连接使用期间，所有其他使用该网站的用户都必须等待挂起的 দী finished。事务变得不可能，因为您无法区分谁在做什么。
- 您永远无法关闭连接，因为下一个用户可能会遇到错误的连接状态，并且由于它在多线程环境中工作，因此在使用前检查连接状态没有帮助。更糟糕的是，如果数据库断开连接，提供程序仍可能显示连接已打开。
- 您消除了同时使用多个数据集的能力。
总之，虽然静态变量适用于某些用途，但数据库连接**不**属于其中之一。连接字符串列表是一个很好的例子，说明了什么可能有用，尤其是在清理完字符串之后，因为您不想每次都读取配置、解析连接字符串然后使用它。
我上一篇文章中的静态数据库类封装了许多处理.NET中数据库的繁琐代码。没有类似的代码，您必须读取连接字符串，希望是提供程序，获取工厂类，创建一个连接并打开连接，然后在做任何事情之前创建一个命令。当然，我见过有人跳过读取工厂，并将其余部分封装到一个类中。然而，这并没有真正利用.NET中已有的功能。
我希望我的类能达到某些目标
- 处理连接字符串的读取，并使用
DbConnectionStringBuilder对象进行验证。 - 处理提供程序的读取并获取工厂的单例实例。
- 使用工厂创建所有新对象：
DbConnectionStringBuilder、DbConnection、DbDataAdapter、DbDataReader，特别是DbParameter。 - 创建一个我可以保持打开状态的连接，以便进行事务处理；否则，它会为我关闭连接，并将其返回到池中。
- 公开底层对象的大部分（如果不是全部）功能，目标是简化，而不是消除功能。
- 使用我的
DataSourceInformation类来包装对象名称并带有适当的转义字符，允许使用关键字和特殊字符作为对象名称（例如，名为group的表）。 - 使用我的
DataSourceInformation类来确定要使用哪个字符。
包装类常常会移除功能，尤其是在数据库包装代码中。它倾向于强制您使用一个连接字符串，限制您的操作，最常见的是移除参数的使用。
我认为您不应忽视基础库中已有的东西。所以第一个想法是包装某些函数，然后返回一个已连接的DbCommand对象。这可以轻松地解决我的许多目标。不幸的是，处理参数会变得非常困难，因为如果我们使用的是DbCommand而不是派生对象，那么在不重新获取工厂引用)的情况下，就无法添加参数。此外，当使用这样的代码进行字符串连接时，字符串连接可能会非常慢。
cmd.CommandText = "SELECT ";
cmd.CommandText += "Colum1, ";
cmd.CommandText += "Colum2, ";
cmd.CommandText += "Colum3, ";
cmd.CommandText += "Colum4 ";
cmd.CommandText += "FROM " + WrapObjectName("dbo.TableName") + " a WHERE ";
cmd.CommandText += WrapObjectName("a.ID") + " = " +
GetParameterChar("nameIfNamedParamsAreSupported");
所以我想使用一个StringBuilder对象来进行字符串操作，这样它就变成了一个新对象。
下一步是想出一个名字，SQLCommand是我唯一能想到的，因为它体现了我正在做什么以及我正在包装什么。
接下来，我需要一个对象来保存一些信息：我的DataSourceInformation对象、已验证的连接字符串、我可以用它来创建新对象的工厂，以及一个字典，我可以用它来填充并按连接字符串名称访问它。
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Security.Cryptography;
using System.Text;
using System.Threading;
namespace CodeProjectArticles
{
public class DataSource
{
private static readonly RandomNumberGenerator
_random = RandomNumberGenerator.Create();
private string _name;
private DataSourceInformation _information;
private DbCommandBuilder _commandBuilder;
private DbProviderFactory _factory;
private DbConnectionStringBuilder _connectionStringBuilder;
private char _compositeIdentifierSeparatorPattern = ' ';
private bool _trackOpenConnections;
private string _seperator;
private string _quoteSuffix;
private string _quotePrefix;
private int _openConnections;
public DataSource(string name)
{
// this will throw if it doesn't exist
var css = ConfigurationManager.ConnectionStrings[name];
Initialize(name, css.ConnectionString, css.ProviderName);
}
public DataSource(string name, string connectionString,
string providerName)
{
Initialize(name, connectionString, providerName);
}
public string Name
{
get { return _name; }
}
public DataSourceInformation Information
{
get { return _information; }
}
public DbProviderFactory Factory
{
get { return _factory; }
}
public DbConnectionStringBuilder ConnectionStringBuilder
{
get { return _connectionStringBuilder; }
}
public string ConnectionString
{
get { return _connectionStringBuilder.ConnectionString; }
}
private DbCommandBuilder CommandBuilder
{
get { return _commandBuilder ??
(_commandBuilder = Factory.CreateCommandBuilder()); }
}
private char CompositeIdentifierSeparatorPattern
{
get
{
if (_compositeIdentifierSeparatorPattern == ' ')
{
var seperator = '.';
var s = _information.CompositeIdentifierSeparatorPattern;
if (!string.IsNullOrEmpty(s))
{
seperator = s.Replace("\\", string.Empty)[0];
}
_compositeIdentifierSeparatorPattern = seperator;
}
return _compositeIdentifierSeparatorPattern;
}
}
private string JoinSeperator
{
get
{
if (string.IsNullOrEmpty(_seperator))
{
_seperator = string.Concat(QuoteSuffix,
CompositeIdentifierSeparatorPattern, QuotePrefix);
}
return _seperator;
}
}
private string QuoteSuffix
{
get
{
if (string.IsNullOrEmpty(_quoteSuffix))
{
_quoteSuffix = CommandBuilder.QuoteSuffix;
if (string.IsNullOrEmpty(_quoteSuffix))
{
_quoteSuffix = "\"";
}
_quoteSuffix = _quoteSuffix.Trim();
}
return _quoteSuffix;
}
}
private string QuotePrefix
{
get
{
if (string.IsNullOrEmpty(_quotePrefix))
{
_quotePrefix = CommandBuilder.QuotePrefix;
if (string.IsNullOrEmpty(_quotePrefix))
{
_quotePrefix = "\"";
}
_quotePrefix = _quotePrefix.Trim();
}
return _quotePrefix;
}
}
public string GenerateNewParameterName()
{
var len = Information.ParameterNameMaxLength;
return GenerateNewParameterName(len);
}
public string GenerateNewParameterName(int length)
{
if (length == 0 || length > 8)
{
length = 8;
}
var buffer = new byte[length];
_random.GetBytes(buffer);
var sb = new StringBuilder();
var i = 0;
foreach (var b in buffer)
{
var valid = b > 64 && b < 91; // A-Z are valid
valid |= b > 96 && b < 123; // a-z are also valid
if (i > 0)
{
valid |= b > 47 && b < 58;
// 0-9 are only valid if not the first char
}
// if the byte is a valid char use it,
// otherwise, use modulo divide and addition
// to make it an a-z value
var c = !valid ? (char)((b % 26) + 'a') : (char)b;
sb.Append(c);
i++;
}
return sb.ToString();
}
public string WrapObjectName(string objectName)
{
if (!string.IsNullOrEmpty(objectName))
{
var quoteSuffix = QuoteSuffix;
var quotePrefix = QuotePrefix;
if (objectName.Contains(quotePrefix) ||
objectName.Contains(quoteSuffix))
{
objectName = UnwrapObjectName(objectName);
}
var ss = objectName.Split(CompositeIdentifierSeparatorPattern);
if (ss.Length > 1)
{
objectName = string.Join(JoinSeperator, ss);
}
objectName =
string.Concat(quotePrefix, objectName, quoteSuffix);
}
return objectName;
}
public string UnwrapObjectName(string objectName)
{
if (!string.IsNullOrEmpty(objectName))
{
var ss = objectName.Split(CompositeIdentifierSeparatorPattern);
var quotePrefix = QuotePrefix;
var quoteSuffix = QuoteSuffix;
if (ss.Length > 1 && quoteSuffix.Length > 0 &&
quotePrefix.Length > 0)
{
var list = new List<string>();
foreach (var s in ss)
{
var tmp = s;
var len = tmp.Length;
if (len > 2)
{
if (tmp.Substring(0, 1) == quotePrefix &&
tmp.Substring(len - 1, 1) == quoteSuffix)
{
tmp = tmp.Substring(1, len - 2);
}
}
list.Add(tmp);
}
list.CopyTo(ss);
}
objectName = string.Join(
CompositeIdentifierSeparatorPattern.ToString(), ss);
}
return objectName;
}
public DbConnection GetNewConnection()
{
var conn = Factory.CreateConnection();
conn.ConnectionString = _connectionStringBuilder.ConnectionString;
if (_trackOpenConnections)
{
//Add connection state change events if the
//provider supports it
conn.StateChange += StateChange;
}
conn.Disposed += ConnDisposed;
conn.Open();
return conn;
}
public string GetParameterName(string parameterName)
{
var s = parameterName;
var l = Information.ParameterNameMaxLength;
if (l < 1)
{
return Information.ParameterMarker;
}
if (l < s.Length)
{
s = s.Substring(0, l);
}
var reg = Information.ParameterNamePatternRegex;
if (!reg.IsMatch(s))
{
s = GenerateNewParameterName();
}
return string.Concat(Information.ParameterMarker, s);
}
private void Initialize(string name,
string connectionString, string providerName)
{
_name = name;
// get the provider and then get the Factory Singleton
_factory = DbProviderFactories.GetFactory(providerName);
//some providers, don't provide an inherited DbConnectionStringBuilder
//so if the factory call returns null, use the default.
_connectionStringBuilder = Factory.CreateConnectionStringBuilder() ??
new DbConnectionStringBuilder(true);
_connectionStringBuilder.ConnectionString = connectionString;
TestConnectionStringForMicrosoftExcelOrAccess();
using (var conn = Factory.CreateConnection())
{
conn.ConnectionString = ConnectionString;
//add a state change event, if this one is called, it will
//set up the events later, so we can keep track of how many are open
conn.StateChange += ConnStateChange;
conn.Open();
_information = new DataSourceInformation(
conn.GetSchema(DbMetaDataCollectionNames.DataSourceInformation));
}
}
private void TestConnectionStringForMicrosoftExcelOrAccess()
{
var useSquareBrackets = false;
var name = _connectionStringBuilder.GetType().FullName ?? string.Empty;
if (name.StartsWith("System.Data.OleDb"))
{
//this is a OleDb connection
var s = _connectionStringBuilder["Extended Properties"] as string;
if (!string.IsNullOrEmpty(s) && s.ToLower().Contains("excel"))
{
//we found MS Excel
useSquareBrackets = true;
}
else
{
//check for MS Acess
s = _connectionStringBuilder["Provider"] as string ?? string.Empty;
useSquareBrackets = s.Contains("MS Remote");
if (!useSquareBrackets)
{
s = (_connectionStringBuilder["Data Source"]
as string ?? string.Empty).ToLower();
useSquareBrackets = s.EndsWith(".accdb") || s.EndsWith(".mdb");
}
}
}
else
{
if (name.StartsWith("System.Data.Odbc"))
{
//this is an Odbc Connection
var s = _connectionStringBuilder["driver"] as string;
if (!string.IsNullOrEmpty(s))
{
s = s.ToLower();
//test for either excel or access
useSquareBrackets =
s.Contains("*.xls") || s.Contains("*.mdb");
}
}
}
if (useSquareBrackets)
{
_quotePrefix = "[";
_quoteSuffix = "]";
}
}
private void ConnStateChange(object sender, StateChangeEventArgs e)
{
_trackOpenConnections = true;
}
private void ConnDisposed(object sender, EventArgs e)
{
//Debug.WriteLine("Connection Disposed");
}
private void StateChange(object sender, StateChangeEventArgs e)
{
var connectionState = e.CurrentState;
// Debug.WriteLine(Enum.GetName(typeof(ConnectionState),
// connectionState));
switch (connectionState)
{
case ConnectionState.Open:
Interlocked.Increment(ref _openConnections);
break;
case ConnectionState.Closed:
case ConnectionState.Broken:
Interlocked.Decrement(ref _openConnections);
break;
default:
//case ConnectionState.Connecting:
//case ConnectionState.Executing:
//case ConnectionState.Fetching:
break;
}
// Debug.WriteLine("Open Connections :" +
// Interlocked.Add(ref _openConnections, 0));
}
}
}
这个类的全部内容都致力于跟踪您在处理数据库时应该处理的所有“东西”。这包括
- 从相应的命令构建器获取像
QuotePrefix和QuoteSuffix这样的数据 - 获取正确的
DbProviderFactory - 正确地用引号包装实体名称的函数
- 验证参数名称，使用提供程序提供的正则表达式
- 如果支持命名参数，则生成有效的参数名称
- 计算打开的连接数（如果支持事件）
- 从.config文件的
ConnectionStrings区域加载信息 - 允许使用内置连接字符串（允许您将连接信息存储在其他地方）
您将开始看到一个主题出现：惰性加载我需要的东西（仅在需要时加载），验证它，然后将其保存在内存中，这样我就不必一次又一次地重复。使用这种方法占用大量内存的担忧是有效的，但对于大多数应用程序来说，您拥有的连接非常少，最多我只见过一个应用程序中有5个连接（连接到三种不同的数据库类型）；在绝大多数情况下，通常只有一个连接。
接下来，我需要一个类来让我使用这些信息，封装所有标准函数。在.NET中，现有的DbCommand对象包含三个非常简单的函数：ExecuteScalar、ExecuteReader和ExecuteNonQuery。出于某种未知原因，它们没有选择实现ExecuteDataSet或ExecuteDataTable，所以我将添加自己的。另外，ExecuteScalar在TimesTen数据库上每次都会失败，错误显示它试图通过额外的记录集。为了缓解这个错误，我通过执行ExecuteReader来处理它，指定我想要的行为：“返回单行，单列”。如果数据库和底层提供程序遵循这一点，那么SQL语句可以是
SELECT * FROM TableWithAMillionRows
只会返回第一行的第一列。尽管如此，最好还是编写您的SQL，以便只返回您想要的内容。
我希望将参数与DbCommand分开，所以我创建了一个简单的参数重载
using System.Collections.Generic;
using System.Data.Common;
namespace CodeProjectArticles
{
public class ParameterDictionary : Dictionary<string, DbParameter>
{
public void Add(DbParameter item)
{
Add(item.ParameterName, item);
}
}
}
现在来处理做所有工作的类
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Threading;
namespace CodeProjectArticles
{
public sealed class SQLCommand : IDisposable
{
private static readonly Dictionary<string, DataSource>
_dataSourceDictionary = new Dictionary<string, DataSource>();
private static readonly object _syncObject = new object();
private readonly ParameterDictionary _parameters =
new ParameterDictionary();
private readonly StringBuilder _commandText = new StringBuilder();
private DataSource _dataSource;
private int _disposed;
private DbConnection _connection;
public SQLCommand(string connectionName)
{
Initialize(connectionName);
}
public SQLCommand(string connectionName, string connectionString,
string providerName)
{
DataSource dataSource = null;
lock (_syncObject)
{
if (!_dataSourceDictionary.TryGetValue(
connectionName, out dataSource))
{
dataSource = new DataSource(connectionName,
connectionString, providerName);
_dataSourceDictionary.Add(connectionName, dataSource);
}
}
_dataSource = dataSource;
}
~SQLCommand()
{
Dispose(false);
}
public StringBuilder CommandText
{
get { return _commandText; }
}
public bool InTransaction
{
get { return false; }
}
public ParameterDictionary Parameters
{
get { return _parameters; }
}
public void Dispose()
{
Dispose(true);
}
public void Initialize(string connectionName)
{
DataSource dataSource = null;
lock (_syncObject)
{
if (!_dataSourceDictionary.TryGetValue(connectionName,
out dataSource))
{
dataSource = new DataSource(connectionName);
_dataSourceDictionary.Add(connectionName, dataSource);
}
}
_dataSource = dataSource;
}
public DbDataReader ExecuteReader()
{
var behavior = InTransaction ?
CommandBehavior.Default : CommandBehavior.CloseConnection;
return ExecuteReader(behavior, CommandType.Text, 30);
}
public DbDataReader ExecuteReader(CommandBehavior commandBehavior,
CommandType commandType, int? commandTimeOut)
{
var conn = GetConnection();
try
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = _commandText.ToString();
cmd.CommandType = commandType;
cmd.CommandTimeout = commandTimeOut ?? cmd.CommandTimeout;
try
{
foreach (var parameter in Parameters.Values)
{
cmd.Parameters.Add(parameter);
}
return cmd.ExecuteReader(commandBehavior);
}
finally
{
cmd.Parameters.Clear();
}
}
}
finally
{
// this is a special case even if this object
// is NOT part of a transaction so handle it
// differently than other cases
if ((commandBehavior & CommandBehavior.CloseConnection) ==
CommandBehavior.CloseConnection)
{
// get rid of the connection
// so the connection won't be reused
// if not in a transaction
// and the SQLCommand is reused.
_connection = null;
}
}
}
public object ExecuteScalar()
{
return ExecuteScalar(CommandType.Text, null);
}
public object ExecuteScalar(CommandType commandType)
{
return ExecuteScalar(commandType, null);
}
public object ExecuteScalar(CommandType commandType,
int? commandTimeout)
{
try
{
var behavior = InTransaction ? CommandBehavior.Default :
CommandBehavior.CloseConnection;
behavior |= CommandBehavior.SingleRow |
CommandBehavior.SingleResult;
using (var dr = ExecuteReader(behavior,
commandType, commandTimeout))
{
dr.Read();
return dr.GetValue(0);
}
}
finally
{
DisposeConnection();
}
}
public T ExecuteScalar<T>()
{
return (T)ExecuteScalar(CommandType.Text, null);
}
public T ExecuteScalar<T>(CommandType commandType)
{
return (T)ExecuteScalar(commandType, null);
}
public T ExecuteScalar<T>(CommandType commandType, int? commandTimeout)
{
return (T)ExecuteScalar(commandType, commandTimeout);
}
public int ExecuteNonQuery(CommandType commandType)
{
return ExecuteNonQuery(commandType, null);
}
public int ExecuteNonQuery(CommandType commandType, int? commandTimeout)
{
var conn = GetConnection();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = _commandText.ToString();
cmd.CommandTimeout = commandTimeout ?? cmd.CommandTimeout;
cmd.CommandType = commandType;
try
{
foreach (var parameter in Parameters.Values)
{
cmd.Parameters.Add(parameter);
}
return cmd.ExecuteNonQuery();
}
finally
{
cmd.Parameters.Clear();
}
}
}
public DataSet ExecuteDataSet()
{
var conn = GetConnection();
try
{
using (var cmd = conn.CreateCommand())
{
try
{
cmd.CommandText = _commandText.ToString();
// cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.Text;
using (var da = _dataSource.Factory.CreateDataAdapter())
{
da.SelectCommand = cmd;
var dt = new DataSet();
da.Fill(dt);
return dt;
}
}
finally
{
cmd.Parameters.Clear();
}
}
}
finally
{
DisposeConnection();
}
}
public DataTable ExecuteDataTable()
{
var conn = GetConnection();
try
{
using (var cmd = conn.CreateCommand())
{
try
{
cmd.CommandText = _commandText.ToString();
// cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.Text;
using (var da = _dataSource.Factory.CreateDataAdapter())
{
da.SelectCommand = cmd;
var dt = new DataTable();
da.Fill(dt);
return dt;
}
}
finally
{
cmd.Parameters.Clear();
}
}
}
finally
{
DisposeConnection();
}
}
public void BeginTransaction()
{
throw new NotImplementedException();
}
public void CommitTransaction()
{
throw new NotImplementedException();
}
public void RollbackTransaction()
{
throw new NotImplementedException();
}
public string WrapObjectName(string objectName)
{
return _dataSource.WrapObjectName(objectName);
}
public DbParameter CreateParameter(DbType dbType,
string name, object value)
{
var p = _dataSource.Factory.CreateParameter();
p.ParameterName = name;
p.Value = value;
p.DbType = dbType;
return p;
}
public string GenerateNewParameterName()
{
return _dataSource.GenerateNewParameterName();
}
public string GetParameterName(DbParameter dbParameter)
{
return _dataSource.GetParameterName(dbParameter.ParameterName);
}
public string GetParameterName(string parameterName)
{
return _dataSource.GetParameterName(parameterName);
}
public DbParameter CreateParameter(DbType dbType, object value)
{
return CreateParameter(dbType,
_dataSource.GenerateNewParameterName(), value);
}
private DbConnection GetConnection()
{
// While I am not going to cover it here,
// you would get the transactions existing connection
// from that transaction, or if need be,
// get a new connection for a seperate database
if (_connection != null && _connection.State ==
ConnectionState.Closed)
{
DisposeConnection();
}
_connection = _connection ?? GetNewConnection();
return _connection;
}
private void DisposeConnection()
{
if (!InTransaction && _connection != null)
{
_connection.Dispose();
_connection = null;
}
}
private void Dispose(bool disposing)
{
if (Interlocked.Increment(ref _disposed) == 1)
{
if (disposing)
{
GC.SuppressFinalize(this);
}
if (_connection != null)
{
if (InTransaction)
{
//rollback
}
DisposeConnection();
}
_dataSource = null;
}
Interlocked.Exchange(ref _disposed, 1);
}
private DbConnection GetNewConnection()
{
return _dataSource.GetNewConnection();
}
}
}
有几点需要注意：泛型ExecuteScalar重载、配置的惰性加载，以及（有意地）部分实现的事务（我选择将这部分留给读者，或者在以后的文章中讨论）。这个类的很多部分是自解释的，有些则不是。如果您不熟悉此块中的??运算符
_connection = _connection ?? GetNewConnection();
它等同于这段代码，只是让事情更简单
if(_connection == null)
{
_connection = GetNewConnection();
}
其他有趣的几点包括，我选择实现IDisposable，以便它可以在using块中使用。默认行为是使用DbDataReader时关闭连接，这可以防止打开的连接闲置。
我不得不更新我的DataSourceInformation类，所以这是修改后的版本
using System;
using System.Data;
using System.Data.Common;
using System.Reflection;
using System.Text.RegularExpressions;
namespace CodeProjectArticles
{
public 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
private Regex _parameterNamePatternRegex;
private string _parameterPrefix;
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; }
}
public Regex ParameterNamePatternRegex
{
get { return _parameterNamePatternRegex ??
(_parameterNamePatternRegex = new Regex(ParameterNamePattern)); }
}
public string ParameterMarker
{
get
{
if (string.IsNullOrEmpty(_parameterPrefix))
{
_parameterPrefix = _parameterNameMaxLength != 0
? ParameterMarkerPattern.Substring(0, 1)
: ParameterMarkerFormat;
}
return _parameterPrefix;
}
}
}
}
使用代码
最后，这是如何使用它的示例，这也是所有这些工作似乎有用的地方。
using (var sc = new SQLCommand("dsn"))
{
var g = new Guid("2ac385a5-7843-4027-a586-dd4e9db8e72b");
sc.CommandText.AppendFormat("SELECT * from {0}",
sc.WrapObjectName("sec.page"));
sc.CommandText.AppendFormat(" WHERE {0}=",
sc.WrapObjectName("ID"));
var p = sc.CreateParameter(DbType.Guid, g);
sc.CommandText.Append(sc.GetParameterName(p));
sc.Parameters.Add(p);
using (var dr = sc.ExecuteReader())
{
while (dr.Read())
{
Debug.WriteLine(dr.GetGuid(0).ToString());
}
}
}
这个例子非常简单，新的SQLCommand从web.config或app.config加载所有信息。CommandText使用WrapObjectName构建，并创建一个参数名称，如果数据库支持，则插入命名参数，否则插入位置参数。这允许我从一个数据库切换到另一个数据库（这个例子在MS SQL（通过SqlClient）、OLEDB和ODBC，PostgreSQL（通过OLEDB、ODBC和NpgSQL）上都能工作）。还有其他方法可以使其更具可移植性，但那是另一篇文章的内容。
CreateParameter调用使用没有参数名的重载，这会创建一个有效的随机名称。这对于创建带有许多参数的SQL语句非常有用。我不想深入讨论命名参数和位置参数，除了说命名参数可以在语句内部重用，而位置参数不能；如果您的代码最终要独立于数据库，那么就假设您使用的是位置参数。
如果Установите breakpoint на строке ExecuteReader, вы увидите, что она генерирует разный SQL, это может быть
SELECT * from "sec"."page" WHERE "ID"=?
这将适用于任何符合SQL92标准的数据库，它将生成专有的内容
SELECT * from [sec].[page] WHERE [ID]=@ggatndbp
这适用于SQL Server。
关注点
没有什么能解决所有问题，这也不能解决不参数化SQL（但它使正确做到这一点变得容易），或编写糟糕的SQL，或搞错参数的位置。它确实解决了许多常见的错误，例如保持连接打开。
我无法很好地设计的一件事是连接的数据集/数据表。我个人不经常使用数据表或数据集，数据读取器更快，所以这不是我想花时间的事情。尤其是在Web上，一旦页面渲染完毕，您连接的数据表就会断开连接。
SQLCommand可以被使用和重用，而无需创建新的。因为我清除了并释放了内部的DbCommand对象，所以整个语句都可以重用。
我选择不使用现有的ParameterCollection对象，以便在添加参数后可以轻松访问它们。