C# 數據操作工具類，
CREATE PROCEDURE [dbo].[RecordFromPage]
@SelectList VARCHAR(max),
@TableSource VARCHAR(100),
@SearchCondition VARCHAR(max),
@OrderExpression VARCHAR(1000),
@PageIndex INT = 1,
@PageSize INT = 10,
@TotalCount int output
AS
BEGIN
IF @SelectList IS NULL OR LTRIM(RTRIM(@SelectList)) = ''
BEGIN
SET @SelectList = '*'
END
PRINT @SelectList
SET @SearchCondition = ISNULL(@SearchCondition,'')
SET @SearchCondition = LTRIM(RTRIM(@SearchCondition))
IF @SearchCondition <> ''
BEGIN
IF UPPER(SUBSTRING(@SearchCondition,1,5)) <> 'WHERE'
BEGIN
SET @SearchCondition = 'WHERE ' + @SearchCondition
END
END
PRINT @SearchCondition
SET @OrderExpression = ISNULL(@OrderExpression,'')
SET @OrderExpression = LTRIM(RTRIM(@OrderExpression))
IF @OrderExpression <> ''
BEGIN
IF UPPER(SUBSTRING(@OrderExpression,1,5)) <> 'WHERE'
BEGIN
SET @OrderExpression = 'ORDER BY ' + @OrderExpression
END
END
PRINT @OrderExpression
IF @PageIndex IS NULL OR @PageIndex < 1
BEGIN
SET @PageIndex = 1
END
PRINT @PageIndex
IF @PageSize IS NULL OR @PageSize < 1
BEGIN
SET @PageSize = 10
END
PRINT @PageSize
DECLARE @Total int
DECLARE @Sql nVarchar(max)
SET @Sql=(N'SELECT @Total=Count(*) FROM ' + @TableSource +' '+ @SearchCondition)
print @sql
Exec sp_executesql @Sql, N'@Total Int Out',@Total Out
set @TotalCount=@Total
DECLARE @SqlQuery VARCHAR(max)
SET @SqlQuery='SELECT '+@SelectList+',RowNumber
FROM
(SELECT ' + @SelectList + ',ROW_NUMBER() OVER( '+ @OrderExpression +') AS RowNumber
FROM '+@TableSource+' '+ @SearchCondition +') AS RowNumberTableSource
WHERE RowNumber BETWEEN ' + CAST(((@PageIndex - 1)* @PageSize+1) AS VARCHAR)
+ ' AND ' +
CAST((@PageIndex * @PageSize) AS VARCHAR)
-- ORDER BY ' + @OrderExpression
PRINT @SqlQuery
SET NOCOUNT ON
EXECUTE(@SqlQuery)
SET NOCOUNT OFF
END
/// <summary>
/// 查詢接口
/// </summary>
public interface IQuery
{
string Column { get; set; } //列名
string TableName { get; set; } //表名
string OrderBy { get; set; } //查詢的"OrderBy"語句，不包含OrderBy，例子：ID DESC
string BulidQuery(); //查詢的"where"語句
string BulidSelect(string where, string tableName = ""); //查語句
}
/// <summary>
/// 分頁實現簡單查詢
/// </summary>
public class CommonPageSql
{
/// <summary>
/// 分頁獲取數據列表 適用於SQL2005和SQL2008
/// </summary>
/// <param name="con">數據庫連接字符串</param>
/// <param name="pageindex">頁索引 從0開始</param>
/// <param name="pagesize">每頁記錄數</param>
/// <param name="query">查詢接口</param>
/// <param name="p">輸出參數</param>
/// <returns>DataRead數據集</returns>
public static IDataReader GetDataReaderByPager(string con, int pageindex, int pagesize, IQuery query, out SqlParameter p)
{
string cmd = "RecordFromPage";
SqlParameter[] para = SqlHelperParameterCache.GetSpParameterSet(con, cmd);
para[0].Value = query.Column;
para[1].Value = query.TableName;
para[2].Value = query.BulidQuery();
para[3].Value = query.OrderBy;
para[4].Value = pageindex;
para[5].Value = pagesize;
para[6].Direction = ParameterDirection.Output;
//SqlParameter[] para = new SqlParameter[7];
//para[0] = new SqlParameter("@SelectList", query.Column);
//para[1] = new SqlParameter("@TableSource", query.TableName);
//para[2] = new SqlParameter("@SearchCondition", query.BulidQuery());
//para[3] = new SqlParameter("@OrderExpression", query.OrderBy);
//para[4] = new SqlParameter("@pageindex", pageindex);
//para[5] = new SqlParameter("@pagesize", pagesize);
//para[6] = new SqlParameter("@TotalCount", SqlDbType.Int);
//para[6].Direction = ParameterDirection.Output;
IDataReader reader = SqlHelper.ExecuteReader(con, CommandType.StoredProcedure, cmd, para);
p = para[6];
return reader;
}
/// <summary>
/// 分頁獲取數據列表 適用於SQL2005和SQL2008
/// </summary>
/// <param name="con">數據庫連接字符串</param>
/// <param name="pageindex">頁索引 從0開始</param>
/// <param name="pagesize">每頁記錄數</param>
/// <param name="query">查詢接口</param>
/// <param name="p">輸出參數</param>
/// <returns>DataTable數據集</returns>
public static DataTable GetDataByPager(string con, int pageindex, int pagesize, IQuery query, out SqlParameter p)
{
string cmd = "RecordFromPage";
SqlParameter[] para = SqlHelperParameterCache.GetSpParameterSet(con, cmd);
para[0].Value = query.Column;
para[1].Value = query.TableName;
para[2].Value = query.BulidQuery();
para[3].Value = query.OrderBy;
para[4].Value = pageindex;
para[5].Value = pagesize;
para[6].Direction = ParameterDirection.Output;
DataTable datatable = SqlHelper.ExecuteDataset(con,CommandType.StoredProcedure,cmd,para).Tables[0];
p = para[6];
return datatable;
}
}
/// <summary>
/// 簡單查詢幫助類
/// </summary>
public class CommonSelectSql
{
#region DataReader
/// <summary>
/// 獲取數據
/// </summary>
/// <param name="con">數據庫連接字符串</param>
/// <param name="top">前幾條</param>
/// <param name="Query">查詢構建類</param>
/// <returns>DataReader數據表</returns>
public static IDataReader SelectGetReader(string con,int top,IQuery Query)
{
return GetDataReader(con,top, Query.TableName, Query.Column, Query.BulidQuery(), Query.OrderBy);
}
/// <summary>
/// 獲得前幾行數據
/// </summary>
/// <param name="connstring">數據庫連接字符串</param>
/// <param name="Top">幾條記錄</param>
/// <param name="tableName">表名</param>
/// <param name="Column">列名</param>
/// <param name="strWhere">查詢條件</param>
/// <param name="filedOrder">排序方式</param>
/// <returns>DataReader數據表</returns>
private static IDataReader GetDataReader(string connstring,int Top, string tableName, string Column, string strWhere, string filedOrder)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat("SELECT * FROM(SELECT ROW_NUMBER() OVER(ORDER BY {0}) ROWNUM,", filedOrder);
if (!string.IsNullOrEmpty(Column))
{
strSql.Append(Column);
}
else
{
strSql.Append("*");
}
strSql.Append(" FROM " + tableName);
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere + ") T");
}
else
{
strSql.Append(") T");
}
if (Top > 0)
{
strSql.AppendFormat(" WHERE T.ROWNUM <= {0}",Top);
}
return SqlHelper.ExecuteReader(connstring, CommandType.Text, strSql.ToString());
}
#endregion
#region DataTable
/// <summary>
/// 獲取數據
/// </summary>
/// <param name="con">數據庫連接字符串</param>
/// <param name="top">前幾條</param>
/// <param name="Query">查詢構建類</param>
/// <returns>DataTable數據表</returns>
public static DataTable SelectGetTable(string con, int top, IQuery Query)
{
return GetDataTable(con, top, Query.TableName, Query.Column, Query.BulidQuery(), Query.OrderBy);
}
/// <summary>
/// 獲得前幾行數據
/// </summary>
/// <param name="con">數據庫連接字符串</param>
/// <param name="Top">幾條記錄</param>
/// <param name="tableName">表名</param>
/// <param name="Column">列名</param>
/// <param name="strWhere">查詢條件</param>
/// <param name="filedOrder">排序方式</param>
/// <returns>DataTable數據表</returns>
private static DataTable GetDataTable(string con,int Top, string tableName, string Column, string strWhere, string filedOrder)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
if (Top > 0)
{
strSql.Append(" top " + Top.ToString());
}
strSql.Append(Column);
strSql.Append(" FROM " + tableName);
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
strSql.Append(" order by " + filedOrder);
return SqlHelper.ExecuteDataset(con,CommandType.Text,strSql.ToString()).Tables[0];
}
#endregion
}
/// <summary>
/// 用來緩存SqlParameter
/// </summary>
public sealed class SqlHelperParameterCache
{
#region 私有方法，變量，跟構造函數
private SqlHelperParameterCache() { }
private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
/// <summary>
/// 返回存儲過程中的參數信息
/// </summary>
/// <param name="connection">數據庫連接對象</param>
/// <param name="spName">存儲過程名</param>
/// <param name="includeReturnValueParameter">是否返回需要返回的參數@RETURN_VALUE</param>
/// <returns>返回的參數數組.</returns>
private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null) throw new ArgumentNullException("沒有提供數據庫連接對象");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("存儲過程名");
SqlCommand cmd = new SqlCommand(spName, connection);
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);
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">存儲過程的名字或者 T-SQL 語句</param>
/// <param name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</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">存儲過程的名字或者 T-SQL 語句</param>
/// <returns>參數數組</returns>
public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("沒有提供數據庫連接字符串");
if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("沒有提供存儲過程的名字或者 T-SQL 語句");
string hashKey = connectionString + ":" + commandText;
SqlParameter[] cachedParameters = paramCache[hashKey] as SqlParameter[];
if (cachedParameters == null)
{
return null;
}
else
{
return CloneParameters(cachedParameters);
}
}
#endregion caching functions
#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">是否返回輸出參數</param>
/// <returns>參數數組</returns>
public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("沒有提供數據庫連接字符串");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("沒有提供存儲過程名");
using (SqlConnection connection = new SqlConnection(connectionString))
{
return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
}
}
/// <summary>
/// 通過數據庫連接對象獲取緩存中的參數數組
/// </summary>
/// <param name="connection">數據庫連接對象</param>
/// <param name="spName">存儲過程名</param>
/// <returns>參數數組</returns>
internal 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">是否返回輸出參數</param>
/// <returns>參數數組</returns>
internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null) throw new ArgumentNullException("沒有提供數據庫連接對象");
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">是否返回輸出參數</param>
/// <returns>參數數組</returns>
private static SqlParameter[] GetSpParameterSetInternal(SqlConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null) throw new ArgumentNullException("沒有提供數據庫連接對象");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("沒有提供存儲過程名");
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
}
/// <summary>
/// 封裝數據庫操作方法
/// </summary>
public abstract class SqlHelper
{
#region 私有方法以及構造方法
//不允許建立實例
private SqlHelper() { }
//獲取數據庫連接
public static SqlConnection GetSqlConnection(string con)
{
try
{
return new SqlConnection(con);
}
catch
{
throw new Exception("連接出錯");
}
}
/// <summary>
/// 這個方法給SqlCommand添加參數
/// </summary>
/// <param name="command">需要添加參數的command</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)
{
// 檢查當為輸入參數時賦予默認值
if ((p.Direction == ParameterDirection.InputOutput ||
p.Direction == ParameterDirection.Input) &&
(p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
}
}
/// <summary>
/// 這個方法使用的DataRow列的值填充SqlParameter數組
/// </summary>
/// <param name="commandParameters">需要填充到SqlParameter數組的DataRow</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(
"沒有給SqlParameter賦予ParameterName");
if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1)
commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
i++;
}
}
/// <summary>
/// 給SqlParameter數組賦值
/// </summary>
/// <param name="commandParameters">SqlParameter數組</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("SqlParameter數組的長度跟參數值的數組的長度不匹配");
}
// Iterate through the SqlParameters, assigning the values from the corresponding position in the
// value array
for (int i = 0, j = commandParameters.Length; i < j; i++)
{
// 如果目前的數組值來自IDbDataParameter 則填充
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">執行sql需要傳入的參數</param>
/// <param name="mustCloseConnection">如果sql連接還沒有關閉著返回true</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("缺少SqlCommand參數");
if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("缺少sql語句或存儲過程語句");
//當數據庫沒有連接則連接
if (connection.State != ConnectionState.Open)
{
mustCloseConnection = true;
connection.Open();
}
else
{
mustCloseConnection = true;
}
command.Connection = connection;
command.CommandText = commandText;
if (transaction != null)
{
if (transaction.Connection == null) throw new ArgumentException("請提供一個已經打開的事務", "transaction");
command.Transaction = transaction;
}
command.CommandType = commandType;
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
#endregion
#region ExecuteNonQuery
/// <summary>
///執行的SqlCommand （返回結果並沒有任何參數）
/// </summary>
/// <param name="connectionString">一個有效的數據庫連接字符串</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <returns>返回一個數值表示此SqlCommand命令執行後影響的行數</returns>
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
{
return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
///執行的SqlCommand （沒有返回結果）
/// </summary>
/// <param name="connectionString">一個有效的數據庫連接字符串</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <param name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回一個數值表示此SqlCommand命令執行後影響的行數</returns>
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("沒有提供數據庫連接字符串");
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
return ExecuteNonQuery(connection, commandType, commandText, commandParameters);
}
}
/// <summary>
/// 執行存儲過程通過的SqlCommand （沒有返回結果）
/// </summary>
/// <param name="connectionString">一個有效的數據庫連接字符串</param>
/// <param name="spName">存儲過程名稱</param>
/// <param name="parameterValues">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回一個數值表示此SqlCommand命令執行後影響的行數</returns>
public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("沒有提供數據庫連接字符串");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("沒有提供存儲過程名");
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 執行的SqlCommand （返回結果並沒有任何參數）。
/// </summary>
/// <param name="connection">SqlConnection連接</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <returns>返回一個數值表示此SqlCommand命令執行後影響的行數</returns>
public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of SqlParameters
return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 執行的SqlCommand （返回結果並沒有任何參數）
/// </summary>
/// <param name="connection">一個現有的數據庫連接</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <param name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回一個數值表示此SqlCommand命令執行後影響的行數</returns>
public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("沒有提供現有的數據庫連接");
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = true;
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
int retval = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
return retval;
}
/// <summary>
///執行存儲過程通過的SqlCommand （沒有返回結果）
/// </summary>
/// <param name="connection">一個現有的數據庫連接</param>
/// <param name="spName">存儲過程名稱</param>
/// <param name="parameterValues">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回一個數值表示此SqlCommand命令執行後影響的行數</returns>
public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues)
{
if (connection == null) throw new ArgumentNullException("沒有提供現有的數據庫連接");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("存儲過程的名字");
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 執行的SqlCommand （沒有返回結果並沒有任何參數）對所提供的SqlTransaction 。
/// </summary>
/// <param name="transaction">事務</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <returns>A返回一個數值表示此SqlCommand命令執行後影響的行數</returns>
public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText)
{
return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 執行的SqlCommand （沒有返回結果）對所提供的SqlTransaction 。
/// </summary>
/// <param name="transaction">事務</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <param name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回一個數值表示此SqlCommand命令執行後影響的行數</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("請為提供SqlTransaction提供連接", "transaction");
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = true;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
int retval = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return retval;
}
/// <summary>
/// 執行的SqlCommand （沒有返回結果）對所提供的SqlTransaction
/// </summary>
/// <param name="transaction">事務</param>
/// <param name="spName">存儲過程名</param>
/// <param name="parameterValues">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回一個數值表示此SqlCommand命令執行後影響的行數</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("請為提供SqlTransaction提供連接", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("請提供存儲過程名");
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion ExecuteNonQuery
#region ExecuteDataset
/// <summary>
/// 執行的SqlCommand （即返回一個結果，並沒有任何參數）
/// </summary>
/// <param name="connectionString">數據庫連接字符串</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <returns>返回結果集DataSet</returns>
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
{
return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 執行的SqlCommand （即返回一個結果）
/// </summary>
/// <param name="connectionString">數據庫連接字符串</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <param name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回結果集DataSet</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>
/// 使用存儲過程執行的SqlCommand （即返回一個結果）.
/// </summary>
/// <param name="connectionString">數據庫連接字符串</param>
/// <param name="spName">存儲過程名</param>
/// <param name="parameterValues">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回結果集DataSet</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("沒有提供存儲過程名");
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 執行的SqlCommand （即返回一個結果，並沒有任何參數）
/// </summary>
/// <param name="connection">數據庫連接對象</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <returns>返回結果集DataSet</returns>
public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)
{
return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 執行的SqlCommand （即返回一個結果）
/// </summary>
/// <param name="connection">數據庫連接對象</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <param name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回結果集DataSet</returns>
public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("沒有提供SqlConnection對象");
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = true;
cmd.CommandTimeout = connection.ConnectionTimeout;
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
return ds;
}
}
/// <summary>
/// 使用存儲過程執行的SqlCommand （即返回一個結果）.
/// </summary>
/// <param name="connection">數據庫連接對象</param>
/// <param name="spName">存儲過程的名</param>
/// <param name="parameterValues">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回結果集DataSet</returns>
public static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] parameterValues)
{
if (connection == null) throw new ArgumentNullException("沒有提供SqlConnection對象");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("沒有提供存儲過程名");
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteDataset(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 執行的SqlCommand （即返回一個結果，沒有任何參數）對所提供的SqlTransaction 。
/// </summary>
/// <param name="transaction">事務</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <returns>返回結果集DataSet</returns>
public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText)
{
return ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 執行的SqlCommand （即返回一個結果）對所提供的SqlTransaction 。
/// </summary>
/// <param name="transaction">事務</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <param name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回結果集DataSet</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("沒有提供一個打開的transaction.", "transaction");
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = true;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
}
/// <summary>
/// 使用存儲過程執行的SqlCommand （即返回一個結果）對所提供的SqlTransaction 。.
/// </summary>
/// <param name="transaction">事務</param>
/// <param name="spName">存儲過程名</param>
/// <param name="parameterValues">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回結果集DataSet</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("沒有提供一個打開的transaction.", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("沒有提供存儲過程名");
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion ExecuteDataset
#region ExecuteReader
/// <summary>
/// 這個枚舉用來表是把數據庫連接對象交給sqlHelper來處理還是調用者自己進行處理
/// </summary>
private enum SqlConnectionOwnership
{
/// <summary>SqlHelper來處理</summary>
Internal,
/// <summary>調用者自行處理</summary>
External
}
/// <summary>
/// 執行的SqlCommand （即返回一個只讀結果）
/// </summary>
/// <param name="connection">一個有效的SqlConnection </param>
/// <param name="transaction">一個有效的SqlTransaction,可以為null</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <param name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <param name="connectionOwnership">sqlHelper來處理還是調用者自己進行處理數據庫連接對象</param>
/// <returns>返回一個只讀結果</returns>
private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
{
if (connection == null) throw new ArgumentNullException("沒有提供SqlConnection對象");
bool mustCloseConnection = true;
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = 180;
try
{
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
SqlDataReader dataReader;
if (connectionOwnership == SqlConnectionOwnership.External)
{
dataReader = cmd.ExecuteReader();
}
else
{
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
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>
/// 執行的SqlCommand （即返回一個只讀結果，沒有參數）
/// </summary>
/// <param name="connectionString">數據庫連接字符串</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <returns>返回一個只讀結果</returns>
public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
{
return ExecuteReader(connectionString, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 執行的SqlCommand （即返回一個只讀結果）
/// </summary>
/// <param name="connectionString">數據庫連接字符串</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <param name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回一個只讀結果</returns>
public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("沒有提供數據庫連接字符串");
SqlConnection connection = null;
try
{
connection = new SqlConnection(connectionString);
connection.Open();
return ExecuteReader(connection, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal);
}
catch
{
if (connection != null) connection.Close();
throw;
}
}
/// <summary>
/// 使用存儲過程執行SqlCommand （即返回一個只讀結果）
/// </summary>
/// <param name="connectionString">數據庫連接字符串</param>
/// <param name="spName">存儲過程名</param>
/// <param name="parameterValues">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回一個只讀結果</returns>
public static SqlDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("沒有提供數據庫連接字符串");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("沒有提供存儲過程名");
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 執行的SqlCommand （即返回一個只讀結果，沒有參數）
/// </summary>
/// <param name="connection">一個有效的SqlConnection </param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <returns>返回一個只讀結果</returns>
public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText)
{
return ExecuteReader(connection, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 執行的SqlCommand （即返回一個只讀結果）
/// </summary>
/// <param name="connection">一個有效的SqlConnection</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <param name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回一個只讀結果</returns>
public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
return ExecuteReader(connection, (SqlTransaction)null, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
}
/// <summary>
/// 使用存儲過程執行SqlCommand （即返回一個只讀結果）
/// </summary>
/// <param name="connection">一個有效的SqlConnection</param>
/// <param name="spName">存儲過程名</param>
/// <param name="parameterValues">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回一個只讀結果</returns>
public static SqlDataReader ExecuteReader(SqlConnection connection, string spName, params object[] parameterValues)
{
if (connection == null) throw new ArgumentNullException("沒有提供數據庫連接對象");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("沒有提供存儲過程名");
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteReader(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 執行的SqlCommand （即返回一個只讀結果，沒有傳入的參數）對所提供的SqlTransaction 。.
/// </summary>
/// <param name="transaction">一個有效的SqlTransaction</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <returns>返回一個只讀結果</returns>
public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText)
{
return ExecuteReader(transaction, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 執行的SqlCommand （即返回一個只讀結果）對所提供的SqlTransaction 。
/// </summary>
/// <param name="transaction">一個有效的SqlTransaction</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <param name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回一個只讀結果</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("沒有提供一個打開的transaction.", "transaction");
return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
}
/// <summary>
/// 使用存儲過程執行的SqlCommand （即返回一個只讀結果）對所提供的SqlTransaction 。
/// </summary>
/// <param name="transaction">一個有效的SqlTransaction</param>
/// <param name="spName">存儲過程名</param>
/// <param name="parameterValues">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回一個只讀結果</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("沒有提供一個打開的transaction.", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("存儲過程");
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteReader(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion ExecuteReader
#region ExecuteScalar
/// <summary>
/// 執行的SqlCommand （即返回第一行第一列，不帶參數）
/// </summary>
/// <param name="connectionString">數據庫連接字符串</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <returns>返回數據庫中第一行第一列的數據</returns>
public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)
{
return ExecuteScalar(connectionString, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 執行的SqlCommand （即返回第一行第一列）
/// </summary>
/// <param name="connectionString">數據庫連接字符串</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <param name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回數據庫中第一行第一列的數據</returns>
public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("沒有提供數據庫連接字符串");
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
return ExecuteScalar(connection, commandType, commandText, commandParameters);
}
}
/// <summary>
/// 使用存儲過程執行的SqlCommand （即返回第一行第一列）
/// </summary>
/// <param name="connectionString">數據庫連接字符串</param>
/// <param name="spName">存儲過程名</param>
/// <param name="parameterValues">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回數據庫中第一行第一列的數據</returns>
public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("沒有提供數據庫連接字符串");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("存儲過程名");
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
}
}
/// <summary>
///執行的SqlCommand （即返回第一行第一列,不帶參數）
/// </summary>
/// <param name="connection">有效的數據庫連接</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <returns>返回數據庫中第一行第一列的數據</returns>
public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText)
{
return ExecuteScalar(connection, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 有效的數據庫連接
/// </summary>
/// <param name="connection">有效的數據庫連接</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <param name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回數據庫中第一行第一列的數據</returns>
public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("沒有提供數據庫連接對象");
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = true;
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
object retval = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
return retval;
}
/// <summary>
/// 使用存儲過程執行的SqlCommand （即返回第一行第一列）
/// </summary>
/// <param name="connection">有效的數據庫連接</param>
/// <param name="spName">存儲過程名</param>
/// <param name="parameterValues">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回數據庫中第一行第一列的數據</returns>
public static object ExecuteScalar(SqlConnection connection, string spName, params object[] parameterValues)
{
if (connection == null) throw new ArgumentNullException("沒有提供數據庫連接對象");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("沒有提供存儲過程");
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteScalar(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 執行SqlCommand （即返回第一行第一列，不帶參數）對所提供的SqlTransaction 。
/// </summary>
/// <param name="transaction">一個有效的SqlTransaction</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <returns>返回數據庫中第一行第一列的數據</returns>
public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText)
{
return ExecuteScalar(transaction, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 執行SqlCommand （即返回第一行第一列）對所提供的SqlTransaction 。
/// </summary>
/// <param name="transaction">一個有效的SqlTransaction</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <param name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回數據庫中第一行第一列的數據</returns>
public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("沒有提供SqlTransaction對象");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("沒有提供一個打開的transaction", "transaction");
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = true;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
object retval = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return retval;
}
/// <summary>
/// 使用存儲過程執行的SqlCommand （即返回第一行第一列）對所提供的SqlTransaction 。
/// </summary>
/// <param name="transaction">一個有效的SqlTransaction</param>
/// <param name="spName">存儲過程名</param>
/// <param name="parameterValues">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回數據庫中第一行第一列的數據</returns>
public static object ExecuteScalar(SqlTransaction transaction, string spName, params object[] parameterValues)
{
if (transaction == null) throw new ArgumentNullException("沒有提供SqlTransaction對象");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("沒有提供一個打開的transaction", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("存儲過程名");
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion ExecuteScalar
#region ExecuteXmlReader
/// <summary>
/// 執行的SqlCommand （即返回一個XmlReader，並沒有任何參數）對所提供的SqlConnection 。
/// </summary>
/// <param name="connection">有效的數據庫連接</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <returns>返回的xmlReader對象</returns>
public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText)
{
return ExecuteXmlReader(connection, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 執行的SqlCommand （即返回一個XmlReader）對所提供的SqlConnection 。
/// </summary>
/// <param name="connection">有效的數據庫連接</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <param name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回的xmlReader對象</returns>
public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("沒有提供SqlConnection對象");
bool mustCloseConnection = true;
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
XmlReader retval = cmd.ExecuteXmlReader();
cmd.Parameters.Clear();
return retval;
}
catch
{
if (mustCloseConnection)
connection.Close();
throw;
}
}
/// <summary>
/// 使用存儲過程執行的SqlCommand （即返回一個XmlReader）對所提供的SqlConnection 。
/// </summary>
/// <param name="connection">有效的數據庫連接</param>
/// <param name="spName">存儲過程名</param>
/// <param name="parameterValues">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回的xmlReader對象</returns>
public static XmlReader ExecuteXmlReader(SqlConnection connection, string spName, params object[] parameterValues)
{
if (connection == null) throw new ArgumentNullException("沒有提供SqlConnection對象");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("存儲過程名");
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 執行SqlCommand （即返回第一行第一列，不帶參數）對所提供的SqlTransaction 。
/// </summary>
/// <param name="transaction">一個有效的SqlTransaction</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <returns>返回的xmlReader對象</returns>
public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText)
{
return ExecuteXmlReader(transaction, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 執行SqlCommand （即返回第一行第一列）對所提供的SqlTransaction 。
/// </summary>
/// <param name="transaction">一個有效的SqlTransaction</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <param name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回的xmlReader對象</returns>
public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("沒有提供SqlTransaction對象");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("沒有提供一個打開的transaction", "transaction");
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = true;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
XmlReader retval = cmd.ExecuteXmlReader();
cmd.Parameters.Clear();
return retval;
}
/// <summary>
/// 使用存儲過程執行SqlCommand （即返回第一行第一列）對所提供的SqlTransaction 。
/// </summary>
/// <param name="transaction">一個有效的SqlTransaction</param>
/// <param name="spName">存儲過程名</param>
/// <param name="parameterValues">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <returns>返回的xmlReader對象</returns>
public static XmlReader ExecuteXmlReader(SqlTransaction transaction, string spName, params object[] parameterValues)
{
if (transaction == null) throw new ArgumentNullException("沒有提供SqlTransaction對象");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("沒有提供一個打開的transaction", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("存儲過程");
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion ExecuteXmlReader
#region FillDataset
/// <summary>
/// 執行的SqlCommand （即返回一個DataSet，並沒有任何參數）
/// </summary>
/// <param name="connectionString">數據庫連接對象字符串</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <param name="dataSet">一個DataSet ，其中將載有結果</param>
/// <param name="tableNames">該數組將被用於創建表映射允許DataTables被引用的用戶定義的名稱（可能是實際的表名稱）</param>
public static void FillDataset(string connectionString, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("缺少數據庫連接字符串");
if (dataSet == null) throw new ArgumentNullException("dataSet不能為null");
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
FillDataset(connection, commandType, commandText, dataSet, tableNames);
}
}
/// <summary>
/// 執行的SqlCommand （即返回一個DataSet）
/// </summary>
/// <param name="connectionString">數據庫連接對象字符串</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <param name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>
/// <param name="dataSet">一個DataSet ，其中將載有結果</param>
/// <param name="tableNames">該數組將被用於創建表映射允許DataTables被引用的用戶定義的名稱（可能是實際的表名稱）
/// </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("缺少數據庫連接字符串");
if (dataSet == null) throw new ArgumentNullException("dataSet不能為null");
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
FillDataset(connection, commandType, commandText, dataSet, tableNames, commandParameters);
}
}
/// <summary>
/// 使用存儲過程執行的SqlCommand （即返回一個DataSet）
/// </summary>
/// <param name="connectionString">數據庫連接對象字符串</param>
/// <param name="spName">存儲過程名</param>
/// <param name="dataSet">一個DataSet ，其中將載有結果</param>
/// <param name="tableNames">該數組將被用於創建表映射允許DataTables被引用的用戶定義的名稱（可能是實際的表名稱）
/// </param>
/// <param name="parameterValues">以數組形式提供SqlCommand命令中用到的參數列表</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("缺少數據庫連接字符串");
if (dataSet == null) throw new ArgumentNullException("dataSet不能為null");
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
FillDataset(connection, spName, dataSet, tableNames, parameterValues);
}
}
/// <summary>
/// 執行的SqlCommand （即返回一個DataSet，不帶參數）
/// </summary>
/// <param name="connection">數據庫連接對象</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <param name="dataSet">一個DataSet ，其中將載有結果</param>
/// <param name="tableNames">該數組將被用於創建表映射允許DataTables被引用的用戶定義的名稱（可能是實際的表名稱）
/// </param>
public static void FillDataset(SqlConnection connection, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames)
{
FillDataset(connection, commandType, commandText, dataSet, tableNames, null);
}
/// <summary>
/// 執行的SqlCommand （即返回一個DataSet）
/// </summary>
/// <param name="connection">數據庫連接對象</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <param name="dataSet">一個DataSet ，其中將載有結果</param>
/// <param name="tableNames">該數組將被用於創建表映射允許DataTables被引用的用戶定義的名稱（可能是實際的表名稱）
/// </param>
/// <param name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</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>
/// 執行的SqlCommand （即返回一個DataSet）
/// </summary>
/// <param name="connection">數據庫連接對象</param>
/// <param name="spName">存儲過程名</param>
/// <param name="dataSet">一個DataSet ，其中將載有結果</param>
/// <param name="tableNames">該數組將被用於創建表映射允許DataTables被引用的用戶定義的名稱（可能是實際的表名稱）
/// </param>
/// <param name="parameterValues">以數組形式提供SqlCommand命令中用到的參數列表</param>
public static void FillDataset(SqlConnection connection, string spName,
DataSet dataSet, string[] tableNames,
params object[] parameterValues)
{
if (connection == null) throw new ArgumentNullException("沒有提供數據庫連接對象");
if (dataSet == null) throw new ArgumentNullException("dataSet不能為null");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("存儲過程名");
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
AssignParameterValues(commandParameters, parameterValues);
FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
}
else
{
FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames);
}
}
/// <summary>
/// 執行SqlCommand （即返回一個DataSet，不帶參數）對所提供的SqlTransaction 。
/// </summary>
/// <param name="transaction">有效的SqlTransaction對象</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <param name="dataSet">一個DataSet ，其中將載有結果</param>
/// <param name="tableNames">該數組將被用於創建表映射允許DataTables被引用的用戶定義的名稱（可能是實際的表名稱）
/// </param>
public static void FillDataset(SqlTransaction transaction, CommandType commandType,
string commandText,
DataSet dataSet, string[] tableNames)
{
FillDataset(transaction, commandType, commandText, dataSet, tableNames, null);
}
/// <summary>
/// 執行SqlCommand （即返回一個DataSet）對所提供的SqlTransaction 。
/// </summary>
/// <param name="transaction">有效的SqlTransaction對象</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <param name="dataSet">一個DataSet ，其中將載有結果</param>
/// <param name="tableNames">該數組將被用於創建表映射允許DataTables被引用的用戶定義的名稱（可能是實際的表名稱）
/// </param>
/// <param name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</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>
/// 使用存儲過程執行SqlCommand （即返回一個DataSet）對所提供的SqlTransaction 。
/// </summary>
/// <param name="transaction">有效的SqlTransaction對象</param>
/// <param name="spName">存儲過程</param>
/// <param name="dataSet">一個DataSet ，其中將載有結果</param>
/// <param name="tableNames">該數組將被用於創建表映射允許DataTables被引用的用戶定義的名稱（可能是實際的表名稱）
/// </param>
/// <param name="parameterValues">以數組形式提供SqlCommand命令中用到的參數列表</param>
public static void FillDataset(SqlTransaction transaction, string spName,
DataSet dataSet, string[] tableNames,
params object[] parameterValues)
{
if (transaction == null) throw new ArgumentNullException("沒有提供SqlTransaction對象");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("沒有提供一個打開的transaction", "transaction");
if (dataSet == null) throw new ArgumentNullException("dataSet不能為null");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("存儲過程名");
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
AssignParameterValues(commandParameters, parameterValues);
FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
}
else
{
FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames);
}
}
/// <summary>
/// 私有方法，執行的SqlCommand （即返回一個結果集）對指定的SqlTransaction和SqlConnection使用提供的參數。
/// </summary>
/// <param name="connection">數據庫連接對象</param>
/// <param name="transaction">有效的SqlTransaction對象</param>
/// <param name="commandType">SqlCommand命令類型 (存儲過程， T-SQL語句， 等等。)</param>
/// <param name="commandText">存儲過程的名字或者 T-SQL 語句</param>
/// <param name="dataSet">一個DataSet ，其中將載有結果</param>
/// <param name="tableNames">該數組將被用於創建表映射允許DataTables被引用的用戶定義的名稱（可能是實際的表名稱）
/// </param>
/// <param name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</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("沒有提供SqlConnection對象");
if (dataSet == null) throw new ArgumentNullException("dataSet不能為null");
SqlCommand command = new SqlCommand();
bool mustCloseConnection = true;
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("該tableNames參數必須包含一個表格中的字段，這裡提供了一個值為空或空字符串.", "tableNames");
dataAdapter.TableMappings.Add(tableName, tableNames[index]);
tableName += (index + 1).ToString();
}
}
dataAdapter.Fill(dataSet);
command.Parameters.Clear();
}
if (mustCloseConnection)
connection.Close();
}
#endregion
#region UpdateDataset
/// <summary>
/// 執行各自的命令為每個插入，更新或刪除的行中的數據。
/// </summary>
/// <param name="insertCommand">插入的SqlCommand</param>
/// <param name="deleteCommand">刪除的SqlCommand</param>
/// <param name="updateCommand">更新的SqlCommand</param>
/// <param name="dataSet">一個DataSet ，其中將載有結果</param>
/// <param name="tableName">該數組將被用於創建表映射允許DataTables被引用的用戶定義的名稱（可能是實際的表名稱）</param>
public static void UpdateDataset(SqlCommand insertCommand, SqlCommand deleteCommand, SqlCommand updateCommand, DataSet dataSet, string tableName)
{
if (insertCommand == null) throw new ArgumentNullException("沒有提供插入的SqlCommand");
if (deleteCommand == null) throw new ArgumentNullException("沒有提供刪除的SqlCommand");
if (updateCommand == null) throw new ArgumentNullException("沒有提供更新的SqlCommand");
if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("沒有提供tableName");
using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
{
dataAdapter.UpdateCommand = updateCommand;
dataAdapter.InsertCommand = insertCommand;
dataAdapter.DeleteCommand = deleteCommand;
dataAdapter.Update(dataSet, tableName);
dataSet.AcceptChanges();
}
}
#endregion
#region CreateCommand
/// <summary>
/// 簡化建立一個SQL命令對象，允許存儲過程和可選的參數
/// </summary>
/// <param name="connection">有效的SqlConnection對象</param>
/// <param name="spName">存儲過程</param>
/// <param name="sourceColumns">該數組將被用於創建表映射允許DataTables被引用的用戶定義的名稱（可能是實際的表名稱）</param>
/// <returns>sql命令對象</returns>
public static SqlCommand CreateCommand(SqlConnection connection, string spName, params string[] sourceColumns)
{
if (connection == null) throw new ArgumentNullException("沒有提供SqlConnection對象");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("存儲過程名");
SqlCommand cmd = new SqlCommand(spName, connection);
cmd.CommandType = CommandType.StoredProcedure;
if ((sourceColumns != null) && (sourceColumns.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
for (int index = 0; index < sourceColumns.Length; index++)
commandParameters[index].SourceColumn = sourceColumns[index];
AttachParameters(cmd, commandParameters);
}
return cmd;
}
#endregion
#region ExecuteNonQueryTypedParams
/// <summary>
/// 執行存儲過程通過的SqlCommand （沒有返回結果）使用的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("沒有提供數據庫連接字符串");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("沒有提供存儲過程名");
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 執行存儲過程通過的SqlCommand （沒有返回結果）使用的DataRow列的值的存儲過程的參數值。這種方法將查詢數據庫，發現參數的存儲過程（第一次每一個存儲過程被稱為） ，並指定值的基礎上連續值。
/// </summary>
/// <param name="connection">有效的SqlConnection對象</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("沒有提供SqlConnection對象");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("沒有提供存儲過程");
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 執行存儲過程通過的SqlCommand （沒有返回結果）使用的DataRow列的值的存儲過程的參數值。這種方法將查詢數據庫，發現參數的存儲過程（第一次每一個存儲過程被稱為） ，並指定值的基礎上連續值。
/// </summary>
/// <param name="transaction">有效的SqlTransaction對象</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("沒有提供SqlTransaction對象");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("沒有提供有連接的SqlTransaction對象", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("沒有提供存儲過程");
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion
#region ExecuteDatasetTypedParams
/// <summary>
///執行預存程序通過的SqlCommand （返回DataSet）使用的DataRow列的值的存儲過程的參數值。這種方法將查詢數據庫，發現參數的存儲過程（第一次每一個存儲過程被稱為） ，並指定值的基礎上連續值。
/// </summary>
/// <param name="connectionString">數據庫連接字符串</param>
/// <param name="spName">存儲過程名</param>
/// <param name="dataRow">儲存參數值的DataRow</param>
/// <returns>返回結果集Dataset</returns>
public static DataSet ExecuteDatasetTypedParams(string connectionString, String spName, DataRow dataRow)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("沒有提供數據庫連接字符串");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("沒有提供存儲過程");
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 執行預存程序通過的SqlCommand （返回DataSet）使用的DataRow列的值的存儲過程的參數值。這種方法將查詢數據庫，發現參數的存儲過程（第一次每一個存儲過程被稱為） ，並指定值的基礎上連續值。
/// </summary>
/// <param name="connection">有效的SqlConnection對象</param>
/// <param name="spName">存儲過程名</param>
/// <param name="dataRow">儲存參數值的DataRow</param>
/// <returns>返回結果集Dataset</returns>
public static DataSet ExecuteDatasetTypedParams(SqlConnection connection, String spName, DataRow dataRow)
{
if (connection == null) throw new ArgumentNullException("沒有提供數據庫連接對象");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("沒有提供存儲過程");
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 執行預存程序通過的SqlCommand （返回DataSet）使用的DataRow列的值的存儲過程的參數值。這種方法將查詢數據庫，發現參數的存儲過程（第一次每一個存儲過程被稱為） ，並指定值的基礎上連續值。
/// </summary>
/// <param name="transaction">有效的SqlTransaction對象</param>
/// <param name="spName">存儲過程名</param>
/// <param name="dataRow">儲存參數值的DataRow</param>
/// <returns>返回結果集Dataset</returns>
public static DataSet ExecuteDatasetTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
{
if (transaction == null) throw new ArgumentNullException("沒有提供SqlTransaction對象");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("沒有提供有連接的SqlTransaction對象", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("沒有提供存儲過程");
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion
#region ExecuteReaderTypedParams
/// <summary>
/// 執行預存程序通過的SqlCommand （返回SqlDataReader）使用的DataRow列的值的存儲過程的參數值。這種方法將查詢數據庫，發現參數的存儲過程（第一次每一個存儲過程被稱為） ，並指定值的基礎上連續值。
/// </summary>
/// <param name="connectionString">數據庫連接字符串</param>
/// <param name="spName">存儲過程名</param>
/// <param name="dataRow">儲存參數值的DataRow</param>
/// <returns>返回結果集SqlDataReader</returns>
public static SqlDataReader ExecuteReaderTypedParams(String connectionString, String spName, DataRow dataRow)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("沒有提供數據庫連接字符串");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("沒有提供存儲過程");
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 執行預存程序通過的SqlCommand （返回SqlDataReader）使用的DataRow列的值的存儲過程的參數值。這種方法將查詢數據庫，發現參數的存儲過程（第一次每一個存儲過程被稱為） ，並指定值的基礎上連續值。
/// </summary>
/// <param name="connection">有效的SqlConnection對象</param>
/// <param name="spName">存儲過程名</param>
/// <param name="dataRow">儲存參數值的DataRow</param>
/// <returns>返回結果集SqlDataReader</returns>
public static SqlDataReader ExecuteReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow)
{
if (connection == null) throw new ArgumentNullException("沒有提供數據庫連接對象");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("沒有提供存儲過程");
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 執行預存程序通過的SqlCommand （返回SqlDataReader）使用的DataRow列的值的存儲過程的參數值。這種方法將查詢數據庫，發現參數的存儲過程（第一次每一個存儲過程被稱為） ，並指定值的基礎上連續值。
/// </summary>
/// <param name="transaction">有效的SqlTransaction對象</param>
/// <param name="spName">存儲過程名</param>
/// <param name="dataRow">儲存參數值的DataRow</param>
/// <returns>返回結果集SqlDataReader</returns>
public static SqlDataReader ExecuteReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
{
if (transaction == null) throw new ArgumentNullException("沒有提供SqlTransaction對象");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("沒有提供有連接的SqlTransaction對象.", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("沒有提供存儲過程");
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion
#region ExecuteScalarTypedParams
/// <summary>
/// 執行預存程序通過的SqlCommand （返回第一行第一列）使用的DataRow列的值的存儲過程的參數值。這種方法將查詢數據庫，發現參數的存儲過程（第一次每一個存儲過程被稱為） ，並指定值的基礎上連續值。
/// </summary>
/// <param name="connectionString">數據庫連接字符串</param>
/// <param name="spName">存儲過程名</param>
/// <param name="dataRow">儲存參數值的DataRow</param>
/// <returns>返回第一行第一列</returns>
public static object ExecuteScalarTypedParams(String connectionString, String spName, DataRow dataRow)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("沒有提供數據庫連接字符串");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("沒有提供存儲過程");
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 執行預存程序通過的SqlCommand （返回第一行第一列）使用的DataRow列的值的存儲過程的參數值。這種方法將查詢數據庫，發現參數的存儲過程（第一次每一個存儲過程被稱為） ，並指定值的基礎上連續值。
/// </summary>
/// <param name="connection">有效的SqlConnection對象</param>
/// <param name="spName">存儲過程名</param>
/// <param name="dataRow">儲存參數值的DataRow</param>
/// <returns>返回第一行第一列</returns>
public static object ExecuteScalarTypedParams(SqlConnection connection, String spName, DataRow dataRow)
{
if (connection == null) throw new ArgumentNullException("沒有提供數據庫連接對象");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("沒有提供存儲過程");
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 執行預存程序通過的SqlCommand （返回第一行第一列）使用的DataRow列的值的存儲過程的參數值。這種方法將查詢數據庫，發現參數的存儲過程（第一次每一個存儲過程被稱為） ，並指定值的基礎上連續值。
/// </summary>
/// <param name="transaction">有效的SqlTransaction對象</param>
/// <param name="spName">存儲過程名</param>
/// <param name="dataRow">儲存參數值的DataRow.</param>
/// <returns>返回第一行第一列</returns>
public static object ExecuteScalarTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
{
if (transaction == null) throw new ArgumentNullException("沒有提供SqlTransaction對象");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("沒有提供有連接的SqlTransaction對象", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("沒有提供存儲過程");
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion
#region ExecuteXmlReaderTypedParams
/// <summary>
/// 執行預存程序通過的SqlCommand （返回XmlReader）使用的DataRow列的值的存儲過程的參數值。這種方法將查詢數據庫，發現參數的存儲過程（第一次每一個存儲過程被稱為） ，並指定值的基礎上連續值。
/// </summary>
/// <param name="connection">有效的SqlConnection對象</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("沒有提供數據庫連接對象");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("沒有提供存儲過程");
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
}
}
/// <summary>
/// 執行預存程序通過的SqlCommand （返回XmlReader）使用的DataRow列的值的存儲過程的參數值。這種方法將查詢數據庫，發現參數的存儲過程（第一次每一個存儲過程被稱為） ，並指定值的基礎上連續值。
/// </summary>
/// <param name="transaction">有效的SqlTransaction對象</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("沒有提供SqlTransaction對象");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("沒有提供有連接的SqlTransaction對象.", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("沒有提供存儲過程");
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion
/// <summary>
/// 執行多條SQL語句，實現數據庫事務。
/// </summary>
/// <param name="connectionString">多條SQL語句</param>
/// <param name="SQLStringList">多條SQL語句</param>
/// <returns>影響的記錄數</returns>
public static int ExecuteSqlTran(string connectionString, List<String> SQLStringList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
int count = 0;
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n];
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
count += cmd.ExecuteNonQuery();
}
}
tx.Commit();
return count;
}
catch
{
tx.Rollback();
return 0;
}
}
}
/// <summary>
/// 執行多條SQL語句，實現數據庫事務。
/// </summary>
/// <param name="connectionString">泛型列</param>
/// <param name="SQLStringList">泛型列</param>
/// <returns>影響的記錄數</returns>
public static int ExecuteSqlTran(string connectionString, List<CommandInfo> cmdList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = trans;
int count = 0;
//循環
foreach (CommandInfo myDE in cmdList)
{
cmd.Parameters.Clear();
cmd.CommandText = myDE.CommandText;
cmd.CommandType = myDE.CommandType;
AttachParameters(cmd, myDE.Parameters);
try
{
int result = cmd.ExecuteNonQuery();
count += result;
}
catch
{
trans.Rollback();
return 0;
}
}
trans.Commit();
return count;
}
}
}
/// <summary>
/// 執行多條SQL語句，實現數據庫事務。
/// </summary>
/// <param name="connectionString">泛型列</param>
/// <param name="SQLStringList">泛型列</param>
/// <returns>影響的記錄數</returns>
public static int ExecuteSqlTranWithException(string connectionString, List<CommandInfo> cmdList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = trans;
int count = 0;
//循環
foreach (CommandInfo myDE in cmdList)
{
cmd.Parameters.Clear();
cmd.CommandText = myDE.CommandText;
cmd.CommandType = myDE.CommandType;
AttachParameters(cmd, myDE.Parameters);
try
{
int result = cmd.ExecuteNonQuery();
count += result;
}
catch (Exception ex)
{
trans.Rollback();
throw;
}
}
trans.Commit();
return count;
}
}
}
///<summary>
///直接通過DataSet更新數據庫表
///</summary>
///<param name="ds"></param>
///<param name="strTblName">ds中要更新的表名</param>
///<param name="strConnection"></param>
///<returns></returns>
public static int UpdateByDataSet(DataSet ds, string strTblName, string strConnection)
{
SqlConnection conn = new SqlConnection(strConnection);
SqlDataAdapter myAdapter = new SqlDataAdapter();
SqlCommand myCommand = new SqlCommand("select * from " + strTblName, conn);
myAdapter.SelectCommand = myCommand;
SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);
return myAdapter.Update(ds, strTblName);
}
/// <summary>
/// 判斷reader裡是否存在列名
/// </summary>
/// <param name="reader"></param>
/// <param name="name"></param>
/// <returns></returns>
public static bool ContainsField(IDataReader reader, string name)
{
for (int i = 0; i < reader.FieldCount; i++)
{
if (reader.GetName(i) == name)
return true;
}
return false;
}
}
public class CommandInfo
{
private string _commandText;
private CommandType _commandType;
public CommandType CommandType
{
get { return _commandType; }
set { _commandType = value; }
}
public string CommandText
{
get { return _commandText; }
set { _commandText = value; }
}
private SqlParameter[] _parameters;
public SqlParameter[] Parameters
{
get { return _parameters; }
set { _parameters = value; }
}
public CommandInfo(string sqlText, SqlParameter[] para, CommandType ct)
{
_commandText = sqlText;
_parameters = para;
_commandType = ct;
}
}
