Database operation class implementation (C #, SQLCLIENT)

xiaoxiao2021-03-06 61

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.

The following is an attempt class: using System; using System.Data.SqlClient; using System.Text; using System.Data; using System.Collections; using System.Configuration; public class DBAccess {///

// / Declare the ole DB required Objects /// ///

/// An ole DB adapter to act as the bridge to the dtabase /// private sqldataadapter dbdataadapter; /// < Summary> ///// private sqlconnection dbconnection; ///

/// private sqlcommand dbinsertcommand; ///

/// THE Command for doing the deletes /// private sqlcommand dbdeleteCommand; ///

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

///// private sqlcommand dbselectcommand; private sqlcommand dbselectcommandofadapter; ///

/// the command for get dataset /// private SqlDataAdapter DataAdapterCommand; ///

/// public sqldataareader dbdatarader; ///

/// Declare An Enum To Allow Internal Tracking of Commands /// Enum Command {None, Insert, Update, Delete, SELECT, DATASET}; ///

/// Internal Member for Tracking Command Progress /// private command command; ///

/// String to hold error message if a commman fails /// priz

///

/// Get a stored error message /// 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;}} /// < summary> /// 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 ) {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;}} ///

/// Execute the Command That Has B Een set up previously //// /// a boolean value indicating true or false public bool executeCommand () {bool breturn = false; if (command == comMMand.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 meansing to select, error given ="

Exp.Message "Check the SQL"; Return Breturn = false;}}} else =} {Return Breturn;} else {INT NAFFECTED = -1; if (DBDataReader! = null) {dbdataareader .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 . DelteCommand.executenonQuery (); break; case command.insert: naffected = dbinsertcommand.executenonQuery (); break; cascand.Update: naffected = dbupd ateCommand.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.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 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 {copy command.delete: dbdeleteCommand.Transaction. Commit (); break; break; case; break; break; break; breturn;} 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;}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}} #region select functions ///

/// Get the select reader from the select command /// Private SqldataReader Selectreader {Get {IF (DBDataReader! = NULL) {if (dbdataareader.isclosed ==

false) {dbDataReader.Close (); dbDataReader = null;}} dbDataReader = dbSelectCommand.ExecuteReader (); return dbDataReader;}} ///

/// Get the Update reader from the update command /// private SqlDataReader UpdateReader {get {if (dbDataReader.IsClosed == false) dbDataReader.Close (); dbDataReader = dbSelectCommand.ExecuteReader (); return dbDataReader;}} ///

/// Get the Insert Reader from the Insert Command /// private SqlDataReader InsertReader {get {if (dbDataReader.IsClosed == false) dbDataReader.Close (); dbDataReader = dbSelectCommand.ExecuteReader (); return dbDataReader;}} ///

/// Get the delete reader from the delete command /// private sqldataareader deleteReader {get {i (dbdataareader! = Null) {if (dbdatareader.isclosed == false) {dbdataad er.Close (); dbDataReader = null;}} dbDataReader = dbSelectCommand.ExecuteReader (); return dbDataReader;}} #endregion ///

/// Standard Constructor /// 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 properties reading strBuild.AppendFormat (ConfigurationSettings.AppSettings [ "dBConnection"]) from the configuration file; dbConnection. Connectionstring = strbuild.toString (); try {dbconnect ION.Open (); bopen = true;} catch (exception exp) {error = exp .MESSAGE;}} ///

/// close the currently open connection /// public void close ) {If (dbdataareader! = Null) {if (dbdataareader.isclosed == false) {dbdatareader.close (); dbdataareader = null;}}}}}}}} 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 ("' " divart "); 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 ();

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

9cbs

New Post(0)