/// 示例:
/// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, \
///
///
///
///
/// ///
public static void UpdateDataset(SqlCommand insertCommand, SqlCommand deleteCommand, SqlCommand updateCommand, DataSet dataSet, string tableName) ...{ if(
insertCommand
==
null null null
) ) )
throw throw throw
new new new
ArgumentNullException( \ if( deleteCommand == ArgumentNullException( \ if(
updateCommand
==
ArgumentNullException( \
if( tableName == null || tableName.Length == 0 ) throw new ArgumentNullException( \
// 创建SqlDataAdapter,当操作完成后释放.
using (SqlDataAdapter dataAdapter = new SqlDataAdapter()) ...{
// 设置数据适配器命令
dataAdapter.UpdateCommand = updateCommand; dataAdapter.InsertCommand = insertCommand; dataAdapter.DeleteCommand = deleteCommand;
// 更新数据集改变到数据库
dataAdapter.Update (dataSet, tableName);
// 提交所有改变到数据集. dataSet.AcceptChanges(); } }
#endregion
CreateCommand 创建一条SqlCommand命令#region CreateCommand 创建一条SqlCommand命令 /**////
/// 创建SqlCommand命令,指定数据库连接对象,存储过程名和参数. /// ///
/// SqlCommand command = CreateCommand(conn, \\
///
/// ///
/// ///
public static SqlCommand CreateCommand(SqlConnection connection, string spName, params string[] sourceColumns) ...{
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;
// 如果有参数值
if ((sourceColumns != null) && (sourceColumns.Length > 0)) ...{
// 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()
SqlParameter[] commandParameters SqlHelperParameterCache.GetSpParameterSet(connection, spName);
// 将源表的列到映射到DataSet命令中.
for (int index=0; index < sourceColumns.Length; index++)
commandParameters[index].SourceColumn = sourceColumns[index];
// Attach the discovered parameters to the SqlCommand object AttachParameters (cmd, commandParameters); }
return cmd; }
#endregion
ExecuteNonQueryTypedParams 类型化ExecuteNonQueryTypedParams 类型化参数(DataRow)
参
数
(DataRow)#region
=
/**////
/// 执行指定连接数据库连接字符串的存储过程,使用DataRow做为参数值,返回受影响的行数.
///
/// ///
///
///
public static int ExecuteNonQueryTypedParams(String connectionString, String spName, DataRow dataRow)
...{
if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( \
if( spName == null || spName.Length ArgumentNullException( \
// 如果row有值,存储过程必须初始化.
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
CommandType.StoredProcedure, spName); } }
/**////
/// 执行指定连接数据库连接对象的存储过程,使用DataRow做为参数值,返回受影响的行数.
///
/// ///
///
SqlHelper.ExecuteNonQuery(connectionString,
=
==
0
)
throw
new
///
public static int ExecuteNonQueryTypedParams(SqlConnection connection, String spName, DataRow dataRow) ...{
if( connection == null ) throw new ArgumentNullException( \ if(
spName
==
null
||
spName.Length
==
0
)
throw
new
ArgumentNullException( \
// 如果row有值,存储过程必须初始化.
if (dataRow != null && dataRow.ItemArray.Length > 0)
...{
// 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()
SqlParameter[]
commandParameters
=
SqlHelperParameterCache.GetSpParameterSet(connection, spName);
// 分配参数值
AssignParameterValues(commandParameters, dataRow); return } else ...{
return
CommandType.StoredProcedure, spName); }
}
/**////
/// 执行指定连接数据库事物的存储过程,使用DataRow做为参数值,返回受影响的行数.
///
/// ///
/// ///
public static int ExecuteNonQueryTypedParams(SqlTransaction transaction, String spName, DataRow dataRow) ...{
if( transaction == null ) throw new ArgumentNullException( \ if( transaction != null && transaction.Connection == null ) throw new ArgumentException( \transaction was rollbacked or commited, please provide an open transaction.\
SqlHelper.ExecuteNonQuery(connection,
CommandType.StoredProcedure, spName, commandParameters);
SqlHelper.ExecuteNonQuery(connection,
if( spName == null || spName.Length == 0 ) throw new
ArgumentNullException( \
// Sf the row has values, the store procedure parameters must be initialized if (dataRow != null && dataRow.ItemArray.Length > 0)
...{
// 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()
SqlParameter[]
// 分配参数值
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters); } else ...{ return
CommandType.StoredProcedure, spName);
} }
#endregion
ExecuteDatasetTypedParams 类型ExecuteDatasetTypedParams 类型化参数(DataRow)
SqlHelper.ExecuteNonQuery(transaction, commandParameters
=
SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
化参数(DataRow)#region
/**////
/// 执行指定连接数据库连接字符串的存储过程,使用DataRow做为参数值,返回DataSet.
///
/// ///
/// ///
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( \
//如果row有值,存储过程必须初始化.