The purpose of this class is to save you from having to continuously re-query the database to get new data. You would have probably done this by setting up a timer that executes every X amount of seconds so that your display control would be displaying the most up-to-date information. You will no longer have to do this.
We will be using a Service Broker and a QUEUE in SQL Server 2005. These were new additions to SQL Server 2005. I will assume these are also in SQL Server 2008, but can't guarantee.
My example will be doing something very simple. I have a "Users" table in my database with two fields: FirstName and LastName. I am simply displaying these in a ListBox on one form. On a second form, I have textboxes to insert the data into the database.
So first, we need to create the Queue and the Service broker and assign the privileges to the SQL user.
USE YourDatabaseName; CREATE QUEUE NameChangeQueue; CREATE SERVICE NameChangeService ON QUEUE NameChangeQueue ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]); GRANT SUBSCRIBE QUERY NOTIFICATIONS TO YourUserName; ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;
You can now see that we have a new queue and a new service.
Now we move on to the code. Add this Imports statement to the top.
Imports System.Data.SqlClient
The first thing you will need to do is to test if the connecting user has the privileges for the query notifications.
Private Function DoesUserHavePermission() As Boolean
Try
Dim clientPermission As SqlClientPermission = New SqlClientPermission(Security.Permissions.PermissionState.Unrestricted)
' this will throw an error if the user does not have the permissions
clientPermission.Demand()
Return True
Catch ex As Exception
Return False
End Try
Return True
End Function
Next, we have our method to get the user names from the database.
Public Sub GetNames()
If Not DoesUserHavePermission() Then
Return
End If
lbNames.Items.Clear()
' You must stop the dependency before starting a new one.
' You must start the dependency when creating a new one.
SqlDependency.Stop(connectionString)
SqlDependency.Start(connectionString)
Using cn As SqlConnection = New SqlConnection(connectionString)
Using cmd As SqlCommand = cn.CreateCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT FirstName, LastName FROM dbo.[Users]"
cmd.Notification = Nothing
' creates a new dependency for the SqlCommand
Dim dep As SqlDependency = New SqlDependency(cmd)
' creates an event handler for the notification of data changes in the database
AddHandler dep.onchange, AddressOf dep_onchange
cn.Open()
Using dr As SqlDataReader = cmd.ExecuteReader()
While dr.Read()
lbNames.Items.Add(dr.GetString(0) & " " & dr.GetString(1))
End While
End Using
End Using
End Using
End Sub
THIS IS VERY IMPORTANT.
1. In the previous code, you will notice that my SQL query does not use the "*" wildcard to return all columns. You MUST return the exact columns that you want. If you use the "*", you will have bad consequences.
2. Also in the previous code, you will notice that my SQL query contains the "two-part" table name. This is also REQUIRED. Using just "TableName" instead of "owner.TableName" will also cause unwanted consequences.
Here is the method for the onchange event
Private Sub dep_onchange(ByVal sender As System.Object, ByVal e As System.Data.SqlClient.SqlNotificationEventArgs)
' this event is run asynchronously so you will need to invoke to run on the UI thread(if required)
If Me.InvokeRequired Then
lbNames.BeginInvoke(New MethodInvoker(AddressOf GetNames))
Else
GetNames()
End If
' this will remove the event handler since the dependency is only for a single notification
Dim dep As SqlDependency = DirectCast(sender, SqlDependency)
RemoveHandler dep.onchange, AddressOf dep_onchange
End Sub
You will also need to stop the dependency when the form closes so that it doesn't leave it running.
Private Sub Form1_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing
SqlDependency.Stop(connectionString)
End Sub
The other events..
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
GetNames()
End Sub
Private Sub btnShowForm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShowForm.Click
Dim f As Form2 = New Form2
f.Show()
End Sub
And my simple second form's code..
Imports System.Data.SqlClient
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Using cn As SqlConnection = New SqlConnection(connectionString)
Using cmd As SqlCommand = cn.CreateCommand()
cmd.CommandText = String.Format("INSERT INTO Users VALUES ('{0}', '{1}')", txtFirstName.Text, txtLastName.Text)
cmd.CommandType = CommandType.Text
cn.Open()
cmd.ExecuteNonQuery()
End Using
End Using
End Sub
And that is really all you have to do. Here are a couple of screenshots.
Before clicking "Save"...
After clicking "Save"...
This will work for SQL Server 2005 databases running on the local PC or on a server. This also works from multiple PCs. You can distribute this little app to multiple people and have them insert names, and you can sit back and watch each one that is entered.
To me, this is one of the best functionalities that I have come across. It is amazing how easy it is to get it running. I plan on starting to use it very soon.
MultiQuote
| 