C # Operations SQLite database Help class--sqlitehelper

Using system;using system.collections.generic;using system.data.sqlite;using system.data;namespace Com.zcwl.rock.helper{public class Sqlitehelper{private static string connectionString = String. empty;///<summary>///Sets the connection string based on the data source, password, and version number. </summary>///<param name= "datasource" > Data source. </param>///<param name= "password" > password. </param>///<param name= "version" > Release number (default is 3). </param>public static void Setconnectionstring (String datasource, string password, int version = 3) { connectionString = string. Format ("Data source={0}; Version={1};p assword={2} ", DataSource, version, password);} <summary>///Create a database file. If a database file with the same name exists, it is overwritten. </summary>///<param name= "dbName" > Database file name. is not created when null or an empty string. </param>///<param name= "password" > (optional) database password, default is empty. </param>///<exception cref= "exception" ></exception>public static void Createdb (String dbName) {if ( !string. IsNullOrEmpty (DbName)) {try {sqliteconnection.createfile (dbName);} CaTCH (Exception) {throw;}}} <summary>////to the SQLite database to perform additions and deletions, returns the number of rows affected. </summary>//<param name= "SQL" > SQL statements to be executed and deleted. </param>//<param name= "Parameters" > perform the required parameters for adding or deleting statements, and the parameters must be in the order in which they are in the SQL statement. </param>//<returns></returns>/<exception cref= "Exception" ></exception>public int ExecuteNonQuery (String sql, params sqliteparameter[] parameters) {int affectedrows = 0;using (sqliteconnection Connection = new Sqliteconnection (connectionString)) {using (Sqlitecommand command = new Sqlitecommand (connection)) {try {connection. Open (); command.commandtext = sql;if (parameters. Length! = 0) {command. Parameters.addrange (Parameters);} affectedrows = command. ExecuteNonQuery ();} catch (Exception) {throw;}}} return affectedrows;} <summary>///bulk processing of data manipulation statements. </summary>///<param name= "list" >sql statement collection. </param>///<exception cref= "Exception" ></exception>public void Executenonquerybatch (List< Keyvaluepair<stRing, sqliteparameter[]>> list) {using (sqliteconnection conn = new Sqliteconnection (connectionString)) {try {conn . Open (); }catch {throw;} using (sqlitetransaction TRAN = conn. BeginTransaction ()) {using (Sqlitecommand cmd = new Sqlitecommand (conn)) {Try{foreach (var item in list) {cmd. CommandText = Item. Key;if (item. Value = null) {cmd. Parameters.addrange (item. Value);} Cmd. ExecuteNonQuery ();} Tran. Commit ();} catch (Exception) {tran. Rollback (); Throw }}}}}///<summary>///executes a query statement and returns the first result. </summary>///<param name= "SQL" > Query statements. </param>///<returns> Query results. </returns>///<exception cref= "Exception" ></exception>public object ExecuteScalar (String sql, params sqliteparameter[] parameters) {using (sqliteconnection conn = new Sqliteconnection (connectionString)) {using ( Sqlitecommand cmd = new Sqlitecommand (conn)) {Try{conn. Open (); cmd. CommandText = sql;if (parameters. Length! = 0) {cmd. Parameters.addrange (Parameters);} return CMD. ExecuteScalar ();} catch (Exception) {throw;}}} <summary>////Executes a query statement that returns a DataTable that contains the results of the query. </summary>//<param name= "SQL" > the query statement to execute. </param>//<param name= "Parameters" > The parameters required to execute SQL query statements must be in the order in which they are in the SQL statement. </param>//<returns></returns>/<exception cref= "Exception" ></exception>public DataTable ExecuteQuery (String sql, params sqliteparameter[] parameters) {using (sqliteconnection connection = new Sqliteconnection (connectionString)) {using (Sqlitecommand command = new Sqlitecommand (SQL, connection)) {if (parameters . Length! = 0) {command. Parameters.addrange (Parameters);} Sqlitedataadapter adapter = new Sqlitedataadapter (command);D atatable data = new DataTable (), try {adapter. Fill (data); }catch (Exception) {throw;} return data;}}} <summary>/////Executes a query statement that returns an associated Sqlitedatareader instance. </summary>//<param name= "SQL" > the query statement to execute. </param>//<param name= "Parameters" > The parameters required to execute SQL query statements must be in the order in which they are in the SQL statement. </param&gT <returns></returns>//<exception cref= "Exception" ></exception>public Sqlitedatareader ExecuteReader (String sql, params sqliteparameter[] parameters) {sqliteconnection connection = new Sqliteconnection ( connectionString); Sqlitecommand command = new Sqlitecommand (sql, connection); try{if (parameters. Length! = 0) {command. Parameters.addrange (Parameters);} Connection. Open (); return command. ExecuteReader (commandbehavior.closeconnection);} catch (Exception) {throw;}} <summary>///query all data type information in the database. </summary>//<returns></returns>/<exception cref= "Exception" ></exception> Public DataTable GetSchema () {using (sqliteconnection connection = new Sqliteconnection (connectionString)) {try{ Connection. Open (); return connection. GetSchema ("TABLES");} catch (Exception) {throw;}}} }}

Note: When you use this class, you need to reference the System.Data.SQLite assembly .

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

C # Operations SQLite database Help class--sqlitehelper