how do add multi sql dependency to this code?

2019-08-13 01:32发布

问题:

i have person table in sql server with fields id and Name

and csharp statement that work well.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace SQLNotifications
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }


        string connectionstring = @"Server=EEPERSIAN-PC\SQLEXPRESS;Database=Chatter;User ID=sa;pwd=1";

        delegate void GridDelegate(DataTable table);

        SqlDependency dep;

        private void Form1_Load(object sender, EventArgs e)
        {
            SqlDependency.Start(connectionstring);

            UpdateGrid();
        }

        private void UpdateGrid()
        {
            string sql = "select Name from dbo.person";

            DataTable dt = new DataTable();

            using (SqlConnection con = new SqlConnection(connectionstring))

            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    con.Open();
                    dep = new SqlDependency(cmd);

                    dep.OnChange += dep_OnChange;

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        dt.Load(rdr);
                    }
                }
            }

            dataGridView1.Invoke(
                (GridDelegate)delegate(DataTable table)
                {
                    dataGridView1.DataSource = table; 
                }
                , dt);
        }
        private void dep_OnChange(object sender, SqlNotificationEventArgs e)
        {
            MessageBox.Show("Insert Accourd");
            UpdateGrid();
        }

        private void Form1_FormClosed(object sender, FormClosedEventArgs e)
        {
            SqlDependency.Stop(connectionstring);
        }
    }
}

now , i have 2 problem :

1- i cant understant this section of code

 dataGridView1.Invoke(
                (GridDelegate)delegate(DataTable table)
                {
                    dataGridView1.DataSource = table; 
                }
                , dt);

2- i want monitor multi table instead of one table . How i can do this ?

Please help me to solve this problem.

回答1:

  1. A delegate is just a method that doesnt return anything. This code is just a call to an inline method.

Think of it like this:

 MyDelegateFunction(dt);

 private void MyDelegateFunction(DataTable table)
 {
    dataGridView1.DataSource = table; 
 }

dt is the parameter passed into the method, it is set to the datasource of the dataGridView

Here is the MSDN article for that method.

2 - If by monitoring many tables you mean you want to see the data from them, then you just change the sql that you are using to query.

This:

string sql = "select Name from dbo.person";

Might change to this:

string sql = "select 
                 name, other, data 
              from 
                dbo.person p 
                inner join dbo.anothertable a on p.personid = a.personid"

EDIT:

After a bit of clarification, you want to set up insert triggers on your tables to monitor them. I'd suggest something like this:

  1. An insert occurs, and the OnInsert trigger fires.
  2. The insert trigger puts logging information into a logging table.
  3. Your c# application polls that table for new information and executes accordingly.

Some ideas like this are discussed in Some data changes in the database. How can I trigger some C# code doing some work upon these changes?