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
* 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:
="1.0"="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
- 30th March, 2017: Initial version