/********************************************************************
Copyright (C) 2003 Lenovo Software(WuHan)Design Center
Program Name : Database.cs
Create by : 成志伟
Create Date : 2003-9-15
Description : SqlServer数据库访问密封类
: 以MS的Database类为基础,扩展了部分方法(说明以Lenovo Add标记)
Maintain Log :
<author> <time> <version> <desc>
成志伟 2003-9-15 1.0 build this program
********************************************************************/
// ===============================================================================
// Microsoft Data Access Application Block for .NET
// http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp
//
// Database.cs
//
// This file contains the implementations of the Database and DatabaseParameterCache
// classes.
//
// For more information see the Data Access Application Block Implementation Overview.
// ===============================================================================
// Release history
// VERSION DESCRIPTION
// 2.0 Added support for FillDataset, UpdateDataset and "Param" helper methods
//
// ===============================================================================
// Copyright (C) 2000-2001 Microsoft Corporation
// All rights reserved.
// THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
// OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
// LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
// FITNESS FOR A PARTICULAR PURPOSE.
// ==============================================================================
using System;
using System.Data;
using System.Configuration;
using System.Xml;
using System.Data.SqlClient;
using System.Collections;
namespace DBUtility
{
/// <summary>
/// Database类提供高性能可扩充的数据库访问方法
/// </summary>
public class Database
{
//此类只提供静态方法,构造函数应当是私有的
private Database() { }
/// <summary>
/// 取回连接字符串
/// </summary>
public static string ConnectionString
{
//2005获取配置文件中的使用方法
get { return ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; }
//过时使用方法
//get { return System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString(); }
}
/// <summary>
/// 在给定的数据库连接下根据类型、文本(存储过程名或者T-SQL命令)和参数数组执行获取数据集的SQL命令
/// </summary>
/// <remarks>
/// 举例:
/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">数据库连接对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <param name="commandParameters">参数数组</param>
/// <returns>返回执行命令得到的数据集</returns>
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, int startRecord, int recordNum, string TableName, params SqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
// 创建数据库连接并打开它,使用完后销毁
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// 创建命令对象并且绑定
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = connection.ConnectionTimeout;
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
// 创建数据适配器,使用完后销毁;创建返回的数据集
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
// 填充数据集(使用默认的表名)
da.Fill(ds, startRecord, recordNum, TableName);
// 清除SQL命令对象的参数,是参数数组可以重用
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
// 返回数据集
return ds;
}
}
}
/// <summary>
/// 在给定的数据库连接下根据类型、文本(存储过程名或者T-SQL命令)和参数数组执行获取数据集的SQL命令
/// </summary>
/// <remarks>
/// 举例:
/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">数据库连接对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <param name="commandParameters">参数数组</param>
/// <returns>返回执行命令得到的数据集</returns>
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, int startRecord, int recordNum, string TableName)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
// 创建数据库连接并打开它,使用完后销毁
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// 创建命令对象并且绑定
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = connection.ConnectionTimeout;
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, null, out mustCloseConnection);
// 创建数据适配器,使用完后销毁;创建返回的数据集
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
// 填充数据集(使用默认的表名)
da.Fill(ds, startRecord, recordNum, TableName);
// 清除SQL命令对象的参数,是参数数组可以重用
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
// 返回数据集
return ds;
}
}
}
#region 私有工具方法 构造函数
/// <summary>
/// 将参数数组绑定到SQL命令上
/// 输入类型的参数(InputOutput和Input)为null时转换为DBNull
/// </summary>
/// <param name="command">SQL命令</param>
/// <param name="commandParameters">参数数组</param>
private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
{
if (command == null) throw new ArgumentNullException("command");
if (commandParameters != null)
{
foreach (SqlParameter p in commandParameters)
{
if (p != null)
{
// 检查参数类型是否是输入类型并且为null
if ((p.Direction == ParameterDirection.InputOutput ||
p.Direction == ParameterDirection.Input) &&
(p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
}
}
/// <summary>
/// 将dataRow的字段值分配到参数数组的各项上
/// </summary>
/// <param name="commandParameters">参数数组</param>
/// <param name="dataRow">保存着存储过程参数值的DataRow</param>
private static void AssignParameterValues(SqlParameter[] commandParameters, DataRow dataRow)
{
if ((commandParameters == null) || (dataRow == null))
{
// 没有数据时直接返回
return;
}
int i = 0;
// 设置参数值
foreach (SqlParameter commandParameter in commandParameters)
{
// 检查参数名称
if (commandParameter.ParameterName == null ||
commandParameter.ParameterName.Length <= 1)
throw new Exception(
string.Format(
"Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: '{1}'.",
i, commandParameter.ParameterName));
if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1)
commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
i++;
}
}
/// <summary>
/// 将值数组的各项分配到参数数组的各项上
/// </summary>
/// <param name="commandParameters">参数数组</param>
/// <param name="parameterValues">值数组</param>
private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
{
if ((commandParameters == null) || (parameterValues == null))
{
// 没有数据时直接返回
return;
}
// 参数数组的长度和值数组的长度必须相等
if (commandParameters.Length != parameterValues.Length)
{
throw new ArgumentException("Parameter count does not match Parameter Value count.");
}
// 将值数组中的值分配到参数数组对应位置的参数中
for (int i = 0, j = commandParameters.Length; i < j; i++)
{
// 如果值数组兼容于IDbDataParameter类型,则分配值数组的Value属性
if (parameterValues[i] is IDbDataParameter)
{
IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i];
if (paramInstance.Value == null)
{
commandParameters[i].Value = DBNull.Value;
}
else
{
commandParameters[i].Value = paramInstance.Value;
}
}
else if (parameterValues[i] == null)
{
commandParameters[i].Value = DBNull.Value;
}
else
{
commandParameters[i].Value = parameterValues[i];
}
}
}
/// <summary>
/// 将数据库连接,事务处理,命令文本,命令类型,和命令参数绑定到将要执行的SQL命令上
/// </summary>
/// <param name="command">SQL命令</param>
/// <param name="connection">数据库连接对象</param>
/// <param name="transaction">事务</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名称或者SQL语句)</param>
/// <param name="commandParameters">命令参数</param>
/// <param name="mustCloseConnection">在本方法内打开数据库连接时为true,否则为false</param>
private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection)
{
if (command == null) throw new ArgumentNullException("command");
if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
// 将没有打开的数据库连接设为打开状态
if (connection.State != ConnectionState.Open)
{
mustCloseConnection = true;
connection.Open();
}
else
{
mustCloseConnection = false;
}
// 设置数据库连接
command.Connection = connection;
// 设置命令文本(存储过程名称或者SQL语句)
command.CommandText = commandText;
// 设置事务处理(如果提供了事务)
if (transaction != null)
{
if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
command.Transaction = transaction;
}
// 设置命令类型
command.CommandType = commandType;
// 设置命令参数(如果提供了命令参数)
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
#endregion 私有工具方法 构造函数
#region 执行非查询SQL命令
/// <summary>
/// 在给定的数据库连接串下根据的类型、文本(存储过程名或者T-SQL命令)执行非查询SQL命令
/// </summary>
/// <remarks>
/// 举例:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");
/// </remarks>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <returns>返回命令影响的行数</returns>
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
{
// 设定参数数组为null,调用重载方法
return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 在给定的数据库连接串下根据的类型、文本(存储过程名或者T-SQL命令)和参数数组执行非查询SQL命令
/// </summary>
/// <remarks>
/// 举例:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <param name="commandParameters">参数数组</param>
/// <returns>返回命令影响的行数</returns>
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
// 创建数据库连接并打开它,使用完后销毁
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// 用数据库连接对象替换参数连接串,调用重载方法
return ExecuteNonQuery(connection, commandType, commandText, commandParameters);
}
}
/// <summary>
/// 在给定的数据库连接串下根据的存储过程名称、值数组执行没有返回值的存储过程
/// 此方法将先根据连接串和存储过程名称获得参数数组(从数据库或者缓存),再将值数组分配到参数数组中
/// </summary>
/// <remarks>
/// 举例:
/// int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36);
/// </remarks>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="spName">存储过程名称</param>
/// <param name="parameterValues">值数组</param>
/// <returns>返回命令影响的行数</returns>
public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// 值数组不为空
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// 根据连接串和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(connectionString, spName);
// 值数组分配到参数数组中
AssignParameterValues(commandParameters, parameterValues);
// 用赋值后的参数数组为参数,调用重载方法
return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// 调用没有参数数组为参数的重载方法
return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 在指定的数据库连接下根据类型、文本(存储过程名或者T-SQL命令)执行非查询SQL命令
/// </summary>
/// <remarks>
/// 举例:
/// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");
/// </remarks>
/// <param name="connection">数据库连接对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <returns>返回命令影响的行数</returns>
public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)
{
// 设定参数数组为null,调用重载方法
return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 在指定的数据库连接下根据类型、文本(存储过程名或者T-SQL命令)和参数数组执行非查询SQL命令
/// </summary>
/// <remarks>
/// 举例:
/// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">数据库连接对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <param name="commandParameters">参数数组</param>
/// <returns>返回命令影响的行数</returns>
public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
// 创建命令并且绑定
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = connection.ConnectionTimeout;
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
// 执行SQL命令
int retval = cmd.ExecuteNonQuery();
// 清除SQL命令参数
cmd.Parameters.Clear();
//关闭新建的数据库连接
if (mustCloseConnection)
connection.Close();
return retval;
}
/// <summary>
/// 在指定的数据库连接下根据的存储过程名称、值数组执行没有返回值的存储过程
/// 此方法将先根据连接串和存储过程名称获得参数数组(从数据库或者缓存),再将值数组分配到参数数组中
/// </summary>
/// <remarks>
/// 举例:
/// int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36);
/// </remarks>
/// <param name="connection">数据库连接对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="parameterValues">值数组</param>
/// <returns>返回命令影响的行数</returns>
public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// 值数组不为空
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(connection, spName);
// 值数组分配到参数数组中
AssignParameterValues(commandParameters, parameterValues);
// 用赋值后的参数数组为参数,调用重载方法
return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// 调用没有参数数组为参数的重载方法
return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 在指定的事务下根据类型、文本(存储过程名或者T-SQL命令)执行非查询SQL命令
/// </summary>
/// <remarks>
/// 举例:
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders");
/// </remarks>
/// <param name="transaction">事物对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <returns>返回命令影响的行数</returns>
public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText)
{
// 设定参数数组为null,调用重载方法
return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 在指定的事务下根据类型、文本(存储过程名或者T-SQL命令)和参数数组执行非查询SQL命令
/// 数据库连接使用指定事务对象本身的连接,因为事物必须建立在相同的数据库连接内
/// </summary>
/// <remarks>
/// 举例:
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">事务对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <param name="commandParameters">参数数组</param>
/// <returns>返回命令影响的行数</returns>
public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
// 创建SQL命令并且绑定
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = transaction.Connection.ConnectionTimeout;
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// 执行SQL命令
int retval = cmd.ExecuteNonQuery();
// 清除SQL命令参数
cmd.Parameters.Clear();
return retval;
}
/// <summary>
/// 在指定的事务下根据类型、文本(存储过程名或者T-SQL命令)和值数数组执行没有返回值的存储过程
/// 此方法将先根据事物对象的连接和存储过程名称获得参数数组(从数据库或者缓存),再将值数组分配到参数数组中
/// </summary>
/// <remarks>
/// 举例:
/// int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36);
/// </remarks>
/// <param name="transaction">事物对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="parameterValues">参数数组</param>
/// <returns>返回命令影响的行数</returns>
public static int ExecuteNonQuery(SqlTransaction transaction, string spName, params object[] parameterValues)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// 值数组不为空
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// 根据事物对象的连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(transaction.Connection, spName);
// 值数组分配到参数数组中
AssignParameterValues(commandParameters, parameterValues);
// 用赋值后的参数数组为参数,调用重载方法
return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// 调用没有参数数组为参数的重载方法
return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion 执行非查询SQL命令
#region 执行返回数据集的SQL命令
/// <summary>
/// 在给定的数据库连接串下根据的类型、文本(存储过程名或者T-SQL命令)执行获取数据集的SQL命令
/// </summary>
/// <remarks>
/// 举例:
/// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");
/// </remarks>
/// <param name="connectionString">数据库连接串</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <returns>返回执行命令得到的数据集</returns>
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
{
// 设定参数数组为null,调用重载方法
return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 在给定的数据库连接串下根据的类型、文本(存储过程名或者T-SQL命令)和参数数组执行获取数据集的SQL命令
/// </summary>
/// <remarks>
/// 举例:
/// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">数据库连接串</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <param name="commandParameters">参数数组</param>
/// <returns>返回执行命令得到的数据集</returns>
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
// 创建数据库连接并打开它,使用完后销毁
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// 用数据库连接对象替换参数连接串,调用重载方法
return ExecuteDataset(connection, commandType, commandText, commandParameters);
}
}
/// <summary>
/// 在给定的数据库连接串下根据存储过程名、值数组执行存储过程获取数据集
/// 此方法将先根据连接串和存储过程名称获得参数数组(从数据库或者缓存),再将值数组分配到参数数组中
/// </summary>
/// <remarks>
/// 举例:
/// DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);
/// </remarks>
/// <param name="connectionString">数据库连接串</param>
/// <param name="spName">存储过程名</param>
/// <param name="parameterValues">值数组</param>
/// <returns>返回执行命令得到的数据集</returns>
public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// 值数组不为空
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(connectionString, spName);
// 值数组分配到参数数组中
AssignParameterValues(commandParameters, parameterValues);
// 用赋值后的参数数组为参数,调用重载方法
return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// 调用没有参数数组为参数的重载方法
return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 在给定的数据库连接下根据类型、文本(存储过程名或者T-SQL命令)执行获取数据集的SQL命令
/// </summary>
/// <remarks>
/// 举例:
/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");
/// </remarks>
/// <param name="connection">数据库连接对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <returns>返回执行命令得到的数据集</returns>
public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)
{
// 设定参数数组为null,调用重载方法
return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 在给定的数据库连接下根据类型、文本(存储过程名或者T-SQL命令)和参数数组执行获取数据集的SQL命令
/// </summary>
/// <remarks>
/// 举例:
/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">数据库连接对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <param name="commandParameters">参数数组</param>
/// <returns>返回执行命令得到的数据集</returns>
public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
// 创建命令对象并且绑定
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = connection.ConnectionTimeout;
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
// 创建数据适配器,使用完后销毁;创建返回的数据集
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
// 填充数据集(使用默认的表名)
da.Fill(ds);
// 清除SQL命令对象的参数,是参数数组可以重用
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
// 返回数据集
return ds;
}
}
/// <summary>
/// 在给定的数据库连接下根据存储过程名、值数组执行存储过程获取数据集
/// 此方法将先根据连接和存储过程名称获得参数数组(从数据库或者缓存),再将值数组分配到参数数组中
/// </summary>
/// <remarks>
/// 举例:
/// DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);
/// </remarks>
/// <param name="connection">数据库连接对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="parameterValues">值数组</param>
/// <returns>返回执行命令得到的数据集</returns>
public static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] parameterValues)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// 值数组不为空
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(connection, spName);
// 值数组分配到参数数组中
AssignParameterValues(commandParameters, parameterValues);
// 用赋值后的参数数组为参数,调用重载方法
return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// 调用没有参数数组为参数的重载方法
return ExecuteDataset(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 在指定的事务下根据类型、文本(存储过程名或者T-SQL命令)执行获取数据集的SQL命令
/// </summary>
/// <remarks>
/// 举例:
/// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders");
/// </remarks>
/// <param name="transaction">事物对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <returns>返回执行命令得到的数据集</returns>
public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText)
{
// 设定参数数组为null,调用重载方法
return ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 在指定的事务下根据类型、文本(存储过程名或者T-SQL命令)和参数数组执行获取数据集的SQL命令
/// </summary>
/// <remarks>
/// 举例:
/// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">事物对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <param name="commandParameters">参数数组</param>
/// <returns>返回执行命令得到的数据集</returns>
public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
// 创建SQL命令并绑定
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = transaction.Connection.ConnectionTimeout;
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// 创建数据适配器,使用完后销毁;创建返回的数据集
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
// 填充数据集(使用默认的表名)
da.Fill(ds);
// 清除SQL命令参数
cmd.Parameters.Clear();
// 返回数据集
return ds;
}
}
/// <summary>
/// 在指定事务的数据库连接下根据的存储过程名称、值数组执行的存储过程获取数据集
/// 此方法将先根据连接和存储过程名称获得参数数组(从数据库或者缓存),再将值数组分配到参数数组中
/// </summary>
/// <remarks>
/// 举例:
/// DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36);
/// </remarks>
/// <param name="transaction">事物对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="parameterValues">值数组</param>
/// <returns>返回执行命令得到的数据集</returns>
public static DataSet ExecuteDataset(SqlTransaction transaction, string spName, params object[] parameterValues)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// 值数组不为空
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(transaction.Connection, spName);
// 值数组分配到参数数组中
AssignParameterValues(commandParameters, parameterValues);
// 用赋值后的参数数组为参数,调用重载方法
return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// 调用没有参数数组为参数的重载方法
return ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion 执行返回数据集的SQL命令
#region 执行返回DataReader的SQL命令
/// <summary>
/// 枚举常量指出数据库连接对象的所属,从而为ExecuteReader提供恰当的CommandBehavior参数
/// </summary>
private enum SqlConnectionOwnership
{
/// <summary>数据库连接对象属于Database并由它来管理</summary>
Internal,
/// <summary>数据库连接对象属于外部调用并由外部调用来管理</summary>
External
}
/// <summary>
/// 创建SQL命令对象并绑定,使用恰当的CommandBehavior参数调用ExecuteReader方法
/// </summary>
/// <remarks>
/// 如果数据库连接对象是由Database创建打开的,那么将在DataReader对象关闭的时候一起关闭
/// 如果数据库连接对象是由调用者提供的,那么将有调用者负责管理
/// </remarks>
/// <param name="connection">数据库连接对象</param>
/// <param name="transaction">事物对象(或者为null)</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <param name="commandParameters">参数数组</param>
/// <param name="connectionOwnership">数据库连接对象所有者标记</param>
/// <returns>返回保存执行结果的DataReader</returns>
private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
{
if (connection == null) throw new ArgumentNullException("connection");
bool mustCloseConnection = false;
// 创建命令对象并且绑定
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = connection.ConnectionTimeout;
try
{
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// 创建阅读器
SqlDataReader dataReader;
// 使用恰当的CommandBehavior参数调用ExecuteReader方法
if (connectionOwnership == SqlConnectionOwnership.External)
{
dataReader = cmd.ExecuteReader();
}
else
{
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
// 清除SQL命令对象的参数
// HACK: There is a problem here, the output parameter values are fletched
// when the reader is closed, so if the parameters are detached from the command
// then the SqlReader can磘 set its values.
// When this happen, the parameters can磘 be used again in other command.
// 注意:问题在于当reader被关闭时output类型的参数值是可以被引用的,
// 所以如果参数数组被从SQL命令对象中分离掉了,SqlReader将不能再给它赋值
// 这种情况一旦发生,参数数组将不能再被其他命令对象使用
bool canClear = true;
foreach (SqlParameter commandParameter in cmd.Parameters)
{
if (commandParameter.Direction != ParameterDirection.Input)
canClear = false;
}
if (canClear)
{
cmd.Parameters.Clear();
}
return dataReader;
}
catch
{
if (mustCloseConnection)
connection.Close();
throw;
}
}
/// <summary>
/// 在给定的数据库连接串下根据的类型、文本(存储过程名或者T-SQL命令)执行获取DataReader的SQL命令
/// </summary>
/// <remarks>
/// 举例:
/// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders");
/// </remarks>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <returns>返回保存执行结果的DataReader</returns>
public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
{
// 设定参数数组为null,调用重载方法
return ExecuteReader(connectionString, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 在给定的数据库连接串下根据的类型、文本(存储过程名或者T-SQL命令)和参数数组执行获取DataReader的SQL命令
/// </summary>
/// <remarks>
/// 举例:
/// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <param name="commandParameters">参数数组</param>
/// <returns>返回保存执行结果的DataReader</returns>
public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
SqlConnection connection = null;
try
{
connection = new SqlConnection(connectionString);
connection.Open();
// 调用重载方法,事务参数为null,指定数据库对象由Database管理
return ExecuteReader(connection, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal);
}
catch
{
// 调用方法失败,关闭数据库对象
if (connection != null) connection.Close();
throw;
}
}
/// <summary>
/// 在指定的数据库连接串下根据的存储过程名称、值数组执行获取DataReader的SQL命令
/// 此方法将先根据连接和存储过程名称获得参数数组(从数据库或者缓存),再将值数组分配到参数数组中
/// </summary>
/// <remarks>
/// 举例:
/// SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);
/// </remarks>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="spName">存储过程名称</param>
/// <param name="parameterValues">值数组</param>
/// <returns>返回保存执行结果的DataReader</returns>
public static SqlDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// 值数组不为空
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(connectionString, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// 调用没有参数数组为参数的重载方法
return ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 在给定的数据库连接下根据的类型、文本(存储过程名或者T-SQL命令)执行获取DataReader的SQL命令
/// </summary>
/// <remarks>
/// 举例:
/// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders");
/// </remarks>
/// <param name="connection">数据库连接对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <returns>返回保存执行结果的DataReader</returns>
public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText)
{
// 设定参数数组为null,调用重载方法
return ExecuteReader(connection, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 在给定的数据库连接下根据的类型、文本(存储过程名或者T-SQL命令)和参数数组执行获取DataReader的SQL命令
/// </summary>
/// <remarks>
/// 举例:
/// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">数据库连接对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <param name="commandParameters">参数数组</param>
/// <returns>返回保存执行结果的DataReader</returns>
public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
// 调用重载方法,事务参数为null,指定数据库对象由调用者管理
return ExecuteReader(connection, (SqlTransaction)null, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
}
/// <summary>
/// 在指定的数据库连接下根据的存储过程名称、值数组执行获取DataReader的SQL命令
/// 此方法将先根据连接和存储过程名称获得参数数组(从数据库或者缓存),再将值数组分配到参数数组中
/// </summary>
/// <remarks>
/// 举例:
/// SqlDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36);
/// </remarks>
/// <param name="connection">数据库连接对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="parameterValues">值数组</param>
/// <returns>返回保存执行结果的DataReader</returns>
public static SqlDataReader ExecuteReader(SqlConnection connection, string spName, params object[] parameterValues)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// 值数组不为空
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(connection, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// 调用没有参数数组为参数的重载方法
return ExecuteReader(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 在给定事物的数据库连接下根据的类型、文本(存储过程名或者T-SQL命令)执行获取DataReader的SQL命令
/// </summary>
/// <remarks>
/// 举例:
/// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders");
/// </remarks>
/// <param name="transaction">事物对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <returns>返回保存执行结果的DataReader</returns>
public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText)
{
// 设定参数数组为null,调用重载方法
return ExecuteReader(transaction, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 在给定事物的数据库连接下根据的类型、文本(存储过程名或者T-SQL命令)和参数数组执行获取DataReader的SQL命令
/// </summary>
/// <remarks>
/// 举例:
/// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">事物对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <param name="commandParameters">参数数组</param>
/// <returns>返回保存执行结果的DataReader</returns>
public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
// 调用重载方法,指定数据库对象由调用者管理
return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
}
/// <summary>
/// 在指定事物的数据库连接下根据的存储过程名称、值数组执行获取DataReader的SQL命令
/// 此方法将先根据连接和存储过程名称获得参数数组(从数据库或者缓存),再将值数组分配到参数数组中
/// </summary>
/// <remarks>
/// 举例:
/// SqlDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36);
/// </remarks>
/// <param name="transaction">事物对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="parameterValues">值数组</param>
/// <returns>返回保存执行结果的DataReader</returns>
public static SqlDataReader ExecuteReader(SqlTransaction transaction, string spName, params object[] parameterValues)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// 值数组不为空
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(transaction.Connection, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// 调用没有参数数组为参数的重载方法
return ExecuteReader(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion 执行返回DataReader的SQL命令
#region 执行返回单值的SQL命令
/// <summary>
/// 在给定的数据库连接串下根据的类型、文本(存储过程名或者T-SQL命令)执行获取数据集首行首列上单个数据的SQL命令
/// </summary>
/// <remarks>
/// 举例:
/// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount");
/// </remarks>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <returns>返回首行首列上的数据,类型为Object</returns>
public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)
{
// 设定参数数组为null,调用重载方法
return ExecuteScalar(connectionString, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 在给定的数据库连接串下根据的类型、文本(存储过程名或者T-SQL命令)和参数数组执行获取数据集首行首列上单个数据的SQL命令
/// </summary>
/// <remarks>
/// 举例:
/// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <param name="commandParameters">参数数组</param>
/// <returns>返回首行首列上的数据,类型为Object</returns>
public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
// 创建数据库连接并打开它,使用完后销毁
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// 用数据库连接对象替换参数连接串,调用重载方法
return ExecuteScalar(connection, commandType, commandText, commandParameters);
}
}
/// <summary>
/// 在给定的数据库连接串下根据存储过程名、值数组执行存储过程获取数据集首行首列上单个数据
/// 此方法将先根据连接串和存储过程名称获得参数数组(从数据库或者缓存),再将值数组分配到参数数组中
/// </summary>
/// <remarks>
/// 举例:
/// int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36);
/// </remarks>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="spName">存储过程名称</param>
/// <param name="parameterValues">值数组</param>
/// <returns>返回首行首列上的数据,类型为Object</returns>
public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// 值数组不为空
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(connectionString, spName);
// 值数组分配到参数数组中
AssignParameterValues(commandParameters, parameterValues);
// 用赋值后的参数数组为参数,调用重载方法
return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// 调用没有参数数组为参数的重载方法
return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 在给定的数据库连接下根据类型、文本(存储过程名或者T-SQL命令)执行获取数据集首行首列上单个数据的SQL命令
/// </summary>
/// <remarks>
/// 举例:
/// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount");
/// </remarks>
/// <param name="connection">数据库连接对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <returns>返回首行首列上的数据,类型为Object</returns>
public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText)
{
// 设定参数数组为null,调用重载方法
return ExecuteScalar(connection, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 在给定的数据库连接下根据类型、文本(存储过程名或者T-SQL命令)和参数数组执行获取数据集首行首列上单个数据的SQL命令
/// </summary>
/// <remarks>
/// 举例:
/// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">数据库连接对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <param name="commandParameters">参数数组</param>
/// <returns>返回首行首列上的数据,类型为Object</returns>
public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
// 创建命令对象并且绑定
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = connection.ConnectionTimeout;
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
// 执行命令并得到返回值
object retval = cmd.ExecuteScalar();
// 清除SQL命令对象的参数
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
return retval;
}
/// <summary>
/// 在给定的数据库连接下根据存储过程名、值数组执行存储过程获取数据集首行首列上单个数据
/// 此方法将先根据连接串和存储过程名称获得参数数组(从数据库或者缓存),再将值数组分配到参数数组中
/// </summary>
/// <remarks>
/// 举例:
/// int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36);
/// </remarks>
/// <param name="connection">数据库连接对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="parameterValues">值数组</param>
/// <returns>返回首行首列上的数据,类型为Object</returns>
public static object ExecuteScalar(SqlConnection connection, string spName, params object[] parameterValues)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// 值数组不为空
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(connection, spName);
// 值数组分配到参数数组中
AssignParameterValues(commandParameters, parameterValues);
// 用赋值后的参数数组为参数,调用重载方法
return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// 调用没有参数数组为参数的重载方法
return ExecuteScalar(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 在给定的数据库事务对象的连接下根据类型、文本(存储过程名或者T-SQL命令)
/// 执行获取数据集首行首列上单个数据的SQL命令
/// </summary>
/// <remarks>
/// 举例:
/// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount");
/// </remarks>
/// <param name="transaction">事物对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <returns>返回首行首列上的数据,类型为Object</returns>
public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText)
{
// 设定参数数组为null,调用重载方法
return ExecuteScalar(transaction, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 在给定的数据库事务对象的连接下根据类型、文本(存储过程名或者T-SQL命令)和参数数组
/// 执行获取数据集首行首列上单个数据的SQL命令
/// </summary>
/// <remarks>
/// 举例:
/// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">事物对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <param name="commandParameters">参数数组</param>
/// <returns>返回首行首列上的数据,类型为Object</returns>
public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
// 创建命令对象并且绑定
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = transaction.Connection.ConnectionTimeout;
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// 执行命令并获得返回值
object retval = cmd.ExecuteScalar();
// 清除SQL命令对象的参数
cmd.Parameters.Clear();
return retval;
}
/// <summary>
/// 在给定的数据库事务对象的连接下根据存储过程名、值数组执行存储过程获取数据集首行首列上单个数据
/// 此方法将先根据连接串和存储过程名称获得参数数组(从数据库或者缓存),再将值数组分配到参数数组中
/// </summary>
/// <remarks>
/// 举例:
/// int orderCount = (int)ExecuteScalar(trans, "GetOrderCount", 24, 36);
/// </remarks>
/// <param name="transaction">事物对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="parameterValues">值数组</param>
/// <returns>返回首行首列上的数据,类型为Object</returns>
public static object ExecuteScalar(SqlTransaction transaction, string spName, params object[] parameterValues)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// 值数组不为空
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(transaction.Connection, spName);
// 值数组分配到参数数组中
AssignParameterValues(commandParameters, parameterValues);
// 用赋值后的参数数组为参数,调用重载方法
return ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// 调用没有参数数组为参数的重载方法
return ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion 执行返回单值的SQL命令
#region 执行通过XmlReader对象返回数据的SQL命令
/// <summary>
/// 在给定的数据库连接下根据类型、文本(存储过程名或者使用"FOR XML AUTO"的T-SQL命令)执行SQL命令并通过XmlReader对象返回数据
/// </summary>
/// <remarks>
/// 举例:
/// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders");
/// </remarks>
/// <param name="connection">数据库连接对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者使用"FOR XML AUTO"的T-SQL命令)</param>
/// <returns>返回XmlReader数据对象</returns>
public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText)
{
// 设定参数数组为null,调用重载方法
return ExecuteXmlReader(connection, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 在给定的数据库连接下根据类型、文本(存储过程名或者使用"FOR XML AUTO"的T-SQL命令)和参数数组执行SQL命令并通过XmlReader对象返回数据
/// </summary>
/// <remarks>
/// 举例:
/// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">数据库连接对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者使用"FOR XML AUTO"的T-SQL命令)</param>
/// <param name="commandParameters">参数数组</param>
/// <returns>返回XmlReader数据对象</returns>
public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
bool mustCloseConnection = false;
// 创建命令对象并且绑定
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = connection.ConnectionTimeout;
try
{
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
// 执行命令并获得返回值
XmlReader retval = cmd.ExecuteXmlReader();
// 清除SQL命令对象的参数
cmd.Parameters.Clear();
return retval;
}
catch
{
if (mustCloseConnection)
connection.Close();
throw;
}
}
/// <summary>
/// 在给定的数据库连接下根据存储过程名、值数组执行存储过程并通过XmlReader对象返回数据
/// 此方法将先根据连接串和存储过程名称获得参数数组(从数据库或者缓存),再将值数组分配到参数数组中
/// </summary>
/// <remarks>
/// 举例:
/// XmlReader r = ExecuteXmlReader(conn, "GetOrders", 24, 36);
/// </remarks>
/// <param name="connection">数据库连接对象</param>
/// <param name="spName">使用"FOR XML AUTO"的存储过程名称</param>
/// <param name="parameterValues">值数组</param>
/// <returns>返回XmlReader数据对象</returns>
public static XmlReader ExecuteXmlReader(SqlConnection connection, string spName, params object[] parameterValues)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// 值数组不为空
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(connection, spName);
// 值数组分配到参数数组中
AssignParameterValues(commandParameters, parameterValues);
// 用赋值后的参数数组为参数,调用重载方法
return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// 调用没有参数数组为参数的重载方法
return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 在给定的数据库事物对象的连接下根据类型、文本(存储过程名或者使用"FOR XML AUTO"的T-SQL命令)执行SQL命令并通过XmlReader对象返回数据
/// </summary>
/// <remarks>
/// 举例:
/// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders");
/// </remarks>
/// <param name="transaction">事物对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者使用"FOR XML AUTO"的T-SQL命令)</param>
/// <returns>返回XmlReader数据对象</returns>
public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText)
{
// 设定参数数组为null,调用重载方法
return ExecuteXmlReader(transaction, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 在给定的数据库事物对象的连接下根据类型、文本(存储过程名或者使用"FOR XML AUTO"的T-SQL命令)和参数数组执行SQL命令并通过XmlReader对象返回数据
/// </summary>
/// <remarks>
/// 举例:
/// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">事物对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者使用"FOR XML AUTO"的T-SQL命令)</param>
/// <param name="commandParameters">参数数组</param>
/// <returns>返回XmlReader数据对象</returns>
public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
// 创建命令对象并且绑定
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = transaction.Connection.ConnectionTimeout;
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// 执行命令并获得返回值
XmlReader retval = cmd.ExecuteXmlReader();
// 清除SQL命令对象的参数
cmd.Parameters.Clear();
return retval;
}
/// <summary>
/// 在给定的数据库事物对象的连接下根据存储过程名、值数组执行存储过程并通过XmlReader对象返回数据
/// 此方法将先根据连接串和存储过程名称获得参数数组(从数据库或者缓存),再将值数组分配到参数数组中
/// </summary>
/// <remarks>
/// 举例:
/// XmlReader r = ExecuteXmlReader(trans, "GetOrders", 24, 36);
/// </remarks>
/// <param name="transaction">事物对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="parameterValues">值数组</param>
/// <returns>返回XmlReader数据对象</returns>
public static XmlReader ExecuteXmlReader(SqlTransaction transaction, string spName, params object[] parameterValues)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// 值数组不为空
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(transaction.Connection, spName);
// 值数组分配到参数数组中
AssignParameterValues(commandParameters, parameterValues);
// 用赋值后的参数数组为参数,调用重载方法
return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// 调用没有参数数组为参数的重载方法
return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion 执行通过XmlReader对象返回数据的SQL命令
#region 填充数据集
/// <summary>
/// 在指定的数据库连接字符串下根据类型、文本(存储过程名或者T-SQL命令)、参数数组和表名数组执行存储过程填充数据集
/// </summary>
/// <remarks>
/// 举例:
/// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
/// </remarks>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <param name="dataSet">将要填充数据的数据集</param>
/// <param name="tableNames">将要填充或者增加的表名(只对数组的第一个表名有效)</param>
public static void FillDataset(string connectionString, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (dataSet == null) throw new ArgumentNullException("dataSet");
// 创建数据库连接并打开,使用完销毁
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// 用数据库连接对象替换参数连接串,调用重载方法
FillDataset(connection, commandType, commandText, dataSet, tableNames);
}
}
/// <summary>
/// 在指定的数据库连接字符串下根据类型、文本(存储过程名或者T-SQL命令)、参数数组和表名数组执行存储过程填充数据集
/// </summary>
/// <remarks>
/// 举例:
/// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <param name="commandParameters">参数数组</param>
/// <param name="dataSet">将要填充数据的数据集</param>
/// <param name="tableNames">将要填充或者增加的表名(只对数组的第一个表名有效)</param>
public static void FillDataset(string connectionString, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames,
params SqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (dataSet == null) throw new ArgumentNullException("dataSet");
// 创建数据库连接并打开,使用完销毁
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// 用数据库连接对象替换参数连接串,调用重载方法
FillDataset(connection, commandType, commandText, dataSet, tableNames, commandParameters);
}
}
/// <summary>
/// 在指定事务的数据库连接串下根据的存储过程名称、值数组执行的存储过程填充数据集
/// 此方法将先根据连接和存储过程名称获得参数数组(从数据库或者缓存),再将值数组分配到参数数组中
/// </summary>
/// <remarks>
/// 举例:
/// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, 24);
/// </remarks>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="spName">存储过程名称</param>
/// <param name="dataSet">将要填充数据的数据集</param>
/// <param name="tableNames">将要填充或者增加的表名(只对数组的第一个表名有效)</param>
/// <param name="parameterValues">值数组</param>
public static void FillDataset(string connectionString, string spName,
DataSet dataSet, string[] tableNames,
params object[] parameterValues)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (dataSet == null) throw new ArgumentNullException("dataSet");
// 创建数据库连接并打开,使用完销毁
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// 用数据库连接对象替换参数连接串,调用重载方法
FillDataset(connection, spName, dataSet, tableNames, parameterValues);
}
}
/// <summary>
/// 在指定的数据库连接下根据类型、文本(存储过程名或者T-SQL命令)、表名数组执行存储过程填充数据集
/// </summary>
/// <remarks>
/// 举例:
/// FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
/// </remarks>
/// <param name="connection">数据库连接对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <param name="dataSet">将要填充数据的数据集</param>
/// <param name="tableNames">将要填充或者增加的表名(只对数组的第一个表名有效)</param>
public static void FillDataset(SqlConnection connection, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames)
{
FillDataset(connection, commandType, commandText, dataSet, tableNames, null);
}
/// <summary>
/// 在指定的数据库连接下根据类型、文本(存储过程名或者T-SQL命令)、表名数组和参数数组执行存储过程填充数据集
/// </summary>
/// <remarks>
/// 举例:
/// FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">数据库连接对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <param name="dataSet">将要填充数据的数据集</param>
/// <param name="tableNames">将要填充或者增加的表名(只对数组的第一个表名有效)</param>
/// <param name="commandParameters">参数数组</param>
public static void FillDataset(SqlConnection connection, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames,
params SqlParameter[] commandParameters)
{
FillDataset(connection, null, commandType, commandText, dataSet, tableNames, commandParameters);
}
/// <summary>
/// 在指定事务的数据库连接下根据的存储过程名称、值数组执行的存储过程填充数据集
/// 此方法将先根据连接和存储过程名称获得参数数组(从数据库或者缓存),再将值数组分配到参数数组中
/// </summary>
/// <remarks>
/// 举例:
/// FillDataset(conn, "GetOrders", ds, new string[] {"orders"}, 24, 36);
/// </remarks>
/// <param name="connection">数据库连接对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="dataSet">将要填充数据的数据集</param>
/// <param name="tableNames">将要填充或者增加的表名(只对数组的第一个表名有效)</param>
/// <param name="parameterValues">值数组</param>
public static void FillDataset(SqlConnection connection, string spName,
DataSet dataSet, string[] tableNames,
params object[] parameterValues)
{
if (connection == null) throw new ArgumentNullException("connection");
if (dataSet == null) throw new ArgumentNullException("dataSet");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// 值数组不为空
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(connection, spName);
// 值数组分配到参数数组中
AssignParameterValues(commandParameters, parameterValues);
// 用赋值后的参数数组为参数,调用重载方法
FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
}
else
{
// 调用没有参数数组为参数的重载方法
FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames);
}
}
/// <summary>
/// 在指定事务的数据库连接下根据类型、文本(存储过程名或者T-SQL命令)、表名数组执行存储过程填充数据集
/// </summary>
/// <remarks>
/// 举例:
/// FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
/// </remarks>
/// <param name="transaction">事物对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <param name="dataSet">将要填充数据的数据集</param>
/// <param name="tableNames">将要填充或者增加的表名(只对数组的第一个表名有效)</param>
public static void FillDataset(SqlTransaction transaction, CommandType commandType,
string commandText,
DataSet dataSet, string[] tableNames)
{
FillDataset(transaction, commandType, commandText, dataSet, tableNames, null);
}
/// <summary>
/// 在指定事务的数据库连接下根据类型、文本(存储过程名或者T-SQL命令)、参数数组和表名数组执行的存储过程填充数据集
/// </summary>
/// <remarks>
/// 举例:
/// FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">事物对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <param name="dataSet">将要填充数据的数据集</param>
/// <param name="tableNames">将要填充或者增加的表名(只对数组的第一个表名有效)</param>
/// <param name="commandParameters">参数数组</param>
public static void FillDataset(SqlTransaction transaction, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames,
params SqlParameter[] commandParameters)
{
FillDataset(transaction.Connection, transaction, commandType, commandText, dataSet, tableNames, commandParameters);
}
/// <summary>
/// 在指定事务的数据库连接下根据存储过程名称、值数组和表名数组执行的存储过程填充数据集
/// 此方法将先根据连接和存储过程名称获得参数数组(从数据库或者缓存),再将值数组分配到参数数组中
/// </summary>
/// <remarks>
/// 举例:
/// FillDataset(trans, "GetOrders", ds, new string[]{"orders"}, 24, 36);
/// </remarks>
/// <param name="transaction">事物对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="dataSet">将要填充数据的数据集</param>
/// <param name="tableNames">将要填充或者增加的表名(只对数组的第一个表名有效)</param>
/// <param name="parameterValues">值数组</param>
public static void FillDataset(SqlTransaction transaction, string spName,
DataSet dataSet, string[] tableNames,
params object[] parameterValues)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
if (dataSet == null) throw new ArgumentNullException("dataSet");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// 值数组不为空
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(transaction.Connection, spName);
// 值数组分配到参数数组中
AssignParameterValues(commandParameters, parameterValues);
// 用赋值后的参数数组为参数,调用重载方法
FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
}
else
{
// 调用没有参数数组为参数的重载方法
FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames);
}
}
/// <summary>
/// 在给定的事务和连接下根据类型、文本(存储过程名或者T-SQL命令)、自定义表名数组和参数数组对给定的数据集进行数据填充
/// </summary>
/// <remarks>
/// 举例:
/// FillDataset(conn, trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">数据库连接对象</param>
/// <param name="transaction">事物对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本(存储过程名或者T-SQL命令)</param>
/// <param name="dataSet">将要填充数据的数据集</param>
/// <param name="tableNames">将要填充或者增加的表名(只对数组的第一个表名有效)</param>
/// <param name="commandParameters">参数数组</param>
private static void FillDataset(SqlConnection connection, SqlTransaction transaction, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames,
params SqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
if (dataSet == null) throw new ArgumentNullException("dataSet");
// 创建SQL命令对象并绑定
SqlCommand command = new SqlCommand();
command.CommandTimeout = connection.ConnectionTimeout;
bool mustCloseConnection = false;
PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// 创建数据适配器,使用完后销毁
using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
{
// 用自定义表名与适配器将要自动创建的表建立映射
if (tableNames != null && tableNames.Length > 0)
{
string tableName = "Table";
for (int index = 0; index < tableNames.Length; index++)
{
if (tableNames[index] == null || tableNames[index].Length == 0) throw new ArgumentException("The tableNames parameter must contain a list of tables, a value was provided as null or empty string.", "tableNames");
dataAdapter.TableMappings.Add(tableName, tableNames[index]);
tableName += (index + 1).ToString();
}
}
// 填充数据集
dataAdapter.Fill(dataSet);
// 清除SQL命令对象的参数
command.Parameters.Clear();
}
if (mustCloseConnection)
connection.Close();
}
public static DataSet GetDataset(SqlConnection cn, string SqlStr)
{
if (cn == null) throw new ArgumentNullException("connection");
SqlCommand cmd = new SqlCommand(SqlStr, cn);
cmd.CommandTimeout = cn.ConnectionTimeout;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
da = null;
return ds;
}
#endregion 填充数据集
#region 更新数据集
/// <summary>
/// 用的inserted, updated, or deleted命令更新数据集源表中相应的行
/// </summary>
/// <remarks>
/// 举例:
/// UpdateDataset(insertCommand, deleteCommand, updateCommand, dataSet, "Order");
/// </remarks>
/// <param name="insertCommand">InsertSQL命令对象</param>
/// <param name="deleteCommand">DeleteSQL命令对象</param>
/// <param name="updateCommand">UpdateSQL命令对象</param>
/// <param name="dataSet">将要更新的数据集</param>
/// <param name="tableName">将要更新数据集的源表</param>
public static void UpdateDataset(SqlCommand insertCommand, SqlCommand deleteCommand, SqlCommand updateCommand, DataSet dataSet, string tableName)
{
if (insertCommand == null) throw new ArgumentNullException("insertCommand");
if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
if (updateCommand == null) throw new ArgumentNullException("updateCommand");
if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");
// 创建数据适配器,使用完后销毁
using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
{
// 将SQL命令绑定到适配器
dataAdapter.UpdateCommand = updateCommand;
dataAdapter.InsertCommand = insertCommand;
dataAdapter.DeleteCommand = deleteCommand;
// 更新数据集
dataAdapter.Update(dataSet, tableName);
// 接收数据集变化
dataSet.AcceptChanges();
}
}
/// <summary>
/// Lenovo Add By peter on 030911
/// 用的inserted, updated, or deleted命令更新数据集源表中相应的行
/// 数据集中其他表并不接受变更
/// </summary>
/// <remarks>
/// 举例:
/// UpdateDataset(insertCommand, deleteCommand, updateCommand, dataSet, "Order");
/// </remarks>
/// <param name="insertCommand">InsertSQL命令对象</param>
/// <param name="deleteCommand">DeleteSQL命令对象</param>
/// <param name="updateCommand">UpdateSQL命令对象</param>
/// <param name="dataSet">将要更新的数据集</param>
/// <param name="tableName">将要更新数据集的源表</param>
public static void UpdateDatasetNoAccept(SqlCommand insertCommand, SqlCommand deleteCommand, SqlCommand updateCommand, DataSet dataSet, string tableName)
{
if (insertCommand == null) throw new ArgumentNullException("insertCommand");
if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
if (updateCommand == null) throw new ArgumentNullException("updateCommand");
if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");
// 创建数据适配器,使用完后销毁
using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
{
// 将SQL命令绑定到适配器
dataAdapter.UpdateCommand = updateCommand;
dataAdapter.InsertCommand = insertCommand;
dataAdapter.DeleteCommand = deleteCommand;
// 更新数据集
dataAdapter.Update(dataSet, tableName);
}
}
/// <summary>
/// Lenovo Add By peter on 030911
/// 根据数据库连接和查询语句将更新的数据集提交到数据库
/// 数据集中其他表并不接受变更
/// </summary>
/// <param name="sqlConn">数据库连接对象</param>
/// <param name="strCommandTextArray">创建数据表的查询命令文本</param>
/// <param name="dsUpdate">将要更新的数据集</param>
/// <param name="strTableName">将要更新数据集的源表</param>
public static void UpdateDatasetNoAccept(SqlConnection sqlConn, string strQueryCmdText, DataSet dsUpdate, string strTableName)
{
if (sqlConn == null) throw new ArgumentNullException("connection");
if (strQueryCmdText == null || strQueryCmdText.Length == 0) throw new ArgumentNullException("SelectCommand");
if (strTableName == null || strTableName.Length == 0) throw new ArgumentNullException("tableName");
// 根据数据库连接和查询语句创建数据适配器,使用完后销毁
using (SqlDataAdapter sqlAdpt = new SqlDataAdapter(strQueryCmdText, sqlConn))
{
// 创建SqlCommandBuilder对象来自动生成INSERT、UPDATE 或 DELETE命令
sqlAdpt.MissingSchemaAction = MissingSchemaAction.Add;
SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(sqlAdpt);
// 更新数据集
sqlAdpt.Update(dsUpdate, strTableName);
}
}
/// <summary>
/// Lenovo Add By peter on 030911
/// 根据事务对象和查询语句将更新的数据集提交到数据库
/// 数据集中其他表并不接受变更
/// </summary>
/// <param name="sqlTrans">事务对象</param>
/// <param name="strCommandTextArray">创建数据表的查询命令文本</param>
/// <param name="dsUpdate">将要更新的数据集</param>
/// <param name="strTableName">将要更新数据集的源表</param>
public static void UpdateDatasetNoAccept(SqlTransaction sqlTrans, string strQueryCmdText, DataSet dsUpdate, string strTableName)
{
if (sqlTrans == null) throw new ArgumentNullException("SqlTransaction");
if (strQueryCmdText == null || strQueryCmdText.Length == 0) throw new ArgumentNullException("SelectCommand");
if (strTableName == null || strTableName.Length == 0) throw new ArgumentNullException("tableName");
// 根据数据库连接和查询语句创建数据适配器,使用完后销毁
using (SqlDataAdapter sqlAdpt = new SqlDataAdapter(strQueryCmdText, sqlTrans.Connection))
{
// 添加事物对象
sqlAdpt.SelectCommand.Transaction = sqlTrans;
// 创建SqlCommandBuilder对象来自动生成INSERT、UPDATE 或 DELETE命令
sqlAdpt.MissingSchemaAction = MissingSchemaAction.Add;
SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(sqlAdpt);
// 更新数据集
sqlAdpt.Update(dsUpdate, strTableName);
}
}
#endregion 更新数据集
#region 更新数据表
/// <summary>
/// Lenovo Add By peter on 030911
/// 用的inserted, updated, or deleted命令更新数据表中相应的行
/// </summary>
/// <remarks>
/// 举例:
/// UpdateDatatable(insertCommand, deleteCommand, updateCommand, dataSet.Tables["Order"]);
/// </remarks>
/// <param name="insertCommand">InsertSQL命令对象</param>
/// <param name="deleteCommand">DeleteSQL命令对象</param>
/// <param name="updateCommand">UpdateSQL命令对象</param>
/// <param name="dt">将要更新的数据表</param>
public static void UpdateDatatable(SqlCommand insertCommand, SqlCommand deleteCommand, SqlCommand updateCommand, DataTable dtUpdate)
{
if (insertCommand == null) throw new ArgumentNullException("insertCommand");
if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
if (updateCommand == null) throw new ArgumentNullException("updateCommand");
// 创建数据适配器,使用完后销毁
using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
{
// 将SQL命令绑定到适配器
dataAdapter.UpdateCommand = updateCommand;
dataAdapter.InsertCommand = insertCommand;
dataAdapter.DeleteCommand = deleteCommand;
// 更新数据集
dataAdapter.Update(dtUpdate);
}
}
/// <summary>
/// Lenovo Add By peter on 031105
/// 根据数据库连接和查询语句将更新的数据表提交到数据库
/// </summary>
/// <param name="sqlConn">数据库连接对象</param>
/// <param name="strCommandTextArray">创建数据表的查询命令文本</param>
/// <param name="dtUpdate">将要更新的数据表</param>
public static void UpdateDatatable(SqlConnection sqlConn, string strQueryCmdText, DataTable dtUpdate)
{
if (sqlConn == null) throw new ArgumentNullException("sqlConn");
if (strQueryCmdText == null || strQueryCmdText.Length == 0) throw new ArgumentNullException("strQueryCmdText");
// 根据数据库连接和查询语句创建数据适配器,使用完后销毁
using (SqlDataAdapter sqlAdpt = new SqlDataAdapter(strQueryCmdText, sqlConn))
{
// 创建SqlCommandBuilder对象来自动生成INSERT、UPDATE 或 DELETE命令
sqlAdpt.MissingSchemaAction = MissingSchemaAction.Add;
SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(sqlAdpt);
// 更新数据表
sqlAdpt.Update(dtUpdate);
}
}
/// <summary>
/// Lenovo Add By peter on 031105
/// 根据事务对象和查询语句将更新的数据表提交到数据库
/// </summary>
/// <param name="sqlTrans">事务对象</param>
/// <param name="strCommandTextArray">创建数据表的查询命令文本</param>
/// <param name="dtUpdate">将要更新的数据表</param>
public static void UpdateDatatable(SqlTransaction sqlTrans, string strQueryCmdText, DataTable dtUpdate)
{
if (sqlTrans == null) throw new ArgumentNullException("sqlTrans");
if (strQueryCmdText == null || strQueryCmdText.Length == 0) throw new ArgumentNullException("strQueryCmdText");
// 根据数据库连接和查询语句创建数据适配器,使用完后销毁
using (SqlDataAdapter sqlAdpt = new SqlDataAdapter(strQueryCmdText, sqlTrans.Connection))
{
// 添加事物对象
sqlAdpt.SelectCommand.Transaction = sqlTrans;
// 创建SqlCommandBuilder对象来自动生成INSERT、UPDATE 或 DELETE命令
sqlAdpt.MissingSchemaAction = MissingSchemaAction.Add;
SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(sqlAdpt);
// 更新数据表
sqlAdpt.Update(dtUpdate);
}
}
#endregion 更新数据表
#region 创建SQL命令对象
/// <summary>
/// 根据存储过程名称和SourceColumn数组,在给定的数据库连接下构造SqlCommand
/// </summary>
/// <remarks>
/// 举例:
/// SqlCommand command = CreateCommand(conn, "AddCustomer", "CustomerID", "CustomerName");
/// </remarks>
/// <param name="connection">数据库连接对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="sourceColumns">存储过程参数名数组</param>
/// <returns>SQL命令对象</returns>
public static SqlCommand CreateCommand(SqlConnection connection, string spName, params string[] sourceColumns)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// 创建SQL命令对象
SqlCommand cmd = new SqlCommand(spName, connection);
cmd.CommandTimeout = connection.ConnectionTimeout;
cmd.CommandType = CommandType.StoredProcedure;
// 存储过程字段名数组不为空
if ((sourceColumns != null) && (sourceColumns.Length > 0))
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(connection, spName);
// 存储过程参数名数组分配到参数数组中
for (int index = 0; index < sourceColumns.Length; index++)
commandParameters[index].SourceColumn = sourceColumns[index];
// 将参数数组绑定到SQL命令对象上
AttachParameters(cmd, commandParameters);
}
return cmd;
}
/// <summary>
/// Lenovo Add By peter on 030911
/// 根据存储过程名称和SourceColumn数组,在给定的事务下构造SqlCommand
/// </summary>
/// <param name="connection">数据库事务对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="sourceColumns">存储过程参数名数组</param>
/// <returns>SQL命令对象</returns>
public static SqlCommand CreateCommand(SqlTransaction sqlTrans, string spName, params string[] sourceColumns)
{
if (sqlTrans == null) throw new ArgumentNullException("SqlTransaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// 创建SQL命令对象
SqlCommand cmd = new SqlCommand(spName, sqlTrans.Connection, sqlTrans);
cmd.CommandTimeout = sqlTrans.Connection.ConnectionTimeout;
cmd.CommandType = CommandType.StoredProcedure;
// 存储过程字段名数组不为空
if ((sourceColumns != null) && (sourceColumns.Length > 0))
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(sqlTrans.Connection, spName);
// 存储过程参数名数组分配到参数数组中
for (int index = 0; index < sourceColumns.Length; index++)
commandParameters[index].SourceColumn = sourceColumns[index];
// 将参数数组绑定到SQL命令对象上
AttachParameters(cmd, commandParameters);
}
return cmd;
}
/// <summary>
/// Lenovo Add By peter on 030911
/// 根据SQL语句,在给定的数据库连接下构造SqlCommand(SelectCommand、InsertCommand、UpdateCommand、DeleteCommand)
/// </summary>
/// <param name="sqlConn">数据库连接对象</param>
/// <param name="commandText">构造SqlCommand用的SQL语句(Select、Insert、Update、Delete)</param>
/// <param name="sqlParameter">SqlCommand的相关参数SqlParameter数组</param>
/// <returns>返回SqlCommand命令对象</returns>
public static SqlCommand CreateCommand(SqlConnection sqlConn, string strCommandText, SqlParameter[] sqlParaArray)
{
if (sqlConn == null) throw new ArgumentNullException("connection");
if (strCommandText == null || strCommandText.Length == 0) throw new ArgumentNullException("CommandText");
// 创建SQL命令对象
SqlCommand sqlCmd = new SqlCommand(strCommandText, sqlConn);
sqlCmd.CommandTimeout = sqlConn.ConnectionTimeout;
sqlCmd.CommandType = CommandType.Text;
// 将参数数组绑定到SQL命令对象上
AttachParameters(sqlCmd, sqlParaArray);
return sqlCmd;
}
/// <summary>
/// Lenovo Add By peter on 030911
/// 根据SQL语句,在给定的事务下构造SqlCommand(SelectCommand、InsertCommand、UpdateCommand、DeleteCommand)
/// </summary>
/// <param name="sqlTrans">数据库事务对象</param>
/// <param name="commandText">构造SqlCommand用的SQL语句(Select、Insert、Update、Delete)</param>
/// <param name="sqlParameter">SqlCommand的相关参数SqlParameter数组</param>
/// <returns>返回SqlCommand命令对象</returns>
public static SqlCommand CreateCommand(SqlTransaction sqlTrans, string strCommandText, SqlParameter[] sqlParaArray)
{
if (sqlTrans == null) throw new ArgumentNullException("SqlTransaction");
if (strCommandText == null || strCommandText.Length == 0) throw new ArgumentNullException("CommandText");
// 创建SQL命令对象
SqlCommand sqlCmd = new SqlCommand(strCommandText, sqlTrans.Connection, sqlTrans);
sqlCmd.CommandTimeout = sqlTrans.Connection.ConnectionTimeout;
sqlCmd.CommandType = CommandType.Text;
// 将参数数组绑定到SQL命令对象上
AttachParameters(sqlCmd, sqlParaArray);
return sqlCmd;
}
#endregion 创建命令对象
#region 执行参数类型为DataRow的非查询存储过程
/// <summary>
/// 在给定的数据库的连接串下根据存储过程名、保存参数值的dataRow执行不返回值的存储过程
/// 此方法将先根据连接串和存储过程名称获得参数数组(从数据库或者缓存),再将保存参数值的dataRow分配到参数数组中
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="spName">存储过程名称</param>
/// <param name="dataRow">保存参数值的dataRow</param>
/// <returns>返回存储过程影响的行数</returns>
public static int ExecuteNonQueryTypedParams(String connectionString, String spName, DataRow dataRow)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// dataRow不为空
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(connectionString, spName);
// dataRow分配到参数数组中
AssignParameterValues(commandParameters, dataRow);
return Database.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return Database.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 在给定的数据库的连接下根据存储过程名、保存参数值的dataRow执行不返回值的存储过程
/// 此方法将先根据连接串和存储过程名称获得参数数组(从数据库或者缓存),再将保存参数值的dataRow分配到参数数组中
/// </summary>
/// <param name="connection">数据库连接对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="dataRow">保存参数值的dataRow</param>
/// <returns>返回存储过程影响的行数</returns>
public static int ExecuteNonQueryTypedParams(SqlConnection connection, String spName, DataRow dataRow)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// dataRow不为空
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(connection, spName);
// dataRow分配到参数数组中
AssignParameterValues(commandParameters, dataRow);
return Database.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return Database.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 在给定事务的数据库的连接下根据存储过程名、保存参数值的dataRow执行不返回值的存储过程
/// 此方法将先根据连接串和存储过程名称获得参数数组(从数据库或者缓存),再将保存参数值的dataRow分配到参数数组中
/// </summary>
/// <param name="transaction">事物对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="dataRow">保存参数值的dataRow</param>
/// <returns>返回存储过程影响的行数</returns>
public static int ExecuteNonQueryTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// dataRow不为空
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(transaction.Connection, spName);
// dataRow分配到参数数组中
AssignParameterValues(commandParameters, dataRow);
return Database.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return Database.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion 执行参数类型为DataRow的非查询存储过程
#region 执行参数类型为DataRow返回数据集的存储过程
/// <summary>
/// 在给定的数据库的连接串下根据存储过程名、保存参数值的dataRow执行存储过程获取数据集
/// 此方法将先根据连接串和存储过程名称获得参数数组(从数据库或者缓存),再将保存参数值的dataRow分配到参数数组中
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="spName">存储过程名称</param>
/// <param name="dataRow">保存参数值的dataRow</param>
/// <returns>返回执行存储过程得到的数据集</returns>
public static DataSet ExecuteDatasetTypedParams(string connectionString, String spName, DataRow dataRow)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// dataRow不为空
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(connectionString, spName);
// dataRow分配到参数数组中
AssignParameterValues(commandParameters, dataRow);
return Database.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return Database.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 在给定的数据库的连接下根据存储过程名、保存参数值的dataRow执行存储过程获取数据集
/// 此方法将先根据连接串和存储过程名称获得参数数组(从数据库或者缓存),再将保存参数值的dataRow分配到参数数组中
/// </summary>
/// <param name="connection">数据库连接对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="dataRow">保存参数值的dataRow</param>
/// <returns>返回执行存储过程得到的数据集</returns>
public static DataSet ExecuteDatasetTypedParams(SqlConnection connection, String spName, DataRow dataRow)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// dataRow不为空
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(connection, spName);
// dataRow分配到参数数组中
AssignParameterValues(commandParameters, dataRow);
return Database.ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return Database.ExecuteDataset(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 在给定事务的数据库的连接下根据存储过程名、保存参数值的dataRow执行存储过程获取数据集
/// 此方法将先根据连接串和存储过程名称获得参数数组(从数据库或者缓存),再将保存参数值的dataRow分配到参数数组中
/// </summary>
/// <param name="transaction">事物对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="dataRow">保存参数值的dataRow</param>
/// <returns>返回执行存储过程得到的数据集</returns>
public static DataSet ExecuteDatasetTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// dataRow不为空
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(transaction.Connection, spName);
// dataRow分配到参数数组中
AssignParameterValues(commandParameters, dataRow);
return Database.ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return Database.ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion 执行参数类型为DataRow返回数据集的存储过程
#region 执行参数类型为DataRow返回DataReader的存储过程
/// <summary>
/// 在给定的数据库的连接串下根据存储过程名、保存参数值的dataRow执行存储过程获取DataReader
/// 此方法将先根据连接串和存储过程名称获得参数数组(从数据库或者缓存),再将保存参数值的dataRow分配到参数数组中
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="spName">存储过程名称</param>
/// <param name="dataRow">保存参数值的dataRow</param>
/// <returns>返回执行存储过程获得的DataReader</returns>
public static SqlDataReader ExecuteReaderTypedParams(String connectionString, String spName, DataRow dataRow)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// dataRow不为空
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(connectionString, spName);
// dataRow分配到参数数组中
AssignParameterValues(commandParameters, dataRow);
return Database.ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return Database.ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 在给定的数据库的连接下根据存储过程名、保存参数值的dataRow执行存储过程获取DataReader
/// 此方法将先根据连接串和存储过程名称获得参数数组(从数据库或者缓存),再将保存参数值的dataRow分配到参数数组中
/// </summary>
/// <param name="connection">数据库连接对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="dataRow">保存参数值的dataRow</param>
/// <returns>返回执行存储过程获得的DataReader</returns>
public static SqlDataReader ExecuteReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// dataRow不为空
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(connection, spName);
// dataRow分配到参数数组中
AssignParameterValues(commandParameters, dataRow);
return Database.ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return Database.ExecuteReader(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 在给定事务的数据库的连接下根据存储过程名、保存参数值的dataRow执行存储过程获取DataReader
/// 此方法将先根据连接串和存储过程名称获得参数数组(从数据库或者缓存),再将保存参数值的dataRow分配到参数数组中
/// </summary>
/// <param name="transaction">事物对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="dataRow">保存参数值的dataRow</param>
/// <returns>返回执行存储过程获得的DataReader</returns>
public static SqlDataReader ExecuteReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// dataRow不为空
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(transaction.Connection, spName);
// dataRow分配到参数数组中
AssignParameterValues(commandParameters, dataRow);
return Database.ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return Database.ExecuteReader(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion 执行参数类型为DataRow返回DataReader的存储过程
#region 执行参数类型为DataRow返回单值的存储过程
/// <summary>
/// 在给定的数据库的连接串下根据存储过程名、保存参数值的dataRow执行存储过程获取数据集首行首列上单个数据
/// 此方法将先根据连接串和存储过程名称获得参数数组(从数据库或者缓存),再将保存参数值的dataRow分配到参数数组中
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="spName">存储过程名称</param>
/// <param name="dataRow">保存参数值的dataRow</param>
/// <returns>返回首行首列上的数据,类型为Object</returns>
public static object ExecuteScalarTypedParams(String connectionString, String spName, DataRow dataRow)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// dataRow不为空
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(connectionString, spName);
// dataRow分配到参数数组中
AssignParameterValues(commandParameters, dataRow);
return Database.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return Database.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 在给定的数据库的连接下根据存储过程名、保存参数值的dataRow执行存储过程获取数据集首行首列上单个数据
/// 此方法将先根据连接串和存储过程名称获得参数数组(从数据库或者缓存),再将保存参数值的dataRow分配到参数数组中
/// </summary>
/// <param name="connection">数据库连接对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="dataRow">保存参数值的dataRow</param>
/// <returns>返回首行首列上的数据,类型为Object</returns>
public static object ExecuteScalarTypedParams(SqlConnection connection, String spName, DataRow dataRow)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// dataRow不为空
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(connection, spName);
// dataRow分配到参数数组中
AssignParameterValues(commandParameters, dataRow);
return Database.ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return Database.ExecuteScalar(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 在给定事务的数据库的连接下根据存储过程名、保存参数值的dataRow执行存储过程获取数据集首行首列上单个数据
/// 此方法将先根据连接串和存储过程名称获得参数数组(从数据库或者缓存),再将保存参数值的dataRow分配到参数数组中
/// </summary>
/// <param name="transaction">事物对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="dataRow">保存参数值的dataRow</param>
/// <returns>返回首行首列上的数据,类型为Object</returns>
public static object ExecuteScalarTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// dataRow不为空
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(transaction.Connection, spName);
// dataRow分配到参数数组中
AssignParameterValues(commandParameters, dataRow);
return Database.ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return Database.ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion 执行参数类型为DataRow返回单值的存储过程
#region 执行参数类型为DataRow返回XmlReader的存储过程
/// <summary>
/// 在给定的数据库的连接下根据存储过程名、保存参数值的dataRow执行存储过程获取XmlReader
/// 此方法将先根据连接串和存储过程名称获得参数数组(从数据库或者缓存),再将保存参数值的dataRow分配到参数数组中
/// </summary>
/// <param name="connection">数据库连接对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="dataRow">保存参数值的dataRow</param>
/// <returns>返回保存结果的XmlReader数据对象</returns>
public static XmlReader ExecuteXmlReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// dataRow不为空
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(connection, spName);
// dataRow分配到参数数组中
AssignParameterValues(commandParameters, dataRow);
return Database.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return Database.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 在给定事务的数据库的连接下根据存储过程名、保存参数值的dataRow执行存储过程获取XmlReader
/// 此方法将先根据连接串和存储过程名称获得参数数组(从数据库或者缓存),再将保存参数值的dataRow分配到参数数组中
/// </summary>
/// <param name="transaction">事物对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="dataRow">保存参数值的dataRow</param>
/// <returns>返回保存结果的XmlReader数据对象</returns>
public static XmlReader ExecuteXmlReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// dataRow不为空
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// 根据数据库连接和存储过程名称获得参数数组(从数据库或者缓存)
SqlParameter[] commandParameters = DatabaseParameterCache.GetSpParameterSet(transaction.Connection, spName);
// dataRow分配到参数数组中
AssignParameterValues(commandParameters, dataRow);
return Database.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return Database.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion 执行参数类型为DataRow返回XmlReader的存储过程
#region Make SqlParameters
/// <summary>
/// Make input param.
/// </summary>
/// <param name="ParamName">Name of param.</param>
/// <param name="DbType">Param type.</param>
/// <param name="Size">Param size.</param>
/// <param name="Value">Param value.</param>
/// <returns>New parameter.</returns>
public static SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
}
/// <summary>
/// Make input param.
/// </summary>
/// <param name="ParamName">Name of param.</param>
/// <param name="DbType">Param type.</param>
/// <param name="Size">Param size.</param>
/// <returns>New parameter.</returns>
public static SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, "");
}
/// <summary>
/// Make stored procedure param.
/// </summary>
/// <param name="ParamName">Name of param.</param>
/// <param name="DbType">Param type.</param>
/// <param name="Size">Param size.</param>
/// <param name="Direction">Parm direction.</param>
/// <param name="Value">Param value.</param>
/// <returns>New parameter.</returns>
public static SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
{
SqlParameter param;
if (Size > 0)
param = new SqlParameter(ParamName, DbType, Size);
else
param = new SqlParameter(ParamName, DbType);
param.Direction = Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
param.Value = Value;
return param;
}
#endregion
#region Make SqlParameters
/// <summary>
/// Make input param.
/// </summary>
/// <param name="ParamName">Name of param.</param>
/// <param name="DbType">Param type.</param>
/// <param name="Size">Param size.</param>
/// <param name="Value">Param value.</param>
/// <returns>New parameter.</returns>
public static SqlParameter MakeInParam(string ParamName, object Value)
{
return MakeParam(ParamName, ParameterDirection.Input, Value);
}
/// <summary>
/// Make input param.
/// </summary>
/// <param name="ParamName">Name of param.</param>
/// <param name="DbType">Param type.</param>
/// <param name="Size">Param size.</param>
/// <returns>New parameter.</returns>
public static SqlParameter MakeOutParam(string ParamName)
{
return MakeParam(ParamName, ParameterDirection.Output, "");
}
/// <summary>
/// Make stored procedure param.
/// </summary>
/// <param name="ParamName">Name of param.</param>
/// <param name="DbType">Param type.</param>
/// <param name="Size">Param size.</param>
/// <param name="Direction">Parm direction.</param>
/// <param name="Value">Param value.</param>
/// <returns>New parameter.</returns>
public static SqlParameter MakeParam(string ParamName, ParameterDirection Direction, object Value)
{
SqlParameter param;
param = new SqlParameter(ParamName, Value);
param.Direction = Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
param.Value = Value;
return param;
}
#endregion
}
/// <summary>
/// 存储过程参数数组缓存、获取类
/// </summary>
public sealed class DatabaseParameterCache
{
#region 私有方法 变量 构造函数
//此类只提供静态方法,构造函数应当是私有的
private DatabaseParameterCache() { }
//在哈希表中静态保存的存储过程参数数组
private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
/// <summary>
/// 根据给定的数据库连接和存储过程名查询数据库名称获取参数数组信息
/// </summary>
/// <param name="connection">数据库连接对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="includeReturnValueParameter">返回</param>
/// <returns>返回存储过程参数数组</returns>
private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
SqlCommand cmd = new SqlCommand(spName, connection);
cmd.CommandTimeout = connection.ConnectionTimeout;
cmd.CommandType = CommandType.StoredProcedure;
connection.Open();
SqlCommandBuilder.DeriveParameters(cmd);
connection.Close();
if (!includeReturnValueParameter)
{
cmd.Parameters.RemoveAt(0);
}
SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];
cmd.Parameters.CopyTo(discoveredParameters, 0);
// 初始化数组中每个参数值为DBNull
foreach (SqlParameter discoveredParameter in discoveredParameters)
{
discoveredParameter.Value = DBNull.Value;
}
return discoveredParameters;
}
/// <summary>
/// 全部复制参数数组
/// </summary>
/// <param name="originalParameters">参数数组源</param>
/// <returns>复制的参数数组</returns>
private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
{
SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];
for (int i = 0, j = originalParameters.Length; i < j; i++)
{
clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
}
return clonedParameters;
}
#endregion 私有方法 变量 构造函数
#region 缓存方法
/// <summary>
/// 以数据库连接串和存储过程名称为键值将存储过程参数数组保存到缓存中(静态哈希表)
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="commandText">存储过程名称</param>
/// <param name="commandParameters">存储过程参数数组</param>
public static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
string hashKey = connectionString + ":" + commandText;
paramCache[hashKey] = commandParameters;
}
/// <summary>
/// 根据数据库连接字符串和存储过程名从缓存中获得参数数组
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="commandText">存储过程名称</param>
/// <returns>返回存储过程参数数组</returns>
public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
string hashKey = connectionString + ":" + commandText;
SqlParameter[] cachedParameters = paramCache[hashKey] as SqlParameter[];
if (cachedParameters == null)
{
return null;
}
else
{
return CloneParameters(cachedParameters);
}
}
#endregion 缓存方法
#region 存储过程参数数组获取方法
/// <summary>
/// 根据数据库连接字符串和存储过程名称查找存储过程参数列表信息
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="spName">存储过程名称</param>
/// <returns>返回存储过程参数数组</returns>
public static SqlParameter[] GetSpParameterSet(string connectionString, string spName)
{
return GetSpParameterSet(connectionString, spName, false);
}
/// <summary>
/// 根据数据库连接串创建连接后再根据存储过程名称在数据库查找存储过程参数列表信息
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="spName">存储过程名称</param>
/// <param name="includeReturnValueParameter">返回型参数(OUT/IN OUT)是否包含在返回结果中的标志</param>
/// <returns>返回存储过程参数数组</returns>
public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
using (SqlConnection connection = new SqlConnection(connectionString))
{
return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
}
}
/// <summary>
/// 根据数据库连接和存储过程名称库查找存储过程参数列表信息
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="spName">存储过程名称</param>
/// <returns>返回存储过程参数数组</returns>
public static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName)
{
return GetSpParameterSet(connection, spName, false);
}
/// <summary>
/// 根据给定的数据库连接创建备份并在备份连接下根据存储过程名称在数据库查找存储过程参数列表信息
/// </summary>
/// <param name="connection">数据库连接对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="includeReturnValueParameter">返回型参数(OUT/IN OUT)是否包含在返回结果中的标志</param>
/// <returns>返回存储过程参数数组</returns>
public static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null) throw new ArgumentNullException("connection");
using (SqlConnection clonedConnection = (SqlConnection)((ICloneable)connection).Clone())
{
return GetSpParameterSetInternal(clonedConnection, spName, includeReturnValueParameter);
}
}
/// <summary>
/// 在指定数据库连接下根据存储过程名称在数据库查找存储过程参数列表信息
/// 并且将此信息保存在缓存中以备后用
/// </summary>
/// <param name="connection">数据库连接对象</param>
/// <param name="spName">存储过程名称</param>
/// <param name="includeReturnValueParameter">返回型参数(OUT/IN OUT)是否包含在返回结果中的标志</param>
/// <returns>返回存储过程参数数组</returns>
private static SqlParameter[] GetSpParameterSetInternal(SqlConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// 用数据库连接字符串和一个标识(是否包含返回结果)作为哈希表键值
string hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");
SqlParameter[] cachedParameters;
// 如果哈希表键值存在于缓存中,则从缓存中取得参数数组,否则将连接数据库查找相关信息
cachedParameters = paramCache[hashKey] as SqlParameter[];
if (cachedParameters == null)
{
SqlParameter[] spParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter);
paramCache[hashKey] = spParameters; //将结果缓存于哈希表中
cachedParameters = spParameters;
}
return CloneParameters(cachedParameters);
}
#endregion 存储过程参数数组获取方法
}
}