Save, Delete, Search And Update Records in ADO.NET

In this article you will learn to save, delete, search and update records in ADO.NET.

Introduction

In this article I am performing simple operations like save, delete, update and search operations in a Windows Forms application. At first we should have a Database. So create a database. In this example my database name is "STUDENT" and database table is "student_detail" which has four columns as "roll_no", "s_name", "age" and "course".

Create a Windows Forms Application. Take some UI controls.

save record in ado.net

Now we write code to perform the operations described in this article.

Code for Saving Record

  1. conn = new SqlConnection(connstring);  
  2. conn.Open();  
  3. comm = new SqlCommand("insert into student_detail values(" + txtrn.Text + ",'" + txtname.Text + "'," + txtage.Text + ",'" + txtcourse.Text + "')", conn);  
  4. try  
  5. {  
  6.       comm.ExecuteNonQuery();  
  7.       MessageBox.Show("Saved...");  
  8. }  
  9. catch (Exception)  
  10. {  
  11.       MessageBox.Show("Not Saved");  
  12. }  
  13. finally  
  14. {  
  15.       conn.Close();  
  16. }  
Look at the above code. In the first line of code, an instance of a SqlConnection is created. In the next, an instance of a SqlCommand class is created and a SQL statement for inserting values into the database table is specified. Then I am calling the ExecuteNonQuery() method in a try block. In the finally block I am closing the SqlConnection by the Close() method. The same as writing SQL Statements for performing various operations. Look at the following code for performing all the operations.
  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.SqlClient;  
  10.    
  11. namespace savedeleteupdateapp  
  12. {  
  13.     public partial class Form1 : Form  
  14.     {  
  15.         public Form1()  
  16.         {  
  17.             InitializeComponent();  
  18.         }  
  19.         SqlConnection conn;  
  20.         SqlCommand comm;  
  21.         SqlDataReader dreader;  
  22.         string connstring = "server=localhost;database=student;user=sa;password=wintellect";  
  23.         private void btnsave_Click(object sender, EventArgs e)  
  24.         {  
  25.             conn = new SqlConnection(connstring);  
  26.             conn.Open();  
  27.             comm = new SqlCommand("insert into student_detail values(" + txtrn.Text + ",'" + txtname.Text + "'," + txtage.Text + ",'" + txtcourse.Text + "')", conn);  
  28.             try  
  29.             {  
  30.                 comm.ExecuteNonQuery();  
  31.                 MessageBox.Show("Saved...");  
  32.             }  
  33.             catch (Exception)  
  34.             {  
  35.                 MessageBox.Show("Not Saved");  
  36.             }  
  37.             finally  
  38.             {  
  39.                 conn.Close();  
  40.             }  
  41.         }  
  42.    
  43.         private void btnclear_Click(object sender, EventArgs e)  
  44.         {  
  45.             txtage.Clear();  
  46.             txtcourse.Clear();  
  47.             txtname.Clear();  
  48.             txtrn.Clear();  
  49.             txtrn.Focus();  
  50.         }  
  51.    
  52.         private void btndelete_Click(object sender, EventArgs e)  
  53.         {  
  54.             conn = new SqlConnection(connstring);  
  55.             conn.Open();  
  56.             comm = new SqlCommand("delete from student_detail where roll_no = " + txtrn.Text + " ", conn);  
  57.             try  
  58.             {  
  59.                 comm.ExecuteNonQuery();  
  60.                 MessageBox.Show("Deleted...");  
  61.                 txtage.Clear();  
  62.                 txtcourse.Clear();  
  63.                 txtname.Clear();  
  64.                 txtrn.Clear();  
  65.                 txtrn.Focus();  
  66.             }  
  67.             catch (Exception x)  
  68.             {  
  69.                 MessageBox.Show(" Not Deleted" + x.Message );  
  70.             }  
  71.             finally  
  72.             {  
  73.                 conn.Close();  
  74.             }  
  75.         }  
  76.    
  77.         private void btnsearch_Click(object sender, EventArgs e)  
  78.         {  
  79.             conn = new SqlConnection(connstring);  
  80.             conn.Open();  
  81.             comm = new SqlCommand("select * from student_detail where roll_no = " + txtrn.Text + " ", conn);  
  82.            try  
  83.             {  
  84.                 dreader = comm.ExecuteReader();  
  85.                 if (dreader.Read())  
  86.                 {  
  87.                     txtname.Text = dreader[1].ToString();  
  88.                     txtage.Text = dreader[2].ToString();  
  89.                     txtcourse.Text = dreader[3].ToString();  
  90.                 }  
  91.                 else  
  92.                 {  
  93.                     MessageBox.Show(" No Record");  
  94.                 }  
  95.                 dreader.Close();  
  96.             }  
  97.             catch (Exception)  
  98.             {  
  99.                 MessageBox.Show(" No Record");  
  100.             }  
  101.             finally  
  102.             {  
  103.                 conn.Close();  
  104.             }  
  105.         }  
  106.    
  107.         private void btnupdate_Click(object sender, EventArgs e)  
  108.         {  
  109.             conn = new SqlConnection(connstring);  
  110.             conn.Open();  
  111.             comm = new SqlCommand("update student_detail set s_name= '"+txtname.Text+"', age= "+txtage.Text+" , course=' "+txtcourse.Text+"' where roll_no =     
  112.             "+txtrn.Text+" ", conn);  
  113.             try  
  114.             {  
  115.                 comm.ExecuteNonQuery();  
  116.                 MessageBox.Show("Updated..");  
  117.             }  
  118.             catch (Exception)  
  119.             {  
  120.                 MessageBox.Show(" Not Updated");  
  121.             }  
  122.             finally  
  123.             {  
  124.                 conn.Close();  
  125.             }  
  126.         }  
  127.    
  128.         private void Form1_Load(object sender, EventArgs e)  
  129.        {  
  130.             txtrn.Focus();  
  131.         }  
  132.     }  
  133. }  
Now run the application. You can Save, Search, Delete and Update records.

Summary

In this article you learned how to save, delete, search and update records in ADO.NET. I hope it will be helpful for beginners.  

Here are some related resource