How to return multi-table join value from BLL

Question

Hi All, This question is regarding the ASP.NET webservice that i am creating using the DAL-BLL architecture for my final school project.

I have a stored procedure, which is a select query with an inner join for 2 tables. Hence the stored procedure returns multi-table value. One of my DAL tableAdapter methods accesses this stored procedure. How do i retrieve the return value in the BLL? Do i have to create a class structure similar to the one supposed to be returned by the stored proc? or is there a direct way to achieve the same? Help greatly appreciated. Please let me know if someone needs code applet to get a better understanding. Thanks

Here is some more information: I am using the SQL dataset (.xsd) in DAL. So i have a datatable called "Insurance", which has a tableAdapter. One of the queries in the adapter references to a stored procedure, which has an inner join. So my SP looks like:

ALTER PROCEDURE dbo.GetInsurancesPaged
    (
        @startRowIndex int,
        @maximumRows int,
        @patientID int
    )
AS
    select * from
    (
    SELECT Insurance.insuranceID, Insurance.memberID, Insurance.groupID, Insurance.accountType, Insurance.comments, Insurance.patient, Insurance.company, InsuranceCompany.companyID, InsuranceCompany.companyName, InsuranceCompany.address, InsuranceCompany.phone, InsuranceCompany.fax, ROW_NUMBER() over (order by Insurance.dateModified DESC) as ROWRANK
FROM Insurance INNER JOIN InsuranceCompany ON Insurance.company = InsuranceCompany.companyID
WHERE Insurance.patient = @patientID
    )
    AS DataWithRowNumbers
WHERE ROWRANK > @startRowIndex AND ROWRANK <= (@startRowIndex + @maximumRows)

So this SP returns a datatable which will be a combination of the 2 tables in the inner join. Please correct me if i am wrong.

Now in my BLL, i have:

[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
    public mySys.InsuranceDataTable GetInsurancesPaged(int startRowIndex, int maximumRows, int patientID)
    {
       return insAdapter.GetInsurancesPaged(startRowIndex, maximumRows, patientID);
    }

where insAdapter is an instance of insuranceTableAdapter

This gives an error on execution. I can execute the SP successfully, so i think the problem is only bcz i am trying to return a wrong datatable from the BLL. Please help me solve this.

Answer 1

If using ADO .Net dataset. The wizard will definetly create a table for the same. now from the dataaccess layer, do the following steps

1. Create a object of dataset. (DLL)

Private YourCustomeDataSetDatatable  DataAccess()
{
 YourCustomDataSet ds = new YourCustomDataSet();  // also called strongly typed dataset
YourCustomeDataSetDatatable dt = ds.YourCustomeDataSetDatatable ()
YourCustomeDataSetTableAdapter ta = new ds.YourCustomeDataSetTableAdapter (); // table adapter that will be invoked 
ta.Fill(dt); // or if you have set to return only you can also use GetData()
}

2. Now in business layer

Private YourCustomeDataSetDatatable  DataAccess()
{
// create a object of DLL. 
MyDAL myDal = new MyDAL ();
return myDal.DataAccess();
}

1. Catch this on your UI page by following the creating object of BLL and call the method. Here in BLL you can also do various operations to lowered the codes in you ui and keeping it clean from various manipulations.

Answer 2

Found a solution :) Finally got it working.

I created a new table adapter using the Dataset designer, and called the SP as one of the queries there. The datatable thus created, has all the fields (from Insurance and InsuranceCompany) included. Now, ASP.NET can detect that the return type is the newly created datatable. Works like a charm.

If there is a better way to solve this, please comment.

Thank you all for your time.