将多个表格转换为数据集

Question

我的问题看起来很简单，但对我来说变得很繁琐。在我的项目中，我有一个DBML文件，它作为数据访问层。还有一个实用方法，将现有的结果转换为数据集。以下是我的方法：

 #region To convert from LINQ to dataset
        /// <summary>
        /// Function convert linq to Dataset
        /// </summary>
        public static DataSet LINQToDataTable<T>(IEnumerable<T> varlist)
        {
            DataSet ds = new DataSet();
            //Creating an object of datatable
            DataTable dtReturn = new DataTable();

            dtReturn.Rows.Clear();

            // column names 
            PropertyInfo[] oProps = null;

            if (varlist == null) return ds;

            foreach (T rec in varlist)
            {
                // Use reflection to get property names, to create table, Only first time, others will follow 
                if (oProps == null)
                {
                    oProps = ((Type)rec.GetType()).GetProperties();
                    foreach (PropertyInfo pi in oProps)
                    {
                        Type colType = pi.PropertyType;

                        if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()
                        == typeof(Nullable<>)))
                        {
                            colType = colType.GetGenericArguments()[0];
                        }

                        dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
                    }
                }

                DataRow dr = dtReturn.NewRow();

                foreach (PropertyInfo pi in oProps)
                {
                    dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue
                    (rec, null);
                }

                dtReturn.Rows.Add(dr);
            }
            ds.Tables.Add(dtReturn);
            return ds;
        }
        #endregion

现在我的存储过程有三个不同的Select查询，从中返回三个表格。当我在代码后台返回结果并快速查看结果时，只给出了其中一个表格。

可能的解决方案是什么？请帮帮我。我认为以上LOC可能会有一些问题。

Sql查询已更新

ALTER PROCEDURE [dbo].[usp_GetTenantPropertyDetailsForAgreement] 
    @TenantId INT,
    @PropertyDetailsId INT,
    @BillingPlanId INT
)
AS
BEGIN

    -- Select Tenant's Name and his/her household member info from the two tables
    SELECT      (tbl_MSTTenantPersonalInfo.FirstName + ' ' + ISNULL(tbl_MSTTenantPersonalInfo.MiddleInitial,'') + ' ' + tbl_MSTTenantPersonalInfo.LastName) AS Tenant, 
                (ISNULL(tbl_TenantFamilyMemberInfo.FirstName,'') + ' ' + ISNULL(tbl_TenantFamilyMemberInfo.MiddleName,'') + ' ' + ISNULL(tbl_TenantFamilyMemberInfo.LastName,'')) AS FMName 
    FROM        tbl_MSTTenantPersonalInfo LEFT JOIN
                tbl_TenantFamilyMemberInfo 
    ON          tbl_MSTTenantPersonalInfo.TenantPersonalInfoID = tbl_TenantFamilyMemberInfo.TenantPersonalInfoID
    WHERE       tbl_MSTTenantPersonalInfo.TenantPersonalInfoID = @TenantId AND (tbl_MSTTenantPersonalInfo.IsDelete = 0)



    SELECT      tbl_PropertyConstructionInfo.BedRoomsNo, 
                (ISNULL(tbl_MSTPropertyDetails.Area,'')+''+ ISNULL(tbl_MSTPropertyDetails.Project,'')+' '+ ISNULL(tbl_MSTPropertyDetails.Unit,'')) AS Project, 
                 (tbl_MSTPropertyDetails.Address+' <br/>'+ ISNULL(tbl_MSTPropertyDetails.Address2,'')+' <br/>'+tbl_MSTPropertyDetails.City+' '+tbl_MSTPropertyDetails.State 
                  +'  '+ tbl_MSTPropertyDetails.Zip ) AS PropertyAdress
    FROM         tbl_MSTPropertyDetails INNER JOIN
                      tbl_PropertyConstructionInfo ON tbl_MSTPropertyDetails.PropertyDetailsID = tbl_PropertyConstructionInfo.PropertyDetailsID
    WHERE       tbl_MSTPropertyDetails.PropertyDetailsID = @PropertyDetailsId AND tbl_MSTPropertyDetails.IsDelete = 0



    SELECT      BiilingDayOfMonth, LateFeeAmount, LateFeeAppliedDayofMonth
    FROM        tbl_MSTBillingPlan
    WHERE       BillingplanID = @BillingPlanId

END

调用存储过程的方法

/// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public DataSet GetTenantPropertyDetailsForAgreement(int tenantId, int propertyId,int billingPlanId)
        {
            DataSet ds = new DataSet();
            try
            {
                using (objDataManagerDataContext = new DataManagerDataContext())
                {
                    ds = Utility.LINQToDataTable(objDataManagerDataContext.usp_GetTenantPropertyDetailsForAgreement(tenantId,propertyId,billingPlanId));
                }
            }
            catch (Exception ex)
            {
                SaveLogger.WriteError(ex.ToString());
            }
            return ds;
        }

DBML FILE method

[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.usp_GetTenantPropertyDetailsForAgreement")]
            public IEnumerable<usp_GetTenantPropertyDetailsForAgreementResult> usp_GetTenantPropertyDetailsForAgreement([global::System.Data.Linq.Mapping.ParameterAttribute(Name = "TenantId", DbType = "Int")] System.Nullable<int> tenantId, [global::System.Data.Linq.Mapping.ParameterAttribute(Name = "PropertyDetailsId", DbType = "Int")] System.Nullable<int> propertyDetailsId, [global::System.Data.Linq.Mapping.ParameterAttribute(Name = "BillingPlanId", DbType = "Int")] System.Nullable<int> billingPlanId)
            {
                IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), tenantId, propertyDetailsId, billingPlanId);
                return ((IEnumerable<usp_GetTenantPropertyDetailsForAgreementResult>)(result.ReturnValue));
            }

- user240141

---

你的存储过程在哪里？请发布你调用SP的代码。 - Muhammad Akhtar

Answer 1

如果可以的话，尽量不要使用LINQ。只用普通的ADO.NET就好了，这样你就不需要将LINQ转换为数据集。

- beater

---

如果我能做到，显然我会这样做。但正如我所说，我正在使用DBML文件，因此对于单个方法，我无法使用常规的ado.net方式... - user240141