本文实例讲述了c#基于sqlitehelper类似sqlhelper类实现存取sqlite数据库的方法。分享给大家供大家参考。具体如下：
这个类不是我实现的，英文原文地址为http://www.eggheadcafe.com/articles/20050315.asp，这里修改了原文中分析sql语句参数的方法，将方法名修改为attachparameters，将其修饰符修改为private，并直接传递command到这个方法，直接绑定参数到comand。修改后的代码如下
using system;
using system.data;
using system.text.regularexpressions;
using system.xml;
using system.io;
using system.collections;
using system.data.sqlite;
namespace dbutility.sqlite
{
/// <summary>
/// sqlitehelper is a utility class similar to "sqlhelper" in ms
/// data access application block and follows similar pattern.
/// </summary>
public class sqlitehelper
{
/// <summary>
/// creates a new <see cref="sqlitehelper"/> instance. the ctor is marked private since all members are static.
/// </summary>
private sqlitehelper()
{
}
/// <summary>
/// creates the command.
/// </summary>
/// <param name="connection">connection.</param>
/// <param name="commandtext">command text.</param>
/// <param name="commandparameters">command parameters.</param>
/// <returns>sqlite command</returns>
public static sqlitecommand createcommand(sqliteconnection connection, string commandtext, params sqliteparameter[] commandparameters)
{
sqlitecommand cmd = new sqlitecommand(commandtext, connection);
if (commandparameters.length > 0)
{
foreach (sqliteparameter parm in commandparameters)
cmd.parameters.add(parm);
}
return cmd;
}
/// <summary>
/// creates the command.
/// </summary>
/// <param name="connectionstring">connection string.</param>
/// <param name="commandtext">command text.</param>
/// <param name="commandparameters">command parameters.</param>
/// <returns>sqlite command</returns>
public static sqlitecommand createcommand(string connectionstring, string commandtext, params sqliteparameter[] commandparameters)
{
sqliteconnection cn = new sqliteconnection(connectionstring);
sqlitecommand cmd = new sqlitecommand(commandtext, cn);
if (commandparameters.length > 0)
{
foreach (sqliteparameter parm in commandparameters)
cmd.parameters.add(parm);
}
return cmd;
}
/// <summary>
/// creates the parameter.
/// </summary>
/// <param name="parametername">name of the parameter.</param>
/// <param name="parametertype">parameter type.</param>
/// <param name="parametervalue">parameter value.</param>
/// <returns>sqliteparameter</returns>
public static sqliteparameter createparameter(string parametername, system.data.dbtype parametertype, object parametervalue)
{
sqliteparameter parameter = new sqliteparameter();
parameter.dbtype = parametertype;
parameter.parametername = parametername;
parameter.value = parametervalue;
return parameter;
}
/// <summary>
/// shortcut method to execute dataset from sql statement and object[] arrray of parameter values
/// </summary>
/// <param name="connectionstring">sqlite connection string</param>
/// <param name="commandtext">sql statement with embedded "@param" style parameter names</param>
/// <param name="paramlist">object[] array of parameter values</param>
/// <returns></returns>
public static dataset executedataset(string connectionstring, string commandtext, object[] paramlist)
{
sqliteconnection cn = new sqliteconnection(connectionstring);
sqlitecommand cmd = cn.createcommand();
cmd.commandtext = commandtext;
if (paramlist != null)
{
attachparameters(cmd,commandtext, paramlist);
}
dataset ds = new dataset();
if (cn.state == connectionstate.closed)
cn.open();
sqlitedataadapter da = new sqlitedataadapter(cmd);
da.fill(ds);
da.dispose();
cmd.dispose();
cn.close();
return ds;
}
/// <summary>
/// shortcut method to execute dataset from sql statement and object[] arrray of parameter values
/// </summary>
/// <param name="cn">connection.</param>
/// <param name="commandtext">command text.</param>
/// <param name="paramlist">param list.</param>
/// <returns></returns>
public static dataset executedataset(sqliteconnection cn, string commandtext, object[] paramlist)
{
sqlitecommand cmd = cn.createcommand();
cmd.commandtext = commandtext;
if (paramlist != null)
{
attachparameters(cmd,commandtext, paramlist);
}
dataset ds = new dataset();
if (cn.state == connectionstate.closed)
cn.open();
sqlitedataadapter da = new sqlitedataadapter(cmd);
da.fill(ds);
da.dispose();
cmd.dispose();
cn.close();
return ds;
}
/// <summary>
/// executes the dataset from a populated command object.
/// </summary>
/// <param name="cmd">fully populated sqlitecommand</param>
/// <returns>dataset</returns>
public static dataset executedataset(sqlitecommand cmd)
{
if (cmd.connection.state == connectionstate.closed)
cmd.connection.open();
dataset ds = new dataset();
sqlitedataadapter da = new sqlitedataadapter(cmd);
da.fill(ds);
da.dispose();
cmd.connection.close();
cmd.dispose();
return ds;
}
/// <summary>
/// executes the dataset in a sqlite transaction
/// </summary>
/// <param name="transaction">sqlitetransaction. transaction consists of connection, transaction, /// and command, all of which must be created prior to making this method call. </param>
/// <param name="commandtext">command text.</param>
/// <param name="commandparameters">sqlite command parameters.</param>
/// <returns>dataset</returns>
/// <remarks>user must examine transaction object and handle transaction.connection .close, etc.</remarks>
public static dataset executedataset(sqlitetransaction transaction, string commandtext, params sqliteparameter[] commandparameters)
{
if (transaction == null) throw new argumentnullexception("transaction");
if (transaction != null && transaction.connection == null) throw new argumentexception("the transaction was rolled back or committed, please provide an open transaction.", "transaction");
idbcommand cmd = transaction.connection.createcommand();
cmd.commandtext = commandtext;
foreach (sqliteparameter parm in commandparameters)
{
cmd.parameters.add(parm);
}
if (transaction.connection.state == connectionstate.closed)
transaction.connection.open();
dataset ds = executedataset((sqlitecommand)cmd);
return ds;
}
/// <summary>
/// executes the dataset with transaction and object array of parameter values.
/// </summary>
/// <param name="transaction">sqlitetransaction. transaction consists of connection, transaction, /// and command, all of which must be created prior to making this method call. </param>
/// <param name="commandtext">command text.</param>
/// <param name="commandparameters">object[] array of parameter values.</param>
/// <returns>dataset</returns>
/// <remarks>user must examine transaction object and handle transaction.connection .close, etc.</remarks>
public static dataset executedataset(sqlitetransaction transaction, string commandtext, object[] commandparameters)
{
if (transaction == null) throw new argumentnullexception("transaction");
if (transaction != null && transaction.connection == null) throw new argumentexception("the transaction was rolled back or committed, please provide an open transaction.", "transaction");
idbcommand cmd = transaction.connection.createcommand();
cmd.commandtext = commandtext;
attachparameters((sqlitecommand)cmd,cmd.commandtext, commandparameters);
if (transaction.connection.state == connectionstate.closed)
transaction.connection.open();
dataset ds = executedataset((sqlitecommand)cmd);
return ds;
}
#region updatedataset
/// <summary>
/// executes the respective command for each inserted, updated, or deleted row in the dataset.
/// </summary>
/// <remarks>
/// e.g.:
/// updatedataset(conn, insertcommand, deletecommand, updatecommand, dataset, "order");
/// </remarks>
/// <param name="insertcommand">a valid sql statement to insert new records into the data source</param>
/// <param name="deletecommand">a valid sql statement to delete records from the data source</param>
/// <param name="updatecommand">a valid sql statement used to update records in the data source</param>
/// <param name="dataset">the dataset used to update the data source</param>
/// <param name="tablename">the datatable used to update the data source.</param>
public static void updatedataset(sqlitecommand insertcommand, sqlitecommand deletecommand, sqlitecommand updatecommand, dataset dataset, string tablename)
{
if (insertcommand == null) throw new argumentnullexception("insertcommand");
if (deletecommand == null) throw new argumentnullexception("deletecommand");
if (updatecommand == null) throw new argumentnullexception("updatecommand");
if (tablename == null || tablename.length == 0) throw new argumentnullexception("tablename");
// create a sqlitedataadapter, and dispose of it after we are done
using (sqlitedataadapter dataadapter = new sqlitedataadapter())
{
// set the data adapter commands
dataadapter.updatecommand = updatecommand;
dataadapter.insertcommand = insertcommand;
dataadapter.deletecommand = deletecommand;
// update the dataset changes in the data source
dataadapter.update(dataset, tablename);
// commit all the changes made to the dataset
dataset.acceptchanges();
}
}
#endregion
/// <summary>
/// shortcut method to return idatareader
/// note: you should explicitly close the command.connection you passed in as
/// well as call dispose on the command after reader is closed.
/// we do this because idatareader has no underlying connection property.
/// </summary>
/// <param name="cmd">sqlitecommand object</param>
/// <param name="commandtext">sql statement with optional embedded "@param" style parameters</param>
/// <param name="paramlist">object[] array of parameter values</param>
/// <returns>idatareader</returns>
public static idatareader executereader(sqlitecommand cmd, string commandtext, object[] paramlist)
{
if (cmd.connection == null)
throw new argumentexception("command must have live connection attached.", "cmd");
cmd.commandtext = commandtext;
attachparameters(cmd,commandtext, paramlist);
if (cmd.connection.state == connectionstate.closed)
cmd.connection.open();
idatareader rdr = cmd.executereader(commandbehavior.closeconnection);
return rdr;
}
/// <summary>
/// shortcut to executenonquery with sqlstatement and object[] param values
/// </summary>
/// <param name="connectionstring">sqlite connection string</param>
/// <param name="commandtext">sql statement with embedded "@param" style parameters</param>
/// <param name="paramlist">object[] array of parameter values</param>
/// <returns></returns>
public static int executenonquery(string connectionstring, string commandtext, params object[] paramlist)
{
sqliteconnection cn = new sqliteconnection(connectionstring);
sqlitecommand cmd = cn.createcommand();
cmd.commandtext = commandtext;
attachparameters(cmd,commandtext, paramlist);
if (cn.state == connectionstate.closed)
cn.open();
int result = cmd.executenonquery();
cmd.dispose();
cn.close();
return result;
}
public static int executenonquery(sqliteconnection cn, string commandtext, params object[] paramlist)
{
sqlitecommand cmd = cn.createcommand();
cmd.commandtext = commandtext;
attachparameters(cmd,commandtext, paramlist);
if (cn.state == connectionstate.closed)
cn.open();
int result = cmd.executenonquery();
cmd.dispose();
cn.close();
return result;
}
/// <summary>
/// executes non-query sql statment with transaction
/// </summary>
/// <param name="transaction">sqlitetransaction. transaction consists of connection, transaction, /// and command, all of which must be created prior to making this method call. </param>
/// <param name="commandtext">command text.</param>
/// <param name="paramlist">param list.</param>
/// <returns>integer</returns>
/// <remarks>user must examine transaction object and handle transaction.connection .close, etc.</remarks>
public static int executenonquery(sqlitetransaction transaction, string commandtext, params object[] paramlist)
{
if (transaction == null) throw new argumentnullexception("transaction");
if (transaction != null && transaction.connection == null) throw new argumentexception("the transaction was rolled back or committed, please provide an open transaction.", "transaction");
idbcommand cmd = transaction.connection.createcommand();
cmd.commandtext = commandtext;
attachparameters((sqlitecommand)cmd,cmd.commandtext, paramlist);
if (transaction.connection.state == connectionstate.closed)
transaction.connection.open();
int result = cmd.executenonquery();
cmd.dispose();
return result;
}
/// <summary>
/// executes the non query.
/// </summary>
/// <param name="cmd">cmd.</param>
/// <returns></returns>
public static int executenonquery(idbcommand cmd)
{
if (cmd.connection.state == connectionstate.closed)
cmd.connection.open();
int result = cmd.executenonquery();
cmd.connection.close();
cmd.dispose();
return result;
}
/// <summary>
/// shortcut to executescalar with sql statement embedded params and object[] param values
/// </summary>
/// <param name="connectionstring">sqlite connection string</param>
/// <param name="commandtext">sql statment with embedded "@param" style parameters</param>
/// <param name="paramlist">object[] array of param values</param>
/// <returns></returns>
public static object executescalar(string connectionstring, string commandtext, params object[] paramlist)
{
sqliteconnection cn = new sqliteconnection(connectionstring);
sqlitecommand cmd = cn.createcommand();
cmd.commandtext = commandtext;
attachparameters(cmd,commandtext, paramlist);
if (cn.state == connectionstate.closed)
cn.open();
object result = cmd.executescalar();
cmd.dispose();
cn.close();
return result;
}
/// <summary>
/// execute xmlreader with complete command
/// </summary>
/// <param name="command">sqlite command</param>
/// <returns>xmlreader</returns>
public static xmlreader executexmlreader(idbcommand command)
{ // open the connection if necessary, but make sure we
// know to close it when we�re done.
if (command.connection.state != connectionstate.open)
{
command.connection.open();
}
// get a data adapter
sqlitedataadapter da = new sqlitedataadapter((sqlitecommand)command);
dataset ds = new dataset();
// fill the data set, and return the schema information
da.missingschemaaction = missingschemaaction.addwithkey;
da.fill(ds);
// convert our dataset to xml
stringreader stream = new stringreader(ds.getxml());
command.connection.close();
// convert our stream of text to an xmlreader
return new xmltextreader(stream);
}
/// <summary>
/// parses parameter names from sql statement, assigns values from object array , /// and returns fully populated parametercollection.
/// </summary>
/// <param name="commandtext">sql statement with "@param" style embedded parameters</param>
/// <param name="paramlist">object[] array of parameter values</param>
/// <returns>sqliteparametercollection</returns>
/// <remarks>status experimental. regex appears to be handling most issues. note that parameter object array must be in same ///order as parameter names appear in sql statement.</remarks>
private static sqliteparametercollection attachparameters(sqlitecommand cmd, string commandtext, params object[] paramlist)
{
if (paramlist == null || paramlist.length == 0) return null;
sqliteparametercollection coll = cmd.parameters;
string parmstring = commandtext.substring(commandtext.indexof("@"));
// pre-process the string so always at least 1 space after a comma.
parmstring = parmstring.replace(",", " ,");
// get the named parameters into a match collection
string pattern = @"(@)\s*(.*?)\b";
regex ex = new regex(pattern, regexoptions.ignorecase);
matchcollection mc = ex.matches(parmstring);
string[] paramnames = new string[mc.count];
int i = 0;
foreach (match m in mc)
{
paramnames[i] = m.value;
i++;
}
// now let's type the parameters
int j = 0;
type t = null;
foreach (object o in paramlist)
{
t = o.gettype();
sqliteparameter parm = new sqliteparameter();
switch (t.tostring())
{
case ("dbnull"):
case ("char"):
case ("sbyte"):
case ("uint16"):
case ("uint32"):
case ("uint64"):
throw new systemexception("invalid data type");
case ("system.string"):
parm.dbtype = dbtype.string;
parm.parametername = paramnames[j];
parm.value = (string)paramlist[j];
coll.add(parm);
break;
case ("system.byte[]"):
parm.dbtype = dbtype.binary;
parm.parametername = paramnames[j];
parm.value = (byte[])paramlist[j];
coll.add(parm);
break;
case ("system.int32"):
parm.dbtype = dbtype.int32;
parm.parametername = paramnames[j];
parm.value = (int)paramlist[j];
coll.add(parm);
break;
case ("system.boolean"):
parm.dbtype = dbtype.boolean;
parm.parametername = paramnames[j];
parm.value = (bool)paramlist[j];
coll.add(parm);
break;
case ("system.datetime"):
parm.dbtype = dbtype.datetime;
parm.parametername = paramnames[j];
parm.value = convert.todatetime(paramlist[j]);
coll.add(parm);
break;
case ("system.double"):
parm.dbtype = dbtype.double;
parm.parametername = paramnames[j];
parm.value = convert.todouble(paramlist[j]);
coll.add(parm);
break;
case ("system.decimal"):
parm.dbtype = dbtype.decimal;
parm.parametername = paramnames[j];
parm.value = convert.todecimal(paramlist[j]);
break;
case ("system.guid"):
parm.dbtype = dbtype.guid;
parm.parametername = paramnames[j];
parm.value = (system.guid)(paramlist[j]);
break;
case ("system.object"):
parm.dbtype = dbtype.object;
parm.parametername = paramnames[j];
parm.value = paramlist[j];
coll.add(parm);
break;
default:
throw new systemexception("value is of unknown data type");
} // end switch
j++;
}
return coll;
}
/// <summary>
/// executes non query typed params from a datarow
/// </summary>
/// <param name="command">command.</param>
/// <param name="datarow">data row.</param>
/// <returns>integer result code</returns>
public static int executenonquerytypedparams(idbcommand command, datarow datarow)
{
int retval = 0;
// if the row has values, the store procedure parameters must be initialized
if (datarow != null && datarow.itemarray.length > 0)
{
// set the parameters values
assignparametervalues(command.parameters, datarow);
retval = executenonquery(command);
}
else
{
retval = executenonquery(command);
}
return retval;
}
/// <summary>
/// this method assigns datarow column values to an idataparametercollection
/// </summary>
/// <param name="commandparameters">the idataparametercollection to be assigned values</param>
/// <param name="datarow">the datarow used to hold the command's parameter values</param>
/// <exception cref="system.invalidoperationexception">thrown if any of the parameter names are invalid.</exception>
protected internal static void assignparametervalues(idataparametercollection commandparameters, datarow datarow)
{
if (commandparameters == null || datarow == null)
{
// do nothing if we get no data
return;
}
datacolumncollection columns = datarow.table.columns;
int i = 0;
// set the parameters values
foreach (idataparameter commandparameter in commandparameters)
{
// check the parameter name
if (commandparameter.parametername == null ||
commandparameter.parametername.length <= 1)
throw new invalidoperationexception(string.format(
"please provide a valid parameter name on the parameter #{0}, the parametername property has the following value: '{1}'.",
i, commandparameter.parametername));
if (columns.contains(commandparameter.parametername))
commandparameter.value = datarow[commandparameter.parametername];
else if (columns.contains(commandparameter.parametername.substring(1)))
commandparameter.value = datarow[commandparameter.parametername.substring(1)];
i++;
}
}
/// <summary>
/// this method assigns datarow column values to an array of idataparameters
/// </summary>
/// <param name="commandparameters">array of idataparameters to be assigned values</param>
/// <param name="datarow">the datarow used to hold the stored procedure's parameter values</param>
/// <exception cref="system.invalidoperationexception">thrown if any of the parameter names are invalid.</exception>
protected void assignparametervalues(idataparameter[] commandparameters, datarow datarow)
{
if ((commandparameters == null) || (datarow == null))
{
// do nothing if we get no data
return;
}
datacolumncollection columns = datarow.table.columns;
int i = 0;
// set the parameters values
foreach (idataparameter commandparameter in commandparameters)
{
// check the parameter name
if (commandparameter.parametername == null ||
commandparameter.parametername.length <= 1)
throw new invalidoperationexception(string.format(
"please provide a valid parameter name on the parameter #{0}, the parametername property has the following value: '{1}'.",
i, commandparameter.parametername));
if (columns.contains(commandparameter.parametername))
commandparameter.value = datarow[commandparameter.parametername];
else if (columns.contains(commandparameter.parametername.substring(1)))
commandparameter.value = datarow[commandparameter.parametername.substring(1)];
i++;
}
}
/// <summary>
/// this method assigns an array of values to an array of idataparameters
/// </summary>
/// <param name="commandparameters">array of idataparameters to be assigned values</param>
/// <param name="parametervalues">array of objects holding the values to be assigned</param>
/// <exception cref="system.argumentexception">thrown if an incorrect number of parameters are passed.</exception>
protected void assignparametervalues(idataparameter[] commandparameters, params object[] parametervalues)
{
if ((commandparameters == null) || (parametervalues == null))
{
// do nothing if we get no data
return;
}
// we must have the same number of values as we pave parameters to put them in
if (commandparameters.length != parametervalues.length)
{
throw new argumentexception("parameter count does not match parameter value count.");
}
// iterate through the idataparameters, assigning the values from the corresponding position in the
// value array
for (int i = 0, j = commandparameters.length, k = 0; i < j; i++)
{
if (commandparameters[i].direction != parameterdirection.returnvalue)
{
// if the current array value derives from idataparameter, then assign its value property
if (parametervalues[k] is idataparameter)
{
idataparameter paraminstance;
paraminstance = (idataparameter)parametervalues[k];
if (paraminstance.direction == parameterdirection.returnvalue)
{
paraminstance = (idataparameter)parametervalues[++k];
}
if (paraminstance.value == null)
{
commandparameters[i].value = dbnull.value;
}
else
{
commandparameters[i].value = paraminstance.value;
}
}
else if (parametervalues[k] == null)
{
commandparameters[i].value = dbnull.value;
}
else
{
commandparameters[i].value = parametervalues[k];
}
k++;
}
}
}
}
}
希望本文所述对大家的c#程序设计有所帮助。
更多c#基于sqlitehelper类似sqlhelper类实现存取sqlite数据库的方法。