Sqlite数据库操作

 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
 }