Use SQLite in. net

Document directory

• First
• Second
• Next

I recently learned how to use SQLite and record it.

About the configuration in. Net can be seen: local data storage solution SQLite: http://www.cnblogs.com/luminji/archive/2010/12/19/1910396.html

This article is very detailed.

First

The SQLite database is a text file. Therefore, you do not need to consider the tools to be installed on the server or the client. Generally, compared with access, access must be installed before it can be used.

Second

There are a lot of SQLite management tools. I searched a lot online. sqlitestudio is a lightweight management tool. I use navicat for SQLite. Of course, there are other management tools based on personal preferences.

 

Next

Start pasting code,

I am too lazy about the sqlitehelper class. I copied the link above, but I still posted it.

 

Sqlitehelper public class sqlitehelper {/// <summary> /// connectionstring sample ''example: Using datasource = test. db3; pooling = true; failifmissing = false /// </Summary> Public static string Path = system. appdomain. currentdomain. basedirectory + "Dal \ x2010.dll"; public static string connectionstring = "Data Source =" + path + "; pooling = true; failifmissing = false "; private Static void preparecommand (sqlitecommand cmd, sqliteco Nnection Conn, string plain text, Params sqliteparameter [] 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); cmd. parameters. add (parm) ;}} public static dataset executequery (string plain text, Params sqliteparameter [] 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 sqliteparameter [] 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 sqliteparameter [] 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 sqliteparameter [] P) {using (sqliteconnection conn = new sqliteconnection (connectionstring )) {using (sqlitecommand command = new sqlitecommand () {preparecommand (command, Conn, plain text, P); return command. executescalar ();}}}

 

For example, add, delete, modify, and query:

A table created in the database:

sqlCREATE TABLE "tb_nodes" ("id"  INTEGER,"fid"  INTEGER,"NodeName"  TEXT(50),"Remark"  TEXT(500),PRIMARY KEY ("id" ASC));

/// <Summary> /// does the addmediaworkflow?? Data volume: Y // </Summary> /// <Param name = "Node"> </param> /// <returns> </returns> Public int nodeadd (model. nodes node) {If (node! = NULL) {string SQL = "insert into tb_nodes (FID, nodename, remark) values (@ FID, @ nodename, @ remark); select last_insert_rowid ();"; sqliteparameter [] parameter = {New sqliteparameter ("@ FID", node. FID), new sqliteparameter ("@ nodename", node. nodename), new sqliteparameter ("@ remark", node. remark)}; object I = Dal. sqlitehelper. executescalar (SQL, parameter); if (I! = NULL) return convert. toint16 (I); else return 0;} else return 0;} /// <summary> // modify t? One shard?? Data volume: Y // </Summary> /// <Param name = "Node"> </param> /// <returns> </returns> Public int nodeupdate (model. nodes node) {If (node! = NULL) {string SQL = "Update tb_nodes set FID = @ FID, nodename = @ nodename, remark = @ remark where id = @ ID "; sqliteparameter [] parameter = {New sqliteparameter ("@ ID", node. ID), new sqliteparameter ("@ FID", node. FID), new sqliteparameter ("@ nodename", node. nodename), new sqliteparameter ("@ remark", node. remark)}; int I = Dal. sqlitehelper. executenonquery (SQL, parameter); return I;} else return 0;} // <sum Mary> // Delete objects? Except that yid is anodeid? Data volume: Y // </Summary> /// <Param name = "nodeid"> </param> /// <returns> </returns> Public int nodedelete (int nodeid) {string SQL = "delete from tb_nodes where id = @ nodeid"; sqliteparameter [] parameter = {New sqliteparameter ("@ nodeid", nodeid)}; int I = Dal. sqlitehelper. executenonquery (SQL, parameter); return I ;}

Is it found that the operation is very similar to that of SQL Server.

There is an error:
If the database does not exist during the operation, it will automatically create a database for you. When the database access path name is accidentally written incorrectly, because a new database is created, the error "No such table: [tablename]" will not be thrown because the database does not exist.