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

Why is the parameter named @TableVariable in the proc and @CurrentTableInitial in the c# code? — Conrad Frix
– Conrad Frix, Commented May 2, 2012 at 6:08

Romil Kumar Jain · Accepted Answer · 2012-05-02 11:28:04Z

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

You missed the statement a.TypeName = "dbo.TableTypeInitial";
Correct cmd.CommandText statement and type TableTypeInitial instead of CurrentTableInitial.

Collectives™ on Stack Overflow

Passing datatable from C# to SQL Server 2008

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related