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())
{
cmd.Connection = con;
cmd.CommandText = "SELECT BatchNo,BatchDesc from GL_tblBatchList ";
if (chkDocDate.Checked)
{
cmd.CommandText += "Where ";
var adapter = new SqlDataAdapter(cmd);
var ds = new DataSet();
adapter.Fill(ds);
gridLookUpEditBatch.Properties.DataSource = ds.Tables[0];
gridLookUpEditBatch.Properties.DisplayMember = "BatchNo";
gridLookUpEditBatch.Properties.ValueMember = "BatchNo";
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("Connect Fail");
}
}
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 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]);
}
}
}
}
}