Common addition, deletion, modification, and query Functions

No matter what the system is, it is essential to add, delete, modify, and query databases. The following are the built-in functions for adding, deleting, modifying, and querying databases. They are for your reference only.

Using system; using system. componentmodel; using system. collections; using system. diagnostics; using system. data; using system. data. sqlclient; using system. configuration; using onlineexam. helperclass; using onlineexam. commonclass; namespace onlineexam. databaseclass {// database interface class public class database {// Private variable, database connection protected sqlconnection connection; protected string connectionstring; // constructor public database () {Conne Ctionstring = "Data Source = (local); database = mronlineexam; user id = sa; Password = 123456;" ;}// protection method, open the database connection private void open () {// determine whether the database connection exists if (connection = NULL) {// does not exist. Create and open connection = new sqlconnection (connectionstring); connection. open ();} else {// exists to determine whether it is in the closed state if (connection. state. equals (connectionstate. closed) connection. open (); // The connection is closed, re-open} // public method, close the database connection public void close () {If (connection. State. equals (connectionstate. open) {connection. close (); // The connection is on and the connection is closed} /// <summary> // destructor to release unmanaged resources /// </Summary> ~ Database () {try {If (connection! = NULL) connection. close () ;}catch {}try {dispose () ;}catch {}// public method to release the public void dispose () {If (connection! = NULL) // make sure the connection is closed {connection. dispose (); connection = NULL ;}// public method. Based on the SQL statement, whether the public bool getrecord (string xsqlstring) {open () record is queried is returned (); sqldataadapter adapter = new sqldataadapter (xsqlstring, connection); dataset = new dataset (); adapter. fill (Dataset); close (); If (Dataset. tables [0]. rows. count> 0) {return true;} else {return false;} // public method, return the data value obtained by the SQL statement // sqlstring format: Select count (*) from XXX whe Re... // select max (XXX) from YYY where... public int getrecordcount (string xsqlstring) {string scount; open (); sqlcommand cmd = new sqlcommand (xsqlstring, connection); scount = cmd. executescalar (). tostring (). trim (); If (scount = "") scount = "0"; close (); Return convert. toint32 (scount);} // public method. update some records in the xtablename table based on xwhere. // xtablename -- table name // xht -- hash table. The key is the field name, the value is the field value public dataset advancedsearch (string xtablena Me, hashtable xht) {int COUNT = 0; string fields = ""; foreach (dictionaryentry item in xht) {If (count! = 0) {fields + = "and";} fields + = item. key. tostring (); fields + = "like '%"; fields + = item. value. tostring (); fields + = "% '"; count ++;} fields + = ""; string sqlstring = "select * from" + xtablename + "where" + fields; open (); sqldataadapter adapter = new sqldataadapter (sqlstring, connection); dataset DS = new dataset (); adapter. fill (DS); close (); Return Ds;} // Private method to obtain a sqlcommand used to call the Stored Procedure // input: // P Rocname-stored procedure name // Params-used to call the parameter table private sqlcommand createcommand (string procname, sqlparameter [] prams) {open (); sqlcommand cmd = new sqlcommand (procname, connection); cmd. commandtype = commandtype. storedprocedure; If (prams! = NULL) {foreach (sqlparameter parameter in prams) cmd. parameters. add (parameter) ;}return cmd ;}// private method, run the SQL command // input: // strname-stored procedure name // Params-used to call the parameter table private sqlcommand createstrcommand (string strname, sqlparameter [] prams) {open (); sqlcommand cmd = new sqlcommand (strname, connection); cmd. commandtype = commandtype. text; If (prams! = NULL) {foreach (sqlparameter parameter in prams) cmd. parameters. add (parameter) ;}return cmd ;}// public method, instantiate a parameter used to call a stored procedure // input: // paramname-parameter name // dbtype-parameter type // size-parameter size // ction-transfer direction // value-value public sqlparameter makeparam (string paramname, sqldbtype dbtype, int32 size, parameterdirection direction, object Value) {sqlparameter Param; If (size> 0) Param = new sqlparameter (paramname, Dbtype, size); else Param = new sqlparameter (paramname, dbtype); Param. Direction = direction; If (value! = NULL) Param. value = value; return Param;} // public method. instantiate an input parameter used to call a stored procedure. // input: // paramname-parameter name // dbtype-parameter type // size-parameter size // value-value public sqlparameter makeinparam (string paramname, sqldbtype dbtype, int size, object value) {return makeparam (paramname, dbtype, size, parameterdirection. input, value) ;}// public method, call the Stored Procedure (without parameters) // input: // procname stored procedure name // output: // The number of rows affected by the update, insert, and delete operations. In other cases, the value is-1 public. Int runproc (string procname) {int COUNT =-1; sqlcommand cmd = createcommand (procname, null); Count = cmd. executenonquery (); close (); Return count ;}// public method, call a stored procedure (without parameters) // input: // procname stored procedure name // output: // number of rows affected by the update, insert, and delete operations. In other cases, the value is-1 Public int runstr (string strname) {int COUNT =-1; sqlcommand cmd = createstrcommand (strname, null); Count = cmd. executenonquery (); close (); Return count ;} // Public method: Call a stored procedure (with parameters) // input: // procname-name of the stored procedure // Params-parameter table used to call the Stored Procedure // output: // The number of rows affected by the update, insert, and delete operations. In other cases, the value is-1 Public int runproc (string procname, sqlparameter [] Params) {int COUNT =-1; sqlcommand cmd = createcommand (procname, Params); Count = cmd. executenonquery (); close (); Return count ;}// public method, run the specified SQL statement // input: // procstr-SQL statement // output: // The number of rows affected by the update, insert, and delete operations. In other cases, the value is-1 public. Int procstr (string strname, sqlparameter [] Params) {int COUNT =-1; sqlcommand cmd = createstrcommand (strname, Params); Count = cmd. executenonquery (); close (); Return count ;}// public method, call a stored procedure (without parameters) // input: // procname stored procedure name // output: // return the execution result with sqldatareader // Note: sqldatareader is called after use. close () method public sqldatareader runprocgetreader (string procname) {sqlcommand cmd = createcommand (procname, null); Return cmd. Executereader (system. data. commandbehavior. closeconnection);} // public method, call the stored procedure (with parameters) // input: // procname-stored procedure name // Params-parameters required for the stored procedure // output: // return the execution result with sqldatareader // Note: sqldatareader is called after use. close () method public sqldatareader runprocgetreader (string procname, sqlparameter [] Params) {sqlcommand cmd = createcommand (procname, Params); Return cmd. executereader (system. data. commandbehavior. closeconnection);} // public method, execute s QL command // input: // strname-stored procedure name // Params-parameters required for executing the SQL command // output: // return the execution result using sqldatareader // note: call sqldatareader after use. close () method public sqldatareader runstrgetreader (string strname, sqlparameter [] Params) {sqlcommand cmd = createstrcommand (strname, Params); Return cmd. executereader (system. data. commandbehavior. closeconnection);} // public method, call the stored procedure (with parameters) // input: // procname-stored procedure name // Params-parameters required for the stored procedure // output: // Execute The result is returned using sqldatareader. // Note: The sqldatareader is called after use. close () method public int runprocgetcount (string procname, sqlparameter [] Params) {sqlcommand cmd = createcommand (procname, Params); string scount; scount = cmd. executescalar (). tostring (). trim (); If (scount = "") scount = "0"; close (); Return convert. toint32 (scount);} // public method, call the Stored Procedure (without parameters) // input: // procname stored procedure name // output: // return the execution result as dataset to public dataset getdata Set (string procname) {open (); sqldataadapter adapter = new sqldataadapter (procname, connection); dataset = new dataset (); adapter. fill (Dataset); close (); Return dataset;} // public method, execute SQL command // input: // strname stored procedure name // output: // return the execution result as dataset public dataset getstrdataset (string strname) {open (); sqldataadapter adapter = new sqldataadapter (strname, connection); dataset = new dataset (); adapt Er. fill (Dataset); close (); Return dataset;} // public method, call a stored procedure (without parameters) // input: // procname stored procedure name // output: // return the execution result as dataset public dataset getdataset (string procname, sqlparameter [] Params) {open (); sqlcommand cmd = createcommand (procname, Params ); sqldataadapter adapter = new sqldataadapter (CMD); dataset = new dataset (); adapter. fill (Dataset); close (); Return dataset;} // public method, call the Stored Procedure (without parameters) // input: // PR Ocname stored procedure name // output: // return the execution result to the public dataset getstrdatasetsql (string strname, sqlparameter [] Params) {open (); sqlcommand cmd = createstrcommand (strname, params); sqldataadapter adapter = new sqldataadapter (CMD); dataset = new dataset (); adapter. fill (Dataset); close (); Return dataset;} // public method. Based on the SQL statement, a result dataset public dataset getdatasetsql (string xsqlstring) {open (); sqldataadapter is returned. Adapter = new sqldataadapter (xsqlstring, connection); dataset DS = new dataset (); adapter. fill (DS); close (); Return Ds;} // public method. According to the SQL statement, the insert record public int insert (string xsqlstring) {int COUNT =-1; open (); sqlcommand cmd = new sqlcommand (xsqlstring, connection); Count = cmd. executenonquery (); close (); Return count;} // public method. Insert a record based on the SQL statement and return the generated ID number public int getidinsert (string xsqlstring) {int count. =-1; open (); sqlcommand cmd = new sqlcommand (xsqlstring, connection); Count = convert. toint32 (CMD. executescalar (). tostring (). trim (); close (); Return count;} // public method to obtain data and return a datarow. Public datarow getdatarow (string sqlstring) {dataset = getdataset (sqlstring); dataset. casesensitive = false; If (Dataset. tables [0]. rows. count> 0) {return dataset. tables [0]. rows [0] ;}else {return NULL ;}/// <summary> /// public method to update a data table. /// </Summary> /// <Param name = "tablename"> table name </param> /// <Param name = "Cols"> Hasse table, the key value is the field name, the value is the field value </param> /// <Param name = "where"> where clause </param> /// <returns> whether the clause is successful </returns> Public bool update (string tablename, hashtable cols, string where) {int COUNT = 0; If (cols. count <= 0) {return true;} string fields = ""; foreach (dictionaryentry item in Cols) {If (count! = 0) {fields + = ",";} fields + = "[" + item. key. tostring () + "]"; fields + = "="; fields + = item. value. tostring (); count ++;} fields + = ""; string sqlstring = "Update" + tablename + "set" + fields + where; string [] sqls = {sqlstring}; return executesql (sqls);} public bool executesql (string [] sqlstrings) {bool success = true; open (); sqlcommand cmd = new sqlcommand (); sqltransaction trans = Connection. begintransaction (); cmd. connection = connection; cmd. transaction = trans; int I = 0; try {foreach (string STR in sqlstrings) {cmd. commandtext = STR; cmd. executenonquery (); I ++;} trans. commit () ;}catch {success = false; trans. rollback (); close () ;}finally {close () ;}return success ;}/// <summary> /// public method to obtain data and return a datatable. /// </Summary> /// <Param name = "sqlstring"> SQL statement </param> /// <returns> datatable </returns> Public datatable getdatatable (string sqlstring) {dataset = getdataset (sqlstring); dataset. casesensitive = false; return dataset. tables [0] ;}}

Common addition, deletion, modification, and query Functions