1、在5月份的使用做一个小型管理软件,用到Sqlite, 最近几天又用到,因此整一个C# 上,Sqlite的帮助类
如下:
/// <summary>
/// SQLITE 帮助类
/// </summary>
class SQLiteHelper
{
private static SQLiteHelper mInstance = null;
private String mConnectdbPath = "Data Source=" + Environment.CurrentDirectory + "\\active.db";
private static Object mLockObject = new Object();
/// <summary>
/// 构造函数
/// </summary>
private SQLiteHelper()
{
}
/// <summary>
/// 初始化实例
/// </summary>
/// <returns></returns>
public static SQLiteHelper GetInstance()
{
if (mInstance == null)
{
lock (mLockObject)
{
mInstance = new SQLiteHelper();
}
}
return mInstance;
}
/// <summary>
/// 对SQLite数据库执行增删改操作,返回受影响的行数。
/// </summary>
/// <param name="sql">要执行的增删改的SQL语句</param>
/// <param name="parameters">执行增删改语句所需要的参数,参数必须以它们在SQL语句中的顺序为准</param>
/// <returns></returns>
public int ExecuteNonQuery(String sql, SQLiteParameter[] paramters)
{
int affectedRows = 0;
try
{
using (SQLiteConnection conn = new SQLiteConnection(mConnectdbPath))
{
conn.Open();
using (DbTransaction transaction = conn.BeginTransaction())
{
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
cmd.CommandText = sql;
if (paramters != null)
{
cmd.Parameters.AddRange(paramters);
}
affectedRows = cmd.ExecuteNonQuery();
}
transaction.Commit();
}
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
return affectedRows;
}
/// <summary>
/// 对SQLite数据库执行增删改操作,返回受影响的行数。
/// </summary>
/// <param name="sql">要执行的增删改的SQL语句</param>
/// <returns></returns>
public int ExecuteNonQuery(String sql)
{
int affectedRows = 0;
try
{
using (SQLiteConnection conn = new SQLiteConnection(mConnectdbPath))
{
conn.Open();
using (DbTransaction transaction = conn.BeginTransaction())
{
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText = sql;
affectedRows = cmd.ExecuteNonQuery();
}
transaction.Commit();
}
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
return affectedRows;
}
/// <summary>
/// 执行一个查询语句,返回一个包含查询结果的DataTable
/// </summary>
/// <param name="sql">要执行的查询语句</param>
/// <returns></returns>
public DataTable ExecuteQueryDataTable(String sql)
{
DataTable data = null;
try
{
using (SQLiteConnection conn = new SQLiteConnection(mConnectdbPath))
{
conn.Open();
using (DbTransaction transaction = conn.BeginTransaction())
{
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
data = new DataTable();
adapter.Fill(data);
}
transaction.Commit();
}
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
return data;
}
/*
public List<RecordJaveBean> ExecuteQuery(String sql)
{
try
{
mList.Clear();
using (SQLiteConnection conn = new SQLiteConnection(mConnectdbPath))
{
conn.Open();
SQLiteCommand cmdRead = new SQLiteCommand(sql, conn);
using (SQLiteDataReader reader = cmdRead.ExecuteReader())
{
while (reader.Read())
{
mList.Add(new RecordJaveBean(reader.GetString(1), reader.GetString(2), reader.GetString(3)));
}
}
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
return mList;
}
*/
public void ExecuteInsert(String sql)
{
try
{
using (SQLiteConnection conn = new SQLiteConnection(mConnectdbPath))
{
conn.Open();
SQLiteCommand cmdRead = new SQLiteCommand(sql, conn);
cmdRead.ExecuteReader();
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
public void ExecuteDelegate(String sql)
{
try
{
using (SQLiteConnection conn = new SQLiteConnection(mConnectdbPath))
{
conn.Open();
SQLiteCommand cmdRead = new SQLiteCommand(sql, conn);
cmdRead.ExecuteReader();
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
/// <summary>
/// 对SQLite数据库获取当家查询结果集的记录总数
/// </summary>
/// <param name="sql">要执行的增删改的SQL语句</param>
/// <returns></returns>
public int ExecuteGetResultTotalLine(String sql)
{
int lines = 0;
try
{
using (SQLiteConnection conn = new SQLiteConnection(mConnectdbPath))
{
conn.Open();
using (DbTransaction transaction = conn.BeginTransaction())
{
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText = sql;
lines = Convert.ToInt32(cmd.ExecuteScalar());
}
transaction.Commit();
}
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
return lines;
}
}