C # SQLite database access Encapsulation

C # SQLite database access Encapsulation

In the client configuration file Under the node, add:

 

[Localdb] is the name of the local SQLite database, and [config/local. db] is the location of the SQLite database under the directory where the current program is running.

 

C # SQLite database access encapsulation code:

 

////// This class is a static class used by the SQLite database. You only need to call this class directly without instantiating it ///Public static class SQLiteHelper {// Application. StartupPath public static string LocalDbConnectionString = ConfigurationManager. ConnectionStrings [localdb]. ConnectionString; # region ExecuteNonQuery ////// Perform database operations (ADD, update, or delete )//////Connection string ///SqlCommand object ///
 
  
Number of affected rows
 Public static int ExecuteNonQuery (string connectionString, SQLiteCommand cmd) {int result = 0; if (connectionString = null | connectionString. length = 0) throw new ArgumentNullException (connectionString); using (SQLiteConnection con = new SQLiteConnection (connectionString) {SQLiteTransaction trans = null; PrepareCommand (cmd, con, ref trans, true, cmd. commandType, cmd. commandText); try {result = cmd. executeNonQuery (); trans. commit ();} catch (Exception ex) {trans. rollback (); throw ex;} return result ;}////// Perform database operations (ADD, update, or delete )//////Connection string ///Execution statement or stored procedure name ///Execution type ///
 
  
Number of affected rows
 Public static int ExecuteNonQuery (string connectionString, string commandText, CommandType commandType) {int result = 0; if (connectionString = null | connectionString. length = 0) throw new ArgumentNullException (connectionString); if (commandText = null | commandText. length = 0) throw new ArgumentNullException (commandText); SQLiteCommand cmd = new SQLiteCommand (); using (SQLiteConnection con = new SQLiteConnection (connectionString) {SQLiteTransaction trans = null; prepareCommand (cmd, con, ref trans, true, commandType, commandText); try {result = cmd. executeNonQuery (); trans. commit ();} catch (Exception ex) {trans. rollback (); throw ex;} return result ;}////// Perform database operations (ADD, update, or delete )//////Connection string ///Execution statement or stored procedure name ///Execution type ///SQL parameter object ///
 
  
Number of affected rows
 Public static int ExecuteNonQuery (string connectionString, string commandText, CommandType commandType, params SQLiteParameter [] cmdParms) {int result = 0; if (connectionString = null | connectionString. length = 0) throw new ArgumentNullException (connectionString); if (commandText = null | commandText. length = 0) throw new ArgumentNullException (commandText); SQLiteCommand cmd = new SQLiteCommand (); using (SQLiteConnection con = new SQLiteConnection (connectionString) {SQLiteTransaction trans = null; prepareCommand (cmd, con, ref trans, true, commandType, commandText); try {result = cmd. executeNonQuery (); trans. commit ();} catch (Exception ex) {trans. rollback (); throw ex ;}} return result ;}# endregion # region ExecuteScalar ////// Perform database operations (ADD, update, or delete) and return the 1st rows and 1st columns of data obtained after execution //////Connection string ///SqlCommand object ///
 
  
Query the 1st rows and 1st columns of data
 Public static object ExecuteScalar (string connectionString, SQLiteCommand cmd) {object result = 0; if (connectionString = null | connectionString. length = 0) throw new ArgumentNullException (connectionString); using (SQLiteConnection con = new SQLiteConnection (connectionString) {SQLiteTransaction trans = null; PrepareCommand (cmd, con, ref trans, true, cmd. commandType, cmd. commandText); try {result = cmd. executeScalar (); trans. commit ();} catch (Exception ex) {trans. rollback (); throw ex;} return result ;}////// Perform database operations (ADD, update, or delete) and return the 1st rows and 1st columns of data obtained after execution //////Connection string ///Execution statement or stored procedure name ///Execution type ///
 
  
Query the 1st rows and 1st columns of data
 Public static object ExecuteScalar (string connectionString, string commandText, CommandType commandType) {object result = 0; if (connectionString = null | connectionString. length = 0) throw new ArgumentNullException (connectionString); if (commandText = null | commandText. length = 0) throw new ArgumentNullException (commandText); SQLiteCommand cmd = new SQLiteCommand (); using (SQLiteConnection con = new SQLiteConnection (connectionString) {SQLiteTransaction trans = null; prepareCommand (cmd, con, ref trans, true, commandType, commandText); try {result = cmd. executeScalar (); trans. commit ();} catch (Exception ex) {trans. rollback (); throw ex;} return result ;}////// Perform database operations (ADD, update, or delete) and return the 1st rows and 1st columns of data obtained after execution //////Connection string ///Execution statement or stored procedure name ///Execution type ///SQL parameter object ///
 
  
Query the 1st rows and 1st columns of data
 Public static object ExecuteScalar (string connectionString, string commandText, CommandType commandType, params SQLiteParameter [] cmdParms) {object result = 0; if (connectionString = null | connectionString. length = 0) throw new ArgumentNullException (connectionString); if (commandText = null | commandText. length = 0) throw new ArgumentNullException (commandText); SQLiteCommand cmd = new SQLiteCommand (); using (SQLiteConnection con = new SQLiteConnection (connectionString) {SQLiteTransaction trans = null; prepareCommand (cmd, con, ref trans, true, commandType, commandText); try {result = cmd. executeScalar (); trans. commit ();} catch (Exception ex) {trans. rollback (); throw ex;} return result;} # endregion # region ExecuteReader ////// Execute the database query and return the SqlDataReader object //////Connection string ///SqlCommand object ///
 
  
SqlDataReader object
 Public static DbDataReader ExecuteReader (string connectionString, SQLiteCommand cmd) {DbDataReader reader = null; if (connectionString = null | connectionString. length = 0) throw new ArgumentNullException (connectionString); SQLiteConnection con = new SQLiteConnection (connectionString); SQLiteTransaction trans = null; PrepareCommand (cmd, con, ref trans, false, cmd. commandType, cmd. commandText); try {reader = cmd. executeReader (CommandBehavior. closeConnection);} catch (Exception ex) {throw ex;} return reader ;}////// Execute the database query and return the SqlDataReader object //////Connection string ///Execution statement or stored procedure name ///Execution type ///
 
  
SqlDataReader object
 Public static DbDataReader ExecuteReader (string connectionString, string commandText, CommandType commandType) {DbDataReader reader = null; if (connectionString = null | connectionString. length = 0) throw new ArgumentNullException (connectionString); if (commandText = null | commandText. length = 0) throw new partition (commandText); SQLiteConnection con = new SQLiteConnection (connectionString); SQLiteCommand cmd = new SQLiteCommand (); SQLiteTransaction trans = null; PrepareCommand (cmd, con, ref trans, false, commandType, commandText); try {reader = cmd. executeReader (CommandBehavior. closeConnection);} catch (Exception ex) {throw ex;} return reader ;}////// Execute the database query and return the SqlDataReader object //////Connection string ///Execution statement or stored procedure name ///Execution type ///SQL parameter object ///
 
  
SqlDataReader object
 Public static DbDataReader ExecuteReader (string connectionString, string commandText, CommandType commandType, params SQLiteParameter [] partition parms) {DbDataReader reader = null; if (connectionString = null | connectionString. length = 0) throw new ArgumentNullException (connectionString); if (commandText = null | commandText. length = 0) throw new partition (commandText); SQLiteConnection con = new SQLiteConnection (connectionString); SQLiteCommand cmd = new SQLiteCommand (); SQLiteTransaction trans = null; PrepareCommand (cmd, con, ref trans, false, commandType, commandText, partition parms); try {reader = cmd. executeReader (CommandBehavior. closeConnection);} catch (Exception ex) {throw ex;} return reader;} # endregion # region ExecuteDataSet ////// Execute the database query and return the DataSet object //////Connection string ///SqlCommand object ///
 
  
DataSet object
 Public static DataSet ExecuteDataSet (string connectionString, SQLiteCommand cmd) {DataSet ds = new DataSet (); SQLiteConnection con = new SQLiteConnection (connectionString); SQLiteTransaction trans = null; PrepareCommand (cmd, con, ref trans, false, cmd. commandType, cmd. commandText); try {SQLiteDataAdapter sda = new SQLiteDataAdapter (cmd); sda. fill (ds);} catch (Exception ex) {throw ex;} finally {If (cmd. Connection! = Null) {if (cmd. Connection. State = ConnectionState. Open) {cmd. Connection. Close () ;}} return ds ;}////// Execute the database query and return the DataSet object //////Connection string ///Execution statement or stored procedure name ///Execution type ///
 
  
DataSet object
 Public static DataSet ExecuteDataSet (string connectionString, string commandText, CommandType commandType) {if (connectionString = null | connectionString. length = 0) throw new ArgumentNullException (connectionString); if (commandText = null | commandText. length = 0) throw new ArgumentNullException (commandText); DataSet ds = new DataSet (); SQLiteConnection con = new SQLiteConnection (connectio NString); SQLiteCommand cmd = new SQLiteCommand (); SQLiteTransaction trans = null; PrepareCommand (cmd, con, ref trans, false, commandType, commandText ); try {SQLiteDataAdapter sda = new SQLiteDataAdapter (cmd); sda. fill (ds);} catch (Exception ex) {throw ex;} finally {if (con! = Null) {if (con. State = ConnectionState. Open) {con. Close () ;}} return ds ;}////// Execute the database query and return the DataSet object //////Connection string ///Execution statement or stored procedure name ///Execution type ///SQL parameter object ///
 
  
DataSet object
 Public static DataSet ExecuteDataSet (string connectionString, string commandText, CommandType commandType, params SQLiteParameter [] cmdParms) {if (connectionString = null | connectionString. length = 0) throw new ArgumentNullException (connectionString); if (commandText = null | commandText. length = 0) throw new ArgumentNullException (commandText); DataSet ds = new DataSet (); SQLiteConnection c On = new SQLiteConnection (connectionString); SQLiteCommand cmd = new SQLiteCommand (); SQLiteTransaction trans = null; PrepareCommand (cmd, con, ref trans, false, commandType, commandText, cmdParms ); try {SQLiteDataAdapter sda = new SQLiteDataAdapter (cmd); sda. fill (ds);} catch (Exception ex) {throw ex;} finally {if (con! = Null) {if (con. State = ConnectionState. Open) {con. Close () ;}} return ds ;}# endregion # region common paging query method ////// General paging query method //////Connection string ///Table Name ///Query field name ///Where condition ///Sorting condition ///Data volume per page ///Current page number ///Total data volume ///
 
  
DataTable data table
 Public static DataTable SelectPaging (string connString, string tableName, string strColumns, string strWhere, string strOrder, int pageSize, int currentIndex, out int recordOut) {DataTable dt = new DataTable (); recordOut = Convert. toInt32 (ExecuteScalar (connString, select count (*) from + tableName, CommandType. text); string pagingTemplate = select {0} from {1} where {2} order by {3} limit {4} offs Et {5}; int offsetCount = (currentIndex-1) * pageSize; string commandText = String. format (pagingTemplate, strColumns, tableName, strWhere, strOrder, pageSize. toString (), offsetCount. toString (); using (DbDataReader reader = ExecuteReader (connString, commandText, CommandType. text) {if (reader! = Null) {dt. load (reader) ;}} return dt ;}# endregion # region preprocessing Command object, database link, transaction, object to be executed, parameter initialization, etc ////// Pre-process the initialization of Command objects, database links, transactions, objects to be executed, parameters, etc //////Command object ///Connection object ///Transcation object ///Whether to use transactions ///SQL string execution type ///SQL Text ///SQLiteParameters to use in the command private static void PrepareCommand (SQLiteCommand cmd, SQLiteConnection conn, ref SQLiteTransaction trans, bool useTrans, CommandType parameter type, string plain text, params parameters [] partition parms) {if (conn. state! = ConnectionState. open) conn. open (); cmd. connection = conn; cmd. commandText = plain text; if (useTrans) {trans = conn. beginTransaction (IsolationLevel. readCommitted); cmd. transaction = trans;} cmd. commandType = partition type; if (partition parms! = Null) {foreach (SQLiteParameter parm in parallel parms) cmd. Parameters. Add (parm) ;}# endregion}

Use demo:

 

////// Obtain the database keyword information //////CATEGORY ///Version ///
 Private DataSet GetSystemDataBaseKeyWords (string category, string versions) {StringBuilder SQL = new StringBuilder (); SQL. append (SELECT Keywords, Versions, Type, Description, Category, Id, Extends); SQL. append (FROM A_DataBaseKeyWords); SQL. appendFormat (WHERE 1 = {0}, 1); if (! String. IsNullOrEmpty (category) {SQL. AppendFormat (AND Category = '{0}', category);} if (! String. isNullOrEmpty (versions) {SQL. appendFormat (AND Versions = '{0}', versions);} return SQLiteHelper. executeDataSet (SQLiteHelper. localDbConnectionString, SQL. toString (), CommandType. text );}