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(); }