Update table using table datatype

Question

Hello I am facing some problem in passing data table parameter to stored procedure. There are two table tblQuestion and tblAnswerChoice 1. tblQuestion is for storing questions and its columns are a)Qid int primary key identity b)Question ntext c)Createiondate datetime 2. tblAnswerChoice is for storing Answer Choices for the particular question and its columns are a)Ansid int primary key identity b)qid int c)AnswerChoice ntext Now about the problem I want to update the data of the tblAnswerChoice I am passing the datatable as parameter to the datatabse. I need the stored procedure for updating the datatbase. Is is possible using table datatype or not. If yes then how to write the stored procedure for update the tblAnswerChoice . If table stored procedure is not use in this case how can I update the tblAnswerChoice Thanks Deepak Pandey

Javeed M Shaikh · Answer

Hi Deepak Please check if the following code is what helps in your task: SQL Code below: CREATE TYPE TYPANSWERCHOICE AS TABLE( ANSID INT QID INT ANSWERCHOICE NEXT ) GO CREATE PROCEDURE INSERTANSWERCHOICE( @ANSWERCH AS TYPANSWERCHOICE READONLY) AS BEGIN INSERT INTO [tblAnswerChoice] SELECT * FROM @ANSWERCH END GO C# code below: using (var conn = new SqlConnection(CONNECTIONSTRING)) { conn.Open(); using (var cmd = new SqlCommand(INSERTANSWERCHOICE conn)) { cmd.CommandType = CommandType.StoredProcedure; var answerParam = cmd.Parameters.AddWithValue(@ANSWERCH ANSWERDATATABLE); answerParam.SqlDbType = SqlDbType.Structured; cmd.ExecuteNonQuery(); } conn.Close(); Please do not forget to mark Accepted Answer.

Suthish Nair · Answer

Deepak thread resolved?

Prabhu Raja · Answer

Hi I have no experience with passing datatable to stored Procedure.You may try this. CREATE PROCEDURE InsertAnswer @Question varchar(250) @CreationDate SmallDateTime @AnswerChoice varchar(250) AS INSERT INTO tblQuestion(Question CreateDate) VALUES(@ Question  @CreationDate ) DECLARE @Identity int SET @Identity = SCOPE_IDENTITY() INSERT INTO tblAnswerChoice(qid AnswerChoice) VALUES(@ Identity  @ AnswerChoice ) GO