1.所要用到的dll:http://download.csdn.net/detail/pp880509/6930161
2.【连接sqlite的类】
public class SQLiteHelper
{//代码导读:|DataDirectory|
//对于用户计算机上的应用程序而言,|DataDirectory|变量代表的路径就是应用程序的.exe文件所在位置的路径。
//对于使用ClickOnce所部署的应用程序而言,|DataDirectory|变量所代表的路径则是ClickOnce所建立的特殊文件目录。
//对于Web应用程序而言,|DataDirectory|变量所代表的路径就是App_Date文件夹。
//也可以根据自己的需要设置|DataDirectory|的默认值,代 码:AppDomain.CurrentDomain.SetData("DataDirectory",newpath)
//public static readonly string SQLiteConnectionString = "Data Source=" + System.Windows.Forms.Application.StartupPath + "\\TaiSol.zhushipeng";
public static readonly string SQLiteConnectionString = "Data Source=|DataDirectory|\\weixin.db";
//public static readonly string SQLiteConnectionString = "Data Source=F:\\SourceCode\\CWVSweixin\\weixin.db";
//轻量级数据库SQLite的连接字符串写法:"Data Source=D:\database\test.s3db"
//轻量级数据库SQLite的加密后的连接字符串写法:"Data Source=D:"database\test.s3db;Version=3;Password=SQLite数据库密码;"
public SQLiteHelper()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/ <summary>
/ 执行多条SQL语句,实现数据库事务。
/ </summary>
/ <param name="SQLStringList">多条SQL语句</param>
//public static void ExecuteSqlTran(ArrayList SQLStringList)
//{
// using (SQLiteConnection conn = new SQLiteConnection(connectionString))
// {
// conn.Open();
// SQLiteCommand cmd = new SQLiteCommand();
// cmd.Connection = conn;
// SQLiteTransaction tx = conn.BeginTransaction();
// cmd.Transaction = tx;
// try
// {
// for (int n = 0; n < SQLStringList.Count; n++)
// {
// string strsql = SQLStringList[n].ToString();
// if (strsql.Trim().Length > 1)
// {
// cmd.CommandText = strsql;
// cmd.ExecuteNonQuery();
// }
// }
// tx.Commit();
// }
// catch (System.Data.SQLite.SQLiteException E)
// {
// tx.Rollback();
// throw new Exception(E.Message);
// }
// }
//}
#region ExecuteNonQuery
/// <summary>
/// 对连接执行Transact-SQL语句并返回受影响的行数
/// </summary>
/// <param name="commandText">SQL语句或存储过程名</param>
/// <param name="isProcedure">第一个参数是否为存储过程名,true为是,false为否</param>
/// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
/// <returns></returns>
public static int ExecuteNonQuery(string commandText, bool isProcedure, params SQLiteParameter[] paras)
{
SQLiteConnection con = new SQLiteConnection(SQLiteHelper.SQLiteConnectionString);
SQLiteCommand cmd = new SQLiteCommand(commandText, con);
if (isProcedure)
{
cmd.CommandType = CommandType.StoredProcedure;
}
else
{
cmd.CommandType = CommandType.Text;
}
cmd.Parameters.Clear();
if (paras != null)
{
foreach (SQLiteParameter para in paras)
{
cmd.Parameters.Add(para);
}
}
try
{
con.Open();
return cmd.ExecuteNonQuery();
}
finally
{
con.Close();
}
}
/// <summary>
/// 对连接执行Transact-SQL语句并返回受影响的行数
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
/// <returns></returns>
public static int ExecuteNonQuery(string commandText, params SQLiteParameter[] paras)
{
return ExecuteNonQuery(commandText, false, paras);
}
/// <summary>
/// 对连接执行Transact-SQL语句并返回受影响的行数
/// </summary>
/// <param name="trans">传递事务对象</param>
/// <param name="commandText">SQL语句或存储过程名</param>
/// <param name="isProcedure">第二个参数是否为存储过程名,true为是,false为否</param>
/// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
/// <returns></returns>
public static int ExecuteNonQuery(SQLiteTransaction trans, string commandText, bool isProcedure, params SQLiteParameter[] paras)
{
SQLiteConnection con = trans.Connection;
SQLiteCommand cmd = new SQLiteCommand(commandText, con);
if (isProcedure)
{
cmd.CommandType = CommandType.StoredProcedure;
}
else
{
cmd.CommandType = CommandType.Text;
}
cmd.Parameters.Clear();
foreach (SQLiteParameter para in paras)
{
cmd.Parameters.Add(para);
}
if (trans != null)
{
cmd.Transaction = trans;
}
try
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
return cmd.ExecuteNonQuery();
}
finally
{
if (trans == null)
{
con.Close();
}
}
}
/// <summary>
/// 对连接执行Transact-SQL语句并返回受影响的行数
/// </summary>
/// <param name="trans">传递事务对象</param>
/// <param name="commandText">SQL语句</param>
/// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
/// <returns></returns>
public static int ExecuteNonQuery(SQLiteTransaction trans, string commandText, params SQLiteParameter[] paras)
{
return ExecuteNonQuery(trans, commandText, false, paras);
}
#endregion
#region ExecuteQueryScalar
/// <summary>
/// 执行查询,并返回查询结果集中的第一行第一列,忽略其它行或列
/// </summary>
/// <param name="commandText">SQL语句或存储过程名</param>
/// <param name="isProcedure">第一个参数是否为存储过程名,true为是,false为否</param>
/// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
/// <returns></returns>
public static object ExecuteQueryScalar(string commandText, bool isProcedure, params SQLiteParameter[] paras)
{
SQLiteConnection con = new SQLiteConnection(SQLiteHelper.SQLiteConnectionString);
SQLiteCommand cmd = new SQLiteCommand(commandText, con);
if (isProcedure)
{
cmd.CommandType = CommandType.StoredProcedure;
}
else
{
cmd.CommandType = CommandType.Text;
}
foreach (SQLiteParameter para in paras)
{
cmd.Parameters.Add(para);
}
try
{
con.Open();
return cmd.ExecuteScalar();
}
finally
{
con.Close();
}
}
/// <summary>
/// 执行查询,并返回查询结果集中的第一行第一列,忽略其它行或列
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
/// <returns></returns>
public static object ExecuteQueryScalar(string commandText, params SQLiteParameter[] paras)
{
return ExecuteQueryScalar(commandText, false, paras);
}
/// <summary>
/// 执行查询,并返回查询结果集中的第一行第一列,忽略其它行或列
/// </summary>
/// <param name="trans">传递事务对象</param>
/// <param name="commandText">SQL语句或存储过程名</param>
/// <param name="isProcedure">第二个参数是否为存储过程名,true为是,false为否</param>
/// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
/// <returns></returns>
public static object ExecuteQueryScalar(SQLiteTransaction trans, string commandText, bool isProcedure, params SQLiteParameter[] paras)
{
SQLiteConnection con = trans.Connection;
SQLiteCommand cmd = new SQLiteCommand(commandText, con);
if (isProcedure)
{
cmd.CommandType = CommandType.StoredProcedure;
}
else
{
cmd.CommandType = CommandType.Text;
}
cmd.Parameters.Clear();
foreach (SQLiteParameter para in paras)
{
cmd.Parameters.Add(para);
}
if (trans != null)
{
cmd.Transaction = trans;
}
try
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
return cmd.ExecuteScalar();
}
finally
{
if (trans == null)
{
con.Close();
}
}
}
/// <summary>
/// 执行查询,并返回查询结果集中的第一行第一列,忽略其它行或列
/// </summary>
/// <param name="trans">传递事务对象</param>
/// <param name="commandText">SQL语句</param>
/// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
/// <returns></returns>
public static object ExecuteQueryScalar(SQLiteTransaction trans, string commandText, params SQLiteParameter[] paras)
{
return ExecuteQueryScalar(trans, commandText, false, paras);
}
#endregion
#region ExecuteDataReader
/// <summary>
/// 执行SQL,并返回结果集的只前进数据读取器
/// </summary>
/// <param name="commandText">SQL语句或存储过程名</param>
/// <param name="isProcedure">第一个参数是否为存储过程名,true为是,false为否</param>
/// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
/// <returns></returns>
public static SQLiteDataReader ExecuteDataReader(string commandText, bool isProcedure, params SQLiteParameter[] paras)
{
SQLiteConnection con = new SQLiteConnection(SQLiteHelper.SQLiteConnectionString);
SQLiteCommand cmd = new SQLiteCommand(commandText, con);
if (isProcedure)
{
cmd.CommandType = CommandType.StoredProcedure;
}
else
{
cmd.CommandType = CommandType.Text;
}
if (paras != null)
{
foreach (SQLiteParameter para in paras)
{
cmd.Parameters.Add(para);
}
}
try
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
{
con.Close();
throw;
}
}
/// <summary>
/// 执行SQL,并返回结果集的只前进数据读取器
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
/// <returns></returns>
public static SQLiteDataReader ExecuteDataReader(string commandText, params SQLiteParameter[] paras)
{
return ExecuteDataReader(commandText, false, paras);
}
/// <summary>
/// 执行SQL,并返回结果集的只前进数据读取器
/// </summary>
/// <param name="trans">传递事务对象</param>
/// <param name="commandText">SQL语句或存储过程名</param>
/// <param name="isProcedure">第二个参数是否为存储过程名,true为是,false为否</param>
/// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
/// <returns></returns>
public static SQLiteDataReader ExecuteDataReader(SQLiteTransaction trans, string commandText, bool isProcedure, params SQLiteParameter[] paras)
{
SQLiteConnection con = trans.Connection;
SQLiteCommand cmd = new SQLiteCommand(commandText, con);
if (isProcedure)
{
cmd.CommandType = CommandType.StoredProcedure;
}
else
{
cmd.CommandType = CommandType.Text;
}
cmd.Parameters.Clear();
foreach (SQLiteParameter para in paras)
{
cmd.Parameters.Add(para);
}
if (trans != null)
{
cmd.Transaction = trans;
}
try
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
{
if (trans == null)
{
con.Close();
}
throw;
}
}
/// <summary>
/// 执行SQL,并返回结果集的只前进数据读取器
/// </summary>
/// <param name="trans">传递事务对象</param>
/// <param name="commandText">SQL语句</param>
/// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
/// <returns></returns>
public static SQLiteDataReader ExecuteDataReader(SQLiteTransaction trans, string commandText, params SQLiteParameter[] paras)
{
return ExecuteDataReader(trans, commandText, false, paras);
}
#endregion
#region ExecuteDataSet
/// <summary>
/// 执行SQL,并返回DataSet结果集
/// </summary>
/// <param name="commandText">SQL语句或存储过程名</param>
/// <param name="isProcedure">第一个参数是否为存储过程名,true为是,false为否</param>
/// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
/// <returns></returns>
public static DataSet ExecuteDataSet(string commandText, bool isProcedure, params SQLiteParameter[] paras)
{
SQLiteConnection con = new SQLiteConnection(SQLiteHelper.SQLiteConnectionString);
SQLiteCommand cmd = new SQLiteCommand(commandText, con);
if (isProcedure)
{
cmd.CommandType = CommandType.StoredProcedure;
}
else
{
cmd.CommandType = CommandType.Text;
}
if (paras != null)
{
foreach (SQLiteParameter para in paras)
{
cmd.Parameters.Add(para);
}
}
try
{
con.Open();
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
return ds;
}
finally
{
con.Close();
}
}
/// <summary>
/// 执行SQL,并返回DataSet结果集
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
/// <returns></returns>
public static DataSet ExecuteDataSet(string commandText, params SQLiteParameter[] paras)
{
return ExecuteDataSet(commandText, false, paras);
}
/// <summary>
/// 执行SQL,并返回DataSet结果集
/// </summary>
/// <param name="trans">传递事务对象</param>
/// <param name="commandText">SQL语句或存储过程名</param>
/// <param name="isProcedure">第二个参数是否为存储过程名,true为是,false为否</param>
/// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
/// <returns></returns>
public static DataSet ExecuteDataSet(SQLiteTransaction trans, string commandText, bool isProcedure, params SQLiteParameter[] paras)
{
SQLiteConnection con = trans.Connection;
SQLiteCommand cmd = new SQLiteCommand(commandText, con);
if (isProcedure)
{
cmd.CommandType = CommandType.StoredProcedure;
}
else
{
cmd.CommandType = CommandType.Text;
}
cmd.Parameters.Clear();
foreach (SQLiteParameter para in paras)
{
cmd.Parameters.Add(para);
}
if (trans != null)
{
cmd.Transaction = trans;
}
try
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
return ds;
}
finally
{
if (trans == null)
{
con.Close();
}
}
}
/// <summary>
/// 执行SQL,并返回DataSet结果集
/// </summary>
/// <param name="trans">传递事务对象</param>
/// <param name="commandText">SQL语句</param>
/// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
/// <returns></returns>
public static DataSet ExecuteDataSet(SQLiteTransaction trans, string commandText, params SQLiteParameter[] paras)
{
return ExecuteDataSet(trans, commandText, false, paras);
}
#endregion
}