SQLiteHelper.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Reflection;
using System.Text;

namespace FLY.OBJComponents.Common.SQLite
{
    public class SQLiteHelper
    {
        #region 静态操作
        public class SQLiteFieldTypeInfo
        {
            /// <summary>
            /// sqlite field 类型
            /// </summary>
            public string FieldType { get; set; }
            /// <summary>
            /// C# 类型
            /// </summary>
            public Type PropertyType { get; set; }

            /// <summary>
            /// C# 类型转 sqlite 字符串
            /// </summary>
            public Func<object, string> PtoS { get; set; } = DefaultToS;

            public Func<object, object> StoP { get; set; } = DefaultToP;

            public static string DefaultToS(object obj)
            {
                return obj.ToString();
            }

            public static object DefaultToP(object obj)
            {
                return obj;
            }

            public SQLiteFieldTypeInfo(string fieldtype, Type propertytype)
            {
                FieldType = fieldtype;
                PropertyType = propertytype;
            }
            public SQLiteFieldTypeInfo(string fieldtype, Type propertytype, Func<object, string> ptos, Func<object, object> stop)
            {
                FieldType = fieldtype;
                PropertyType = propertytype;
                PtoS = ptos;
                StoP = stop;

            }


        }
        public static List<SQLiteFieldTypeInfo> FieldTypeInfo { get; set; }


        static SQLiteHelper()
        {

            FieldTypeInfo = new List<SQLiteFieldTypeInfo>
            {
                new SQLiteFieldTypeInfo("INTEGER",typeof(int),
                        SQLiteFieldTypeInfo.DefaultToS,
                        (obj)=>{
                            return Convert.ToInt32(obj);
                        }
                    ),
                new SQLiteFieldTypeInfo("INTEGER",typeof(Int64)),
                new SQLiteFieldTypeInfo("BOOLEAN",typeof(bool)),
                new SQLiteFieldTypeInfo("DOUBLE",typeof(double),
                        (obj)=>{
                            if(double.IsNaN((double)obj))
                                    return "NULL";
                            else return obj.ToString();
                        },
                        (obj)=>{
                            if(obj == DBNull.Value)
                                return double.NaN;
                            else
                                return obj;
                        }
                     ),
                new SQLiteFieldTypeInfo("STRING",typeof(string),
                        (obj)=>"'" + obj + "'",
                        SQLiteFieldTypeInfo.DefaultToP
                     ),
                new SQLiteFieldTypeInfo("DATETIME",typeof(DateTime),
                        (obj)=>"'" + ((DateTime)obj).ToString("yyyy-MM-dd HH:mm:ss.fff") + "'",
                        SQLiteFieldTypeInfo.DefaultToP
                     )
            };
        }

        public class ArrayFieldTypeInfo
        {
            /// <summary>
            /// 属性名
            /// </summary>
            public string PropertyName { get; set; }
            /// <summary>
            /// 数组大小
            /// </summary>
            public int Length { get; set; }

            public ArrayFieldTypeInfo(string propertyname, int length)
            {

                PropertyName = propertyname;
                Length = length;
            }
        }


        public static string GetTableName(Type type)
        {
            var attributes = type.GetCustomAttributes(typeof(TableAttribute), false);
            if (attributes.Count() > 0)
            {
                return ((TableAttribute)attributes.First()).Name;
            }
            else
            {
                return type.Name;
            }
        }

