Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /**************************************************************************
- .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;
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement