c# - 将多个表转换为数据集

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 文件方法

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

Answer 1

如果可以，请尝试不使用 linq。只是普通的 ado.net。这样您就不需要进行从 linq 到数据集的转换。