安装Nuget包
/// <summary>
/// 创建数据表字段类型
/// </summary>
public enum AttrsType
{
Self = BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Static | BindingFlags.Instance,
Default
}
using ChromeDefaultUI.Enums;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.SQLite;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
namespace ChromeDefaultUI.sqlite
{
public class SQLHelper
{
private static SQLHelper helper = new SQLHelper();
public static SQLHelper Instance
{
get
{
return helper;
}
}
private SQLHelper()
{
}
/// <summary>
/// 根据属性创建字典
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public static Dictionary<string, string> CreateObjectData(object obj, AttrsType attrsType)
{
System.Reflection.PropertyInfo[] propertyInfos = obj.GetType().GetProperties((BindingFlags)attrsType);
Dictionary<string, string> data = new Dictionary<string, string>();
foreach (var item in propertyInfos)
{
string name = item.Name;
string val = "";
if (item.GetValue(obj) != null)
{
val = item.GetValue(obj).ToString();
}
data.Add(name, val);
}
return data;
}
/// <summary>
/// 增加数据,如果没有表,则自动创建表
/// </summary>
/// <param name="dic">数据</param>
/// <param name="tableName"></param>
public int Insert(Dictionary<string, string> dic, Tables tableName)
{
int ins = 0;
string connStr = @"URI=file:data.db";
using (SQLiteConnection conn = new SQLiteConnection(connStr))
{
conn.Open();
CreateTable(dic, conn, tableName.ToString());
string key = "";
string val = "";
foreach (var item in dic.Keys)
{
key += (item + ",");
val += ("'" + dic[item] + "',");
}
key = key.Substring(0, key.Length - 1);
val = val.Substring(0, val.Length - 1);
string sql = "insert into " + tableName + " (" + key + ") values (" + val + ")";
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
ins = cmd.ExecuteNonQuery();
}
conn.Close();
}
return ins;
}
/// <summary>
/// 根据条件删除数据
/// </summary>
/// <param name="par">查找条件 null 或 size=0 则删除所有数据</param>
/// <param name="tableName"></param>
public int Delete(Dictionary<string, string> par, Tables tableName)
{
int del = 0;
string connStr = @"URI=file:data.db";
using (SQLiteConnection conn = new SQLiteConnection(connStr))
{
conn.Open();
string sqlPar = "";
if (par != null && par.Count > 0)
{
foreach (string item in par.Keys)
{
sqlPar += item + " = '" + par[item] + "' AND ";
}
sqlPar = sqlPar.Substring(0, sqlPar.Length - (" AND ".Length));
}
string sql = "Delete from " + tableName + (sqlPar.Length > 0 ? (" where " + sqlPar) : "");
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
del = cmd.ExecuteNonQuery();//exq !=0 则删除成功,exq==0则删除失败或没有此数据
}
conn.Close();
}
return del;
}
/// <summary>
/// 通过事务删除多条
/// </summary>
/// <param name="par"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public int DeleteTransaction(List<Dictionary<string, string>> parList, Tables tableName)
{
int del = 0;
string connStr = @"URI=file:data.db";
using (SQLiteConnection conn = new SQLiteConnection(connStr))
{
conn.Open();
SQLiteTransaction tr = conn.BeginTransaction();//事务开始
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
for (int i = 0; i < parList.Count; i++)
{
Dictionary<string, string> par = parList[i];
string sqlPar = "";
if (par != null && par.Count > 0)
{
foreach (string item in par.Keys)
{
sqlPar += item + " = '" + par[item] + "' AND ";
}
sqlPar = sqlPar.Substring(0, sqlPar.Length - " AND ".Length);
}
string sql = "Delete from " + tableName + (sqlPar.Length > 0 ? (" where " + sqlPar) : "");
cmd.CommandText = sql;
int d = cmd.ExecuteNonQuery();//exq !=0 则删除成功,exq==0则删除失败或没有此数据
if (d > 0)
{
del++;
}
}
tr.Commit();
}
conn.Close();
}
return del;
}
/// <summary>
/// 根据条件查询数据
/// </summary>
/// <param name="par">查找条件,null 则查询所有</param>
/// <param name="tableName"></param>
public int FindRows(Dictionary<string, string> par, Tables tableName)
{
int row = 0;
string connStr = @"URI=file:data.db";
using (SQLiteConnection conn = new SQLiteConnection(connStr))
{
conn.Open();
string finTab = "select * from sqlite_master where type = 'table' and name ='" + tableName + "'";
int tabCount = 0;
using (SQLiteCommand cmd = new SQLiteCommand(finTab, conn))
{
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
tabCount++;
}
}
}
}
if (tabCount > 0)
{
string sqlPar = "";
if (par != null && par.Count > 0)
{
foreach (string item in par.Keys)
{
sqlPar += item + " = '" + par[item] + "' AND ";
}
sqlPar = sqlPar.Substring(0, sqlPar.Length - (" AND ".Length));
}
string sql = "SELECT * FROM " + tableName + (sqlPar.Length > 0 ? (" WHERE " + sqlPar) : "");
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
row++;
}
}
}
}
}
conn.Close();
}
return row;
}
/// <summary>
/// 根据条件更新数据
/// </summary>
/// <param name="update">更新值</param>
/// <param name="condition">条件</param>
/// <param name="tableName">表名</param>
/// <returns></returns>
public int Update(Dictionary<string, string> update, Dictionary<string, string> condition, Tables tableName)
{
if (update == null || update.Count == 0 || condition == null || condition.Count == 0)
{
throw new Exception("条件不足");
}
int rowCount = 0;
string connStr = @"URI=file:data.db";
using (SQLiteConnection conn = new SQLiteConnection(connStr))
{
conn.Open();
string finTab = "select * from sqlite_master where type = 'table' and name ='" + tableName + "'";
int tabCount = 0;
using (SQLiteCommand cmd = new SQLiteCommand(finTab, conn))
{
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
tabCount++;
}
}
}
}
if (tabCount > 0)
{
string value = UpdateValue(update);
string cond = Condition(condition);
string sql = "Update " + tableName + " Set " + value + " Where " + cond;
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
rowCount = cmd.ExecuteNonQuery();
}
}
conn.Close();
}
return rowCount;
}
/// <summary>
/// 根据条件反射返回obj,条件为null时,查询所有
/// </summary>
/// <param name="par">查找条件</param>
/// <param name="tableName"></param>
/// <param name="type"></param>
/// <returns></returns>
public ObservableCollection<Dictionary<string, object>> FindToObj(Dictionary<string, string> par, Tables tableName)
{
ObservableCollection<Dictionary<string, object>> o = new ObservableCollection<Dictionary<string, object>>();
string connStr = @"URI=file:data.db";
using (SQLiteConnection conn = new SQLiteConnection(connStr))
{
conn.Open();
string sqlPar = "";
if (par != null && par.Count > 0)
{
foreach (string item in par.Keys)
{
sqlPar += item + " = '" + par[item] + "' AND ";
}
sqlPar = sqlPar.Substring(0, sqlPar.Length - (" AND ".Length));
}
string sql = "SELECT * FROM " + tableName + (sqlPar.Length > 0 ? (" WHERE " + sqlPar) : "");
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var vals = reader.GetValues();
Dictionary<string, object> dic = new Dictionary<string, object>();
foreach (var item in vals.AllKeys)
{
dic.Add(item, vals[item]);
}
o.Add(dic);
}
}
}
conn.Close();
}
return o;
}
/// <summary>
/// 创建表
/// </summary>
/// <param name="data"></param>
/// <param name="cnn"></param>
/// <param name="tableName"></param>
private void CreateTable(Dictionary<string, string> data, SQLiteConnection cnn, string tableName)
{
string v = "";
foreach (var item in data.Keys)
{
v += item;
v += " text,";
}
v = v.Substring(0, v.Length - 1);
string sql = "Create table IF NOT EXISTS " + tableName + " (Id integer primary key, " + v + ");";
SQLiteCommand cmd = new SQLiteCommand(sql, cnn);
cmd.ExecuteNonQuery();
}
/// <summary>
/// 返回条件语句
/// </summary>
/// <param name="condition"></param>
/// <returns></returns>
private static string Condition(Dictionary<string, string> condition)
{
string sqlPar = "";
if (condition != null && condition.Count > 0)
{
foreach (string item in condition.Keys)
{
sqlPar += item + " = '" + condition[item] + "' AND ";
}
sqlPar = sqlPar.Substring(0, sqlPar.Length - (" AND ".Length));
}
return sqlPar;
}
/// <summary>
/// 返回更新值语句
/// </summary>
/// <param name="condition"></param>
/// <returns></returns>
private static string UpdateValue(Dictionary<string, string> condition)
{
string sqlPar = "";
if (condition != null && condition.Count > 0)
{
foreach (string item in condition.Keys)
{
sqlPar += item + " = '" + condition[item] + "' , ";
}
sqlPar = sqlPar.Substring(0, sqlPar.Length - (" , ".Length));
}
return sqlPar;
}
}
}