Click here to Skip to main content
Click here to Skip to main content

Updatable and reusable DataGridView for Oracle or SQL Server

, 12 Sep 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
Reusable form containing an updatable DataGrid. Pass a connection string and a Select statement to the form, and you can add/update/delete records in a table on an Oracle or SQL Server database.

Screenshot - screenshot.jpg

Introduction

I was looking for ways to use a grid to display and modify Oracle and SQL Server tables as easily as it is with MS Access. I think this is even easier. You can add/update/delete records on any table only by passing the connection string and the select * from [table] statement. Note: The table must have a primary key.

Using the code

This is how you call the updatable grid windows. This will display the table in a window ready to be updated. The two boolean parameters are there to specify if you want to allow insert and delete.

// Oracle
frmGridUpdateOracle fOra = new frmGridUpdateOracle("Data Source=mydsn;" + 
                           "User Id=myid;Password=mypass;Integrated Security=no;", 
                           "select * from tfbcomptes", true, true);
fOra.ShowDialog();

// Sql Server
frmGridUpdateSql fSql = new frmGridUpdateSql("Data Source=gemini12;" + 
                        "Initial Catalog=Northwind;Integrated Security=SSPI;", 
                        "select * from customers2", true, true);
fSql.ShowDialog();

//-----------------------------------------------------------------------------
// Below is the code for the 2 forms included in the zip file
// Note: The code in the forms for Oracle and Sql are almost identical
// only the "using" clauses and the data objects are different.


/////////////////////////////////////////////////////////////////////////////
// SQL SERVER: Here is the entire code in the form for updating an Sql Server
/////////////////////////////////////////////////////////////////////////////

using System;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace UpdateGrid
{
    public partial class frmGridUpdateSql : Form
    {
        private SqlConnection connection;
        private SqlCommand command;
        private SqlDataAdapter adapter;
        private SqlCommandBuilder builder;
        private DataSet ds;
        private DataTable userTable;
        private bool mAllowInsert;
        private bool mAllowDelete;
        private bool IsDirty;

        public frmGridUpdateSql(string connectionString, string sqlQuery, 
                                bool AllowInsert, bool AllowDelete)
        {
            InitializeComponent();
            connection = new SqlConnection(connectionString);
            command = new SqlCommand(sqlQuery, connection);
            adapter = new SqlDataAdapter(command);
            builder = new SqlCommandBuilder(adapter);
            ds = new DataSet();
            adapter.Fill(ds);
            userTable = ds.Tables[0];

            userDataGridView.AllowUserToAddRows = mAllowInsert = AllowInsert;
            userDataGridView.AllowUserToDeleteRows = mAllowDelete = AllowDelete;
            btnDelete.Enabled = AllowDelete;
        }

        private void frmGridUpdateSql_Load(object sender, EventArgs e)
        {
            userDataGridView.DataSource = userTable.DefaultView;
            lblRowCount.Text = "Number of records: " + 
                               userTable.Rows.Count.ToString();
            userDataGridView.AllowUserToResizeColumns = true;
            if (userTable.Rows.Count == 0)
            {
                btnDelete.Enabled = false;
                btnUpdate.Enabled = false;
            }
        }

        private void Update()
        {
            try
            {
                connection.Open();
                adapter.Update(userTable);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                connection.Close();
            }
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            Update();
        }

        private void Delete()
        {
            if (MessageBox.Show("Do you really want to delete the selected record(s)?",
                   "Delete records", MessageBoxButtons.YesNo,
                   MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2, 0, false)
                   == DialogResult.Yes)
            {
                try
                {
                    connection.Open();
                    int cnt = userDataGridView.SelectedRows.Count;
                    for (int i = 0; i < cnt; i++)
                    {
                        if (this.userDataGridView.SelectedRows.Count > 0 &&
                            this.userDataGridView.SelectedRows[0].Index !=
                            this.userDataGridView.Rows.Count - 1)
                        {
                            this.userDataGridView.Rows.RemoveAt(
                               this.userDataGridView.SelectedRows[0].Index);
                        }
                    }

                    adapter.Update(userTable);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                finally
                {
                    connection.Close();
                }
            }
            if (userTable.Rows.Count == 0)
            {
                btnUpdate.Enabled = false;
                if (mAllowDelete) btnDelete.Enabled = false;
            }
            IsDirty = true;
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            Delete();
        }

        private void userDataGridView_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Delete) Delete();
            e.Handled = true; // Cancel the event (sinon ça supprime la ligne
                              // du grid alors qu'on le fait avec Delete())
        }

        private void frmGridUpdateSql_Resize(object sender, EventArgs e)
        {
            int ButtonTop = this.Height - 48 - 24;

            btnUpdate.Left = this.Width - 112;
            btnUpdate.Top = ButtonTop;

            btnDelete.Left = this.Width - 214;
            btnDelete.Top = ButtonTop;

            userDataGridView.Width = this.Width - 24;
            userDataGridView.Height = this.Height - 56 - 32;

            lblRowCount.Top = ButtonTop;
            lblRowCount.Left = userDataGridView.Left;
        }

        private void userDataGridView_UserAddedRow(object sender, 
                     DataGridViewRowEventArgs e)
        {
            if (userTable.Rows.Count == 0)
            {
                btnUpdate.Enabled = true;
                if (mAllowDelete) btnDelete.Enabled = true;
            }
        }

        private void frmGridUpdateSql_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (IsDirty)
                if (MessageBox.Show("Do you want to save changes?", this.Text, 
                    MessageBoxButtons.YesNo, MessageBoxIcon.Warning, 
                    MessageBoxDefaultButton.Button2, 0, false) == DialogResult.Yes)
                Update(); 
        }

        private void userDataGridView_CellValueChanged(object sender, 
                                      DataGridViewCellEventArgs e)
        {
            IsDirty = true;
        }
    }
}

