Reader Level:
ARTICLE

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

Posted by A K Articles | ADO.NET January 20, 2012
In this article you will learn to save, delete, search and update records in ADO.NET.
  • 0
  • 0
  • 49810
Download Files:
 

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

conn = new SqlConnection(connstring);
conn.Open();
comm = new SqlCommand("insert into student_detail values(" + txtrn.Text + ",'" + txtname.Text + "'," + txtage.Text + ",'" + txtcourse.Text + "')", conn);
try
{
      comm.ExecuteNonQuery();
      MessageBox.Show("Saved...");
}
catch (Exception)
{
      MessageBox.Show("Not Saved");
}
finally
{
      conn.Close();
}
           
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.


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

namespace
savedeleteupdateapp
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        SqlConnection conn;
        SqlCommand comm;
        SqlDataReader dreader;
        string connstring = "server=localhost;database=student;user=sa;password=wintellect";
        private void btnsave_Click(object sender, EventArgs e)
        {
            conn = new SqlConnection(connstring);
            conn.Open();
            comm = new SqlCommand("insert into student_detail values(" + txtrn.Text + ",'" + txtname.Text + "'," + txtage.Text + ",'" + txtcourse.Text + "')", conn);
            try
            {
                comm.ExecuteNonQuery();
                MessageBox.Show("Saved...");
            }
            catch (Exception)
            {
                MessageBox.Show("Not Saved");
            }
            finally
            {
                conn.Close();
            }
        }
 
        private void btnclear_Click(object sender, EventArgs e)
        {
            txtage.Clear();
            txtcourse.Clear();
            txtname.Clear();
            txtrn.Clear();
            txtrn.Focus();
        }
 
        private void btndelete_Click(object sender, EventArgs e)
        {
            conn = new SqlConnection(connstring);
            conn.Open();
            comm = new SqlCommand("delete from student_detail where roll_no = " + txtrn.Text + " ", conn);
            try
            {
                comm.ExecuteNonQuery();
                MessageBox.Show("Deleted...");
                txtage.Clear();
                txtcourse.Clear();
                txtname.Clear();
                txtrn.Clear();
                txtrn.Focus();
            }
            catch (Exception x)
            {
                MessageBox.Show(" Not Deleted" + x.Message );
            }
            finally
            {
                conn.Close();
            }
        }
 
        private void btnsearch_Click(object sender, EventArgs e)
        {
            conn = new SqlConnection(connstring);
            conn.Open();
            comm = new SqlCommand("select * from student_detail where roll_no = " + txtrn.Text + " ", conn);
           try
            {
                dreader = comm.ExecuteReader();
                if (dreader.Read())
                {
                    txtname.Text = dreader[1].ToString();
                    txtage.Text = dreader[2].ToString();
                    txtcourse.Text = dreader[3].ToString();
                }
                else
                {
                    MessageBox.Show(" No Record");
                }
                dreader.Close();
            }
            catch (Exception)
            {
                MessageBox.Show(" No Record");
            }
            finally
            {
                conn.Close();
            }
        }
 
        private void btnupdate_Click(object sender, EventArgs e)
        {
            conn = new SqlConnection(connstring);
            conn.Open();
            comm = new SqlCommand("update student_detail set s_name= '"+txtname.Text+"', age= "+txtage.Text+" , course=' "+txtcourse.Text+"' where roll_no =  
           
"+txtrn.Text+" ", conn);
            try
            {
                comm.ExecuteNonQuery();
                MessageBox.Show("Updated..");
            }
            catch (Exception)
            {
                MessageBox.Show(" Not Updated");
            }
            finally
            {
                conn.Close();
            }
        }
 
        private void Form1_Load(object sender, EventArgs e)
       {
            txtrn.Focus();
        }
    }
}
 
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

COMMENT USING

Trending up