How to show MS Access database table in dataGridView

Search other websites:

Task

Suppose we have a database created in Microsoft Access program.
The filename of database is “mydb.mdb“. The database file located on the drive by the following path:

C:\Programs\C_Sharp\Train_01\WindowsFormsApplication1\mydb.mdb

Database consists several tables, one of them is named “Order“.

The problem is that using the C# language make a database connection and show the table “Order” on the main form of application.

Application must be developed as Windows Forms Application.

The general view of the database tables and connections between them is shown on fig. 1.

Figure 1. Connections between database tables

Instructions

1. Creating an application of Windows Forms Application type.

Run MS Visual Studio. Creating application as a Windows Forms Application.

Source code of the main form (file “form1.cs“) is following:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace WindowsFormsApplication1
{
   public partial class Form1 : Form
   {
       public Form1()
       {
           InitializeComponent();
       }
   }
}

As you can see in the namespace WindowsFormsApplication1 is only one constructor of the main form, that calls InitializeComponent() function.

⇑

2. Connecting to database. Reading the string of connection with name of “ConnectionString“.

Make a connection our application to the MS Access database.

As a result we receive the connection string to database (ConnectionString). This string will be used in our application later.

To get the correct text of string of database connection we should select database name in the panel Server Explorer (mydb.mdb) and in the “Properties” window we need read (or copy) the value of the “ConnectionString” property (Fig. 2). Note that the backslash ‘\’ in a string in C# should be replaced by ‘\\’ (two slashes) according to the syntax of the language in program code (see the item 4.1).

Fig. 2. Reading of the ConnectionString property

⇑

3. Adding a component of dataGridView type.

Add dataGridView component on the form. This is a component-table, that will be show our “Order” table from database. As result we get the variable with the name of dataGridView1.

Fig. 3. A component “DataGridView” at the Toolbox palette

The placing of component dataGridView on the form is shown in figure 4.

Fig. 4. A dataGridView component on the main form

⇑

4. Changing a program code.

4.1. Adding SQL-query variables and connection string to database.

Activate a text of “Form1.cs” unit (the main form). In the program code of form need to input additional variables CmdText and ConnString.

The CmdText variable includes a SQL-query text for output all records of the “Order” table.

The ConnString variable represents a string of connection to the database (see item 2). General view of program code of main form is the following:

public partial class Form1 : Form
{
    public string CmdText = "SELECT * FROM [Order]";
    public string ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\\Programs\\C_Sharp\\WindowsFormsApplication1\\mydb.mdb";
    public Form1()
    {
        InitializeComponent();
    }
}

⇑

4.2. Connecting the namespace OleDb.

In Microsoft Visual Studio interaction with the file of Microsoft Access database carried out by means OLE DB provider or ODBC. OLE DB provider ensures the access to data, which are located in any data repository, if they support the classical protocol OLE DB based on COM technology. This provider contains types, which are defined in the namespace System.Data.OleDb.

In the next steps we will use methods from this namespace. Thus, in the beginning of the file Form1.cs after the line

using System.Windows.Forms;

we add the string of connection of namespace OleDb:

using System.Data.OleDb;

⇑

4.3. Creating an OleDbDataAdapter type object.

In the constructor of form after calling

InitializeComponent();

add a string of creating a OleDbDataAdapter type object.

OleDbDataAdapter dA = new OleDbDataAdapter(CmdText, ConnString);

Object of OleDbDataAdapter type sends datasets into caller process and returns it backward. Data adapters consists a set of four internal command objects. Data adapters contains a set of four internal command objects. This is a commands of reading, changing, inserting and deleting information. As we see in program code, the constructor of object gets both of string of SQL-query and connection string to database as a input parameters. Thus, the data adapter object already connected to our database.

⇑

4.4. Creating an object of DataSet data set.

After creating data adapter (OleDbDataAdapter) need to create object of DataSet type:

DataSet ds = new DataSet();

The data set is intermediate buffer for data that can be displayed. The dataset is a convenient mechanism for reading data and updating data and encapsulates a set of tables and relations between them.

⇑

4.5. Filling the table “Order” based on SQL-query.

The next command is filling data set (variable ds) by values of records from database on SQL-query, that is contained in the data adapter dA. The method Fill() realizes it:

dA.Fill(ds, "[Order]");

⇑

4.6. Visualisation of data in dataGridView1.

At the moment, data of “Order” table are reading from object ds (the type of DataSet), that is a data set.

Currently, data from a table “Orders” are placed in the ds object (the DataSet type), which is a set of data.

To display them need to DataSource property of dataGridView1 component was referred to the first table (we have one table) dataset ds. Program code is the following:

dataGridView1.DataSource = ds.Tables[0].DefaultView;

⇑

5. All program code.

All code of class of main form is the following:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
namespace WindowsFormsApplication1
{
  public partial class Form1 : Form
  {
    public string CmdText = "SELECT * FROM [Order]";
    public string ConnString = 
    "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\\Programs\\C_Sharp\\WindowsFormsApplication1\\mydb.mdb";
    public Form1()
    {
      InitializeComponent();
      OleDbDataAdapter dataAdapter = new OleDbDataAdapter(CmdText, ConnString);
      // creating a DataSet object
      DataSet ds = new DataSet();
      // filling table Order  
      dataAdapter.Fill(ds, "[Order]");
      dataGridView1.DataSource = ds.Tables[0].DefaultView;
    }
  }
}

After that we can run our application and view the result on the screen.

The result of running the application is shown in Figure 5.

Fig. 5. The result of running the application

⇑

6. Scheme of interaction.

The scheme of interaction between the objects is shown on fig. 6.

Fig. 6. Scheme of interaction between the database objects

So we can display any table of database on the form. Conditions of data output from the database are set in SQL-query of CmdText variable.

For example, if variable CmdText contains the string:

CmdText = "SELECT * FROM [Order] WHERE [Name] LIKE 'І%'";

as the result from the database will be output those records that begin with the letter ‘I‘.

BestProg

C#. Windows Forms. How to show MS Access database table in dataGridView