insert update delete in datagridview in c# windows application
  • Hi i am sharing Insert, Update and Delete functionality for datagridview using query in C# keep in mind i am opening connection on every button its just for copy paste or beginner its better that you keep connection and other things on form load or in separate class.


    Source code is also attached you can also download it.
    First create table in database with name of tblSale and my database name is dbTest2.

    CREATE TABLE [dbo].[tblSale](
    [SaleNo] [int] NULL,
    [ProductCode] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Qty] [int] NULL,
    [Price] [decimal](18, 2) NULL

    image



    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;


    namespace WindowsFormsApplication5
    {
    public partial class Form1 : Form
    {
    public Form1()
    {
    InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
    dataGridView1.AutoGenerateColumns = false;
    }

    private void btnSearch_Click(object sender, EventArgs e)
    {
    SqlConnection con = new SqlConnection(@"Data Source=.\SqlExpress;Initial Catalog=dbTest2;Integrated Security=True");
    DataTable dt = new DataTable();
    SqlDataAdapter sda = new SqlDataAdapter("select * from tblSale where SaleNo='" + txtSearch.Text + "'", con);
    sda.Fill(dt);
    dataGridView1.DataSource = dt;
    }

    private void btnInsert_Click(object sender, EventArgs e)
    {
    SqlConnection objCon = new SqlConnection(@"Data Source=.\SqlExpress;Initial Catalog=dbTest2;Integrated Security=True");

    SqlCommand objCmd = new SqlCommand();
    objCmd.Connection = objCon;

    string query = "";

    for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
    {
    query = query + "insert into tblSale values(@SaleNo,@ProductCode,@Qty,@Price) ";
    query = query.Replace("@SaleNo", "'" + txtSearch.Text + "'");
    query = query.Replace("@ProductCode", "'" + dataGridView1.Rows[i].Cells["ProductCode"].Value.ToString() + "'");
    query = query.Replace("@Qty", "'" + dataGridView1.Rows[i].Cells["Qty"].Value.ToString() + "'");
    query = query.Replace("@Price", "'" + dataGridView1.Rows[i].Cells["Price"].Value.ToString() + "'");

    }

    objCmd.CommandText = query;

    objCon.Open();
    int j = objCmd.ExecuteNonQuery();
    if (j > 0)
    {
    MessageBox.Show("Record Inserted","www.codingresolved.com");
    dataGridView1.Rows.Clear();
    }
    objCon.Close();
    }

    private void btnUpdate_Click(object sender, EventArgs e)
    {
    SqlConnection objCon = new SqlConnection(@"Data Source=.\SqlExpress;Initial Catalog=dbTest2;Integrated Security=True");

    SqlCommand objCmd = new SqlCommand();
    objCmd.Connection = objCon;

    string query = "delete from tblSale where SaleNo='" + txtSearch.Text + "'";

    for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
    {
    query = query + " insert into tblSale values(@SaleNo,@ProductCode,@Qty,@Price) ";
    query = query.Replace("@SaleNo", "'" + txtSearch.Text + "'");
    query = query.Replace("@ProductCode", "'" + dataGridView1.Rows[i].Cells["ProductCode"].Value.ToString() + "'");
    query = query.Replace("@Qty", "'" + dataGridView1.Rows[i].Cells["Qty"].Value.ToString() + "'");
    query = query.Replace("@Price", "'" + dataGridView1.Rows[i].Cells["Price"].Value.ToString() + "'");

    }

    objCmd.CommandText = query;

    objCon.Open();
    int j = objCmd.ExecuteNonQuery();
    if (j > 0)
    {
    MessageBox.Show("Record Updated", "www.codingresolved.com");
    dataGridView1.DataSource = null;
    }
    objCon.Close();
    }

    private void btnDelete_Click(object sender, EventArgs e)
    {
    SqlConnection objCon = new SqlConnection(@"Data Source=.\SqlExpress;Initial Catalog=dbTest2;Integrated Security=True");

    SqlCommand objCmd = new SqlCommand();
    objCmd.Connection = objCon;

    string query = "delete from tblSale where SaleNo='" + txtSearch.Text + "'";

    objCmd.CommandText = query;

    objCon.Open();
    int j = objCmd.ExecuteNonQuery();
    if (j > 0)
    {
    MessageBox.Show("Record Deleted", "www.codingresolved.com");
    dataGridView1.DataSource = null;
    }
    objCon.Close();
    }
    }
    }
    3.jpg
    439 x 339 - 32K
    WindowsFormsApplication5.zip
    45K

Howdy, Stranger!

It looks like you're new here. If you want to get involved, or you want to Ask a new Question, Please Login or Create a new Account by Clicking below

Login with Facebook

Tagged

Popular Posts of the Week

Top Posters Weekly

Optimum Creative