How to execute Table SP in csharp

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

Could the error be coming from the parseInputFile table instead? NumCols is defined as an identify field, but in the C# code you are setting the value of the record to 1.

Answer 2

Okay, I haven't actually worked with TVPs yet, so I could be wrong--but from my reading, & general SS background, I think your problem is this:

Your permanent table has an Identity column, so SS wants to handle that for you. You can set Identity Insert on, but that's not a good idea....

One thing to try is to specify the column to insert in your SP:

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

The other thing to look at, as Tim Lentine suggested (while I was writing this answer), is whether or not you're actually getting to the server. Since you've defined the NumCols column in your DataTable as AutoIncrement, it could be that you're getting your exception in the C# code, at:

...
dr["NumCols"] = 1;
...

< edit>

Don't know what I was thnking when I clicked Submit...

If that's the case, try leaving out the AutoIncrement when you creat your DataTable. If the column data types are consistent, this property shouldn't matter when lining up the TVP as being "the same" as the SS Type.

< /edit>

Answer 3

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