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); }