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

来源：作者：出处：巧巧读书 2006-09-11 进入讨论组

　　使用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 COMM转载保留：http://www.qqread.com/csharp/u237422.html