Sqllitehelper: C #-based sqllite Database Access Base Class

Using system;
Using system. Collections. Generic;
Using system. text;
Using system. Web;
Using system. configuration;
Using system. Data;
Using system. Data. SQLite;
Namespace dal
{
Public class SQLite
{
/// <Summary>
/// Obtain the connection object
/// </Summary>
/// <Returns> </returns>
Public static sqliteconnection getsqliteconnection ()
{
Return new sqliteconnection ("Data Source =" + system. web. httpcontext. current. server. mappath (system. configuration. configurationmanager. appsettings ["DB"]. tostring ()));
}
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 );
// For (INT I = 0; I <p. length; I ++)
// Cmd. Parameters [I]. value = P [I];
}
}
Public static dataset executedataset (string plain text, Params object [] P)
{
Dataset DS = new dataset ();
Sqlitecommand command = new sqlitecommand ();
Using (sqliteconnection connection = getsqliteconnection ())
{
Preparecommand (command, connection, plain text, P );
Sqlitedataadapter da = new sqlitedataadapter (command );
Da. Fill (DS );
}
Return Ds;
}
Public static datarow executedatarow (string plain text, Params object [] P)
{
Dataset DS = executedataset (plain text, P );
If (Ds! = NULL & Ds. Tables. Count> 0 & Ds. Tables [0]. Rows. Count> 0)
Return Ds. Tables [0]. Rows [0];
Return NULL;
}
/// <Summary>
/// Return the number of affected rows
/// </Summary>
/// <Param name = "plain text"> A </param>
/// <Param name = "commandparameters"> input parameters </param>
/// <Returns> </returns>
Public static int executenonquery (string plain text, Params object [] P)
{
Sqlitecommand command = new sqlitecommand ();
Using (sqliteconnection connection = getsqliteconnection ())
{
Preparecommand (command, connection, plain text, P );
Return command. executenonquery ();
}
}
/// <Summary>
/// Return the sqldatareader object
/// </Summary>
/// <Param name = "plain text"> </param>
/// <Param name = "commandparameters"> input parameters </param>
/// <Returns> </returns>
Public static sqlitedatareader executereader (string plain text, Params object [] P)
{
Sqlitecommand command = new sqlitecommand ();
Sqliteconnection connection = getsqliteconnection ();
Try
{
Preparecommand (command, connection, plain text, P );
Sqlitedatareader reader = command. executereader (commandbehavior. closeconnection );
Return reader;
}
Catch
{
Connection. Close ();
Throw;
}
}
/// <Summary>
/// The first column of the First row in the returned result set. ignore other rows or columns.
/// </Summary>
/// <Param name = "plain text"> </param>
/// <Param name = "commandparameters"> input parameters </param>
/// <Returns> </returns>
Public static object executescalar (string plain text, Params object [] P)
{
Sqlitecommand cmd = new sqlitecommand ();
Using (sqliteconnection connection = getsqliteconnection ())
{
Preparecommand (CMD, connection, plain text, P );
Return cmd. executescalar ();
}
}
/// <Summary>
/// Pagination
/// </Summary>
/// <Param name = "recordcount"> </param>
/// <Param name = "pageindex"> </param>
/// <Param name = "pagesize"> </param>
/// <Param name = "plain text"> </param>
/// <Param name = "counttext"> </param>
/// <Param name = "p"> </param>
/// <Returns> </returns>
Public static dataset executepager (ref int recordcount, int pageindex, int pagesize, string comment text, string counttext, Params object [] P)
{
If (recordcount <0)
Recordcount = int. parse (executescalar (counttext, p). tostring ());
Dataset DS = new dataset ();
Sqlitecommand command = new sqlitecommand ();
Using (sqliteconnection connection = getsqliteconnection ())
{
Preparecommand (command, connection, plain text, P );
Sqlitedataadapter da = new sqlitedataadapter (command );
Da. Fill (DS, (pageindex-1) * pagesize, pagesize, "result ");
}
Return Ds;
}
}
}