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
![sqlparamater class in ado.net]()
You can Save, Delete, Search and Update records.
Here are some related resources.