数据库操作类实现(C#,SqlClient)

使用ADO.NET时，每次数据库操作都要设置connection属性、建立connection、使用command、事务处理等，比较繁琐，有很多重复工作。能不能把这些繁琐的、常用的操作再封装一下，以更方便、安全地使用。下面这个类就是一种尝试：

using System;
using System.Data.SqlClient;
using System.Text;
using System.Data;
using System.Collections;
using System.Configuration;

  public class DBAccess
  {
   /// <summary>
   /// Declare the ole db required objects
   /// </summary>
  

   /// <summary>
   /// An ole db adapter to act as the bridge to the database
   /// </summary>
   private SqlDataAdapter dbDataAdapter;
   /// <summary>
   /// The connection to the database
   /// </summary>
   private SqlConnection dbConnection;
   /// <summary>
   /// The command for doing the inserts
   /// </summary>
   private SqlCommand dbInsertCommand;
   /// <summary>
   /// The command for doing the deletes
   /// </summary>
   private SqlCommand dbDeleteCommand;
   /// <summary>
   /// The command for doing the updates
   /// </summary>
   private SqlCommand dbUpdateCommand;
   /// <summary>
   /// The command for doing the Selects
   /// </summary>
   private SqlCommand dbSelectCommand;

   private SqlCommand dbSelectCommandofAdapter;

   /// <summary>
   /// The command for get dataset
   /// </summary>
   private SqlDataAdapter dataAdapterCommand;

   /// <summary>
   /// The data reader for the application
   /// </summary>
   public SqlDataReader dbDataReader;

   /// <summary>
   /// Declare an enum to allow internal tracking of commands
   /// </summary>
   enum COMMAND{ NONE, INSERT, UPDATE, DELETE, SELECT,DATASET };
 
   /// <summary>
   /// Internal member for tracking command progress
   /// </summary>
   private COMMAND command;

   /// <summary>
   /// String to hold error messages if a command fails
   /// </summary>
   private string error;

   /// <summary>
   /// Get a stored error message if ExecuteCommand fails
   /// </summary>
   public string ErrorMessage
   {
    get
    {
     return error;
    }
   }

   /// <summary>
   /// bool holder for is open
   /// </summary>
   private bool bOpen;

   /// <summary>
   /// Check to see if a data base is open
   /// </summary>
   public bool IsOpen
   {
    get
    {
     return bOpen;
    }
   }

   /// <summary>
   /// Declare a string object for the insert command
   /// </summary>
   public string InsertCommand 
   {
    get
    {
     return dbInsertCommand.CommandText;
    }
    set 
    {
     command = COMMAND.INSERT;
     dbInsertCommand.CommandText = value;
    }
   }

   /// <summary>
   /// Declare a string object for the delete command
   /// </summary>
   public string DeleteCommand
   {
    get
    {
     return dbDeleteCommand.CommandText;
    }
    set
    {
     command = COMMAND.DELETE;
     dbDeleteCommand.CommandText = value;
    }
   }

   /// <summary>
   /// Declare a string object for the update command
   /// </summary>
   public string UpdateCommand
   {
    get
    {
     return dbUpdateCommand.CommandText;
    }
    set
    {
     command = COMMAND.UPDATE;
     dbUpdateCommand.CommandText = value;
    }
   }

   /// <summary>
   /// Declare a string object for the select command
   /// </summary>
   public string SelectCommand
   {
    get
    {
     return dbSelectCommand.CommandText;
    }
    set
    {
     command = COMMAND.SELECT;
     dbSelectCommand.CommandText = value;
    }
   }

   public string SelectDataSetCommand
   {
    get
    {
     return dataAdapterCommand.SelectCommand.CommandText;
    }
    set
    {
     command = COMMAND.DATASET;
     dataAdapterCommand.SelectCommand.CommandText = value;
    }
   }

   /// <summary>
   /// Get the reader from the class
   /// </summary>
   public SqlDataReader GetReader
   {
    get
    {
     switch( command )
     {
      case COMMAND.NONE: return null;
      case COMMAND.DELETE: return DeleteReader;
      case COMMAND.INSERT: return InsertReader;
      case COMMAND.SELECT: return SelectReader;
      case COMMAND.UPDATE: return UpdateReader;
      default: return null;
     }
    }
   }

   public DataSet GetDataSet
   {
    get
    {
     switch( command )
     {
      case COMMAND.DATASET: return SelectDataSet();
      default: return null;
     }
    }
   }

   public DataSet SelectDataSet()
   {
    try
    {
     dataAdapterCommand.SelectCommand.Connection = dbConnection;
     DataSet dataset = new DataSet();
     dataAdapterCommand.Fill(dataset);
     return dataset;
    }
    catch (Exception exp)
    {
     error = exp.Message;
     return null;
    }

   }

   /// <summary>
   /// Execute the command that has been set up previously
   /// </summary>
   /// <returns>A boolean value indicating true or false</returns>
   public bool ExecuteCommand()
   {
    bool bReturn = false;
    if( command == COMMAND.NONE )
    {
     return bReturn;
    }
    else if( command == COMMAND.SELECT )
    {
     /// select only returns true as the get reader function will
     /// execute the command

     try
     {
      if( dbDataReader != null )
      {
       dbDataReader.Close();
       dbDataReader = null;
      }

      bReturn = true;
      ///   return bReturn;
     }
     catch( SqlException exp )
     {
      error = "dbException thrown when trying to Select, error given = " + exp.Message + " check the sql";
      return bReturn = false;
     }

    }
    else if( command == COMMAND.DATASET )
    {
     return bReturn;     
    }
    else
    {
     int nAffected = -1;

     if( dbDataReader != null )
     {
      dbDataReader.Close();
      dbDataReader = null;
     }

     /// get the transaction object from the connection
     SqlTransaction trans = dbConnection.BeginTransaction();

     try
     {
      /// create a nested transaction on the connection transaction
      switch( command )
      {
       case COMMAND.DELETE: dbDeleteCommand.Transaction = trans; break;
       case COMMAND.INSERT: dbInsertCommand.Transaction = trans; break;
       case COMMAND.UPDATE: dbUpdateCommand.Transaction = trans; break;
      }

      /// execute the command
      switch( command )
      {
       case COMMAND.DELETE: nAffected = dbDeleteCommand.ExecuteNonQuery(); break;
       case COMMAND.INSERT: nAffected = dbInsertCommand.ExecuteNonQuery(); break;
       case COMMAND.UPDATE: nAffected = dbUpdateCommand.ExecuteNonQuery(); break;
      }

     }
     catch( InvalidOperationException ioexp )
     {
      StringBuilder buildError = new StringBuilder();
      buildError.Append( "InvalidOperationException thrown when trying to " );

      switch( command )
      {
       case COMMAND.DELETE: buildError.Append( "Delete" ); break;
       case COMMAND.INSERT: buildError.Append( "Insert" ); break;
       case COMMAND.UPDATE: buildError.Append( "Update" ); break;
      }

      buildError.Append( ", error given = " + ioexp.Message + " check the sql" );

      error = buildError.ToString();

      return bReturn = false;
     }
     catch( SqlException dbexp )
     {
      StringBuilder buildError = new StringBuilder();
      buildError.Append( "InvalidOperationException thrown when trying to " );

      switch( command )
      {
       case COMMAND.DELETE: buildError.Append( "Delete" ); break;
       case COMMAND.INSERT: buildError.Append( "Insert" ); break;
       case COMMAND.UPDATE: buildError.Append( "Update" ); break;
      }

      buildError.Append( ", error given = " + dbexp.Message + " check the sql" );

      error = buildError.ToString();

      return bReturn = false;
     }
     finally
     {
      /// commit the command
      if( nAffected == 1 )
      {
       switch( command )
       {
        case COMMAND.DELETE: dbDeleteCommand.Transaction.Commit(); break;
        case COMMAND.INSERT: dbInsertCommand.Transaction.Commit(); break;
        case COMMAND.UPDATE: dbUpdateCommand.Transaction.Commit(); break;
       }

       //trans.Commit();

       bReturn = true;
      }
      else /// if something went wrong rollback
      {
       switch( command )
       {
        case COMMAND.DELETE: dbDeleteCommand.Transaction.Rollback(); break;
        case COMMAND.INSERT: dbInsertCommand.Transaction.Rollback(); break;
        case COMMAND.UPDATE: dbUpdateCommand.Transaction.Rollback(); break;
       }
      
       //trans.Rollback();

       bReturn = false;
      }
     }
    }

    return bReturn;
   }

   #region select functions

   /// <summary>
   /// Get the Select reader from the select command
   /// </summary>
   private SqlDataReader SelectReader
   {
    get
    {
     if( dbDataReader != null )
     {
      if( dbDataReader.IsClosed == false )
      {
       dbDataReader.Close();
       dbDataReader = null;
      }
     }

     dbDataReader = dbSelectCommand.ExecuteReader();
     return dbDataReader;
    }
   }

   /// <summary>
   /// Get the Update reader from the update command
   /// </summary>
   private SqlDataReader UpdateReader
   {
    get
    {
     if( dbDataReader.IsClosed == false )
      dbDataReader.Close();

     dbDataReader = dbSelectCommand.ExecuteReader();
     return dbDataReader;
    }
   }

   /// <summary>
   /// Get the Insert Reader from the Insert Command
   /// </summary>
   private SqlDataReader InsertReader
   {
    get
    {
     if( dbDataReader.IsClosed == false )
      dbDataReader.Close();

     dbDataReader = dbSelectCommand.ExecuteReader();
     return dbDataReader;
    }
   }

   /// <summary>
   /// Get the Delete Reader from the Delete Command
   /// </summary>
   private SqlDataReader DeleteReader
   {
    get
    {
     if( dbDataReader != null )
     {
      if( dbDataReader.IsClosed == false )
      {
       dbDataReader.Close();
       dbDataReader = null;
      }
     }

     dbDataReader = dbSelectCommand.ExecuteReader();
     return dbDataReader;
    }
   }

   #endregion

   /// <summary>
   /// Standard Constructor
   /// </summary>
   public DBAccess()
   {
    /// NOTE That we are not setting the commands up the way the wizard would
    /// but building them more generically

    // create the command variables
    dbDataAdapter = new SqlDataAdapter();
    dbConnection = new SqlConnection();
    dbSelectCommand = new SqlCommand();
    dbDeleteCommand = new SqlCommand();
    dbUpdateCommand = new SqlCommand();
    dbInsertCommand = new SqlCommand();

    /// set up the adapter
    dbDataAdapter.DeleteCommand = dbDeleteCommand;
    dbDataAdapter.InsertCommand = dbInsertCommand;
    dbDataAdapter.SelectCommand = dbSelectCommand;
    dbDataAdapter.UpdateCommand = dbUpdateCommand;

    /// make sure everyone knows what conection to use
    dbSelectCommand.Connection = dbConnection;
    dbDeleteCommand.Connection = dbConnection;
    dbUpdateCommand.Connection = dbConnection;
    dbInsertCommand.Connection = dbConnection;

    command = COMMAND.NONE;
    dbDataReader = null;

    dbSelectCommandofAdapter = new SqlCommand();
    dataAdapterCommand = new SqlDataAdapter();
    dataAdapterCommand.SelectCommand = dbSelectCommandofAdapter;
   }

    public void Open()
   {
    /// set up the connection string
    StringBuilder strBuild = new StringBuilder();

  //Connection的属性从配置文件读取
    strBuild.AppendFormat(ConfigurationSettings.AppSettings["DBConnection"]);
    
    dbConnection.ConnectionString = strBuild.ToString();

    try
    {
     dbConnection.Open();
     bOpen = true;
    }
    catch (Exception exp)
    {
     error = exp.Message;
    }

    
   }

   /// <summary>
   /// Close the currently open connection
   /// </summary>
   public void Close()
   {
    if (dbDataReader != null)
    {
     if( dbDataReader.IsClosed == false )
     {
      dbDataReader.Close();
      dbDataReader = null;
     }
    }

    dbConnection.Close();
   }

  }

 

使用示例：

Insert操作，新建用户：

public bool NewUser()
  {
   DBAccess newUserDBAccess = new DBAccess();
   StringBuilder sqlStr = new StringBuilder();
   sqlStr.Append( "Insert into userTable(usrName,pwd,name,depart,role,available) values(");
   sqlStr.Append(  "'" + usrName + "',");
   sqlStr.Append(  "'" + pwd + "',");
   sqlStr.Append(  "'" + name + "',");
   sqlStr.Append(  "'" + depart + "',");
   sqlStr.Append(  "'" + role + "',");
   sqlStr.Append(1);
   sqlStr.Append(  ")");
   
   newUserDBAccess.InsertCommand = sqlStr.ToString();
   newUserDBAccess.Open();
   try
   {
    if (!newUserDBAccess.ExecuteCommand())
    {
     errMsg = newUserDBAccess.ErrorMessage;
     
     return false;
    }
    else
    {
     return true;
    }
   }
   finally
   {
    newUserDBAccess.Close();
   }

  }

 

Update操作，修改用户信息：

public bool ModifyUser()
  {
   DBAccess modifyUserDBAccess = new DBAccess();
   StringBuilder sqlStr = new StringBuilder();
   sqlStr.Append( "update userTable set ");
   sqlStr.Append(  " usrName = ");
   sqlStr.Append(  "'" + usrName + "',");
   sqlStr.Append(  " name =");
   sqlStr.Append(  "'" + name + "',");
   sqlStr.Append(  " pwd =");
   sqlStr.Append(  "'" + pwd + "',");
   sqlStr.Append(  " depart =");
   sqlStr.Append(  "'" + depart + "',");
   sqlStr.Append(  " role =");
   sqlStr.Append(  "'" + role + "'");
   sqlStr.Append(  "  where usrID = ");
   sqlStr.Append(id);
   
   modifyUserDBAccess.UpdateCommand = sqlStr.ToString();
   modifyUserDBAccess.Open();
   try
   {
    if (!modifyUserDBAccess.ExecuteCommand())
    {
     errMsg = modifyUserDBAccess.ErrorMessage;
     
     return false;
    }
    else
    {
     return true;
    }
   }
   finally
   {
    modifyUserDBAccess.Close();
   }

  }

 

Delete操作，删除用户：
  public static bool DelUser(int usrID)
  {
   DBAccess delUserDBAccess = new DBAccess();
   StringBuilder sqlStr = new StringBuilder();
   sqlStr.Append( "update userTable set ");
   sqlStr.Append(  " available =");
   sqlStr.Append(0);
   sqlStr.Append(  "  where usrID = ");
   sqlStr.Append(usrID);
   
   
   delUserDBAccess.UpdateCommand = sqlStr.ToString();
   delUserDBAccess.Open();
   try
   {
    if (!delUserDBAccess.ExecuteCommand())
    {         
     return false;
    }
    else
    {
     return true;
    }
   }
   finally
   {
    delUserDBAccess.Close();
   }

  }