ASP.NET开发 封装的DB类（一）

下面是我对在C#开发过程中用到的数据库BaseDao的有关代码的整理

1对传入参数的处理

  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 )
{
// Check for derived output value with no value assigned
if ( ( p.Direction == ParameterDirection.InputOutput || 
p.Direction == ParameterDirection.Input ) && 
(p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
}
        } 

2  用于创建一个关联Connection可以执行的Command，

 private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection )
        {
if( command == null ) throw new ArgumentNullException( "command" );
if( commandText == null || commandText.Length == 0 ) throw new ArgumentNullException( "commandText" );


            // If the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
mustCloseConnection = true;
connection.Open();
                
}
else
{
mustCloseConnection = false;
}
            
            // Associate the connection with the command
            command.Connection = connection;
            command.CommandTimeout = 0;
            // Set the command text (stored procedure name or SQL statement)
            command.CommandText = commandText;


            // If we were provided a transaction, assign it
            if (transaction != null)
            {
if( transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
                command.Transaction = transaction;
            }


            // Set the command type
            command.CommandType = commandType;


            // Attach the command parameters if they are provided
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
            return;
        }

3执行sql语句

      public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
if( connection == null ) throw new ArgumentNullException( "connection" );


            // Create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();cmd.CommandTimeout = 0;
bool mustCloseConnection = false;
            PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );
   
            // Finally, execute the command
            int retval = cmd.ExecuteNonQuery();
   
            // Detach the SqlParameters from the command object, so they can be used again
            cmd.Parameters.Clear();
if( mustCloseConnection )
connection.Close();
            return retval;
        }

4  获取dataset对象

public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if( transaction == null ) throw new ArgumentNullException( "transaction" );
if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );


// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();cmd.CommandTimeout = 0;
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );
   
// Create the DataAdapter & DataSet
using( SqlDataAdapter da = new SqlDataAdapter(cmd) )
{
DataSet ds = new DataSet();


// Fill the DataSet using default values for DataTable names, etc
da.Fill(ds);
   
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();


// Return the dataset
return ds;
}
}