Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SqlDependency
I am hoping some one can help with my problem regarding SqlDependency, to which I have found plenty of information on google but nothing regarding my specific problem.
 
The program I am currently writing is an ADO.Net application in windows forms and implements 3 layer architecture, plus uses SQL Server 2008 as the back end data base. Therefore I have a database layer which retrieves the data, a Business layer which holds the data, and a presentation layer which puts the data onto the form. The below code below sits in the data base layer, and deals with retrieving a list of messages from the User.Messages table based on the “username”.
    public class MessagesDB
    {
        public static List<Messages> GetMessageList(string UserName)
        {
            List<Messages> messageList = new List<Messages>();
            SqlConnection connection = ESM_Demand_ProformaDB.GetConection();
            string selectStatement =
                "SELECT MessageID, UserName, MessageTitle, Date, Frome," +
                "MessageText, ESMNo " +
                "FROM Users.Message " +
                "ORDER BY Date ";
            SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
            selectCommand.Parameters.AddWithValue("@UserName", UserName);
            try
            {
                connection.Open();
                SqlDataReader reader = selectCommand.ExecuteReader();
                while (reader.Read())
                {
                    Messages mesages = new Messages();
                    mesages.MesageID = (Int64)reader["MessageID"];
                    mesages.UserName = reader["UserName"].ToString();
                    mesages.MessageTitle = reader["MessageTitle"].ToString();
                    mesages.Date = (DateTime)reader["Frome"];
                    mesages.From = reader["Frome"].ToString();
                    mesages.MessageText = reader["MessageText"].ToString();
                    mesages.ESMNo = (Int64)reader["ESMNo"];
                    messageList.Add(mesages);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return messageList;
        }
    }
The code above works fine when coupled to an event in the presentation layer such as on loading a form or pushing a button. But I won’t to implement SqlDependency , hence when my start page loads it gets a list of messages for that user, then when a new message is received as message pops up saying you have a new message and re-loads the list of messages. All the methods I have seen on the internet have the On-Change event handler in the same class as the method that retrieves the data from the data base. Hence it’s easy to set the on change event handler of the SqlDependency to the Onchange event handler delegate in the same class as below.
private void dependency_OnDataChangeDelagate(object sender, SqlNotificationEventArgs e)
{
   //do somthing
   //and reset sqldependency
}
 
//Code in database method 
SqlDependency dependency = new SqlDependency(sqlCmd);
dependency.OnChange += new OnChangeEventHandler(dependency_OnDataChangedDelegate);
But how do you do this when you On-Change Delegate is in your presentation layer, and the public static to retrieve the message list is in the database layer.
 
If anyone can point me in the right direction that would be great, Also hope I explained that right, and hope the code helps.
Posted 27-Jan-13 10:24am
Comments
Zoltán Zörgő at 27-Jan-13 16:55pm
   
You said "start page", but also windows forms... I am confused. Since in asp.net you should use some other approach.
Lance Parker at 27-Jan-13 17:06pm
   
Apologies for the confusion, the form the sqldependency is on is called the start page, I should be more careful in my explanation.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

If you have a presentation layer, use it as such, and don't try to make it do what's not it's purpose. If your PL is thick, you should implement a messaging feature to notify the PL, and ask it to refresh the data. If it is thin, you should do scheduled callback on PL side to check if there is any new message, end refresh if necessary.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sarvesh Kumar Gupta 228
1 CHill60 200
2 DamithSL 153
3 OriginalGriff 138
4 Sergey Alexandrovich Kryukov 133


Advertise | Privacy | Mobile
Web04 | 2.8.140709.1 | Last Updated 27 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid