C# ComboBox SQLServer – INSERT SELECT UPDATE DELETE

So here we how to perform all the CRUD operations against SQlServer database.We shall insert data,select data and fill combobox,update the selected data and finally delete data.

First the user selects the given data,for instance for editing then the data gets set into the textbox and the user can then edit it.

 

Here's the source code.

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_COMBOBOX_SSERVER
{
  public partial class Form1 : Form
  {

    static String conString = @"Data Source=(LOCAL)SIRI;Initial Catalog=firstdb;Integrated Security=True;Pooling=False";
    SqlConnection con = new SqlConnection(conString);
    SqlDataAdapter adapter;
    SqlCommand cmd;
    DataTable dt = new DataTable();

    public Form1()
    {
      InitializeComponent();
    }

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

      //ADD PARAMETERS
      cmd.Parameters.AddWithValue("@PNAME", name);

      try
      {
        con.Open();

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

        con.Close();

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

    //RETRIEVE
    private void  retrieve()
    {
      comboBox1.Items.Clear();

      //SQL STMT
      String sql = "SELECT * FROM firstTB";
      cmd = new SqlCommand(sql, con);

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

        adapter = new SqlDataAdapter(cmd);

        adapter.Fill(dt);

        //LOOP THRU DT
        foreach (DataRow row in dt.Rows)
        {
          comboBox1.Items.Add(row[1].ToString());
        }

        con.Close();

        //CLEAR DT
        dt.Rows.Clear();
      }
      catch (Exception ex)
      {

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

    }

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

      //OPEN CON,UPDATE,RETRIEVE COMBOBOX
      try
      {
        con.Open();

        adapter = new SqlDataAdapter(cmd);

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

        //EXECUTE UPDATE
        if(adapter.UpdateCommand.ExecuteNonQuery()>0)
        {
          nameTxt.Text = "";
          MessageBox.Show("Successfully Updated");
        }

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

    //DELETE
    private void delete(String id)
    {
      //SQLSTMT
      string sql = "DELETE FROM firstTB WHERE name='" + 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 = "";
            MessageBox.Show("Successfully deleted");
          }
        }

        con.Close();

        //REFRESH
        retrieve();
      }catch(Exception ex)
      {
        MessageBox.Show(ex.Message);
        con.Close();
      }
    }
    //CLEAR
    private void clear()
    {
      comboBox1.Items.Clear();
      nameTxt.Text = "";
    }

    private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
    {
      nameTxt.Text = comboBox1.SelectedItem.ToString();
    }

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

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

    private void updateBtn_Click(object sender, EventArgs e)
    {
      String id = comboBox1.SelectedItem.ToString();
      String newName = nameTxt.Text;

      update(id, newName);

      comboBox1.Text = newName;
    }

    private void deleteBtn_Click(object sender, EventArgs e)
    {
      String id = comboBox1.SelectedItem.ToString();
      delete(id);

      comboBox1.Text = "";
    }

    private void clearBtn_Click(object sender, EventArgs e)
    {
      clear();
    }

  }
}

Cheers.