数据库操作类实现(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();

   }

  }