C# 封装Sqlite 调用方式

• 下载
• 异常
• C# 封装
• • 1.调用方式
  • 2.创建 接口 IDBSupport
  • 3.DBSupport 实现 IDBSupport
  • 4. 封装 DbHelperSQLite

下载

异常

试图加载格式不正确的程序。 (异常来自 HRESULT:0x8007000B)
出现这种错误，可以先把程序改为x64

C# 封装

1.调用方式

YC.Sqlite_DB.IDBSupport<YC.Sqlite_DB.Model.Student> db_stu = new YC.Sqlite_DB.DBSupport<YC.Sqlite_DB.Model.Student>();     var stu = new YC.Sqlite_DB.Model.Student()     {  id = Guid.NewGuid().ToString("N"),  Name = "张三",  Age = 23,  Add_Time = DateTime.Now.ToString("yyyy-MM-dd HH:MM:ss.fff"),  Remark = "新员工"     };     var i = db_stu.Insert(stu);     if (i)  Console.WriteLine(String.Format("数据插入：{0} 创建成功！", stu.Name));     else  Console.WriteLine(String.Format("数据插入：{0} 创建失败！", stu.Name));     //更新     i = false;     stu.Age = 40;     i = db_stu.Update(stu);     if (i)  Console.WriteLine(String.Format("数据更新：{0} 更新成功！", stu.Name));     else  Console.WriteLine(String.Format("数据更新：{0} 更新失败！", stu.Name));     //事务批量添加     var list = Get_List();     var res = db_stu.InsertSqlStran(list);     if (res)  Console.WriteLine(String.Format("数据批量插入成功！", stu.Name));     else  Console.WriteLine(String.Format("数据批量插入失败！", stu.Name));     var q_list = db_stu.Query("1=1", "Age");     Console.WriteLine(String.Format("数据查询：{0} 条数据！", q_list.Count));

