In this tutorial, we are going to learn a code to retrieve data from the table into a datagridview in CSharp. Datagridview control is a powerful way to display data in tabular format.
And you can create a control of datagridview has to show the read-only amount of data and show the editable amount of data by clicking the items to easily control and update it.
Let's started with a few steps and easy to understand what is datagridview in CSharp.
In datagridview control, we have to connect with SQL Server and Microsoft visual studio and get the select the database table to show in the datagridview with some codes.
First, we have to create the database and table, I named it as a phone for table and database name blogger.
Here is the code,
create table phone
(
id int primary key identity(1,1),
firstname varchar(50) not null,
lastname varchar(50) not null,
mobileno varchar(25) not null,
emailid varchar(200) not null
);
second, create a Form design using four textboxes, buttons (use for insert, delete, update, Add) and drag and drop the DataGridView control from the toolbox.
In Microsoft, visual studio need to connect the database in a few steps and choose your data connection
Tools-> connect database connection -> Data Source(choose Microsoft SQL Server(SqlClient) -> finally click ok button.
If you need details of SQL server database connection just follow the above link.
SqlConnection con = new SqlConnection(@"Data Source=.\;Initial Catalog=blogger;Integrated Security=True");
You need to add a Csharp code and just double click on the button and create an insert data code and like below.
private void button2_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cm=new SqlCommand("INSERT INTO phone (firstname,lastname,mobileno,emailid)
VALUES('"+textBox1.Text+"','"+textBox2.Text+"','"+textBox3.Text+"',
'"+textBox4.Text+"')",con);
cm.ExecuteNonQuery();
con.Close();
MessageBox.Show("data insert successfully...!");
}
private void button3_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cm=new SqlCommand("DELETE FROM phone WHERE (mobileno='"+textBox3.Text+"')",con);
cm.ExecuteNonQuery();
con.Close();
MessageBox.Show("delete successfully...!");
display();
}
private void button4_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cm = new SqlCommand("UPDATE phone SET firstname='"+textBox1.Text+"',
lastname='"+textBox2.Text+"',mobileno='"+textBox3.Text+"',emailid='"+textBox4.Text+"'
WHERE (emailid='"+textBox4.Text+"')", con);
cm.ExecuteNonQuery();
con.Close();
MessageBox.Show("update successfully...!");
display();
}
In the datagridview control have style, text formate, background color and font.And need to help of display function to show the database table data in the datagridview.Here the use of the SqlDataAdapter to fetch the data and fill the datagridview.
void display() {
SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM phone",con);
System.Data.DataTable dt = new System.Data.DataTable();
sda.Fill(dt);
dataGridView1.Rows.Clear();
foreach(DataRow item in dt.Rows){
int n = dataGridView1.Rows.Add();
dataGridView1.Rows[n].Cells[0].Value = item[1].ToString();
dataGridView1.Rows[n].Cells[1].Value = item[2].ToString();
dataGridView1.Rows[n].Cells[2].Value = item[3].ToString();
dataGridView1.Rows[n].Cells[3].Value = item[4].ToString();
}
private void dataGridView1_MouseClick(object sender, MouseEventArgs e)
{
textBox1.Text = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
textBox2.Text = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
textBox3.Text = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
textBox4.Text = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
namespace tele
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
SqlConnection con = new SqlConnection(@"Data Source=.\;Initial Catalog=blogger;Integrated Security=True");
private void Form1_Load(object sender, EventArgs e)
{
display();
}
private void button1_Click(object sender, EventArgs e)
{
textBox1.Text = "";
textBox2.Clear();
textBox3.Text = "";
textBox4.Clear();
textBox1.Focus();
}
private void button2_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cm=new SqlCommand("INSERT INTO phone (firstname,lastname,mobileno,emailid)
VALUES('"+textBox1.Text+"','"+textBox2.Text+"','"+textBox3.Text+"',
'"+textBox4.Text+"')",con);
cm.ExecuteNonQuery();
con.Close();
MessageBox.Show("data insert successfully...!");
}
void display() {
SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM phone",con);
System.Data.DataTable dt = new System.Data.DataTable();
sda.Fill(dt);
dataGridView1.Rows.Clear();
foreach(DataRow item in dt.Rows){
int n = dataGridView1.Rows.Add();
dataGridView1.Rows[n].Cells[0].Value = item[1].ToString();
dataGridView1.Rows[n].Cells[1].Value = item[2].ToString();
dataGridView1.Rows[n].Cells[2].Value = item[3].ToString();
dataGridView1.Rows[n].Cells[3].Value = item[4].ToString();
}
}
private void dataGridView1_MouseClick(object sender, MouseEventArgs e)
{
textBox1.Text = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
textBox2.Text = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
textBox3.Text = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
textBox4.Text = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
}
private void button3_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cm=new SqlCommand("DELETE FROM phone WHERE (mobileno='"+textBox3.Text+"')",con);
cm.ExecuteNonQuery();
con.Close();
MessageBox.Show("delete successfully...!");
display();
}
private void button4_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cm = new SqlCommand("UPDATE phone SET firstname='"+textBox1.Text+"',
lastname='"+textBox2.Text+"',mobileno='"+textBox3.Text+"',emailid='"+textBox4.Text+"'
WHERE (emailid='"+textBox4.Text+"')", con);
cm.ExecuteNonQuery();
con.Close();
MessageBox.Show("update successfully...!");
display();
}
}
}
