Sqlite Operation Class Code _sqlite

1. Ado.net Provider for SQLite.
The ado.net provider is provided by a company in Hong Kong (seemingly). See Project Address: Http://sourceforge.net/projects/sqlite-dotnet2
2. For sqlite.net encapsulation, provide a simple operation help class.
Sqlitehelper

Copy Code code as follows:

/**//**
* Help class for SQLite operation.
*
* 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>
return database link string
</summary>
public static string connstring
{
get {return connstring;}
}
/**////<summary>
Executes an SQL statement that returns the number of rows affected
</summary>
<param name= "Cmdtext" > SQL statement to be executed </param>
<returns> number of rows affected </returns>
public static int ExecuteNonQuery (string cmdtext)
{
Return ExecuteNonQuery (connstring, Cmdtext);
}
/**////<summary>
Executing an SQL statement with a transaction
</summary>
<param name= "Trans" > Affairs </param>
<param name= "Cmdtext" >sql statement </param>
<returns> number of rows affected </returns>
public static int ExecuteNonQuery (Sqlitetransaction trans, string cmdtext, params sqliteparameter[] parameters)
{
int val = 0;
using (sqlitecommand cmd = new Sqlitecommand ())
{
PrepareCommand (cmd, (sqliteconnection) trans. Connection, trans, cmdtext, parameters);
val = cmd. ExecuteNonQuery ();
Cmd. Parameters.clear ();
}
return Val;
}
/**////<summary>
Executes an SQL statement that returns the number of rows affected
</summary>
<param name= "connstring" > Connection string </param>
<param name= "Cmdtext" >sql statement </param>
<param name= "Parameters" >sql parameters </param>
<returns> number of rows affected </returns>
public static int ExecuteNonQuery (string connstring, String cmdtext, params sqliteparameter[] parameters)
{
using (sqliteconnection conn = new Sqliteconnection (connstring))
{
Return ExecuteNonQuery (conn, cmdtext, parameters);
}
}
/**////<summary>
Executes an SQL statement that returns the number of rows affected
</summary>
<param name= "Connection" > Database links </param>
<param name= "Cmdtext" >sql statement </param>
<param name= "Parameters" > Parameters </param>
<returns> number of rows affected </returns>
public static int ExecuteNonQuery (sqliteconnection connection, string cmdtext, params sqliteparameter[] parameters)
{
int val = 0;
using (sqlitecommand cmd = new Sqlitecommand ())
{
PrepareCommand (cmd, connection, NULL, cmdtext, parameters);
val = cmd. ExecuteNonQuery ();
Cmd. Parameters.clear ();
}
return Val;
}
/**////<summary>
Executes the query and returns the first column of the first row of the result set. All other rows and columns are ignored.
</summary>
<param name= "Cmdtext" >sql statement </param>
<returns> the value of the first column of the first row </returns>
public static Object ExecuteScalar (String cmdtext)
{
Return ExecuteScalar (connstring, Cmdtext);
}
/**////<summary>
Executes the query and returns 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= "Cmdtext" >sql statement </param>
<returns> the value of the first column of the first row </returns>
public static Object ExecuteScalar (String connstring, String cmdtext)
{
using (sqliteconnection conn = new Sqliteconnection (connstring))
{
Return ExecuteScalar (conn, cmdtext);
}
}
/**////<summary>
Executes the query and returns the first column of the first row of the result set. All other rows and columns are ignored.
</summary>
<param name= "Connection" > Database links </param>
<param name= "Cmdtext" >sql statement </param>
<returns> the value of the first column of the first row </returns>
public static object ExecuteScalar (sqliteconnection connection, string cmdtext)
{
Object Val;
using (sqlitecommand cmd = new Sqlitecommand ())
{
PrepareCommand (cmd, connection, NULL, cmdtext);
val = cmd. ExecuteScalar ();
}
return Val;
}
/**////<summary>
Executes the SQL statement that returns the DataReader of the result set
</summary>
<param name= "Cmdtext" >sql statement </param>
<param name= "Parameters" > Parameters </param>
Datareader</returns> of <returns> result set
public static Sqlitedatareader ExecuteReader (string cmdtext, params sqliteparameter[] parameters)
{
Return ExecuteReader (connstring, cmdtext, parameters);
}
/**////<summary>
Executes the SQL statement that returns the DataReader of the result set
</summary>
<param name= "connstring" > Connection string </param>
<param name= "Cmdtext" >sql statement </param>
<param name= "Parameters" > Parameters </param>
Datareader</returns> of <returns> result set
public static Sqlitedatareader ExecuteReader (String connstring, String cmdtext, params sqliteparameter[] parameters)
{
Sqliteconnection conn = new sqliteconnection (connstring);
Sqlitecommand cmd = new Sqlitecommand ();
Try
{
PrepareCommand (CMD, conn, null, cmdtext, parameters);
Sqlitedatareader rdr = cmd. ExecuteReader (commandbehavior.closeconnection);
Cmd. Parameters.clear ();
return RDR;
}
Catch
{
Conn. Close ();
Throw
}
}
/**////<summary>
Preprocessing command objects, database links, transactions, objects that need to be executed, parameters, and so on initialization
</summary>
<param name= "cmd" >command object </param>
<param name= "conn" >connection object </param>
<param name= "Trans" >transcation object </param>
<param name= "Cmdtext" >sql text</param>
<param name= "Parameters" > Parameter Instance </param>
private static void PrepareCommand (Sqlitecommand cmd, sqliteconnection conn, sqlitetransaction trans, string cmdtext, par AMS sqliteparameter[] Parameters)
{
IF (Conn. State!= ConnectionState.Open)
Conn. Open ();
Cmd. Connection = conn;
Cmd.commandtext = Cmdtext;
if (trans!= null)
Cmd. Transaction = trans;
if (null!= parameters && parameters. Length > 0)
{
Cmd. Parameters.addrange (Parameters);
}
}
}
}

3. Change and check:

Copy Code code 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_addresstreeintosqlite,
New sqliteparameter[] Here
);
Select
Const string s_addrestreefromsqlce = "SELECT [Id],[itemtype],[parentid],[name] from [Addresstree];";
using (SqlCeDataReader RDR = Sqlcehelper.executereader (s_addrestreefromsqlce))
{
while (RDR. Read ())
{
Read Data Here
}
}

4. Other
SQLite performance is absolutely strong. Four table connection query, query 200 times, SQL CE needs 44 seconds (with index), SQLite only need 3-6 seconds (with index).
Insert, remove update performance See http://www.cnblogs.com/egmkang/archive/2009/06/06/1497678.html
PS: It is best to use a single long connection, rather than connectionstring, when executing SQL recently. The reason is very simple, the embedded database does not have connection pooling technology,
The cost of opening and closing a link in a database query is relatively high. This is noted below when writing a program.
Also, with regard to binary resources, need to be released in time, such as SqlCommand, these are not very experienced when writing Web applications, after all, that environment has a large number of
of memory, the efficiency of GC is relatively high.