SHARE
TWEET

Test Behavior of SqlCommand.Prepare() on SQL Server

SqlQuantumLeap Feb 19th, 2016 (edited) 149 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /**************************************************************************
  2.     .NET / C# Console App to test the behavior of calling SqlCommand.Prepare().
  3.  
  4.     This code relates to the following DBA.StackExchange answer:
  5.     http://dba.stackexchange.com/questions/129659/what-is-the-sense-and-benefit-of-using-sqlcommand-prepare/129721#129721
  6.  
  7.     Copyright (c) 2016 Sql Quantum Leap
  8.     http://www.SqlQuantumLeap.com
  9.  
  10.  **************************************************************************/
  11.  
  12. /*******************************************************
  13.  *******************************************************
  14.  ** Be sure to have SQL Server Profler running a trace.
  15.  ** The trace should have the following events selected:
  16.  **
  17.  **  1) Security Audit - Audit Login
  18.  **
  19.  **  2) Stored Procedures - RPC:Completed
  20.  **  3) Stored Procedures - RPC:Starting
  21.  **  4) Stored Procedures - SP:CacheHit
  22.  **  5) Stored Procedures - SP:CacheInsert
  23.  **  6) Stored Procedures - SP:CacheMiss
  24.  **  7) Stored Procedures - SP:CacheRemove
  25.  **
  26.  **  8) TSQL - Exec Prepared SQL
  27.  **  9) TSQL - Prepare SQL
  28.  ** 10) TSQL - Unprepare SQL
  29.  **
  30.  ** Also, add a "Column Filter" under "ApplicationName":
  31.  ** Under "Like" add the text "PreparedSqlTest", but without the quotes.
  32.  **
  33.  *******************************************************
  34.  *******************************************************/
  35.  
  36. using System;
  37. using System.Data;
  38. using System.Data.SqlClient;
  39.  
  40. namespace SqlQuantumLeap.PreparedSQL
  41. {
  42.     class Program
  43.     {
  44.         static void Main(string[] args)
  45.         {
  46.             string _ConnectionString = "Trusted_Connection=true; Application Name=PreparedSqlTest;";
  47.  
  48.             if (args.Length >= 1 && !String.IsNullOrWhiteSpace(args[0]))
  49.             {
  50.                 _ConnectionString += args[0]; // extra stuff for the ConnectionString
  51.             }
  52.  
  53.             using (SqlConnection _Connection = new SqlConnection(_ConnectionString))
  54.             {
  55.                 using (SqlCommand _Command = _Connection.CreateCommand())
  56.                 {
  57.                     Console.Clear(); // CLS
  58.  
  59.                     _Command.CommandText = @"
  60.                        IF (OBJECT_ID('tempdb.dbo.PreparedSqlTest') IS NULL)
  61.                        BEGIN
  62.                            CREATE TABLE tempdb.dbo.PreparedSqlTest
  63.                            (
  64.                                [PreparedSqlTestID] INT NOT NULL IDENTITY(1, 1) CONSTRAINT [PK_PreparedSqlTest] PRIMARY KEY,
  65.                                [ObjectType] VARCHAR(50),
  66.                                [InsertTime] DATETIME NOT NULL CONSTRAINT [DF_PreparedSqlTest_InsertTime] DEFAULT (GETDATE())
  67.                            );
  68.                        END;
  69.                        INSERT INTO tempdb.dbo.PreparedSqlTest ([ObjectType]) VALUES (@ObjectType);
  70.  
  71.                        SELECT so.[name], so.[object_id], so.[schema_id], so.[create_date]
  72.                        FROM[sys].[objects] so
  73.                        WHERE so.[type] = @ObjectType;";
  74.  
  75.                     SqlParameter _ObjectType = new SqlParameter("@ObjectType", SqlDbType.Char, 2);
  76.                     _Command.Parameters.Add(_ObjectType);
  77.  
  78.                     Pause("Press the <any> key to open the connection to SQL Server." +
  79.                             "\n\tSQL Server Profiler will register the Audit Login event, and either" +
  80.                             "\n\tSP:CacheInsert or SP:CacheHit if there are any Logon Triggers.");
  81.  
  82.                     _Connection.Open();
  83.  
  84.                     Pause("Press the <any> key to call SqlCommand.Prepare()." +
  85.                             "\n\tSQL Server Profiler will not register any events.");
  86.  
  87.                     _Command.Prepare();
  88.  
  89.                     Pause("Press the <any> key to set the parameter value to 'S'." +
  90.                             "\n\tSQL Server Profiler will not register any events.");
  91.  
  92.                     _ObjectType.Value = "S";
  93.  
  94.                     Pause("Press the <any> key to call SqlCommand.ExecuteReader()." +
  95.                             "\n\tSQL Server Profiler will register the following events:" +
  96.                             "\n\tRPC:Starting, SP:Cache(Hit or Insert), Prepare SQL, and RPC:Completed.");
  97.  
  98.                     //_Command.ExecuteNonQuery();
  99.                     GetAndDisplayResults(_Command);
  100.  
  101.                     Console.WriteLine("\n\tIn SSMS, execute:\n\t\tSELECT * FROM tempdb.dbo.PreparedSqlTest;");
  102.  
  103.                     Pause("Press the <any> key to change the parameter value to 'U'." +
  104.                             "\n\tSQL Server Profiler will not register any events.");
  105.  
  106.                     _ObjectType.Value = "U";
  107.  
  108.                     Pause("Press the <any> key to call SqlCommand.ExecuteReader() again." +
  109.                             "\n\tSQL Server Profiler will register the following events:" +
  110.                             "\n\tRPC:Starting, SP:CacheHit, Exec Prepared SQL, and RPC:Completed.");
  111.  
  112.                     //_Command.ExecuteNonQuery();
  113.                     GetAndDisplayResults(_Command);
  114.  
  115.                     Console.WriteLine("\n\tIn SSMS, execute:\n\t\tSELECT * FROM tempdb.dbo.PreparedSqlTest;");
  116.  
  117.                     Pause("Press the <any> key to close the connection and exit.");
  118.                 }
  119.             }
  120.  
  121.             return;
  122.         }
  123.  
  124.         private static void Pause(string Message)
  125.         {
  126.             Console.Write("\n\t");
  127.             Console.WriteLine(Message);
  128.  
  129.             Console.ReadKey(true);
  130.  
  131.             return;
  132.         }
  133.  
  134.         private static int GetAndDisplayResults(SqlCommand Command)
  135.         {
  136.             int _RowNumber = 0;
  137.  
  138.             using (SqlDataReader _Reader = Command.ExecuteReader())
  139.             {
  140.                 Console.WriteLine("\n\tQuery executed.");
  141.  
  142.                 if (_Reader.HasRows)
  143.                 {
  144.                     Console.WriteLine("\n\tRows were returned:\n");
  145.  
  146.                     object[] _Fields = new object[_Reader.FieldCount];
  147.  
  148.                     while (_Reader.Read())
  149.                     {
  150.                         _Reader.GetValues(_Fields);
  151.  
  152.                         Console.WriteLine(String.Concat(
  153.                                             ++_RowNumber,
  154.                                             "\t\"",
  155.                                             String.Join("\", \"", _Fields),
  156.                                             "\"")
  157.                                         );
  158.                     }
  159.                 }
  160.             }
  161.  
  162.             return _RowNumber;
  163.         }
  164.     }
  165. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top