\nI've been trying out table-valued parameters along with ADO.NET support in Orcas and came across an interesting dilemma. It centers around INSERTs using TVP against a table with an identity column. ADO.NET can use DataTable, DbDataReader or IList<SqlDataRecord> to represent a TVP parameter. Let's say that I want a TVP and a procedure for insert that looks like this:\n<\/p>\n
\nCREATE TYPE dbo.JobsTableWithIdentity AS TABLE (
\n job_id smallint identity primary key,
\n job_desc varchar(50),
\n min_lvl tinyint,
\n max_lvl tinyint
\n);\n<\/p>\n
\nThe "insert proc" would look like this:\n<\/p>\n
\nCREATE PROCEDURE dbo.InsertJobsID (@tvp1 dbo.JobsTableWithIdentity readonly)
\nas
\nINSERT INTO dbo.Jobs (job_desc, min_lvl, max_lvl)
\n SELECT job_desc, min_lvl, max_lvl from @tvp1;\n<\/p>\n
\nUsing this in ADO.NET (with either DataTable or DbDataReader as a parameter) produces the error: "INSERT into an identity column not allowed on table variables. The data for table-valued parameter "@tvp1" doesn't conform to the table type of the parameter." But I didn't DO an insert into an identity column in the proc. And this works just fine in T-SQL:\n<\/p>\n
\nDECLARE @t dbo.JobsTableWithIdentity;
\nINSERT @t VALUES('hi', 10, 10);
\nINSERT @t VALUES('hi2', 10, 10);
\nEXEC InsertJobsID @t;\n<\/p>\n
\nSo its ADO.NET "deciding" this is an error. The ADO.NET workaround (if I do want to start with a DataTable that contains the identity column, add rows to it, and call Update) is this:\n<\/p>\n
\nCREATE TYPE dbo.JobsTableWithoutIdentity AS TABLE (
\n — job_id smallint identity primary key,
\n job_desc varchar(50),
\n min_lvl tinyint,
\n max_lvl tinyint
\n);\n<\/p>\n
\n— sproc dbo.InsertJobsNoID changed accordingly\n<\/p>\n
\n\/\/ and then, in ADO.NET code
\n\/\/ DataTable "t" contains a real jobs table, to which I've added rows\n<\/p>\n
\nDataTable added = t.GetChanges(DataRowState.Added);
\nadded.Columns.Remove("job_id");
\nda.InsertCommand.CommandText = "dbo.InsertJobsNoID";
\nda.InsertCommand.CommandType = CommandType.StoredProcedure;
\nda.InsertCommand.Parameters.AddWithValue("@tvp1", added);\n<\/p>\n
\nBut, should I have to do this? Or modify the T-SQL code, given I've not used the IDENTITY column? But, I will need this column to UPDATE (or MERGE) using the TVP. A dilemma…<\/p>\n","protected":false},"excerpt":{"rendered":"
I've been trying out table-valued parameters along with ADO.NET support in Orcas and came across an interesting dilemma. It centers around INSERTs using TVP against a table with an identity column. ADO.NET can use DataTable, DbDataReader or IList<SqlDataRecord> to represent a TVP parameter. Let's say that I want a TVP and a procedure for insert that […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12,29,40],"tags":[],"class_list":["post-780","post","type-post","status-publish","format-standard","hentry","category-data-access","category-sql-server-2008","category-transact-sql"],"yoast_head":"\n