数据库操作类实现

使用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();

}

}