Click here to Skip to main content
12,998,315 members (59,530 online)

SqlDependency in a N-Tier (3 layer architecture) windows form application.

Lance Parker asked:

Open original thread
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);
            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"];
        catch (SqlException ex)
            throw ex;
        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.
Tags: C#, SqlDependency


When answering a question please:
  1. Read the question carefully.
  2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
  3. If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Let's work to help developers, not make them feel stupid.
Please note that all posts will be submitted under the The Code Project Open License (CPOL).

Advertise | Privacy | Mobile
Web01 | 2.8.170622.1 | Last Updated 26 Mar 2009
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100