public void TestDemoStatisticsTimeIO()
{
GetConnection u = new GetConnection();
GetBatches b = new GetBatches();
GetBatchResult r = new GetBatchResult();
GetSqlInfo s = new GetSqlInfo();
string constring_1 = u.GetConnectionString("AdventureWorks2014");
string[] batches = b.GetFileContent(@"..\\..\\..\\SabinIO.Performance.StatisticsTimeIO\\Demos\\DemoStatisticsTimeIO.sql");
SqlConnection _conn = new SqlConnection(constring_1);
_conn.InfoMessage += new SqlInfoMessageEventHandler(s.Message);
for (int i = 0; i < batches.Length; i++)
{
_conn.Open();
SqlCommand cmd_1 = new SqlCommand(batches[i], _conn);
cmd_1.ExecuteNonQuery();
_conn.Close();
}
MatchCollection q = Regex.Matches(s.SqlInfoMessages[1], @"\d+");
Assert.IsTrue(Convert.ToInt32(q[1].ToString()) <= 1246);
MatchCollection w = Regex.Matches(s.SqlInfoMessages[2], @"\d+");
Assert.IsTrue(Convert.ToInt32(q[0].ToString()) <= 200);
}
public void TestUnionUnionAllDemo2()
{
GetConnection u = new GetConnection();
GetBatches b = new GetBatches();
GetBatchResult r = new GetBatchResult();
GetQueryPlan qp = new GetQueryPlan();
GetQueryPlanValues qv = new GetQueryPlanValues();
string constring_1 = u.GetConnectionString("AdventureWorks2014");
string[] batches = b.GetFileContent(@"..\\..\\..\\SabinIO.Performance.UnionUnionAll\\Demos\\UnionUnionAllDemo2.sql");
string SqlNamespace = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
string XPath = "//sql:Batch/sql:Statements/sql:StmtSimple/sql:QueryPlan[1]/sql:RelOp[1]/@LogicalOp";
for (int i = 0; i < batches.Length; i++)
{
string batch = batches[i];
DataSet ds = r.GetDataSet(batch, constring_1);
if (i == 4)
{
string QueryPlan = qp.GetQueryPlanForQuery(constring_1, batch);
string _operator = qv.GetSingleValue(QueryPlan, SqlNamespace, XPath);
Assert.AreEqual("Distinct Sort", _operator);
}
}
}
public void TestForcedParamDemo1()
{
GetConnection u = new GetConnection();
GetBatches b = new GetBatches();
GetBatchResult r = new GetBatchResult();
List <String> simpleExec = new List <String>();
List <String> forcedExec = new List <String>();
string constring_1 = u.GetConnectionString("SabinIO.ForcedParameterisation.Demo");
string[] batches = b.GetFileContent(@"..\\..\\..\\QueryExecution\\demos\\ForcedParamDemo1.sql");
for (int i = 0; i < batches.Length; i++)
{
string batch = batches[i];
DataSet ds = r.GetDataSet(batch, constring_1);
if (i == 1)
{
string t = ds.Tables["Table"].Rows[0]["is_parameterization_forced"].ToString();
Assert.AreEqual("False", t);
}
if (i == 7)
{
for (int a = 0; a < ds.Tables["Table"].Rows.Count; a++)
{
simpleExec.Add(ds.Tables["Table"].Rows[a]["execution_count"].ToString());
}
Assert.AreEqual("10", simpleExec[0]);
Assert.AreEqual("10", simpleExec[1]);
Assert.AreEqual("10", simpleExec[2]);
Assert.AreEqual("1", simpleExec[3]);
}
if (i == 9)
{
string t = ds.Tables["Table"].Rows[0]["is_parameterization_forced"].ToString();
Assert.AreEqual("True", t);
}
if (i == 15)
{
for (int a = 0; a < ds.Tables["Table"].Rows.Count; a++)
{
forcedExec.Add(ds.Tables["Table"].Rows[a]["execution_count"].ToString());
}
Assert.AreEqual("20", forcedExec[0]);
Assert.AreEqual("10", forcedExec[1]);
Assert.AreEqual("1", forcedExec[2]);
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection();
con.ConnectionString = GetConnection.GetConnectionString();
con.Open();
SqlDataReader dr;
SqlCommand cmd;
string referenceId = Session["referenceId"].ToString();
string query1 = "prc_ViewBookedTicket";
cmd = new SqlCommand(query1, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ReferenceId", referenceId);
dr = cmd.ExecuteReader();
dt.Load(dr);
con.Close();
GridView1.DataSource = dt;
GridView1.DataBind();
if (Session["tripType"].ToString().Equals("Round Trip"))
{
DataTable dt2 = new DataTable();
con.ConnectionString = GetConnection.GetConnectionString();
con.Open();
SqlDataReader dr2;
SqlCommand cmd2;
string referenceIdRoundTrip = Session["referenceIdRoundTrip"].ToString();
string query2 = "prc_ViewBookedTicket";
cmd2 = new SqlCommand(query2, con);
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.Parameters.AddWithValue("@ReferenceId", referenceIdRoundTrip);
dr2 = cmd2.ExecuteReader();
dt2.Load(dr2);
con.Close();
GridView2.DataSource = dt2;
GridView2.DataBind();
}
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
if (optionsBuilder.IsConfigured)
{
return;
}
optionsBuilder.UseSqlServer(GetConnection.GetConnectionString());
}
}
public void TestTruncateDeleteDemo1()
{
GetConnection u = new GetConnection();
GetBatches b = new GetBatches();
GetBatchResult r = new GetBatchResult();
GetSqlInfo s = new GetSqlInfo();
string constring_1 = u.GetConnectionString("SabinIO.Performance.TruncateDelete");
string[] batches = b.GetFileContent(@"..\\..\\..\\SabinIO.Performance.TruncateDelete\\Demos\\TruncateDeleteDemo1.sql");
SqlConnection _conn = new SqlConnection(constring_1);
_conn.InfoMessage += new SqlInfoMessageEventHandler(s.Message);
for (int i = 0; i < batches.Length; i++)
{
_conn.Open();
SqlCommand cmd_1 = new SqlCommand(batches[i], _conn);
cmd_1.CommandTimeout = 60;
cmd_1.ExecuteNonQuery();
_conn.Close();
}
MatchCollection q = Regex.Matches(s.SqlInfoMessages[1], @"\d+");
Assert.IsTrue(Convert.ToInt32(q[0].ToString()) <= 10);
Assert.IsTrue(Convert.ToInt32(q[1].ToString()) <= 10);
MatchCollection w = Regex.Matches(s.SqlInfoMessages[2], @"\d+");
Assert.IsTrue(Convert.ToInt32(w[0].ToString()) <= 10);
Assert.IsTrue(Convert.ToInt32(w[1].ToString()) <= 22);
Assert.IsTrue(Convert.ToInt32(w[2].ToString()) <= 10);
Assert.IsTrue(Convert.ToInt32(w[3].ToString()) <= 10);
Assert.IsTrue(Convert.ToInt32(w[4].ToString()) <= 10);
Assert.IsTrue(Convert.ToInt32(w[5].ToString()) <= 10);
Assert.IsTrue(Convert.ToInt32(w[6].ToString()) <= 10);
}
public void TestArithAbortDemo1()
{
GetConnection u = new GetConnection();
GetBatches b = new GetBatches();
GetBatchResult r = new GetBatchResult();
string constring_1 = u.GetConnectionString("SabinIO.ArithAbort.PlanCache");
string[] batches = b.GetFileContent(@"..\\..\\..\\SabinIO.ArithAbort.PlanCache\\demos\\ArithAbortDemo1.sql");
List <String> value = new List <String>();
List <String> exec = new List <String>();
for (int i = 0; i < batches.Length; i++)
{
string batch = batches[i];
if (i == 0)
{
batch = batch.Replace("100", "5");
}
if (i == 1)
{
batch = batch.Replace("50", "2");
}
DataSet ds = r.GetDataSet(batch, constring_1);
if (@i == 1)
{
for (int a = 0; a < ds.Tables["Table"].Rows.Count; a++)
{
value.Add(ds.Tables["Table"].Rows[a]["value"].ToString());
exec.Add(ds.Tables["Table"].Rows[a]["execution_count"].ToString());
}
Assert.AreEqual("251", value[0]);
Assert.AreEqual("4347", value[1]);
Assert.AreEqual("10", exec[0]);
Assert.AreEqual("2", exec[1]);
}
}
}
public void TestOutOfDateStatistics_Demo2()
{
GetConnection u = new GetConnection();
GetBatches b = new GetBatches();
GetBatchResult r = new GetBatchResult();
string constring_1 = u.GetConnectionString("SabinIO.Statistics.OutOfDate");
string[] batches = b.GetFileContent(@"..\\..\\..\\SabinIO.Statistics.OutOfDate\\demos\\OutOfDateStatistics_Demo2.sql");
int Rows = 0;
string StatsDate = string.Empty;
for (int i = 0; i < batches.Length; i++)
{
string batch = batches[i];
DataSet ds = r.GetDataSet(batch, constring_1);
if (i == 1)
{
Rows = Convert.ToInt32(ds.Tables["table"].Rows[0]["rows"]);
StatsDate = Convert.ToString(ds.Tables["table"].Rows[0]["column1"]);
}
if (i == 4 || i == 7 || i == 10)
{
Assert.IsTrue(Convert.ToInt32(ds.Tables["table"].Rows[0]["rows"]) > Rows);
Assert.AreEqual(Convert.ToString(ds.Tables["table"].Rows[0]["column1"]), StatsDate);
//increase row count for next test in this loop
Rows = Convert.ToInt32(ds.Tables["table"].Rows[0]["rows"]);
}
if (i == 13)
{
Assert.IsTrue(Convert.ToInt32(ds.Tables["table"].Rows[0]["rows"]) > Rows);
Assert.AreNotEqual(Convert.ToString(ds.Tables["table"].Rows[0]["column1"]), StatsDate);
}
}
}
public void TestForcedParamDemo2()
{
GetConnection u = new GetConnection();
GetBatches b = new GetBatches();
GetBatchResult r = new GetBatchResult();
string constring_1 = u.GetConnectionString("AdventureWorks2014");
string[] batches = b.GetFileContent(@"..\\..\\..\\QueryExecution\\demos\\ForcedParamDemo2.sql");
for (int i = 0; i < batches.Length; i++)
{
string batch = batches[i];
DataSet ds = r.GetDataSet(batch, constring_1);
if (i == 5)
{
DataRow[] adhoc = ds.Tables["Table"].Select(@"objtype = 'Adhoc'");
Assert.AreEqual(2, adhoc.Length);
Assert.AreEqual(1, Convert.ToInt32(adhoc[0]["usecounts"]));
Assert.AreEqual(1, Convert.ToInt32(adhoc[1]["usecounts"]));
DataRow[] Prepared = ds.Tables["Table"].Select(@"objtype = 'Prepared'");
Assert.AreEqual(1, Prepared.Length);
Assert.AreEqual(2, Convert.ToInt32(Prepared[0]["usecounts"]));
Assert.AreNotEqual(Convert.ToString(adhoc[0]["query_plan"]), Convert.ToString(Prepared[0]["query_plan"]));
Assert.AreNotEqual(Convert.ToString(adhoc[1]["query_plan"]), Convert.ToString(Prepared[0]["query_plan"]));
}
if (i == 10)
{
DataRow[] adhoc = ds.Tables["Table"].Select(@"objtype = 'Adhoc'");
Assert.AreEqual(2, adhoc.Length);
Assert.AreEqual(1, Convert.ToInt32(adhoc[0]["usecounts"]));
Assert.AreEqual(1, Convert.ToInt32(adhoc[1]["usecounts"]));
}
}
}
public void TestUDFDemo3()
{
GetConnection u = new GetConnection();
GetBatches b = new GetBatches();
GetBatchResult r = new GetBatchResult();
string constring_1 = u.GetConnectionString("AdventureWorks2014");
string[] batches = b.GetFileContent(@"..\\..\\..\\SabinIO.Performance.UDF\\Demos\\Udf_Demo3.sql");
long batchThreeDuration = 0;
long batchSixDuration = 0;
for (int i = 0; i < batches.Length; i++)
{
string batch = batches[i];
DataSet ds = r.GetDataSet(batch, constring_1);
if (i == 3)
{
Stopwatch sw = new Stopwatch();
sw.Start();
DataSet ds_5 = r.GetDataSet(batch, constring_1);
sw.Stop();
batchThreeDuration = sw.ElapsedMilliseconds;
}
if (i == 6)
{
Stopwatch sw = new Stopwatch();
sw.Start();
DataSet ds_5 = r.GetDataSet(batch, constring_1);
sw.Stop();
batchSixDuration = sw.ElapsedMilliseconds;
Assert.IsTrue(batchThreeDuration > batchSixDuration);
}
}
}
public void TestUDFDemo1()
{
GetConnection u = new GetConnection();
GetBatches b = new GetBatches();
GetBatchResult r = new GetBatchResult();
string constring_1 = u.GetConnectionString("SabinIO.Performance.UDF");
string[] batches = b.GetFileContent(@"..\\..\\..\\SabinIO.Performance.UDF\\Demos\\Udf_Demo1.sql");
for (int i = 0; i < batches.Length; i++)
{
string batch = batches[i];
DataSet ds = r.GetDataSet(batch, constring_1);
if (i == 3)
{
DataView v = new DataView(ds.Tables["table"]);
DataTable distinctValues = v.ToTable(true, "CurDate");
//Ive seen cases of values looking identical but not being handled as identical, so the count just confirms that everything is identical
int count = Convert.ToInt32(distinctValues.Rows.Count);
if (count == 1)
{
Assert.AreEqual(1, Convert.ToInt32(distinctValues.Rows.Count));
}
if (count == 2)
{
Assert.AreEqual(ds.Tables["Table"].Rows[0]["CurDate"], ds.Tables["Table"].Rows[1]["CurDate"]);
}
}
if (i == 5)
{
Assert.IsTrue(ds.Tables["Table"].Rows.Count >= 2);
}
}
}
protected void txtEmailId_TextChanged(object sender, EventArgs e)
{
if (Page.IsPostBack == true)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = GetConnection.GetConnectionString();
SqlCommand cmd = new SqlCommand("select * from Customer where EmailId='" + txtEmailId.Text + "'", con);
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "Customer");
if (ds.Tables[0].Rows.Count > 0)
{
lblEmailId.Visible = true;
lblEmailId.Text = "Email Id already exists.";
}
}
}
public pemms_epdController()
{
_connectionString = GetConnection.GetConnectionString();
}
public void TestParameterisationDemo()
{
GetConnection u = new GetConnection();
GetBatches b = new GetBatches();
GetBatchResult r = new GetBatchResult();
GetQueryPlanValues q = new GetQueryPlanValues();
string constring_1 = u.GetConnectionString("AdventureWorks2014");
string[] batches = b.GetFileContent(@"..\\..\\..\\SabinIO.Parameterisation\\demos\\Parameterisation.Demo1.sql");
string XPath = "//sql:Batch/sql:Statements/sql:StmtSimple/" + "sql:QueryPlan[1]/sql:RelOp[1]/@EstimateRows";
string SqlNamespace = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
string ConvertQueryPlan;
string Batch6EstimatedRows = String.Empty;
string Batch9EstimatedRows = String.Empty;
string Batch12EstimatedRows = String.Empty;
string Batch15EstimatedRows = String.Empty;
for (int i = 0; i < batches.Length; i++)
{
string batch = batches[i];
DataSet ds = r.GetDataSet(batch, constring_1);
if (i == 4)
{
Assert.AreEqual(2, ds.Tables["Table"].Rows.Count);
DataRow[] adhoc = ds.Tables["Table"].Select(@"objtype = 'Adhoc'");
Assert.AreEqual(2, adhoc.Length);
}
if (i == 6)
{
Assert.AreEqual(3, ds.Tables["Table"].Rows.Count);
DataRow[] Prepared = ds.Tables["Table"].Select(@"objtype = 'Prepared'");
Assert.AreEqual(1, Prepared.Length);
ConvertQueryPlan = Convert.ToString(Prepared[0]["query_plan"]);
Batch6EstimatedRows = q.GetSumOfValues(ConvertQueryPlan, SqlNamespace, XPath);
}
if (i == 9 || i == 12 || i == 15)
{
Assert.AreEqual(1, ds.Tables["Table"].Rows.Count);
DataRow[] Prepared = ds.Tables["Table"].Select(@"objtype = 'Prepared'");
ConvertQueryPlan = Convert.ToString(Prepared[0]["query_plan"]);
if (i == 9)
{
Batch9EstimatedRows = q.GetSumOfValues(ConvertQueryPlan, SqlNamespace, XPath);
Assert.AreNotEqual(Batch6EstimatedRows, Batch9EstimatedRows);
}
if (i == 12)
{
Batch12EstimatedRows = q.GetSumOfValues(ConvertQueryPlan, SqlNamespace, XPath);
Assert.AreEqual(Batch6EstimatedRows, Batch12EstimatedRows);
}
if (i == 15)
{
Batch15EstimatedRows = q.GetSumOfValues(ConvertQueryPlan, SqlNamespace, XPath);
Assert.AreNotEqual(Batch12EstimatedRows, Batch15EstimatedRows);
Assert.AreNotEqual(Batch6EstimatedRows, Batch15EstimatedRows);
}
}
}
}
public void TestUDFDemo2()
{
GetConnection u = new GetConnection();
GetBatches b = new GetBatches();
GetBatchResult r = new GetBatchResult();
GetQueryPlan qp = new GetQueryPlan();
GetQueryPlanValues qv = new GetQueryPlanValues();
string constring_1 = u.GetConnectionString("AdventureWorks2014");
string[] batches = b.GetFileContent(@"..\\..\\..\\SabinIO.Performance.UDF\\Demos\\Udf_Demo2.sql");
string SqlNamespace = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
string XPath = "//sql:Batch/sql:Statements/sql:StmtSimple/sql:QueryPlan[1]/sql:RelOp/sql:StreamAggregate/sql:RelOp/sql:Top/sql:RelOp/@LogicalOp";
long batchFiveDuration = 0;
long batchSixDuration = 0;
long batchTwelveDuration = 0;
long batchThirteenDuration = 0;
for (int i = 0; i < batches.Length; i++)
{
string batch = batches[i];
DataSet ds = r.GetDataSet(batch, constring_1);
if (i == 5)
{
Stopwatch sw = new Stopwatch();
sw.Start();
DataSet ds_5 = r.GetDataSet(batch, constring_1);
sw.Stop();
batchFiveDuration = sw.ElapsedMilliseconds;
string QueryPlan = qp.GetQueryPlanForQuery(constring_1, batch);
string _operator = qv.GetSingleValue(QueryPlan, SqlNamespace, XPath);
Assert.AreEqual("Compute Scalar", _operator);
}
if (i == 6)
{
Stopwatch sw = new Stopwatch();
sw.Start();
DataSet ds_6 = r.GetDataSet(batch, constring_1);
sw.Stop();
batchSixDuration = sw.ElapsedMilliseconds;
Assert.IsTrue(batchFiveDuration < batchSixDuration);
}
if (i == 12)
{
Stopwatch sw = new Stopwatch();
sw.Start();
DataSet ds_5 = r.GetDataSet(batch, constring_1);
sw.Stop();
batchTwelveDuration = sw.ElapsedMilliseconds;
Assert.IsTrue(batchTwelveDuration < batchSixDuration);
}
if (i == 13)
{
Stopwatch sw = new Stopwatch();
sw.Start();
DataSet ds_6 = r.GetDataSet(batch, constring_1);
sw.Stop();
batchThirteenDuration = sw.ElapsedMilliseconds;
Assert.IsTrue(batchTwelveDuration <= batchThirteenDuration);
}
}
}
public void TestOutOfDateStatistics_Demo1()
{
GetConnection u = new GetConnection();
GetBatches b = new GetBatches();
GetBatchResult r = new GetBatchResult();
GetQueryPlan qp = new GetQueryPlan();
GetQueryPlanValues qv = new GetQueryPlanValues();
string constring_1 = u.GetConnectionString("SabinIO.Statistics.OutOfDate");
string[] batches = b.GetFileContent(@"..\\..\\..\\SabinIO.Statistics.OutOfDate\\demos\\OutOfDateStatistics_Demo1.sql");
string SqlNamespace = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
string XPath = "//sql:Batch/sql:Statements/sql:StmtSimple/sql:QueryPlan[1]/sql:RelOp[1]/@EstimateRows";
string XPathForSproc = "//sql:Batch/sql:Statements/sql:StmtSimple/sql:StoredProc/sql:Statements/sql:StmtSimple[2]/sql:QueryPlan[1]/sql:RelOp[1]/@EstimateRows";
for (int i = 0; i < batches.Length; i++)
{
string batch = batches[i];
DataSet ds = r.GetDataSet(batch, constring_1);
if (i == 6)
{
Assert.AreEqual(0, Convert.ToInt32(ds.Tables["table"].Rows[0]["Column1"]));
string QueryPlan = qp.GetQueryPlanForQuery(constring_1, batch);
string RowEstimate = qv.GetSumOfValues(QueryPlan, SqlNamespace, XPath);
Assert.AreEqual("1", RowEstimate);
}
if (i == 7)
{
Assert.AreEqual(0, Convert.ToInt32(ds.Tables["table"].Rows[0]["RowsAffected"]));
string QueryPlan = qp.GetQueryPlanForQuery(constring_1, batch);
string RowEstimate = qv.GetSumOfValues(QueryPlan, SqlNamespace, XPathForSproc);
Assert.AreEqual("1", RowEstimate);
}
if (i == 9 || i == 11)
{
Assert.AreEqual(100, Convert.ToInt32(ds.Tables["table"].Rows[0]["RowsAffected"]));
string QueryPlan = qp.GetQueryPlanForQuery(constring_1, batch);
string RowEstimate = qv.GetSumOfValues(QueryPlan, SqlNamespace, XPathForSproc);
Assert.AreEqual("1", RowEstimate);
}
if (i == 4 || i == 12)
{
DateTime d = new DateTime(2016, 01, 10, 23, 45, 36);
DateTime t = Convert.ToDateTime(ds.Tables["table"].Rows[199]["RANGE_HI_KEY"]);
Assert.AreEqual(200, ds.Tables["table"].Rows.Count);
Assert.AreEqual(d, t);
}
if (i == 14)
{
DateTime d = new DateTime(2016, 01, 11, 23, 45, 36);
DateTime t = Convert.ToDateTime(ds.Tables["table"].Rows[199]["RANGE_HI_KEY"]);
Assert.AreEqual(200, ds.Tables["table"].Rows.Count);
Assert.AreEqual(d, t);
}
if (i == 15)
{
Assert.AreEqual(100, Convert.ToInt32(ds.Tables["table"].Rows[0]["RowsAffected"]));
string QueryPlan = qp.GetQueryPlanForQuery(constring_1, batch);
string RowEstimate = qv.GetSumOfValues(QueryPlan, SqlNamespace, XPathForSproc);
Assert.AreEqual("99.125", RowEstimate);
}
}
}
