public static void UpdateStatus(string RecordID, string status = "", string process = "", string remark = "")
{
string sql;
if (!Program.CanUpdateStatus)
{
Console.WriteLine(process + " " + remark);
return;
}
try
{
if (process != "")
{
sql = "update datacake.sync_datasource set ToCLStatus = " + status + " where id = " + RecordID;
MysqlClass.ExecuteSQL(sql, connectstring);
}
if (status != "")
{
sql = "update datacake.sync_datasource set ToCLProcess = '" + process + "' where id = " + RecordID;
MysqlClass.ExecuteSQL(sql, connectstring);
}
if (remark != "")
{
sql = "update datacake.sync_datasource set ToCLRemark = '" + remark + "' where id = " + RecordID;
MysqlClass.ExecuteSQL(sql, connectstring);
}
}
catch (Exception e)
{
Logging.WriteLog("SQLERROR", e.Message + "\r\n");
}
}
static void MainThread()
{
try
{
//check status db
if (!StatusRefresh.CheckStatusDBConnect(projectid))
{
Console.WriteLine("Can not connect datacake db or project ID is not exists! The process status will not update!");
}
else
{
CanUpdateStatus = true;
}
//init target db;
MysqlClass.InitDatabasePara(DataBaseName);
if (MysqlClass.ConnectDb() == null)
{
Console.WriteLine("Can not connect target database,please check setting! press any key to exit!");
Logging.LogStop();
return;
}
//check logfile
string logfoldname = ConfigurationManager.AppSettings["LogFoldName"];
string folderpath = logpath + "\\" + logfoldname;
DirectoryInfo di = new DirectoryInfo(folderpath);
if (!di.Exists)
{
Console.WriteLine("Wrong Path, Try To Use Application Current Path!");
DirectoryInfo cdi = new DirectoryInfo(AppDomain.CurrentDomain.BaseDirectory + "LogFoldName");
if (!cdi.Exists)
{
Console.WriteLine("Wrong Path, application will close!");
Logging.LogStop();
return;
}
}
CSVToDB.Process(folderpath, projectid, DataBaseName);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
Logging.LogStop();
}
Logging.LogStop();
}
public static Boolean CheckStatusDBConnect(string RecordID)
{
try
{
if (MysqlClass.ConnectDb(connectstring) == null)
{
return(false);
}
string sql = "select id,ToCLStatus, ToCLProcess, ToCLRemark from datacake.sync_datasource where id = " + RecordID;
DataTable dt = MysqlClass.GetDataTable(sql, "recordtable", connectstring);
if (dt.Rows.Count > 0)
{
return(true);
}
}
catch (Exception e)
{
Logging.WriteLog("SQLERROR", e.Message + "\r\n");
return(false);
}
return(false);
}
private static void WriteRecordToDB(string tablename, DataTable dt, string TargetDBName)
{
try
{
Dictionary <string, string> tabletype = new Dictionary <string, string>();
MysqlClass.GetColNameType(TargetDBName, tablename, ref tabletype);
if (tabletype.Count == 0)
{
return;
}
foreach (DataRow row in dt.Rows)
{
string sql = "insert into " + TargetDBName + "." + tablename + "(";
string columnslist = "";
string valuelist = "";
string value;
foreach (DataColumn c in dt.Columns)
{
if (tabletype.ContainsKey(c.ColumnName))
{
columnslist += c.ColumnName + ",";
//
if (tabletype[c.ColumnName].ToUpper().IndexOf("INT") >= 0 || tabletype[c.ColumnName].ToUpper().IndexOf("DEC") >= 0)
{
value = row[c.ColumnName].ToString();
if (value == "?")
{
valuelist += "null,";
}
else
{
valuelist += value + ",";
}
continue;
}
//char,varchar
if (tabletype[c.ColumnName].ToUpper().IndexOf("CHAR") >= 0)
{
value = row[c.ColumnName].ToString();
if (value == "?")
{
valuelist += "null,";
}
else
{
valuelist += "'" + value + "'" + ",";
}
continue;
}
//datetime
if (tabletype[c.ColumnName].ToUpper() == "DATATIME")
{
value = row[c.ColumnName].ToString();
if (value == "?")
{
valuelist += "null,";
}
else
{
try
{
var datetime = DateTime.Parse(value);
value = datetime.ToLongDateString() + " " + datetime.ToLongTimeString();
}
catch
{
value = "null";
continue;
}
valuelist += "'" + value + "'" + ",";
}
continue;
}
//date
if (tabletype[c.ColumnName].ToUpper() == "DATA")
{
value = row[c.ColumnName].ToString();
if (value == "?")
{
valuelist += "null,";
}
else
{
try
{
var datetime = DateTime.Parse(value);
value = datetime.ToLongDateString();
}
catch
{
valuelist += "null,";
continue;
}
valuelist += "'" + row[c.ColumnName].ToString() + "'" + ",";
}
continue;
}
}
}
columnslist = columnslist.Substring(0, columnslist.Length - 1);
valuelist = valuelist.Substring(0, valuelist.Length - 1);
string excutesql = sql + columnslist + ") values(" + valuelist + ")";
MysqlClass.ExecuteSQL(excutesql);
//if (tablename.ToUpper() == "cl_result".ToUpper())
//{
// string recordid = GetResultID(row);
//}
}
}
catch (Exception ex)
{
Logging.WriteLog("SystemError in Class CSVToDB", ex.Message);
}
}
private static void CleanDB(string targetdbname, string tablename)
{
string sql = "delete from " + targetdbname + "." + tablename;
MysqlClass.ExecuteSQL(sql);
}
private static void BulkWriteRecordToDB(string tablename, DataTable dt, string TargetDBName)
{
try
{
Dictionary <string, string> tabletype = new Dictionary <string, string>();
MysqlClass.GetColNameType(TargetDBName, tablename, ref tabletype);
if (tabletype.Count == 0)
{
return;
}
StringBuilder sqlbuilder = new StringBuilder();
sqlbuilder.Append("insert into " + TargetDBName + "." + tablename + "(");
string sqlcolumns = "";
string columnnamelist = "";
string[] columnarry;
foreach (DataColumn c in dt.Columns)
{
if (tabletype.ContainsKey(c.ColumnName))
{
sqlcolumns += "`" + c.ColumnName + "`" + ",";
columnnamelist += c.ColumnName + ",";
}
}
sqlcolumns = sqlcolumns.Substring(0, sqlcolumns.Length - 1);
columnnamelist = columnnamelist.Substring(0, columnnamelist.Length - 1);
columnarry = columnnamelist.Split(',');
sqlbuilder.Append(sqlcolumns + ") ");
if (columnarry.Length > 0)
{
StringBuilder sbvalues = new StringBuilder();
sbvalues.Append(" value ");
if (dt.Rows.Count > 0)
{
Logging.WriteLog("TableProcessing ", " Process " + tablename + " " + dt.Rows.Count.ToString() + " " + " Rows");
foreach (DataRow row in dt.Rows)
{
StringBuilder sbsinglevalus = new StringBuilder();
sbsinglevalus.Append("(");
foreach (string colna in columnarry)
{
string value;
if (tabletype[colna].ToUpper().IndexOf("INT") >= 0 || tabletype[colna].ToUpper().IndexOf("DEC") >= 0)
{
value = row[colna].ToString();
if (value == "?")
{
sbsinglevalus.Append("null,");
}
else
{
sbsinglevalus.Append(value + ",");
}
continue;
}
//char,varchar
if (tabletype[colna].ToUpper().IndexOf("CHAR") >= 0)
{
value = row[colna].ToString();
if (value == "?")
{
sbsinglevalus.Append("null,");
}
else
{
sbsinglevalus.Append("'" + value + "'" + ",");
}
continue;
}
//datetime
if (tabletype[colna].ToUpper() == "DATETIME")
{
value = row[colna].ToString();
if (value == "?")
{
sbsinglevalus.Append("null,");
}
else
{
//try
//{
// var datetime = DateTime.Parse(value);
// value = datetime.ToLongDateString() + " " + datetime.ToLongTimeString();
//}
//catch
//{
// value = "null";
// continue;
//}
sbsinglevalus.Append("'" + value + "'" + ",");
}
continue;
}
//date
if (tabletype[colna].ToUpper() == "DATE")
{
value = row[colna].ToString();
if (value == "?")
{
sbsinglevalus.Append("null,");
}
else
{
//try
//{
// var datetime = DateTime.Parse(value);
// value = datetime.ToLongDateString();
//}
//catch
//{
// sbsinglevalus.Append("null,");
// continue;
//}
sbsinglevalus.Append("'" + value + "'" + ",");
}
continue;
}
}
string ssinglevalue = sbsinglevalus.ToString();
ssinglevalue = ssinglevalue.Substring(0, ssinglevalue.Length - 1) + "),";
sbvalues.Append(ssinglevalue);
if (sbvalues.Length > 300000)
{
string sqlvalues = sbvalues.ToString();
sbvalues = new StringBuilder();
sbvalues.Append(" value ");
sqlvalues = sqlvalues.Substring(0, sqlvalues.Length - 1);
StringBuilder sbtemp = new StringBuilder();
sbtemp.Append(sqlbuilder.ToString() + sqlvalues);
string sqltemp = sbtemp.ToString();
MysqlClass.ExecuteSQL(sqltemp);
}
}
string sqlvalue = sbvalues.ToString();
sqlvalue = sqlvalue.Substring(0, sqlvalue.Length - 1);
sqlbuilder.Append(sqlvalue);
string excutesql = sqlbuilder.ToString();
MysqlClass.ExecuteSQL(excutesql);
}
}
}
catch (Exception ex)
{
Logging.WriteLog("SystemError in Class CSVToDB", ex.Message);
Logging.WriteLog("TableProcessing", "Process " + tablename + " Error");
}
Logging.WriteLog("TableProcessing", "Process " + tablename + " Done");
ddt[tablename].Clear();
GC.Collect();
}