Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / desktop / WinForms

Updating Database from DataGridView Using a Class Two-Tier Architecture

3.72/5 (9 votes)
5 Oct 2018CPOL2 min read 17.3K   375  
By reading this trick, you will be able to update your database from DataGridView through external class instead of writing code on each page.

Introduction

In this post, I will show you a new trick which will be very helpful for beginner and intermediate .NET developers and learners.

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

Table to create

* 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:

Form Design

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

XML
<?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:

C#
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...

C#
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:

Result

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.

C#
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

  • 30th March, 2017: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)