using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MeasureTempdbCausedByTVPs
{
    class Program
    {
        static void Main(string[] args)
        {
            StreamWriter file = new StreamWriter(@"c:\temp\output.csv"); 
            for (int i = 5; i <= 4000; i += 5)
            {
                int gam;
                int pfs;
                int sgam;
                CreateType( i );
                string begin = GetXactString();
                SelectFromTVP();
                string end = GetXactString();
                MeasureTempdb( begin, end, out gam, out pfs, out sgam );
                Output( file, i, gam, pfs, sgam, begin, end );
            }
            file.Flush();
            file.Close();
        }

        private static void Output( StreamWriter sw, params object[] objects ) {

            string outputString = string.Join( "\t", objects );
            sw.WriteLine( outputString );
            Console.WriteLine( outputString );
        }

        private static void MeasureTempdb( string begin, string end, out int gam, out int pfs, out int sgam ) {
            gam = 0;
            pfs = 0;
            sgam = 0;
            string sql = @"select Context from fn_dblog(@begin, @end);";
            string connectionString = GetConnectionString("tempdb");
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.Add( "@begin", SqlDbType.VarChar ).Value = begin;
            cmd.Parameters.Add( "@end", SqlDbType.VarChar ).Value = end;
            SqlDataReader reader = cmd.ExecuteReader();
            while( reader.Read() ) {
                if( reader[ "Context" ].ToString() == "LCX_GAM" ) {
                    gam ++;
                } else if( reader[ "Context" ].ToString() == "LCX_PFS" ) {
                    pfs++;
                }
                else if( reader[ "Context" ].ToString() == "LCX_SGAM" ) {
                    sgam++;
                }
            }
                
            conn.Close();            
        }

        private static void SelectFromTVP() {
            string sql = @"Select COUNT(1) From @TVP";

            string connectionString = GetConnectionString( "testTVPs" );
            SqlConnection conn = new SqlConnection( connectionString );
            
            DataTable dt = new DataTable();
            dt.Columns.Add("bigMessage", typeof(string));

            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            SqlParameter tvpParam = cmd.Parameters.Add("TVP", SqlDbType.Structured);
            tvpParam.Value = dt;
            tvpParam.TypeName = "AMessage";

            cmd.ExecuteNonQuery();

            conn.Close();
        }

        private static string GetXactString() {
            string sql = @"
              -- get the latest lsn for tempdb
              declare @xact_seqno binary(10);
              declare @xact_seqno_string varchar(50);
              exec sp_replincrementlsn @xact_seqno OUTPUT;
              set @xact_seqno_string = '0x' + CONVERT(varchar(50), @xact_seqno, 2);
              set @xact_seqno_string = stuff(@xact_seqno_string, 11, 0, ':');
              set @xact_seqno_string = stuff(@xact_seqno_string, 20, 0, ':');
              select @xact_seqno_string;";
            
            string connectionString = GetConnectionString("tempdb");
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            string xactNo = cmd.ExecuteScalar().ToString();
            conn.Close();
            return xactNo;
        }

        private static void CreateType( int i ) {
            string sql = string.Format(@"
                if exists (select * from sys.types where name like 'AMessage')
                begin
                  drop type AMessage;
                end;
                
                create type AMessage AS TABLE
                (
                  bigMessage nvarchar({0})
                );", i);

            string connectionString = GetConnectionString("testTVPs");
            SqlConnection conn = new SqlConnection( connectionString );
            conn.Open();
            SqlCommand cmd = new SqlCommand( sql, conn );
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        private static string GetConnectionString(string databaseName) {
            SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder {
                DataSource = @".\sql2012",
                InitialCatalog = databaseName,
                IntegratedSecurity = true
            };
            return csb.ToString();
        }
    }
}