public void Close(bool closeSession)
{
if (_connection != null)
{
if (_connection.State != ConnectionState.Closed && _connection.State != ConnectionState.Broken)
{
_connection.Close(closeSession);
}
}
}
private static DataTable RunQuery(string server, string model, string query)
{
ADOTabularConnection srcConx = null;
String srcErrorMsg = "", status = "PASS";
DataTable srcDT = new DataTable();
DataTable ret = new DataTable();
try
{
try
{
status = "PASS";
srcConx = new ADOTabularConnection(server, ADOTabular.Enums.AdomdType.AnalysisServices);
srcConx.ChangeDatabase(model);
srcDT = srcConx.ExecuteDaxQueryDataTable(query);
//recordCount = srcDT.Rows.Count;
//SPID = srcConx.SPID;
//Console.WriteLine("Session:" + srcConx.SPID);
}
catch (Exception ee)
{
Console.WriteLine(ee.Message);
Console.WriteLine(ee.StackTrace);
srcErrorMsg = ee.Message;
}
finally
{
//srcEndTime = DateTime.Now;
srcConx.Close();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine(e.StackTrace);
status = "FAILED";
}
finally
{
srcConx.Close();
//utils.getStatusRow(overallStatusDT, testName, status, srcStartTime, srcEndTime, (double)srcEndTime.Subtract(srcStartTime).TotalMilliseconds,
// recordCount, srcErrorMsg, srcQuery, SPID);
}
return(srcDT);
}
private static void RunQuery(string server, string model, string query, string testName)
{
DateTime srcStartTime = DateTime.Now, srcEndTime = DateTime.Now;
ADOTabularConnection srcConx = null;
String srcErrorMsg = "", status = null;
DataTable srcDT = new DataTable();
int recordCount = 0;
int SPID = 0;
try
{
try
{
status = "PASS";
srcConx = new ADOTabularConnection(server, ADOTabular.Enums.AdomdType.AnalysisServices);
srcConx.ChangeDatabase(model);
srcDT = srcConx.ExecuteDaxQueryDataTable(query);
recordCount = srcDT.Rows.Count;
SPID = srcConx.SPID;
srcEndTime = DateTime.Now;
Console.WriteLine("Test Name:[" + testName + "] | Start:[" + srcStartTime + "] | End:[" + srcEndTime + "] | Total Time:[" + (srcEndTime - srcStartTime).TotalSeconds.ToString() + "]");
}
catch (Exception ee)
{
Console.WriteLine(ee.Message);
Console.WriteLine(ee.StackTrace);
srcErrorMsg = ee.Message;
}
finally
{
srcConx.Close();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine(e.StackTrace);
status = "FAILED";
}
finally
{
srcConx.Close();
}
}
public async Task <bool> HasSchemaChangedAsync()
{
return(await _retry.Execute(async() =>
{
bool hasChanged = await Task.Run(() =>
{
var conn = new ADOTabularConnection(this.ConnectionString, this.Type);
conn.ChangeDatabase(this.SelectedDatabaseName);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
var dbChanges = conn.Database?.LastUpdate > _lastSchemaUpdate;
conn.Close(true); // close and end the session
return dbChanges;
});
return hasChanged;
}));
}
static void Main(string[] args)
{
Parser.Default.ParseArguments <Options>(args)
.WithParsed <Options>(o =>
{
Utils utils = new Utils();
DataTable excel = utils.ImportExceltoDatatable(o.inExcel, "Queries");
DirectoryInfo od = new DirectoryInfo(o.outDir);
od.Create();
DataTable dtSummary = new DataTable();
dtSummary.Columns.Add(new DataColumn("SSASModel", typeof(String)));
dtSummary.Columns.Add(new DataColumn("TableName", typeof(String)));
dtSummary.Columns.Add(new DataColumn("TestResults", typeof(String)));
dtSummary.Columns.Add(new DataColumn("TDQueryStartTime", typeof(DateTime)));
dtSummary.Columns.Add(new DataColumn("TDQueryEndTime", typeof(DateTime)));
dtSummary.Columns.Add(new DataColumn("TDQueryExecutionTime(minutes)", typeof(System.Double)));
dtSummary.Columns.Add(new DataColumn("SSASQueryStartTime", typeof(DateTime)));
dtSummary.Columns.Add(new DataColumn("SSASQueryEndTime", typeof(DateTime)));
dtSummary.Columns.Add(new DataColumn("SSASQueryExecutionTime(minutes)", typeof(System.Double)));
dtSummary.Columns.Add(new DataColumn("Exception", typeof(String)));
dtSummary.Columns.Add(new DataColumn("TDQuery", typeof(String)));
dtSummary.Columns.Add(new DataColumn("DAX", typeof(String)));
foreach (DataRow row in excel.Rows)
{
string ssasModel = row["SSAS_MODEL"].ToString();
string tableName = row["TABLE_NAME"].ToString();
string tdQuery = row["TD_QUERY"].ToString();
string daxQuery = row["DAX"].ToString();
DateTime tdStartTime = DateTime.Now, tdEndTime = DateTime.Now, SSASStartTime = DateTime.Now, SSASEndTime = DateTime.Now;
if (tableName.ToString().Equals(""))
{
continue;
}
DataRow drSummary = dtSummary.NewRow();
drSummary["SSASModel"] = ssasModel;
drSummary["TableName"] = tableName;
drSummary["TDQuery"] = tdQuery;
drSummary["DAX"] = daxQuery;
drSummary["TestResults"] = "Failed";
Console.WriteLine("---------------------------------------------------------------");
Console.WriteLine(ssasModel + " -> " + tableName);
using (var workbook = new XLWorkbook())
{
try
{
Console.WriteLine(tdQuery);
TdConnection cn = new TdConnection(o.Server);
cn.Open();
TdCommand cmd = null;
TdDataAdapter adapter = null;
DataTable dtt = new DataTable();
tdStartTime = DateTime.Now;
try
{
cmd = new TdCommand(tdQuery, cn);
cmd.CommandTimeout = 1200;
adapter = new TdDataAdapter(cmd);
tdStartTime = DateTime.Now;
adapter.Fill(dtt);
tdEndTime = DateTime.Now;
workbook.Worksheets.Add(dtt, "TD");
cmd.Connection.Close();
cmd.Dispose();
adapter.Dispose();
}
catch (Exception e)
{
tdEndTime = DateTime.Now;
cmd.Connection.Close();
cmd.Dispose();
adapter.Dispose();
throw e;
}
drSummary["TDQueryStartTime"] = tdStartTime;
drSummary["TDQueryEndTime"] = tdEndTime;
drSummary["TDQueryExecutionTime(minutes)"] = (double)tdEndTime.Subtract(tdStartTime).Seconds / 60;
//ADOTabularConnection conx = new ADOTabularConnection(@"Data Source = .; Catalog = AW;", AdomdType.AnalysisServices);
System.Data.DataTable dt = new DataTable();
ADOTabularConnection conx = null;
try
{
conx = new ADOTabularConnection(o.SSAS, ADOTabular.Enums.AdomdType.AnalysisServices);
if (!ssasModel.Trim().Equals(""))
{
conx.ChangeDatabase(ssasModel);
}
Console.WriteLine("--------------------DAX---------------------------");
Console.WriteLine(daxQuery);
SSASStartTime = DateTime.Now;
drSummary["SSASQueryStartTime"] = SSASStartTime;
dt = conx.ExecuteDaxQueryDataTable(daxQuery);
SSASEndTime = DateTime.Now;
conx.Close();
conx.Dispose();
}
catch (Exception e)
{
SSASEndTime = DateTime.Now;
conx.Close();
conx.Dispose();
throw e;
}
drSummary["SSASQueryEndTime"] = SSASEndTime;
drSummary["SSASQueryExecutionTime(minutes)"] = (double)SSASEndTime.Subtract(SSASStartTime).Seconds / 60;
int noOfRows = dt.Rows.Count;
if (noOfRows > MAX_ROWS)
{
dt = dt.Rows.Cast <DataRow>().Take(MAX_ROWS).CopyToDataTable();
}
workbook.Worksheets.Add(dt, "SSAS");
DataTable ret = utils.getDifferentRecords(dtt, dt);
DataTable dtOutput = new DataTable();
dtOutput.Columns.Add(new DataColumn("SSASModel", typeof(String)));
dtOutput.Columns.Add(new DataColumn("TableName", typeof(String)));
dtOutput.Columns.Add(new DataColumn("TDQuery", typeof(String)));
dtOutput.Columns.Add(new DataColumn("DAX", typeof(String)));
dtOutput.Columns.Add(new DataColumn("TestResults", typeof(String)));
dtOutput.Columns.Add(new DataColumn("TDQueryStartTime", typeof(DateTime)));
dtOutput.Columns.Add(new DataColumn("TDQueryEndTime", typeof(DateTime)));
dtOutput.Columns.Add(new DataColumn("TDQueryExecutionTime(minutes)", typeof(System.Double)));
dtOutput.Columns.Add(new DataColumn("SSASQueryStartTime", typeof(DateTime)));
dtOutput.Columns.Add(new DataColumn("SSASQueryEndTime", typeof(DateTime)));
dtOutput.Columns.Add(new DataColumn("SSASQueryExecutionTime(minutes)", typeof(System.Double)));
DataRow dr = dtOutput.NewRow();
dr["SSASModel"] = ssasModel;
dr["TableName"] = tableName;
dr["TDQuery"] = tdQuery;
dr["DAX"] = daxQuery;
dr["TDQueryStartTime"] = tdStartTime;
dr["TDQueryEndTime"] = tdEndTime;
dr["TDQueryExecutionTime(minutes)"] = (double)tdEndTime.Subtract(tdStartTime).Seconds / 60;
dr["SSASQueryStartTime"] = SSASStartTime;
dr["SSASQueryEndTime"] = SSASEndTime;
dr["SSASQueryExecutionTime(minutes)"] = (double)SSASEndTime.Subtract(SSASStartTime).Seconds / 60;
if (noOfRows == 0)
{
//tableName = tableName + "_NODATA";
dr["TestResults"] = "No data";
drSummary["TestResults"] = "No data";
}
else if (ret.Rows.Count > 0)
{
workbook.Worksheets.Add(ret, "ERRORS");
//tableName = tableName + "_FAILED";
dr["TestResults"] = "Failed";
drSummary["TestResults"] = "Failed";
}
else
{
//tableName = tableName + "_PASSED";
dr["TestResults"] = "Passed";
drSummary["TestResults"] = "Passed";
}
dtOutput.Rows.Add(dr);
workbook.Worksheets.Add(dtOutput, "TestOutput");
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine(e.StackTrace);
tableName = tableName + "_Exception";
DataTable ex = new DataTable("Exception");
ex.Columns.AddRange(new DataColumn[2] {
new DataColumn("ERROR1"), new DataColumn("ERROR2")
});
drSummary["Exception"] = e.Message;
ex.Rows.Add("Failed", e.Message);
ex.Rows.Add("", e.StackTrace);
workbook.Worksheets.Add(ex, "Exception");
}
finally
{
dtSummary.Rows.Add(drSummary);
workbook.SaveAs(od.FullName + "\\" + tableName + ".xlsx");
XLWorkbook summaryworkbook = new XLWorkbook();
summaryworkbook.Worksheets.Add(dtSummary, "TestSummary");
summaryworkbook.SaveAs(od.FullName + "\\TestSummary.xlsx");
}
}
}
});
}
public static void runAllDAX(Options o)
{
Utils utils = new Utils();
DataTable excel = utils.ImportExceltoDatatable(o.inExcel, "Queries");
int recordCount = 0;
DirectoryInfo od = new DirectoryInfo(o.outDir);
od.Create();
DataTable overallStatusDT = utils.getStatusDataTable();
using (var statusWorkbook = new XLWorkbook())
{
try
{
foreach (DataRow row in excel.Rows)
{
if (!keepRunning)
{
break;
}
string testName = row["NAME"].ToString();
if (testName.Trim().Equals(""))
{
continue;
}
string srcSSAS = "Data Source = " + row["SRC_SSAS"].ToString();
string srcSSASModel = row["SRC_SSAS_MODEL"].ToString();
string srcQuery = row["SRC_DAX"].ToString();
DateTime srcStartTime = DateTime.Now, srcEndTime = DateTime.Now;
String srcErrorMsg = "", status = "PASS";
int SPID = 0;
ADOTabularConnection srcConx = null;
try
{
Console.WriteLine("--------------------Src DAX (" + srcSSASModel + ")---------------------------");
Console.WriteLine(srcQuery);
DataTable srcDT = new DataTable();
DataTable ret = new DataTable();
try
{
status = "PASS";
srcConx = new ADOTabularConnection(srcSSAS, ADOTabular.Enums.AdomdType.AnalysisServices);
srcConx.ChangeDatabase(srcSSASModel);
srcStartTime = DateTime.Now;
srcDT = srcConx.ExecuteDaxQueryDataTable(srcQuery);
recordCount = srcDT.Rows.Count;
SPID = srcConx.SPID;
Console.WriteLine("Session:" + srcConx.SPID);
}
catch (Exception ee)
{
Console.WriteLine(ee.Message);
Console.WriteLine(ee.StackTrace);
srcErrorMsg = ee.Message;
}
finally
{
srcEndTime = DateTime.Now;
srcConx.Close();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine(e.StackTrace);
status = "FAILED";
}
finally
{
srcConx.Close();
utils.getStatusRow(overallStatusDT, testName, status, srcStartTime, srcEndTime, (double)srcEndTime.Subtract(srcStartTime).TotalMilliseconds,
recordCount, srcErrorMsg, srcQuery, SPID);
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine(e.StackTrace);
}
finally
{
statusWorkbook.AddWorksheet(overallStatusDT, "Status");
statusWorkbook.SaveAs(od.FullName + "\\" + "_OverallStatus.xlsx");
}
}
}
public static Utils.StatusRow runComparison(DataRow row, DirectoryInfo od, Utils.StatusRow statusrow)
{
string testName = row["NAME"].ToString();
Console.WriteLine("Test [" + testName + "] Started executing");
string srcSSAS = "Data Source = " + row["SRC_SSAS"].ToString();
string tgtSSAS = "Data Source = " + row["TGT_SSAS"].ToString();
string srcSSASModel = row["SRC_SSAS_MODEL"].ToString();
string tgtSSASModel = row["TGT_SSAS_MODEL"].ToString();
string srcQuery = row["SRC_DAX"].ToString();
string tgtQuery = row["TGT_DAX"].ToString();
DateTime srcStartTime = DateTime.Now, srcEndTime = DateTime.Now, tgtStartTime = DateTime.Now, tgtEndTime = DateTime.Now;
String srcErrorMsg = "", tgtErrorMsg = "", status = "PASS";
int srcNoOfRows = 0, tgtNoOfRows = 0, srcNoOfColumn = 0, tgtNoOfColumn = 0;
bool srcError = false, tgtError = false;
ADOTabularConnection srcConx = null;
ADOTabularConnection tgtConx = null;
try
{
//Console.WriteLine("--------------------Src DAX---------------------------");
//Console.WriteLine(srcQuery);
//Console.WriteLine("--------------------tgt DAX---------------------------");
//Console.WriteLine(tgtQuery);
DataTable srcDT = new DataTable();
DataTable tgtDT = new DataTable();
DataTable ret = new DataTable();
try
{
srcConx = new ADOTabularConnection(srcSSAS, ADOTabular.Enums.AdomdType.AnalysisServices);
srcConx.ChangeDatabase(srcSSASModel);
srcStartTime = DateTime.Now;
srcDT = srcConx.ExecuteDaxQueryDataTable(srcQuery);
}
catch (Exception ee)
{
Console.WriteLine(ee.Message);
Console.WriteLine(ee.StackTrace);
srcErrorMsg = ee.Message;
srcError = true;
}
finally
{
srcEndTime = DateTime.Now;
srcConx.Close();
}
try
{
tgtConx = new ADOTabularConnection(tgtSSAS, ADOTabular.Enums.AdomdType.AnalysisServices);
tgtConx.ChangeDatabase(tgtSSASModel);
tgtStartTime = DateTime.Now;
tgtDT = tgtConx.ExecuteDaxQueryDataTable(tgtQuery);
}
catch (Exception ee)
{
Console.WriteLine(ee.Message);
Console.WriteLine(ee.StackTrace);
tgtErrorMsg = ee.Message;
tgtError = true;
}
finally
{
tgtEndTime = DateTime.Now;
tgtConx.Close();
}
if (srcError || tgtError)
{
status = "FAILED";
}
else
{
srcNoOfRows = srcDT.Rows.Count;
tgtNoOfRows = tgtDT.Rows.Count;
srcNoOfColumn = srcDT.Columns.Count;
tgtNoOfColumn = tgtDT.Columns.Count;
if (srcNoOfRows == 0 || tgtNoOfRows == 0)
{
status = "FAILED";
srcErrorMsg = "No Data:" + srcNoOfRows;
tgtErrorMsg = "No Data:" + tgtNoOfRows;
}
else if (srcNoOfRows != tgtNoOfRows)
{
status = "FAILED";
srcErrorMsg = "Number of rows:" + srcNoOfRows;
tgtErrorMsg = "Number of rows:" + tgtNoOfRows;
}
else if (srcNoOfColumn != tgtNoOfColumn)
{
status = "FAILED";
srcErrorMsg = "Number of Columns:" + srcNoOfColumn;
tgtErrorMsg = "Number of Columns:" + tgtNoOfColumn;
}
else
{
ret = Utils.getDifferentRecords(srcDT, tgtDT);
if (ret.Rows.Count > 0)
{
status = "FAILED";
srcErrorMsg = "Data Mismatch";
}
}
}
DataTable statusDT = Utils.getStatusDataTable();
statusrow.NAME = testName;
statusrow.STATUS = status;
statusrow.SRC_SSASQueryStartTime = srcStartTime;
statusrow.SRC_SSASQueryEndTime = srcEndTime;
statusrow.SRC_SSASQueryExecutionTime = (double)srcEndTime.Subtract(srcStartTime).Seconds;
statusrow.TGT_SSASQueryStartTime = tgtStartTime;
statusrow.TGT_SSASQueryEndTime = tgtEndTime;
statusrow.TGT_SSASQueryExecutionTime = (double)tgtEndTime.Subtract(tgtStartTime).Seconds;
statusrow.SRC_Exception = srcErrorMsg;
statusrow.TGT_Exception = tgtErrorMsg;
statusrow.SRC_DAX = srcQuery;
statusrow.TGT_DAX = tgtQuery;
Utils.getStatusRow(statusDT, statusrow);
using (var workbook = new XLWorkbook())
{
try
{
workbook.AddWorksheet(statusDT, "Status");
workbook.AddWorksheet(srcDT, "Source");
workbook.AddWorksheet(tgtDT, "Target");
workbook.AddWorksheet(ret, "Delta");
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine(e.StackTrace);
}
finally
{
workbook.SaveAs(od.FullName + "\\" + status + "_" + testName + ".xlsx");
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine(e.StackTrace);
statusrow.STATUS = "FAILED";
}
finally
{
srcConx.Close();
tgtConx.Close();
//Utils.getStatusRow(overallStatusDT, statusrow);
//Console.WriteLine("Tast [" + testName + "] Finished with Status:" + statusrow.STATUS);
}
return(statusrow);
}