C# SQLiteHelper类.docx
《C# SQLiteHelper类.docx》由会员分享，可在线阅读，更多相关《C# SQLiteHelper类.docx（20页珍藏版）》请在冰豆网上搜索。
C#SQLiteHelper类
C#SQLiteHelper类似SqlHelper类实现存取Sqlite数据库
这个类不是我实现的，原文在这里，我修改了原文中分析sql语句参数的方法，将方法名修改为AttachParameters，将其修饰符修改为private，并直接传递command到这个方法，直接绑定参数到comand。
修改后的代码如下
SQLiteHelper实现
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
{
///
/// SQLiteHelper is a utility class similar to "SQLHelper" in MS
/// Data Access Application Block and follows similar pattern.
///
public class SQLiteHelper
{
///
/// Creates a new instance. The ctor is marked private since all members are static.
///
private SQLiteHelper（）
{
}
///
/// Creates the command.
///
/// Connection.
/// Command text.
/// Command parameters.
/// SQLite Command
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;
}
///
/// Creates the command.
///
/// Connection string.
/// Command text.
/// Command parameters.
/// SQLite Command
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;
}
///
/// Creates the parameter.
///
/// Name of the parameter.
/// Parameter type.
/// Parameter value.
/// SQLiteParameter
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;
}
///
/// Shortcut method to execute dataset from SQL Statement and object[] arrray of parameter values
///
/// SQLite Connection string
/// SQL Statement with embedded "@param" style parameter names
/// object[] array of parameter values
///
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;
}
///
/// Shortcut method to execute dataset from SQL Statement and object[] arrray of parameter values
///
/// Connection.
/// Command text.
/// Param list.
///
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;
}
///
/// Executes the dataset from a populated Command object.
///
/// Fully populated SQLiteCommand
/// DataSet
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;
}
///
/// Executes the dataset in a SQLite Transaction
///
/// SQLiteTransaction. Transaction consists of Connection, Transaction, /// and Command, all of which must be created prior to making this method call.
/// Command text.
/// Sqlite Command parameters.
/// DataSet
/// user must examine Transaction Object and handle transaction.connection .Close, etc.
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;
}
///
/// Executes the dataset with Transaction and object array of parameter values.
///
/// SQLiteTransaction. Transaction consists of Connection, Transaction, /// and Command, all of which must be created prior to making this method call.
/// Command text.
/// object[] array of parameter values.
/// DataSet
/// user must examine Transaction Object and handle transaction.connection .Close, etc.
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
///
/// Executes the respective command for each inserted, updated, or deleted row in the DataSet.
///
///
/// e.g.:
/// UpdateDataset（conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order"）;
///
/// A valid SQL statement to insert new records into the data source
/// A valid SQL statement to delete records from the data source
/// A valid SQL statement used to update records in the data source
/// The DataSet used to update the data source
/// The DataTable used
升级会员 