none
SqlCommand not returning data after some time RRS feed

  • Question

  • Hello, here is my situation:

    First of all, I have a windows service that updates (inserts records) data in SQL database table according to information given by some events.

    Secondly, I have an ASP.NET web application that every 3 seconds (using timer) does following things:

    -set LastCheck value for logged user to current DateTime (this way I recognize that user is not using application and can be deleted after one minute of no "LastCheck")

    - check table (updated by the service) if there is a new record and process it eventually

    Everything seems to be ok some time, however, time to time SqlCommand returns null (no matter there is a new record in the table). The same issue with a DoCheck method.

    My code looks like:

    class SqlService
    {
    SqlConnection connection;
    SqlCommand command;
    
    private void OpenConnection() { connection = new SqlConnection(connectionString); command = connection.CreateCommand(); connection.Open(); } private void CloseConnection() { if (connection.State != ConnectionState.Closed) connection.Close(); } public void DoCheck() {
    try
    {
    OpenConnection(); // do ExecuteNonQuery stuff
    }
    finally { CloseConnection(); } }

    public Record GetNewRecord() {
    Record record = null;
    try
    {
    OpenConnection(); // do ExecuteReader stuff
    record = ...
    }
    finally { CloseConnection(); }
    return record;
    } } in timer.Tick: ... SqlService service = new SqlService(); service.DoCheck(); Record record = service.GetNewRecord(); ...

    I tried everything to make this work, for example setting Min Pool Size = 5 and Max Pool Size = 20 in connection string. It sometimes get better but I don't know.. usually after a few hours either DoCheck() doesn't set the right value in the table or GetNewRecord() returns null, or both.

    Is there anything I'm doing bad? Or is some special treating needed when using sql commands every 3 seconds?

    The application is supposed to run like a few hours or all day long and it can't catch these events directly (they must be cought only from server, that's why I've written the service too).

     

    Thank you very much in advance for any ideas.

    Friday, March 19, 2010 11:50 PM

All replies

  • >some time, however, time to time SqlCommand returns null

    What method exactly is returning null?  ExecuteNonQuery?

    Also, are you using the ReadUncommitted isolation level or the NOLOCK hint in the queries involved in this operation?  If so, there are cases where SQL Server will skip over your data.  These modes sacrifice correctness for performance, sometimes in surprising ways.

     

     

    Saturday, March 20, 2010 6:59 PM
  • >some time, however, time to time SqlCommand returns null

    What method exactly is returning null?  ExecuteNonQuery?

    ExecuteReader simply returns no rows or ExecuteNonQuery doesn't make any change in database.

    I'm not using ReadUncomitted or nothing like that...

    If guess that could be something like connection leak, but I don't know where to look for a mistake.

    Saturday, March 20, 2010 9:51 PM
  • Some other thoughts:

    1.  Are there multiple threads sharing the same SqlConnection object?  That can result in incorrect behavior.

    2.  How often does the incorrect behavir occur?

    3.  What does your SQL statement look like?

    4.  Are other apps simultaneously updating this data in the database?

     

    Saturday, March 20, 2010 10:40 PM
  • Some other thoughts:

    1.  Are there multiple threads sharing the same SqlConnection object?  That can result in incorrect behavior.

    2.  How often does the incorrect behavir occur?

    3.  What does your SQL statement look like?

    4.  Are other apps simultaneously updating this data in the database?

     

    1. No, there is only one thread in the application.

    2. Sometime after a few hours, other time after 30 minutes.

    3.

    I have SqlService:

        public class SqlService
        {
            private SqlConnection connection;
            private SqlCommand command;

            public void OpenConnection()
            {
                string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["OPConnectionString"].ConnectionString;
                connection = new SqlConnection(connStr);
                command = connection.CreateCommand();
                connection.Open();
            }

            public void CloseConnection()
            {
                if (command != null) command.Dispose();
                if (connection != null) connection.Dispose();
            }

            public Record GetNewRecord(int id)
            {
                SqlDataReader reader = null;
                Hovor hovor = null;
                try
                {
                    OpenConnection();
                    command.CommandText = "SELECT ID, //columns// FROM Hovor WHERE ID = " + id.ToString() + " AND State = 0";
                    reader = command.ExecuteReader();
                    while (reader.Read())
                    {

                        int id = reader.GetInt32(0); //columns = ...//
                        record = new Record(id, //columns//);
                    }
                }
                finally { reader.Close(); CloseConnection(); }
                return record;
            }
     
            public void DoCheck(int id)
            {
                try
                {
                    OpenConnection();
                    command.CommandText = String.Format("UPDATE Operator SET LastCheck = '{0}' WHERE ID = {1}",
                        DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), id);
                    command.ExecuteNonQuery();
                }
                finally { CloseConnection(); }
            }
    }

    On Timer's tick event I do this:

    protected void Timer_Tick(object sender, EventArgs e)

    {

    SqlService service = new SqlService();

    service.DoCheck(id);

    Record newRecord = service.GetNewRecord(id);

    ... // some other stuff

    }

     

    4. There are multiple instances of the web application running and so accessing the database

    Thanks for your replies..

    Monday, March 22, 2010 12:32 PM