/**************************************************************************
.NET / C# Console App to test the behavior of calling SqlCommand.Prepare().
This code relates to the following DBA.StackExchange answer:
http://dba.stackexchange.com/questions/129659/what-is-the-sense-and-benefit-of-using-sqlcommand-prepare/129721#129721
Copyright (c) 2016 Sql Quantum Leap
http://www.SqlQuantumLeap.com
**************************************************************************/
/*******************************************************
*******************************************************
** Be sure to have SQL Server Profler running a trace.
** The trace should have the following events selected:
**
** 1) Security Audit - Audit Login
**
** 2) Stored Procedures - RPC:Completed
** 3) Stored Procedures - RPC:Starting
** 4) Stored Procedures - SP:CacheHit
** 5) Stored Procedures - SP:CacheInsert
** 6) Stored Procedures - SP:CacheMiss
** 7) Stored Procedures - SP:CacheRemove
**
** 8) TSQL - Exec Prepared SQL
** 9) TSQL - Prepare SQL
** 10) TSQL - Unprepare SQL
**
** Also, add a "Column Filter" under "ApplicationName":
** Under "Like" add the text "PreparedSqlTest", but without the quotes.
**
*******************************************************
*******************************************************/
using System;
using System.Data;
using System.Data.SqlClient;
namespace SqlQuantumLeap.PreparedSQL
{
class Program
{
static void Main(string[] args)
{
string _ConnectionString = "Trusted_Connection=true; Application Name=PreparedSqlTest;";
if (args.Length >= 1 && !String.IsNullOrWhiteSpace(args[0]))
{
_ConnectionString += args[0]; // extra stuff for the ConnectionString
}
using (SqlConnection _Connection = new SqlConnection(_ConnectionString))
{
using (SqlCommand _Command = _Connection.CreateCommand())
{
Console.Clear(); // CLS
_Command.CommandText = @"
IF (OBJECT_ID('tempdb.dbo.PreparedSqlTest') IS NULL)
BEGIN
CREATE TABLE tempdb.dbo.PreparedSqlTest
(
[PreparedSqlTestID] INT NOT NULL IDENTITY(1, 1) CONSTRAINT [PK_PreparedSqlTest] PRIMARY KEY,
[ObjectType] VARCHAR(50),
[InsertTime] DATETIME NOT NULL CONSTRAINT [DF_PreparedSqlTest_InsertTime] DEFAULT (GETDATE())
);
END;
INSERT INTO tempdb.dbo.PreparedSqlTest ([ObjectType]) VALUES (@ObjectType);
SELECT so.[name], so.[object_id], so.[schema_id], so.[create_date]
FROM[sys].[objects] so
WHERE so.[type] = @ObjectType;";
SqlParameter _ObjectType = new SqlParameter("@ObjectType", SqlDbType.Char, 2);
_Command.Parameters.Add(_ObjectType);
Pause("Press the <any> key to open the connection to SQL Server." +
"\n\tSQL Server Profiler will register the Audit Login event, and either" +
"\n\tSP:CacheInsert or SP:CacheHit if there are any Logon Triggers.");
_Connection.Open();
Pause("Press the <any> key to call SqlCommand.Prepare()." +
"\n\tSQL Server Profiler will not register any events.");
_Command.Prepare();
Pause("Press the <any> key to set the parameter value to 'S'." +
"\n\tSQL Server Profiler will not register any events.");
_ObjectType.Value = "S";
Pause("Press the <any> key to call SqlCommand.ExecuteReader()." +
"\n\tSQL Server Profiler will register the following events:" +
"\n\tRPC:Starting, SP:Cache(Hit or Insert), Prepare SQL, and RPC:Completed.");
//_Command.ExecuteNonQuery();
GetAndDisplayResults(_Command);
Console.WriteLine("\n\tIn SSMS, execute:\n\t\tSELECT * FROM tempdb.dbo.PreparedSqlTest;");
Pause("Press the <any> key to change the parameter value to 'U'." +
"\n\tSQL Server Profiler will not register any events.");
_ObjectType.Value = "U";
Pause("Press the <any> key to call SqlCommand.ExecuteReader() again." +
"\n\tSQL Server Profiler will register the following events:" +
"\n\tRPC:Starting, SP:CacheHit, Exec Prepared SQL, and RPC:Completed.");
//_Command.ExecuteNonQuery();
GetAndDisplayResults(_Command);
Console.WriteLine("\n\tIn SSMS, execute:\n\t\tSELECT * FROM tempdb.dbo.PreparedSqlTest;");
Pause("Press the <any> key to close the connection and exit.");
}
}
return;
}
private static void Pause(string Message)
{
Console.Write("\n\t");
Console.WriteLine(Message);
Console.ReadKey(true);
return;
}
private static int GetAndDisplayResults(SqlCommand Command)
{
int _RowNumber = 0;
using (SqlDataReader _Reader = Command.ExecuteReader())
{
Console.WriteLine("\n\tQuery executed.");
if (_Reader.HasRows)
{
Console.WriteLine("\n\tRows were returned:\n");
object[] _Fields = new object[_Reader.FieldCount];
while (_Reader.Read())
{
_Reader.GetValues(_Fields);
Console.WriteLine(String.Concat(
++_RowNumber,
"\t\"",
String.Join("\", \"", _Fields),
"\"")
);
}
}
}
return _RowNumber;
}
}
}
Comments
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