Updating Database from DataGridView Using a Class Two-Tier Architecture

• Download demo project - 59 KB

Introduction

Background

Till now, we were using a very basic idea to update database from DatagridView in WinForm applications. For this, we were writing code on each WinForm pages which contains DataGridView.

Now, we will be able to write a single class and use it for every winform pages having DataGridView control.

Using the Code

So let us start now...

Create a table name = DemoTableProduct

* Insert some data in the DemoTableProduct.

Now open Visual Studio and create a new Windows Form application project. Give it a suitable name like DataGridViewUpdateDemo.

Drag a DataGridView control and two Buttons and design your Windows Form like the image below:

Add a reference to System.Configuration in your project and add your database connection string in App.Config file like:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <startup>
    <supportedRuntime version="v4.0" 
    sku=".NETFramework,Version=v4.5" />
  </startup>
  <connectionStrings>
    <add name="connStr" 
    connectionString="your connection string here" 
    providerName="System.Data.OleDb"/>
  </connectionStrings>
</configuration>

Now create a class DatabaseManager.cs and write the following code in it:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace DataGridViewUpdateDemo
{
    class DatabaseManager
    {
        string connectionString = 
        ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
        OleDbConnection conn;
        OleDbDataAdapter adapter;
        OleDbCommandBuilder builder;
        public DatabaseManager()
        {
            conn = new OleDbConnection(connectionString);
        }

        public DataTable FillData(string command)
        {
            if (command.Trim().Length < 0)
                throw new Exception("Command text is empty");
            OleDbDataAdapter _ad = new OleDbDataAdapter(command, conn);
            DataTable dt = new DataTable();
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            _ad.Fill(dt);
            adapter = _ad;
            builder = new OleDbCommandBuilder(adapter);
            adapter.InsertCommand = builder.GetInsertCommand();
            adapter.UpdateCommand = builder.GetUpdateCommand();
            adapter.DeleteCommand = builder.GetDeleteCommand();

            return dt;
        }
        public int UpdateData(DataTable dt)
        {
            if (adapter == null)
                throw new Exception("Parameter adapter is null");
            return adapter.Update(dt);
        }
    }
}

On your winform's code page, write the following code...

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace DataGridViewUpdateDemo
{
    public partial class Form1 : Form
    {
        DatabaseManager db;
        DataTable dtData;
        public Form1()
        {
            InitializeComponent();
            db = new DatabaseManager();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            LoadData();
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            UpdateData();
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            LoadData();
        }

        private void LoadData()
        {
            dtData = db.FillData("SELECT * FROM DEMOTABLEPRODUCT");
            gvMain.DataSource = dtData;
        }
        private void UpdateData()
        {
            try
            {
                int i = db.UpdateData(dtData);
                MessageBox.Show(i + " : records updated successfully");
            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message);
            }
        }
    }
}

Now debug the project by pressing F5 button. Try to enter some data in datagridview and click Update button. For example:

Points of Interest

I would like to talk about the interesting thing in this app. That is when you debug the application, it will take too much time to load data.

Do you know why?

No problem, I will tell you. Since we have used OleDbCommandBuilder class to get Insert, Update and Delete commands, this OleDbCommandBuilder class takes too much time to generate commands.

One more interesting thing is that you can make it VERY FAST by using the concept of Threading.

Just execute the following lines from DatabaseManager in a Thread.

builder = new OleDbCommandBuilder(adapter);
adapter.InsertCommand = builder.GetInsertCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();

Download the demo project from the link given at the top of the page.

Please vote and comment if you find it helpful. Any suggestions are appreciated.

Thank you for reading!

History

• 30^th March, 2017: Initial version