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

A K
13y
104.1k
0
0

Article

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.

sqlparamater class in .net

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.

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

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