/// <summary>
/// 如果解析数据并且插入成功，则将Status标记为1,作为事务使用
/// </summary>
/// <returns></returns>
public static string GetUpdateStatusSQL(SourceDataHeader header)
{
string sql = string.Format(@"update GatewayData set Status=1 where BuildID='{0}' and GatewayID = '{1}'
and CollectTime='{2}' and DatagramType='{3}';", header.BuildID, header.GatewayID,
header.CollectTime, header.DatagramType);
return(sql);
}
/// <summary>
/// 事务：将实时数据按照要求存入mysql数据库中，存储成功，则更新原始数据
/// </summary>
/// <param name="meters"></param>
/// <param name="header"></param>
public static void ExecuteInsertTransactions(MeterList meters,SourceDataHeader header)
{
List<string> insertSqls = Energy.Common.DAL.MySQLHelper.GetInsertSqls(GetEnergyData(meters),GetVoltageData(meters),
GetCurrentData(meters),GetPowerData(meters),GetBaseElecData(meters));
int count = 0;
MySqlConnection connection = new MySqlConnection(Runtime.MySqlConnectString);
SQLiteConnection sqliteConnection = new SQLiteConnection("Data Source=TempData;Version=3;");
SQLiteCommand sQLiteCommand = new SQLiteCommand(SQLiteHelper.GetUpdateStatusSQL(header),sqliteConnection);
connection.Open();
sqliteConnection.Open();
SQLiteTransaction sqliteTrans = sqliteConnection.BeginTransaction();
MySqlTransaction mySqlTrans = connection.BeginTransaction();
try
{
foreach (var sql in insertSqls)
{
MySqlCommand mySqlCommand = new MySqlCommand(sql, connection, mySqlTrans);
count += mySqlCommand.ExecuteNonQuery();
}
count += sQLiteCommand.ExecuteNonQuery();
sqliteTrans.Commit();
mySqlTrans.Commit();
Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "<" + Thread.CurrentThread.ManagedThreadId.ToString() + "> -> " + "事务执行成功，共影响{0}条数据。",count);
}
catch (Exception e)
{
Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "<" + Thread.CurrentThread.ManagedThreadId.ToString() + "> -> " + "事务执行失败，正在回滚操作，失败内容为：{0}。",e.Message);
sqliteTrans.Rollback();
mySqlTrans.Rollback();
Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "<" + Thread.CurrentThread.ManagedThreadId.ToString() + "> -> " + "事务回滚成功！");
}
finally
{
sQLiteCommand.Connection.Close();
connection.Close();
Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "<" + Thread.CurrentThread.ManagedThreadId.ToString() + "> -> " + "已关闭数据库连接！");
}
}
public static SourceData GetSourceByHeader(SourceDataHeader header)
{
string sql = "select * from {0}" + string.Format(@" where BuildID='{0}'
and GatewayID='{1}' and CollectTime='{2}'
and DatagramType='{3}' and Status={4};", header.BuildID, header.GatewayID, header.CollectTime, header.DatagramType, header.Status);
SourceData sourceData = new SourceData();
SQLiteDataReader reader = ExecuteReader("TempData", "GatewayData", sql);
while (reader.Read())
{
sourceData.BuildID = reader["BuildID"].ToString();
sourceData.GatewayID = reader["GatewayID"].ToString();
sourceData.CollectTime = reader["CollectTime"].ToString();
sourceData.DatagramType = reader["DatagramType"].ToString();
sourceData.Status = Convert.ToInt32(reader["Status"]);
sourceData.JsonData = reader["JsonData"].ToString();
}
reader.Close();
return(sourceData);
}
public static List <SourceDataHeader> GetUnStoreList()
{
string sql = @"select BuildID,GatewayID,CollectTime,DatagramType,Status from {0} where Status=0 order by CollectTime ASC limit 0,1000;";
SQLiteDataReader reader = ExecuteReader("TempData", "GatewayData", sql);
List <SourceDataHeader> headerList = new List <SourceDataHeader>();
while (reader.Read())
{
SourceDataHeader header = new SourceDataHeader();
header.BuildID = reader["BuildID"].ToString();
header.GatewayID = reader["GatewayID"].ToString();
header.CollectTime = DateTime.Parse(reader["CollectTime"].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
header.DatagramType = reader["DatagramType"].ToString();
header.Status = Convert.ToInt32(reader["Status"]);
headerList.Add(header);
}
reader.Close();
return(headerList);
}
