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 classcreate 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=@FROMTOHOUR=@TOWHEREUSERID=@USERIDAND[WEEKDAY]=@WEEKDAY END ELSE BEGIN INSERTINTOWORKINGHOURS(USERID[WEEKDAY]FROMHOURTOHOUR)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 T P · 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