private string UpdateSetting(TableSetting setting, Table table)
{
string data = string.Format("ConfigName={0}&StartValue={1}&EndValue={2}&LastValue={3}&Truncate={4}",
setting.ConfigName, setting.StartValue, setting.EndValue, setting.LastValue, setting.Truncate);
return(APIHelper(table.UpdateSetting, data, table.Name));
}
private TableSetting GetSetting(Table table)
{
string data = string.Format("ConfigName={0}", table.Name);
string result = APIHelper(table.GetSetting, data, table.Name);
JsonSerializer serializer = new JsonSerializer();
TableSetting obj = JsonConvert.DeserializeObject <TableSetting>(result);
return(obj);
}
private string GetData(string query, TableSetting setting, out string minvalue)
{
string data = string.Empty;
minvalue = "";
DataSet ds = new DataSet();
string sError = "";
if (SqlHelper.ExecuteSQLDataSet(ds, query, out sError) > -1)
{
DataTable dt = ds.Tables[0];
if (dt.Rows.Count == 0)
{
return("");
}
else
{
//get max value
minvalue = dt.Rows[dt.Rows.Count - 1][setting.PrimaryKey].ToString();
}
//Get header
foreach (DataColumn col in dt.Columns)
{
data += col.ColumnName + ",";
}
data = data.Substring(0, data.Length - 1) + System.Environment.NewLine;
//Get content of data
foreach (DataRow row in dt.Rows)
{
string content = "";
for (int index = 0; index < dt.Columns.Count; index++)
{
content += "'" + row[index].ToString().Replace("'", "''") + "',";
}
content = content.Length > 0 ? content.Substring(0, content.Length - 1) : content;
data += content + "\n";
}
}
else
{
WriteLog("Read data from " + setting.SourceTable + " Failed : " + sError);
}
return(data);
}
private void ProcessTable(Table table)
{
WriteLog("Started processing table " + table.Name);
TableSetting setting = GetSetting(table);
if (setting.Active)
{
#region Master/Truncate table
if (setting.Truncate)
{
int round = 0;
string minvalue = "";
while (true)
{
string query = string.Format("SELECT TOP {0} {1} FROM {2} WHERE 1 = 1 {3} ORDER BY {4} ASC",
setting.RowPerRound, //0
setting.Columns, //1
setting.SourceTable, //2
round == 0 ? "" : " AND " + setting.PrimaryKey + " > '" + minvalue + "'", //3
setting.PrimaryKey //4
);
string currentmaxvalue = "";
string data = GetData(query, setting, out currentmaxvalue);
if (!string.IsNullOrEmpty(data))
{
PostDataToAPI(table.API, data, round == 0, table.Name, round);
round++;
minvalue = currentmaxvalue;
}
else
{
break;
}
}
string response = UpdateSetting(setting, table);
WriteLog("Update setting (" + table.Name + "): " + response);
}
#endregion
#region Event table - get config from server
else
{
int round = 0;
string minvalue = "";
while (true)
{
string query = string.Format("SELECT TOP {0} {1} FROM {2} WHERE 1 = 1 {3} {4} ORDER BY {5} ASC",
setting.RowPerRound, //0
setting.Columns, //1
setting.SourceTable, //2
round == 0 ? //3
(string.IsNullOrEmpty(setting.LastValue) ? "" :
" AND " + setting.PrimaryKey + " > '" + setting.LastValue + "'") :
" AND " + setting.PrimaryKey + " > '" + minvalue + "'",
" AND (" + setting.WhereColumn + " BETWEEN '" + setting.StartValue + "' AND '" + setting.EndValue + "'" + ")", //4
setting.PrimaryKey); //5
string currentmaxvalue = "";
string data = GetData(query, setting, out currentmaxvalue);
if (!string.IsNullOrEmpty(data))
{
PostDataToAPI(table.API, data, false, table.Name, round);
round++;
minvalue = currentmaxvalue;
}
else
{
break;
}
}
//Update setting back to server
setting.StartValue = setting.EndValue;
setting.LastValue = FormatData(minvalue);
int number = 0;
if (int.TryParse(setting.EndValue, out number)) //if number
{
setting.EndValue = (number + setting.Sequence).ToString();
}
else //if a date
{
DateTime dt;
if (DateTime.TryParse(setting.EndValue, out dt))
{
setting.EndValue = dt.AddHours(setting.Sequence).ToString("yyyy-MM-dd HH:mm:ss");
}
}
string response = UpdateSetting(setting, table);
WriteLog("Update setting (" + table.Name + "): " + response);
}
#endregion
}
WriteLog("End of processing table " + table.Name);
}