Convert Multiple tables to Dataset

Question

My question seems quite simple but became tedious for me. In my project, i have a DBML file which is acting as a Data Access Layer. There one more utility method that is converting the existing result into dataset. Below is my method:

 #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

Now one of my stored procedure has three different Select queries running thus returning three tables from there. When I return the result on the code behind and quick watch the result, its giving me the only one table out of the three.

What will be the possible solution. Please help me out. I think there might be some issue with the above LOC.

Sql Query Updated

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

Method that call stored procedure

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

Answer 1

try it without linq if you can. Just regular ado.net. that way you don't need to do the conversion from linq to dataset.