Menu
I need to make two queries on two different tables and the data isn't really related. So when I call the stored proc through my code, I should be getting a DataSet with two DataTables, one DataTable for each query. How is that done in SQL Server stored procs?
829
All the applications that can access Relational databases (Java, Python, PHP etc.), can access stored procedures. Stored procedures contain IN and OUT parameters or both. They may return result sets in case you use SELECT statements. Stored procedures can return multiple result sets.
In order to fetch the multiple returned values from the Stored Procedure, you need to make use of a variable with data type and size same as the Output parameter and pass it as Output parameter using OUTPUT keyword. You can also make use of the Split function to split the comma separated (delimited) values into rows.
You absolutely can, although you may want to consider the overall goal and implications of considering this design.
In order to get multiple result sets working we need to drop to the ObjectContext API by using the IObjectContextAdapter interface. Once we have an ObjectContext then we can use the Translate method to translate the results of our stored procedure into entities that can be tracked and used in EF as normal.
Simply execute two SELECT statements in the proc:
SELECT * FROM Foo
SELECT * FROM Bla
when you then Fill() a dataset, you'll get two datatables, one with the first resultset, the other with the second.
170
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
