The application of SQLite in. Net

ide:vs2005

Language: C #

Component: sqlite-netfx20-setup-bundle-x86-2005-1.0.92.0

Brief introduction:

This example uses the password and foreign key features of SQLite. and close temporary files (journal mode=off).

Demonstrate the practicality of C # using SQLite by querying and inserting (modifying, deleting).

First, design the form

Drag 2 textbox, 2 button, 1 DataGridView, 1 sqliteconnection, 1 Sqlitecommand

Set the Connection property of Sqlitecommand to Sqliteconnection

The complete form:

Second, the function realizes 1. Inquire

(1) Setting up the data bridge

           DataAdapter = new Sqlitedataadapter (SQLITECOMMAND1);

(2) Setting the cache

Cmdbuilder = new Sqlitecommandbuilder (dataAdapter);

(3) Create a data sheet

Table = new DataTable (tblname);

(4) Connection data Sheet

DataAdapter.Fill (table);

(5) Specifying a data source

Datagridview1.datasource = table;

To locate the name of the table, we use the Find specific keyword: from.

int iPos = Statementstr.lastindexof ("from") + 5;            String tblname = statementstr.substring (IPos, (statementstr.length-1)-IPos);

2. Insert

Break the INSERT statement by line, or you can execute the update, delete, and so on.

            int i = edtStmt.Lines.Length;            for (int j = 0; J < i; j + +)            {                String Strsta = edtstmt.lines[j]. ToString ();                Sqlitecommand1.commandtext = Strsta;                Sqlitecommand1.executenonquery ();            }

Third, the source code

Form1.cs

Using system;using system.collections.generic;using system.componentmodel;using system.data;using System.Drawing; Using system.text;using system.windows.forms;using system.io;using system.data.sqlite;namespace testSQLite{Public        Partial class Form1:form {private DataTable table;        Private Sqlitedataadapter DataAdapter;        Private Sqlitecommandbuilder Cmdbuilder;        Private String strfmt, connstr;        Private String Currdir;            Public Form1 () {InitializeComponent ();            Currdir = Directory.GetCurrentDirectory ();                    STRFMT = "Data source=\" "+ Currdir +"/{0}\ "" + ";p age size=32768;"                    + "Cache size=0;"                    + "password=20130321;"                    + "foreign keys=true;"        + "Journal Mode=off"; The private void Btnexec_click (object sender, EventArgs e) {if (EdtStmt.Text.ToLower ().          IndexOf ("Insert") < 0) return;  ConnStr = string.            Format (strfmt, Edtdb.text);            Sqliteconnection1.close ();            sqliteconnection1.connectionstring = ConnStr;            Sqliteconnection1.open ();            int i = edtStmt.Lines.Length; for (int j = 0; J < i; J + +) {String Strsta = edtstmt.lines[j].                ToString ();                Sqlitecommand1.commandtext = Strsta;            Sqlitecommand1.executenonquery ();        } MessageBox.Show ("Insert done"); private void btnSearch_Click (object sender, EventArgs e) {if (EdtStmt.Text.IndexOf (";") < 0                {MessageBox.Show ("Please enter a SQL statement Terminator-'; '");            Return } connstr = string.            Format (strfmt, Edtdb.text);            String statementstr = EdtStmt.Text.ToLower ();            int iPos = Statementstr.lastindexof ("from") + 5; String tblname = statementstr.substring (IPos, (Statementstr.length-1)-IPos);            Sqliteconnection1.close ();            sqliteconnection1.connectionstring = ConnStr;            Sqliteconnection1.open ();            Sqlitecommand1.commandtext = Edtstmt.text;            DataAdapter = new Sqlitedataadapter (SQLITECOMMAND1);            Cmdbuilder = new Sqlitecommandbuilder (dataAdapter);            Table = new DataTable (tblname);            DataAdapter.Fill (table);        Datagridview1.datasource = table; }    }}

Reference Document: SQLite.NET.chm