Introduction to SQLite and the application of SQLite in. net

After publishing WCF on IIS, I have been trying to find the simplest database except XML. Although I have heard of SQLite, I heard that SQLite is used in both Android and iOS today.

There are various platform versions on the official website to find the platform you need. As follows:

Select one of the various versions on the platform, and select 32 characters. the bundle version of net4.0: here we can see that one is bundle, the other is not bundle; the bundle represents system. data. SQLite. the DLL is mixed with SQLite. interOP. DLL.

Here we download the bundle mixed version (if there is another version, an error occurs when SQLite. InterOP. dll is added to the project. If SQLite. InterOP. dll is not added, it will be saved ).

 

Another preparation is to download a tool: sqlitespy, which is used to operate the SQLite database.

 

All the tools are ready and you can start.

1. Use spy to create a database

 
Drop table [Book]; Create Table [Book] ([ID] integer not null primary key autoincrement, [bookname] varchar (50) not null, [price] Real not null );
 

 

2. Write the database operation class in vs (the writing is simple, and you can complete it yourself)

Using system; using system. collections. generic; using system. LINQ; using system. text; using system. data; using system. data. SQLite; namespace sqlitedemo {public class sqlitedatabase {string dbconnection; sqliteconnection CNN; # region ctor // <summary> // default constructor for sqlitedatabase class. /// </Summary> Public sqlitedatabase () {dbconnection = "Data Source = recipes. s3db "; CNN = new sqliteconn Ection (dbconnection);} // <summary> // single Param constructor for specifying the DB file. /// </Summary> /// <Param name = "inputfile"> the file containing the DB </param> Public sqlitedatabase (string inputfile) {dbconnection = string. format ("Data Source = {0}", inputfile); CNN = new sqliteconnection (dbconnection);} // <summary> // single Param constructor for specifying advanced connection OPT Ions. /// </Summary> /// <Param name = "connectionopts"> A dictionary containing all desired options and their values </param> Public sqlitedatabase (Dictionary <string, string> connectionopts) {string STR = ""; foreach (keyvaluepair <string, string> row in connectionopts) {STR ++ = string. format ("{0 }={ 1};", row. key, row. value);} STR = Str. trim (). substring (0, str. length-1); dbconnection = STR; CNN = New sqliteconnection (dbconnection);} # endregion // <summary> // allows the programmer to run a query against the database. /// </Summary> /// <Param name = "SQL"> the SQL to run </param> /// <returns> a datatable containing the result set. </returns> Public datatable getdatatable (string SQL) {datatable dt = new datatable (); try {sqliteconnection CNN = new sqliteconnection (dbconnection); CNN. open (); Sqlitecommand mycommand = new sqlitecommand (CNN); mycommand. commandtext = SQL; sqlitedatareader reader = mycommand. executereader (); DT. load (Reader); reader. close (); CNN. close ();} catch (exception e) {Throw new exception (E. message);} return DT;} public datatable getdatatable (string SQL, ilist <sqliteparameter> extends Params) {datatable dt = new datatable (); try {sqliteconnection CNN = new sqlitec Onnection (dbconnection); CNN. open (); sqlitecommand mycommand = new sqlitecommand (CNN); mycommand. commandtext = SQL; mycommand. parameters. addrange (partition Params. toarray (); mycommand. commandtimeout = 180; sqlitedatareader reader = mycommand. executereader (); DT. load (Reader); reader. close (); CNN. close ();} catch (exception e) {Throw new exception (E. message);} return DT;} // <summary> // allows the P Rogrammer to interact with the database for purposes other than a query. /// </Summary> /// <Param name = "SQL"> the SQL to be run. </param> // <returns> an integer containing the number of rows updated. </returns> Public bool executenonquery (string SQL) {bool successstate = false; CNN. open (); Using (sqlitetransaction mytrans = CNN. begintransaction () {sqlitecommand mycommand = new sqlitecommand (SQL, CNN); try {mycommand. commandtimeout = 180; mycommand. executenonquery (); mytrans. commit (); successstate = true; CNN. close ();} catch (exception e) {mytrans. rollback ();} finally {mycommand. dispose (); CNN. close () ;}return successstate;} public bool executenonquery (string SQL, ilist <sqliteparameter> extends Params) {bool successstate = false; CNN. open (); Using (sqlitetransaction mytrans = CNN. beg Intransaction () {sqlitecommand mycommand = new sqlitecommand (SQL, CNN, mytrans); try {mycommand. parameters. addrange (partition Params. toarray (); mycommand. commandtimeout = 180; mycommand. executenonquery (); mytrans. commit (); successstate = true; CNN. close ();} catch (exception e) {mytrans. rollback (); throw E;} finally {mycommand. dispose (); CNN. close () ;}return successstate;} // <summary> // temporarily No // allows the programmer to retrieve single items from the DB. /// </Summary> /// <Param name = "SQL"> the query to run. </param> // <returns> a string. </returns> Public String executescalar (string SQL) {CNN. open (); sqlitecommand mycommand = new sqlitecommand (CNN); mycommand. commandtext = SQL; object value = mycommand. executescalar (); CNN. close (); If (value! = NULL) {return value. tostring ();} return "" ;}/// <summary> // allows the programmer to easily update rows in the DB. /// </Summary> /// <Param name = "tablename"> the table to update. </param> // <Param name = "data"> A dictionary containing column names and their new values. </param> // <Param name = "where"> the WHERE clause for the update statement. </param> // <returns> A boolean true or false to signify success or failure. </returns> Public bool Update (string tablename, Dictionary <string, string> data, string where) {string Vals = ""; Boolean returncode = true; If (data. count> = 1) {foreach (keyvaluepair <string, string> Val in data) {Vals + = string. format ("{0} = '{1}',", Val. key. tostring (), Val. value. tostring ();} Vals = Vals. substring (0, Vals. length-1);} Try {This. executenonquery (string. format ("Update {0} set {1} Where {2};", tablename, Vals, where) ;}catch {returncode = false ;}return returncode ;}}}

 

3. Write the Dal. Here is a prompt that the ID can be auto-incrementing, but it must be null.

Using system; using system. collections. generic; using system. LINQ; using system. text; using system. data. SQLite; namespace sqlitedemo {public class bookdal {sqlitedatabase sqlexcute = new sqlitedatabase (); Public bool create (Book) {try {var SQL = "insert into book values (@ ID, @ bookname, @ price); "; var partition Params = new list <sqliteparameter> () {New sqliteparameter (" ID ", null), new sqliteparameter (" bookname ", book. bookname), new sqliteparameter ("price", book. price)}; return sqlexcute. executenonquery (SQL, writable Params);} catch (exception e) {// do any logging operation here if necessary throw E; return false;} public bool Update (Book) {try {var SQL = "Update book set bookname = @ bookname, price = @ price where id = @ ID;"; VaR parameter Params = new list <sqliteparameter> () {New sqliteparameter ("ID", book. ID), new sqliteparameter ("bookname", book. bookname), new sqliteparameter ("price", book. price)}; return sqlexcute. executenonquery (SQL, writable Params);} catch (exception) {// do any logging operation here if necessary return false;} public bool Delete (INT ID) {try {using (sqliteconnection conn = new sqliteconnection ("Data Source = E: \ test. db3 ") {Conn. open (); sqlitecommand cmd = Conn. createcommand (); cmd. commandtext = "delete from book where id = @ ID;"; cmd. parameters. add (New sqliteparameter ("ID", ID); int I = cmd. executenonquery (); return I = 1 ;}} catch (exception) {// do any logging operation here if necessary return false ;}} public book getbyid (INT ID) {try {var SQL = "select * from book where id = @ ID;"; var partition Params = new list <sqliteparameter> () {New sqliteparameter ("ID ", ID)}; var dt = sqlexcute. getdatatable (SQL, callback Params); If (DT. rows. count> 0) {book = New Book (); book. id = int. parse (DT. rows [0] ["ID"]. tostring (); book. bookname = DT. rows [0] ["bookname"]. tostring (); book. price = decimal. parse (DT. rows [0] ["price"]. tostring (); Return book;} else return NULL;} catch (exception) {// do any logging operation here if necessary return NULL ;}}}}

 

4. Write the call in the console

Using system; using system. collections. generic; using system. LINQ; using system. text; using system. data; using system. data. SQLite; namespace sqlitedemo {class program {static void main (string [] ARGs) {bookdal = new sqlitedemo. bookdal (); book = New Book (); book. bookname = "the first book"; book. price = 10.0 m; bookdal. create (book); book. bookname = "Second Book"; book. price = 13.0 m; bookdal. create (book); book = bookdal. getbyid (2); console. writeline (book. ID + "" + book. bookname + "" + book. price); book. price = 11.1 m; bookdal. update (book); book = bookdal. getbyid (2); console. writeline (book. ID + "" + book. bookname + "" + book. price); book = bookdal. getbyid (1); console. writeline (book. ID + "" + book. bookname + "" + book. price); console. read ();}}}

 

5. Instances

 

Http://system.data.sqlite.org/downloads/1.0.65.0/sqlite-netFx40-setup-x86-2010-1.0.65.0.exe

 

Note:

If a client call error occurs, the database location may be incorrect, because this is relative to the location in Bin/debug, it is best to place a fixed location.

 

Refer:

Http://stackoverflow.com/questions/2605490/system-data-sqlite-net-4