Save, Delete, Search And Update Record Using SqlParameter Class in ADO.NET

Introduction

Here I am performing simple save, delete, search and update operations in a Windows Forms Application using the SqlParameter class. The SqlParamater class is found in the "System.Data.SqlClient" namespace. It is a class of a connected architecture of .NET framework. It represents parameters. We use the SqlParameter class to perform various operations.

Open Visual Studio 2010 and create a Windows Forms Application. Add some UI Controls and arrange them like in the following figure.



Write the following code

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 sqlparamater
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        SqlConnection conn;
        SqlCommand comm;
        string connstr = "database=student;server=.;user=sa;password=wintellect";
        private void btnsave_Click(object sender, EventArgs e)
        {
            conn = new SqlConnection(connstr);
            comm = new SqlCommand();
            conn.Open();
            // creating instance of SsqlParameter
            SqlParameter rollno=new SqlParameter("@rn",SqlDbType.Int);
            SqlParameter name = new SqlParameter("@n", SqlDbType.VarChar);
            SqlParameter course = new SqlParameter("@c", SqlDbType.VarChar);
            SqlParameter city = new SqlParameter("@ci", SqlDbType.VarChar);
            // Adding parameter to SqlCommand
            comm.Parameters.Add(rollno);
            comm.Parameters.Add(name);
            comm.Parameters.Add(course);
            comm.Parameters.Add(city);
            // Setting values
            rollno.Value = Convert.ToInt32(txtrollno.Text);
            name.Value = txtname.Text;
            course.Value = txtcourse.Text;
            city.Value = txtcity.Text;
            // adding connection to SqlCommand
            comm.Connection = conn;
            // Sql Statement
            comm.CommandText = "insert into student_detail values(@rn,@n,@c,@ci)";
 
            try
            {
                comm.ExecuteNonQuery();
                MessageBox.Show("Saved");
                txtcity.Clear();
                txtcourse.Clear();
                txtname.Clear();
                txtrollno.Clear();
            }
            catch (Exception)
            {
                MessageBox.Show("Not Saved");
            }
            finally
            {
                conn.Close();
            }
        }
        private void btndelete_Click(object sender, EventArgs e)
        {
            conn = new SqlConnection(connstr);
            comm = new SqlCommand();
            conn.Open();
 
            SqlParameter rollno = new SqlParameter("@rn", SqlDbType.Int);
          
            comm.Parameters.Add(rollno);
              
            rollno.Value = Convert.ToInt32(txtrollno.Text);
         
 
            comm.Connection = conn;
            comm.CommandText = "delete from student_detail where rollno=@rn";
 
            try
            {
                comm.ExecuteNonQuery();
                MessageBox.Show("Deleted....");
 
                txtcity.Clear();
                txtcourse.Clear();
                txtname.Clear();
                txtrollno.Clear();
                txtrollno.Focus();
               
            }
            catch (Exception)
            {
                MessageBox.Show("Not Deleted....");
            }
            finally
            {
                conn.Close();
            }
        }
 
        private void btnsearch_Click(object sender, EventArgs e)
        {
            if (txtrollno.Text == "")
            {
                MessageBox.Show("Please, Enter Roll No. of Student");
            }
            else
            {
                conn = new SqlConnection(connstr);
                comm = new SqlCommand();
                conn.Open();
 
                SqlParameter rollno = new SqlParameter("@rn", SqlDbType.Int);
                SqlParameter name = new SqlParameter("@n", SqlDbType.VarChar);
                SqlParameter course = new SqlParameter("@c", SqlDbType.VarChar);
                SqlParameter city = new SqlParameter("@ci", SqlDbType.VarChar);
 
                comm.Parameters.Add(rollno);
                comm.Parameters.Add(name);
                comm.Parameters.Add(course);
                comm.Parameters.Add(city);
                rollno.Direction = ParameterDirection.Input;
                name.Direction = ParameterDirection.Output;
                course.Direction = ParameterDirection.Output;
                city.Direction = ParameterDirection.Output;
 
                name.Size = 30;
                course.Size = 20;
                city.Size = 20;
 
                rollno.Value = Convert.ToInt32(txtrollno.Text);
 
 
                comm.Connection = conn;
                comm.CommandText = "select @n=name,@c=course,@ci=city from student_detail where rollno=@rn";
 
                try
                {
                    comm.ExecuteNonQuery();
                    txtname.Text = name.Value.ToString();
                    txtcourse.Text = course.Value.ToString();
                    txtcity.Text = city.Value.ToString();
 
                }
                catch (Exception)
                {
                    MessageBox.Show("Not Found");
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        private void btnupdate_Click(object sender, EventArgs e)
        {
            conn = new SqlConnection(connstr);
            comm = new SqlCommand();
            conn.Open();
            SqlParameter rollno = new SqlParameter("@rn", SqlDbType.Int);
            SqlParameter name = new SqlParameter("@n", SqlDbType.VarChar);
            SqlParameter course = new SqlParameter("@c", SqlDbType.VarChar);
            SqlParameter city = new SqlParameter("@ci", SqlDbType.VarChar);

            comm.Parameters.Add(rollno);
            comm.Parameters.Add(name);
            comm.Parameters.Add(course);
            comm.Parameters.Add(city); 

            rollno.Value = Convert.ToInt32(txtrollno.Text);
            name.Value = txtname.Text;
            course.Value = txtcourse.Text;
            city.Value = txtcity.Text;
 
            comm.Connection = conn;
            comm.CommandText = "update student_detail set name=@n,course=@c,city=@ci where rollno=@rn";
 
            try
            {
                comm.ExecuteNonQuery();
                MessageBox.Show("Updated....");
                txtcity.Clear();
                txtcourse.Clear();
                txtname.Clear();
                txtrollno.Clear();
            }
            catch (Exception)
            {
                MessageBox.Show("Not Updated....");
            }
            finally
            {
                conn.Close();
            }
        }
 
        private void Form1_Load(object sender, EventArgs e)
        {
 
        }
    }
}

Run the application.

Output



You can Save, Delete, Search and Update records.

Here are some related resources.

• Executing DML commands in F#

• Inserting & Retrieving records from MS Access 2007 using ODBC

• CASCADING Referential Integrity in SqlServer

• Getting started with ADO.NET Entity Framework in .NET 3.5 - Part II

• Insert/Update/Delete in DataGridView using Linq in Windows Forms