I am trying setup SQL dependency on local machine using SQL express, and i have following questions
1>Does SQL dependency works with SQLExpress or do i need full SQL Server?
2>On local machine, The dbo user is running under my login account. My login account is "sysadmin" in ./SQLExpress. Im assuming that means it has all the permissions. Do i still need to set all other permissions to run SQLDependency?
3>When i Start dependency by passing queue name, i get the following exception
When using SqlDependency without providing an options value, SqlDependency.Start() must be called prior to execution of a command added to the SqlDependency instance.
When I dont pass queue name, I dont get exception but .net application doesnt get call back either when SQL table is updated.
(Note that eventually i'm going to call Intialization() method at application start)
Here is my complete code
public class SqlHelper
{
public void Initialization()
{
// Create a dependency connection.
SqlDependency.Start(GetConnectionString(), "SendStatusChangeMessages");
CanRequestNotifications();
}
public void SomeMethod()
{
// Assume connection is an open SqlConnection.
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
con.Open();
// Create a new SqlCommand object.
using (SqlCommand command = new SqlCommand(
"SELECT BatchStatus from MyTable WHERE ID = 1",
con))
{
// Create a dependency and associate it with the SqlCommand.
SqlDependency dependency = new SqlDependency(command);
// Maintain the refence in a class member.
// Subscribe to the SqlDependency event.
dependency.OnChange += new
OnChangeEventHandler(OnDependencyChange);
Initialization();
// Execute the command.
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var message = reader.GetString(0);
}
}
}
}
}
// Handler method
public void OnDependencyChange(object sender,
SqlNotificationEventArgs e)
{
var info = e.Info;
}
public void Termination()
{
// Release the dependency.
SqlDependency.Stop(GetConnectionString(), "SendStatusChangeMessages");
}
private string GetConnectionString()
{
return System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
}
private bool CanRequestNotifications()
{
SqlClientPermission permission =
new SqlClientPermission(
PermissionState.Unrestricted);
try
{
permission.Demand();
return true;
}
catch (System.Exception)
{
return false;
}
}
}
- Edited by lax4u Wednesday, April 30, 2014 4:09 AM
Other recent topics
