/// <summary>
/// 获取生成Kline/Kline_Raw表的sql
/// </summary>
/// <param name="symbol"></param>
/// <param name="tableType">Kline/Kline_Raw</param>
/// <returns></returns>
internal static string GetTableSql(string symbol, string interval, string tableType, string newName)
{
var sql = $"SELECT sql FROM sqlite_master WHERE type='table' and tbl_name = '{tableType}'";
var sqlT = DapperUtil.GetScalar <string>(DataBaseType.SQLITE, ConstVar.Conn, sql);
return(sqlT.Replace(tableType, newName));
}
private void button4_Click(object sender, EventArgs e)
{
var conn = System.Configuration.ConfigurationManager.ConnectionStrings["MSSQL"].ConnectionString;
var sql = "select * from monitoring_rx_3301_201911";
//var table = DapperExReader.GetTable(DataBaseType.MSSQL, conn, sql);
//var table = DapperExReader.GetTable("MSSQL", sql);
var instance = DapperUtil.GetScalar <string>(DataBaseType.MSSQL, conn, "select top 1 HIN_FACILITY_IDENT,* from monitoring_rx_3301_201911");
Console.WriteLine(instance);
}
private void button3_Click(object sender, EventArgs e)
{
var conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySql"].ConnectionString;
var sql = "select * from monitoring_rx_3301_201911";
//var table = DapperExReader.GetTable(DataBaseType.MYSQL, conn, sql);
//var table = DapperExReader.GetTable("MySql", sql);
//var instance = DapperExReader.GetInstances<monitoring_rx>("MySql", sql);
//var instance = DapperExReader.GetInstance("MySql", sql);
//var instance = DapperExReader.GetInstance<monitoring_rx>(DataBaseType.MYSQL, conn, "0d3925fb-ae82-4c96-8f57-c131239a9564", "monitoring_rx_3301_201912");
var scalar = DapperUtil.GetScalar <int>(DataBaseType.MYSQL, conn, "select count(*) from monitoring_rx_3301_201911");
//dgv.DataSource = instance;
Console.WriteLine(scalar);
}
/// <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);
}
}
}