public void GetDatabaseList()
{
try
{
using (SqlConnection con = new SqlConnection(GetStrConnect.GetStrSrc()))
{
con.Open();
// Set up a command with the given query and associate
// this with the current connection.
using (SqlCommand cmd = new SqlCommand("SELECT BatchNo,BatchDesc from GL_tblBatchList", con))
{
var adapter = new SqlDataAdapter(cmd);
var ds = new DataSet();
adapter.Fill(ds);
//cbbBatchNo.Properties.DataSource = ds.Tables[0];
//cbbBatchNo.Properties.DisplayMember = "BatchNo";
//cbbBatchNo.Properties.ValueMember = "BatchNo";
gridLookUpEditBatch.Properties.DataSource = ds.Tables[0];
gridLookUpEditBatch.Properties.DisplayMember = "BatchDesc";
gridLookUpEditBatch.Properties.ValueMember = "BatchNo";
}
}
}
catch (Exception ex)
{
MessageBox.Show(@"Connect Fail");
}
}
private void DeleteTableTemp()
{
using (SqlConnection connect = new SqlConnection(GetStrConnect.GetStrDsc()))
{
connect.Open();
SqlDataAdapter sqlDAdetail;
string query = "SELECT name FROM sys.tables WHERE name LIKE 'zzzlvimport%'";
sqlDAdetail = new SqlDataAdapter(query, connect);
var dsdetail = new DataSet();
sqlDAdetail.Fill(dsdetail);
var ListItem = dsdetail.Tables[0]; foreach (DataRow item in ListItem.Rows)
{
SqlCommand command = new SqlCommand("DROP TABLE " + item["name"], connect);
command.ExecuteNonQuery();
}
}
}
private void ProcessImport(DoWorkEventArgs e)
{
try
{
// Đọc File XML Data
XmlDocument docProcess = new XmlDocument();
docProcess.Load(Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location) + "\\dataSrcExportCheck.xml");
//lấy Table từ table sau khi check chọn
var Table = docProcess.GetElementsByTagName("Function");
foreach (XmlNode item in Table)
{
table.Add(new DataDetail
{
ID = item.SelectNodes("ID")?.Item(0)?.InnerText,
Table = item.SelectNodes("Table")?.Item(0)?.InnerText,
// Name = item.SelectNodes("Name").Item(0).InnerText,
Condition = item.SelectNodes("Condition")?.Item(0)?.InnerText
});
foreach (XmlNode itemdetail in item.SelectNodes("Detail"))
{
detail.Add(new Detail
{
ID = item.SelectNodes("ID")?.Item(0)?.InnerText,
DetailName = itemdetail.InnerXml,
ConditionDetail = itemdetail.Attributes[0].Value
});
count.Add(itemdetail.Attributes[0].Value);
}
count.Add(item.SelectNodes("Table")?.Item(0)?.InnerText);
}
var Posted = docProcess.GetElementsByTagName("Posted")?.Item(0)?.InnerText;
//var Overwrite = docProcess.GetElementsByTagName("Overwrite").Item(0).InnerText;
// kết nối data lấy dữ liệu bảng muốn chuyển
using (SqlConnection sourceConnection = new SqlConnection(GetStrConnect.GetStrSrc()))
{
sourceConnection.Open();
//Lọc từng Table từ danh sách trong file XML
int i = 0;
//Import Master
foreach (var tableName in table)
{
//Lấy danh sách Data
string queryMaster = $"Select {tableName.Table}.* from {tableName.Table} Where {tableName.Condition}";
SqlDataAdapter sqlDA;
sqlDA = new SqlDataAdapter(queryMaster, sourceConnection);
var ds = new DataSet();
sqlDA.Fill(ds);
//Khởi tạo danh sách Column, dataTable, Khóa chính cột ----> Khi chạy từng vòng lặp
List <string> column = new List <string>();
Dictionary <string, string> openWith = new Dictionary <string, string>();
DataTable dataTable = new DataTable();
//check Posted
if (Posted.ToString() == "True" && i == 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
row.SetField("Posted", 0);
}
}
dataTable = ds.Tables[0];
//Lấy danh sách Column trong Danh sách
//Lấy Khóa chính trong danh sách Column
foreach (var item in dataTable.Columns)
{
openWith.Add(item.ToString(), item.ToString());
column.Add(item.ToString());
}
using (SqlConnection descConnection = new SqlConnection(GetStrConnect.GetStrDsc()))
{
descConnection.Open(); CopyData(descConnection, tableName.Table, ds.Tables[0], true, openWith, tableName.Table, null, column[0]);
this.Invoke(OnUpdateLog, "Import " + tableName.Table + ", Rows: " + dataTable.Rows.Count);
}
//===========//
//Import Detail//
foreach (var item in detail)
{
if (item.ID == tableName.ID)
{
SqlDataAdapter sqlDAdetail;
string query = $"Select {item.DetailName}.* from {tableName.Table},{item.DetailName} Where {tableName.Table}.{item.ConditionDetail} = {item.DetailName}.{item.ConditionDetail} and {tableName.Condition} ";
sqlDAdetail = new SqlDataAdapter(query, sourceConnection);
var dsdetail = new DataSet();
sqlDAdetail.Fill(dsdetail);
//Khởi tạo danh sách Column, dataTable, Khóa chính cột ----> Khi chạy từng vòng lặp
List <string> columnDetail = new List <string>();
Dictionary <string, string> openWithDetail = new Dictionary <string, string>();
//DataTable dataTableDetail = new DataTable();
//DataTable results = new DataTable();
//dataTableDetail = dsdetail.Tables[0];
//var resultsDetail = (from tableMaster in dataTable.AsEnumerable()
// join tableDetail in dataTableDetail.AsEnumerable() on tableMaster[0] equals tableDetail[item.ConditionDetail]
// select tableDetail).ToList();
//if (!resultsDetail.Any()){
// continue;
//}
//results = resultsDetail.CopyToDataTable();
//Lấy danh sách Column trong Danh sách
//Lấy Khóa chính trong danh sách Column
foreach (var itemDetail in dsdetail.Tables[0].Columns)
{
openWithDetail.Add(itemDetail.ToString(), itemDetail.ToString());
columnDetail.Add(itemDetail.ToString());
}
using (SqlConnection descConnection = new SqlConnection(GetStrConnect.GetStrDsc())){
descConnection.Open();
CopyData(descConnection, item.DetailName, dsdetail.Tables[0], true, openWithDetail, item.DetailName, null, columnDetail[0]);
this.Invoke(OnUpdateLog, "Import " + item.DetailName + ", Rows: " + dsdetail.Tables[0].Rows.Count);
}
}
}
bw.ReportProgress((100 * (i + 1)) / table.Count);
i++;
}
}
e.Result = "";
}
catch (Exception ex)
{
e.Result = ex.Message;
}
}
public string CheckData()
{
try
{
string[] BatchCheck = gridLookUpEditBatch.Text.Split(',');
DataTable dtLockData = new DataTable();
DataTable dtdata = new DataTable();
using (SqlConnection conn = new SqlConnection(GetStrConnect.GetStrSrc()))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("select * from AD_tblLockDataParameter", conn))
{
dtLockData.Load(cmd.ExecuteReader());
}
string Condition;
if (data[0].Condition != null)
{
Condition = data[0].Condition + " and ";
}
else
{
Condition = "";
}
using (SqlCommand cmd1 = new SqlCommand())
{
cmd1.Connection = conn;
cmd1.CommandType = CommandType.Text;
if (chkDocDate.Checked && chkBatchNo.Checked)
{
cmd1.CommandText = $"select * from {data[0].Table} Where {Condition} DocumentDate between '{dtpfromdate.Value.ToShortDateString()}' and '{dtptodate.Value.ToShortDateString()}' and";
cmd1.CommandText += "(";
foreach (var value in BatchCheck)
{
cmd1.CommandText += $" BatchNo = '{value}' or";
}
cmd1.CommandText = cmd1.CommandText.Substring(0, cmd1.CommandText.Length - 2);
cmd1.CommandText += ")";
dtdata.Load(cmd1.ExecuteReader());
if (dtdata.Rows.Count <= 0)
{
return("0");
}
else
{
//check ngày khóa chứng từ
foreach (DataRow row in dtLockData.Rows)
{
foreach (var item in data) // duyệt danh sách chọn
{
if (row["FunctionID"].ToString() == item.ID) // kiểm tra có chức năng khóa
{
foreach (DataRow rowdata in dtdata.Rows)
{
if ((DateTime)row["ClosedToDate"] >= (DateTime)rowdata["DocumentDate"])
{
return(item.Name);
}
}
}
}
}
}
}
else if (chkBatchNo.Checked)
{
cmd1.CommandText = $"select * from {data[0].Table} Where {Condition} ";
cmd1.CommandText += "(";
foreach (var value in BatchCheck)
{
cmd1.CommandText += $" BatchNo = '{value}' or";
}
cmd1.CommandText = cmd1.CommandText.Substring(0, cmd1.CommandText.Length - 2);
cmd1.CommandText += ")";
dtdata.Load(cmd1.ExecuteReader());
if (dtdata.Rows.Count <= 0)
{
return("0");
}
else
{
//check ngày khóa chứng từ
foreach (DataRow row in dtLockData.Rows)
{
foreach (var item in data) // duyệt danh sách chọn
{
if (row["FunctionID"].ToString() == item.ID) // kiểm tra có chức năng khóa
{
foreach (DataRow rowdata in dtdata.Rows)
{
if ((DateTime)row["ClosedToDate"] >= (DateTime)rowdata["DocumentDate"])
{
return(item.Name);
}
}
}
}
}
}
}
else if (chkDocDate.Checked)
{
cmd1.CommandText = "select * from " + data[0].Table + " Where " + Condition + " DocumentDate between '" + dtpfromdate.Value.ToShortDateString() + "' and '" + dtptodate.Value.ToShortDateString() + "'";
dtdata.Load(cmd1.ExecuteReader());
if (dtdata.Rows.Count <= 0)
{
return("0");
}
else
{
//check ngày khóa chứng từ
foreach (DataRow row in dtLockData.Rows)
{
foreach (var item in data) // duyệt danh sách chọn
{
if (row["FunctionID"].ToString() == item.ID) // kiểm tra có chức năng khóa
{
foreach (DataRow rowdata in dtdata.Rows)
{
if ((DateTime)row["ClosedToDate"] >= (DateTime)rowdata["DocumentDate"])
{
return(item.Name);
}
}
}
}
}
}
}
else
{
cmd1.CommandText = "select * from " + data[0].Table + " Where " + Condition.Replace("and ", "");
dtdata.Load(cmd1.ExecuteReader());
if (dtdata.Rows.Count <= 0)
{
return("0");
}
else
{
//check ngày khóa chứng từ
foreach (DataRow row in dtLockData.Rows)
{
foreach (var item in data) // duyệt danh sách chọn
{
if (row["FunctionID"].ToString() == item.ID) // kiểm tra có chức năng khóa
{
foreach (DataRow rowdata in dtdata.Rows)
{
if ((DateTime)row["ClosedToDate"] >= (DateTime)rowdata["DocumentDate"])
{
return(item.Name);
}
}
}
}
}
}
}
}
}
return("1");
}
catch (Exception ex)
{
MessageBox.Show(@"Lỗi: " + ex.Message);
return("0");
}
}
private void btnchitiet_Click(object sender, EventArgs e)
{
string textname = "";
int textcount = 0;
List <TableOld> TableOld = new List <TableOld>();
List <TableNew> TableNew = new List <TableNew>();
int CountSrc = 0, CountDsc = 0;
DataSet dsdetailSrc, dsSrc, dsDsc, dsdetailDsc;
using (SqlConnection conn = new SqlConnection(GetStrConnect.GetStrDsc()))
{
conn.Open();
foreach (var tableName in table)
{
//Lấy danh sách Data
SqlDataAdapter sqlDA;
sqlDA = new SqlDataAdapter("Select zzzlvimport" + tableName.Table + ".* from zzzlvimport" + tableName.Table + " Where " + tableName.Condition, conn);
dsSrc = new DataSet();
sqlDA.Fill(dsSrc);
TableOld.Add(new TableOld
{
tableNameOld = tableName.Table,
RowsCountOld = (int)dsSrc.Tables[0].Rows.Count,
});
foreach (var item in detail)
{
if (item.ID == tableName.ID)
{
SqlDataAdapter sqlDAdetail;
string query = "Select * From zzzlvimport" + item.DetailName;
sqlDAdetail = new SqlDataAdapter(query, conn);
dsdetailSrc = new DataSet();
sqlDAdetail.Fill(dsdetailSrc);
TableOld.Add(new TableOld
{
tableNameOld = item.DetailName,
RowsCountOld = dsdetailSrc.Tables[0].Rows.Count,
});
}
}
}
foreach (var tableName in table)
{
//Lấy danh sách Data
SqlDataAdapter sqlDA;
sqlDA = new SqlDataAdapter("Select " + tableName.Table + ".* from " + tableName.Table + " Where " + tableName.Condition, conn);
dsDsc = new DataSet();
sqlDA.Fill(dsDsc);
TableNew.Add(new TableNew
{
tableNameNew = tableName.Table,
RowsCountNew = (int)dsDsc.Tables[0].Rows.Count,
});
foreach (var item in detail)
{
if (item.ID == tableName.ID)
{
SqlDataAdapter sqlDAdetail;
string query = "Select * From " + item.DetailName;
sqlDAdetail = new SqlDataAdapter(query, conn);
dsdetailDsc = new DataSet();
sqlDAdetail.Fill(dsdetailDsc);
TableNew.Add(new TableNew
{
tableNameNew = item.DetailName,
RowsCountNew = (int)dsdetailDsc.Tables[0].Rows.Count,
});
}
}
}
foreach (var itemold in TableOld)
{
foreach (var itemnew in TableNew)
{
if (itemold.tableNameOld == itemnew.tableNameNew)
{
textname += itemold.tableNameOld;
textname += "\r\n";
textcount += itemnew.RowsCountNew - itemold.RowsCountOld;
//textcount += ;
}
}
}
FrmShow frmShow = new FrmShow(textname + " " + textcount);
frmShow.Show();
//MessageBox.Show(textname + " " + textcount, "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void ExistTable()
{
using (SqlConnection sourceConnection = new SqlConnection(GetStrConnect.GetStrSrc()))
{
sourceConnection.Open(); foreach (var tablenameTrans in table)
{
List <DataTransfer> tabletrans = new List <DataTransfer>();
var tran = sourceConnection.BeginTransaction();
using (SqlCommand cmd = new SqlCommand("AD_spImportTable", sourceConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = tran;
cmd.Parameters.Add("@TableToInsert", SqlDbType.VarChar, 50).Value = tablenameTrans.Table;
using (SqlDataReader rdr = cmd.ExecuteReader())
{
// iterate through results, printing each to console
while (rdr.Read())
{
if (rdr["TableName"].ToString() == tablenameTrans.Table)
{
continue;
}
tabletrans.Add(new DataTransfer()
{
Table = rdr["TableName"].ToString(),
});
//Console.WriteLine("Product: {0,-35} Total: {1,2}", rdr["TableName"], rdr["Level"]);
}
}
tran.Commit();
}
foreach (var tableName in tabletrans)
{
SqlDataAdapter sqlDA;
sqlDA = new SqlDataAdapter("select * from " + tableName.Table, sourceConnection);
var ds = new DataSet();
sqlDA.Fill(ds);
//Khởi tạo danh sách Column, dataTable, Khóa chính cột ---- Khi chạy từng vòng lặp
List <string> column = new List <string>();
Dictionary <string, string> openWith = new Dictionary <string, string>();
DataTable dataTable = new DataTable();
dataTable = ds.Tables[0];
//Lấy danh sách Column trong Danh sách
//Lấy Khóa chính trong danh sách Column
foreach (var item in dataTable.Columns)
{
openWith.Add(item.ToString(), item.ToString());
column.Add(item.ToString());
}
using (SqlConnection descConnection = new SqlConnection(GetStrConnect.GetStrDsc()))
{
descConnection.Open();
CopyDataExsit(descConnection, tableName.Table, ds.Tables[0], bool.Parse(Overwrite), openWith, tableName.Table, null, column[0]);
}
}
}
foreach (var tablenameTrans in detail)
{
List <DataTransfer> tabletrans = new List <DataTransfer>();
var tran = sourceConnection.BeginTransaction();
using (SqlCommand cmd = new SqlCommand("AD_spImportTable", sourceConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = tran;
cmd.Parameters.Add("@TableToInsert", SqlDbType.VarChar, 50).Value = tablenameTrans.DetailName;
using (SqlDataReader rdr = cmd.ExecuteReader())
{
// iterate through results, printing each to console
while (rdr.Read())
{
if (rdr["TableName"].ToString() == tablenameTrans.DetailName)
{
continue;
}
tabletrans.Add(new DataTransfer()
{
Table = rdr["TableName"].ToString(),
});
}
}
tran.Commit();
}
foreach (var tableName in tabletrans)
{
SqlDataAdapter sqlDA;
sqlDA = new SqlDataAdapter("select * from " + tableName.Table, sourceConnection);
var ds = new DataSet();
sqlDA.Fill(ds);
//Khởi tạo danh sách Column, dataTable, Khóa chính cột ---- Khi chạy từng vòng lặp
List <string> column = new List <string>();
Dictionary <string, string> openWith = new Dictionary <string, string>();
DataTable dataTable = new DataTable();
dataTable = ds.Tables[0];
//Lấy danh sách Column trong Danh sách
//Lấy Khóa chính trong danh sách Column
foreach (var item in dataTable.Columns)
{
openWith.Add(item.ToString(), item.ToString());
column.Add(item.ToString());
}
using (SqlConnection descConnection = new SqlConnection(GetStrConnect.GetStrDsc()))
{
descConnection.Open();
CopyDataExsit(descConnection, tableName.Table, ds.Tables[0], bool.Parse(Overwrite), openWith, tableName.Table, null, column[0]);
}
}
}
}
}
private void ImportData()
{
// kết nối data lấy dữ liệu bảng muốn chuyển
using (SqlConnection sourceConnection = new SqlConnection(GetStrConnect.GetStrDsc()))
{
sourceConnection.Open();
//Lọc từng Table từ danh sách trong file XML
int i = 1;
//Import Master
foreach (var tableName in table)
{
string queryImport = "Select zzzlvimport" + tableName.Table + ".* from zzzlvimport" +
tableName.Table + " Where " + tableName.Condition;
//Lấy danh sách Data
SqlDataAdapter sqlDA;
sqlDA = new SqlDataAdapter(queryImport, sourceConnection);
var ds = new DataSet();
sqlDA.Fill(ds);
//Khởi tạo danh sách Column, dataTable, Khóa chính cột ----> Khi chạy từng vòng lặp
List <string> column = new List <string>();
Dictionary <string, string> openWith = new Dictionary <string, string>();
DataTable dataTable = new DataTable();
//check Posted
if (Posted.ToString() == "True" && i == 1)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
row.SetField("Posted", 0);
}
}
dataTable = ds.Tables[0];
//Lấy danh sách Column trong Danh sách
//Lấy Khóa chính trong danh sách Column
foreach (var item in dataTable.Columns)
{
openWith.Add(item.ToString(), item.ToString());
column.Add(item.ToString());
}
using (SqlConnection descConnection = new SqlConnection(GetStrConnect.GetStrDsc()))
{
descConnection.Open();
CopyData(descConnection, tableName.Table, ds.Tables[0], bool.Parse(Overwrite), openWith, tableName.Table, null, column[0]);
this.Invoke(OnUpdateLog, "Import " + tableName.Table + ", Rows: " + dataTable.Rows.Count);
}
//===========//
//Import Detail//
foreach (var item in detail)
{
if (item.ID == tableName.ID)
{
SqlDataAdapter sqlDAdetail;
string query = $" SELECT zzzlvimport{item.DetailName}.* FROM zzzlvimport{item.DetailName} ,zzzlvimport{tableName.Table} Where {tableName.Condition} and zzzlvimport{tableName.Table}.{item.ConditionDetail}=zzzlvimport{item.DetailName}.{item.ConditionDetail}";
sqlDAdetail = new SqlDataAdapter(query, sourceConnection);
var dsdetail = new DataSet();
sqlDAdetail.Fill(dsdetail);
//Khởi tạo danh sách Column, dataTable, Khóa chính cột ----> Khi chạy từng vòng lặp
List <string> columnDetail = new List <string>();
Dictionary <string, string> openWithDetail = new Dictionary <string, string>();
//DataTable dataTableDetail = new DataTable();
//DataTable results = new DataTable();
//dataTableDetail = dsdetail.Tables[0];
//var resultsDetail = from tableMaster in dataTable.AsEnumerable()
// join tableDetail in dataTableDetail.AsEnumerable() on tableMaster[0] equals tableDetail[item.ConditionDetail]
// select tableDetail;
//if (!resultsDetail.Any())
//{
// continue;
//}
//results = resultsDetail.CopyToDataTable();
//Lấy danh sách Column trong Danh sách
//Lấy Khóa chính trong danh sách Column
foreach (var itemDetail in dsdetail.Tables[0].Columns)
{
openWithDetail.Add(itemDetail.ToString(), itemDetail.ToString());
columnDetail.Add(itemDetail.ToString());
}
var a = dsdetail.Tables[0].Rows.Count;
using (SqlConnection descConnection = new SqlConnection(GetStrConnect.GetStrDsc()))
{
descConnection.Open();
CopyData(descConnection, item.DetailName, dsdetail.Tables[0], bool.Parse(Overwrite), openWithDetail, item.DetailName, null, columnDetail[0]);
this.Invoke(OnUpdateLog, "Import " + item.DetailName + ", Rows: " + dsdetail.Tables[0].Rows.Count);
}
}
}
bw.ReportProgress((100 * i) / table.Count);
i++;
}
}
}
