第一步：使用Nuget安装System.Data.SQLite
第二步：使用SqlLite可视化工具创建数据库、表--略
SqliteHelper==》
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
namespace DBHelper
{
public class SqliteHelper
{
//private string ConStr = "Data Source=C:\\ZDPACS_DJ\\DB\\SinglePacs.sqlite";
//string conStr = "Data Source=D:\\SinglePacs.sqlite";
public static string ConStr { get; set; }
//定义一个用于保存静态变量的实例
private static SqliteHelper instance = null;
//定义一个保证线程同步的标识
private static readonly object locker = new object();
//构造函数为私有，使外界不能创建该类的实例
private SqliteHelper() { }
public static SqliteHelper Instance(string conStr)
{
if (instance == null)
{
lock (locker)
{
if (instance == null)
{
instance = new SqliteHelper();
ConStr = conStr;
}
}
}
return instance;
}
/// <summary>
/// 执行非查询的sql语句，返回受影响的行数
/// </summary>
/// <param name="cmdText"></param>
/// <param name="paramters"></param>
/// <returns></returns>
public int ExecuteNonQuery(string cmdText, params SQLiteParameter[] paramters)
{
using (SQLiteConnection con = new SQLiteConnection(ConStr))
{
try
{
con.Open();
using (SQLiteCommand cmd = new SQLiteCommand(con))
{
cmd.CommandText = cmdText;
if (paramters != null)
{
//SQLiteParameter myParameter = new SQLiteParameter("@content", DbType.String);
//myParameter.Value = paramters;
//cmd.Parameters.Add(paramters);
//多个参数
foreach (SQLiteParameter sp in paramters)
{
cmd.Parameters.Add(sp);
}
}
return cmd.ExecuteNonQuery();
}
}
catch (SQLiteException ex)
{
//_log.E(ex);
}
return -1;
}
}
/// <summary>
/// 执行非查询的sql语句，返回第一行第一列的值
/// </summary>
/// <param name="cmdText"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public object ExecuteScalar(string cmdText, params SQLiteParameter[] parameters)
{
using (SQLiteConnection conn = new SQLiteConnection(ConStr))
{
try
{
conn.Open();
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteScalar();
}
}
catch (SQLiteException ex)
{
//_log.E(ex);
}
return null;
}
}
/// <summary>
/// 执行查询语句，返回查询到的结果
/// </summary>
/// <param name="cmdText"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public DataTable GetDataTable(string cmdText, params SQLiteParameter[] parameters)
{
using (SQLiteConnection conn = new SQLiteConnection(ConStr))
{
try
{
conn.Open();
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
DataTable dt = new DataTable();
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(parameters);
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
da.Fill(dt);
return dt;
}
}
catch (SQLiteException ex)
{
//_log.E(ex);
}
return null;
}
}
/// <summary>
/// 执行查询语句，返回查询到的结果
/// </summary>
/// <param name="cmdText"></param>
/// <returns></returns>
public DataTable GetDataTableNoParam(string cmdText)
{
using (SQLiteConnection conn = new SQLiteConnection(ConStr))
{
try
{
conn.Open();
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
DataTable dt = new DataTable();
cmd.CommandText = cmdText;
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
da.Fill(dt);
return dt;
}
}
catch (SQLiteException ex)
{
//_log.E(ex);
}
return null;
}
}
/// <summary>
/// 执行事务，如果出现异常则回滚
/// </summary>
/// <param name="models"></param>
/// <returns></returns>
public bool ExecTransaction(List<TransModel> models)
{
using (SQLiteConnection con = new SQLiteConnection(ConStr))
{
try
{
con.Open();
using (SQLiteTransaction trans = con.BeginTransaction())
{
using (SQLiteCommand cmd = new SQLiteCommand(con))
{
cmd.Transaction = trans;
try
{
foreach (var model in models)
{
cmd.CommandText = model.CmdText;
if (model.Paras != null)
cmd.Parameters.AddRange(model.Paras);
cmd.ExecuteNonQuery();
}
trans.Commit();
return true;
}
catch (SQLiteException ex)
{
trans.Rollback();
//_log.E(ex);
}
}
}
}
catch (SQLiteException ex)
{
// _log.E(ex);
}
}
return false;
}
}
public class TransModel
{
public string CmdText { get; set; }
public SQLiteParameter[] Paras { get; set; }
}
}
应用示例：
using DBHelper;
using System;
using System.Windows.Forms;
namespace SqlLiteDemo
{
public partial class SqlLiteForm : Form
{
private string ConnString = "Data Source = " + Application.StartupPath + "\\SqlLiteDemo.db3";
//string conStr = "Data Source=D:\\SinglePacs.sqlite";
public SqlLiteForm()
{
InitializeComponent();
}
private void SqlLiteForm_Load(object sender, EventArgs e)
{
this.dgvList.AutoGenerateColumns = false;
string sql = "select * from UserInfo";
//var list = SqliteHelper.Instance(ConnString).GetDataTableNoParam(sql);
var list = SqliteHelper.Instance(ConnString).GetDataTable(sql);
this.dgvList.DataSource = list;
}
private void btnAdd_Click(object sender, EventArgs e)
{
try
{
string sql = @"insert into [UserInfo] ([UserId],[UserName],[PassWord],[NickName]) values ("
+ "'" + this.txtUserId.Text + "',"
+ "'" + this.txtUserName.Text + "',"
+ "'" + this.txtPwd.Text + "',"
+ "'" + this.txtNickName.Text + "')";
int count = SqliteHelper.Instance(ConnString).ExecuteNonQuery(sql);
MessageBox.Show("新增成功：" + count);
//UserId,UserName,PassWord,NickName
}
catch (Exception ex)
{
}
}
private void btnDel_Click(object sender, EventArgs e)
{
string sql = "delete from UserInfo where UserId=='1'";
int count = SqliteHelper.Instance(ConnString).ExecuteNonQuery(sql);
MessageBox.Show("删除成功:" + count);
}
private void btnUpdate_Click(object sender, EventArgs e)
{
string sql = string.Format(@"update [UserInfo] set [PassWord] = '001' where [PassWord] ='{0}'", 1);
int count = SqliteHelper.Instance(ConnString).ExecuteNonQuery(sql);
MessageBox.Show("修改成功：" + count);
}
private void btnQuery_Click(object sender, EventArgs e)
{
string sql = "select * from UserInfo";
var list = SqliteHelper.Instance(ConnString).GetDataTableNoParam(sql);
this.dgvList.DataSource = list;
}
}
}