2.创建 接口 IDBSupport

 public interface IDBSupport<T>    { bool Insert(T t); bool Delete(string strWhere); bool DropTable(); bool ClearTable(); /// 
 ///  ///  /// 条件 /// 排序字段 /// 排序方式ASC/DESC ///  List<T> Query(string strWhere, string order_by, string sort = "desc"); List<T> QueryTop(int topNm, string strWhere, string order_by, string sort = "desc"); List<T> Query_Sql(string strSql,string where); /// 
 ///  ///  /// 条件 /// 排序字段 /// 排序方式ASC/DESC ///  T QuerySingle(string strWhere, string order_by, string sort = "desc"); T QueryMaxSingle(string strWhere, string Field); /// 
 /// 获取数量 ///  ///  ///  int QueryCount(string strWhere); List<T> Query_GroupBy(string strWhere, string group_by); bool ExeTrans(List<SqliteCommandInfo> list); bool InsertSqlStran(List<T> list); bool ExeTrans(ArrayList list); bool Exe_Sql(string sql); bool Update(T Model); bool Update(string SqlStr, SQLiteParameter[] cmdParms = null);    }

3.DBSupport 实现 IDBSupport

这里我只实现了一部分，具体在下载内容中

 private Type m_type; public DBSupport() {     m_type = typeof(T);     CreateTable(); } public void CreateTable() {     try     {  string sqlStr = "CREATE TABLE IF NOT EXISTS \"{0}\" ({1})";  PropertyInfo[] pi = m_type.GetProperties();  string sqlFormat = "\"{0}\" {1}";  System.Attribute[] attrs = System.Attribute.GetCustomAttributes(m_type);  if (attrs.Count() == 1)  {      var s = (Common.DB_FieldAttribute)attrs[0];      if (s.Usage == Common.EnumDBFieldUsage.View)//验证是否是视图，视图不需要创建表      {   return;      }  }  string sqlStr2 = "";  foreach (PropertyInfo p in pi)  {      string name = p.Name;      var s = (Common.DB_FieldAttribute)System.Attribute.GetCustomAttribute(p, typeof(Common.DB_FieldAttribute));// 属性值      if (s.Usage == Common.EnumDBFieldUsage.PrimaryKey)      {   sqlStr2 += string.Format(sqlFormat, name, SqlType(p, s.FieldLen)) + " NOT NULL PRIMARY KEY,";      }      else      {   sqlStr2 += string.Format(sqlFormat, name, SqlType(p, s.FieldLen)) + ",";      }  }  var strsql = string.Format(sqlStr, m_type.Name, sqlStr2.TrimEnd(','));  DBUtility.DbHelperSQLite.ExecuteSql(strsql);     }     catch (Exception ex)     {  throw;     } } public bool Delete(string strWhere) {     StringBuilder strSql = new StringBuilder();     strSql.Append("delete from ").Append("'" + m_type.Name + "'");     if (string.IsNullOrEmpty(strWhere))     {  return false;     }     else     {  strSql.AppendFormat(" where {0}", strWhere);     }     return DBUtility.DbHelperSQLite.ExecuteSql(strSql.ToString()) > 0; } public bool DropTable() {     StringBuilder strSql = new StringBuilder();     strSql.Append("drop table").Append("'" + m_type.Name + "'");     return DBUtility.DbHelperSQLite.ExecuteSql(strSql.ToString()) > 0; } public bool ClearTable() {     StringBuilder strSql = new StringBuilder();     strSql.Append("delete from ").Append("'" + m_type.Name + "'");     return DBUtility.DbHelperSQLite.ExecuteSql(strSql.ToString()) > 0; }  public bool Insert(T t) {     string columns = GetColmons();     if (string.IsNullOrEmpty(columns))     {  return false;     }     string values = GetValues(t);     if (string.IsNullOrEmpty(values))     {  return false;     }     StringBuilder sql = new StringBuilder();     sql.AppendFormat("Insert into '{0}'", m_type.Name);     sql.Append("(" + columns + ")");     sql.Append(" values(" + values + ")");     sql.ToString();     return DBUtility.DbHelperSQLite.ExecuteSql(sql.ToString()) > 0; } public bool InsertSqlStran(List<T> list) {     string columns = GetColmons();     if (string.IsNullOrEmpty(columns))     {  return false;     }     string values = GetColmonsEx();     if (string.IsNullOrEmpty(values))     {  return false;     }     StringBuilder sql = new StringBuilder();     sql.AppendFormat("Insert into '{0}'", m_type.Name);     sql.Append("(" + columns + ")");     sql.Append(" values(" + values + ")");     sql.ToString();     return DBUtility.DbHelperSQLite.InsertSqlTran<T>(sql.ToString(), list) > 0; }   public bool Update(T model) {     StringBuilder sb = new StringBuilder();     sb.Append("update ");     sb.Append(m_type.Name);     sb.Append(" set ");     object pkValue = null; var pkName = "";     PropertyInfo[] props = m_type.GetProperties();     List<string> paraList = new List<string>();     foreach (var item in props)     {  var s = ((Common.DB_FieldAttribute)System.Attribute.GetCustomAttribute(item, typeof(Common.DB_FieldAttribute)));// 属性值  if (s.Usage == Common.EnumDBFieldUsage.PrimaryKey)  {      if (item.PropertyType == typeof(String))      {   pkValue = (string)item.GetValue(model, null);      }      else if (item.PropertyType == typeof(Int32))      {   pkValue = (int)item.GetValue(model, null);      }      pkName = s.FieldName;  }  else  {      paraList.Add(string.Format("\"{0}\"='{1}' ", s.FieldName, item.GetValue(model, null)));  }     }     if (paraList.Count == 0)     {  return false;     }     sb.Append(string.Join(",", paraList));     if (string.IsNullOrEmpty(pkValue.ToString()) && string.IsNullOrEmpty(pkName))     {  return false;     }     sb.Append(" where ");     sb.Append("\"" + pkName + "\"");     sb.Append(" = ");     sb.AppendFormat("'{0}'", pkValue);     return DBUtility.DbHelperSQLite.ExecuteSql(sb.ToString()) > 0;   }

4. 封装 DbHelperSQLite

 /// 
 /// 执行SQL语句，返回影响的记录数 ///  /// SQL语句 /// 影响的记录数 public static int ExecuteSql(string SQLString) {     using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))     {  using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))  {      try      {   connection.Open();   int rows = cmd.ExecuteNonQuery();   return rows;      }      catch (System.Data.SQLite.SQLiteException E)      {   connection.Close();   throw new Exception(E.Message);      }  }     } } public static int InsertSqlTran<T>(string strSql, List<T> list) where T : class {     using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))     {  conn.Open();  SQLiteCommand cmd = new SQLiteCommand();  cmd.Connection = conn;  SQLiteTransaction tx = conn.BeginTransaction();  cmd.Transaction = tx;  int count = 0;  try  {      var m_type = typeof(T);      cmd.CommandText = strSql;      foreach (var item in list)      {   PropertyInfo[] props = m_type.GetProperties();   foreach (var p in props)   {cmd.Parameters.AddWithValue("@" + p.Name, p.GetValue(item, null));   }   count += cmd.ExecuteNonQuery();      }      tx.Commit();      return count;  }  catch (System.Data.SQLite.SQLiteException E)  {      tx.Rollback();      return 0;  }     } } public static DataSet Query(string SQLString) {     using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))     {  DataSet ds = new DataSet();  try  {      connection.Open();      SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, connection);      command.Fill(ds, "ds");  }  catch (System.Data.SQLite.SQLiteException ex)  {      throw new Exception(ex.Message);  }  return ds;     } }

开发者涨薪指南 48位大咖的思考法则、工作方式、逻辑体系