Using XML as a SQL Parameter to load multiple objects

Question

Hey everyone,

I'm a newbie at this, so bear with me for a second.

I have a dictionary like so:

var dict = new Dictionary<int, SalesStatistics>();

SalesStatistics is a custom class; that contains various information about products. The key field holds the ProductId.

I need to iterate across this dictionary, and for each ProductId in the dictionary; I need to load a Product class. Since this dictionary contains about 20,000 records; I don't want to ding my database every time I need a Product object. Ideally; I'd like to pass in the XML list of Id's, load all of them into a ProductsCollection, and then pull the Products object from my loaded collection. This should allow me to hit the database once.

Anybody have some tips/tricks to get me going in the right direction?

Answer 1

You could pass the Id's as a comma separated varchar and break them up using fn_Split()

Or use SQL XML to break it put into a table and Join it - I don't have a better example at home, sorry.