SQL dependency using stored procedure

Question

I am using SqlDependency in C# code to alert my application when there is a change in a particular column value in a row in database. I could achieve it using inline SQL.

I would like to replace it with stored procedure but for some reason the code isn't executing the stored procedure. I am pasting few lines of code from my application. Please let me know how to modify it so that I can have inline SQL replaced with a procedure call to achieve the same.

var con = new SqlConnection(ConnectionString);
SqlDependency.Stop(con.ConnectionString);
SqlDependency.Start(con.ConnectionString);

var connection = new SqlConnection(con.ConnectionString);
connection.Open();

try
{
    using (var command = new SqlCommand("inlinesql", connection))
    {
        var dependency = new SqlDependency(command);
        dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);

        using (SqlDataReader rdr = command.ExecuteReader())
        {
            try
            {
                if (rdr.HasRows)
                {
                    _autoEvent.WaitOne();
                }
                else
                {
                    continue;
                }
            }
            finally
            {
                rdr.Close();
            }
        }
    }
}
finally
{
    connection.Close();
    SqlDependency.Stop(con.ConnectionString);
}

void OnDependencyChange(object sender, SqlNotificationEventArgs e)
{
    dosomething();
}

Did the below answer work for you? If so, please mark as answer. Thanks! — ragerory, Oct 05 '15 at 18:53

score 2 · Accepted Answer · edited Oct 05 '15 at 17:22

2

Above var dependency... put:

command.CommandType = CommandType.StoredProcedure;

And replace inlinesql with the name of the stored procedure.

edited Oct 05 '15 at 17:22

marc_s

732,580
175
1,330
1,459

answered Oct 05 '15 at 13:30

ragerory

1,360
1
8
27

score 0 · Answer 2 · answered Jun 05 '18 at 14:41

Example code that worked for me:

using (var sqlConnection = new SqlConnection (_connectionString)) {
  sqlConnection.Open ();
  using (var sqlCommand = sqlConnection.CreateCommand ()) {
    sqlCommand.Parameters.Clear ();
    sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
    // YOUR STORED PROCEDURE NAME AND PARAMETERS SHOULD COME HERE
    sqlCommand.CommandText = "sp_InsertData";
    sqlCommand.Parameters.AddWithValue ("@Code", e.Entity.Symbol);
    sqlCommand.Parameters.AddWithValue ("@Name", e.Entity.Name);
    sqlCommand.Parameters.AddWithValue ("@Price", e.Entity.Price);

    try {
      var sqlDataInsert = sqlCommand.ExecuteNonQuery ();                    
    } catch (Exception ex) {
       MessageBox.Show (ex.Message, this.Title);
    }
  }
}

SQL dependency using stored procedure

2 Answers2

Linked