I am trying to implement query notifications for a current project. I'm implementing query notifications using SqlDependency. I'm able to get the code to work when I do inline SQL for the query directly in the Ado.net command, however, when I put the same exact query in a stored procedure the query notification doesn't seem to work. It returns in the SqlNotificationEventArgs the value of Query(8) in the e.Info parameter of the event args. It also continues to fire approx. every 30+ seconds.
Public Sub GetCust(ByVal CustomerId As String)
Dim oConn As SqlConnection
Dim connectionString As string
connectionString = GetConnectionString()
SqlDependency.Stop(connectionString)
SqlDependency.Start(connectionString)
oConn = New SqlConnection(connectionString)
'This is the code for the stored procedure path
'Dim oCommand As New SqlCommand("SelectCustomerInfo", oConn)
'oCommand.CommandType = CommandType.StoredProcedure
'oCommand.Parameters.AddWithValue("@CustomerId", CustomerId)
'This is the code for using inline SQL
Dim strSQL As String
strSQL = "select [CustomerId],[name], [age],[gender],[favorite_food] from dbo.custpreferences where customerid = '" & CustomerId & "'"
Dim oCommand As New SqlCommand(strSQL, oConn)
oCommand.CommandType = CommandType.Text
oCommand.Notification = Nothing
Dim dep As SqlDependency = New SqlDependency(oCommand)
AddHandler dep.OnChange, AddressOf cust_onchange
oConn.Open()
Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter(oCommand)
dataAdapter.Fill(dsCustomers)
If (Not dataAdapter Is Nothing) Then dataAdapter.Dispose()
If Not oConn Is Nothing Then oConn.Dispose()
End Sub
Private Sub cust_onchange(ByVal sender As System.Object, ByVal e As System.Data.SqlClient.SqlNotificationEventArgs)
GetCustInfo(mCustomer.strCustomerID)
Dim dep As SqlDependency = DirectCast(sender, SqlDependency)
RemoveHandler dep.OnChange, AddressOf cust_onchange
End Sub
Here's the stored procedure:
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET ARITHABORT ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SelectCustInfo]
@CustomerID VARCHAR(6)
AS
SELECT
[CustomerId], [name], [age], [gender], [favorite_food]
FROM
dbo.CustInfo (NOLOCK)
WHERE
(CustomerId = @CustomerID)
Any ideas why this works with inline SQL vs using a stored procedure?
