method returning dataset question

  • Thread starter Thread starter Mike P
  • Start date Start date
M

Mike P

I have written a method that is meant to return a dataset from a SQL
Server table (or not if there is nothing there to return). My app
regularly times out and crashes at this point and I don't know why.
Here is my method :

public DBResult Get_Prepay_Dataset(out DataSet dsPrepay, string strCUG,
string strSubcugPrepayQuery, DateTime dtmStartDatePrepayQuery, DateTime
dtmEndDatePrepayQuery)
{
DBResult dbrGetPrepayDataset;
string strGetPrepayDataset;

if (strSubcugPrepayQuery != "")
{
strGetPrepayDataset = "SELECT INCEPTTIME, STARTTIME, ENDTIME,
RTRIM(ODN) AS 'ODN', RTRIM(OOLI) AS 'OOLI' FROM RSP_LOG WITH (NOLOCK)
WHERE CUG = '" + strCUG + "' AND SUB_CUG = '" + strSubcugPrepayQuery +
"' AND STARTTIME BETWEEN '" + dtmStartDatePrepayQuery + "' AND '" +
dtmEndDatePrepayQuery + "'";
}
else
{
strGetPrepayDataset = "SELECT INCEPTTIME, STARTTIME, ENDTIME,
RTRIM(ODN) AS 'ODN', RTRIM(OOLI) AS 'OOLI' FROM RSP_LOG WITH (NOLOCK)
WHERE CUG = '" + strCUG + "' AND STARTTIME BETWEEN '" +
dtmStartDatePrepayQuery + "' AND '" + dtmEndDatePrepayQuery + "'";
}

SqlConnection objConnection = new
SqlConnection(ConfigurationSettings.AppSettings["strConnectHomelinkTest"
]);
dsPrepay = new DataSet();

try
{
SqlDataAdapter objAdapter = new SqlDataAdapter(strGetPrepayDataset,
objConnection);

objAdapter.Fill(dsPrepay, "tblPrepay");

int intCount = dsPrepay.Tables["tblPrepay"].Rows.Count;

if (intCount > 0)
{
dbrGetPrepayDataset = DBResult.Valid;
}
else
{
dbrGetPrepayDataset = DBResult.Invalid;
}
}
catch
{
dsPrepay = null;
dbrGetPrepayDataset = DBResult.Error;
}

objConnection.Close();

return dbrGetPrepayDataset;
}

Can anybody find something I am doing wrong in my code? Any help would
be really appreciated.


Cheers,

Mike
 
Hi,

The only part where it can be get stuck is in the Fill method , how big is
your DB and how long this query takes?

Does this happens with only you accesing or when more than one page is being
served?



cheers,
 
Hi,

The table being accessed has approx 17,000 records. At any one time
there may be 0-20 users accessing it via my code.


Regards,

Mike
 
Does it occur when only one user is accesing it?

Maybe the bottleneck is in the DB access, if the DB server is slow, or too
much data is returned, did you try it without the hint?

If the DB does not change a lot maybe a better idea is to keep it in the
cache and refresh it from time to time.

are you reloading the data on each postback ?

you should look in a way to improve the access to the dB.


cheers,
 
Back
Top