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

Krishna Rana

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.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tapan Patel10y ago
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 Palaniappan10y ago
1. varparameter=newSqlParameter("@allValue",SqlDbType.Structured);
2. parameter.Value=allValue;
3. parameter.TypeName="dbo.AllValue";

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

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.

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<=7AND@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.

Herehour.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...

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