How to Update and Delete Record in the Database using C#

Submitted by joken on Tuesday, April 15, 2014 - 13:24.
This lesson is a continuation of our last topic called “How to Save Record in Database using C#”. At this time we will be focusing on how to update and delete a record from the database using C#. To start with, this application. Open our project called “usermanagement”, then we need to add another control such as buttons, textbox and label. Then arrange all the objects like as shown below. a1 Before we proceed on adding a code to our application, we need to understand first the process on how the application works. First the user we will click the “Load record” button, then all the records will be listed in the datagridview. Next when the user wants to update the a specific record, the user should click the specific record listed in the datagridview. And the user can observe that the “User ID” of a specific record, we display in the textbox like as shown below. a2 This time we’re going to add functionality to our application by adding a code that will get the user id and put it into the textbox when the specific record has been click by the user. And here’s the following code: 
  1. int i = e.RowIndex;
  2. DataGridViewRow r = dataGridView1.Rows[i];
  3.  
  4. int id = Convert.ToInt32(r.Cells[0].Value);
  5. txtuserID.Text = r.Cells[0].Value + "";
Next for the “Update Record” button, add the following code: 
  1. //set our SQL UPDATE statement
  2. string sqlUpdate = "UPDATE tbluseraccounts set username ='" + txtname.Text  + "' , userusername = '" + txtuser .Text  + "', userpassword = '" + txtpass.Text + "', usertype= '" + txttype .Text  + "' where userID = " + txtuserID .Text  + "";
  3. try
  4. {
  5.  
  6.     //open the connection
  7.     conn.Open();
  8.     //set the connection
  9.     cmd.Connection = conn;
  10.     //get the SQL statement to be executed
  11.     cmd.CommandText = sqlUpdate;
  12.     //execute the query
  13.     cmd.ExecuteNonQuery();
  14.     //display a message
  15.     MessageBox.Show("Record Updated!....");
  16.     //close the connection
  17.     conn.Close();
  18.  
  19. }
  20. catch (Exception ex)
  21. {
  22.     //this will display some error message if something 
  23.     //went wrong to our code above during execution
  24.     MessageBox.Show(ex.ToString());
  25. }
  26. //we call the loadrecord() function after adding a new record
  27. loadrecord();
And for the “Delete Record” button, add the following code: 
  1. OleDbCommand cmd = new OleDbCommand();
  2. //set our SQL DELETE statement
  3. string sqlUpdate = "Delete * from tbluseraccounts where userID= " + txtuserID.Text + "";
  4. try
  5. {
  6.  
  7.     //open the connection
  8.     conn.Open();
  9.     //set the connection
  10.     cmd.Connection = conn;
  11.     //get the SQL statement to be executed
  12.     cmd.CommandText = sqlUpdate;
  13.     //execute the query
  14.     cmd.ExecuteNonQuery();
  15.     //display a message
  16.     MessageBox.Show("Record Deleted!....");
  17.     //close the connection
  18.     conn.Close();
  19.  
  20. }
  21. catch (Exception ex)
  22. {
  23.     //this will display some error message if something 
  24.     //went wrong to our code above during execution
  25.     MessageBox.Show(ex.ToString());
  26. }
  27. //we call the loadrecord() function after adding a new record
  28. loadrecord();
If you have observed, our code in “Save Record”, “Update Record” and “Delete Record” button is similar except the query. Because when you are doing the saving of record you are Inserting a new record, and if you are modifying a record you are updating a record as well as in the deleting of record. Here’s all the code used in this application: 
  1. using System;
  2. using System.Collections.Generic;
  3. using System.ComponentModel;
  4. using System.Data;
  5. using System.Drawing;
  6. using System.Linq;
  7. using System.Text;
  8. using System.Windows.Forms;
  9. using System.Data.OleDb;
  10.  
  11. namespace WindowsFormsApplication1
  12. {
  13.     public partial class Form1 : Form
  14.     {
  15.         //declare new variable named dt as New Datatable
  16.         DataTable dt = new DataTable();
  17.         //this line of code used to connect to the server and locate the database (usermgt.mdb)
  18.         static string connection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + Application .StartupPath + "/usermgt.mdb";
  19.         OleDbConnection conn = new OleDbConnection(connection);
  20.  
  21.         public Form1()
  22.         {
  23.  
  24.             InitializeComponent();// calling the function
  25.         }
  26.  
  27.         private void Form1_Load(object sender, EventArgs e)
  28.         {
  29.  
  30.         }
  31.         private void loadrecord()
  32.         {
  33.             dt = new DataTable();
  34.             string sql = "Select * from tbluseraccounts";
  35.             OleDbDataAdapter da = new OleDbDataAdapter(sql , conn);
  36.             da.Fill(dt);
  37.             dataGridView1.DataSource = dt; 
  38.         }
  39.  
  40.         private void button1_Click(object sender, EventArgs e)
  41.         {
  42.             loadrecord(); 
  43.         }
  44.  
  45.         private void btninsert_Click(object sender, EventArgs e)
  46.         {
  47.  
  48.             OleDbCommand cmd = new OleDbCommand(); 
  49.             //set our SQL Insert INTO statement
  50.             string sqlInsert = "INSERT INTO tbluseraccounts ( username, userusername, userpassword, usertype ) VALUES('" + txtname.Text + "','" + txtuser.Text + "','" + txtpass.Text + "','" + txttype.Text  + "')";
  51.             try 
  52.             {
  53.  
  54.                 //open the connection
  55.                 conn.Open();
  56.                 //set the connection
  57.                 cmd.Connection = conn;
  58.                 //get the SQL statement to be executed
  59.                 cmd.CommandText = sqlInsert;
  60.                 //execute the query
  61.                 cmd.ExecuteNonQuery();
  62.                 //display a message
  63.                 MessageBox.Show("New Record Added!....");
  64.                 //close the connection
  65.                 conn.Close();
  66.  
  67.             }
  68.             catch (Exception ex)
  69.             {
  70.                 //this will display some error message if something 
  71.                 //went wrong to our code above during execution
  72.                 MessageBox.Show(ex.ToString());
  73.             }
  74.             //we call the loadrecord() function after adding a new record
  75.             loadrecord();
  76.  
  77.  
  78.         }
  79.  
  80.  
  81.  
  82.         private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
  83.         {
  84.             int i = e.RowIndex;
  85.             DataGridViewRow r = dataGridView1.Rows[i];
  86.  
  87.             int id = Convert.ToInt32(r.Cells[0].Value);
  88.             txtuserID.Text = r.Cells[0].Value + "";
  89.  
  90.         }
  91.  
  92.         private void btnupdate_Click(object sender, EventArgs e)
  93.         {
  94.             OleDbCommand cmd = new OleDbCommand();
  95.             //set our SQL Insert INTO statement
  96.             string sqlUpdate = "UPDATE tbluseraccounts set username ='" + txtname.Text  + "' , userusername = '" + txtuser .Text  + "', userpassword = '" + txtpass.Text + "', usertype= '" + txttype .Text  + "' where userID = " + txtuserID .Text  + "";
  97.             try
  98.             {
  99.  
  100.                 //open the connection
  101.                 conn.Open();
  102.                 //set the connection
  103.                 cmd.Connection = conn;
  104.                 //get the SQL statement to be executed
  105.                 cmd.CommandText = sqlUpdate;
  106.                 //execute the query
  107.                 cmd.ExecuteNonQuery();
  108.                 //display a message
  109.                 MessageBox.Show("Record Updated!....");
  110.                 //close the connection
  111.                 conn.Close();
  112.  
  113.             }
  114.             catch (Exception ex)
  115.             {
  116.                 //this will display some error message if something 
  117.                 //went wrong to our code above during execution
  118.                 MessageBox.Show(ex.ToString());
  119.             }
  120.             //we call the loadrecord() function after adding a new record
  121.             loadrecord();
  122.         }
  123.  
  124.         private void btndelteRecord_Click(object sender, EventArgs e)
  125.         {
  126.             OleDbCommand cmd = new OleDbCommand();
  127.             //set our SQL DELETE statement
  128.             string sqlUpdate = "Delete * from tbluseraccounts where userID= " + txtuserID.Text + "";
  129.             try
  130.             {
  131.  
  132.                 //open the connection
  133.                 conn.Open();
  134.                 //set the connection
  135.                 cmd.Connection = conn;
  136.                 //get the SQL statement to be executed
  137.                 cmd.CommandText = sqlUpdate;
  138.                 //execute the query
  139.                 cmd.ExecuteNonQuery();
  140.                 //display a message
  141.                 MessageBox.Show("Record Deleted!....");
  142.                 //close the connection
  143.                 conn.Close();
  144.  
  145.             }
  146.             catch (Exception ex)
  147.             {
  148.                 //this will display some error message if something 
  149.                 //went wrong to our code above during execution
  150.                 MessageBox.Show(ex.ToString());
  151.             }
  152.             //we call the loadrecord() function after adding a new record
  153.             loadrecord();
  154.         }
  155.  
  156.  
  157.  
  158.  
  159.     }
  160. }
You can now test the application by pressing the “F5” or the start button.

Tags

Add new comment

About text formats
  • 1542 views