BulkDbSQLite.cs

using FLY.OBJComponents.IService;

using FObjBase;
using SQLite;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FLY.OBJComponents.Server
{
    public class BulkDbSQLite<TLc, TDb> : IBulkDbSQLiteService
        where TLc : IDbBase, new()
        where TDb : IDbBase, new()
    {
        DBTable<TDb> dbTable;
        SQLiteHelper sqliteHelper;
        Func<TLc, TDb> mapLc2Db;
        Func<TDb, TLc> mapDb2Lc;

        public long LastId { get; private set; }

        public event PropertyChangedEventHandler PropertyChanged;


        public BulkDbSQLite()
        {

        }
        public void Init(DBTable<TDb> dbTable,
            Func<TLc, TDb> mapLc2Db, Func<TDb, TLc> mapDb2Lc)
        {
            this.dbTable = dbTable;
            this.sqliteHelper = dbTable.sqliteHelper;
            this.mapLc2Db = mapLc2Db;
            this.mapDb2Lc = mapDb2Lc;
            //获取最后一行数据
            LastId = dbTable.MaxID;
        }

        public void Add(TLc lc)
        {
            //添加 数据库必要的 field
            lc.ID = dbTable.FreeID;

            //SQLs
            List<string> sqls = new List<string>();
            sqls.Add(SQLiteHelper.GetInsertCommandText(mapLc2Db(lc)));
            sqliteHelper.QueryTranAsync(sqls);

            LastId = lc.ID;
        }
        public void AddRange(IEnumerable<TLc> lcs)
        {
            //SQLs
            List<string> sqls = new List<string>();

            foreach (var lc in lcs)
            {
                //添加 数据库必要的 field
                lc.ID = dbTable.FreeID;
                sqls.Add(SQLiteHelper.GetInsertCommandText(mapLc2Db(lc)));
            }
            sqliteHelper.QueryTranAsync(sqls);

            LastId = lcs.Last().ID;
        }

        public async void GetTrend(Pack_GetTrendRequest request, AsyncCBHandler asyncDelegate, object asyncContext)
        {

            Pack_GetTrendReponse<TLc> reponse = new Pack_GetTrendReponse<TLc>();
            reponse.Request = request;

            if (request.Count > 400)//数量限制，避免死机
                request.Count = 400;
            else if (request.Count < 1)
                request.Count = 1;

            if (request.Interval < 1)//避免 /0
                request.Interval = 1;


            await Task.Factory.StartNew(() =>
            {
                List<TDb> dbs = null;
                if (!request.IsSearchByTime)
                {
                    if (request.Id <= 0)
                        dbs = dbTable.Find(
                            $"WHERE (ID % {request.Interval} = 0)" +
                            $" ORDER BY ID DESC" +
                            $" LIMIT {request.Count} OFFSET {-request.Id}");
                    else
                        dbs = dbTable.Find(
                            $"WHERE (ID <= {request.Id})" +
                            $" AND (ID % {request.Interval} = 0)" +
                            $" ORDER BY ID DESC" +
                            $" LIMIT {request.Count}");
                }
                else
                {
                    dbs = dbTable.Find(
                            $"WHERE ( Time <= {request.Time.ToStringOfSQLiteFieldType()})" +
                            $" AND ( ID % {request.Interval} = 0)" +
                            $" ORDER BY ID DESC" +
                            $" LIMIT {request.Count}");
                }


                if (dbs.Count() == 0)
                    return;

                //转为 Lc_XXX
                List<TLc> lcs = new List<TLc>();
                foreach (var db in dbs)
                {
                    lcs.Add(mapDb2Lc(db));
                }

                //从尾向前排的！！！！
                reponse.Values = lcs;
            });

            asyncDelegate(asyncContext, reponse);
        }
    }

    public class BulkDbSQLite<TDb> : IBulkDbSQLiteService
        where TDb : IDbBase, new()
    {
        DBTable<TDb> dbTable;
        SQLiteHelper sqliteHelper;

        public long LastId { get; private set; }

        public event PropertyChangedEventHandler PropertyChanged;


        public BulkDbSQLite()
        {

        }
        public void Init(DBTable<TDb> dbTable)
        {
            this.dbTable = dbTable;
            this.sqliteHelper = dbTable.sqliteHelper;
            //获取最后一行数据
            LastId = dbTable.MaxID;
        }

        public void Add(TDb lc)
        {
            //添加 数据库必要的 field
            lc.ID = dbTable.FreeID;

            //SQLs
            List<string> sqls = new List<string>();
            sqls.Add(SQLiteHelper.GetInsertCommandText(lc));
            sqliteHelper.QueryTranAsync(sqls);

            LastId = lc.ID;
        }
        public void AddRange(IEnumerable<TDb> lcs)
        {
            //SQLs
            List<string> sqls = new List<string>();

            foreach (var lc in lcs)
            {
                //添加 数据库必要的 field
                lc.ID = dbTable.FreeID;
                sqls.Add(SQLiteHelper.GetInsertCommandText(lc));
            }
            sqliteHelper.QueryTranAsync(sqls);

            LastId = lcs.Last().ID;
        }

        public async void GetTrend(Pack_GetTrendRequest request, AsyncCBHandler asyncDelegate, object asyncContext)
        {

            Pack_GetTrendReponse<TDb> reponse = new Pack_GetTrendReponse<TDb>();
            reponse.Request = request;

            if (request.Count > 400)//数量限制，避免死机
                request.Count = 400;
            else if (request.Count < 1)
                request.Count = 1;

            if (request.Interval < 1)//避免 /0
                request.Interval = 1;


            await Task.Factory.StartNew(() =>
            {
                List<TDb> dbs = null;
                if (!request.IsSearchByTime)
                {
                    if (request.Id <= 0)
                        dbs = dbTable.Find(
                            $"WHERE (ID % {request.Interval} = 0)" +
                            $" ORDER BY ID DESC" +
                            $" LIMIT {request.Count} OFFSET {-request.Id}");
                    else
                        dbs = dbTable.Find(
                            $"WHERE (ID <= {request.Id})" +
                            $" AND (ID % {request.Interval} = 0)" +
                            $" ORDER BY ID DESC" +
                            $" LIMIT {request.Count}");
                }
                else
                {
                    dbs = dbTable.Find(
                            $"WHERE ( Time <= {request.Time.ToStringOfSQLiteFieldType()})" +
                            $" AND ( ID % {request.Interval} = 0)" +
                            $" ORDER BY ID DESC" +
                            $" LIMIT {request.Count}");
                }


                if (dbs.Count() == 0)
                    return;

                //从尾向前排的！！！！
                reponse.Values = dbs;
            });

            asyncDelegate(asyncContext, reponse);
        }
    }
}