C # SQLite for local data storage

Even for network applications, local data storage needs to be considered in case of disconnection. Before SQLite appeared, when the data volume was large, we always used access. If the data volume was small, the file was stored. Access does not support the atomicity of transactions. In the case of power failure (which will always happen), data is hard to be restored.

I. Installation

SQLite is a lightweight database and an acid-compliant associated database management system. I am using http://sqlite.phxsoftware.com directly/(an Open Source ADO. net provider for the SQLite database engine ). The downloaded file is an EXE. The root directory after installation is as follows:

There is a test tool in BIN to view the performance indicators of running SQLite locally.

Ii. Create a database

After the installation is complete, open Visual Studio and create a data connection. You can see that the data source has an SQLite parameter.

Create a connection, as shown in. The SQLite database is saved as a file.

Iii. Database Maintenance

You can maintain SQLite data in Vs, for example:

You can use functions similar to SQL query analyzer in Vs, for example:

4. hybrid mode

After the installation is complete, you can directly reference the project set.

System. Data. SQLite

System. Data. SQLite. LINQ

TwoProgramBecause the assembly is loaded in runtime 4.0. Therefore, you must configure the following parameters in APP. config.

  
    XML version = "1.0" encoding = "UTF-8" ?>     Configuration  >    startup   uselegacyv2runtimeactivationpolicy  = " true " >   supportedruntime   version  = " v4.0 " />     startup  >   Configuration  > 

 

 

V. sqlitehelper

Finally, a self-written sqlitehelper is provided:

 

 Using System; Using System. Collections. Generic; Using System. LINQ; Using System. text; Using System. Data. SQLite; Using System. Data; Using System. Data. Common; Namespace Com. luminji. dataservice. sqlhelpers { Public  Class Sqlitehelper { /// <Summary>          /// Connectionstring example: datasource = test. db3; pooling = true; failifmissing = false          /// </Summary>          Public   Static   String Connectionstring { Get ; Set ;} Private   Static   Void Preparecommand (sqlitecommand cmd, sqliteconnection Conn,String Plain text, Params   Object [] P ){ 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 (P! = Null ){ Foreach ( Object Parm In P) cmd. Parameters. addwithvalue ( String . Empty, parm );}} Public   Static Dataset executequery ( String Plain text, Params   Object [] P ){ Using (Sqliteconnection conn = New Sqliteconnection (connectionstring )){ Using (Sqlitecommand command = New Sqlitecommand () {dataset DS = New Dataset (); preparecommand (command, Conn, plain text, P); sqlitedataadapter da = New Sqlitedataadapter (command); da. Fill (DS ); Return DS ;}}} Public   Static   Int Executenonquery ( String Plain text, Params   Object [] P ){Using (Sqliteconnection conn = New Sqliteconnection (connectionstring )){ Using (Sqlitecommand command = New Sqlitecommand () {preparecommand (command, Conn, plain text, P ); Return Command. executenonquery ();}}} Public   Static Sqlitedatareader executereader ( String Plain text, Params  Object [] P ){ Using (Sqliteconnection conn = New Sqliteconnection (connectionstring )){ Using (Sqlitecommand command = New Sqlitecommand () {preparecommand (command, Conn, plain text, P ); Return Command. executereader (commandbehavior. closeconnection );}}} Public   Static   Object Executescalar (String Plain text, Params   Object [] P ){ Using (Sqliteconnection conn = New Sqliteconnection (connectionstring )){ Using (Sqlitecommand command = New Sqlitecommand () {preparecommand (command, Conn, plain text, P ); Return Command. executescalar ();}}}}}

  Vi. Notes SQLite Official Website: http://www.sqlite. org/at first glance saw the characteristics of SQLite. 1. ACID transaction 2. zero Configuration-no installation or configuration management required. 3. A complete database stored in a single disk file 4. database files can be freely shared among machines with different bytes. the database size is 2 TB 6. small enough, roughly 30 thousand rows of C Code , 250 K 7. it is faster than some popular databases in most common databases. simple and Easy API 9. including TCL binding and supporting binding in other languages through wrapper 10. well noted Source code And has a test coverage rate of more than 90%. 11. independent: no additional dependencies 12. source completely open, you can use it for any purpose, including sell it 13. supports multiple development languages, including C, PHP, Perl, Java, and ASP. net, Python