Link to home
Start Free TrialLog in
Avatar of jamesdean666
jamesdean666

asked on

C# Microsoft Enterprise Library 4.1 - return DataTable

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

Avatar of avarmaavarma
avarmaavarma
Flag of United States of America image

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

Avatar of jamesdean666
jamesdean666

ASKER

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

I ended up abondoning this approach as I found a workaround... Thanks for the suggestions.
ASKER CERTIFIED SOLUTION
Avatar of avarmaavarma
avarmaavarma
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial