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)
{
}
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.