0

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

haansi
  • 5,470
  • 21
  • 63
  • 91

3 Answers3

1

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

Thanigainathan
  • 1,505
  • 14
  • 25
  • thanks for guiding and sharing. I am using custom objects and not dataset or data tables. Can u plz help a little more. – haansi Jan 06 '11 at 10:22
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

hungryMind
  • 6,931
  • 4
  • 29
  • 45
  • thanks for guiding me. I am not using data table or dataset. I am using custom objects. Can it be done using custom objects ? plz advice. – haansi Jan 06 '11 at 10:21
  • 1
    SP does not return custom objects, you load the result from DB in custom objects. DAAB with return either single value or DataReader or DataSet. Once you have the dataset, you can load the data from dataset to your custom object – hungryMind Jan 06 '11 at 10:29
  • Is there any way I can do it with DataReader ? – haansi Jan 06 '11 at 10:33
  • Sure, You should be able to use dataReader.NextResult() to get to the next resultset. – hungryMind Jan 06 '11 at 11:08
0

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.

jimjim
  • 2,414
  • 2
  • 26
  • 46