In this article I perform some simple operations using a DataGridView CheckBox, Combobox and Button. The following is a summary of what this articloe does:
- We are creating the database and table in SQL.
- Create a class for the SQL connection.
- Create a class to get data and execute a query against the database table.
- Save, Update and Delete in a DataGridView on button click.
- Delete a row from the database in the DataGridView's current row button click.
Here we create a database for our project.
![Figure 1.jpg]()
Then create the ConMgr Class for the database connection. This is a static class that returns the SQL connection. The Code for ConMgr.cs is:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
namespace dataGridCorner
{
class ConMgr
{
public static SqlConnection GetConnection()
{
SqlConnection con = new SqlConnection();
con.ConnectionString = @"Data Source=.\\SQLEXPRESS;Initial Catalog=CORNER_DB;Integrated Security=True;";
return con;
}
}
}
Now we are going to design a form for the project. This Project contains only one form. The main control of the form is a DataGridView and three buttons.
Using the show button we are going to show all records from the table. Using the save button we are performing save, update and delete operations.
After that we will create a method named "GridColumn". This method contains code for the DataGridView column and design. The code for this method is given below.
Creating checkbox column for DataGridView:
DataGridViewCheckBoxColumn chk = new ataGridViewCheckBoxColumn();
chk.HeaderText = "Select";
chk.Name = "chk";
chk.AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
chk.FlatStyle = FlatStyle.Standard;
chk.ThreeState = false;
Creaeting ComboBox Column for DataGridView:
DataGridViewComboBoxColumn combo = new DataGridViewComboBoxColumn();
combo.HeaderText = "Choose";
combo.FlatStyle = FlatStyle.Standard;
Adding Item to ComBoxColumn:
combo.Items.Add("SOFTWERE DEVELOPER");
combo.Items.Add("PROGRAMMER");
combo.Items.Add("SECURITY EXPRERT");
combo.Items.Add("DATABASE ADMINISTRATOR");
Adding button Column for DataGridView:
DataGridViewButtonColumn button = new DataGridViewButtonColumn();
button.HeaderText = "Delete";
button.FlatStyle = FlatStyle.Popup;
button.DefaultCellStyle.BackColor = Color.Red;
button.DefaultCellStyle.ForeColor = Color.White;
Adding All Column to DataGridView:
dataGridView1.Columns.Insert(0, chk);
dataGridView1.Columns.Add("", "Name");
dataGridView1.Columns.Add("", "Country");
dataGridView1.Columns.Insert(3, combo);
dataGridView1.Columns.Insert(4, button);
dataGridView1.Columns.Add("", "ID");
Hide ID Column:
dataGridView1.Columns[5].Visible = false;
Assign Width For Columns:
dataGridView1.Columns[0].Width = 70;
dataGridView1.Columns[1].Width = 205;
dataGridView1.Columns[2].Width = 120;
dataGridView1.Columns[3].Width = 190;
dataGridView1.Columns[4].Width = 90;
Color and Font Design For DataGridView:
dataGridView1.DefaultCellStyle.Font = new Font("Calibri", 10.25f, FontStyle.Regular);
dataGridView1.ColumnHeadersDefaultCellStyle.Font = new Font("Calibri", 11, FontStyle.Regular);
dataGridView1.ColumnHeadersDefaultCellStyle.BackColor = Color.BurlyWood;
dataGridView1.EnableHeadersVisualStyles = false;
dataGridView1.RowHeadersVisible = false;
dataGridView1.BackgroundColor = Color.White;
After creating this method our form looks like the following (design of the form):
![Figure 2.jpg]()
Now we are going to create a class named "QueryClass", This class contains two methods, "GetData" and "ExcuteQuery". The GetData method takes the SQL Query and returns the dataset. In the dataset there is a table name "s" and the second method () is excecuting the query aginst the SQL database. This class also uses the GetConnection method of the conMgr class (the connection class already created).
The code of the "Queryclass" class:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data;
namespace dataGridCorner
{
class QueryClass
{
SqlConnection con = ConMgr.GetConnection();
DataSet ds = new DataSet();
public DataSet GetData(string Query)
{
try
{
if (ds.Tables.Count > 0)
{
ds.Tables.Clear();
}
SqlDataAdapter adp = new SqlDataAdapter(Query, con);
adp.Fill(ds, "s");
return ds;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString()); return ds;
}
}
public void ExcuteQuery(string Query)
{
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand(Query, con);
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ex) { MessageBox.Show(ex.ToString()); }
}
}
}
Now we are going to save and update the DataGridView.
The DataGridView save and update code:
//COUNT ALL ROWS
int rowCount = dataGridView1.Rows.Count;
for (int i = 0; i < rowCount; i++)
{
//CHECK ROW IS NOT SELECTED
if (Convert.ToBoolean(dataGridView1.Rows[i].Cells[0].Value) == false)
{
//ROW MUST HAVE STRING VALUE(NOT NULL OR EMPTY)
if (dataGridView1.Rows[i].Cells[1].Value != null && string.IsNullOrEmpty(dataGridView1.Rows[i].Cells[1].Value.ToString()))
{
//IF ID IS NULL THAN SAVE
if (dataGridView1.Rows[i].Cells[5].Value == null)
{
sSql = "INSERT INTO REG_USER VALUES('" + dataGridView1.Rows[i].Cells[1].Value + "','" + dataGridView1.Rows[i].Cells[2].Value + "','" + dataGridView1.Rows[i].Cells[3].Value + "')";
objQuery.ExcuteQuery(sSql);
}
//IF ID IS NOT NULL THAN UPDATE
else
{
sSql = "UPDATE REG_USER SET NAME='" + dataGridView1.Rows[i].Cells[1].Value + "' ,COUNTRY='" + dataGridView1.Rows[i].Cells[2].Value + "', JOB_TITLE='" + dataGridView1.Rows[i].Cells[3].Value + "' WHERE ID='" + dataGridView1.Rows[i].Cells[5].Value + "'";
objQuery.ExcuteQuery(sSql);
}
}
}
}
Here, delete the row from the database table if the checkbox is checked in DataGridView. All checked rows are deleted upon a Save button click.
![Figure 3.jpg]()
The code for DataGridView Checked Row Delete Row Delete:
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
//delete the row form database on gridview buttion click
if (e.ColumnIndex == 4)
{
//check id value for current row
if (dataGridView1.CurrentRow.Cells[5].Value != null)
{
//sql query for delete row
sSql = "DELETE FROM REG_USER WHERE ID='" + dataGridView1.CurrentRow.Cells[5].Value.ToString() + "'";
//calling the method of QueryClass
objQuery.ExcuteQuery(sSql);
MessageBox.Show("Record Delete From The Database."
, "Record Deleted.", MessageBoxButtons.OK, MessageBoxIcon.Information);
//fill the data again
FillGrid();
}
}
}
Thank You.