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

class Manyparamtest {

   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=.\\IKI;Initial Catalog=listtest";

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

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

      // 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 convert(int, Number) FROM fn_nums(6000000) 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["@clientms"].Direction = ParameterDirection.InputOutput;
      get_clientms.Parameters["@clientms"].Value = 0;

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

      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);

      int datastore_ix = 0;

      foreach (int listlen in listlens) {
         for (int selector = 0; selector < 2; selector++) {
            for (int testrun = 0; testrun <= 10; testrun++) {
               int clientms,
                   tookms;
               string method = "MANYPARAM" + (selector == 1 ? "$A" : "$B");

               testsp.Parameters.Clear();
               testsp.Parameters.Add("@str", SqlDbType.NVarChar);
               testsp.Parameters["@str"].Direction = ParameterDirection.Input;
               testsp.Parameters["@str"].Value = "Dummy";
               testsp.Parameters.Add("@tookms", SqlDbType.Int);
               testsp.Parameters["@tookms"].Value = 0;
               testsp.Parameters["@tookms"].Direction = ParameterDirection.InputOutput;

               if (selector == 1) start_client_timer.ExecuteNonQuery();

               SqlParameter[] parameters = new SqlParameter[listlen];

               for (int i = 0; i < listlen; i++) {
                  parameters[i] = new SqlParameter("@p" + (i + 1).ToString(),
                                                   SqlDbType.Int);
                  parameters[i].Value = datastore.Tables[0].Rows[datastore_ix++][0];
               }

               testsp.Parameters.AddRange(parameters);

               begintrans.ExecuteNonQuery();

               if (selector == 0) start_client_timer.ExecuteNonQuery();

               testsp.ExecuteNonQuery();
               tookms = (int) testsp.Parameters["@tookms"].Value;

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

               commit.ExecuteNonQuery();


               insert_timing.Parameters["@method"].Value = method;
               insert_timing.Parameters["@datatype"].Value = "Int";
               insert_timing.Parameters["@optype"].Value   = "COUNT";
               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["@inputsize"].Value   = 0;

               insert_timing.ExecuteNonQuery();

               GC.Collect();
           }
        }
     }
   }
}