/// <summary>
/// Compare table to table
/// </summary>
private static TableDB ValidationByTable(TableDB tbl)
{
string tblName = tbl.TableName;
// validate header field
try
{
int bashSize = int.Parse(ConfigurationManager.AppSettings["BashSize"]);
for (int i = 0; i < Math.Ceiling((decimal)tbl.RecordCount / bashSize); i++)
{
var dataTableMySql = DBMySQLUtils.DataTableInMySql(tblName, i + 1, bashSize);
var dataTableSqlServer = DBSqlServerUtils.DataTableInSqlServer(tblName, i + 1, bashSize);
CompareTableMySqlAndSqlServer(dataTableMySql, dataTableSqlServer, tbl);
}
}
catch (SqlException e)
{
if (e.Number == 208)
{
tbl.Problems.Clear();
tbl.Problems.Add("Table is not found in Sql Server");
}
}
catch (Exception e)
{
//tbl.Problems.Add(e.Message);
}
return(tbl);
}
/// <summary>
/// Main menu
/// </summary>
public static void MainMenu()
{
bool isCustomInput = false;
bool hideMenu = false;
int subMenu = 0;
string selected = "m";
while (!hideMenu)
{
try
{
isCustomInput = false;
Console.Clear();
if (selected == "q")
{
break;
}
if (selected == "m")
{
subMenu = 0;
Console.WriteLine("1) Lists tables in MySql database");
Console.WriteLine("2) Lists tables in Sql Server database");
Console.WriteLine("3) Validate all tables in database - using query");
Console.WriteLine("4) Validate selection tables in database - using query");
Console.WriteLine("5) Export Mysql database to csv file");
Console.WriteLine("6) Validate all tables in database - using csv file");
}
else if (subMenu == 0 && selected == "1")
{
subMenu = 1;
var listTbl = DBMySQLUtils.GetListTable();
PrintLine();
PrintRow("Lists tables in MySql database");
PrintLine();
PrintRow("Table name", "Number of records");
PrintLine();
foreach (var tbl in listTbl)
{
PrintRow(tbl.Key, tbl.Value.ToString());
}
}
else if (subMenu == 0 && selected == "2")
{
subMenu = 2;
var listTblSqlServer = DBSqlServerUtils.GetListTable();
PrintLine();
PrintRow("Lists tables in Sql Server database");
PrintLine();
PrintRow("Table name", "Number of records");
PrintLine();
foreach (var tbl in listTblSqlServer)
{
PrintRow(tbl.Key, tbl.Value.ToString());
}
}
else if (subMenu == 0 && selected == "3")
{
subMenu = 3;
var listTblMySql = DBMySQLUtils.GetListTable()
.Select(item => new TableDB
{
TableName = item.Key,
RecordCount = item.Value
}).ToList();
var listTblSqlServer = DBSqlServerUtils.GetListTable();
PrintLine();
PrintRow("Validation Summary Report");
PrintLine();
PrintRow("", "MySql", "Sql Server");
PrintLine();
foreach (var tbl in listTblMySql)
{
ValidationByTable(tbl);
}
PrintRow("Number of tables Checked", listTblMySql.Count().ToString(), listTblSqlServer.Count().ToString());
PrintLine();
var listTblPass = listTblMySql.Where(tbl => tbl.Problems.Count() == 0);
var listTblFail = listTblMySql.Where(tbl => !listTblPass.Any(item => item.TableName == tbl.TableName));
PrintRow("Table Passed", listTblPass.Count().ToString(), "");
PrintLine();
PrintRow("Table Failed", listTblFail.Count().ToString(), "");
foreach (var tblFailed in listTblFail.ToList())
{
for (int index = 0; index < tblFailed.Problems.Count(); index++)
{
var problem = tblFailed.Problems[index];
if (index == 0)
{
PrintRow("", tblFailed.TableName, "- " + problem);
}
else
{
PrintRowRight("", "", "- " + problem);
}
}
}
PrintLine();
}
else if (subMenu == 0 && selected == "4")
{
subMenu = 4;
var listTbl = DBMySQLUtils.GetListTable();
PrintRow("Validate table theo lựa chọn");
PrintLine();
PrintRow("Danh sách table trong Mysql");
PrintLine();
PrintRow("#", "table", "Number of Records");
PrintLine();
for (int index = 0; index < listTbl.Count(); index++)
{
var tbl = listTbl.ElementAt(index);
PrintRow(index.ToString(), tbl.Key, tbl.Value.ToString());
}
Console.WriteLine("Có 3 cách nhập: ");
Console.WriteLine(" - Chọn tất cả table: a hoặc A hoặc All");
Console.WriteLine(" - Chọn khoảng table: 1-10 hoặc 1-5 5-10");
Console.WriteLine(" - Chọn từng table: 1,2,3 hoặc 1 2 3");
}
else if (subMenu == 4)
{
var listTbl = DBMySQLUtils.GetListTable()
.Select((item, index) => new TableDB
{
TableName = item.Key,
RecordCount = item.Value,
Index = index
}).ToList();
if (new Regex("(\\d-\\d \\d-\\d)|(\\d-\\d)").IsMatch(selected))
{
var listSelected = selected.Split(' ').ToList();
var selectedTbl = new List <TableDB>();
foreach (var item in listSelected)
{
var selectedTables = item.Split('-').Select(int.Parse).ToList();
if (selectedTables[0] < 0 || selectedTables[0] < selectedTables[1] || selectedTables[1] > listTbl.Count())
{
selected = "4";
}
selectedTbl.AddRange(listTbl.Where(e => e.Index >= selectedTables[0] && e.Index <= selectedTables[1]).ToList());
}
listTbl = selectedTbl.Distinct().ToList();
}
else if (new Regex("(\\d)|(\\d \\d)|(\\d,\\d)").IsMatch(selected))
{
var selectedTables = selected.Split(' ', ',').Select(int.Parse).Distinct().ToList();
if (selectedTables.Any(e => e < 0) || selectedTables.Any(e => e > listTbl.Count()))
{
selected = "4";
}
listTbl = listTbl.Where(item => selectedTables.Contains(item.Index)).ToList();
}
else if (new string[] { "a", "all" }.Contains(selected.ToLower()))
{
}
else
{
subMenu = 0;
selected = "4";
continue;
}
PrintLine();
PrintRow("Validation summary report for selection tables");
PrintLine();
PrintRow("Table", "Checked Records", "Status", "Error Message");
PrintLine();
foreach (var tbl in listTbl)
{
ValidationByTable(tbl);
if (tbl.Problems.Count() > 0)
{
for (int index = 0; index < tbl.Problems.Count(); index++)
{
var problem = tbl.Problems[index];
if (index == 0)
{
PrintRow(tbl.TableName, tbl.RecordCount.ToString(), "Failed", problem);
}
else
{
PrintRowRight("", "", "", problem);
}
}
}
else
{
PrintRow(tbl.TableName, tbl.RecordCount.ToString(), "Passed", "");
}
PrintLine();
}
}
else if (subMenu == 0 && selected == "5")
{
PrintRow("Export Mysql database to csv file");
PrintLine();
subMenu = 5;
var listTbl = DBMySQLUtils.GetListTable()
.Select((item, index) => new TableDB
{
TableName = item.Key,
RecordCount = item.Value,
Index = index
}).ToList();
int exportSuccessCount = 0;
foreach (var tbl in listTbl)
{
try
{
DBMySQLUtils.ExportTableMetadataCsv(tbl.TableName);
DBMySQLUtils.ExportTableDataCsv(tbl.TableName);
PrintRow($"{tbl.TableName}", "Done");
exportSuccessCount++;
}
catch (Exception e)
{
PrintRow(tbl.TableName, e.Message);
}
PrintLine();
}
if (exportSuccessCount > 0)
{
Console.WriteLine($"{exportSuccessCount} tables exported");
}
else
{
Console.WriteLine($"Export failed.");
}
Console.WriteLine($"File path (MySql Server's absolute path): {DBMySQLUtils.GetExportFolder()}");
}
else if (subMenu == 6)
{
var listTblMySql = DBMySQLUtils.GetListTable()
.Select((item, index) => new TableDB
{
TableName = item.Key,
RecordCount = item.Value,
Index = index
}).ToList();
var listTblSqlServer = DBSqlServerUtils.GetListTable();
PrintRow("Validation Summary Report");
PrintLine();
PrintRow("", "MySql", "Sql Server");
PrintLine();
foreach (var tbl in listTblMySql)
{
try
{
var tableDataCsvPath = Path.Combine(selected, $"{tbl.TableName}.csv");
var tableMetadataCsvPath = Path.Combine(selected, $"{tbl.TableName}_metadata.csv");
ValidDataFromCsvAndSqlServer(tableDataCsvPath, tableMetadataCsvPath, tbl);
}
catch (Exception e)
{
if (e is SqlException)
{
if ((e as SqlException).Number == 208)
{
tbl.Problems.Add("Table not exist in Sql Server");
}
else if ((e as SqlException).Number == 207)
{
tbl.Problems.Add(e.Message);
}
}
if (e is FileNotFoundException)
{
tbl.Problems.Add("Cant find csv file");
}
}
}
PrintRow("Number of tables Checked", listTblMySql.Count().ToString() + "", listTblMySql.Count().ToString() + "");
PrintLine();
var listTblPass = listTblMySql.Where(tbl => tbl.Problems.Count() == 0);
var listTblFail = listTblMySql.Where(tbl => !listTblPass.Any(item => item.TableName == tbl.TableName));
PrintRow("Table Passed", listTblPass.Count().ToString(), "");
PrintLine();
PrintRow("Table Failed", listTblFail.Count().ToString(), "");
foreach (var tblFailed in listTblFail.ToList())
{
for (int index = 0; index < tblFailed.Problems.Count(); index++)
{
var problem = tblFailed.Problems[index];
if (index == 0)
{
PrintRow("", tblFailed.TableName, "- " + problem);
}
else
{
PrintRowRight("", "", "- " + problem);
}
}
}
PrintLine();
}
else if (subMenu == 0 && selected == "6")
{
isCustomInput = true;
string path = "";
Console.WriteLine("m) Menu");
Console.WriteLine("q) Quit");
while (true)
{
Console.Write("Enter exported csv path (Example: C:/mysql/excel): ");
var input = Console.ReadLine().Trim().ToLower();
if (input == "m" || input == "q")
{
selected = input;
subMenu = 0;
break;
}
if (Directory.Exists(input))
{
selected = input;
subMenu = 6;
break;
}
else
{
Console.WriteLine("Path is not found");
}
}
}
else
{
subMenu = 0;
selected = "m";
continue;
}
Console.WriteLine("m) Menu");
Console.WriteLine("q) Quit");
if (!isCustomInput)
{
Console.Write("\r\nEnter: ");
selected = Console.ReadLine().Trim().ToLower();
}
}
catch (Exception)
{
Console.WriteLine("Input again!");
}
}
}
/// <summary>
/// Read excel file -> compare with sql server. Using paging for each table query
/// </summary>
/// <param name="tableDataCsvPath">Csv table data file path</param>
/// <param name="tableMetadataCsvPath">Csv table metadata file path</param>
/// <param name="tbl">Table to compare</param>
/// <returns></returns>
private static bool ValidDataFromCsvAndSqlServer(string tableDataCsvPath, string tableMetadataCsvPath, TableDB tbl)
{
int bashSize = int.Parse(ConfigurationManager.AppSettings["BashSize"]);
DataTable csvData = new DataTable();
//read column names
List <string> colNames = new List <string>();
//read column data type
List <string> colDataTypes = new List <string>();
using (var tblMetadataCsvReader = new TextFieldParser(tableMetadataCsvPath, Encoding.Default, true))
{
tblMetadataCsvReader.SetDelimiters(new string[] { ";" });
tblMetadataCsvReader.HasFieldsEnclosedInQuotes = true;
tblMetadataCsvReader.TrimWhiteSpace = false;
while (!tblMetadataCsvReader.EndOfData)
{
List <string> cellData = tblMetadataCsvReader.ReadFields().ToList();
if (cellData.Count() < 2)
{
tbl.Problems.Add($"Table {tbl.TableName} metadata corrupted");
return(false);
}
colNames.Add(cellData[0]);
colDataTypes.Add(cellData[1]);
}
}
//read row data
using (var csvReader = new TextFieldParser(tableDataCsvPath, Encoding.Default, true))
{
csvReader.SetDelimiters(new string[] { ";" });
csvReader.HasFieldsEnclosedInQuotes = true;
csvReader.TrimWhiteSpace = false;
// Set column datatype
for (int i = 0; i < colNames.Count(); i++)
{
Type colType = typeof(string);
if (listInt.Contains(colDataTypes[i], StringComparer.OrdinalIgnoreCase))
{
colType = typeof(int);
}
else if (listDecimal.Contains(colDataTypes[i], StringComparer.OrdinalIgnoreCase))
{
colType = typeof(decimal);
}
else if (listDate.Contains(colDataTypes[i], StringComparer.OrdinalIgnoreCase))
{
colType = typeof(DateTime);
}
DataColumn datecolumn = new DataColumn(colNames[i], colType)
{
AllowDBNull = true
};
csvData.Columns.Add(datecolumn);
}
int page = 1;
int csvRow = 0;
int sqlServerRow = 0;
// Set row data
while (!csvReader.EndOfData)
{
csvRow++;
string[] cellData = csvReader.ReadFields();
object[] cellDataParsed = new object[cellData.Length];
//Making empty value as null
for (int i = 0; i < cellData.Length; i++)
{
if (cellData[i] == "")
{
cellDataParsed[i] = null;
}
else if (listInt.Contains(colDataTypes[i], StringComparer.OrdinalIgnoreCase))
{
if (int.TryParse(cellData[i], out int parsed))
{
cellDataParsed[i] = parsed;
}
}
else if (listDecimal.Contains(colDataTypes[i], StringComparer.OrdinalIgnoreCase))
{
if (decimal.TryParse(cellData[i], NumberStyles.Any, new NumberFormatInfo()
{
NumberDecimalSeparator = "."
}, out decimal parsed))
{
cellDataParsed[i] = parsed;
}
}
else if (listDate.Contains(colDataTypes[i], StringComparer.OrdinalIgnoreCase))
{
if (DateTime.TryParse(cellData[i], out DateTime parsed))
{
cellDataParsed[i] = parsed;
}
}
else if (listStr.Contains(colDataTypes[i], StringComparer.OrdinalIgnoreCase))
{
cellDataParsed[i] = cellData[i];
}
}
csvData.Rows.Add(cellDataParsed);
// Compare with sql server
if (csvData.Rows.Count == bashSize || csvReader.EndOfData)
{
DataTable DBServer = DBSqlServerUtils.GetDataTablePaging(tbl.TableName, colNames.ToArray(), page, bashSize);
sqlServerRow += DBServer.Rows.Count;
CompareTableMySqlAndSqlServer(csvData, DBServer, tbl);
csvData.Rows.Clear();
page++;
}
}
if (csvRow > sqlServerRow)
{
//tbl.Problems.Add($"Missing {csvRow - sqlServerRow} records");
csvData.Rows.Clear();
return(false);
}
else if (csvRow <= 0)
{
DataTable DBServer = DBSqlServerUtils.GetDataTablePaging(tbl.TableName, colNames.ToArray(), page, bashSize);
CompareTableMySqlAndSqlServer(csvData, DBServer, tbl);
csvData.Rows.Clear();
}
}
return(true);
}
