Keep C# app open and check the database

Question

I have this app. Is connected with one database and sending notification to android devices. I want to keep this app always open and check the database for new record. The only idea i had is to put in one infinity loop like while(true) but i have warning in the line with connection.Open(); about memory and the program is stopping.

namespace AndroidParse
{
class Program
{

    static void Main(string[] args)
    {
        //SqlDataReader reader;
        SqlConnection conn = new SqlConnection();

        string queryString = "SELECT TOP 1 device_id FROM Temp ORDER BY ID_Requests DESC";
        string connectionString = "XXXX";

        while (true)
        {

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(queryString, connection);

                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {



                        Console.WriteLine(reader[0]);
                        bool isPushMessageSend = false;

                        string postString = "";
                        string urlpath = "https://api.parse.com/1/push";
                        var httpWebRequest = (HttpWebRequest)WebRequest.Create(urlpath);

                        postString = "{\"data\": { \"alert\": \"Finally is working\" },\"where\": { \"device_id\": \"" + reader[0] + "\" }}";
                        httpWebRequest.ContentType = "application/json";
                        httpWebRequest.ContentLength = postString.Length;
                        httpWebRequest.Headers.Add("X-Parse-Application-Id", "XXXX");
                        httpWebRequest.Headers.Add("X-Parse-REST-API-KEY", "XXXX");
                        httpWebRequest.Method = "POST";

                        StreamWriter requestWriter = new StreamWriter(httpWebRequest.GetRequestStream());
                        requestWriter.Write(postString);
                        requestWriter.Close();
                        var httpResponse = (HttpWebResponse)httpWebRequest.GetResponse();


                        using (var streamReader = new StreamReader(httpResponse.GetResponseStream()))
                        {
                            var responseText = streamReader.ReadToEnd();
                            JObject jObjRes = JObject.Parse(responseText);
                            if (Convert.ToString(jObjRes).IndexOf("true") != -1)
                            {
                                isPushMessageSend = true;
                            }
                        }
                        //--------------------------------------------------
                        SqlConnection sqlConnection1 = new SqlConnection(connectionString);
                        SqlCommand cmd = new SqlCommand();
                        SqlDataReader reader1;

                        cmd.CommandText = "delete from Temp where ID_Requests in (select top 1 ID_Requests from Temp order by ID_Requests desc)";

                        cmd.Connection = sqlConnection1;

                        sqlConnection1.Open();

                        reader1 = cmd.ExecuteReader();
                        // Data is accessible through the DataReader object here.

                        sqlConnection1.Close();
                        //--------------------------------------------------
                        Console.ReadLine();
                    }
                }
                finally
                {
                    reader.Close();
                }
                connection.Close();

            }
        }

    }

    private static void println()
    {
        throw new NotImplementedException();
    }
}

}

If you have write access to the DB, just set up a Trigger on Update or Insert and have it execute this script. Otherwise, yeah, A timer is probably best. — DiscipleMichael, Jan 26 '16 at 17:03
Keep in mind, if you are using a timer (and not a DB trigger), if more than one update is made within the timer.tick events then you will only act on the most recent one because you are only selecting the "TOP 1" — DiscipleMichael, Jan 26 '16 at 17:09
This is not problem because after that i delete the TOP 1 and select the next TOP 1 — Konstantinos Evangelidis, Jan 26 '16 at 17:16
Look at the SqlDependency for checking new records instead of polling. — Denis Reznik, Jan 26 '16 at 17:29
After one month, i tried both timer and trigger. Both are working good in my case but generally Trigger is more flexible and i prefer it. Thank you all. — Konstantinos Evangelidis, Mar 02 '16 at 15:34

score 2 · Answer 1 · edited May 23 '17 at 12:31

Using a SqlDependency object like Denis Reznik suggested is a great solution. A few things to keep in mind:

