C# SQLServer => ListView – INSERT SLECT UPDATE DELETE

C# SQLServer ListView crud tutorial. We are here with C# SQLServer tutorial once more.We want to explore how to insert,select,update and delete data to and from our SQLServer database.

First we insert from our TextBoxes,we set the selected ListView items to TextBoxes and edit them.Then we update when update button is clicked.Lastly we delete the selected row when delete button is clicked.

Source Code

The source code is below :

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_LVIEW_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);
    SqlCommand cmd;
    SqlDataAdapter adapter;
    DataTable dt = new DataTable();

    public Form1()
    {
      InitializeComponent();

      //COLUMNS
      listView1.Columns.Add("ID", 70);
      listView1.Columns.Add("Name", 150);
      listView1.Columns.Add("Position", 150);

      //PROPERTIES
      listView1.View = View.Details;
      listView1.FullRowSelect = true;
    }

    private void populateLV(String id, String name, String position)
    {
      String[] row = { id, name, position };

      listView1.Items.Add(new ListViewItem(row));
    }

    //INSERTING
    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();

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

    //SELECT
    private void retrieve()
    {
      listView1.Items.Clear();

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

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

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

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

        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 LISTVIEW
      try
      {
        con.Open();

        adapter = new SqlDataAdapter(cmd);
        adapter.UpdateCommand = con.CreateCommand();
        adapter.UpdateCommand.CommandText = sql;

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

        con.Close();

        //REFRESH
        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;

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

        con.Close();

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

    }

    private void listView1_MouseClick(object sender, MouseEventArgs e)
    {
      nameTxt.Text = listView1.SelectedItems[0].SubItems[1].Text;
      ComboBox1.Text = listView1.SelectedItems[0].SubItems[2].Text;
    }

    private void addBtn_Click(object sender, EventArgs e)
    {
      add(nameTxt.Text, ComboBox1.SelectedItem.ToString());

    }

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

    private void updateBtn_Click(object sender, EventArgs e)
    {
      int id = Convert.ToInt16(listView1.SelectedItems[0].SubItems[0].Text);
      String newName = nameTxt.Text;
      String newPosition = ComboBox1.SelectedItem.ToString();

      update(id, newName, newPosition);
    }

    private void deleteBtn_Click(object sender, EventArgs e)
    {
      int id = Convert.ToInt16 (listView1.SelectedItems[0].SubItems[0].Text);

      delete(id);
    }

    private void clearbtn_Click(object sender, EventArgs e)
    {
      listView1.Items.Clear();
      nameTxt.Text = "";
      ComboBox1.Text = "";
    }
  }
}

Conclusion

We have looked at how to insert,select,update and delete data to and from SQlserver database.Our component was the ListView.We used SQLdataAdapter because our database of choice was SQLServer.Now you guys must use the appropriate SQLServer instance.First you must SQLserver installed in your machine then pass the appropriate name in the connection string.

Best Regards.