C # Sqlite help class

C # The Sqlite help class has recently used WPF as a client and requires offline operations to store data. In the project, considering using Sqlite embedded databases, I found a lot of information on the Internet, finally, a public help class is compiled. Sqlite is a very small database, basically having most of the functions of relational database operations, and the SQL syntax is similar. The following is my help code: 1. Obtain the SQLiteConnection object and input the database with an address. 1 // <summary> <br> // obtain the connection object <br> /// </summary> <br> /// <returns> SQLiteConnection </returns> <br> public static SQLiteConnection GetSQLiteConnection () <br >{< br> // Sqlite database address <br> string str = AppDomain. currentDomain. baseDirectory; <br> var con = new SQLiteConnection ("Data Source =" + str + "DataBass \ InfoServiceDbB. db "); <br> return con; <br>} 2. prepare the Operation Command Parameters and construct the SQLiteCommand object: copy the code /// <summary> /// prepare the operation Command Parameter /// </summary> /// <param name = "cmd"> SQLiteCommand </param> /// <param name = "conn"> SQLiteConnection </param> /// <param name = "plain text"> SQL command text </param> /// <param name = "data"> parameter array </param> private static void PrepareCommand (SQLiteCommand cmd, SQLiteConnection conn, string literal text, Dictionary <String, String> data) {if (conn. state! = ConnectionState. open) conn. open (); cmd. parameters. clear (); cmd. connection = conn; cmd. commandText = plain text; cmd. commandType = CommandType. text; cmd. commandTimeout = 30; if (data! = Null & data. count> = 1) {foreach (KeyValuePair <String, String> val in data) {cmd. parameters. addWithValue (val. key, val. value) ;}} copy code 3. query, return the DataSet copy code /// <summary> /// query, return DataSet /// </summary> /// <param name = "plain text"> SQL command text </param> /// <param name = "data"> parameter Array </param> /// <returns> DataSet </returns> public static DataSet ExecuteDataset (string plain text, dictionary <string, string> data) {va R ds = new DataSet (); using (SQLiteConnection connection = GetSQLiteConnection () {var command = new SQLiteCommand (); PrepareCommand (command, connection, plain text, data ); var da = new SQLiteDataAdapter (command); da. fill (ds);} return ds;} copy code 4. query, return DataTable copy code /// <summary> /// query, return the DataTable /// </summary> /// <param name = "plain text"> SQL command text </param> /// <param name = "data"> parameter Array </param> /// <retur Ns> DataTable </returns> public static DataTable ExecuteDataTable (string plain text, Dictionary <string, string> data) {var dt = new DataTable (); using (SQLiteConnection connection = GetSQLiteConnection () {var command = new SQLiteCommand (); PrepareCommand (command, connection, plain text, data); SQLiteDataReader reader = command. executeReader (); dt. load (reader) ;}return dt ;}copy Code 5. returns the data copy code of a row // /<Summary> /// return a row of Data /// </summary> /// <param name = "plain text"> SQL command text </param> /// <param name = "data"> parameter array </param> // <returns> DataRow </returns> public static DataRow ExecuteDataRow (string plain text, dictionary <string, string> data) {DataSet ds = ExecuteDataset (plain text, data); if (ds! = Null & ds. tables. count> 0 & ds. tables [0]. rows. count> 0) return ds. tables [0]. rows [0]; return null;} copy Code 6. execute database operations and copy the code /// <summary> /// execute Database Operations /// </summary> /// <param name = "plain text"> SQL command text </ param> /// <param name = "data"> input parameter </param> /// <returns> returns the number of affected rows </returns> public static int ExecuteNonQuery (string plain text, dictionary <string, string> data) {using (SQLiteConnection connection = GetS QLiteConnection () {var command = new SQLiteCommand (); PrepareCommand (command, connection, plain text, data); return command. executeNonQuery () ;}} copy code 7. returns the SqlDataReader object copy code /// <summary> /// returns the SqlDataReader object /// </summary> /// <param name = "plain text"> SQL command text </ param> /// <param name = "data"> input parameter </param> /// <returns> SQLiteDataReader </returns> public static SQLiteDataReader ExecuteReader (string cmdT Ext, Dictionary <string, string> data) {var command = new SQLiteCommand (); SQLiteConnection connection = GetSQLiteConnection (); try {PrepareCommand (command, connection, plain text, data ); SQLiteDataReader reader = command. executeReader (CommandBehavior. closeConnection); return reader;} catch {connection. close (); command. dispose (); throw ;}} copy code 8. the first column in the first row of the returned result set. ignore other rows or columns and copy the code. /// <summary> /// First column of the first row, ignore other rows or columns /// </summary> /// <param name = "plain text"> SQL command text </param> /// <param name = "data"> input parameters </param> /// <returns> object </returns> public static object ExecuteScalar (string plain text, dictionary <string, string> data) {using (SQLiteConnection connection = GetSQLiteConnection () {var cmd = new SQLiteCommand (); PrepareCommand (cmd, connection, plain text, data ); return cmd. executeScalar () ;}} Code 9. copy code by PAGE /// <summary> /// query by PAGE /// </summary> /// <param name = "recordCount"> total number of records </param> /// <param name = "pageIndex"> page channeling </param> /// <param name = "pageSize"> page size </param> /// <param name = "plain text"> SQL command text </param> // <param name = "countText"> query the SQL Text of the total number of records </param> // <param name = "data"> command parameters </param> // <returns> DataSet </returns> public static DataSet ExecutePager (ref int recordCount, int pageIndex, Int pageSize, string comment text, string countText, Dictionary <string, string> data) {if (recordCount <0) recordCount = int. parse (ExecuteScalar (countText, data ). toString (); var ds = new DataSet (); using (SQLiteConnection connection = GetSQLiteConnection () {var command = new SQLiteCommand (); PrepareCommand (command, connection, plain text, data); var da = new SQLiteDataAdapter (command); da. fill (ds ,( PageIndex-1) * pageSize, pageSize, "result");} return ds;} copy code 10. reorganizing the database when you delete data from the SQLite database, unused disk space will be added to an internal "Free List. This part of space can be reused when you insert data next time. The disk space will not be lost, but will not be returned to the operating system. If you delete a large amount of data and want to reduce the space occupied by database files, run the VACUUM command. VACUUM will re-organize the database from the ground up. You can re-organize the database at an interval in your program to save the space to copy the public void ResetDataBass () code () {using (SQLiteConnection conn = GetSQLiteConnection () {var cmd = new SQLiteCommand (); if (conn. state! = ConnectionState. open) conn. open (); cmd. parameters. clear (); cmd. connection = conn; cmd. commandText = "vacuum"; cmd. commandType = CommandType. text; cmd. commandTimeout = 30; cmd. executeNonQuery ();}}