SqlDependency requires the SQL Server Service Broker service to be running on SQL Server (more details here: https://msdn.microsoft.com/en-us/library/ms172133(v=vs.110).aspx)
The queries that can be used in the SqlCommand are essentially continuously executed on the server... Because of this there is a handful of limitations on what the query can do (e.g. no aggregates). More details in the SO answer from Smudge202 here: What are the limitations of SqlDependency
I have found that using SqlDependency to simply notify of a change, and then acting on that by calling data access methods, etc... is easier than attempting to actually use the query to retrieve data. So, in your example, you may want to let the SqlDependency notify there is a change, then create a separate data access method / sp / etc... to retrieve details like the device_id.

Here is a sample that is sort of based on your code above... It will probably require a few tweaks. Good Luck!

namespace AndroidParse
{
    public class DbMonitor
    {
        private readonly string _connectionString = ConfigurationManager.ConnectionStrings["XXXXX"].ConnectionString;
        private SqlDependency _dependency;
        private SqlConnection _conn;
        private SqlCommand _command;
        public void MonitorDatabase()
        {
            SqlDependency.Start(_connectionString);

            // Open DB Connection
            using (_conn = new SqlConnection(_connectionString))
            {
                // Setup SQL Command
                using (_command = new SqlCommand("SELECT TOP 1 device_id FROM Temp ORDER BY ID_Requests DESC", _conn))
                {
                    // Create a dependency and associate it with the SqlCommand. *** MAGIC ****
                    _dependency = new SqlDependency(_command);

                    // Subscribe to the SqlDependency event.
                    _dependency.OnChange += HandleDatabaseChange;

                    // Execute 
                    _command.Connection.Open();
                    _command.ExecuteReader();
                }
            }
        }

        public void Stop()
        {
            SqlDependency.Stop(_connectionString);
        }

        private void HandleDatabaseChange(object sender, SqlNotificationEventArgs e)
        {
            if (e.Info == SqlNotificationInfo.Invalid)
            {
                Console.WriteLine("The above notification query is not valid.");
            }
            else
            {
                Console.WriteLine("Database Changed based on query");
                Console.WriteLine("------------------------------------");
                Console.WriteLine("Event Details:");
                Console.WriteLine("Notification Info: " + e.Info);
                Console.WriteLine("Notification source: " + e.Source);
                Console.WriteLine("Notification type: " + e.Type);
            }

            //PushMessage logic here
            bool isPushMessageSend = false;

            string postString = "";
            string urlpath = "https://api.parse.com/1/push";
            var httpWebRequest = (HttpWebRequest)WebRequest.Create(urlpath);

            // Use Query to get device_id? 

            postString = "{\"data\": { \"alert\": \"Finally is working\" },\"where\": { \"device_id\": \"" + "deviceID" + "\" }}";
            httpWebRequest.ContentType = "application/json";
            httpWebRequest.ContentLength = postString.Length;
            httpWebRequest.Headers.Add("X-Parse-Application-Id", "XXXX");
            httpWebRequest.Headers.Add("X-Parse-REST-API-KEY", "XXXX");
            httpWebRequest.Method = "POST";

            StreamWriter requestWriter = new StreamWriter(httpWebRequest.GetRequestStream());
            requestWriter.Write(postString);
            requestWriter.Close();
            var httpResponse = (HttpWebResponse)httpWebRequest.GetResponse();


            using (var streamReader = new StreamReader(httpResponse.GetResponseStream()))
            {
                var responseText = streamReader.ReadToEnd();
                JObject jObjRes = JObject.Parse(responseText);
                if (Convert.ToString(jObjRes).IndexOf("true") != -1)
                {
                    isPushMessageSend = true;
                }
            }

            // Resume Monitoring... Requires setting up a new connection, etc.. Reuse existing connection? Tried.
            MonitorDatabase();
        }
    }
}

class Program
{

    static void Main(string[] args)
    {
        try
        {
            // Start the cheese monitor
            DbMonitor dbMonitor = new DbMonitor();
            dbMonitor.MonitorDatabase();

            Console.WriteLine("Monitoring....Press any key to stop.");
            Console.Read();

            dbMonitor.Stop();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            throw;
        }
        finally
        {
            SqlDependency.Stop(ConfigurationManager.ConnectionStrings["XXXXX"].ConnectionString);
        }
    }
}

Keep C# app open and check the database

1 Answers1