getting multiple sets of data in one request?

Question

I am working on a site in which as user logs in (first database request) the stored procedure varify password and user id and then returns user record that I put in session to use next.

After this I do a second db request. it returns addresses of user which I put in cache.

Can you pleas guide me is there some way that I can get both sets of data (user record and his address from 2nd table) in one database requests.

plz guide me on this, I am using DAAB (enter prise library) for data access.

Thanks

Answer 1

Modify your SP which has multiple select statements, as in you case is 2. Two select statements in one SP will return two record sets. Verify in SQL Management Studio, when you run your SP, it should show you multiple Grid in bottom panel.

Once your SP is done, call SP from C# code and load result in DataSet. Dataset will have two table, and you can get the data from different table

Answer 2

You can write two select queries in a stored procedure or Execute two queries one after another . In single query you can execute and receive the data in DataSet .

ExecuteDataset()

So two tables will be returned inside the dataset . You can get the values like

dataset.tables(0) dataset.tables(1)

Thanks

Answer 3

You would gain nothing from retrieving two results sets in one go, But the code will become more incoherent. Why do you thing you need to merge two logically separate operations into one? Instead of using such questionable methods you can use join to get one result set that contains all the data in one go, but still that seems wrong. I can not see a clean way of doing what you are asking for and any benefits that might be gained.