Passing datatable from C# to SQL Server 2008

Question

How can I pass a DataTable from C# to SQL Server 2008?

Exception:

The table type parameter '@CurrentTableInitial' must have a valid type name.

Table structure:

CREATE TABLE [dbo].[RegisterChoiceUserInitial](
    [RegisterChoiceUserInitialID] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [uniqueidentifier] NOT NULL,
    [RegisterChoiceUserInitialJob] [nvarchar](50) NULL,
    [RegisterChoiceUserInitialOrganization] [nvarchar](50) NULL,
    [RegisterChoiceUserInitialUnit] [nvarchar](50) NULL,
    [RegisterChoiceUserInitialMembershipType] [nvarchar](50) NULL,
    [RegisterChoiceUserInitialStart] [nvarchar](10) NULL,
    [RegisterChoiceUserInitialEnd] [nvarchar](10) NULL,
 CONSTRAINT [PK_RegisterChoiceUserInitial] PRIMARY KEY CLUSTERED 
(
    [RegisterChoiceUserInitialID] ASC
)

User-defined type:

CREATE TYPE [dbo].[TableTypeInitial] AS TABLE(
    [ID] [int] NULL,
    [InitialJob] [nvarchar](50) NULL,
    [InitialOrganization] [nvarchar](50) NULL,
    [InitialUnit] [nvarchar](50) NULL,
    [InitialMembershipType] [nvarchar](50) NULL,
    [InitialMembershipStart] [nvarchar](10) NULL,
    [InitialMembershipEnd] [nvarchar](10) NULL
)

Stored procedure:

create PROCEDURE [dbo].[FinishRegisterChoiceUserInitial]
      ( @UserId                         uniqueidentifier,
       @TableVariable TableTypeInitial READONLY)
 AS
 BEGIN

 INSERT INTO [Election].[dbo].[RegisterChoiceUserInitial]
           ([UserId]
           ,[RegisterChoiceUserInitialJob]
           ,[RegisterChoiceUserInitialOrganization]
           ,[RegisterChoiceUserInitialUnit]
           ,[RegisterChoiceUserInitialMembershipType]
           ,[RegisterChoiceUserInitialStart]
           ,[RegisterChoiceUserInitialEnd])
       SELECT
             @UserId AS UserId
             ,InitialJob
             ,InitialOrganization
             ,InitialUnit
             ,InitialMembershipType
             ,InitialMembershipStart
             ,InitialMembershipEnd
       FROM
             @TableVariable   
 END

DataTable:

DataTableInitial.Columns.Add(new DataColumn("ID", typeof(int)));
DataTableInitial.Columns.Add(new DataColumn("InitialJob", typeof(String)));
DataTableInitial.Columns.Add(new DataColumn("InitialOrganization", typeof(String)));
DataTableInitial.Columns.Add(new DataColumn("InitialUnit", typeof(String)));
DataTableInitial.Columns.Add(new DataColumn("InitialMembershipType", typeof(String)));
DataTableInitial.Columns.Add(new DataColumn("InitialMembershipStart", typeof(String)));
DataTableInitial.Columns.Add(new DataColumn("InitialMembershipEnd", typeof(String)));

cmd.CommandText = "EXEC FinishRegisterChoiceUserInitial @UserId, @CurrentTableInitial ";
cmd.Parameters.AddWithValue("@UserId",user.ProviderUserKey);

DataTable TableInitial=(DataTable)ViewState["CurrentTableInitial"];
SqlParameter a = cmd.Parameters.AddWithValue("@CurrentTableInitial", TableInitial);
a.SqlDbType = SqlDbType.Structured;

cmd.ExecuteNonQuery();

Answer 1

You were missing a.TypeName = "dbo.TableTypeInitial"; Put this statement before "a.SqlDbType = SqlDbType.Structured;"

Use

cmd.CommandText = "EXEC FinishRegisterChoiceUserInitial @UserId, @TableTypeInitial "; 

instead of

cmd.CommandText = "EXEC FinishRegisterChoiceUserInitial @UserId, @CurrentTableInitial "; 

Sql Server Scripts :

         CREATE TABLE [Target]
         (
         [ID] [int] NOT NULL PRIMARY KEY IDENTITY,
         [FirstName] [varchar](100)NOT NULL,
         [LastName] [varchar](100)NOT NULL,
         [Email] [varchar](200) NOT NULL
         )
        CREATE TYPE [TargetUDT] AS TABLE
         (
         [FirstName] [varchar](100)NOT NULL,
         [LastName] [varchar](100)NOT NULL,
         [Email] [varchar](200) NOT NULL
         )
         CREATE PROCEDURE AddToTarget(@TargetUDT TargetUDT READONLY)
         AS
         BEGIN
               INSERT INTO [Target]
               SELECT * FROM @TargetUDT
         END

Sample Code :

public static void StartProcess()
        {
            //Create a local data table to hold customer records
            DataTable dtCustomers = new DataTable("Customers");
            DataColumn dcFirstName = new DataColumn("FirstName", typeof(string));
            DataColumn dcLastName = new DataColumn("LastName", typeof(string));
            DataColumn dcEmail = new DataColumn("Email", typeof(string));
            dtCustomers.Columns.Add(dcFirstName);
            dtCustomers.Columns.Add(dcLastName);
            dtCustomers.Columns.Add(dcEmail);
            //Add customer 1
            DataRow drCustomer = dtCustomers.NewRow();
            drCustomer["FirstName"] = "AAA";
            drCustomer["LastName"] = "XYZ";
            drCustomer["Email"] = "[email protected]";
            dtCustomers.Rows.Add(drCustomer);
            //Add customer 2
            drCustomer = dtCustomers.NewRow();
            drCustomer["FirstName"] = "BBB";
            drCustomer["LastName"] = "XYZ";
            drCustomer["Email"] = "[email protected]";
            dtCustomers.Rows.Add(drCustomer);
            //Add customer 3
            drCustomer = dtCustomers.NewRow();
            drCustomer["FirstName"] = "CCC";
            drCustomer["LastName"] = "XYZ";
            drCustomer["Email"] = "[email protected]";
            dtCustomers.Rows.Add(drCustomer);
            //Create Connection object to connect to server/database
            SqlConnection conn = new SqlConnection(ConStr);
            conn.Open();
            //Create a command object that calls the stored procedure
            SqlCommand cmdCustomer = new SqlCommand("AddToTarget", conn);
            cmdCustomer.CommandType = CommandType.StoredProcedure;
            //Create a parameter using the new SQL DB type viz. Structured to pass as table value parameter
            SqlParameter paramCustomer = cmdCustomer.Parameters.Add("@TargetUDT", SqlDbType.Structured);
            paramCustomer.Value = dtCustomers;
            //Execute the query
            cmdCustomer.ExecuteNonQuery();        
        }