public class SQLiteHelper
{
private static Lazy<SQLiteHelper> _instance = new Lazy<SQLiteHelper>(() => new SQLiteHelper());
public static SQLiteHelper Instance = _instance.Value;
private readonly object queryLockObj = new object();
public string dbPath;
public SQLiteConnection sqliteConn;
public string MinuteData = "DbDatas";
private SQLiteHelper()
{
dbPath = System.AppDomain.CurrentDomain.BaseDirectory + "data.db";
}
/// <summary>
/// 创建数据库
/// </summary>
/// <param name="DbFilePath"></param>
public bool CreateDataBaseFile()
{
try
{
if (!File.Exists(dbPath))
{
SQLiteConnection.CreateFile(dbPath);
}
sqliteConn = new SQLiteConnection("data source=" + System.AppDomain.CurrentDomain.BaseDirectory + "data.db");
return true;
}
catch (Exception ex)
{
throw new Exception("新建数据库文件" + dbPath + "失败：" + ex.Message);
}
}
/// <summary>
/// 判断表是否存在
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public bool TableExist(string table)
{
if (sqliteConn.State == ConnectionState.Closed) sqliteConn.Open();
SQLiteCommand mDbCmd = sqliteConn.CreateCommand();
mDbCmd.CommandText = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='" + table + "';";
int row = Convert.ToInt32(mDbCmd.ExecuteScalar());
sqliteConn.Close();
if (0 < row)
{
return true;
}
else
{
return false;
}
}
/// <summary>
/// 创建表
/// </summary>
/// <param name="dbPath">指定数据库文件</param>
/// <param name="tableName">表名称</param>
public void CreateTable(string table, List<string> Columns)
{
if (sqliteConn.State != System.Data.ConnectionState.Open) sqliteConn.Open();
string Column = "";
for (int i = 0; i < Columns.Count; i++)
{
Column += Columns[i] + ",";
}
Column = Column.Substring(0, Column.Length - 1);
var cmd = new SQLiteCommand
{
Connection = sqliteConn,
CommandText = " CREATE TABLE " + table + "(" + Column + ")"
};
cmd.ExecuteNonQuery();
sqliteConn.Close();
}
/// <summary>
/// 添加字段
/// </summary>
/// <param name="Colms"></param>
public void CreateColunm(string table, List<string> Colms)
{
if (sqliteConn.State != System.Data.ConnectionState.Open) sqliteConn.Open();
SQLiteCommand cmd = new SQLiteCommand();
var sql = "select sql from sqlite_master where tbl_name='" + table + "' and type='table';";
cmd.Connection = sqliteConn;
cmd.CommandText = sql;
var com = cmd.ExecuteScalar();
for (var i = 0; i < Colms.Count; i++)
{
try
{
if (!com.ToString().Contains(Colms[i]))
{
var sql2 = "alter table " + table + " add column " + Colms[i] + ";";
cmd.Connection = sqliteConn;
cmd.CommandText = sql2;
cmd.ExecuteScalar();
}
}
catch (Exception ex)
{
Log.Error("新增字段{0}失败" + ex.Message + Colms[i]);
}
}
sqliteConn.Close();
}
/// <summary>
/// 获取类的属性名称和类型
/// </summary>
/// <typeparam name="T">类</typeparam>
/// <param name="model"></param>
/// <returns></returns>
public List<string> GetColumns<T>(T model) where T : class
{
List<string> Columns = new List<string>();
System.Reflection.PropertyInfo[] properties = model.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
if (properties.Length <= 0)
{
throw new Exception("类属性长度为零");
}
foreach (System.Reflection.PropertyInfo item in properties)
{
string Name;
if (item.Name != "ID")
{
if (item.PropertyType.ToString().Contains("DateTime") || item.PropertyType.ToString().Contains("String"))
{
Name = item.Name + " " + " varchar(100) default NULL";
}
else
{
Name = item.Name + " " + item.PropertyType.ToString().Split('.').Last().Replace("]", "") + "(12, 4) default NULL";
}
}
else
{
Name = item.Name + " integer PRIMARY KEY autoincrement";
}
Columns.Add(Name);
}
return Columns;
}
/// <summary>
/// 获取类的属性名称和类型
/// </summary>
/// <typeparam name="T">类</typeparam>
/// <param name="model"></param>
/// <returns></returns>
public List<string> GetColumns(List<string> cols)
{
var Columns = new List<string>
{
"ID integer PRIMARY KEY autoincrement",
"Dbtime varchar(100) default NULL",
"Status integer default 0",
"SpecMax integer default NULL"
};
foreach (string item in cols)
{
string Name = item + " decimal(10, 4) default NULL";
Columns.Add(Name);
}
return Columns;
}
public string[] GetKeys<T>(T model) where T : class
{
System.Reflection.PropertyInfo[] properties = model.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
if (properties.Length <= 0)
{
throw new Exception("类属性长度为零");
}
string[] Columns = new string[properties.Length - 1];
int con = 0;
foreach (System.Reflection.PropertyInfo item in properties)
{
if (item.Name != "ID")
{
Columns[con++] = item.Name;
}
}
return Columns;
}
public string AddString(string TableName, string[] keys, string[] values)
{
string keys_string = "(" + keys[0];
string value_string = "('" + values[0] + "'";
for (int i = 1; i < keys.Length; i++)
{
keys_string += "," + keys[i];
}
for (int i = 1; i < values.Length; i++)
{
value_string += ",'" + values[i] + "'";
}
keys_string += ")";
value_string += ")";
string sql = string.Format("INSERT INTO " + TableName + " {0} VALUES {1} ;", keys_string, value_string);
return sql;
}
public string AddString(string TableName, Dictionary<string, string> keyValues)
{
string keys_string = "( ";
string value_string = "( ";
int count = 0;
foreach (var item in keyValues)
{
if (count == 0)
{
keys_string += item.Key;
value_string += "'" + item.Value + "'";
count++;
}
else
{
keys_string += "," + item.Key;
value_string += ",'" + item.Value + "'";
}
}
keys_string += ")";
value_string += ")";
string sql = string.Format("INSERT INTO " + TableName + " {0} VALUES {1} ;", keys_string, value_string);
return sql;
}
public string AddStringReal(string TableName, Dictionary<string, decimal> keyValues)
{
string keys_string = "( Dbtime ";
string value_string = "('" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'";
foreach (var item in keyValues)
{
keys_string += "," + item.Key;
value_string += ",'" + item.Value + "'";
}
keys_string += ")";
value_string += ")";
string sql = string.Format("INSERT INTO " + TableName + " {0} VALUES {1} ;", keys_string, value_string);
return sql;
}
public int QueryCount(string TableName, string where = "")
{
var value = 0;
try
{
lock (queryLockObj)
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
if (sqliteConn.State != System.Data.ConnectionState.Open) sqliteConn.Open();
cmd.Connection = sqliteConn;
cmd.CommandText = " select count(*) from " + TableName + " " + where;
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
sqliteConn.Close();
if (int.TryParse(dt.Rows[0][0].ToString(), out var result))
{
value = result;
}
}
return value;
}
}
catch (Exception ex)
{
Log.Error("查询出错:" + TableName + where + "\r\n" + ex.Message);
}
return value;
}
public List<T> Query<T>(string TableName, string where = "") where T : new()
{
try
{
lock (queryLockObj)
{
List<T> datas = new List<T>();
using (SQLiteCommand cmd = new SQLiteCommand())
{
if (sqliteConn.State != System.Data.ConnectionState.Open) sqliteConn.Open();
cmd.Connection = sqliteConn;
cmd.CommandText = " select * from " + TableName + " " + where;
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
sqliteConn.Close();
System.Reflection.PropertyInfo[] properties = typeof(T).GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
if (properties.Length <= 0)
{
throw new Exception("类属性长度为零");
}
foreach (DataRow dd in dt.Rows)
{
int i = 0;
var model = new T();
foreach (System.Reflection.PropertyInfo item in properties)
{
var value = dd[i++];
if (value is DBNull)
{
var ds = Convert.ChangeType(0, item.PropertyType);
item.SetValue(model, ds, null);
}
else
{
var ds = Convert.ChangeType(value, item.PropertyType);
item.SetValue(model, ds, null);
}
}
datas.Add(model);
}
}
return datas;
}
}
catch (Exception ex)
{
Log.Error("查询出错:" + TableName + where + "\r\n" + ex.Message);
return new List<T>();
}
}
public List<T> Query<T>(string sql) where T : new()
{
try
{
lock (queryLockObj)
{
List<T> datas = new List<T>();
using (SQLiteCommand cmd = new SQLiteCommand())
{
if (sqliteConn.State != System.Data.ConnectionState.Open) sqliteConn.Open();
cmd.Connection = sqliteConn;
cmd.CommandText = sql;
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
sqliteConn.Close();
System.Reflection.PropertyInfo[] properties = typeof(T).GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
if (properties.Length <= 0)
{
throw new Exception("类属性长度为零");
}
foreach (DataRow dd in dt.Rows)
{
int i = 0;
var model = new T();
foreach (System.Reflection.PropertyInfo item in properties)
{
var value = dd[i++];
if (value is DBNull)
{
var ds = Convert.ChangeType(0, item.PropertyType);
item.SetValue(model, ds, null);
}
else
{
var ds = Convert.ChangeType(value, item.PropertyType);
item.SetValue(model, ds, null);
}
}
datas.Add(model);
}
}
return datas;
}
}
catch (Exception ex)
{
Log.Error("查询出错:" + sql + "\r\n" + ex.Message);
return new List<T>();
}
}
private readonly object LockObj = new object();
public bool SqliteDbTransaction(string sqlString)
{
lock (LockObj)
{
if (sqliteConn.State == ConnectionState.Closed) sqliteConn.Open();
DbTransaction trans = sqliteConn.BeginTransaction();
try
{
using (SQLiteCommand cmd = new SQLiteCommand(sqliteConn))
{
int rows = 0;
cmd.CommandText = sqlString;
rows = cmd.ExecuteNonQuery();
trans.Commit();//提交事务
sqliteConn.Close();
return rows > 0;
}
}
catch (Exception ex)
{
trans.Rollback();//回滚事务
sqliteConn.Close();
Log.Error("提交数据库失败" + ex.Message);
return false;
}
}
}
}