        static string GetCreateTableCommandText_fieldText(Type type, IEnumerable<ArrayFieldTypeInfo> arrayFieldTypeInfos)
        {
            string fieldtext = "";
            PropertyInfo[] propertyInfos = type.GetProperties();
            foreach (var propertyInfo in propertyInfos)
            {
                //忽略
                if (propertyInfo.GetCustomAttributes(typeof(IgnoreAttribute), false).Count() > 0)
                    continue;

                if (fieldtext != "")
                {
                    fieldtext += ",";
                }

                //这个属性 下面的全部属性，是同一个表
                if (propertyInfo.GetCustomAttributes(typeof(BortherAttribute), false).Count() > 0)
                {
                    //从arrayFieldTypeInfos 提取
                    string startswith = propertyInfo.Name + ".";
                    var aftis = from afti in arrayFieldTypeInfos
                    where afti.PropertyName.StartsWith(startswith)
                    select new ArrayFieldTypeInfo(afti.PropertyName.Substring(startswith.Length), afti.PropertyName.Length);
                    fieldtext += GetCreateTableCommandText_fieldText(propertyInfo.PropertyType, aftis);
                    continue;
                }

                if (propertyInfo.PropertyType.IsArray)//是数组，需要明确大小
                {
                    int length = arrayFieldTypeInfos.First((a) => a.PropertyName == propertyInfo.Name).Length;

                    Type elementType = propertyInfo.PropertyType.GetElementType();

                    SQLiteFieldTypeInfo fieldTypeInfo = FieldTypeInfo.Find((fti) => fti.PropertyType == elementType);
 
                    for (int i = 0; i < length; i++)
                    {
                        if (i != 0)
                            fieldtext += ",";

                        fieldtext += string.Format("{0}{1} {2}",
                            propertyInfo.Name,
                            i,
                            fieldTypeInfo.FieldType);
                    }
                }
                else
                {
                    SQLiteFieldTypeInfo fieldTypeInfo = FieldTypeInfo.Find((fti) => fti.PropertyType == propertyInfo.PropertyType);

                    fieldtext += string.Format("{0} {1}", propertyInfo.Name, fieldTypeInfo.FieldType);

                    //主键
                    if (propertyInfo.GetCustomAttributes(typeof(KeyAttribute), false).Count() > 0)
                        fieldtext += " PRIMARY KEY";
                }
            }

            return fieldtext;
        }
        public static string GetCreateTableCommandText(Type type, params ArrayFieldTypeInfo[] arrayFieldTypeInfos)
        {
            //CREATE TABLE table_name(
            //column1 datatype  PRIMARY KEY,
            //column2 datatype,
            //   column3 datatype,
            //   .....
            //   columnN datatype,
            //)
            string tablename = GetTableName(type);
            string fieldtext = GetCreateTableCommandText_fieldText(type, arrayFieldTypeInfos);
            string commandText = string.Format("CREATE TABLE {0} ({1})", tablename, fieldtext);
            return commandText;
        }


        static string GetInsertCommandText_fieldText(object cell)
        {
            Type type = cell.GetType();
            string fieldtext = "";
            PropertyInfo[] propertyInfos = type.GetProperties();
            foreach (var propertyInfo in propertyInfos)
            {
                //忽略
                if (propertyInfo.GetCustomAttributes(typeof(IgnoreAttribute), false).Count() > 0)
                    continue;

                if (fieldtext != "")
                {
                    fieldtext += ",";
                }

                object o = propertyInfo.GetValue(cell, null);


                //这个属性 下面的全部属性，是同一个表
                if (propertyInfo.GetCustomAttributes(typeof(BortherAttribute), false).Count() > 0)
                {
                    fieldtext += GetInsertCommandText_fieldText(o);
                    continue;
                }

                if (propertyInfo.PropertyType.IsArray)//是数组，需要明确大小
                {
                    Type elementType = propertyInfo.PropertyType.GetElementType();

                    SQLiteFieldTypeInfo fieldTypeInfo = FieldTypeInfo.Find((fti) => fti.PropertyType == elementType);

                    Array a = o as Array;

                    for (int i = 0; i < a.Length; i++)
                    {
                        if (i != 0)
                            fieldtext += ",";

                        fieldtext += fieldTypeInfo.PtoS(a.GetValue(i));
                    }
                }
                else
                {
                    SQLiteFieldTypeInfo fieldTypeInfo = FieldTypeInfo.Find((fti) => fti.PropertyType == propertyInfo.PropertyType);

                    fieldtext += fieldTypeInfo.PtoS(o);
                }
            }
            return fieldtext;
        }
        public static string GetInsertCommandText(object cell)
        {
            //不用
            //INSERT INTO TABLE_NAME[(column1, column2, column3,...columnN)]
            //VALUES(value1, value2, value3,...valueN);

            //使用
            //INSERT INTO TABLE_NAME VALUES(value1, value2, value3,...valueN);

            Type type = cell.GetType();
            string tablename = GetTableName(type);
            string fieldtext = GetInsertCommandText_fieldText(cell);
            string commandText = string.Format("INSERT INTO {0} VALUES({1})", tablename, fieldtext);
            return commandText;
        }


