C# 封装Sqlite 调用方式

下载

异常

试图加载格式不正确的程序。 (异常来自 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();
        /// <summary>
        /// 
        /// </summary>
        /// <param name="strWhere">条件</param>
        /// <param name="order_by">排序字段</param>
        /// <param name="sort">排序方式ASC/DESC</param>
        /// <returns></returns>
        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);
        /// <summary>
        /// 
        /// </summary>
        /// <param name="strWhere">条件</param>
        /// <param name="order_by">排序字段</param>
        /// <param name="sort">排序方式ASC/DESC</param>
        /// <returns></returns>
        T QuerySingle(string strWhere, string order_by, string sort = "desc");

        T QueryMaxSingle(string strWhere, string Field);
        /// <summary>
        /// 获取数量
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        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

 		/// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        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;
            }
        }