joining an ASP.NET dataset to a database table

I'm getting a list of review items from a web service that dictates what should be output from my database.

The webservice will return a data set with the following columns

[local_item_id] | [remote_item_transaction_id] | [remote_item_type]

And the local REVIEWS database table looks like this

[id] | [title] | [review_description] | [created_date] | [rating]

I need to get the above data into a stored proc and join the table using like this [id] = [local_item_id] so that I get a table like the following to output to an ASP.NET repeater...

[id] | [remote_item_transaction_id] | [remote_item_type] | [title] | [review_description] | [created_date] | [rating]

I assume the most straight forward way of doing this would be somehow passing the dataset to the stored proc and then parsing it into a temp table which I can then join to my REVIEWS table.

Each repeater item would then output a link as below where the transaction ID will be reported back to the reporting server.

<asp:Repeater ID="reviewItemsRepeater" runat="server">
<ItemTemplate>
<div><asp:HyperLink ID="MyHyperLink" runat="server" NavigateUrl='<%#"~/showreview.a开发者_运维知识库spx?reviewid="+Eval("id").ToString()+"&trans_id="+Eval("remote_item_transaction_id").ToString()+"&trans_type="+Eval("remote_item_type").ToString()%>' Text='<%#Eval("title")%>'>

*Note: the transactionID for each remote item is unique to each webservice call so [remote_item_transaction_id] & [remote_item_type] cant be stored in the reviews table locally.*

Any pointers on this would be really helpful. Thanks

If you are using SQL Server 2008 or above, try table-valued parameter:

http://www.codeproject.com/KB/cs/CSharpAndTableValueParams.aspx

You could do a bulk insert into a temp table and then do a normal join on it. If you need to perform multiple queries against it then this might perform the best.

If the number of items in REVIEWS isn't too huge you could query it into your dataset and use relationship in there to filter the data.