Execute DataSet - Not returning data - URGENT HELP NEEDED

Topics: Data Access Application Block
Dec 5, 2012 at 4:43 PM

I am using entlib 4.1 in my ASP.Net C# application. I am consulting at a company and have no other .NET resources to ask for help. We are going live into production with this application on 1/1/13.

I have implemented entlib 4.1 data block into the application. I use it for all my data calls. I have created a data layer class in my application for managing all the data calls. The code has been in place for over a month and everything has been working without any issues.

Yesterday morning all of a sudden one of the methods in my data layer which executes the ExecuteDataSet method of the data block has stopped working! The ExecuteDatset command runs, no errors or exceptions are raised. The SP I am calling has an output parameter which is getting returned just fine. When I run my code in DEBUG and I view the dataset returned it is completely empty!

I ran the SP from SQL management studio with the same parameters and it returns 4 rows of data along with the output parameter. I am completely lost as to how to track down this problem? Listed below is my method in my data layer.

I really need to get this resolved, ASAP. If someone can please help me here I would greatly appreciate it!

  public DataSet ReqHeaderSearchByPage(Int32 intCurrPage, Int32 intPageSize, Int32 intRQID = 0, Int32 intSTS = 0, 
                                                                  string strREQ = null, Int32 intORG = 0, Int32 intRType = 0, Int32 intORM  = 0)
            {
                try
                {
                    DataSet dsHDR;
                    OrmDB = DatabaseFactory.CreateDatabase("OrmDB");
                    sqlCommand = "[dbo].[ORM_RequestHeader_SearchByPage]";
                    DbCommand dbCmd = OrmDB.GetStoredProcCommand(sqlCommand);
                    if (intRQID != 0)
                    {
                        OrmDB.AddInParameter(dbCmd, "@RQID", DbType.Int32, intRQID);
                    }
                    if (intSTS != 0)
                    {
                        OrmDB.AddInParameter(dbCmd, "@STS", DbType.Int32, intSTS);
                    }
                    if (strREQ != null)
                    {
                        OrmDB.AddInParameter(dbCmd, "@REQ", DbType.String, strREQ);
                    }
                    if (intORG != 0)
                    {
                        OrmDB.AddInParameter(dbCmd, "@ORG", DbType.Int16, intORG);
                    }
                    if (intRType != 0)
                    {
                        OrmDB.AddInParameter(dbCmd, "@TYP", DbType.Int32, intRType);
                    }
                    if (intORM != 0)
                    {
                        OrmDB.AddInParameter(dbCmd, "@ORM", DbType.Int32, intORM);
                    }
                    OrmDB.AddInParameter(dbCmd, "@P_CurrentPageIndex", DbType.Int32, intCurrPage);
                    OrmDB.AddInParameter(dbCmd, "@P_PageSize", DbType.Int32, intPageSize);
                    OrmDB.AddOutParameter(dbCmd, "@P_TotalRecords", DbType.Int32, 6);
                    dsHDR = OrmDB.ExecuteDataSet(dbCmd);
                    if (OrmDB.GetParameterValue(dbCmd, "@P_TotalRecords") != null)
                    {
                        _intTotalRecs = Convert.ToInt32(OrmDB.GetParameterValue(dbCmd, "@P_TotalRecords"));
                    }
                    return dsHDR;
                }
                catch (Exception ex)
                {
                    LogApplicationError(ex, "ORM_DAL-ReqHeaderSearchByPage", _strUserName);
                    _intTotalRecs = 0;
                    return null;
                }
            }

Dec 5, 2012 at 7:13 PM

Can someone also tell me where to find the PDB files for entlib 4.1? I cannot find them on my PC anywhere. If I can debug entlib, maybe I can find the root cause.

Dec 5, 2012 at 11:44 PM

Issue has been resolved.

The parameter @REQ was passing blanks to the SP and thus causing the SP to not return any rows.

Dec 6, 2012 at 4:28 PM

Good to hear that you solved the issue.  Just for posterity (and maybe a future user), let me say that the Enterprise Library 4.1 PDBs can be found on the Enterprise Library 4.1 - October 2008.  

Also, I think a good approach in these "strange" type of scenarios would be to to trace the database to see the input and output parameters going over the wire.

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com