DBTable.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;

namespace SQLite
{
    public class DBTable<T> : IDBTable
        where T : new()
    {
        public string TableName { get; private set; }


        /// <summary>
        /// 每次获取，自动++
        /// </summary>
        public long FreeID
        {
            get
            {
                MaxID++;
                return MaxID;
            }
        }
        public long MaxID { get; set; } = -1;

        

        public string DDL { get; private set; }
        public string INDEX { get; private set; }

        /// <summary>
        /// 时间列 名称
        /// </summary>
        public string TimeFieldName { get; private set; } = null;

        public SQLiteHelper sqliteHelper;

        public DBTable()
        {
            TableName = SQLiteHelper.GetTableName(typeof(T));
            DDL = SQLiteHelper.GetCreateTableCommandText(typeof(T));
            INDEX = SQLiteHelper.GetCreateIndexCommandText(typeof(T));

            updateTimeFieldName();

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

                if (!(propertyInfo.GetCustomAttributes(typeof(TimeAttribute), false).Count() > 0))
                    continue;//没有Time

                //找到了
                TimeFieldName = propertyInfo.Name;
                return;
            }
        }
        public void Init(SQLiteHelper sQLiteHelper)
        {
            sqliteHelper = sQLiteHelper;
        }

        public void Create()
        {
            //创建数据库
            sqliteHelper.ExecuteNonQuery(DDL);

            //创建index
            if(!string.IsNullOrEmpty(INDEX))
                sqliteHelper.ExecuteNonQuery(INDEX);
        }
        public string GetDeleteSQL(DateTime del_time)
        {
            if (string.IsNullOrEmpty(TimeFieldName))
                return null;

            string del_time_str = del_time.ToStringOfSQLiteFieldType();
            //获取 比 del_time 小的 最后一个ID
            var ret = sqliteHelper.ExecuteScalar($"SELECT ID FROM {TableName} WHERE {TimeFieldName} < {del_time_str} ORDER BY ID DESC LIMIT 1");
            if (ret != null)
            {
                long max_id = Convert.ToInt64(ret);
                return $"DELETE FROM {TableName}" +
                        $" WHERE ID < {max_id}";
            }
            return null;
        }

        public void Add(T t)
        {
            sqliteHelper.ExecuteNonQuery(SQLiteHelper.GetInsertCommandText(t));
        }
        public void Update(T t, string condition)
        {
            sqliteHelper.ExecuteNonQuery(SQLiteHelper.GetUpdateCommandText(t, condition));
        }
        public bool AddRange(IEnumerable<T> array)
        {
            List<string> querys = new List<string>();
            foreach (T t in array)
            {
                querys.Add(SQLiteHelper.GetInsertCommandText(t));
            }
            return sqliteHelper.QueryTran(querys);
        }

        public List<T> Find(string condition)
        {
            string sql = $"SELECT * FROM {TableName}";
            if (!string.IsNullOrEmpty(condition))
                sql += " " + condition;

            DataTable dataTable = sqliteHelper.ExecuteReader(sql);
            return SQLiteHelper.ToObjs<T>(dataTable);
        }

        /// <summary>
        /// 获取最后N行数据
        /// </summary>
        /// <param name="count"></param>
        /// <returns></returns>
        public List<T> Last(int count)
        {
            return Find($"LIMIT (SELECT COUNT()-{count} FROM {TableName}),{count}");
        }

        /// <summary>
        /// 获取前面N行数据
        /// </summary>
        /// <param name="count"></param>
        /// <returns></returns>
        public List<T> First(int count)
        {
            return Find($"LIMIT {count}");
        }

        public void Remove()
        {

        }
    }
}