刷新Datagrid视图SQL数据库何时更新? [英] Refresh Datagrid view When SQL Database Update?

问题描述

亲爱的；

当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!

推荐答案

也许是权限问题?

在这里，他们可能会为您提供解决方案:

http://social.msdn.microsoft.com/论坛/en-US/sqldatabaseengine/thread/608f94d6-d982-4a94-9b13-856ce643cb2a [

Maybe a rights problem?

Here they have a solution for you maybe:

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/608f94d6-d982-4a94-9b13-856ce643cb2a[^]

这篇关于刷新Datagrid视图SQL数据库何时更新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！