从ASP.NET Web服务返回多表值 [英] return multi-table value from ASP.NET webservice

问题描述

大家好，
这个问题与我为最终学校项目使用DAL-BLL体系结构创建的ASP.NET Web服务有关.

这里是一些更多信息:
我在DAL中使用SQL数据集(.xsd).因此，我有一个名为"Insurance"的数据表，其中有一个tableAdapter.适配器中的查询之一引用了具有内部联接的存储过程.所以我的SP看起来像:

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

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)

因此，此SP返回一个数据表，该数据表将是内部联接中2个表的组合.

现在在我的BLL中，我有:

So this SP returns a datatable which will be a combination of the 2 tables in the inner join.

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);
        }

其中，insAdapter是insuranceTableAdapter的实例

这会在执行时产生错误.

我可以成功执行SP，所以我认为问题仅是因为我试图从BLL返回错误的数据表.非常感谢帮助.

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 because i am trying to return a wrong datatable from the BLL. Help greatly appreciated.

推荐答案

找到了解决方案:)
终于成功了.

我使用数据集设计器创建了一个新的表适配器，并将SP称为其中的查询之一.这样创建的数据表包含所有字段(来自Insurance和InsuranceCompany).现在，ASP.NET可以检测到返回类型是新创建的数据表.
就像魅力一样.

如果有更好的方法可以解决此问题，请发表评论.

谢谢大家的时间.
感谢Sandeep使我的问题更具可读性.

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.
Thanks Sandeep for making my question more readable.

这篇关于从ASP.NET Web服务返回多表值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！