Database operation class implementation (C #, SQLCLIENT)

xiaoxiao2021-03-06 99

When using ADO.NET, each database operation is set to set the connection property, establish a connection, use Command, transaction processing, etc., more cumbersome, there are many duplicates. Can you add these cumbersome and commonly used operations to use it more convenient and safely. Below this is an attempt:

Using system; using system.text; using system.data; using system.collections;

Public class dbaccess {///

/// Declare the ole db required Objects ///

///

/// An ole DB adapter to act as the bridge to the database /// private sqldataadapter dbdataadapter; ///

///////// < / summary> private sqlconnection dbconnection; ///

//// private sqlcommand dbinsertcommand; ///

/// the command for doing the deletes //// / private sqlcommand dbdeleteCommand; ///

///// private sqlcommand dbupdateCommand; ///

/// The Command for doing the selects /// private sqlcommand dbselectcommand;

Private sqlcommand dbselectcommandofadapter;

///

/// The command for get dataset /// private sqldataadapter dataadaptercommand;

///

///// public sqldatarader dbdatarader;

///

/// Declare an enum to allow internal tracking of commons /// enormand {none, insert, update, delete, select, dataset}; ///

//// INTERNAL MEMBER for Tracking Command Progress /// private command Command;

///

/// String to Hold Error Messages if a commist fan /// private string error; ///

/// Get a stored error message if ExecuteCommand fails /// < / summary> public string errorMessage {get {return error;}}

///

/// Bool Holder for is open /// private bool bopen;

///

/// check to see if a data base is open /// public bool isopen {get {return bopen;}}

///

/// Declare a string object for the insert command /// public string InsertCommand {get {return dbInsertCommand.CommandText;} set {command = COMMAND.INSERT; dbInsertCommand.CommandText = value; }

///

/// Declare a string object for the delete command /// public string DeleteCommand {get {return dbDeleteCommand.CommandText;} set {command = COMMAND.DELETE; dbDeleteCommand.CommandText = value; }

///

/// Declare a string object for the update command /// public string UpdateCommand {get {return dbUpdateCommand.CommandText;} set {command = COMMAND.UPDATE; dbUpdateCommand.CommandText = value; }

///

/// Declare a string object for the select command /// 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;}} ///

/// Get the reader from the class /// 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) {copy 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; }

}

///

/////////// /// a boolean value indicating true {{/ / om () {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) {dbdataareader.close (); dbdataareader = 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) {dbdataareader.close (); dbdataareader = 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 oExp) {stringbuilder builderror = new stringbuilder (); builderror.Append ("InvalidOperationException THROWN WHEN TRYING TO);

Switch (command) {copy command.delete: builderror.Append ("delete"); break; case command.insert: builderror.Append ("insert"); Break; Case Command.Uden: 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");

Switch (command) {copy command.delete: builderror.Append ("delete"); break; case command.insert: builderror.Append ("insert"); Break; Case Command.Uden: 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; copy 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

///

/// Get the Select reader from the select command /// private SqlDataReader SelectReader {get {if (dbDataReader! = Null) {if (dbDataReader.IsClosed == false) {dbDataReader. Close (); dbdataareader = null;}} dbdatareader = dbselectcommand.executeReader (); return dbdatarader;}}

///

/// get the update reader from the update command /// private sqldatareader updateReader {get {i (dbdataareader.isclosed == false) dbdatareader.close ();

DBDataReader = dbselectcommand.executeReader (); return dbdatarader;}}

///

/// Get the insert reader from the insert command /// private sqldatareader insertreader {get {ife (dbdataareader.isclosed == false) dbdatareader.close ();

DBDataReader = dbselectcommand.executeReader (); return dbdatarader;}}

///

/// Get the delete reader from the delete command /// private sqldataareader deletereader {get {= null) {if (dbdataareader.isclosed == false) {dbdataareader. Close (); dBDataReader = null;}}

DBDataReader = dbselectcommand.executeReader (); return dbdatarader;}}

#ndregion

///

/// standard constructor /// public dbaccess () {/// Note That We Are not setting the commands up The Way the wizard 10 /// But building Them More generiL

// 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; dbdataareader = null;

DBSelectCommandofadapter = new sqlcommand (); dataadaptercommand = new sqldataadapter (); dataadaptercommand.selectCommand = dbselectcommandofadapter;

Public void open () {/// set up the connection string stringbuilder strbuild = new stringbuilder ();

// Connection's property reads strbuild.appendformat ("dbconnection"); dbconnection.connectionstring = strbuild.connectionstring = strbuild.tostring ();

Try {dbconnection.open (); bopen = true;} catch (exception exp) {error = exp.Message;}

}

///

/// close the currently open connection /// public void close () {if (dbdataareader! = null) {if (dbdataareader.isclosed == false) {dbdataareader.close () DBDataReader = NULL;}}

dbconnection.close ();

}

Use example:

Insert operation, the 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 ("'" divart "); sqlstr.Append (" role = "); sqlstr.Append ("'" Role "'); sqlstr.Append (" where usrid = "); sqlstr.Append (ID); modify = sqlstr.UpdateCommand = SQLSTR.TOSTRING (); ModifyUserDbaccess.open (); try {= !..................................................... ..

Delete operation, delete users: 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;}}} finally {deluserdbaccess.close ();}}

转载请注明原文地址:https://www.9cbs.com/read-104215.html

9cbs

New Post(0)