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

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 > )

   {

    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 > )

   {

    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 == ) 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 == ) 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 = ;

   foreach (Match m in mc)

   {

    paramNames[i] = m.Value;

    i++;

   }

   // now let's type the parameters

   int j = ;

   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 = ;

   // If the row has values, the store procedure parameters must be initialized

   if (dataRow != null && dataRow.ItemArray.Length > )

   {

    // 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 = ;

   // Set the parameters values

   foreach (IDataParameter commandParameter in commandParameters)

   {

    // Check the parameter name

    if (commandParameter.ParameterName == null ||

     commandParameter.ParameterName.Length <= )

     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()))

     commandParameter.Value = dataRow[commandParameter.ParameterName.Substring()];

    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 = ;

   // Set the parameters values

   foreach (IDataParameter commandParameter in commandParameters)

   {

    // Check the parameter name

    if (commandParameter.ParameterName == null ||

     commandParameter.ParameterName.Length <= )

     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()))

     commandParameter.Value = dataRow[commandParameter.ParameterName.Substring()];

    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 = , j = commandParameters.Length, k = ; 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++;

    }

   }

  }

 }

}