        static string GetUpdateCommandText_fieldText(object cell)
        {
            //UPDATE table_name
            //SET column1 = value1, column2 = value2...., columnN = valueN
            //WHERE[condition];

            Type type = cell.GetType();
            string tablename = GetTableName(type);

            string fieldtext = "";
            PropertyInfo[] propertyInfos = type.GetProperties();
            foreach (var propertyInfo in propertyInfos)
            {
                //忽略
                if (propertyInfo.GetCustomAttributes(typeof(IgnoreAttribute), false).Count() > 0)
                    continue;

                if (fieldtext != "")
                {
                    fieldtext += ",";
                }

                object o = propertyInfo.GetValue(cell, null);

                //这个属性 下面的全部属性，是同一个表
                if (propertyInfo.GetCustomAttributes(typeof(BortherAttribute), false).Count() > 0)
                {
                    fieldtext += GetUpdateCommandText_fieldText(o);
                    continue;
                }

                if (propertyInfo.PropertyType.IsArray)//是数组，需要明确大小
                {
                    Type elementType = propertyInfo.PropertyType.GetElementType();

                    SQLiteFieldTypeInfo fieldTypeInfo = FieldTypeInfo.Find((fti) => fti.PropertyType == elementType);

                    Array a = o as Array;

                    for (int i = 0; i < a.Length; i++)
                    {
                        if (i != 0)
                            fieldtext += ",";

                        fieldtext += string.Format("{0}{1} = {2}", propertyInfo.Name, i, fieldTypeInfo.PtoS(a.GetValue(i)));
                    }
                }
                else
                {
                    SQLiteFieldTypeInfo fieldTypeInfo = FieldTypeInfo.Find((fti) => fti.PropertyType == propertyInfo.PropertyType);


                    fieldtext += string.Format("{0} = {1}", propertyInfo.Name, fieldTypeInfo.PtoS(o));
                }
            }

            return fieldtext;
        }
        /// <summary>
        /// condition 为 "WHERE ......"
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public static string GetUpdateCommandText(object cell, string condition)
        {
            //UPDATE table_name
            //SET column1 = value1, column2 = value2...., columnN = valueN
            //WHERE[condition];

            Type type = cell.GetType();
            string tablename = GetTableName(type);
            string fieldtext = GetUpdateCommandText_fieldText(cell);
            string commandText = $"UPDATE {tablename} SET {fieldtext}";

            if (!string.IsNullOrEmpty(condition))
                commandText += $" {condition}";

            return commandText;
        }

        public static List<T> ToObjs<T>(DataTable dataTable, params ArrayFieldTypeInfo[] arrayFieldTypeInfos)
            where T : new()
        {
            Type type = typeof(T);
            List<T> list = new List<T>();
            foreach (DataRow dataRow in dataTable.Rows)
            {
                list.Add(ToObj<T>(dataRow, arrayFieldTypeInfos));
            }
            return list;
        }

        static void SetObj(object t, DataRow dataRow, IEnumerable<ArrayFieldTypeInfo> arrayFieldTypeInfos)
        {
            Type type = t.GetType();

            PropertyInfo[] propertyInfos = type.GetProperties();

            foreach (var propertyInfo in propertyInfos)
            {
                //忽略
                if (propertyInfo.GetCustomAttributes(typeof(IgnoreAttribute), false).Count() > 0)
                    continue;

                Type ptype = propertyInfo.PropertyType;
                
                //这个属性 下面的全部属性，是同一个表
                if (propertyInfo.GetCustomAttributes(typeof(BortherAttribute), false).Count() > 0)
                {
                    //从arrayFieldTypeInfos 提取
                    string startswith = propertyInfo.Name + ".";
                    var aftis = from afti in arrayFieldTypeInfos
                                where afti.PropertyName.StartsWith(startswith)
                                select new ArrayFieldTypeInfo(afti.PropertyName.Substring(startswith.Length), afti.PropertyName.Length);

                    object obj = propertyInfo.GetValue(t, null);
                    SetObj(obj, dataRow, aftis);
                    continue;
                }


                if (ptype.IsArray)//是数组，需要明确大小
                {
                    Type elementType = propertyInfo.PropertyType.GetElementType();
                    SQLiteFieldTypeInfo fieldTypeInfo = FieldTypeInfo.Find((fti) => fti.PropertyType == elementType);
                    int length = arrayFieldTypeInfos.First((a) => a.PropertyName == propertyInfo.Name).Length;

                    Array array = (Array)ptype.Assembly.CreateInstance(ptype.FullName, false, BindingFlags.CreateInstance, null, new object[] { length }, null, null);
                    for (int i = 0; i < length; i++)
                    {
                        object o = fieldTypeInfo.StoP(dataRow[propertyInfo.Name + i]);
                        array.SetValue(o, i);
                    }
                    propertyInfo.SetValue(t, array, null);
                }
                else
                {
                    SQLiteFieldTypeInfo fieldTypeInfo = FieldTypeInfo.Find((fti) => fti.PropertyType == ptype);
                    object o = fieldTypeInfo.StoP(dataRow[propertyInfo.Name]);
                    propertyInfo.SetValue(t, o, null);
                }
            }
        }
        public static T ToObj<T>(DataRow dataRow, params ArrayFieldTypeInfo[] arrayFieldTypeInfos)
            where T : new()
        {
            Type type = typeof(T);
            T t = new T();
            SetObj(t, dataRow, arrayFieldTypeInfos);
            return t;
        }

