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 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);
}
private Boolean ExportTable(string table, string wh, OleDbConnection conm, SqlConnection con)
{
string sql = "";
sql = "DROP VIEW TAN_BK";
try
{
PublicFunction.SQL_Execute(sql, con);
}
catch {}
sql = "Create View TAN_BK as Select * from [" + table + "]";
if (wh != "")
{
sql += " where " + wh;
}
PublicFunction.SQL_Execute(sql, con, true);
RecordSetMDB rs1 = new RecordSetMDB("Select * from EXP_TB where tb='" + table + "'", conm);
if (rs1.rows <= 0)
{
sql = "SELECT * INTO [" + table +
"] FROM [ODBC;Driver=SQL Server;SERVER=" + PublicFunction.DT_server + ";DATABASE=" + PublicFunction.DT_database;
sql = sql + ";UID=" + PublicFunction.DT_username + ";PWD=" + PublicFunction.DT_password + ";].[TAN_BK]";
// if(wh!="")
// sql+=" where " +wh;
}
else
{
sql = "Insert INTO [" + table +
"] SELECT * FROM [ODBC;Driver=SQL Server;SERVER=" + PublicFunction.DT_server + ";DATABASE=" + PublicFunction.DT_database;
sql = sql + ";UID=" + PublicFunction.DT_username + ";PWD=" + PublicFunction.DT_password + ";].[TAN_BK]";
// if(wh!="")
// sql+=" where " +wh;
}
Boolean t = Excute_MDB(sql, conm);
sql = "Insert Into EXP_TB values('" + table + "')";
Excute_MDB(sql, conm);
sql = "DROP VIEW TAN_BK";
PublicFunction.SQL_Execute(sql, con);
return(t);
}
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();
}