locked
best way to refresh data from database using dataset RRS feed

  • Question

  • Hi,

    to refresh the application data it takes a little time.

    what's the best way to synchronize the data (using dataset) automatically after each action (add, update and delete) in the database ? Knowing that the user can change only one line in a single table and therefore it is not worth to refresh the entire database.
    Thanks.

    Thursday, April 18, 2019 12:16 PM

Answers

  • Hi MohanedAmine,

    To synchronize the data in database in real time, you can try to use "SqlDependency" to implement it.

    Here is a simple demo you can refer to:

        SqlConnection conn = null;
        SqlCommand command = null;
        // Set connection string
        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder
        {
            // set server
            DataSource = @"datasource name",
            // set database
            InitialCatalog = @"catalog name",
            // access the database using the existing windows security certificate
            IntegratedSecurity = true
        };
    
        private void Form1_Load(object sender, EventArgs e)
        {
            conn = new System.Data.SqlClient.SqlConnection(builder.ConnectionString);
            command = conn.CreateCommand();
            command.CommandText = "select Id,Name,Age from dbo.TestTable where id<>20 order by id";
    
            // Start
            SqlDependency.Start(builder.ConnectionString);
            // Get data
            GetData();
        }
    
        private void GetData()
        {
            command.Notification = null;
            SqlDependency dependency = new SqlDependency(command);
            dependency.OnChange += new OnChangeEventHandler(sqlDependency_OnChange);
    
            using (SqlDataAdapter adapter = new SqlDataAdapter(command))
            {
                System.Data.DataSet ds = new DataSet();
                adapter.Fill(ds, "test");
                dataGridView1.DataSource = ds.Tables["test"];
            }
        }
    
        void sqlDependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            // Because it is a child thread, you need to update the ui with the invoke method.
            if (this.InvokeRequired)
            {
                this.Invoke(new OnChangeEventHandler(sqlDependency_OnChange), new object[] { sender, e });
            }
            else
            {
                SqlDependency dependency = (SqlDependency)sender;
                dependency.OnChange -= sqlDependency_OnChange;
                // After the notification, the current dependency is invalid, you need to re-get the data and set the notification.
                GetData();
            }
        }
    
        private void Form1_FormClosed(object sender, FormClosedEventArgs e)
        {
            // Clear resource
            SqlDependency.Stop(builder.ConnectionString);
            conn.Close();
            conn.Dispose();
        }

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 19, 2019 1:49 AM

All replies

  • Hi MohanedAmine,

    To synchronize the data in database in real time, you can try to use "SqlDependency" to implement it.

    Here is a simple demo you can refer to:

        SqlConnection conn = null;
        SqlCommand command = null;
        // Set connection string
        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder
        {
            // set server
            DataSource = @"datasource name",
            // set database
            InitialCatalog = @"catalog name",
            // access the database using the existing windows security certificate
            IntegratedSecurity = true
        };
    
        private void Form1_Load(object sender, EventArgs e)
        {
            conn = new System.Data.SqlClient.SqlConnection(builder.ConnectionString);
            command = conn.CreateCommand();
            command.CommandText = "select Id,Name,Age from dbo.TestTable where id<>20 order by id";
    
            // Start
            SqlDependency.Start(builder.ConnectionString);
            // Get data
            GetData();
        }
    
        private void GetData()
        {
            command.Notification = null;
            SqlDependency dependency = new SqlDependency(command);
            dependency.OnChange += new OnChangeEventHandler(sqlDependency_OnChange);
    
            using (SqlDataAdapter adapter = new SqlDataAdapter(command))
            {
                System.Data.DataSet ds = new DataSet();
                adapter.Fill(ds, "test");
                dataGridView1.DataSource = ds.Tables["test"];
            }
        }
    
        void sqlDependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            // Because it is a child thread, you need to update the ui with the invoke method.
            if (this.InvokeRequired)
            {
                this.Invoke(new OnChangeEventHandler(sqlDependency_OnChange), new object[] { sender, e });
            }
            else
            {
                SqlDependency dependency = (SqlDependency)sender;
                dependency.OnChange -= sqlDependency_OnChange;
                // After the notification, the current dependency is invalid, you need to re-get the data and set the notification.
                GetData();
            }
        }
    
        private void Form1_FormClosed(object sender, FormClosedEventArgs e)
        {
            // Clear resource
            SqlDependency.Stop(builder.ConnectionString);
            conn.Close();
            conn.Dispose();
        }

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 19, 2019 1:49 AM
  • you understand exactly what I am looking for, with an effective strict answer :)

    you are a hero

    Thnaks

    Friday, April 19, 2019 9:06 AM