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

转载 2004年10月19日 14:44:00
使用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();
   }

  }

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

使用ADO.NET时,每次数据库操作都要设置connection属性、建立connection、使用command、事务处理等,比较繁琐,有很多重复工作。能不能把这些繁琐的、常用的操作再封装一下,以更...
  • mengyao
  • mengyao
  • 2006年05月02日 23:15
  • 1249

C#操作数据库简介之SqlClient模式

在C#中,常用的有三种 访问数据库的模式分别为:SqlClient模式、OleDb模式和Odbc模式。其中SqlClient模式是微软老大哥专门为其产品Sql Server数据库而设计的,所以如果欲使...
  • qq_34093024
  • qq_34093024
  • 2017年10月10日 20:10
  • 540

C#数据库操作类(完整通用)

using System; using System.Collections.Generic; using System.Text; using System.Data.SqlClient; ...
  • angela8804100029
  • angela8804100029
  • 2013年04月14日 19:09
  • 1845

C#.NET操作数据库通用类

下面给出了一个C#操作MS SQL Server 数据库的通用类,通过该类可以对数据库进行任何操作,包括执行SQL语句、执行存储过程。以下是其详细实现过程,希望大家共同修改优化之。稍后将介绍如何使用它...
  • ecjtuync
  • ecjtuync
  • 2007年04月25日 16:47
  • 1462

C#---数据库访问通用类、Access数据库操作类、mysql类

//C# 数据库访问通用类 (ADO.NET) using System; using System.Collections.Generic; using System.Text; using Sys...
  • cjh200102
  • cjh200102
  • 2011年09月27日 09:10
  • 3558

ACCESS数据库C#操作类(包含事务)

这个是针对ACCESS数据库操作的类,同样也是从SQLHELPER提取而来,分页程序的调用可以参考MSSQL那个类的调用,差不多的,只是提取所有记录的数量的时候有多一个参数,这个需要注意一下!c# 代...
  • allen3010
  • allen3010
  • 2011年04月20日 18:14
  • 2886

C#SQLServer数据库操作类

using System; using System.Collections.Generic; using System.Text; using System.Configuration; using...
  • zuoyefeng1990
  • zuoyefeng1990
  • 2017年03月15日 14:12
  • 261

.NET/C#/Oracle数据库操作类

using System; using System.Data; using System.Collections.Generic; using System.Configuration;...
  • znb769525443
  • znb769525443
  • 2014年07月16日 16:33
  • 342

C# 对数据库操作的帮助类SQLHelper.cs

在操作数据库的时候,制作一个SqlHelper类封装常用的方法,减少重复代码. 在配置文件App.config中加入数据库的连接信息例如 引用: ...
  • nepture911122
  • nepture911122
  • 2017年02月16日 10:26
  • 1969

c# 操作ACCESS数据库

c# 简单操作ACCESS
  • nanxizhu
  • nanxizhu
  • 2014年11月06日 20:10
  • 2325
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:数据库操作类实现(C#,SqlClient)
举报原因:
原因补充:

(最多只允许输入30个字)