Updatable and reusable DataGridView for Oracle or SQL Server

• Download source code - 27.4 KB

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;
        }
    }
}