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..
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;
}
}
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.
Thanks.
Ok - got it. Once you have the database object (db from your DatabaseFactory.CreateData base), you need to do two simple steps using your sqlQuery string (sbQry):
1) DBCommandWrapper cmdWrapper = db.GetSqlStringCommandWrap per(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.
1) DBCommandWrapper cmdWrapper = db.GetSqlStringCommandWrap
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);
ASKER
I ended up abondoning this approach as I found a workaround... Thanks for the suggestions.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Am I missing something else in your problem?
Thanks
Open in new window