public class SQLiteOperation
{
/// <summary>
/// 数据库位置
/// </summary>
private static readonly string FilePath = Environment.CurrentDirectory + @"\PARAM\SQLiteDatabase.db";
public SQLiteOperation()
{
Init();
}
/// <summary>
/// 初始化
/// </summary>
private void Init()
{
//可在这初始化创建所需要的固定的数据表
}
#region 创建数据库和数据表
/// <summary>
/// 创建数据表
/// </summary>
/// <param name="table"></param>
/// <param name="model"></param>
/// <returns></returns>
public bool CreateDataTable<T>(string table, T model) where T : class
{
if(IsTableExist(table))
{
return true;
}
var str = "CREATE TABLE " + table + "(";
var Property_Types = GetPropertyType(model);
foreach(var type in Property_Types)
{
str += $"{type.Key} {type.Value},";
}
str = str.Remove(str.Length - 1) + ")";
try
{
using(SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + FilePath))
{
if(sqliteConn.State != System.Data.ConnectionState.Open)
{
sqliteConn.Open();
using(SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = sqliteConn;
cmd.CommandText = str;
cmd.ExecuteNonQuery();
}
}
sqliteConn.Close();
}
return true;
}
catch(Exception)
{
return false;
}
}
/// <summary>
/// 创建数据库文件
/// </summary>
/// <returns></returns>
private bool CreateDataBase()
{
if(File.Exists(FilePath))
{
return true;
}
try
{
SQLiteConnection.CreateFile(FilePath);
return true;
}
catch(Exception)
{
return false;
}
}
/// <summary>
/// 判断数据表是否存在
/// </summary>
/// <param name="Table"></param>
/// <returns></returns>
private bool IsTableExist(string Table)
{
using(SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + FilePath))
{
sqliteConn.Open();
//sqlite_master系统表
var checkTableQuery = $"SELECT name FROM sqlite_master WHERE type='table' AND name='{Table}'";
using(SQLiteCommand command = new SQLiteCommand(checkTableQuery, sqliteConn))
{
using(SQLiteDataReader reader = command.ExecuteReader())
{
if(reader.Read())
{
sqliteConn.Close();
return true;
}
else
{
sqliteConn.Close();
return false;
}
}
}
}
}
#endregion
#region 增
/// <summary>
/// 插入数据
/// </summary>
/// <param name="Table"></param>
/// <param name="model">实体类</param>
/// <returns></returns>
public bool InsertData<T>(string Table, T model) where T : class
{
string query = $"insert into {Table}(";
var Property_Values = GetPropertyValue(model);
foreach(var property in Property_Values)
{
query += property.Key + ",";
}
query = query.Remove(query.Length - 1) + ") values(";
foreach(var property in Property_Values)
{
if(property.Key == "DateTime")
{
//查询数据读取DateTime会报错：异常详细信息: System.FormatException: 该字符串未被识别为有效的 DateTime。
//格式化.ToString()方法中加一个s,即可解决日期读取错误的问题。格式化后有个T在中间，去掉
var datatime = ((DateTime)(property.Value)).ToString("s").Replace("T"," ");
query += $"'{datatime}',";
continue;
}
query += $"'{property.Value}',";
}
query = query.Remove(query.Length - 1) + ")";
try
{
using(SQLiteConnection sqliteConn = new SQLiteConnection($"data source={FilePath}"))
{
sqliteConn.Open();
using(SQLiteCommand cmd = new SQLiteCommand(query, sqliteConn))
{
cmd.ExecuteNonQuery();
}
sqliteConn.Close();
}
return true;
}
catch(Exception)
{
return false;
}
}
#endregion
#region 删
/// <summary>
/// 删除数据
/// </summary>
/// <param name="Table">表</param>
/// <param name="dics">条件键值对</param>
/// <returns></returns>
public bool DeleteData(string Table, Dictionary<string, object> dics)
{
var query = $"delete from {Table} where ";
foreach(var dic in dics)
{
query += $"{dic.Key}='{dic.Value}' And ";
}
query = query.Remove(query.Length - 5);
try
{
using(SQLiteConnection sqliteConn = new SQLiteConnection($"data source={FilePath}"))
{
sqliteConn.Open();
using(SQLiteCommand cmd = new SQLiteCommand(query, sqliteConn))
{
cmd.ExecuteNonQuery();
}
sqliteConn.Close();
}
return true;
}
catch(Exception)
{
return false;
}
}
#endregion
#region 查
/// <summary>
/// 根据日期查询数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="Table"></param>
/// <param name="StartDateTime"></param>
/// <param name="EndDateTime"></param>
/// <returns></returns>
public List<T> QueryData<T>(string Table, DateTime StartDateTime, DateTime EndDateTime) where T : class, new()
{
try
{
var StartTime = StartDateTime.ToString("yyyy-MM-dd") + " 00:00:00";
var EndTime = EndDateTime.ToString("yyyy-MM-dd") + " 23:59:59";
using(SQLiteConnection sqliteConn = new SQLiteConnection($"data source={FilePath}"))
{
sqliteConn.Open();
string query = $"select * from {Table} where DateTime between '{StartTime}' and '{EndTime}'";
using(SQLiteCommand cmd = new SQLiteCommand(query, sqliteConn))
{
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
sqliteConn.Close();
return DataTableConvert<T>(dt);
}
}
}
catch(Exception)
{
return null;
}
}
/// <summary>
/// 查询所有数据，返回List<T>类型结果
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="Table"></param>
/// <returns></returns>
public List<T> QueryAllData<T>(string Table) where T : class, new()
{
try
{
using(SQLiteConnection sqliteConn = new SQLiteConnection($"data source={FilePath}"))
{
sqliteConn.Open();
string query = $"select * from {Table}";
SQLiteCommand cmd = new SQLiteCommand(query, sqliteConn);
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
sqliteConn.Close();
return DataTableConvert<T>(dt);
}
}
catch(Exception)
{
return null;
}
}
#endregion
#region Common
/// <summary>
/// 获得对应（属性-类型）键值对
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <returns></returns>
private Dictionary<string, object> GetPropertyType<T>(T model) where T : class
{
//使用反射获取所有属性
PropertyInfo[] properties = model.GetType().GetProperties(BindingFlags.Public|BindingFlags.Instance);
//创建一个字典来存储属性和对应类型
var Property_Types = new Dictionary<string,object>();
foreach(PropertyInfo property in properties)
{
// 检查属性是否有getter，以便可以读取其对应类型
if(property.CanRead)
{
Property_Types[property.Name] = property.PropertyType.ToString().Replace("System.", "");
}
}
return Property_Types;
}
/// <summary>
/// 获得对应（属性-值）键值对
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <returns></returns>
private Dictionary<string, object> GetPropertyValue<T>(T model) where T : class
{
//使用反射获取所有属性
PropertyInfo[] properties = model.GetType().GetProperties(BindingFlags.Public|BindingFlags.Instance);
//创建一个字典来存储属性和对应类型
var Property_Values = new Dictionary<string,object>();
foreach(PropertyInfo property in properties)
{
// 检查属性是否有getter，以便可以读取其对应类型
if(property.CanRead)
{
Property_Values[property.Name] = property.GetValue(model);
if(Property_Values[property.Name] == null)
{
Property_Values[property.Name] = string.Empty;
}
}
}
return Property_Values;
}
/// <summary>
/// 判断该表是否含有该键
/// </summary>
/// <param name="Table"></param>
/// <param name="Property"></param>
/// <returns></returns>
private bool IsLegalJudge(string Table, string Property)
{
List<string> lists = new List<string>();
using(SQLiteConnection connection = new SQLiteConnection($"data source={FilePath}"))
{
connection.Open();
// 使用PRAGMA table_info来获取表的架构信息
string query = $"PRAGMA table_info({Table})";
using(SQLiteCommand command = new SQLiteCommand(query, connection))
using(SQLiteDataReader reader = command.ExecuteReader())
{
while(reader.Read())
{
// 读取列名（name字段）
string columnName = reader.GetString(1); // 第二列是列名
lists.Add(columnName);
}
}
}
foreach(var list in lists)
{
if(list == Property)
return true;
}
return false;
}
/// <summary>
/// DataTable转化为List<T>结果列表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dt"></param>
/// <returns></returns>
private List<T> DataTableConvert<T>(DataTable dt) where T : class, new()
{
List<T> ResultList = new List<T>();
foreach(DataRow dr in dt.Rows)
{
T obj = new T();
foreach(PropertyInfo info in obj.GetType().GetProperties())
{
if(dt.Columns.Contains(info.Name))
{
if(dr[info.Name] != DBNull.Value)
{
info.SetValue(obj, dr[info.Name], null);
}
}
}
ResultList.Add(obj);
}
return ResultList;
}
#endregion
}