///////////////////////////////////////////////////////////////////////////
// ORACLE: Here is the entire code in the form for updating an Oracle Table
///////////////////////////////////////////////////////////////////////////

using System;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.OracleClient;

namespace UpdateGrid
{
    public partial class frmGridUpdateOracle : Form
    {
        private OracleConnection connection;
        private OracleCommand command;
        private OracleDataAdapter adapter;
        private OracleCommandBuilder builder;
        private DataSet ds;
        private DataTable userTable;
        private bool mAllowInsert;
        private bool mAllowDelete;
        private bool IsDirty = false;

        public frmGridUpdateOracle(string connectionString, string sqlQuery, 
                                   bool AllowInsert, bool AllowDelete)
        {
            InitializeComponent();
            connection = new OracleConnection(connectionString);
            command = new OracleCommand(sqlQuery, connection);
            adapter = new OracleDataAdapter(command);
            builder = new OracleCommandBuilder(adapter);
            ds = new DataSet();
            adapter.Fill(ds);
            userTable = ds.Tables[0];

            userDataGridView.AllowUserToAddRows = mAllowInsert = AllowInsert;
            userDataGridView.AllowUserToDeleteRows = mAllowDelete = AllowDelete;
            btnDelete.Enabled = AllowDelete;
        }

        private void frmGridUpdateSql_Load(object sender, EventArgs e)
        {
            userDataGridView.DataSource = userTable.DefaultView;
            lblRowCount.Text = "Number of records: " + 
                               userTable.Rows.Count.ToString();
            userDataGridView.AllowUserToResizeColumns = true;
            if (userTable.Rows.Count == 0)
            {
                btnDelete.Enabled = false;
                btnUpdate.Enabled = false;
            }
        }

        private void Update()
        {
            try
            {
                connection.Open();
                adapter.Update(userTable);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                connection.Close();
            }
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            Update();
        }

        private void Delete()
        {
            if (MessageBox.Show("Do you really want to delete the selected record(s)?",
                   "Delete records", MessageBoxButtons.YesNo,
                   MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2, 0, false)
                   == DialogResult.Yes)
            {
                try
                {
                    connection.Open();
                    int cnt = userDataGridView.SelectedRows.Count;
                    for (int i = 0; i < cnt; i++)
                    {
                        if (this.userDataGridView.SelectedRows.Count > 0 &&
                            this.userDataGridView.SelectedRows[0].Index !=
                            this.userDataGridView.Rows.Count - 1)
                        {
                            this.userDataGridView.Rows.RemoveAt(
                               this.userDataGridView.SelectedRows[0].Index);
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                finally
                {
                    connection.Close();
                }
            }
            if (userTable.Rows.Count == 0)
            {
                btnUpdate.Enabled = false;
                if (mAllowDelete) btnDelete.Enabled = false;
            }
            IsDirty = true;
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            Delete();
        }

        private void userDataGridView_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Delete) Delete();
            e.Handled = true; // Cancel the event (sinon ça supprime la
                              // ligne du grid alors qu'on le fait avec Delete())
        }

        private void frmGridUpdateSql_Resize(object sender, EventArgs e)
        {
            int ButtonTop = this.Height - 48 - 24;

            btnUpdate.Left = this.Width - 112;
            btnUpdate.Top = ButtonTop;

            btnDelete.Left = this.Width - 214;
            btnDelete.Top = ButtonTop;

            userDataGridView.Width = this.Width - 24;
            userDataGridView.Height = this.Height - 56 - 32;

            lblRowCount.Top = ButtonTop;
            lblRowCount.Left = userDataGridView.Left;
        }
        private void userDataGridView_UserAddedRow(object sender, 
                                      DataGridViewRowEventArgs e)
        {
            if (userTable.Rows.Count == 0)
            {
                btnUpdate.Enabled = true;
                if (mAllowDelete) btnDelete.Enabled = true;
            }
        }

        private void frmGridUpdateOracle_FormClosing(object sender, 
                                         FormClosingEventArgs e)
        {
            if (IsDirty)
                if (MessageBox.Show("Do you want to save changes?", this.Text, 
                                    MessageBoxButtons.YesNo, MessageBoxIcon.Warning, 
                                    MessageBoxDefaultButton.Button2, 
                                    0, false) == DialogResult.Yes)
                        Update(); 
        }

        private void userDataGridView_CellValueChanged(object sender, 
                                      DataGridViewCellEventArgs e)
        {
            IsDirty = true;
        }
    }
}

License

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

Share

About the Author

gultron

Canada Canada
No Biography provided

Comments and Discussions

 
QuestionHow to make it compatible in order to use it in Visual Basic .Net (Forms) PinmemberMember 311870920-Jun-14 2:01 
GeneralThanks a lot! PinmemberShumar Oleg17-Nov-09 14:10 
GeneralQuestion PinmemberStephen Noronha13-Jun-08 3:38 
Generalgreat thank you! Pinmemberqingtianyu913-Nov-07 3:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150129.1 | Last Updated 12 Sep 2007
Article Copyright 2007 by gultron
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid