using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections.Generic;
using Microsoft.SqlServer.Server;

class TVPtest {

   protected static void PrintSqlMsgs(SqlErrorCollection msgs) {
      foreach (SqlError e in msgs) {
         Console.WriteLine (
            "Msg {0}, Severity {1}, State: {2}, Procedure {3}, Line no: {4}",
            e.Number.ToString(), e.Class.ToString(), e.State.ToString(),
            e.Procedure, e.LineNumber.ToString()
         );
         Console.WriteLine(e.Message);
      }
   }

   protected static void SqlInfoMessage(object                  sender,
                                        SqlInfoMessageEventArgs ea) {
      PrintSqlMsgs(ea.Errors);
   }

   private static void Main() {

      // Connection string. Change to fit to your environment.
      const string connstr =
            "Application Name=TVPtest;Integrated Security=SSPI;" +
            "Data Source=.;Initial Catalog=listtest";

      SqlConnection cn = new SqlConnection(connstr);
      cn.Open();
      cn.InfoMessage += SqlInfoMessage;
      cn.FireInfoMessageEventOnUserErrors = true;

      int[] listlens = {20, 200, 650, 2000, 10000, 50000};

      // Run initial SQL commands to use.
      SqlCommand initcmd = cn.CreateCommand();
      initcmd.CommandType = CommandType.Text;
      initcmd.CommandTimeout = 0;
      initcmd.CommandText = @"
CREATE TABLE #Int_JOIN   (word   nvarchar(50) NULL)
CREATE TABLE #Int_EXISTS (word   nvarchar(50) NULL)
CREATE TABLE #Int_UNPACK (number int NULL)
CREATE TABLE #Str_JOIN   (wordno int NULL,
                          guid   char(36) NULL)
CREATE TABLE #Str_EXISTS (wordno int NULL,
                          guid   char(36) NULL)
CREATE TABLE #Str_UNPACK (word   nvarchar(50) NULL)";
      initcmd.ExecuteNonQuery();

      // Just like this one.
      initcmd.CommandText = @"SELECT word
                              FROM    usrdictwords
                              ORDER BY newid()";
      SqlDataAdapter da = new SqlDataAdapter(initcmd);
      DataSet datastore = new DataSet();
      da.Fill(datastore);
      da.Dispose();
      initcmd.Dispose();

      // Set up command that we use in the loop.
      SqlCommand begintrans = cn.CreateCommand();
      begintrans.CommandType = CommandType.Text;
      begintrans.CommandText = "BEGIN TRANSACTION";

      SqlCommand commit = cn.CreateCommand();
      commit.CommandType = CommandType.Text;
      commit.CommandText = "COMMIT TRANSACTION";

      SqlCommand start_client_timer = cn.CreateCommand();
      start_client_timer.CommandType = CommandType.StoredProcedure;
      start_client_timer.CommandText = "dbo.start_client_timer";

      SqlCommand get_clientms = cn.CreateCommand();
      get_clientms.CommandType = CommandType.StoredProcedure;
      get_clientms.CommandText = "dbo.get_clientms";
      get_clientms.Parameters.Add("@clientms", SqlDbType.Int);
      get_clientms.Parameters.Add("@starttime", SqlDbType.DateTime);
      get_clientms.Parameters["@clientms"].Direction = ParameterDirection.InputOutput;
      get_clientms.Parameters["@starttime"].Direction = ParameterDirection.InputOutput;
      get_clientms.Parameters["@clientms"].Value = 0;
      get_clientms.Parameters["@starttime"].Value = SqlDateTime.Null;

      SqlCommand testsp = cn.CreateCommand();
      testsp.CommandText = "dbo.TVP$PK_Str_UNPACK_test";
      testsp.CommandType = CommandType.StoredProcedure;
      testsp.Parameters.Add("@tbl", SqlDbType.Structured);
      testsp.Parameters["@tbl"].Direction = ParameterDirection.Input;
      testsp.Parameters["@tbl"].TypeName = "stringlist_pktype";
      testsp.Parameters.Add("@tookms", SqlDbType.Int);
      testsp.Parameters["@tookms"].Value = 0;
      testsp.Parameters["@tookms"].Direction = ParameterDirection.InputOutput;

      SqlCommand insert_timing = cn.CreateCommand();
      insert_timing.CommandType = CommandType.StoredProcedure;
      insert_timing.CommandText = "dbo.insert_timing";
      insert_timing.Parameters.Add("@method", SqlDbType.VarChar, 20);
      insert_timing.Parameters.Add("@datatype", SqlDbType.VarChar, 3);
      insert_timing.Parameters.Add("@optype", SqlDbType.VarChar, 6);
      insert_timing.Parameters.Add("@listlen", SqlDbType.Int);
      insert_timing.Parameters.Add("@testrun", SqlDbType.TinyInt);
      insert_timing.Parameters.Add("@tookms", SqlDbType.Int);
      insert_timing.Parameters.Add("@clientms", SqlDbType.Int);
      insert_timing.Parameters.Add("@inputsize", SqlDbType.Int);
      insert_timing.Parameters.Add("@starttime", SqlDbType.DateTime);

      int datastore_ix = 0;

      foreach (int listlen in listlens) {
         for (int selector = 0; selector < 8; selector++) {
            for (int testrun = 0; testrun <= 10; testrun++) {
               int clientms,
                   tookms;
               SqlDateTime starttime;
               string method   = "TVP$" + (selector % 2 == 0 ? "PK" : "NOPK") +
                                    "$" + (selector % 4 <= 1 ? "DT" : "LIST") +
                                    "$" + (selector <= 3 ? "A" : "B"),
                      procname = "dbo.TVP" + (selector % 2 == 0 ? "$PK" : "") +
                                 "_Str_UNPACK_test",
                      tbltype  = (selector % 2 == 0 ? "stringlist_pktype"
                                                    : "stringlist_tbltype");

               if (datastore_ix > 1000000) {
                  datastore_ix = 0;
                }

               List <SqlDataRecord> list;
               DataTable dt;

               if (selector > 3) start_client_timer.ExecuteNonQuery();

               if (selector % 4 <= 1) {
                  dt = new DataTable();
                  dt.Columns.Add("str", typeof(string));

                  for (int i = 0; i < listlen; i++) {
                     DataRow dr = dt.NewRow();
                     dr["str"] = datastore.Tables[0].Rows[datastore_ix++][0];
                     dt.Rows.Add(dr);
                  }
                  testsp.Parameters["@tbl"].Value = dt;
               }
               else {
                  list = new List<SqlDataRecord>();
                  SqlMetaData metadata = new SqlMetaData("str", SqlDbType.NVarChar, 30);

                  for (int i = 0; i < listlen; i++) {
                     SqlDataRecord rec = new SqlDataRecord(metadata);
                     rec.SetString(0, (string) datastore.Tables[0].Rows[datastore_ix++][0]);
                     list.Add(rec);
                  }
                  testsp.Parameters["@tbl"].Value = list;
               }

               begintrans.ExecuteNonQuery();

               if (selector <= 3) start_client_timer.ExecuteNonQuery();

               testsp.CommandText = procname;
               testsp.Parameters["@tbl"].TypeName = tbltype;
               testsp.ExecuteNonQuery();
               tookms = (int) testsp.Parameters["@tookms"].Value;

               get_clientms.ExecuteNonQuery();
               clientms = (int) get_clientms.Parameters["@clientms"].Value;
               starttime = (SqlDateTime) get_clientms.Parameters["@starttime"].Value;

               commit.ExecuteNonQuery();


               insert_timing.Parameters["@method"].Value = method;
               insert_timing.Parameters["@datatype"].Value = "Str";
               insert_timing.Parameters["@optype"].Value   = "UNPACK";
               insert_timing.Parameters["@listlen"].Value  = listlen;
               insert_timing.Parameters["@testrun"].Value  = testrun;
               insert_timing.Parameters["@tookms"].Value   = tookms;
               insert_timing.Parameters["@clientms"].Value = clientms;
               insert_timing.Parameters["@starttime"].Value = starttime;
               insert_timing.Parameters["@inputsize"].Value   = 0;

               insert_timing.ExecuteNonQuery();

               GC.Collect();
           }
        }
     }
   }
}