安装System.Data.SQLite.Core库，The official SQLite database engine for both x86 and x64 along with the ADO.NET provider.
1. 获取SQLite 数据库的版本
private void btnQueryDbVersion_Click(object sender, RoutedEventArgs e)
{
string db = "Data Source=:memory:";
string stm = "SELECT SQLITE_VERSION()";
using (var con = new SQLiteConnection(db))
{
con.Open();
using (var cmd = new SQLiteCommand(stm, con))
{
var version = cmd.ExecuteScalar().ToString();
txtMessage.Text = $"SQLite version: {version}";
}
}
}
2. 创建表
private string myDb = @"URI=file:SDDb";
static bool CreateTable(string db)
{
try
{
using (var con = new SQLiteConnection(db))
{
con.Open();
using (var cmd = new SQLiteCommand(con))
{
cmd.CommandText = "DROP TABLE IF EXISTS PrintDataInfo";
cmd.ExecuteNonQuery();
cmd.CommandText = @"CREATE TABLE PrintDataInfo(id INTEGER PRIMARY KEY, FullSn TEXT, MeterId INT,OrderNr TEXT,TestDate DATETIME)";
cmd.ExecuteNonQuery();
return true;
}
}
}
catch (Exception)
{
return false;
}
}
private void btnCreateTable_Click(object sender, RoutedEventArgs e)
{
if (CreateTable(myDb))
{
txtMessage.Text = "数据表创建成功";
}
else
{
txtMessage.Text = "数据表创建失败";
}
}
3.插入数据
static bool InsertData(string db, string fullSn, int meterId, string orderNr)
{
try
{
using (var con = new SQLiteConnection(db))
{
con.Open();
using (var cmd = new SQLiteCommand(con))
{
cmd.CommandText = "INSERT INTO PrintDataInfo(FullSn,MeterId, OrderNr,TestDate) VALUES(@sn, @id,@orderNr,@dt)";
cmd.Parameters.AddWithValue("@sn", fullSn);
cmd.Parameters.AddWithValue("@id", meterId);
cmd.Parameters.AddWithValue("@orderNr", orderNr);
cmd.Parameters.AddWithValue("@dt", DateTime.Now);
cmd.Prepare();
int result = cmd.ExecuteNonQuery();
if (result == 1)
{
return true;
}
else
{
return false;
}
}
}
}
catch (Exception)
{
return false;
}
}
private void btnInsertData_Click(object sender, RoutedEventArgs e)
{
if (InsertData(myDb, txtFullSN.Text, Convert.ToInt32(txtMeterID.Text), txtOrderNr.Text))
{
txtMessage.Text = "插入数据成功";
}
else
{
txtMessage.Text = "插入数据失败";
}
}
4. 查询数据
static List<string> ReadData(string db)
{
List<string> listData = new List<string>();
using (var con = new SQLiteConnection(db))
{
con.Open();
string stm = "SELECT * FROM PrintDataInfo LIMIT 5";
using (var cmd = new SQLiteCommand(stm, con))
{
using (SQLiteDataReader rdr = cmd.ExecuteReader())
{
// print column headers with the data from a database table.
Console.WriteLine($"{rdr.GetName(0),-3} {rdr.GetName(1),-8} {rdr.GetName(2),8}");
listData.Add($"{rdr.GetName(0)}\t {rdr.GetName(1)}\t\t\t {rdr.GetName(2)}\t {rdr.GetName(3)} \t {rdr.GetName(4)}");
while (rdr.Read())
{
//Console.WriteLine($"{rdr.GetInt32(0)} {rdr.GetString(1)} {rdr.GetInt32(2)} {rdr.GetString(3)}");
listData.Add($"{rdr.GetInt32(0)} \t{rdr.GetString(1)}\t {rdr.GetInt32(2)}\t{rdr.GetString(3)} \t{rdr.GetDateTime(4)}");
}
}
}
}
return listData;
}
private void btnQueryData_Click(object sender, RoutedEventArgs e)
{
List<string> datas = ReadData(myDb);
foreach (var item in datas)
{
txtMessage.AppendText(item + "\r\n");
}
}
5.更新数据
static bool UpdateDataById(string db, int meterId, string Sn, string OrderNr)
{
using (var con = new SQLiteConnection(db))
{
con.Open();
using (var cmd = new SQLiteCommand(con))
{
//FullSn,MeterId, OrderNr
cmd.CommandText = "UPDATE PrintDataInfo SET FullSn=@sn,OrderNr=@orderNr WHERE MeterId=@id";
cmd.Parameters.AddWithValue("@sn", Sn);
cmd.Parameters.AddWithValue("@id", meterId);
cmd.Parameters.AddWithValue("@orderNr", OrderNr);
cmd.Prepare();
int r = cmd.ExecuteNonQuery();
if (r == 1)
{
return true;
}
else
{
return false;
}
}
}
}
private void btnUpdateData_Click(object sender, RoutedEventArgs e)
{
if (UpdateDataById(myDb, Convert.ToInt32(txtMeterID.Text), txtFullSN.Text, txtOrderNr.Text))
{
txtMessage.Text = "更新OK";
}
else
{
txtMessage.Text = "更新NG";
}
}
更新数据前：
更新后：
