/**************************************************************************
    .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;
        }
    }
}