C# SQLServer => DataGridView - INSERT SLECT UPDATE DELETE

June 8, 2017 Oclemy C# SQLServer, C# DataGridView 2 minutes, 49 seconds

This is a C# SQLServer DataGridView tutorial. We see how to insert data into sqlserver,retrieve that data,update it on button click and delete the given row in case the user clicks the delete button.  

Source Code

Below is the simple one class we are creating responsible for all our CRUD :  

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

namespace CS_DGVIEW_SQLSERVER
{
  public partial class Form1 : Form
  {
    static string conString = @"Data Source=(LOCAL)\SIRI;Initial Catalog=playersDB;Integrated Security=True;Pooling=False";
    SqlConnection con = new SqlConnection(conString);
    SqlDataAdapter adapter;
    SqlCommand cmd;
    DataTable dt = new DataTable();

    public Form1()
    {
      InitializeComponent();

      //PROPERTIES
      dataGridView1.ColumnCount = 3;
      dataGridView1.Columns[0].HeaderText = "ID";
      dataGridView1.Columns[1].HeaderText = "NAME";
      dataGridView1.Columns[2].HeaderText = "POSITION";

      //SLECTIONMODE
      dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
      dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;

    }

    //INSERT
    private void add(String name,String position)
    {
      //SQL STMT
      String sql = "INSERT INTO playersTB(name,position) VALUES(@PNAME,@POSITION)";
      cmd = new SqlCommand(sql, con);

      cmd.Parameters.AddWithValue("@PNAME", name);
        cmd.Parameters.AddWithValue("@POSITION", position);

        try
        {
          con.Open();

          if(cmd.ExecuteNonQuery()>0)
          {
            MessageBox.Show("Inserted");
          }
          con.Close();

          //REFRESH
          retrieve();

        }catch(Exception ex)
        {
          MessageBox.Show(ex.Message);
          con.Close();
        }
    }

    //RETRIEVE OR SELECT
    private void retrieve()
    {
      dataGridView1.Rows.Clear();
      //SQL STMT
      string sql = "SELECT * FROM playersTB";
      cmd = new SqlCommand(sql, con);

      //OPEN CON,RETRIEVE,FILL DGVIEW
      try
      {
        con.Open();

        adapter = new SqlDataAdapter(cmd);
        adapter.Fill(dt);

        //LOOP THRU DT
        foreach(DataRow row in dt.Rows)
        {
          dataGridView1.Rows.Add(row[0], row[1], row[2]);
        }

        con.Close();

        //CLEAR DT
        dt.Rows.Clear();

      }
      catch (Exception ex)
      {

        MessageBox.Show(ex.Message);
        con.Close();
      }
    }

    //UPDATE
    private void update(int id,string newName,string newPosition)
    {
      //SQL STMT
      String sql = "UPDATE playersTB SET name='" + newName + "',position='" + newPosition + "' WHERE id=" + id + "";
      cmd = new SqlCommand(sql, con);

      //OPEN CON,UPDATE,RETRIEVE DGVIEW
      try
      {
        con.Open();
        adapter = new SqlDataAdapter(cmd);

        adapter.UpdateCommand = con.CreateCommand();
        adapter.UpdateCommand.CommandText = sql;

        if(adapter.UpdateCommand.ExecuteNonQuery()>0)
        {
          nameTxt.Text = "";
          positionTxt.Text = "";
          MessageBox.Show("Successfully Updated");

        }

        con.Close();
        //RETRIEVE
        retrieve();
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.Message);
        con.Close();
      }
    }

    //DELETE
    private void delete(int id)
    {
      //SQL STMT
      string sql = "DELETE FROM playersTB WHERE id=" + id + "";
      cmd = new SqlCommand(sql, con);

      //'OPEN CON,EXECUTE DELETE,CLOSE CON
      try
      {
        con.Open();

        adapter = new SqlDataAdapter(cmd);

        adapter.DeleteCommand = con.CreateCommand();
        adapter.DeleteCommand.CommandText = sql;

        //PROMPT FOR CONFIRMATION
        if (MessageBox.Show("Sure ??", "DELETE", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) == DialogResult.OK)
        {
          if(cmd.ExecuteNonQuery()>0)
          {
            nameTxt.Text = "";
            positionTxt.Text = "";
            MessageBox.Show("Successfully deleted");
          }
        }

        con.Close();

        //REFRESH
        retrieve();
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.Message);
        con.Close();
      }

    }

    private void dataGridView1_MouseClick(object sender, MouseEventArgs e)
    {
      nameTxt.Text = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
      positionTxt.Text = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
    }

    private void addBtn_Click(object sender, EventArgs e)
    {
      add(nameTxt.Text, positionTxt.Text);
    }

    private void retrieveBtn_Click(object sender, EventArgs e)
    {
      retrieve();
    }

    private void updateBtn_Click(object sender, EventArgs e)
    {
      int id = Convert.ToInt16(dataGridView1.SelectedRows[0].Cells[0].Value.ToString());

      update(id,nameTxt.Text, positionTxt.Text);
    }

    private void deleteBtn_Click(object sender, EventArgs e)
    {
      int id = Convert.ToInt16(dataGridView1.SelectedRows[0].Cells[0].Value.ToString());

      delete(id);
    }

    private void clearbtn_Click(object sender, EventArgs e)
    {
      nameTxt.Text = "";
      positionTxt.Text = "";
      dataGridView1.Rows.Clear();
    }
  }
}

Conclusion

We saw how to insert,select,update and delete data to and from SQLServer database.We used SQlDataAdapter as our adapter given we are working with SQL Server.

Best Regards.

Comments