Click here to Skip to main content
15,867,869 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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”.
C#
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.
C#
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
Comments
Zoltán Zörgő 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 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

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.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900