问题描述
亲爱的;
当Datagridview将自动刷新时,我有将更新Sql数据库更改的代码
此代码仅在我的计算机上有效,并且在他们需要它或希望计算机连接到我已连接到我的计算机的服务器上时,执行此操作会显示以下消息:
无法创建事件订阅.失败"
一种解决方案我有这个问题?
这是我的代码:
首先,您从路径"C:\ Program Files \ Microsoft SQL Server \ 100 \ SDK \ Assemblies"中导入Dll:
Hi dear;
I have Code that will update the Sql Database Change when Datagridview will automatically refresh
This code only works on my computer and when they need it or want a computer to connect to the server I''m connected to my computer and I do this operation gives the following message:
"Cannot create an event subscription. failed"
One solution I have this problem?
this my code:
First you import Dll From path "C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies":
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SqlEnum
2.通过SQl Server Studio运行Triger:
2. Run Triger by SQl Server Studio:
USE [master]
GO
ALTER DATABASE [Databasename] SET ENABLE_BROKER WITH NO_WAIT
GO
USE [Databasename]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[Tablename_EventSender]
ON [dbo].[Tablename]
FOR INSERT,UPDATE,DELETE AS
BEGIN
BEGIN TRANSACTION
CREATE TABLE [dbo].[Tablename_Tmp_For_Event](
[ID] [bit] NULL
) ON [PRIMARY]
DROP TABLE [dbo].[Tablename_Tmp_For_Event]
COMMIT
END
GO
3.打开Viual Studio并创建项目并创建一个类,然后将以下代码复制到类:
3. open Viual Studio and Create project and Create A class then Copy below code to Class:
Imports Microsoft.SqlServer.Management.Smo
Public Class DatabaseWatcher
Private Shared WatcherServer As Server
Private Shared db As Database
Public Shared Event TableChanged As EventHandler(Of EventArgs)
Private Shared Sub Table_Changed(ByVal sender As Object, ByVal e As ServerEventArgs)
For Each prpty In e.Properties
'If prpty.Value = "TableName_Tmp_For_Event" Then
If prpty.Value.ToString = "TableName_Tmp_For_Event" Then
'Do Here!
' MsgBox("ِYour Table Data Change!")
RaiseEvent TableChanged(prpty.Value, New EventArgs)
End If
Next
End Sub
Public Shared Sub RegisterEvent()
Try
WatcherServer = New Server("Servername")
WatcherServer.ConnectionContext.LoginSecure = False
WatcherServer.ConnectionContext.Login = "SQL Username"
WatcherServer.ConnectionContext.Password = "SQL Password"
WatcherServer.ConnectionContext.AutoDisconnectMode = False
db = WatcherServer.Databases("Databasename")
Dim databaseCreateEventSet As New DatabaseEventSet
databaseCreateEventSet.DropTable = True
Dim serverCreateEventHandler As ServerEventHandler
serverCreateEventHandler = New ServerEventHandler(AddressOf Table_Changed)
db.Events.SubscribeToEvents(databaseCreateEventSet, serverCreateEventHandler)
db.Events.StartEvents()
Catch ex As Exception
MsgBox("SMO ERROR : " & vbNewLine & ex.Message)
'My.Computer.Clipboard.SetText(Err.Description)
End Try
End Sub
4.创建表单并复制以下代码!
4. Create Form And Copy Below code!
Imports System.Data.SqlClient
Imports System.Data
Public Class Form1
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
DoAsycEventraise()
RefreshGrid()
End Sub
Private Sub DoAsycEventraise()
Try
CheckForIllegalCrossThreadCalls = False
AddHandler DatabaseWatcher.TableChanged, AddressOf SQLEvent_Handler
Dim t As New Threading.Thread(AddressOf DatabaseWatcher.RegisterEvent)
t.SetApartmentState(Threading.ApartmentState.MTA)
t.Start()
Catch ex As Exception
End Try
End Sub
Private Sub SQLEvent_Handler(sender As System.Object, e As System.EventArgs)
RefreshGrid()
End Sub
Private Sub RefreshGrid()
Try
Application.DoEvents()
Dim TSQL As String = "Select * From Tablename"
Using Conn As New SqlConnection("Data Source=Servername;Initial Catalog=Databasename;Integrated Security=True;User ID=SQL Username;password=SQl Password")
Dim adp As New SqlDataAdapter(TSQL, Conn)
Dim res As New DataTable
adp.Fill(res)
adp.Dispose()
Conn.Dispose()
If res IsNot Nothing Then Me.DataGridView1.DataSource = res
End Using
Catch ex As Exception
End Try
End Sub
Please help me about this Application!
推荐答案
这篇关于刷新Datagrid视图SQL数据库何时更新?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!