C# Microsoft Enterprise Library 4.1 - return DataTable

jamesdean666
jamesdean666 used Ask the Experts™
on
I am using the Code below and am new to the Enterprise Library.. I can't figure out how to return the datatable with my 'return' statement?  I am used to using the .Add or .Fill technique in my code.

Thanks..
public static BCT.RapidChange.WebApp.Data.PolicyMortgageeDs.MORTGEEDataTable GetMortgeeByPolicy2(String policyNumber, Int32 portfolioSet)
        {
            BCT.RapidChange.WebApp.Data.PolicyMortgageeDs.MORTGEEDataTable dt = new BCT.RapidChange.WebApp.Data.PolicyMortgageeDs.MORTGEEDataTable();
 
            try
            {
                Database db = DatabaseFactory.CreateDatabase("HMWCPYConnectionString");
                StringBuilder sbQry = new StringBuilder();
 
                sbQry.Append("SELECT REGISTER.POLICY_NUMBER, MORTGEE.DATE_TIME, MORTGEE.MORTGEE_NUMBER, MORTGEE.MORTGEE_NAME, MORTGEE.MORTGEE_ADDR_1, ");
                sbQry.Append("MORTGEE.MORTGEE_ADDR_2, MORTGEE.MORTGEE_ADDR_3, MORTGEE.MORTGEE_ZIP_CODE, MORTGEE.MORTGEE_TYPE, MORTGEE.MORTGEE_CITY, MORTGEE.MORTGEE_STATE, ");
                sbQry.Append("MORTGEE.COUNTRY, MORTGEE.ADDRESS_TYPE, MORTGEE.TAX_ID,E.DESCRIPTION AS LOAN_NUMBER, E.SEQUENCE_NUMBER ");
                sbQry.Append("FROM MORTGEE , REGISTER , ENDORSEMENT E ");
                sbQry.Append("WHERE MORTGEE.MORTGEE_NUMBER = E.NAME_AND_ADDRESS ");
                sbQry.Append("AND REGISTER.POLICY_NUMBER =  E.POLICY_NUMBER ");
                sbQry.Append("AND REGISTER.POLICY_DATE_TIME =  E.POLICY_DATE_TIME ");
                sbQry.Append("AND REGISTER.STATUS_3='A' ");
                sbQry.Append("AND REGISTER.PORTFOLIO_SET = @PORTFOLIO_SET ");
                sbQry.Append("AND REGISTER.POLICY_NUMBER=@POLICY_NUMBER ");
                sbQry.Append("AND E.TERMINATION_DATE IS NULL ");
                sbQry.Append("AND E.IDENTIFIER IN ('01w', '03Y', '03i') ORDER BY E.SEQUENCE_NUMBER ");
 
                string sql = sbQry.ToString();
                DbCommand cmd = db.GetSqlStringCommand(sql);
 
                db.AddInParameter(cmd, "@POLICY_NUMBER", DbType.String, policyNumber);
                db.AddInParameter(cmd, "@PORTFOLIO_SET", DbType.Int32, portfolioSet);
 
                using (IDataReader polMortgeeReader = db.ExecuteReader(cmd))
                {
                    while (polMortgeeReader.Read())
                    {
                        PolicyMortgagee policyMort = new PolicyMortgagee();
                        policyMort.Load(polMortgeeReader);
                        //mortByPolList.Add(policyMort);
                        //dt.fil                                             
                    }
                    return dt;
                }
 
 
                return dt;
 
            }
            catch (Exception ex)
            {
                log.ErrorException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
                throw;
            }
        }

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Why don't you have the return statement after the catch block - in a finally block? In case of an exception - you will have a null dt - which you can check for in the caller.
Am I missing something else in your problem?
Thanks
        catch (Exception ex)
            {
                log.ErrorException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
                throw;
            }
 finally
 { 
    return dt;
}

Open in new window

Author

Commented:
The reason why I am posing the question is because I don't understand how to 'fill' the datatable..  I am certain my Sql Statement is returning rows, but the DataTable 'dt' is null in my return..

Thanks.
Ok - got it. Once you have the database object (db from your DatabaseFactory.CreateDatabase), you need to do two simple steps using your sqlQuery string (sbQry):

1) DBCommandWrapper cmdWrapper = db.GetSqlStringCommandWrapper(sbQry);
2) DataSet dsCust = db.ExecuteDataSet( cmdWrapper);

This should give you a regular ADO.NET DataSet which you can then 'Fill' like before.
Let me know if that works.

 
 DBCommandWrapper cmdWrapper = db.GetSqlStringCommandWrapper(sbQry);
 DataSet dsCust = db.ExecuteDataSet( cmdWrapper);

Open in new window

Author

Commented:
I ended up abondoning this approach as I found a workaround... Thanks for the suggestions.
I ended up solving the original problem stated in the question. After a while, the author decided to abandon their original approach. This does not take away from the fact that the author was stuck on the original approach - and that a solution (a little known solution since Enterprise Library is still not widely used) was provided.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial