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