C # basic class library su Fei edition-series tutorial navigation

1. The data access base class (based on SQLite) is mainly used to access the SQLite database.
2. obtain the maximum value, whether it exists, and whether it exists (based on sqliteparameter );
3. Execute the SQL statement and return the number of affected records
4. Execute Multiple SQL statements to implement database transactions.
5. Execute the SQL statement with a stored procedure parameter.
6. Insert image format fields into the database (another example similar to the above)
7. Execute a statement to calculate the query result and return the query result (object ).
8. Execute the query statement and return sqlitedatareader
9. Execute the query statement and return Dataset
10. Execute the SQL statement and return the number of affected records
11. Execute Multiple SQL statements to implement database transactions.
12. Execute a statement to calculate the query result and return the query result (object ).
13. Execute the query statement and return sqlitedatareader
14. Execute the query statement and return the dataset parameter.

/// <Summary> /// Code Editor: su Fei // contact information: 361983679 // update the website: <a href = \ "http://www.sufeinet.com/thread-655-1-1.html\" target = \ "_ blank \"> http://www.sufeinet.com/thread-655-1-1.html </a> /// </Summary> using system; using system. collections; using system. collections. specialized; using system. data; using system. configuration; using system. data. SQLite; namespace maticsoft. dbutility {// <summary> // Data Access Base Class (based on SQLite )/ // You can modify the settings to meet your project needs. /// </Summary> public abstract class dbhelpersqlite {// database connection string (web. you can dynamically change connectionstring to support multiple databases. public static string connectionstring = "connection string"; Public dbhelpersqlite () {}# Region Public method public static int getmaxid (string fieldname, string tablename) {string strsql = "select max (" + fieldname + ") + 1 from" + tablename; object OBJ = getsingle (strsql); If (OBJ = NULL) {return 1;} els E {return Int. parse (obj. tostring () ;}} public static bool exists (string strsql) {object OBJ = getsingle (strsql); int cmdresult; If (object. equals (OBJ, null) | (object. equals (OBJ, system. dbnull. value) {cmdresult = 0;} else {cmdresult = int. parse (obj. tostring ();} If (cmdresult = 0) {return false;} else {return true;} public static bool exists (string strsql, Params sqliteparamete R [] Using parms) {object OBJ = getsingle (strsql, using parms); int cmdresult; If (object. equals (OBJ, null) | (object. equals (OBJ, system. dbnull. value) {cmdresult = 0;} else {cmdresult = int. parse (obj. tostring ();} If (cmdresult = 0) {return false;} else {return true ;}} # endregion # region executes simple SQL statements /// <summary> /// Execute SQL statements, returned Number of affected records /// </Summary> /// <Param name = "sqlstring"> SQL statement </param> /// <Returns> Number of affected records </returns> Public static int executesql (string sqlstring) {using (sqliteconnection connection = new sqliteconnection (connectionstring )) {using (sqlitecommand cmd = new sqlitecommand (sqlstring, connection) {try {connection. open (); int rows = cmd. executenonquery (); Return rows;} catch (system. data. SQLite. sqliteexception e) {connection. close (); throw new exception (E. message) ;}}}/// <Summary> // execute multiple SQL statements to implement database transactions. /// </Summary> /// <Param name = "sqlstringlist"> Multiple SQL statements </param> Public static void executesqltran (arraylist sqlstringlist) {using (sqliteconnection conn = new sqliteconnection (connectionstring) {Conn. open (); sqlitecommand cmd = new sqlitecommand (); cmd. connection = conn; sqlitetransaction Tx = Conn. begintransaction (); cmd. transaction = TX; try {for (INT n = 0; n <sqlstringlist. count; n ++) {St Ring strsql = sqlstringlist [N]. tostring (); If (strsql. trim (). length> 1) {cmd. commandtext = strsql; cmd. executenonquery () ;}} Tx. commit ();} catch (system. data. SQLite. sqliteexception e) {Tx. rollback (); throw new exception (E. message) ;}}/// <summary> // execute an SQL statement with a stored procedure parameter. /// </Summary> /// <Param name = "sqlstring"> SQL statement </param> /// <Param name = "content"> parameter content, for example, a field is a complex article with special symbols, you can add </param> // <returns> Number of affected records </returns> Public static int executesql (string sqlstring, string content) in this way) {using (sqliteconnection connection = new sqliteconnection (connectionstring) {sqlitecommand cmd = new sqlitecommand (sqlstring, connection); sqliteparameter myparameter = new SQ Liteparameter ("@ content", dbtype. string); myparameter. value = content; cmd. parameters. add (myparameter); try {connection. open (); int rows = cmd. executenonquery (); Return rows;} catch (system. data. SQLite. sqliteexception e) {Throw new exception (E. message);} finally {cmd. dispose (); connection. close () ;}}/// <summary> /// insert an image format field to the database (another example similar to the above) /// </Summary> /// <Param name = "strsql"> SQ L statement </param> /// <Param name = "FS"> image byte, when the database field type is image </param> // number of records affected by <returns> </returns> Public static int executesqlinsertimg (string strsql, byte [] FS) {using (sqliteconnection connection = new sqliteconnection (connectionstring) {sqlitecommand cmd = new sqlitecommand (strsql, connection); sqliteparameter myparameter = new sqliteparameter ("@ FS", dbtype. binary); myparameter. value = FS; cmd. P Arameters. add (myparameter); try {connection. open (); int rows = cmd. executenonquery (); Return rows;} catch (system. data. SQLite. sqliteexception e) {Throw new exception (E. message);} finally {cmd. dispose (); connection. close () ;}}/// <summary> // execute a query result statement and return the query result (object ). /// </Summary> /// <Param name = "sqlstring"> calculate the Query Result Statement </param> /// <returns> query result (object) </returns> Public static object getsingle (string sqlstring) {using (sqliteconnection connection = new sqliteconnection (connectionstring) {using (sqlitecommand cmd = new sqlitecommand (sqlstring, connection )) {try {connection. open (); object OBJ = cmd. executescalar (); If (object. equals (OBJ, null) | (object. EQ Uals (OBJ, system. dbnull. value) {return NULL;} else {return OBJ;} catch (system. data. SQLite. sqliteexception e) {connection. close (); throw new exception (E. message) ;}}}/// <summary> // execute the query statement, return sqlitedatareader /// </Summary> /// <Param name = "strsql"> query statement </param> /// <returns> sqlitedatareader </returns> Public static sqlitedatareader executereader (string strsql) {sqliteconnection Conn Ection = new sqliteconnection (connectionstring); sqlitecommand cmd = new sqlitecommand (strsql, connection); try {connection. open (); sqlitedatareader myreader = cmd. executereader (); Return myreader;} catch (system. data. SQLite. sqliteexception e) {Throw new exception (E. message) ;}//< summary> /// execute the query statement, return dataset /// </Summary> /// <Param name = "sqlstring"> query statement </param> /// <returns> dataset </RET Urns> Public static dataset query (string sqlstring) {using (sqliteconnection connection = new sqliteconnection (connectionstring) {dataset DS = new dataset (); try {connection. open (); sqlitedataadapter command = new sqlitedataadapter (sqlstring, connection); command. fill (DS, "ds");} catch (system. data. SQLite. sqliteexception ex) {Throw new exception (ex. message) ;}return DS ;}# endregion # Regi On, execute the SQL statement with parameters /// <summary> /// execute the SQL statement, returned Number of affected records /// </Summary> /// <Param name = "sqlstring"> Number of affected records </param> /// <returns> /returns> Public static int executesql (string sqlstring, params sqliteparameter [] extends parms) {using (sqliteconnection connection = new sqliteconnection (connectionstring) {using (sqlitecommand cmd = new sqlitecommand () {try {preparecommand (CMD, connection, null, sqlstr Ing, writable parms); int rows = cmd. executenonquery (); cmd. parameters. clear (); Return rows;} catch (system. data. SQLite. sqliteexception e) {Throw new exception (E. message) ;}}}/// <summary> // execute multiple SQL statements to implement database transactions. /// </Summary> /// <Param name = "sqlstringlist"> hash table of the SQL statement (the key is an SQL statement, and the value is the sqliteparameter [] of the statement) </param> Public static void executesqltran (hashtable sqlstringlist) {using (sqliteconnection conn = new sqliteconnection (connectionstring) {Conn. open (); Using (sqlitetransaction trans = Conn. begintransaction () {sqlitecommand cmd = new sqlitecommand (); try {// loop foreach (dictionaryentry myde in sqls Tringlist) {string plain text = myde. key. tostring (); sqliteparameter [] partition parms = (sqliteparameter []) myde. value; preparecommand (CMD, Conn, trans, plain text, plain parms); int val = cmd. executenonquery (); cmd. parameters. clear (); Trans. commit () ;}} catch {TRANS. rollback (); throw ;}}/// <summary> /// execute a statement to calculate the query result and return the query result (object ). /// </Summary> /// <Param name = "sqlstring"> calculate the Query Result Statement </param> /// <returns> query result (object) </returns> Public static object getsingle (string sqlstring, Params sqliteparameter [] partition parms) {using (sqliteconnection connection = new sqliteconnection (connectionstring )) {using (sqlitecommand cmd = new sqlitecommand () {try {preparecommand (CMD, connection, null, sqlstring, callback parms); object OBJ = cmd. execut Escalar (); cmd. parameters. clear (); If (object. equals (OBJ, null) | (object. equals (OBJ, system. dbnull. value) {return NULL;} else {return OBJ;} catch (system. data. SQLite. sqliteexception e) {Throw new exception (E. message) ;}}}/// <summary> // execute the query statement, return sqlitedatareader /// </Summary> /// <Param name = "strsql"> query statement </param> /// <returns> sqlitedatareader </returns> Public static sqlitedata Reader executereader (string sqlstring, Params sqliteparameter [] extends parms) {sqliteconnection connection = new sqliteconnection (connectionstring); sqlitecommand cmd = new sqlitecommand (); try {preparecommand (CMD, connection, null, sqlstring, cmdparms); sqlitedatareader myreader = cmd. executereader (); cmd. parameters. clear (); Return myreader;} catch (system. data. SQLite. sqliteexception e) {Throw ne W exception (E. message) ;}//< summary> /// execute the query statement, return dataset /// </Summary> /// <Param name = "sqlstring"> query statement </param> /// <returns> dataset </returns> Public static Dataset query (string sqlstring, params sqliteparameter [] extends parms) {using (sqliteconnection connection = new sqliteconnection (connectionstring) {sqlitecommand cmd = new sqlitecommand (); preparecommand (CMD, connection, null, sqlstring, Writable parms); Using (sqlitedataadapter da = new sqlitedataadapter (CMD) {dataset DS = new dataset (); try {da. fill (DS, "ds"); cmd. parameters. clear ();} catch (system. data. SQLite. sqliteexception ex) {Throw new exception (ex. message);} return DS ;}}} Private Static void preparecommand (sqlitecommand cmd, sqliteconnection Conn, sqlitetransaction trans, string plain text, sqliteparameter [] plain parms ){ If (conn. State! = Connectionstate. Open) Conn. open (); cmd. Connection = conn; cmd. commandtext = plain text; If (trans! = NULL) cmd. Transaction = trans; cmd. commandtype = commandtype. Text; // specify type; If (partition parms! = NULL) {foreach (sqliteparameter parm in parallel parms) cmd. Parameters. Add (parm) ;}# endregion }}