        #endregion


        public string ConnectionString;
        /// <summary> 
        /// 查询数据库中的所有数据类型信息。
        /// </summary> 
        /// <returns></returns> 
        /// <exception cref="Exception"></exception>
        public DataTable ExecuteReader(string sql)
        {
            DataTable data;
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    connection.Open();
                    command.CommandText = sql;
                    // 开始读取
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                    data = new DataTable();
                    adapter.Fill(data);
                    connection.Close();
                }
            }
            return data;
        }
        /// <summary> 
        /// 对SQLite数据库执行增删改操作，返回受影响的行数。 
        /// </summary> 
        /// <param name="sql">要执行的增删改的SQL语句。</param> 
        /// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param> 
        /// <returns></returns> 
        /// <exception cref="Exception"></exception>
        public void ExecuteNonQuery(string sql)
        {
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    connection.Open();
                    command.CommandText = sql;
                    command.ExecuteNonQuery();
                    connection.Close();
                }
            }
        }


        /// <summary>
        ///     
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>The first column of the first row of the first resultset from the query.</returns>
        public object ExecuteScalar(string sql)
        {
            object obj = null;
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    connection.Open();
                    command.CommandText = sql;
                    obj = command.ExecuteScalar();
                    connection.Close();
                }
            }
            return obj;
        }
        /// <summary>
        /// 多行执行
        /// </summary>
        /// <param name="queryList"></param>
        /// <returns></returns>
        public bool QueryTran(IEnumerable<string> queryList)
        {
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    connection.Open();

                    SQLiteTransaction tran = connection.BeginTransaction();
                    bool check = false;
                    try
                    {
                        foreach (string item in queryList)
                        {
                            command.CommandText = item;
                            command.ExecuteNonQuery();
                        }
                        tran.Commit();
                        check = true;
                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        check = false;
                        throw ex;
                    }
                    finally
                    {
                        connection.Close();
                    }
                    return check;
                }
            }


        }


        /// <summary>
        /// 输入DDLs 判断这些table都是否合法
        /// </summary>
        /// <param name="DDLs">key=tablename, value=DDL</param>
        /// <returns></returns>
        public bool IsTableValid(Dictionary<string,string> DDLs)
        {

            //检测 table 是否合法
            DataTable data = ExecuteReader("SELECT name,sql FROM sqlite_master WHERE type = 'table'");

            //任意一个表不对，或者不存在，都必须重建
            bool isVaild = true;
            foreach (var kv in DDLs)
            {
                string tablename = kv.Key;
                string createtable_sql = kv.Value;
                
                var sqls = from r in data.AsEnumerable() where (string)r["name"] == tablename select r["sql"];
                if (sqls.Count() == 0)
                {
                    //不存在该表，创建
                    isVaild = false;
                    break;
                }
                else
                {
                    string sql = (string)sqls.First();
                    if (sql != createtable_sql)
                    {
                        isVaild = false;
                        break;
                    }
                }
            }
            return isVaild;
        }
    }
}