. Net calls sqlite Database

. Net calls sqlite Database

I recently completed a project called Address Book software. This is a very simple system, so we won't talk about it in terms of business. I would like to share with you why the sqlite database is used.

When developing the address book, we hope that the address book can query the address book information when it is online or disconnected. You need to synchronize the contents of the address book to the local device. sqlite is a lightweight database, which is very helpful for local storage. The solution is simple. When I connect to the Internet, I directly access the SQL server database server. However, when the network is disconnected, we only need to read sqlite data.

(1) Create an sqlite table

I downloaded an SQLite Expert Professional 3 tool to directly create a table that stores Address Book information. SQLite Expert Professional 3 is quite useful, but the official version requires money. When creating a data table, we should note how to save the data of the DataTime type. In the sqlite database, I think the best way is to save the date as a string. If necessary, extract it and process it.

(2) Introduce dll

When using the sqlite database, we introduce System. Data. SQLite. dll in the project.

(3) C # Use the sqlite Database Class

This is the most important part of this article. It is to share the basic classes of sqlite operations. The Code is as follows:

 

Using System; using System. collections. generic; using System. text; using System. data. SQLite; using System. data; namespace AddressBook {class SQLiteHelper {private SQLiteConnection conn = null; private string connString = string. empty; public string ConnString {get {return connString;} set {connString = value;} private string err = string. empty; public string Err {get {return err;} set {err = value ;}} public SQLiteHelper () {string connString = Data Source = + Environment. currentDirectory +/AddressBook. db; conn = new SQLiteConnection (connString); // create a database instance and specify the file location // conn. open (); // Open the database. The database will be created automatically if the file does not exist }////// Operate the database /////////
 Public int ExecNoQuery (string SQL) {int retValue =-1; try {conn. open (); SQLiteCommand cmd = new SQLiteCommand (SQL, conn); retValue = cmd. executeNonQuery (); conn. close ();} catch (Exception ex) {err = ex. message; return-1;} return retValue ;}////// Obtain Department data //////
 Public DataSet GetDataSet (string SQL) {try {DataSet dataset = new DataSet (); SQLiteDataAdapter adapter = new SQLiteDataAdapter (); adapter. selectCommand = new SQLiteCommand (SQL, conn); adapter. fill (dataset); return dataset;} catch (Exception ex) {err = ex. message; return null ;}}////// Returns 1 string /////////
 Public string ExecReturenOne (string SQL) {string result = string. empty; try {conn. open (); SQLiteCommand cmd = new SQLiteCommand (SQL, conn); object obj = cmd. executeScalar (); if (obj! = Null) {result = obj. toString ();} conn. close ();} catch (Exception ex) {if (conn. state = System. data. connectionState. open) {conn. close ();} err = ex. message; return ;}return result ;}}}