using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Drawing; using DataLibrary; using System.Data; using System.Diagnostics; using System.Security; using System.IO; namespace Saving.DataCenter { public partial class dbms : System.Web.UI.Page { public string ExecuteCmd(string Arguments, string user, string password, string domain) { return ExecuteCommand("cmd", Arguments, user, password, domain); } public string ExecuteCommand(string command, string Arguments, string user, string password, string domain) { string output = null; Process p = new Process(); ProcessStartInfo s = new ProcessStartInfo(); if (domain != null || domain != "") s.Domain = domain; if (user != null || user != "") s.UserName = user; if (password != null || password != "") { s.Password = new SecureString(); char[] passwords = password.ToCharArray(); for (int i = 0; i < password.Length; i++) { s.Password.AppendChar(passwords[i]); } } s.FileName = command; s.UseShellExecute = false; s.RedirectStandardOutput = true; s.RedirectStandardError = true; s.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden; if (Arguments != null && Arguments != "") s.Arguments = "/C \"" + Arguments+"\""; p.StartInfo = s; p.EnableRaisingEvents = true; try { p.Start(); while (!p.HasExited) { System.Threading.Thread.Sleep(1000); } //check to see what the exit code was if (p.ExitCode != 0) { output = "Exitcode: " + p.ExitCode + " - Err1: " + p.StandardError + " - Executor: " + System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString(); } else { output = "Command Result: " + p.StandardOutput.ReadToEnd(); } } catch (Exception ex) { output += ex.Message; } return output; } String root_drive = "C"; protected void Page_Load(object sender, EventArgs e) { bool loadConfig =false; try{ loadConfig=(Session["LoadConfig"]==null?true:false); Session["LoadConfig"] = false; }catch{ loadConfig=true; } if (loadConfig) { this.root_drive = HttpContext.Current.Request.MapPath("~/").Substring(0, 1); listtable = root_drive + listtable_; listconfig = root_drive + listconfig_; String datasource = "Data Source=coopdb/orcl;Persist Security Info=True;User ID=scopsm;Password=scopsm;Unicode=False;"; String datasource_ = "Data Source=coopdb/orclcenter;Persist Security Info=True;User ID=scomsv;Password=scomsv;Unicode=False;"; datasource = (Request["d"] != null) ? Request["d"] : datasource; datasource_ = (Request["d_"] != null) ? Request["d_"] : datasource_; if (TbConnectionString.Text == null || TbConnectionString.Text.Trim() == "") { TbConnectionString.Text = datasource; } if (TbConnectionStringCen.Text == null || TbConnectionStringCen.Text.Trim() == "") { TbConnectionStringCen.Text = datasource_; } this.LoadConfig(); } string[] tables =null; if (this.TbTableLists.Items.Count == 0) { tables = this.getTablesLists(); for (int i = 0; i < tables.Length; i++) { ListItem item = new ListItem(); item.Text = tables[i].Trim(); item.Value = tables[i].Trim(); this.TbTableLists.Items.Add(item); } } if (this.dbLinkField.Text == "" && this.SyncWithDBLinkField.SelectedValue == "1") { this.dbLinkField.Text = "DBLINK"; } if (this.SyncWithDBLinkField.SelectedValue == "0") { this.dbLinkField.Text = ""; } this.getConnectionDetail(); if (this.TbUserOwner.Text == "") { this.TbUserOwner.Text = this.dbCenterUsr; } LbServerMessage.Text = ""; GridView1.DataSource = null; GridView1.DataBind(); //ตรวจสอบการทำรายการต่อเนื่อง try { if (this.DropDownList1.SelectedValue == "STEP_022"&&this.CbxAuto.Checked) { if (Session["t"] == null || Session["t"] == "") { Session["t"] = tables[0]; this.TbTableLists.SelectedValue = Session["t"].ToString(); } else { ListItemCollection item = this.TbTableLists.Items; bool found = false; for (int i = 0; i < item.Count; i++) { if (item[i].Value == Session["t"]) { found=true; Session["t"]="N/A"; } else if (found && Session["t"] == "N/A") { Session["t"] = item[i].Value; this.TbTableLists.SelectedValue = Session["t"].ToString(); } } } } } catch { } } protected void Button1_Click(object sender, EventArgs e) { LbOutput.Text = ""; string connectionString = TbConnectionString.Text; Sta ta = new Sta(connectionString); ta.Transection(); try { string sql = TbSQL.Text.Trim(); if (sql.ToLower().IndexOf("select") >= 0) { DataTable dt = ta.QueryDataTable(sql); if (dt != null) { GridView1.DataSource = dt; GridView1.DataBind(); LbServerMessage.Text = "ข้อมูล = " + dt.Rows.Count + " row"; LbServerMessage.ForeColor = Color.Green; } else { LbServerMessage.Text = "ไม่พบข้อมูล"; LbServerMessage.ForeColor = Color.Red; } } else { ta.Exe(sql); LbServerMessage.Text = "ทำรายการสำเร็จ"; LbServerMessage.ForeColor = Color.Green; } ta.Commit(); ta.Close(); } catch (Exception ex) { try { ta.RollBack(); } catch { } ta.Close(); LbServerMessage.Text = ex.Message; LbServerMessage.ForeColor = Color.Red; } } protected void Button2_Click(object sender, EventArgs e) { LbOutput.Text = ""; string connectionString = TbConnectionString.Text; Sta ta = new Sta(connectionString); ta.Transection(); try { string sql = TbSQL.Text.Trim(); ta.Exe(sql); ta.Commit(); ta.Close(); LbServerMessage.Text = "ทำงานเสร็จ"; LbServerMessage.ForeColor = Color.Green; } catch (Exception ex) { try { ta.RollBack(); } catch { } ta.Close(); LbServerMessage.Text = ex.Message; LbServerMessage.ForeColor = Color.Red; } } protected void Button3_Click(object sender, EventArgs e) { LbOutput.Text = ""; string connectionString = TbConnectionString.Text; Sta ta = new Sta(connectionString); ta.Transection(); try { string sql = TbSQL.Text.Trim(); ta.ExePlSql(sql); ta.Commit(); ta.Close(); LbServerMessage.Text = "ทำงานเสร็จ"; LbServerMessage.ForeColor = Color.Green; } catch (Exception ex) { try { ta.RollBack(); } catch { } ta.Close(); LbServerMessage.Text = ex.Message; LbServerMessage.ForeColor = Color.Red; } } protected void Button4_Click(object sender, EventArgs e) { LbOutput.Text = ""; string cmd = TbSQL.Text.Trim(); LbServerMessage.Text =this.ExecuteCmd(cmd, "", "", ""); LbServerMessage.ForeColor = Color.Green; } public void RestartIreportBuilder() { try { Process[] process = System.Diagnostics.Process.GetProcessesByName("javaw"); for (int i = 0; i < process.Length; i++) { process[i].Kill(); } //System.Diagnostics.Process.Start("C:\\GCOOP_ALL\\FSCT\\GCOOP\\run_ireport_builder.bat"); } catch { } this.ExecuteCmd("taskkill /F /IM javaw.exe", "", "", ""); System.Diagnostics.Process.Start(root_drive+":\\GCOOP_ALL\\FSCT\\GCOOP\\run_ireport_builder.bat"); /* string args = ""; string str2 = "C:\\GCOOP_ALL\\FSCT\\0.ResetIreportBuilder.bat"; ProcessStartInfo startinfo = new ProcessStartInfo(str2, args); startinfo.CreateNoWindow = true; startinfo.UseShellExecute = false; startinfo.WorkingDirectory = "C:\\GCOOP_ALL\\FSCT"; // *** Redirect the output *** startinfo.RedirectStandardError = true; startinfo.RedirectStandardOutput = true; Process process; process = Process.Start(startinfo); process.WaitForExit(); //string stdinput = process.StandardOutput.ReadToEnd(); //string stdoutput = process.StandardError.ReadToEnd(); int ecode = process.ExitCode; LbServerMessage.ForeColor = Color.Green; //LbServerMessage.Text = (string)ecode+""; //+":
" + stdoutput; */ } protected void Button45_Click(object sender, EventArgs e) { LbOutput.Text = ""; RestartIreportBuilder(); } private void createDataDic() { string v_flag = Request["v"]; LbOutput.Text = ""; string connectionString = TbConnectionString.Text; Sta ta = new Sta(connectionString); Sta ta_ = new Sta(connectionString); string output = ""; int i = 1; try { string sql = "select tname from tab where ( tname not like '%$%' and tname not like '%PB%' ) and lower(tname) like lower('" + TbSQL.Text + "') order by tname asc"; Sdt dt = ta.Query(sql); if (dt != null) { while (dt.Next()) { string table_name = dt.GetString(0); // output += " "; sql = @"SELECT column_name,data_type, data_length,DATA_PRECISION,DATA_SCALE,nullable , " + " (SELECT decode(A.CONSTRAINT_TYPE,'P','PK','') as pk FROM USER_CONSTRAINTS A, USER_CONS_COLUMNS B WHERE A.TABLE_NAME = B.TABLE_NAME AND B.TABLE_NAME = C.table_name AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME and A.CONSTRAINT_TYPE in ('P') and c.column_name =b.column_name and rownum =1 ) as IS_PK , " + " (SELECT decode(A.CONSTRAINT_TYPE,'R','FK','') as fk FROM USER_CONSTRAINTS A, USER_CONS_COLUMNS B WHERE A.TABLE_NAME = B.TABLE_NAME AND B.TABLE_NAME = C.table_name AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME and A.CONSTRAINT_TYPE in ('R') and c.column_name =b.column_name and rownum =1 ) as IS_FK " + " , (select d.cdesc from CMTABCOLS d where lower(c.table_name) =lower(d.tname) and lower(c.column_name)=lower(d.cname) ) as cdesc " + " , (select d.tdesc from CMTABSYS d where lower(c.table_name) =lower(d.tname) ) as tdesc " + " , (select d.from_system from CMTABSYS d where lower(c.table_name) =lower(d.tname) ) as from_system " + ", c.table_name " + "FROM USER_TAB_COLUMNS c WHERE lower(c.table_name) = lower('" + table_name + "') order by c.table_name asc , IS_PK asc, IS_FK asc"; Sdt dt_ = ta_.Query(sql); int j = 1; output += ""; while (dt_.Next()) { string cols_name = dt_.GetString(0); output += ""; output += ""; output += ""; output += ""; //output+=" "; //select constraint_name,constraint_type from all_constraints where constraint_type in ('P','U','R') and table_name='' output += ""; j++; } /* sql = "select distinct constraint_name,DECODE(constraint_type ,'P','PK','U','UNQ','R','FK','') as constraint_type_ ,constraint_type from all_constraints where constraint_type in ('P','U','R') and table_name='" + table_name + "' order by constraint_type asc"; dt_ = ta_.Query(sql); int k = 1; while (dt_.Next()) { if (k == 1) { output += " "; } output += ""; k++; } //output+=""; */ i++; } } //LbServerMessage.Text = "ทำงานเสร็จ"; //LbServerMessage.ForeColor = Color.Green; output += "
"+i+"."+table_name+"
Table Name Table Column Name Description Data Type
" + (j == 1 ? (table_name.ToLower() + "") : "") + ""; output += (j == 2 ? ((v_flag != null ? dt_.GetString(9) : (""))) : ""); output += (j == 2 ? ((v_flag != null ? dt_.GetString(10) : ( ""))) : ""); output += "" + dt_.GetString(0).ToLower() + (dt_.GetString(6) != "" ? ("(" + dt_.GetString(6) + ")") : "") + (dt_.GetString(7) != "" ? ("(" + dt_.GetString(7) + ")") : "") + "" + (v_flag != null ? dt_.GetString(8) : ("")) + "" + dt_.GetString(1) + ""; if (dt_.GetString(1) != "DATE") { if (dt_.GetString(1) == "NUMBER") { if (dt_.GetString(3) != "" && dt_.GetString(4) != "") { output += "(" + (dt_.GetString(3) + ((dt_.GetString(4) != "0" && dt_.GetString(4) != "") ? ("," + dt_.GetString(4)) : "")) + ")"; } else { output += "(" + dt_.GetString(2) + ")"; } } else { output += "(" + dt_.GetString(2) + ")"; } } output += ""+ dt_.GetString(5)+"
constraint
" + k + "." + dt_.GetString(0) + "" + dt_.GetString(1) + "
"; LbOutput.Text = output; //TbSQL.Text = output; } catch (Exception ex) { LbServerMessage.Text = ex.Message; LbServerMessage.ForeColor = Color.Red; } ta_.Close(); ta.Close(); } protected void Button7_Click(object sender, EventArgs e) { this.createDataDic(); } protected void Button8_Click(object sender, EventArgs e) { string connectionString = TbConnectionString.Text; Sta ta = new Sta(connectionString); Sta ta_ = new Sta(connectionString); string dblink = "gcoop_src"; string output = "DROP DATABASE LINK " + dblink + " ;\r\n"; output+=" CREATE DATABASE LINK " + dblink + " CONNECT TO IFSCT IDENTIFIED BY ifsct USING '" + dblink + "';\r\n"; int i = 1; try { string sql = "select tname from tab where ( tname not like '%$%' and tname not like '%PB%' ) and lower(tname) like lower('" + TbSQL.Text + "') order by tname asc"; Sdt dt = ta.Query(sql); if (dt != null) { while (dt.Next()) { string table_name = dt.GetString(0); // output += ""+i+"."+table_name+" "; sql = @"SELECT column_name,nullable FROM USER_TAB_COLUMNS c WHERE lower(c.table_name) = lower('" + table_name + "') "; Sdt dt_ = ta_.Query(sql); int j = 1; output += "delete from " + table_name + ";\r\n"; output += "commit;\r\n"; string cols_names = " "; string cols_names_ = " "; while (dt_.Next()) { string cols_name = dt_.GetString(0); cols_names += (dt_.GetString(1) == "Y" ? "NVL(" : "") + cols_name + (dt_.GetString(1) == "Y" ? ",'')" : "") + ","; cols_names_ += cols_name + ","; j++; } cols_names += ";"; cols_names_ += ";"; cols_names_ = cols_names_.Replace(",;", " "); cols_names = cols_names.Replace(",;", " "); output += " insert into " + table_name + " ( " + cols_names_ + " ) ( \r\n"; output += " select " + cols_names + " from " + table_name + "@" + dblink + " \r\n "; output += " ) ;\r\n "; output += "commit;\r\n"; i++; } } //LbServerMessage.Text = "ทำงานเสร็จ"; //LbServerMessage.ForeColor = Color.Green; //LbOutput.Text = output; TbSQL.Text = output; } catch (Exception ex) { LbServerMessage.Text = ex.Message; LbServerMessage.ForeColor = Color.Red; } ta_.Close(); ta.Close(); } protected void Button6_Click(object sender, EventArgs e) { String values = ""; String table = ""; String from_system = ""; String column = ""; String desc = ""; string connectionString = TbConnectionString.Text; Sta ta = new Sta(connectionString); Sta ta_ = new Sta(connectionString); ta.Transection(); try { foreach (string s in Request.Params.Keys) { if (s.IndexOf("T**") >= 0) { string tmp = s.Replace("T**", ""); string[] tmps = tmp.Split('*'); try { table = tmps[0]; column = tmps[1]; desc = Request.Params[s]; //values += table + ":" + column + "=" + desc + "\r\n"; try { ta.Exe("insert into CMTABCOLS (tname,cname,cdesc) values('" + table + "','" + column + "','" + desc + "')"); } catch { ta.Exe("update CMTABCOLS set cdesc='" + desc + "' where tname='" + table + "' and cname='" + column + "' "); } } catch (Exception em) { values += tmp + "=" + em.Message + "
"; } } if (s.IndexOf("T--") >= 0) { string tmp = s.Replace("T--", ""); string[] tmps = tmp.Split('*'); try { table = tmps[0]; from_system = tmps[1]; desc = Request.Params[s]; //values += table + ":" + column + "=" + desc + "\r\n"; if (s.IndexOf("tdesc") >= 0) { Sdt dt = ta_.Query("select * from CMTABSYS where tname='" + table + "' "); try { if (dt.Next()==false) { ta.Exe("insert into CMTABSYS (tname,tdesc,from_system) values('" + table + "','" + desc + "','ALL')"); } else { ta.Exe("update CMTABSYS set tdesc='" + desc + "' where tname='" + table + "' "); } } catch { } } if (s.IndexOf("from_system") >= 0) { try { ta.Exe("update CMTABSYS set from_system='" + desc + "' where tname='" + table + "' "); } catch { } } } catch (Exception em) { values += tmp + "=" + em.Message + "
"; } } } LbServerMessage.Text = values; LbServerMessage.ForeColor = Color.Red; } catch { } ta.Commit(); ta.Close(); ta_.Close(); this.createDataDic(); LbServerMessage.Text = "สำเร็จ"; LbServerMessage.ForeColor = Color.Green; } protected void Button9_Click(object sender, EventArgs e) { this.TbSQL.Text = this.DropDownList1.SelectedValue; this.Button1_Click(sender, e); } protected string buildTriggerSyncData(string connectionString, string tablenames, string dblink, string dblinkfield, string dblinkfieldValue, string convert, string schema_owner) { string output = ""; Sta ta = new Sta(connectionString); Sta ta_ = new Sta(connectionString); string user=dblink; dblink = dblinkfieldValue; int i = 1; try { string sql = "select tname from tab where ( tname not like '%$%' and tname not like '%PB%' ) and lower(tname) like lower('" + tablenames.Replace(" ", "") + "') order by tname asc"; Sdt dt = ta.Query(sql); if (dt != null) { while (dt.Next()) { string table_name = dt.GetString(0); //CREATE OR REPLACE DIRECTORY DATAPUMP AS '" + dbDataPump.Text+ @"'; output += @" create or replace TRIGGER " + table_name + @"_" + dblink + @" AFTER DELETE OR INSERT OR UPDATE ON " + table_name + @" for each row DECLARE ACT char(1); v_filehandle UTL_FILE.FILE_TYPE; v_curDate DATE; v_filename varchar2(150); v_pk varchar2(30); Procedure doPutLog is begin select to_char(PK_TRIGGER_FILENM.nextval) into v_pk from dual; v_curDate :=sysdate; v_filename :=('" + this.dbCenterUsr + "_" + table_name + @"_'||to_CHAR(v_curDate,'yyyyMMddHH24MISSsss')||v_pk||'.sql'); v_filehandle := UTL_FILE.FOPEN ('DATAPUMP',v_filename,'A',32767); if INSERTING then ACT := 'I'; "; sql = @"select * from ( select c.column_name, c.data_type, c.data_length,( SELECT ac.constraint_name FROM all_constraints ac JOIN all_cons_columns acc ON (ac.CONSTRAINT_NAME = acc.CONSTRAINT_NAME) JOIN all_tab_cols atc ON ( lower( ac.owner) = lower('" + schema_owner + @"') and ac.owner = atc.owner AND ac.table_name = atc.TABLE_NAME AND acc.COLUMN_NAME = atc.COLUMN_NAME) WHERE ac.constraint_type = 'P' and ac.table_name = c.table_name and acc.COLUMN_NAME=c.column_name and ROWNUM=1) as constraint_name from user_tab_columns c where lower(c.table_name) = lower('" + table_name + @"') ) order by constraint_name asc , column_name desc "; Sdt dt_ = ta_.Query(sql); string cols_names = "("; string cols_names_ = "("; string cols_names__ = "("; string cols_names_values = "("; string cols_names_values_pk = ""; string sql_insert = "", sql_delete = "", sql_update = ""; string sql_insert_file = "", sql_delete_file = "", sql_update_file = ""; string cols_convert=""; while (dt_.Next()) { string cols_name = dt_.GetString(0); bool isChar = (dt_.GetString(1).ToUpper().IndexOf("CHAR") >= 0) && convert.Length>0; bool isVarChar = (dt_.GetString(1).ToUpper().IndexOf("VARCHAR") >= 0) && convert.Length > 0; cols_convert=(isChar)?(convert+"Char"):( (isVarChar)?(convert+"VarChar"):"" ) ; if (dt_.GetString(1).ToUpper() != "BLOB") { cols_names += cols_name + ","; cols_names_ += cols_convert + "(:new." + cols_name + "),"; cols_names__ += cols_convert + "(:new." + cols_name + "),"; cols_names_values += "#'||" + cols_convert + "(:new." + cols_name + ")||'#,"; } } cols_names += (dblinkfield != "" ? (dblinkfield + ",") : "") + ")"; cols_names = cols_names.Replace(",)", ")"); cols_names_ += (dblinkfield != "" ? ("'" + dblink + "',") : "") + ")"; cols_names_ = cols_names_.Replace(",)", ")"); cols_names__ += (dblinkfield != "" ? ("'" + dblink + "',") : "") + ")"; cols_names__ = cols_names__.Replace(",)", ")"); cols_names_values += (dblinkfield != "" ? ("#" + dblink + "#,") : "") + ")"; cols_names_values = cols_names_values.Replace(",)", ")"); sql_insert = "insert into " + table_name + @"@" + user + @" "+cols_names + @" values " + cols_names_ + @";"; sql_insert_file = "insert into " + table_name + @"@" + user + @" " + cols_names + @" values " + cols_names__ + @";"; sql_insert_file = "UTL_FILE.PUT_LINE (v_filehandle,'" + sql_insert_file.Replace(("@" + user), "").Replace("'", "''").Replace("(:", "'||(:").Replace(")", ")||'").Replace("||' values", " values").Replace(")||';", ");").Replace("'||(:", "'''||(:").Replace(")||'", ")||'''") + "');"; output += @" " + sql_insert_file + @" UTL_FILE.FCLOSE (v_filehandle); insert into CMLOGSYNCDATA@" + user + @" ( BRANCH_ID,LOG_DATE,""MODE"",TABLE_NM,ORA_DIR,SQL_FILE,""SQL"") values('" + dblink + "',sysdate,'I','" + table_name + "','DATAPUMP',v_filename,'insert into " + table_name + @" " + cols_names.Replace("'", "") + @" values " + cols_names_values + @"'); " + sql_insert + @" elsif DELETING then ACT := 'D'; "; sql = @"select * from ( select c.column_name, c.data_type, c.data_length,NVL(( SELECT ac.constraint_name FROM all_constraints ac JOIN all_cons_columns acc ON (ac.CONSTRAINT_NAME = acc.CONSTRAINT_NAME) JOIN all_tab_cols atc ON ( lower( ac.owner) = lower('" + schema_owner + @"') and ac.owner = atc.owner AND ac.table_name = atc.TABLE_NAME AND acc.COLUMN_NAME = atc.COLUMN_NAME) WHERE ac.constraint_type = 'P' and ac.table_name = c.table_name and acc.COLUMN_NAME=c.column_name and ROWNUM=1),'-') as constraint_name from user_tab_columns c where lower(c.table_name) = lower('" + table_name + @"') ) order by constraint_name asc , column_name desc "; dt_ = ta_.Query(sql); cols_names = ""; cols_names_ = ""; cols_names__ = ""; cols_names_values = ""; while (dt_.Next()) { string cols_name = dt_.GetString(0); string constraint_name = dt_.GetString(3); bool isChar = (dt_.GetString(1).ToUpper().IndexOf("CHAR") >= 0) && convert.Length > 0; bool isVarChar = (dt_.GetString(1).ToUpper().IndexOf("VARCHAR") >= 0) && convert.Length > 0; cols_convert = (isChar) ? (convert + "Char") : ((isVarChar) ? (convert + "VarChar") : ""); if (dt_.GetString(1).ToUpper() != "BLOB") { if (constraint_name != "-") { cols_names += " " + cols_name + "=" + cols_convert + "(:old." + cols_name + ") and"; cols_names_values_pk += " " + cols_name + "=#'||" + cols_convert + "(:old." + cols_name + ")||'# and"; } else { cols_names_ += " " + cols_name + "=" + cols_convert + "(:new." + cols_name + ") ,"; cols_names__ += " " + cols_name + "=" + cols_convert + "(:new." + cols_name + ") ,"; cols_names_values += " " + cols_name + "=#'||" + cols_convert + "(:new." + cols_name + ")||'# ,"; } } } cols_names += (dblinkfield != "" ? (" " + dblinkfield + "='" + dblink + "'" + " and") : "") + ")"; cols_names = cols_names.Replace("and)", " "); cols_names_values_pk += (dblinkfield != "" ? (" " + dblinkfield + "=#" + dblink + "#" + " and") : "") + ")"; cols_names_values_pk = cols_names_values_pk.Replace("and)", " "); cols_names_ += ")"; cols_names_ = cols_names_.Replace(",)", " "); cols_names__ += ")"; cols_names__ = cols_names__.Replace(",)", " "); cols_names_values += ")"; cols_names_values = cols_names_values.Replace(",)", " "); sql_delete = "delete from " + table_name + @"@" + user + @" where " + cols_names + @";"; sql_update = "update " + table_name + @"@" + user + @" set " + cols_names_ + @" where " + cols_names + @";"; sql_delete_file = sql_delete; sql_delete_file = "UTL_FILE.PUT_LINE (v_filehandle,'" + sql_delete_file.Replace(("@" + user),"").Replace("'", "''").Replace("(:", "'||(:").Replace(")", ")||'").Replace("'||(", "'''||(").Replace(")||'", ")||'''") + "');"; sql_update_file = "update " + table_name + @"@" + user + @" set " + cols_names__ + @" where " + cols_names + @";"; sql_update_file = "UTL_FILE.PUT_LINE (v_filehandle,'" + sql_update_file.Replace(("@" + user), "").Replace("'", "''").Replace("(:", "'||(:").Replace(")", ")||'").Replace("'||(", "'''||(").Replace(")||'", ")||'''") + "');"; output += @" " + sql_delete_file + @" UTL_FILE.FCLOSE (v_filehandle); insert into CMLOGSYNCDATA@" + user + @" ( BRANCH_ID,LOG_DATE,""MODE"",TABLE_NM,ORA_DIR,SQL_FILE,""SQL"") values('" + dblink + "',sysdate,'D','" + table_name + "','DATAPUMP',v_filename,'delete from " + table_name + @" where " + cols_names_values_pk + @"'); " + sql_delete + @" elsif UPDATING then ACT := 'U'; " + (this.SyncWithDBLinkField.SelectedValue == "1" ? sql_delete_file : "") + @" " + (this.SyncWithDBLinkField.SelectedValue == "1" ? sql_insert_file : "") + @" " + (this.SyncWithDBLinkField.SelectedValue == "0" ? sql_update_file : "") + @" UTL_FILE.FCLOSE (v_filehandle); insert into CMLOGSYNCDATA@" + user + @" ( BRANCH_ID,LOG_DATE,""MODE"",TABLE_NM,ORA_DIR,SQL_FILE,""SQL"") values('" + dblink + "',sysdate,'U','" + table_name + "','DATAPUMP',v_filename,'update " + table_name + @" set " + cols_names_values + @" where " + cols_names_values_pk + @"'); " + (this.SyncWithDBLinkField.SelectedValue == "1" ? sql_delete : "") + @" " + (this.SyncWithDBLinkField.SelectedValue == "1" ? sql_insert : "") + @" " + (this.SyncWithDBLinkField.SelectedValue == "0" ? sql_update : "") + @" end if; "; /* output += @" v_filehandle := UTL_FILE.FOPEN ('DATAPUMP','RunSQL.bat','w',32767); UTL_FILE.PUT_LINE (v_filehandle,'SET ORA_USR=%1'); UTL_FILE.PUT_LINE (v_filehandle,'SET ORA_PWD=%2'); UTL_FILE.PUT_LINE (v_filehandle,'SET ORA_TARGET_DBSTR=%3'); UTL_FILE.PUT_LINE (v_filehandle,'SET NLS_LANG=AMERICAN_AMERICA.TH8TISASCII'); UTL_FILE.PUT_LINE (v_filehandle,'for /r %%i in (*.sql) do echo quit|sqlplus %ORA_USR%/%ORA_PWD%@%ORA_TARGET_DBSTR% @%%i'); UTL_FILE.PUT_LINE (v_filehandle,'mkdir 0.SQL'); UTL_FILE.PUT_LINE (v_filehandle,'for /r %%i in (*.sql) do move %%i 0.SQL'); UTL_FILE.FCLOSE (v_filehandle); v_filehandle := UTL_FILE.FOPEN ('DATAPUMP','RunSQL-exec.bat','w',32767); UTL_FILE.PUT_LINE (v_filehandle,'@RunSQL.bat " + this.dbCenterUsr + @" " + this.dbCenterPwd + @" "+this.dbCenterHost+@"'); UTL_FILE.FCLOSE (v_filehandle); "; */ output += @" EXCEPTION WHEN others THEN dbms_output.put_line('Error!'); end doPutlog; BEGIN if true then doPutLog; end if; END; / "; i++; } } //LbServerMessage.Text = "ทำงานเสร็จ"; //LbServerMessage.ForeColor = Color.Green; //LbOutput.Text = output; //TbSQL.Text = output; } catch (Exception ex) { LbServerMessage.Text +=";"+ ex.Message; LbServerMessage.ForeColor = Color.Red; } ta_.Close(); ta.Close(); return output; } protected void setMessageError(string txt){ this.LbServerMessage.Text = txt; this.LbServerMessage.ForeColor = Color.Red; } protected void setMessageSuccess(string txt){ this.LbServerMessage.Text = txt; this.LbServerMessage.ForeColor = Color.Green; } string listtable_ = ":\\GCOOP_ALL\\CORE\\GCOOP\\Saving\\DataCenter\\listtable.txt"; string listtable = "C:\\GCOOP_ALL\\CORE\\GCOOP\\Saving\\DataCenter\\listtable.txt" ; protected string[] getTablesLists() { listtable = root_drive + listtable_; string[] tables=null; try { int row = 0; StreamReader sr = new StreamReader(listtable); string data = sr.ReadToEnd().Replace(" ","").Replace("\r",""); tables=data.Split('\n'); /* sr.Close(); sr = new StreamReader(listtable); tables=new string[row]; for (int i = 0; i < row; i++) { tables[i] = sr.ReadLine(); } */ sr.Close(); } catch { this.setMessageError( "ไม่พบรายการตารางใน File " + listtable); } return tables; } protected string[] getSQLs() { string[] sql = this.TbSQL.Text.Split(';'); return sql; } protected string[] getSQLPLs() { string[] sql = this.TbSQL.Text.Split('/'); return sql; } protected string execSQLPLs(string[] sqls, string connectionString) { string output = ""; for (int i = 0; i < sqls.Length; i++) { if (sqls[i].Trim() != "") { Sta ta = new Sta(connectionString); ta.Transection(); string sql = ""; try { sql = sqls[i].Trim(); // if (sql.IndexOf("--") < 0) // { ta.ExePlSql(sql); ta.Commit(); // } ta.Close(); output += sql + ";--\n ================== \n Success ..;\n"; } catch (Exception ex) { try { ta.RollBack(); } catch { } ta.Close(); output += sql + ";\n-- Error : " + ex.Message + ";\n"; } } } return output; } protected string execSQLs(string[] sqls,string connectionString) { string output = ""; for (int i = 0; i < sqls.Length; i++) { if (sqls[i].Trim() != "") { Sta ta = new Sta(connectionString); ta.Transection(); string sql = ""; try { sql= sqls[i].Trim(); if (sql.IndexOf("--") < 0) { ta.Exe(sql); ta.Commit(); } ta.Close(); output += sql + ";--\n ================== \n Success ..;\n"; } catch (Exception ex) { try { ta.RollBack(); } catch { } ta.Close(); output += sql + ";\n-- Error : " + ex.Message + ";\n"; } } } return output; } string dbBranchHost = "", dbBranchUsr = "", dbBranchPwd = "", dbBranchUnicode = ""; string dbCenterHost = "", dbCenterUsr = "", dbCenterPwd = "", dbCenterUnicode = ""; private string getConnectionValuesByName(string connection,string name) { string values = ""; string[] con = connection.Split(';'); for (int i = 0; i < con.Length; i++) { if (con[i].ToLower().IndexOf(name.ToLower()) >= 0) { string[] val = con[i].Split('='); values = val[1]; } } return values; } protected void getConnectionDetail() { dbBranchHost = getConnectionValuesByName(this.TbConnectionString.Text, "Data Source"); dbBranchUsr = getConnectionValuesByName(this.TbConnectionString.Text, "User ID"); dbBranchPwd = getConnectionValuesByName(this.TbConnectionString.Text, "Password"); dbBranchUnicode = getConnectionValuesByName(this.TbConnectionString.Text, "Unicode"); dbCenterHost = getConnectionValuesByName(this.TbConnectionStringCen.Text, "Data Source"); dbCenterUsr = getConnectionValuesByName(this.TbConnectionStringCen.Text, "User ID"); dbCenterPwd = getConnectionValuesByName(this.TbConnectionStringCen.Text, "Password"); dbCenterUnicode = getConnectionValuesByName(this.TbConnectionStringCen.Text, "Unicode"); } protected void PrepareProcess(object sender, EventArgs e) { String msg = ""; String step = this.DropDownList1.SelectedValue; string conBranch = TbConnectionString.Text; Sta taBranch = new Sta(conBranch); string conCenter = TbConnectionStringCen.Text; Sta taCenter = new Sta(conCenter); this.getConnectionDetail(); string owner = TbUserOwner.Text.Trim(); if (owner == "") { owner = this.dbCenterUsr.ToLower(); TbUserOwner.Text = owner; } /* STEP_010=DB CENTER : 1.0 กำหนดรายการ TABLE สำหรับที่จะ sync STEP_011=DB CENTER : 1.1 สร้าง TABLE สำหรับเก็บ SQL ข้อมูลที่ sync STEP_012=DB CENTER : 1.2 ตรวจสอบ DB Struncture ที่ฐานข้อมูล ที่สาขา กับ CENTER STEP_0131=DB CENTER : 1.3.1 ดรอป fk เเละ add dblink column STEP_0132=DB CENTER : 1.3.2 ดรอป pk เเละ add dblink เป็น pk STEP_021=DB BRANCH : 2.1 สร้าง DBLINK ที่จะ เชื่อมโยงข้อมูล ที่ฐานข้อมูล ที่สาขา STEP_022=DB BRANCH : 2.2 สร้าง TRIGGER ที่จะ เชื่อมโยงข้อมูล ทีจากฐานข้อมูล ที่สาขา มา CENTER STEP_023=DB BRANCH : 2.3 ล้างข้อมูลใน DB CENTER ตาราง ทั้งหมด ที่จะ เชื่อมโยงข้อมูล STEP_024=DB BRANCH : 2.4 นำข้าข้อมูลใน DB CENTER ตาราง ทั้งหมด ที่จะ เชื่อมโยงข้อมูล เพื่อตั้งต้นข้อมูล STEP_90=DB CENTER : View Logs การ Sync ข้อมูลที่สาขา มา CENTER STEP_91=DB BRANCH : DISABLE การทำงานของ TRIGGER เชื่อมโยงข้อมูล ทีจากฐานข้อมูล ที่สาขา มา CENTER STEP_92=DB BRANCH : ENABLE การทำงานของ TRIGGER เชื่อมโยงข้อมูล ทีจากฐานข้อมูล ที่สาขา มา CENTER */ string sql=""; string[] tables =this.getTablesLists(); string values = ""; if (step == "STEP_010")//DB CENTER : 1.0 กำหนดรายการ TABLE สำหรับที่จะ sync { int max = tables.Length; for (int i = 0; i < max; i++) { values += tables[i].Trim().ToLower() + "\r\n"; } values += ";"; values = values.Replace("\r\n;",""); } else if (step == "STEP_011")//DB CENTER : 1.1 สร้าง TABLE สำหรับเก็บ SQL ข้อมูลที่ sync { values = "drop table CMLOGSYNCDATA cascade constraints;\r\n " + "CREATE TABLE CMLOGSYNCDATA (\"BRANCH_ID\" VARCHAR2(20) NOT NULL,\"LOG_DATE\" DATE NOT NULL,\"MODE\" CHAR(1) NOT NULL,\"TABLE_NM\" VARCHAR2(150) NOT NULL,\"ORA_DIR\" VARCHAR2(150) NOT NULL,\"SQL_FILE\" VARCHAR2(150) NOT NULL, \"SQL\" CLOB NOT NULL) ;\r\n"+ "ALTER TABLE \"CMLOGSYNCDATA\" ADD ( CONSTRAINT PK_CMLOGSYNCDATA PRIMARY KEY ( \"BRANCH_ID\",\"LOG_DATE\",\"MODE\",\"TABLE_NM\",\"ORA_DIR\",\"SQL_FILE\" )) ;"; } else if (step == "STEP_012") //DB CENTER : 1.2 ตรวจสอบ DB Struncture ที่ฐานข้อมูล ที่สาขา กับ CENTER { } else if (step == "STEP_0131") //DB CENTER : 1.3.1 ดรอป fk เเละ add dblink column { // DROP FK int max = tables.Length; string inTables = ""; for (int i = 0; i < max;i++ ) { inTables += "'"+tables[i].Trim().ToLower()+"',"; //"alter table "+tables[i].Trim().ToLower()+" drop primary key cascade"; values += "ALTER TABLE " + tables[i].Trim().ToUpper() + " ADD ("+this.dbLinkField.Text+" VARCHAR2(50) DEFAULT 0 NOT NULL);\r\n"; } inTables += ")"; inTables = inTables.Replace(",)", ""); sql= @" SELECT distinct ac.constraint_name,ac.table_name,accs.column_name, accs.position, ac.r_constraint_name ,'ALTER TABLE '||ac.table_name||' DROP CONSTRAINT '||ac.constraint_name||' ;' as sql FROM ALL_CONSTRAINTS ac, all_cons_columns accs WHERE lower(ac.owner) = '" + owner.ToLower() + @"' AND ac.constraint_type = 'R' AND lower(ac.table_name) in (" + inTables.Trim().ToLower() + @") AND accs.owner = ac.owner AND accs.table_name = ac.table_name AND accs.constraint_name = ac.constraint_name ORDER BY ac.table_name, ac.constraint_name, accs.position "; // values += sql+";\r\n"; ///* //values += sql + ";\r\n"; try { Sdt dt = taCenter.Query(sql); string table_ = ""; string constraint = ""; int c = 0; while (dt.Next()) { if (table_ != dt.GetString(1)) { c++; if (table_ != "") { //values += "ALTER TABLE " + table_ + " ADD (DBLINK VARCHAR2(50) DEFAULT 0 NOT NULL);\r\n"; values += "-- END DROP FK " + table_ + ";\r\n"; } table_ = dt.GetString(1); values += "-- START DROP FK " + table_ + ";\r\n"; } if (constraint != dt.GetString(0)) { constraint = dt.GetString(0); values += "" + dt.GetString(5) + ";\r\n"; } } if(c>0) values += "-- END DROP FK " + table_ + ";\r\n"; } catch(Exception ex) { values += "-- Error :" + sql + ";\r\n--" + ex.Message + ";\r\n"; } // */ } else if (step == "STEP_0132") //DB CENTER : 1.3.2 ดรอป pk เเละ add dblink เป็น pk { // DROP FK int max = tables.Length; string inTables = ""; for (int i = 0; i < max; i++) { inTables += "'" + tables[i].Trim().ToLower() + "',"; //"alter table "+tables[i].Trim().ToLower()+" drop primary key cascade"; //values += "ALTER TABLE " + tables[i].Trim().ToUpper() + " ADD (DBLINK VARCHAR2(50) DEFAULT 0 NOT NULL);\r\n"; } inTables += ")"; inTables = inTables.Replace(",)", ""); sql = @" SELECT ac.constraint_name,ac.table_name,accs.column_name, accs.position, ac.r_constraint_name ,'ALTER TABLE '||ac.table_name||' drop primary key cascade ;' as sql FROM ALL_CONSTRAINTS ac, all_cons_columns accs WHERE lower(ac.owner) = '" + owner.ToLower() + @"' AND ac.constraint_type = 'P' AND lower(ac.table_name) in (" + inTables.Trim().ToLower() + @") AND accs.owner = ac.owner AND accs.table_name = ac.table_name AND accs.constraint_name = ac.constraint_name ORDER BY ac.table_name, ac.constraint_name, accs.position "; // values += sql+";\r\n"; ///* //values += sql + ";\r\n"; try { Sdt dt = taCenter.Query(sql); TbSQL.Text = sql; string table_ = "",column_="",columns=""; string constraint = ""; int c = 0; while (dt.Next()) { if (table_ != dt.GetString(1)) { c++; if (table_ != "") { //values += "ALTER TABLE " + table_ + " ADD (DBLINK VARCHAR2(50) DEFAULT 0 NOT NULL);\r\n"; values += "-- END DROP PK " + table_ + ";\r\n"; columns += ")"; columns = columns.Replace(",)", "").Replace(("," + this.dbLinkField.Text.Trim() + ""), ""); values += "ALTER TABLE " + table_ + " ADD (CONSTRAINT PK_" + table_ + " PRIMARY KEY (" + columns + "," + this.dbLinkField.Text.Trim() + "));\r\n"; columns = ""; } table_ = dt.GetString(1); values += "-- START DROP PK " + table_ + ";\r\n"; } if (column_ != dt.GetString(2)) { column_ = dt.GetString(2); columns += column_ + ","; } if (constraint != dt.GetString(0)) { constraint = dt.GetString(0); values += "" + dt.GetString(5) + ";\r\n"; } } if (c > 0) { values += "-- END DROP PK " + table_ + ";\r\n"; columns += ")"; columns = columns.Replace(",)", "").Replace(("," + this.dbLinkField.Text.Trim() + ""), ""); values += "ALTER TABLE " + table_ + " ADD (CONSTRAINT PK_" + table_ + " PRIMARY KEY (" + columns + "," + this.dbLinkField.Text.Trim() + "));\r\n"; columns = ""; } } catch (Exception ex) { values += "-- Error :" + sql + ";\r\n--" + ex.Message + ";\r\n"; } // */ } else if (step == "STEP_021") //DB BRANCH : 2.1 สร้าง DBLINK ที่จะ เชื่อมโยงข้อมูล ที่ฐานข้อมูล ที่สาขา { values = " DROP DATABASE LINK " + this.dbCenterUsr + ";\r\n"; values += " CREATE DATABASE LINK " + this.dbCenterUsr + " CONNECT TO " + this.dbCenterUsr + " IDENTIFIED BY " + this.dbCenterPwd + " USING '" +this.dbCenterHost + "';\r\n"; values += " CREATE OR REPLACE DIRECTORY DATAPUMP AS '" + dbDataPump.Text+ "';\r\n"; values += " DROP SEQUENCE PK_TRIGGER_FILENM;\r\n"; values += " CREATE SEQUENCE PK_TRIGGER_FILENM MINVALUE 0 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE ORDER CYCLE ;"; } else if (step == "STEP_022") //DB BRANCH : 2.2 สร้าง TRIGGER ที่จะ เชื่อมโยงข้อมูล ทีจากฐานข้อมูล ที่สาขา มา CENTER { /* int max = tables.Length;max = 1; //string inTables = ""; for (int i = 0; i < max; i++) { //inTables += "'" + tables[i].Trim().ToLower() + "',"; values += buildTriggerSyncData(this.TbConnectionString.Text.Trim(),tables[i].Trim(), owner, "" + this.dbLinkField.Text.Trim() + "", this.dbBranchUsr, "")+"\r\n"; } */ values += buildTriggerSyncData(this.TbConnectionString.Text.Trim(), this.TbTableLists.SelectedValue.Trim().ToLower(), owner, "" + this.dbLinkField.Text.Trim() + "", this.dbBranchUsr, "",this.dbBranchUsr) + "\r\n"; } else if (step == "STEP_023") //DB BRANCH : 2.3 ล้างข้อมูลใน DB CENTER ตาราง ทั้งหมด ที่จะ เชื่อมโยงข้อมูล { int max = tables.Length; string inTables = ""; for (int i = 0; i < max; i++) { inTables += "'" + tables[i].Trim().ToLower() + "',"; values += "DELETE FROM " + tables[i].Trim().ToUpper() + "@" + owner.ToUpper() + " WHERE " + this.dbLinkField.Text.Trim() + " is null or " + this.dbLinkField.Text.Trim() + " ='0' or lower(" + this.dbLinkField.Text.Trim() + ")='" + this.dbBranchUsr.ToLower() + "' ;\r\n"; } } else if (step == "STEP_024") //DB BRANCH : 2.4 นำข้าข้อมูลใน DB CENTER ตาราง ทั้งหมด ที่จะ เชื่อมโยงข้อมูล เพื่อตั้งต้นข้อมูล { int max = tables.Length; string inTables = ""; for (int i = 0; i < max; i++) { inTables += "'" + tables[i].Trim().ToLower() + "',"; values += "INSERT INTO " + tables[i].Trim().ToUpper() + "@" + owner.ToUpper() + " SELECT T.* ,'" + this.dbBranchUsr.ToLower() + "' AS " + this.dbLinkField.Text.Trim() + " FROM " + tables[i].Trim().ToUpper() + " T ;\r\n"; } } else if (step == "STEP_025") //DB SOURCE : 2.5 ตั้งระบบ Update ข้อมูลโดย Windows Scheduler ใน DB TARGET { values += "ขั้นตอนนี้ต้องติดตั้ง Manual ดังนี้\r\n"; values += "1.Copy File จาก " + this.root_drive + ":\\GCOOP_ALL\\CORE\\GCOOP\\Saving\\DataCenter\\RunSQL.bat , RunSQL-exec.bat , SyncDataCenter.xml ไปยังเครื่อง Database Server Center \r\n"; values += "2.แก้ไข File RunSQL-exec.bat ในส่วน Database Profile ให้ถูกต้อง ตรงกับค่าการเชื่อมต่อ DB Center \r\n"; values += "3.เปิด Windows Scheduler และ เลือก Import Task\r\n"; values += "3.1 แก้ไขในส่วนของ Tab Actions แก้ไข Program/Script และ Start In ตรงกับ Path ที่ทำงานจริงบนเครื่อง \r\n"; values += "3.2 แก้ไขในส่วนของ Tab General ให้ Change User ตามจริงบนเครื่อง \r\n"; values += "4. การทำงานคือ ทุกครั้งที่ Trigger ทำงานจะสร้าง File SQL Script Save ไว้ที่ Path เดียวกับ .Bat และหลังประมวล จะ Move ไปที่ Path เก็บ Script \r\n"; } else if (step == "STEP_090") //DB CENTER : View Logs การ Sync ข้อมูลที่สาขา มา CENTER { } else if (step == "STEP_091") //DB BRANCH : DISABLE การทำงานของ TRIGGER เชื่อมโยงข้อมูล ทีจากฐานข้อมูล ที่สาขา มา CENTER { int max = tables.Length; string inTables = ""; for (int i = 0; i < max; i++) { inTables += "'" + tables[i].Trim().ToLower() + "',"; values += "--ALTER TABLE " + tables[i].Trim().ToUpper() + " DISABLE ALL TRIGGERS;\r\n"; sql = "select trigger_name from user_triggers where lower(table_owner)='" + this.dbBranchUsr.ToLower() + "' and base_object_type='TABLE' and lower(table_name)='" + tables[i].Trim().ToLower() + "' and lower(trigger_name) like lower('%" + tables[i].Trim().ToLower() + "%') "; //values += "--" + sql + "\r\n;"; try { Sdt dt = taBranch.Query(sql); TbSQL.Text = sql; while (dt.Next()) { values += "ALTER TRIGGER " + dt.GetString(0) + " DISABLE;\r\n"; } } catch { } } } else if (step == "STEP_092") //DB BRANCH : ENABLE การทำงานของ TRIGGER เชื่อมโยงข้อมูล ทีจากฐานข้อมูล ที่สาขา มา CENTER { int max = tables.Length; string inTables = ""; for (int i = 0; i < max; i++) { //select * from user_triggers inTables += "'" + tables[i].Trim().ToLower() + "',"; values += "--ALTER TABLE " + tables[i].Trim().ToUpper() + " ENABLE ALL TRIGGERS;\r\n"; sql = "select trigger_name from user_triggers where lower(table_owner)='" + this.dbBranchUsr.ToLower() + "' and base_object_type='TABLE' and lower(table_name)='" + tables[i].Trim().ToLower() + "' and lower(trigger_name) like lower('%" + tables[i].Trim().ToLower() + "%') "; //values += "--" + sql + "\r\n;" ; try { Sdt dt = taBranch.Query(sql); TbSQL.Text = sql; while (dt.Next()) { values += "ALTER TRIGGER " + dt.GetString(0) + " ENABLE;\r\n"; } } catch { } } } this.TbSQL.Text = values; taBranch.Close(); taCenter.Close(); } protected void ExecuteProcess(object sender, EventArgs e) { String msg = ""; String step = this.DropDownList1.SelectedValue; /* STEP_011=DB CENTER : 1.1 สร้าง TABLE สำหรับเก็บ SQL ข้อมูลที่ sync STEP_012=DB CENTER : 1.2 ตรวจสอบ DB Struncture ที่ฐานข้อมูล ที่สาขา กับ CENTER STEP_0131=DB CENTER : 1.3.1 ดรอป fk เเละ add dblink column STEP_0132=DB CENTER : 1.3.2 ดรอป pk เเละ add dblink เป็น pk STEP_021=DB BRANCH : 2.1 สร้าง DBLINK ที่จะ เชื่อมโยงข้อมูล ที่ฐานข้อมูล ที่สาขา STEP_022=DB BRANCH : 2.2 สร้าง TRIGGER ที่จะ เชื่อมโยงข้อมูล ทีจากฐานข้อมูล ที่สาขา มา CENTER STEP_023=DB BRANCH : 2.3 ล้างข้อมูลใน DB CENTER ตาราง ทั้งหมด ที่จะ เชื่อมโยงข้อมูล STEP_024=DB BRANCH : 2.4 นำข้าข้อมูลใน DB CENTER ตาราง ทั้งหมด ที่จะ เชื่อมโยงข้อมูล เพื่อตั้งต้นข้อมูล STEP_90=DB CENTER : View Logs การ Sync ข้อมูลที่สาขา มา CENTER STEP_91=DB BRANCH : DISABLE การทำงานของ TRIGGER เชื่อมโยงข้อมูล ทีจากฐานข้อมูล ที่สาขา มา CENTER STEP_92=DB BRANCH : ENABLE การทำงานของ TRIGGER เชื่อมโยงข้อมูล ทีจากฐานข้อมูล ที่สาขา มา CENTER */ string[] tables = this.getTablesLists(); string[] sqls = null; if (step == "STEP_010")//DB CENTER : 1.0 กำหนดรายการ TABLE สำหรับที่จะ sync { try { //บันทึกค่า StreamWriter sw = new StreamWriter(this.listtable, false); sw.Write(this.TbSQL.Text); sw.Close(); tables = this.getTablesLists(); this.TbTableLists.Items.Clear(); for (int i = 0; i < tables.Length; i++) { ListItem item = new ListItem(); item.Text = tables[i].Trim(); item.Value = tables[i].Trim(); this.TbTableLists.Items.Add(item); } this.setMessageSuccess("บันทึกสำเร็จ "+this.listtable); } catch(Exception es) { this.setMessageSuccess("บันทึกไม่สำเร็จ "+es.Message+":" + this.listtable); } } else if (step == "STEP_011")//DB CENTER : 1.1 สร้าง TABLE สำหรับเก็บ SQL ข้อมูลที่ sync { sqls = this.getSQLs(); this.TbSQL.Text = this.execSQLs(sqls, this.TbConnectionStringCen.Text); } else if (step == "STEP_012") //DB CENTER : 1.2 ตรวจสอบ DB Struncture ที่ฐานข้อมูล ที่สาขา กับ CENTER { sqls = this.getSQLs(); this.TbSQL.Text = this.execSQLs(sqls, this.TbConnectionStringCen.Text); } else if (step == "STEP_0131") //DB CENTER : 1.3.1 ดรอป fk เเละ add dblink column { sqls = this.getSQLs(); this.TbSQL.Text = this.execSQLs(sqls, this.TbConnectionStringCen.Text); } else if (step == "STEP_0132") //DB CENTER : 1.3.2 ดรอป pk เเละ add dblink เป็น pk { sqls = this.getSQLs(); this.TbSQL.Text = this.execSQLs(sqls, this.TbConnectionStringCen.Text); } else if (step == "STEP_021") //DB BRANCH : 2.1 สร้าง DBLINK ที่จะ เชื่อมโยงข้อมูล ที่ฐานข้อมูล ที่สาขา { sqls = this.getSQLs(); this.TbSQL.Text = this.execSQLs(sqls, this.TbConnectionString.Text); } else if (step == "STEP_022") //DB BRANCH : 2.2 สร้าง TRIGGER ที่จะ เชื่อมโยงข้อมูล ทีจากฐานข้อมูล ที่สาขา มา CENTER { sqls = this.getSQLPLs(); this.TbSQL.Text = this.execSQLs(sqls, this.TbConnectionString.Text); } else if (step == "STEP_023") //DB BRANCH : 2.3 ล้างข้อมูลใน DB CENTER ตาราง ทั้งหมด ที่จะ เชื่อมโยงข้อมูล { sqls = this.getSQLs(); this.TbSQL.Text = this.execSQLs(sqls, this.TbConnectionString.Text); } else if (step == "STEP_024") //DB BRANCH : 2.4 นำข้าข้อมูลใน DB CENTER ตาราง ทั้งหมด ที่จะ เชื่อมโยงข้อมูล เพื่อตั้งต้นข้อมูล { sqls = this.getSQLs(); this.TbSQL.Text = this.execSQLs(sqls, this.TbConnectionString.Text); } else if (step == "STEP_025") //DB SOURCE : 2.5 ตั้งระบบ Update ข้อมูลโดย Windows Scheduler ใน DB TARGET { } else if (step == "STEP_090") //DB CENTER : View Logs การ Sync ข้อมูลที่สาขา มา CENTER { } else if (step == "STEP_091") //DB BRANCH : DISABLE การทำงานของ TRIGGER เชื่อมโยงข้อมูล ทีจากฐานข้อมูล ที่สาขา มา CENTER { sqls = this.getSQLs(); this.TbSQL.Text = this.execSQLs(sqls, this.TbConnectionString.Text); } else if (step == "STEP_092") //DB BRANCH : ENABLE การทำงานของ TRIGGER เชื่อมโยงข้อมูล ทีจากฐานข้อมูล ที่สาขา มา CENTER { sqls = this.getSQLs(); this.TbSQL.Text = this.execSQLs(sqls, this.TbConnectionString.Text); } } string listconfig_ = ":\\GCOOP_ALL\\CORE\\GCOOP\\Saving\\DataCenter\\listconfig.txt"; string listconfig = "C:\\GCOOP_ALL\\CORE\\GCOOP\\Saving\\DataCenter\\listconfig.txt"; protected void LoadConfig() { listconfig = root_drive + listconfig_; string[] configs = null; try { int row = 0; StreamReader sr = new StreamReader(listconfig); string data = sr.ReadToEnd().Replace(" ", "").Replace("\r", ""); configs = data.Split('\n'); sr.Close(); } catch { this.setMessageError("ไม่พบรายการ File " + listconfig); } for (int i = 0; i < configs.Length;i++ ) { string values=String.Empty; if (configs[i].IndexOf("DB_Source") >= 0) { values = configs[i].Replace("DB_Source=", ""); this.TbConnectionString.Text = values.Replace("DataSource", "Data Source").Replace("PersistSecurityInfo", "Persist Security Info").Replace("UserID", "User ID"); } else if (configs[i].IndexOf("DB_Target") >= 0) { values = configs[i].Replace("DB_Target=", ""); this.TbConnectionStringCen.Text = values.Replace("DataSource", "Data Source").Replace("PersistSecurityInfo", "Persist Security Info").Replace("UserID", "User ID"); } else if (configs[i].IndexOf("Sync_Type") >= 0) { values = configs[i].Replace("Sync_Type=", ""); this.SyncWithDBLinkField.SelectedValue = values; } else if (configs[i].IndexOf("DB_LinkName") >= 0) { values = configs[i].Replace("DB_LinkName=", ""); this.TbUserOwner.Text = values; } else if (configs[i].IndexOf("DB_LinkField") >= 0) { values = configs[i].Replace("DB_LinkField=", ""); this.dbLinkField.Text = values; } } } protected void SaveConfig(object sender, EventArgs e) { try { //บันทึกค่า String config = ""; config += "DB_Source=" + this.TbConnectionString.Text + "\r\n"; config += "DB_Target=" + this.TbConnectionStringCen.Text + "\r\n"; config += "Sync_Type=" + this.SyncWithDBLinkField.SelectedValue + "\r\n"; config += "DB_LinkName=" + this.TbUserOwner.Text + "\r\n"; config += "DB_LinkField=" + this.dbLinkField.Text + "\r\n"; config += "Remark=Sync_Type{ 1=Linkแบบใช้ DB_LinkFieldเป็น PKร่วมในการจัดเก็บข้อมูล,0=Linkแบบรวมข้อมูล}\r\n"; StreamWriter sw = new StreamWriter(this.listconfig, false); sw.Write(config); sw.Close(); this.setMessageSuccess("บันทึกสำเร็จ " + this.listconfig); this.LoadConfig(); } catch (Exception es) { this.setMessageSuccess("บันทึกไม่สำเร็จ " + es.Message + ":" + this.listconfig); } } protected void LoanDefaultConfig(object sender, EventArgs e) { this.LoadConfig(); } } }