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. To 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.

 

 

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

• Executing DML commands in F#
• Inserting & Retrieving records from MS Access 2007 using ODBC
• Searching Records in ADO.NET
• Insert/Update/Delete in DataGridView using Linq in Windows Forms