1. Ado. net provider for SQLite.
ADO. NET provides Program It is provided by a company in Hong Kong. For the project address, see: http://sourceforge.net/projects/sqlite-dotnet2.
2. encapsulate SQLite. NET and provide a simple operation help class.
Sqlitehelper
CopyCode The Code is as follows :/**//**
* SQLite operation help class.
*
* Author: egmkang. Wang
* Date: 2009-06-21
*/
Namespace system. Data. SQLite
{
Using system. Data;
Using system. Data. SQLite;
Using system. IO;
Public class sqlitehelper
{
Private Static string Pwd = "PWD ";
Private Static string Path = path. getdirectoryname (system. reflection. Assembly. getexecutingassembly (). getname (). codebase) + "\ sqlitetest. DB ";
Private Static string connstring = string. Format ("Data Source = \" {0} \ "", path, PWD );
/** // <Summary>
/// Returns the database link string
/// </Summary>
Public static string connstring
{
Get {return connstring ;}
}
/** // <Summary>
/// Execute the SQL statement and return the affected number of rows
/// </Summary>
/// <Param name = "plain text"> SQL statement to be executed </param>
/// <Returns> Number of affected rows </returns>
Public static int executenonquery (string plain text)
{
Return executenonquery (connstring, plain text );
}
/** // <Summary>
/// Execute SQL statements with transactions
/// </Summary>
/// <Param name = "trans"> transaction </param>
/// <Param name = "plain text"> SQL statement </param>
/// <Returns> Number of affected rows </returns>
Public static int executenonquery (sqlitetransaction trans, string plain text, Params sqliteparameter [] parameters)
{
Int val = 0;
Using (sqlitecommand cmd = new sqlitecommand ())
{
Preparecommand (CMD, (sqliteconnection) Trans. Connection, trans, plain text, parameters );
Val = cmd. executenonquery ();
Cmd. Parameters. Clear ();
}
Return val;
}
/** // <Summary>
/// Execute the SQL statement and return the affected number of rows
/// </Summary>
/// <Param name = "connstring"> connection string </param>
/// <Param name = "plain text"> SQL statement </param>
/// <Param name = "Parameters"> SQL parameters </param>
/// <Returns> Number of affected rows </returns>
Public static int executenonquery (string connstring, string plain text, Params sqliteparameter [] parameters)
{
Using (sqliteconnection conn = new sqliteconnection (connstring ))
{
Return executenonquery (Conn, plain text, parameters );
}
}
/** // <Summary>
/// Execute the SQL statement and return the affected number of rows
/// </Summary>
/// <Param name = "connection"> database link </param>
/// <Param name = "plain text"> SQL statement </param>
/// <Param name = "Parameters"> parameter </param>
/// <Returns> Number of affected rows </returns>
Public static int executenonquery (sqliteconnection connection, string plain text, Params sqliteparameter [] parameters)
{
Int val = 0;
Using (sqlitecommand cmd = new sqlitecommand ())
{
Preparecommand (CMD, connection, null, plain text, parameters );
Val = cmd. executenonquery ();
Cmd. Parameters. Clear ();
}
Return val;
}
/** // <Summary>
/// Execute the query and return the first column of the first row of the result set. All other rows and columns are ignored.
/// </Summary>
/// <Param name = "plain text"> SQL statement </param>
/// <Returns> value of the first column of the First row </returns>
Public static object executescalar (string plain text)
{
Return executescalar (connstring, plain text );
}
/** // <Summary>
/// Execute the query and return the first column of the first row of the result set. All other rows and columns are ignored.
/// </Summary>
/// <Param name = "connstring"> connection string </param>
/// <Param name = "plain text"> SQL statement </param>
/// <Returns> value of the first column of the First row </returns>
Public static object executescalar (string connstring, string plain text)
{
Using (sqliteconnection conn = new sqliteconnection (connstring ))
{
Return executescalar (Conn, plain text );
}
}
/** // <Summary>
/// Execute the query and return the first column of the first row of the result set. All other rows and columns are ignored.
/// </Summary>
/// <Param name = "connection"> database link </param>
/// <Param name = "plain text"> SQL statement </param>
/// <Returns> value of the first column of the First row </returns>
Public static object executescalar (sqliteconnection connection, string plain text)
{
Object val;
Using (sqlitecommand cmd = new sqlitecommand ())
{
Preparecommand (CMD, connection, null, plain text );
Val = cmd. executescalar ();
}
Return val;
}
/** // <Summary>
/// Execute the SQL statement and return the datareader of the result set
/// </Summary>
/// <Param name = "plain text"> SQL statement </param>
/// <Param name = "Parameters"> parameter </param>
/// <Returns> datareader of the result set </returns>
Public static sqlitedatareader executereader (string plain text, Params sqliteparameter [] parameters)
{
Return executereader (connstring, plain text, parameters );
}
/** // <Summary>
/// Execute the SQL statement and return the datareader of the result set
/// </Summary>
/// <Param name = "connstring"> connection string </param>
/// <Param name = "plain text"> SQL statement </param>
/// <Param name = "Parameters"> parameter </param>
/// <Returns> datareader of the result set </returns>
Public static sqlitedatareader executereader (string connstring, string plain text, Params sqliteparameter [] parameters)
{
Sqliteconnection conn = new sqliteconnection (connstring );
Sqlitecommand cmd = new sqlitecommand ();
Try
{
Preparecommand (CMD, Conn, null, plain text, parameters );
Sqlitedatareader RDR = cmd. executereader (commandbehavior. closeconnection );
Cmd. Parameters. Clear ();
Return RDR;
}
Catch
{
Conn. Close ();
Throw;
}
}
/** // <Summary>
/// Pre-process the initialization of command objects, database links, transactions, objects to be executed, parameters, etc.
/// </Summary>
/// <Param name = "cmd"> command object </param>
/// <Param name = "conn"> connection object </param>
/// <Param name = "trans"> transcation object </param>
/// <Param name = "plain text"> SQL text </param>
/// <Param name = "Parameters"> parameter instance </param>
Private Static void preparecommand (sqlitecommand cmd, sqliteconnection Conn, sqlitetransaction trans, string plain text, Params sqliteparameter [] parameters)
{
If (conn. State! = Connectionstate. open)
Conn. open ();
Cmd. Connection = conn;
Cmd. commandtext = plain text;
If (trans! = NULL)
Cmd. Transaction = trans;
If (null! = Parameters & parameters. length> 0)
{
Cmd. Parameters. addrange (parameters );
}
}
}
}
3. add, delete, modify, and query: copy the Code the code is as follows: insert, delete, update
const string s_addresstreeintosqlite = "insert into [addresstree] ([ID], [itemtype], [parentid], [name]) values (@ ID, @ itemtype, @ parentid, @ name); ";
sqlitehelper. executenonquery (Tran, s_addresstreew.sqlite,
// new sqliteparameter [] Here
);
select
const string s_addrestreefromsqlce = "select [ID], [itemtype], [parentid], [name] from [addresstree]; ";
using (sqlcedatareader RDR = sqlcehelper. executereader (s_addrestreefromsqlce)
{< br> while (RDR. read ()
{< br> // read data here
}< BR >}
4. other
SQLite is absolutely powerful in performance. four-table join query: 200 queries. It takes 44 seconds for SQL Ce (with index) and 3-6 seconds for SQLite (with index ).
insert, delete update performance see http://www.cnblogs.com/egmkang/archive/2009/06/06/1497678.html
PS: it is best to use a single persistent connection, instead of a connectionstring, When you recently discovered SQL Execution. the reason is very simple. There is no connection pool technology for Embedded databases.
the cost of opening or closing links in database queries is relatively high. note the following when writing a program.
In addition, binary resources need to be released in a timely manner, such as sqlcommand. The experience for writing web programs is not great. After all, the environment has a large amount of
memory, GC efficiency is relatively high.
