Example #1
0
        private void button5_Click(object sender, EventArgs e)
        {
            var trans = new DapperExTrans("MySql");

            try
            {
                trans.BeginTransaction();
                var rows1 = trans.Execute("INSERT INTO t1(name, entered, entered_by) VALUES('name1',now(), 'admin');;");
                var rows2 = trans.Execute("INSERT INTO t2(id, role, entered, entered_by) VALUES(100,'role3',now(), 'admin');");
                trans.CommitTransaction();
            }
            catch (Exception ex)
            {
                trans.RollbackTransaction();
                MessageBox.Show(ex.Message);
            }
        }
Example #2
0
        /// <summary>
        /// 更新kline
        /// </summary>
        /// <param name="symbol">交易对</param>
        public static async Task UpdateKline(string symbol, string interval)
        {
            var klineTableName    = CommonHelper.GetTableName(symbol, interval);
            var klineRawTableName = CommonHelper.GetTableName(symbol, interval, true);
            var wal = "PRAGMA journal_mode=WAL; ";

            DapperUtil.Execute(DataBaseType.SQLITE, ConstVar.Conn, wal);
            var sql1  = $"SELECT count(1) FROM sqlite_master WHERE type='table' and tbl_name ='{klineTableName}'";
            var count = DapperUtil.GetScalar <int>(DataBaseType.SQLITE, ConstVar.Conn, sql1);

            if (count == 0) // Kline 表不存在
            {
                var sql2 = CommonHelper.GetTableSql(symbol, interval, "Kline", klineTableName);
                DapperUtil.Execute(DataBaseType.SQLITE, ConstVar.Conn, sql2);
            }

            sql1  = $"SELECT count(1) FROM sqlite_master WHERE type='table' and tbl_name ='{klineRawTableName}'";
            count = DapperUtil.GetScalar <int>(DataBaseType.SQLITE, ConstVar.Conn, sql1);
            if (count == 0) // Kline_Raw表不存在
            {
                var sql2 = CommonHelper.GetTableSql(symbol, interval, "Kline_Raw", klineRawTableName);
                DapperUtil.Execute(DataBaseType.SQLITE, ConstVar.Conn, sql2);
            }

            await Engine.GetKline(symbol, interval);

            var kline = Core.Resource.LoadedResource.Klines[symbol].IntervalKline[interval];

            using (var connection = ConnectionFactory.GetConnection(DataBaseType.SQLITE, ConstVar.Conn))
            {
                var tran = new DapperExTrans(connection);
                try
                {
                    tran.BeginTransaction();

                    var maxDt        = string.Empty;
                    var maxDatestamp = 0L;
                    if (count > 0)
                    {
                        maxDt        = DapperUtil.GetScalar <string>(DataBaseType.SQLITE, ConstVar.Conn, $"select max(opentime) from {klineTableName}");
                        maxDatestamp = DapperUtil.GetScalar <long>(DataBaseType.SQLITE, ConstVar.Conn, $"select max(OpenTimestamp) from {klineRawTableName}");
                        // 删除最后一条数据
                        tran.Execute($"delete FROM  {klineTableName} where OpenTime ='{maxDt}'");
                        tran.Execute($"delete FROM  {klineRawTableName}  where OpenTimestamp ='{maxDatestamp}'");
                    }



                    foreach (var k in kline)
                    {
                        if (count > 0)
                        {
                            // 插入最新数据
                            var timestamp = long.Parse(k[0].ToString());
                            if (timestamp < maxDatestamp)
                            {
                                continue;
                            }
                        }

                        var sql = $@"insert into {klineTableName}(
                                    OpenTime                ,
                                    Open                    ,
                                    High                    ,
                                    Low                     ,
                                    Close                   ,
                                    Volume                  ,
                                    CloseTime               ,
                                    QuoteAssetVolume        ,
                                    NumberOfTrades          ,
                                    TakerBuyBaseAssetVolume ,
                                    TakerBuyQuoteAssetVolume,
                                    Ignore
                                    ) values(datetime('{k[0].ToString().StampToDatetime(true):yyyy-MM-dd HH:mm:ss.fff}'),
                                    '{k[1]}','{k[2]}','{k[3]}','{k[4]}','{k[5]}',
                                    '{k[6].ToString().StampToDatetime(true).ToString("yyyy-MM-dd HH:mm:ss.fff")}',
                                    '{k[7]}','{k[8]}','{k[9]}','{k[10]}','{k[11]}');";
                        tran.Execute(sql);

                        sql = $@"insert into {klineRawTableName}(
                                    OpenTimestamp                ,
                                    Open                    ,
                                    High                    ,
                                    Low                     ,
                                    Close                   ,
                                    Volume                  ,
                                    CloseTimestamp               ,
                                    QuoteAssetVolume        ,
                                    NumberOfTrades          ,
                                    TakerBuyBaseAssetVolume ,
                                    TakerBuyQuoteAssetVolume,
                                    Ignore
                                    ) values('{k[0]}','{k[1]}','{k[2]}','{k[3]}','{k[4]}','{k[5]}','{k[6]}','{k[7]}','{k[8]}','{k[9]}','{k[10]}','{k[11]}');";
                        tran.Execute(sql);
                    }


                    tran.CommitTransaction();
                }
                catch (Exception e)
                {
                    tran.RollbackTransaction();
                    throw new Exception($"更新{symbol}_{interval} k线失败", e);
                }
            }
        }