0

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.

Improve this question
2
  • To be clear... by "datatable1", do you mean a DataTable object in ADO .NET? And is "datatable2" a result set ("SELECT * FROM table")? Commented Feb 17, 2009 at 13:41
  • "datatable1" is a DataTable object in ADO.Net as you rightly said. But "datatable2" is a table residing in database. Commented Feb 18, 2009 at 10:36

3 Answers 3

Reset to default
3

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/

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Hey Sergio actually second data table is very huge (~ 50,000 records) and bringing it to data set will eat away lot of time. Performance will be the cost.
In that case, I would try one of the other 2 answers.
0

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.

Improve this answer

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.