private void button3_Click(object sender, System.EventArgs e) { if (ff.ShowDialog() == DialogResult.OK) { txt3.Text = ff.SelectedPath; conmdb = ConnectMDB(txt3.Text + "\\hqt_data.mdb"); if (conmdb == null || conmdb.State != ConnectionState.Open) { MessageBox.Show(PublicFunction.L_GetLabel(this.Name, 9)); return; } RecordSetMDB rs = new RecordSetMDB("Select * from SENDER ", conmdb); if (rs.rows > 0) { user.Text = rs.record(0, 0); dt.Text = rs.record(0, 1); } rs = new RecordSetMDB("Select * from EXP_INFO ", conmdb); if (rs.rows > 0) { info.Text = rs.record(0, 0); thumuc = rs.record(0, 1); } db.Text = "hqtan_temp"; PublicFunction.savefilekey1(txt3.Text + "\\" + thumuc + "\\VT.BS", db.Text); txt.Text = txt3.Text + "\\" + thumuc + "\\GP8000.exe"; } }
private Boolean CreateStructure(OleDbConnection conm, SqlConnection con) { RecordSetMDB rs = new RecordSetMDB("Select * from hqtan_TB order by name ", conm); for (int tb = 0; tb < rs.rows; tb++) { ll.Text = rs.record(tb, "name"); if (rs.record(tb, "name") != "dtproperties") { Copy_Table(rs.record(tb, "name") + "", rs.record(tb, "name"), rs.record(tb, "id"), conm, con); } } return(true); }
public static void Copy_Table(string FromTable, string NewTable, string IDTable, OleDbConnection conm, SqlConnection con) { string sql, st = "", key = ""; sql = "SELECT * from hqtan_FD where id=" + IDTable + " order by colid "; RecordSetMDB rs = new RecordSetMDB(sql, conm); for (int i = 0; i < rs.rows; i++) { if (i != 0) { st += ","; } st += "[" + rs.record(i, "name") + "] "; // ten bang st += rs.record(i, "datatype"); if (PublicFunction.S_Right(rs.record(i, "datatype"), 4) == "char" || PublicFunction.S_Right(rs.record(i, "datatype"), 6) == "binary") { st += "(" + T_String.IsNullTo0(rs.record(i, "length")) / 2 + ") "; // chieu dai } if (rs.record(i, "cdefault") + "" != "0") // default { string tam = T_String.GetDataFromSQL("text", "syscomments", "id=" + rs.record(i, "cdefault")); if (tam != "") { st += " Default " + tam; } } if (rs.record(i, "isnullable") + "" == "0") // allow null { st += " not null"; } int count = T_String.IsNullTo0(T_String.GetDataFromSQL("Count(*)", "sysindexkeys", "indid=1 and id=" + IDTable + " and colid=" + rs.record(i, "colid")) + ""); if (count > 0) { if (key != "") { key += ","; } key += "[" + rs.record(i, "name") + "] "; } } st = "Create Table [" + NewTable + "](" + st; if (key != "") { int count = T_String.IsNullTo0(T_String.GetDataFromSQL("Count(*)", "sysobjects", "name='" + NewTable + "'")); if (count <= 0) { st += " , CONSTRAINT PK_" + NewTable + " PRIMARY KEY (" + key + "))"; } else { st += " , CONSTRAINT PK1_" + NewTable + " PRIMARY KEY (" + key + "))"; } } else { st += ")"; } PublicFunction.SQL_Execute(st, con); }
private Boolean ImportTable(string table, OleDbConnection conm, SqlConnection con) { ArrayList fname = new ArrayList(); ArrayList key = new ArrayList(); ArrayList type = new ArrayList(); ArrayList pic = new ArrayList(); string sql, sql1, wh, st, st1; if (!PublicFunction.CheckFieldOfTable(con, table)) { return(false); } sql = "select * from " + table; RecordSetMDB rs = new RecordSetMDB(sql, conm); sql = "Insert Into [" + table + "] ("; for (int i = 0; i < rs.cols; i++) { if (Func.Fun.CheckFieldOfTable(con, table, rs.Field(i))) { fname.Add(rs.Field(i)); key.Add(Func.Fun.CheckPrimaryKey(con, table, rs.Field(i))); st = Func.Fun.GetTypeField(con, table, rs.Field(i)); type.Add(st); if (i != 0) { sql += ","; } sql += "[" + rs.Field(i) + "]"; if (st == "6") { pic.Add(rs.Field(i)); } } } sql += ") values("; for (int j = 0; j < rs.rows; j++) { if (stop) { CL(); return(false); } sql1 = ""; wh = ""; st1 = ""; for (int i = 0; i < fname.Count; i++) { //insert if (sql1 != "") { sql1 += ","; } if (pic.IndexOf(fname[i] + "") >= 0 || rs.record(j, fname[i] + "") == null || rs.record(j, fname[i] + "") == "") { if ((Boolean)key[i]) { sql1 = sql1 + "''"; } else { sql1 = sql1 + "Default"; } } // Get TypeName of Field, return {1 (nvarchar,char..), 2 (datetime), 3 (bit), 4 (float), 5 (int)} else { switch ((string)type[i]) // DataType { case "1": sql1 = sql1 + "N'" + T_String.sqlsql(rs.record(j, fname[i] + "")) + "'"; break; case "2": sql1 = sql1 + doiDT(rs.record(j, fname[i] + "")); break; case "3": if (rs.record(j, fname[i] + "") == "True") { sql1 = sql1 + "1"; } else if (rs.record(j, fname[i] + "") == "False") { sql1 = sql1 + "0"; } else { sql1 = sql1 + rs.record(j, fname[i] + ""); } break; case "4": sql1 = sql1 + rs.record(j, fname[i] + ""); break; case "5": sql1 = sql1 + rs.record(j, fname[i] + ""); break; default: sql1 = sql1 + "N'" + T_String.sqlsql(rs.record(j, fname[i] + "")) + "'"; break; } } //Delete if ((Boolean)key[i]) { if (wh != "") { wh += " and "; } // Get TypeName of Field, return {1 (nvarchar,char..), 2 (datetime), 3 (bit), 4 (float), 5 (int)} switch ((string)type[i]) // DataType { case "1": wh = wh + fname[i] + "=N'" + T_String.sqlsql(rs.record(j, fname[i] + "")) + "'"; break; case "2": wh = wh + fname[i] + "=" + doiDT(rs.record(j, fname[i] + "")) + ""; break; case "3": if (rs.record(j, fname[i] + "") == "True") { wh = wh + fname[i] + "=1"; } else if (rs.record(j, fname[i] + "") == "False") { wh = wh + fname[i] + "=0"; } else { wh = wh + fname[i] + "=" + rs.record(j, fname[i] + ""); } break; case "4": wh = wh + fname[i] + "=" + rs.record(j, fname[i] + ""); break; case "5": wh = wh + fname[i] + "=" + rs.record(j, fname[i] + ""); break; default: wh = wh + fname[i] + "=N'" + T_String.sqlsql(rs.record(j, fname[i] + "")) + "'"; break; } } if ((Boolean)key[i]) { if (st1 != "") { st1 += " and "; } //cu // if (Func.Fun.GetTypeField(con,table,fname[i]+"")=="2") // { // if (rs.record(j,fname[i]+"")+""=="") // st1+=fname[i]+"=''"; // else // st1+=fname[i]+"='"+ DateTime.Parse(rs.record(j,fname[i]+"")).ToString("yyyy/MM/dd HH:mm:ss") +"'"; // } // else // { // st1+=fname[i]+"='"+ T_String.sqlsql(rs.record(j,fname[i]+""))+"'"; // } if (Func.Fun.GetTypeField(con, table, fname[i] + "") == "2") { if (rs.record(j, fname[i] + "") + "" == "") { st1 += fname[i] + "=''"; } else { st1 += fname[i] + "='" + DateTime.Parse(rs.record(j, fname[i] + "")).ToString("yyyy/MM/dd HH:mm:ss") + "'"; } } else { if (Func.Fun.GetTypeField(con, table, fname[i] + "") == "1") { st1 += fname[i] + "='" + T_String.sqlsql(rs.record(j, fname[i] + "")) + "'"; } else { st1 += fname[i] + "=" + T_String.sqlsql(rs.record(j, fname[i] + "")) + ""; } } } } //PublicFunction.SQL_Execute("Delete from ["+table+"] where "+wh,con); PublicFunction.SQL_Execute(sql + sql1 + ")", con); for (int m = 0; m < pic.Count; m++) { if (st1 != "") { st = "Select [" + pic[m] + "] from [" + table + "] where " + st1; UploadImageToSQL(LoadImageFromMDB(st, conm), pic[m] + "", table, wh, con); } } ///lb1.Text=r+"/"+txt5.Text+" records."; ll.Text = "*" + table + "* __ " + (j + 1) + "/" + rs.rows + " records. " + (int)((j + 1) * 100 / rs.rows) + "% "; pro.Value = (int)((j + 1) * 100 / rs.rows); // pro.Value=(int)(r*100/T_String.IsNullTo0(txt5.Text)); } pro.Value = 100; return(true); }
private void BK() { // // constr = "Provider=MSDASQL.1;Extended Properties=DRIVER=SQL Server;" & _ // "SERVER=" & Text1(0).Text & ";UID=" & Text1(1).Text & ";PWD=" & Text1(2).Text & ";APP=Visual Basic;WSID=" & Text1(0).Text & _ // ";DATABASE=" & ";AutoTranslate=False" // concr.ConnectionString = constr // concr.Open // concr.execute " CREATE DATABASE [" & Text1(3).Text & "]" lll.Text = PublicFunction.L_GetLabel(this.Name, 10); SqlConnection con = PublicFunction.connect(PublicFunction.DT_server , "", PublicFunction.DT_username, PublicFunction.DT_password); try { PublicFunction.SQL_Execute(" Drop DATABASE [" + db.Text + "]", con); } catch {} try { PublicFunction.SQL_Execute("CREATE DATABASE [" + db.Text + "]", con); } catch { stop = true; CL(); MessageBox.Show("Can not create database " + db.Text); return; } con = PublicFunction.connect(PublicFunction.DT_server , db.Text, PublicFunction.DT_username, PublicFunction.DT_password); if (conmdb == null || conmdb.State != ConnectionState.Open) { stop = true; CL(); MessageBox.Show(PublicFunction.L_GetLabel(this.Name, 9)); return; } string sql = ""; lll.Text = PublicFunction.L_GetLabel(this.Name, 11); if (!CreateStructure(conmdb, con)) { stop = true; MessageBox.Show("Error!!!!"); lll.Text = "Error!!!!"; CL(); return; } lll.Text = PublicFunction.L_GetLabel(this.Name, 12); sql = "select * from EXP_TB "; RecordSetMDB rstb = new RecordSetMDB(sql, conmdb); for (int i = 0; i < rstb.rows; i++) { if (stop) { CL(); return; } ll.Text = rstb.record(i, "name"); if (r1.Checked) { if (!ImportTable1(rstb.record(i, 0), conmdb, con)) { stop = true; MessageBox.Show("Error!!!! " + rstb.record(i, "name")); lll.Text = "Error!!!!"; CL(); return; } } else { if (!ImportTable(rstb.record(i, 0), conmdb, con)) { stop = true; MessageBox.Show("Error!!!! " + rstb.record(i, "name")); lll.Text = "Error!!!!"; CL(); return; } } pro.Value = (int)((i + 1) * 100 / rstb.rows); } pro.Value = 100; PublicFunction.savefilekey1(txt3.Text + "\\" + thumuc + "\\VT.BS", db.Text); txt.Text = txt3.Text + "\\" + thumuc + "\\GP8000.exe"; p1.Visible = true; lll.Text = PublicFunction.L_GetLabel("frmTaBackup", 10); MessageBox.Show(PublicFunction.L_GetLabel("frmTaBackup", 10)); con.Close(); conmdb.Close(); stop = true; CL(); }