How do I send a datatable as a parameter to a stored procedure in database

Question

Basically I want to do a innerjoin on "datatable1" which is on the webserver cache to that of another "datatable2" stored in database.

I have 2 ways for this 1. I can bring "datatable2" to webserver cache and write a join logic. However this will be very costly and is ruled out. 2. Send "datatable1" to database and do a inner join there and get the result back to web server.

So I need to send this datatable1 as a parameter to stored procedure. I want to avoid looping through this table as this table can be very big.

Answer 1

I think your best option would be to query the second data table into the same dataset of your original datatable and then join then within the dataset.

Check the accepted answer at:

http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/f122d7f4-3b7d-4d93-bd0f-8bb57cd990a4/

Answer 2

One method might be to serialise your datatable to xml and use an xml parameter. It's very easy to join on an xml document as if it were a table.

Answer 3

If you're using SQL Server 2005+, I would use OPENXML. You pass your data table to the procedure using a text or xml parameter (use DataTable.WriteXml), have the procedure parse your document, then you can use it as a result set to join the other table to.