/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
///
///
/// e.g.:
/// FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
///
/// A valid SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset wich will contain the resultset generated by the command
/// This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
///
public static void FillDataset(SqlConnection connection, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames)
{
FillDataset(connection, commandType, commandText, dataSet, tableNames, null);
}
///
/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
/// using the provided parameters.
///
///
/// e.g.:
/// FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
///
/// A valid SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset wich will contain the resultset generated by the command
/// This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
///
/// An array of SqlParamters used to execute the command
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);
}
///
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
/// using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
///
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// FillDataset(conn, "GetOrders", ds, new string[] {"orders"}, 24, 36);
///
/// A valid SqlConnection
/// The name of the stored procedure
/// A dataset wich will contain the resultset generated by the command
/// This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
///
/// An array of objects to be assigned as the input values of the stored procedure
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 we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters
FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
}
else
{
// Otherwise we can just call the SP without params
FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames);
}
}
///
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.
///
///
/// e.g.:
/// FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
///
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset wich will contain the resultset generated by the command
/// This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
///
public static void FillDataset(SqlTransaction transaction, CommandType commandType,
string commandText,
DataSet dataSet, string[] tableNames)
{
FillDataset (transaction, commandType, commandText, dataSet, tableNames, null);
}
///
/// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
/// using the provided parameters.
///
///
/// e.g.:
/// FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
///
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset wich will contain the resultset generated by the command
/// This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
///
/// An array of SqlParamters used to execute the command
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);
}
///
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified
/// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
///
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// FillDataset(trans, "GetOrders", ds, new string[]{"orders"}, 24, 36);
///
/// A valid SqlTransaction
/// The name of the stored procedure
/// A dataset wich will contain the resultset generated by the command
/// This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
///
/// An array of objects to be assigned as the input values of the stored procedure
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 we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters
FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
}
else
{
// Otherwise we can just call the SP without params
FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames);
}
}
///
/// Private helper method that execute a SqlCommand (that returns a resultset) against the specified SqlTransaction and SqlConnection
/// using the provided parameters.
///
///
/// e.g.:
/// FillDataset(conn, trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
///
/// A valid SqlConnection
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset wich will contain the resultset generated by the command
/// This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
///
/// An array of SqlParamters used to execute the command
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" );
// Create a command and prepare it for execution
SqlCommand command = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );
// Create the DataAdapter & DataSet
using( SqlDataAdapter dataAdapter = new SqlDataAdapter(command) )
{
// Add the table mappings specified by the user
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();
}
}
// Fill the DataSet using default values for DataTable names, etc
dataAdapter.Fill(dataSet);
// Detach the SqlParameters from the command object, so they can be used again
command.Parameters.Clear();
}
if( mustCloseConnection )
connection.Close();
}
#endregion
#region UpdateDataset
///
/// Executes the respective command for each inserted, updated, or deleted row in the DataSet.
///
///
/// e.g.:
/// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");
///
/// A valid transact-SQL statement or stored procedure to insert new records into the data source
/// A valid transact-SQL statement or stored procedure to delete records from the data source
/// A valid transact-SQL statement or stored procedure used to update records in the data source
/// The DataSet used to update the data source
/// The DataTable used to update the data source.
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" );
// Create a SqlDataAdapter, and dispose of it after we are done
using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
{
// Set the data adapter commands
dataAdapter.UpdateCommand = updateCommand;
dataAdapter.InsertCommand = insertCommand;
dataAdapter.DeleteCommand = deleteCommand;
// Update the dataset changes in the data source
dataAdapter.Update (dataSet, tableName);
// Commit all the changes made to the DataSet
dataSet.AcceptChanges();
}
}
#endregion
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8781179/viewspace-924807/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8781179/viewspace-924807/