C#基于SQLiteHelper类似SqlHelper类实现存取Sqlite数据库的方法

本文实例讲述了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#程序设计有所帮助。