using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

/*============================================================================
  File:     Program.cs

  Summary:  This program illustrates using table-valued parameters in ADO.NET
            3.5 client code.

  Date:     August 2008

  SQL Server Version: 10.0.1600.22 (RTM)
------------------------------------------------------------------------------
  Written by Bob Beauchemin, SQLskills.com

  For more scripts and sample code, check out http://www.SQLskills.com

  This script is intended only as a supplement to SQL Server Jumpstart program.
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/


namespace TVPParmsJobs
{
    class Program
    {
        static void Main(string[] args)
        {
            TVPWithDataTable();
            TVPWithDataReader();
        }

        public static void TVPWithDataTable()
        {
            SqlConnection conn = new SqlConnection("server=.;database=test;integrated security=sspi");

            SqlDataAdapter da = new SqlDataAdapter(
                "select top(0) * from dbo.jobs",  // to get the shape of the table
                "server=.;database=pubs;integrated security=sspi"
                );

            DataTable t = new DataTable();
            da.Fill(t);
            DataRow r = t.NewRow();
            r["job_desc"] = "new job 1";
            r["min_lvl"] = 10;
            r["max_lvl"] = 10;
            t.Rows.Add(r);
            DataRow r2 = t.NewRow();
            r2["job_desc"] = "new job 2";
            r2["min_lvl"] = 20;
            r2["max_lvl"] = 20;
            t.Rows.Add(r2);
            DataTable added = t.GetChanges(DataRowState.Added);

            da.InsertCommand = new SqlCommand();
            da.InsertCommand.Connection = conn;
            da.InsertCommand.CommandText = "dbo.InsertJobs";

            da.InsertCommand.CommandType = CommandType.StoredProcedure;
            da.InsertCommand.Parameters.AddWithValue("@tvp1", added);
            da.InsertCommand.Parameters[0].SqlDbType = SqlDbType.Structured;
            da.InsertCommand.Parameters[0].TypeName = "dbo.JobsTable";

            da.Update(added);

        }

        public static void TVPWithDataReader()
        {
            SqlConnection conn1 = new SqlConnection("server=.;database=pubs;integrated security=sspi");
            SqlConnection conn2 = new SqlConnection("server=.;database=test;integrated security=sspi");

            conn1.Open();
            conn2.Open();

            // Assumes connection is an open SqlConnection.
            // Retrieve data from SQL Server conn1.
            SqlCommand selectCommand = new SqlCommand(
               "Select job_id, job_desc, max_lvl, min_lvl FROM dbo.Jobs WHERE job_id < 4;", conn1);
            SqlDataReader rdr = selectCommand.ExecuteReader(
               CommandBehavior.CloseConnection);

            // Configure SqlCommand and TVP parameter.
            SqlCommand insertCommand = new SqlCommand("dbo.InsertJobs", conn2);
            insertCommand.CommandType = CommandType.StoredProcedure;
            SqlParameter tvpParam =
               insertCommand.Parameters.AddWithValue("@tvp1", rdr);
            tvpParam.SqlDbType = SqlDbType.Structured;
            tvpParam.TypeName = "dbo.JobsTable";

            // Execute the command.
            insertCommand.ExecuteNonQuery();

            conn2.Dispose();
        }
    }
}
