can i pass whole table as parameter in procedure to insert o

Question

suppose we have ten rows in the gridview, every rows have some input fields like textbox, dropdown. after filling out all rows i want to insert or update on submit button click.

Tapan Patel · Answer

okay. I think you have different question title than the requirement. You want to pass all rows together upon submitting the request. You have multiple options here . Assuming that it is bound to some class,create a list of objects that have these values from each rows and pass that list to save it back or insert new. You can use observable collection to hold or detect the changes in UI and upon submit, directly pass observable collection to the code handling database activities. To understand the exact requirement, it would be great if you can show some code too or categorized question like whether it is win forms or WPF windows. Let me know if you still have a question.

Ramesh Palaniappan · Answer

varparameter= new SqlParameter( "@allValue" ,SqlDbType.Structured); parameter.Value=allValue; parameter.TypeName= "dbo.AllValue" ;

Sharad Gupta · Answer

Hi Krishna Rana , You can use table value parameter in sql and at the c# end you can pass DataTable in this parameter. you can see this link http://www.aspsnippets.com/Articles/Pass-Table-Valued-Parameters-to-Stored-Procedure-from-Code-Behind-using-ADONet-C-and-VBNet.aspx

Vishal Jadav · Answer

Hi, Yes it is possible. SQL server has a tableValueType as UserDefinedTableTypes under Programmability->Types From where you can create a type which looks similar to table. I have an example let me show you. CREATETYPE[dbo].[WorkingTimeType]ASTABLE( [WorkingHRId][bigint]NOTNULL, [WeekDay][ int ]NOTNULL, [FromHour][time](7)NOTNULL, [ToHour][time](7)NOTNULL ) GO Above is used to create a userdefined table type. CREATEPROCEDURE[dbo].[MANAGE_WORKINGHOURS] @USERIDBIGINT,@WTYPEWORKINGTIMETYPEREADONLY,@OUTVALINTOUTPUT AS BEGIN SETNOCOUNTON SET@OUTVAL=0 DECLARE@IDINT,@WEEKDAYINT,@FROMTIME(7),@TOTIME(7) SELECTTOP1@ID=WORKINGHRID,@WEEKDAY=[WEEKDAY],@FROM=FROMHOUR,@TO=TOHOURFROM@WTYPEORDERBYWORKINGHRIDASC WHILE(@ID>0) BEGIN IF(@WEEKDAY =1) BEGIN IFEXISTS(SELECTTOP1WORKINGHRIDFROMWorkingHoursWHEREUSERID=@USERIDAND[WEEKDAY]=@WEEKDAY) BEGIN UPDATEWorkingHoursSETFROMHOUR=@FROM,TOHOUR=@TOWHEREUSERID=@USERIDAND[WEEKDAY]=@WEEKDAY END ELSE BEGIN INSERTINTOWORKINGHOURS(USERID,[WEEKDAY],FROMHOUR,TOHOUR)VALUES(@USERID,@WEEKDAY,@FROM,@TO) END IFEXISTS(SELECTTOP1WORKINGHRIDFROM@WTYPEWHEREWORKINGHRID>@IDORDERBYWORKINGHRIDASC) BEGIN SELECTTOP1@ID=WORKINGHRID,@WEEKDAY=[WEEKDAY],@FROM=FROMHOUR,@TO=TOHOURFROM@WTYPEWHEREWORKINGHRID>@IDORDERBYWORKINGHRIDASC END ELSE BEGIN SET@ID=0 END SET@OUTVAL=1 END ELSE BEGIN SET@ID=0 SET@OUTVAL=0 END END RETURN@OUTVAL END Above is calling stored procedure where you can see a variable of type @WTYPE WORKINGTIMETYPE READONLY which means it will accept table as input. Now from the C# code you have to just pass the DataTable as variable as below. SqlParameter[]p= new SqlParameter[2]; p[0]= new SqlParameter( "userid" ,hour.UserID); p[1]= new SqlParameter( "wtype" ,System.Data.SqlDbType.Structured); p[1].Value=hour.WorkingHourType; Here hour.WorkingHourType is datatable containing same schema as our userdefined table type. If you have any doubt please let me know. Hope it helps. Thanks. Cheers...

Midhun Tp · Answer

Hi, Yes you can pass a complete table as parameter to stored procedure. Please check below link for details - http://www.aspsnippets.com/Articles/Send-Pass-DataTable-as-parameter-to-Stored-Procedure-in-C-and-VBNet.aspx

can i pass whole table as parameter in procedure to insert o

Answers (5)