How to execute Table SP in C#

Question

I looked at the SQLTeam website but now am having a new problem cause I have an IDENTITY column and their example does not.

I have SQL Server 2008 and VS 2008. I am trying to execute the InsertPIF SP using C# and a table UDT, but am getting an exception. I have looked at a SQLTeam website example with a Table UDT, but their example doesn't have an identity column like mine does.

However, their example table doesn't have an identity column like mine. What I am trying to do should be simple. I have tried many different variations of this, but essentially:

ALTER PROCEDURE [dbo].[InsertPIF] @InputFileParam parseInputFile READONLY
AS
INSERT dbo.ParentTable SELECT strRow FROM @InputFileParam
GO

where

CREATE TABLE [dbo].[ParentTable](
    [ParentID] [int] IDENTITY(1,1) NOT NULL,
    [strInput] [varchar](8000) NULL,
PRIMARY KEY NONCLUSTERED 
(...)

and type:

CREATE TYPE [dbo].[parseInputFile] AS TABLE(
    [NumCols] [int] IDENTITY(1,1) NOT NULL,
    [strRow] [varchar](500) NOT NULL,
    PRIMARY KEY CLUSTERED 
(...)

What I am trying to do is to execute the above SP to insert new records into the ParentTable. Example:

INSERT INTO ParentTable(strInput) VALUES ('A3|BB|C|DDD')
INSERT INTO ParentTable(strInput) VALUES ('A4|GOB|BLDY|GOOK')
INSERT INTO ParentTable(strInput) VALUES ('A5|Hello|My|Darling')

Here is more of my C# code prior to the AppendData section:

DataTable dt = new DataTable();
String[] header = myStringArray[0].Split('|');

DataRow dr = dt.NewRow();
DataColumn dc = new DataColumn();
dc.DataType = typeof(Int32);
dc.ColumnName = “NumCols”;
dc.AutoIncrement=true;
dt.Columns.Add(dc);

DataColumn dc2 = new DataColumn();
dc2.DataType = typeof(string);
dc2.ColumnName = “strRow”;
dt.Columns.Add(dc2);
dr["NumCols"] = 1;
dr["strRow"] = "AA|BBB|CC|DD|E";
dt.Rows.Add(dr);

...
                SqlConnection conn = new SqlConnection(connString);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "dbo.InsertPIF";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = conn;
                SqlParameter param = cmd.Parameters.AddWithValue("@InputFileParam", SqlDbType.Structured);

        param.Value = dt;
        conn.Open();
        RowsAffected = cmd.ExecuteNonQuery();

I set identity to be turned on for the ParentTable, but it seems like I have to execute this each session, so I'm not sure how to remove identity requirement or else to make identity remain on. Exception:

INSERT into an identity column not allowed on table variables. The data for table-valued parameter '@InputFileParam' doesn't conform to the table type of the parameter.

How do I execute this SP?

Answer 1

I think Roland is on the right track - the IDENTITY columns definitely are the problem. Since your ultimate target table already has an IDENTITY column which will be filled automagically for you, I would recommend not specifying the same column in your user-defined table type (and also do not declare a primary key on the table type):

CREATE TYPE [dbo].[parseInputFile] AS TABLE
    ([strRow] [varchar](500) NOT NULL)

Then in your C# code, just set up a DataTable with a single column:

DataTable dt = new DataTable();

DataColumn dc = new DataColumn();
dc.DataType = typeof(string);
dc.ColumnName = “strRow”;
dt.Columns.Add(dc);

and then add your columns to your DataTable "dt" - with just the one string "strRow".

In your stored proc that then gets called with your user-defined table type, just simply insert your strings:

ALTER PROCEDURE [dbo].[InsertPIF] @InputFileParam parseInputFile READONLY
AS
  INSERT INTO dbo.ParentTable(strInput)
    SELECT strRow FROM @InputFileParam
GO

That should do it, I hope! (haven't had the time to test this myself)

Marc