A universal Database access Class (C#,sqlclient)

client| Access | data | Database using Ado.net, each database operation to set the connection attribute, establish connection, use command, transaction processing, etc., more cumbersome, there are a lot of duplication of work. Can you put these tedious, commonly used operations again encapsulation, in order to more convenient and safe to use. The following class is an attempt:

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 the 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 the 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 would
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 which 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 properties are read from the configuration file
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 ();
}

}



Use examples:

Insert operation, New User:

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 operation, modify user information:

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 operation, deleting User:
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 ();
}

}