[WPF]C#连接使用sqllite数据库,,支持多线程操作
版权声明：
本文为博主原创文章，转载请声明原文链接...谢谢。o_0。
更新时间：
2017-08-24 11:32:00
温馨提示：
学无止境,技术类文章有它的时效性,请留意文章更新时间,如发现内容有误请留言指出,防止别人"踩坑",我会及时更新文章
项目开发环境
win764
vs2015
.net4.6
下载 
System.Data.SQLite.dll-for-.net4.0.zip 引用到项目中
使用sqllite扩展的时候,项目编译成 Any cpu 的话就要引用sqllite对应的32和64位 SQLite.Interop.dll 直接把压缩包解压下来
引用 System.Data.SQLite.dll 这个,然后把上面两个文件夹放到程序运行目录中,运行的时候 System.Data.SQLite.dll 会自动根据运行环境选译32或64位目录进行加载
下面提供一个操作sqllite的类方便使用
里面用到啦一个记录日志的Log类,可以到下面地址下载
https://www.zhaokeli.com/article/8180.html
MySQLite lite = new MySQLite("./data/mysqllite");
//创建一个表
var r0 = lite.query("create table article (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL DEFAULT NULL,name varchar(20), sex varchar(10))");
//插入一些数据
for (int i = 0; i < 10; i++)
{
Console.WriteLine(lite.insert("article", new Dictionary<string, string>() {
{"name","张三"+(new Random().Next()) },
{"sex","男1"+(new Random().Next()) }
}));
}
//查询出来
var datalist = lite.select("article");
foreach (var item in datalist)
{
Console.WriteLine(item["name"]);
Console.WriteLine(item["sex"]);
}
var re = lite.update("article", new Dictionary<string, string>() {
{"name","修改1"+(new Random().Next()) },
{"sex","修改2"+(new Random().Next()) }
}, "id=3");如图
初始化的时候需要提供数据库的路径,如果不存在会自动创建一个空数据库
MySQLite.cs
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Threading;
using System.Windows;
namespace Ank.Class
{
class MySQLite
{
private SQLiteConnection m_conn;
private string connstr = "";
private string _sql = "";
private Log m_log = null;
private SQLiteCommand m_cmd = null;
private SQLiteDataReader m_reader = null;
private Mutex m_mutex = null;//多线程时互斥锁
//数据库连接
public MySQLite(string databasepath = null)
{
try
{
if (databasepath == null)
{
MessageBox.Show("亲,数据库路径弄哪啦?");
Environment.Exit(0);
return;
}
var pat = Path.GetDirectoryName(databasepath);
if (Directory.Exists(pat) == false)
{
Directory.CreateDirectory(pat);
}
if (File.Exists(databasepath) == false)
{
SQLiteConnection.CreateFile(databasepath);
}
m_conn = new SQLiteConnection("Data Source=" + databasepath + ";Version=3;");
m_conn.Open();
m_mutex = new Mutex();
}
catch (Exception ex)
{
this.errorMsg(ex);
}
}
/**
* 取数据表主键
**/
public string getPrimary(string tablename = "")
{
string key = "";
try
{
m_cmd = new SQLiteCommand("PRAGMA table_info( " + tablename + ")", m_conn);
m_reader = m_cmd.ExecuteReader();
//如果有数据就输出
if (m_reader.HasRows)
{
//逐行读取数据输出
while (m_reader.Read())
{
string prid = m_reader["pk"].ToString();
if (prid == "1")
{
key = m_reader["name"].ToString();
break;
}
}
}
}
catch (Exception ex)
{
this.errorMsg(ex);
}
finally
{
this.closeHandle();
}
return key;
}
public int query(string sql = "")
{
//安全时才可以访问共享资源，否则挂起。检测到安全并访问的同时会上锁。
m_mutex.WaitOne();
this.reConn();
try
{
if (sql != null)
{
this._sql = sql;
SQLiteCommand command = new SQLiteCommand(this._sql, m_conn);
return command.ExecuteNonQuery();
}
else
{
return 0;
}
}
catch (Exception ex)
{
this.errorMsg(ex);
}
finally
{
this.closeHandle();
m_mutex.ReleaseMutex();
}
return 0;
}
/**
* 插入数据成功返回自增id,没有自增id的返回1
**/
public int insert(string tablename, Dictionary<string, string> updatedata)
{
//安全时才可以访问共享资源，否则挂起。检测到安全并访问的同时会上锁。
m_mutex.WaitOne();
this.reConn();
try
{
m_cmd = m_conn.CreateCommand();
//取更新的所有键
string field1 = "";
string field2 = "";
foreach (string key in updatedata.Keys)
{
field1 += "," + key;
field2 += ",@" + key;
m_cmd.Parameters.AddWithValue("@" + key.ToLower(), updatedata[key]);
}
this._sql = "INSERT INTO " + tablename + " (" + field1.Trim(',') + ") VALUES (" + field2.Trim(',') + ")";
m_cmd.CommandText = this._sql;
int result = m_cmd.ExecuteNonQuery();
closeHandle();
if (result == 1)
{
string prid = this.getPrimary(tablename);
if (prid != "")
{
var data = this.get(tablename, prid, "", prid + " desc");
return int.Parse(data[prid]);
}
else
{
return 1;
}
}
}
catch (Exception ex)
{
this.errorMsg(ex);
this.closeHandle();
throw;
}
finally
{
m_mutex.ReleaseMutex();
}
return 0;
}
/**
* 插入数据
**/
public int update(string tablename, Dictionary<string, string> updatedata, string where = "")
{
//安全时才可以访问共享资源，否则挂起。检测到安全并访问的同时会上锁。
m_mutex.WaitOne();
this.reConn();
try
{
m_cmd = m_conn.CreateCommand();
//取更新的所有键
string field1 = "";
foreach (string key in updatedata.Keys)
{
field1 += "," + key + "=@" + key;
m_cmd.Parameters.AddWithValue("@" + key.ToLower(), updatedata[key]);
}
this._sql = "UPDATE " + tablename + " SET " + field1.Trim(',') + " WHERE " + where;
m_cmd.CommandText = this._sql;
return m_cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
this.errorMsg(ex);
}
finally
{
this.closeHandle();
m_mutex.ReleaseMutex();
}
return 0;
}
public List<Dictionary<string, string>> select(string tablename = "", string fields = "", string where = "", string order = "", string limit = "")
{
//安全时才可以访问共享资源，否则挂起。检测到安全并访问的同时会上锁。
m_mutex.WaitOne();
this.reConn();
List<Dictionary<string, string>> datalist = new List<Dictionary<string, string>>();
this._sql = "select " + (fields != "" ? fields : "*") + " from " + tablename + " " + (where != "" ? (" where " + where) : "") + " " + (order != "" ? (" order by " + order) : "") + (limit != "" ? (" limit " + limit) : "");
try
{
m_cmd = new SQLiteCommand(this._sql, m_conn);
m_reader = m_cmd.ExecuteReader();
//如果有数据就输出
if (m_reader.HasRows)
{
//逐行读取数据输出
while (m_reader.Read())
{
Dictionary<string, string> coldata = new Dictionary<string, string>();
//取所有field
for (int i = 0; i < m_reader.FieldCount; i++)
{
string fieldname = m_reader.GetName(i).Trim();
string value = m_reader.GetValue(i).ToString();
coldata.Add(fieldname, value);
}
datalist.Add(coldata);
}
}
}
catch (Exception ex)
{
this.errorMsg(ex);
}
finally
{
this.closeHandle();
m_mutex.ReleaseMutex();
}
return datalist;
}
/**
* 返回一条记录
* */
public Dictionary<string, string> get(string tablename = "", string fields = "", string where = "", string order = "")
{
var dict = this.select(tablename, fields, where, order, "1");
if (dict.Count > 0)
{
return dict[0];
}
else
{
return new Dictionary<string, string>();
}
}
public int count(string tablename = "", string where = "")
{
var dict = this.get(tablename, "", where);
return dict.Count;
}
public bool has(string tablename = "", string where = "")
{
var num = this.count(tablename, where);
if (num > 0)
{
return true;
}
else
{
return false;
}
}
~MySQLite()
{
try
{
if (m_conn.State == System.Data.ConnectionState.Open)
{
m_conn.Close();
m_conn.Dispose();
}
}
catch (Exception ex)
{
this.errorMsg(ex);
}
finally
{
this.closeHandle();
}
}
/************************************************************************/
/* 如果连接已经关闭就重新连接 */
/************************************************************************/
private void reConn()
{
try
{
//防止因网络或其它情况下连接断开时重新连接
if (m_conn.State == System.Data.ConnectionState.Closed)
{
m_conn = new SQLiteConnection(this.connstr);
m_conn.Open();
}
this.closeHandle();
}
catch (Exception ex)
{
this.errorMsg(ex);
}
}
private void errorMsg(Exception ex = null)
{
if (ex != null)
{
Console.WriteLine(ex.Message);
m_log.write(ex.Message, "litedb");
m_log.write(ex.ToString(), "litedb");
}
}
private void closeHandle()
{
try
{
if (m_cmd != null)
{
m_cmd.Dispose();
}
if (m_reader != null && m_reader.IsClosed == false)
{
m_reader.Close();
}
}
catch (Exception ex)
{
this.errorMsg(ex);
}
finally
{
m_cmd = null;
m_reader = null;
}
}
}
}
