public ComparisionOutput CompareColumnValues(DataTable DataTable1, DataTable DataTable2, string RequestType = "immediate")
{
IStringMetric stringMetricObj = new StringMetric();
ComparisionOutput finalOutput = new ComparisionOutput();
List <DataTable> DataTableList = new List <DataTable>();
var TuppledOutput = DataTableRowEqualizer.EqualizeDatable(DataTable1, DataTable2);
DataTable1 = TuppledOutput.Item1;
DataTable2 = TuppledOutput.Item2;
int IteratrionLength = DataTable1.Columns.Count > DataTable2.Columns.Count ? DataTable2.Columns.Count : DataTable1.Columns.Count;
for (int i = 0; i < IteratrionLength; i++)
{
DataTable ColumnValue1 = new DataTable();
DataTable ColumnValue2 = new DataTable();
string Col1 = DataTable1.Columns[i].ColumnName.ToString();
string Col2 = DataTable2.Columns[i].ColumnName.ToString();
if (Col1 == Col2)
{
Col2 = Col2 + "_1";
}
ColumnValue1.Columns.Add(Col1);
ColumnValue2.Columns.Add(Col2);
for (int j = 0; j < DataTable1.Rows.Count; j++)
{
ColumnValue1.Rows.Add(DataTable1.Rows[j][i].ToString());
ColumnValue2.Rows.Add(DataTable2.Rows[j][i].ToString());
}
DataTable dataTableOutput = stringMetricObj.StringComparisonResult(ColumnValue1, ColumnValue2, "db", Col1, Col2, i + 1);
if (i == 0)
{
finalOutput.RowSet = dataTableOutput.Copy();
}
else
{
finalOutput.RowSet = MergeDataTable.MergeDataTables(finalOutput.RowSet, dataTableOutput);
}
}
finalOutput.ColumnSet = finalOutput.RowSet.Columns.Cast <DataColumn>().Select(col => Convert.ToString(col)).ToList();
// if (RequestType == "immediate")
// {
// IntermediateCache<DataTable>.Current.Set("ComparisionOutputGrid", finalOutput.RowSet);
// }
return(finalOutput);
}
public ComparisionOutput CompareColumns(List <DataSourceColumnList> dataSourceColumnList, string RequestType = "immediate")
{
try
{
ComparisionOutput finalOutput = new ComparisionOutput();
finalOutput.RowSet = new DataTable();
IExcelDocumentService excelDocumentServiceObj = new ExcelDocumentService();
List <ExcelDocument> excelDocuments = new List <ExcelDocument>();
foreach (var item in dataSourceColumnList)
{
ExcelDocument excelDoc = new ExcelDocument();
if (RequestType == "immediate")
{
if (item.DataSourceType == "file-upload")
{
//excelDoc = IntermediateCache<ExcelDocumentDTO>.Current.Get(item.Source, excelDoc);
var bytes = Convert.FromBase64String(item.FilePath.Replace("data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,", ""));
excelDoc.Table = excelDocumentServiceObj.ExcelToDataTableUsingExcelDataReader(new MemoryStream(bytes));
excelDoc.FileName = item.FileName;
}
else
{
excelDoc.Table = DbNamesObj.GetColumnData(item.DbServerName, item.DBName, item.TableName, item.SqlQuery, item.ColumnName, item.DatabaseObjectType, item.StoredProcedureName, item.SpParameterValue, item.AuthenticationType, item.UserName, item.Password);
}
excelDoc.Source = item.Source;
excelDocuments.Add((ExcelDocument)excelDoc.Clone());
}
else
{
excelDoc.FileName = item.FileName;
excelDoc.Source = item.Source;
excelDoc.Table = excelDocumentServiceObj.ExcelToDataTableUsingExcelDataReader(item.FileName);
excelDocuments.Add(excelDoc);
}
}
string commonCol1 = dataSourceColumnList.Where(y => y.Source.Contains("DataSource1")).Select(y => y.CommonColumn).FirstOrDefault();
string commonCol2 = dataSourceColumnList.Where(y => y.Source.Contains("DataSource2")).Select(y => y.CommonColumn).FirstOrDefault();
List <DataTableFilter> source1DataTableFilterDTO = dataSourceColumnList.Where(y => y.Source.Contains("DataSource1")).FirstOrDefault().DataTableFilterList == null ? new List <DataTableFilter>() : dataSourceColumnList.Where(y => y.Source.Contains("DataSource1")).FirstOrDefault().DataTableFilterList;
List <DataTableFilter> source2DataTableFilterDTO = dataSourceColumnList.Where(y => y.Source.Contains("DataSource2")).FirstOrDefault().DataTableFilterList == null ? new List <DataTableFilter>() : dataSourceColumnList.Where(y => y.Source.Contains("DataSource2")).FirstOrDefault().DataTableFilterList;
List <DataColumns> ColumnNameList1 = dataSourceColumnList.Where(y => y.Source.Contains("DataSource1")).Select(y => y.ColumnNameList).FirstOrDefault();
List <DataColumns> ColumnNameList2 = dataSourceColumnList.Where(y => y.Source.Contains("DataSource2")).Select(y => y.ColumnNameList).FirstOrDefault();
var TuppledOutput0 = DataTableRowEqualizer.RemoveColumnDuplicatesList(ColumnNameList1, ColumnNameList2);
ColumnNameList1 = TuppledOutput0.Item1;
ColumnNameList2 = TuppledOutput0.Item2;
DataTable dataTable1 = excelDocuments.Where(y => y.Source.Contains("DataSource1")).FirstOrDefault().Table;
DataTable dataTable2 = excelDocuments.Where(y => y.Source.Contains("DataSource2")).FirstOrDefault().Table;
var TuppledOutput1 = DataTableRowEqualizer.RemoveColumnDuplicatesDatable(dataTable1, dataTable2);
dataTable1 = TuppledOutput1.Item1;
dataTable2 = TuppledOutput1.Item2;
var TuppledOutput2 = FilterDatatable.FilterDataTable(dataTable1, dataTable2, source1DataTableFilterDTO, source2DataTableFilterDTO);
dataTable1 = TuppledOutput2.Item1;
dataTable2 = TuppledOutput2.Item2;
if (string.IsNullOrWhiteSpace(commonCol1) || string.IsNullOrWhiteSpace(commonCol2))
{
int IteratrionLength = ColumnNameList1.Count() > ColumnNameList2.Count() ? ColumnNameList2.Count() : ColumnNameList1.Count();
var TuppledOutput = DataTableRowEqualizer.EqualizeDatable(dataTable1, dataTable2);
dataTable1 = TuppledOutput.Item1;
dataTable2 = TuppledOutput.Item2;
for (int i = 0; i < IteratrionLength; i++)
{
string Col1 = ColumnNameList1[i].ColumnName.ToString();
string Col2 = ColumnNameList2[i].ColumnName.ToString();
DataView view = new DataView(dataTable1);
DataTable crunchDataTable1 = view.ToTable(false, ColumnNameList1[i].ColumnName.ToString());
view = new DataView(dataTable2);
DataTable crunchDataTable2 = view.ToTable(false, ColumnNameList2[i].ColumnName.ToString());
DataTable dataTableOutput = stringMetricObj.StringComparisonResult(crunchDataTable1, crunchDataTable2, "ExelFile", Col1, Col2, i + 1);
if (i == 0)
{
finalOutput.RowSet = dataTableOutput.Copy();
}
else
{
finalOutput.RowSet = MergeDataTable.MergeDataTables(finalOutput.RowSet, dataTableOutput);
}
}
//if (RequestType == "immediate")
//{
// DataTable ChartDataTable = new DataTable();
// ChartDataTable.Merge(MergeDataTable.MergeDataTablesPieChart(finalOutput.RowSet));
// finalOutput.RowSet = MergeDataTable.CalculatePieChartNumbers(ChartDataTable);
//}
}
else
{
finalOutput.RowSet = JoinedDataTableResultset(dataTable1, dataTable2, commonCol1, commonCol2, ColumnNameList1, ColumnNameList2);
}
finalOutput.ColumnSet = finalOutput.RowSet.Columns.Cast <DataColumn>().Select(col => Convert.ToString(col)).ToList();
//if (RequestType == "immediate")
//{
// IntermediateCache<DataTable>.Current.Set("ComparisionOutputGrid", finalOutput.RowSet);
//}
return(finalOutput);
}
catch (Exception ex)
{
throw ex;
}
}
