using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Timers;
using System.Configuration;
using System.Threading;
using SrvCommon;
using MySql.Data.MySqlClient;
namespace SrvTracDataCopier
{
public static class ServiceCfg
{
public static string TableName = "trackerlog1";
public static string LogConnectionString = "";
public static string SrvConnectionString = "";
public static int Interval = 10;
public static int MaxBulkRow = 100;
public static long Initial_ID = 49298987;
static ServiceCfg()
{
try { TableName = ConfigurationManager.AppSettings["table_name"].ToString(); }
catch { }
try { Interval = int.Parse(ConfigurationManager.AppSettings["interval"]) * 1000; }
catch { }
try { Initial_ID = long.Parse(ConfigurationManager.AppSettings["initial_id"]); }
catch { }
try { LogConnectionString = ConfigurationManager.ConnectionStrings["db_tracker"].ToString(); }
catch { }
try { SrvConnectionString = ConfigurationManager.ConnectionStrings["db_Srvtrac"].ToString(); }
catch { }
}
}
public partial class Service1 : ServiceBase
{
private System.Timers.Timer timer1 = null;
private string table_name = "";
private MySqlConnection db_trk = null;
private MySqlConnection db_Srv = null;
public Service1()
{
InitializeComponent();
}
protected override void OnStart(string[] args)
{
// on restart, lets copy most recent record from TRACKERLOG so that it doesnt need to copy all old data,
// since copying all lost data takes a VERY LONG time. We don't need all old data in staging anyways.
if (Copy_LastRoutine())
{
timer1 = new System.Timers.Timer();
this.timer1.Interval = ServiceCfg.Interval;
this.timer1.Elapsed += new System.Timers.ElapsedEventHandler(this.timer1_Tick);
this.timer1.Enabled = true;
this.table_name = ServiceCfg.TableName;
SrvLogger.LogInfoLine("SrvTracDataCopier Started");
}
}
protected override void OnStop()
{
this.timer1.Enabled = false;
SrvLogger.LogInfoLine("SrvTracDataCopier Stopped");
}
/// <summary>
/// Create and open database connection.
/// </summary>
private MySqlConnection CreateConnection(string conn_key)
{
try
{
MySqlConnection dbConn = new MySqlConnection(conn_key);
dbConn.Open();
return dbConn;
}
catch (Exception ex)
{
SrvLogger.LogErrorLine("Exception: CannotOpenDbConn - {0}", ex.Message);
}
return null;
}
public void timer1_Tick(object sender, ElapsedEventArgs e)
{
try
{
this.timer1.Enabled = false;
}
catch
{
// TRY/CATCH to circumvent NUnit
}
try
{
this.db_trk = CreateConnection(ServiceCfg.LogConnectionString);
this.db_Srv = CreateConnection(ServiceCfg.SrvConnectionString);
if (this.db_trk != null && this.db_Srv != null)
{
int nRet = Copy_MainRoutine();
SrvLogger.LogInfoLine("Inserted {0} Rows.", nRet);
}
}
catch (MySqlException ex)
{
SrvLogger.LogErrorLine("MySqlException: {0}", ex.Message);
}
catch (Exception ex)
{
SrvLogger.LogErrorLine("Exception: {0}", ex.Message);
}
finally
{
if (this.db_trk != null && this.db_trk.State == ConnectionState.Open)
this.db_trk.Close();
if (this.db_Srv != null && this.db_Srv.State == ConnectionState.Open)
this.db_Srv.Close();
}
try
{
this.timer1.Enabled = true;
}
catch
{
// TRY/CATCH to circumvent NUnit
}
}
protected long GetMaxIdFromDB(MySqlConnection dbConn)
{
using (MySqlCommand dbCmd = dbConn.CreateCommand())
{
dbCmd.CommandText = string.Format("SELECT MAX(ID) FROM {0};", ServiceCfg.TableName);
dbCmd.CommandType = CommandType.Text;
using (MySqlDataReader dbReader = dbCmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (dbReader.Read())
{
return dbReader.GetInt64(0);
}
}
}
return 0;
}
protected bool Copy_LastRoutine()
{
int nRet = 0;
try
{
this.db_trk = CreateConnection(ServiceCfg.LogConnectionString);
this.db_Srv = CreateConnection(ServiceCfg.SrvConnectionString);
long nMaxSource = GetMaxIdFromDB(this.db_trk);
DataTable dt = new DataTable();
string szQuery = string.Format("SELECT * FROM `{0}` WHERE `ID` = '{1}';",
ServiceCfg.TableName, nMaxSource);
using (MySqlCommand dbCmd = this.db_trk.CreateCommand())
{
dbCmd.CommandText = szQuery;
dbCmd.CommandType = CommandType.Text;
using (MySqlDataAdapter dbAdp = new MySqlDataAdapter(dbCmd))
{
dbAdp.Fill(dt);
}
}
if (dt.Rows.Count == 1)
{
string hex = "0x" + SrvUtil.ByteArrayToHexString((byte[])dt.Rows[0]["Datagram"]);
DateTime tm = (DateTime)dt.Rows[0]["RecvTime"];
szQuery = string.Format("INSERT INTO {0} (`ID`,`DevModel`,Datagram,`RecvTime`) VALUES ('{1}', '{2}', {3}, '{4}');",
ServiceCfg.TableName, dt.Rows[0]["ID"], dt.Rows[0]["DevModel"], hex, tm.ToString("yyyy-MM-dd HH:mm:ss.fff"));
using (MySqlCommand dbCmd = this.db_Srv.CreateCommand())
{
dbCmd.CommandText = szQuery;
dbCmd.CommandType = CommandType.Text;
nRet = dbCmd.ExecuteNonQuery();
}
}
}
finally
{
this.db_trk.Close();
this.db_Srv.Close();
}
return (nRet > 0 ? true : false);
}
protected int Copy_MainRoutine()
{
int nRet = 0;
long nMaxSource = GetMaxIdFromDB(this.db_trk);
long nMaxTarget = GetMaxIdFromDB(this.db_Srv);
if (nMaxTarget == 0)
nMaxTarget = ServiceCfg.Initial_ID;
for (long nIndex = nMaxTarget; nIndex < nMaxSource; nIndex += ServiceCfg.MaxBulkRow)
{
DataTable dt = new DataTable();
string szQuery = string.Format("SELECT `ID`,`DevModel`,`Datagram`,`RecvTime` FROM `{0}` WHERE `ID` > '{1}' AND `ID` <= '{2}';",
ServiceCfg.TableName, nIndex, nIndex + ServiceCfg.MaxBulkRow);
using (MySqlCommand dbCmd = this.db_trk.CreateCommand())
{
dbCmd.CommandText = szQuery;
dbCmd.CommandType = CommandType.Text;
using (MySqlDataAdapter dbAdp = new MySqlDataAdapter(dbCmd))
{
dbAdp.Fill(dt);
}
}
szQuery = string.Format("INSERT INTO {0} (`ID`,`DevModel`,Datagram,`RecvTime`) VALUES ", ServiceCfg.TableName);
int nCnt = 1;
int totalrows = dt.Rows.Count;
foreach (DataRow dr in dt.Rows)
{
string hex = "0x" + SrvUtil.ByteArrayToHexString((byte[]) dr["Datagram"]);
DateTime tm = (DateTime)dr["RecvTime"];
string model = dr["DevModel"].ToString();
// don't copy V07/AT05 stuff, there is just too many
if (nCnt != totalrows && (model == "V07" || model == "AT05"))
{
nCnt++;
continue;
}
// ...
string buf = string.Format("('{0}', '{1}', {2}, '{3}')", dr["ID"], model, hex, tm.ToString("yyyy-MM-dd HH:mm:ss.fff"));
if (nCnt == totalrows)
{
szQuery += string.Format("{0};", buf);
}
else
{
szQuery += string.Format("{0},", buf);
}
nCnt++;
}
if (totalrows > 0)
{
//SrvLogger.ConsoleWriteLine(szQuery);
using (MySqlCommand dbCmd = this.db_Srv.CreateCommand())
{
dbCmd.CommandText = szQuery;
dbCmd.CommandType = CommandType.Text;
nRet += dbCmd.ExecuteNonQuery();
}
}
Thread.Sleep(100);
}
return nRet;
}
}
}