Thanks for reply
Here are my answers and code snipshot
1) Are you using DataSet?
=> Yes
2) In the service you created is the DataSet/DataTable held in memory?
=>Yes
3) Do you construct a DataSet new each time when the service starts again?
=> yes
4) Do you get outOfMemoryExceptions?
=> Not yet
5) Do you fill the Data from Sql Server into the DataSet, how many rows?
=> we use SQL server Express edition
6) What operations are you using in DataSet, are you just inserting rows?
=> mostly change the values of some fields
Here is the function called for each job (generally a system have 20-25 jobs) after every 5 seconds
public int SetJobStatisticsToDB(JobStatistics JobStats)
{
int iRetVal = Error.ERROR_FAIL;
ArrayList ErrList = new ArrayList();
try
{
Monitor.Enter(this);
// open the db connestion
int iRet = this.OpenRTMDBConnection(ref ErrList);
if (Error.ERROR_FAIL == iRet ||
true == Error.ErrInList(ErrList))
{
Logger.Log(ELogLevel.ERROR, RTMError.FAILED_TO_OPEN_DB);
return iRetVal;
}
iRet = JobStats.ObjectToDB(this, true);
if (Error.ERROR_SUCCESS != iRet)
{
Logger.Log(ELogLevel.ERROR, RTMError.OBJECT_TO_DB_FAILED);
return iRetVal;
}
this.Update(Constants.JOBSTATISTICSDATATABLE);
iRetVal = Error.ERROR_SUCCESS;
}
catch (Exception Exp)
{
Logger.Log(Exp);
}
finally
{
Monitor.Exit(this);
}
return iRetVal;
}
public int OpenRTMDBConnection(ref ArrayList ErrList)
{
Logger.Log(ELogLevel.DEBUG, System.Reflection.MethodBase.GetCurrentMethod().Name + Constants.ENTRY);
int iRetVal = Error.ERROR_FAIL;
try
{
Monitor.Enter(this);
// check the db connestion state
if (null != this.DBCon &&
this.DBCon.State == ConnectionState.Open)
{
// DBConnection is already open. just get the tables
iRetVal = this.GetTables();
if (Error.ERROR_SUCCESS != iRetVal)
{
Logger.Log(ELogLevel.ERROR, RTMError.FAILED_TO_GET_TABLES);
}
return iRetVal;
}
Logger.Log(ELogLevel.ERROR, "Opening DB new connection");
// connection is not open. Open it
// OpenConnection will create new DBConn instance
int iRet = this.OpenConnection(ref ErrList);
if (Error.ERROR_FAIL == iRet ||
true == Error.ErrInList(ErrList))
{
Logger.Log(ELogLevel.ERROR, RTMError.FAILED_TO_OPEN_DB);
return iRetVal;
}
// DBConnection is already open. just get the tables
iRetVal = this.GetTables();
if (Error.ERROR_SUCCESS != iRetVal)
{
Logger.Log(ELogLevel.ERROR, RTMError.FAILED_TO_GET_TABLES);
}
iRetVal = Error.ERROR_SUCCESS;
}
catch (Exception Exp)
{
Logger.Log(Exp);
}
finally
{
Monitor.Exit(this);
}
/// <summary>
///
/// </summary>
/// <param name="Success"></param>
/// <param name="ErrList"></param>
/// <returns></returns>
public int OpenConnection( ref ArrayList ErrList)
{
int status = Error.ERROR_SUCCESS;
try
{
Logger.Log(ELogLevel.DEBUG, m_ConnectionString);
if (DBCon == null)
{
mDBCon = new System.Data.SqlClient.SqlConnection();
DBCon.ConnectionString = m_ConnectionString;
DBCon.Open();
}
else
{
if (DBCon.State != ConnectionState.Open)
{
DBCon.ConnectionString = m_ConnectionString;
DBCon.Open();
}
}
}
catch (Exception ex)
{
Logger.Log(ex);
status = Error.ERROR_FAIL;
}
return status;
}
/// <summary>
///
/// </summary>
/// <param name="JobStats"></param>
/// <returns></returns>
public int SetJobStatisticsToDB(JobStatistics JobStats)
{
int iRetVal = Error.ERROR_FAIL;
ArrayList ErrList = new ArrayList();
try
{
Monitor.Enter(this);
// open the db connestion
int iRet = this.OpenRTMDBConnection(ref ErrList);
if (Error.ERROR_FAIL == iRet ||
true == Error.ErrInList(ErrList))
{
Logger.Log(ELogLevel.ERROR, RTMError.FAILED_TO_OPEN_DB);
return iRetVal;
}
iRet = JobStats.ObjectToDB(this, true);
if (Error.ERROR_SUCCESS != iRet)
{
Logger.Log(ELogLevel.ERROR, RTMError.OBJECT_TO_DB_FAILED);
return iRetVal;
}
this.Update(Constants.JOBSTATISTICSDATATABLE);
iRetVal = Error.ERROR_SUCCESS;
}
catch (Exception Exp)
{
Logger.Log(Exp);
}
finally
{
Monitor.Exit(this);
}
return iRetVal;
}
// Export this Object to DB Row
public int ObjectToDB(DBManager DBMgr, bool bForce)
{
int status = Error.ERROR_SUCCESS;
try
{
//Logger.Log(ELogLevel.INFO, System.Reflection.MethodBase.GetCurrentMethod().ToString());
String Query = "JobID = " + "'" + this.JobID + "'";
DataRow dRow = null;
DBMgr.GetRow(DBMgr.ds.Tables[Constants.JOBSTATISTICSDATATABLE], Query, out dRow);
if (dRow == null && bForce == true)
{
dRow = DBMgr.ds.Tables[Constants.JOBSTATISTICSDATATABLE].NewRow();
FillRow(ref dRow);
DBMgr.ds.Tables[Constants.JOBSTATISTICSDATATABLE].Rows.Add(dRow);
}
else if (dRow != null)
{
FillRow(ref dRow);
}
}
catch (Exception ex)
{
Logger.Log(ELogLevel.ERROR, ex);
status = Error.ERROR_FAIL;
}
return status;
}
