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);
}
}
/// <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);
}
}
}