一个通用数据库访问类（C#，SqlClient）
2024-07-21 02:19:17
供稿：网友
使用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();
}
}
