Using a DataGrid

• Download source code - 132 KB

Introduction

This is a simple example to show you how to link and edit a data source through a DataGrid control.

What is a DataGrid

DataGrid is a data bound list control that displays the items from a data source in a table-like mode. The DataGrid control allows you to select, sort, add, delete, and edit these items.

How to Use a DataGrid

I think I have commented everything in the code; however, if I missed anything, please let me know.

//By Muammar Yacoob

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
//################# Required Library #################//
using System.Data.OleDb;
//####################################################//

namespace ez_Datagrid
{
    public partial class DG_Form : Form
    {
        //################# Needed Database Objects #################//
        OleDbConnection ConnectMe;
        DataSet ds;
        OleDbDataAdapter dap;
        //###########################################################//
        public DG_Form()
        {
            InitializeComponent();
        }

        private void btn_Connect_Click(object sender, EventArgs e)
        {
            //---------------- Connecting the database (mdb file) ------------------//

            //################## Creating the connection ###########################//
            ConnectMe = new OleDbConnection("Provider=Microsoft.Jet.OLEDB" + 
                        ".4.0;Data Source=" + txt_db_Location.Text);
            //######################################################################//
            try
            {
                //##### Connecting to the mdb file #####//
                ConnectMe.Open();
                //######################################//

                //###### Setting up buttons, text boxes and status bar ######//
                btn_Connect.Enabled = false;
                txt_db_Location.Enabled = false;
                btn_Fill.Enabled = true;
                txt_SQL.Enabled = true;
                status_Connection.Text = "Connected";
                //###########################################################//
            }
            catch (OleDbException)
            {
                MessageBox.Show("Cannot connect to " + txt_db_Location.Text);
             }

        private void btn_Fill_Click(object sender, EventArgs e)
        {
            //----------------------- Filling the datagrid -------------------------//
            try
            {
                //### Creating the sql command using the tables in the mdb file ###//
                OleDbCommand cmd = new OleDbCommand(txt_SQL.Text, ConnectMe);
                //#################################################################//

                //#### Creating the adabter according to the sql command ####//
                dap = new OleDbDataAdapter(cmd);
                //###########################################################//

                //#### Creating the dataset ####//
                ds = new DataSet();
                //##############################//

                //##### Filling the dataset #####//
                dap.Fill(ds);
                //###############################//

                //################ Filling the datagrid control ##################//
                //################ with the first & only table  ##############//
                //################ retrieved by the connection  ##################//
                dataGrid1.DataSource = ds.Tables[0];
                //################################################################//

                //########## I dont have to explain that again, do I?? ###########//
                btn_Fill.Text = "Refresh";
                btn_Update.Enabled = true;
                //################################################################//
            }
            catch (OleDbException)
            {
                MessageBox.Show("Please make sure you type in a correct SQL statement");
            }
        }

        private void btn_Update_Click(object sender, EventArgs e)
        {
            //-------------------------- Updating data ----------------------------//

            //############### Testing changes made to the dataset #################//
            if (ds.GetChanges() != null)
            //#####################################################################//
            {
            //########## generates a command that is used to #########//
            //########### merge changes made to the dataset ##########//
                OleDbCommandBuilder builder = new  OleDbCommandBuilder(dap);
            //########################################################//

                try
                {
                    //########### Getting changes made to the dataset ##########// 
                    //############# applay them against the adapter ############//
                    MessageBox.Show(dap.Update(ds.GetChanges()) + 
                                    " row(s) updated");
                    //##########################################################//

                    //######### Confirming changes made to the dataset ##########//
                    //############ and resetting it for new changes #############//
                    ds.AcceptChanges();
                    //###########################################################//
                }
                catch (InvalidOperationException)
                {
                    MessageBox.Show("Error retrieving data");
                    //######### Rejecting changes made to the dataset ########//
                    //############# through the datagrid control #############//
                    ds.RejectChanges();
                    //########################################################//
                }
            }
            else
            {
                MessageBox.Show("nothing to update", "Ez Datagrid", 
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
    }
}

Points of Interest

Notice that you will get an error message if you try to connect to a table that doesn't contain a primary key.