C# – DataGridView as database frontend

Yesterday I followed a thread on Codepro.vn asking about working with DataGridView. There are 2 questions at this thread:

1. How to get the index of current row of DataGridView?

2. How to add,edit,delete directly on DataGridView?

So I would like to write a small tutorial to answer these questions. To get the index of current row in DataGridView is very simple, we just access the property CurrentCellAddress of DataGridView with the following code:

int nCurrentRow = dataGridView1.CurrentCellAddress.Y;

To answer the second question I would like to “steal” an example of the book “Datenbank-Programmierung mit Visual C# 2005” to explain more about how DataGridView works. The complete source code you can download as following link “DataGridView as database frontend“. For this example, we prepare a Access database with following structure

Then we make a binding connection between the DataGridView and database. When the connection was built, each change on DataGridView will be updated into data source.

BindingSource bs = new BindingSource(); // Create a binding connection
 try
 {
 m_dtExpenditure = CData.getExpenditure(txtDateFrom.Text, txtDateTo.Text); //Get data source
 if (m_dtExpenditure == null)
 {
 dgvExpenditure.DataSource = null;
 tsslStatus.Text = "Access denied";
 }
 else
 {
 bs.DataSource = m_dtExpenditure;
 dgvExpenditure.Columns.Clear();
 dgvExpenditure.DataSource = bs; // Build connection between DataGridView and data source

...

To update the database, we just need to get the changes, update it in the data source and our variable.

DataTable dtChanges = m_dtExpenditure.GetChanges();
 if (dtChanges != null)
 {
 CData.setExpenditure(ref dtChanges);
 m_dtExpenditure.Merge(dtChanges);
 dtChanges.AcceptChanges();
 tsslStatus.Text = "All changes were saved";
 }
 else
 {
 tsslStatus.Text = "No change to save";
 }

I think this example is pretty easy to understand. To load and edit the data source I use the class DbProviderFactory to manage the connection, command and so on.  There are only two errors that I met during implementing this example. They are:

1. The ‘Microsoft.Jet.OLEDB.4.0’ provider is not registered on the local machine : To solve this error we just change the platform target into 0x86 as following image

2. “Concurrency violation: the UpdateCommand affected 0 of the expected 1 records” . After spending half hour to fix this error I found out that I may be use a keyword as the name of the column. I used “No, DateAndTime, Net ,Gst ,Notice”. Really don’t know which of them caused the error above. I just changed all of them and everything worked.

This example I have extracted from book “Datenbank Programmierung mit Visual C# 2005”.