Working With the SqlParameter Class in ADO.NET

Introduction

 
The SqlParameter class is found in the "System.Data.SqlClient" namespace. It is a class of a connected architecture of the .NET framework. It represents parameters. To work with the SqlParameter class we should have a database. In this example, I am using a Database "student" which has a "student_detail" table. "RollNo", "Name" and "City" are column names. I will save and retrieve records using the SqlParameter class.  Here is a list of important properties of the SqlParameter class which will be used in this example.
  1. SqlDbType: It is used to set the SQL Server Datatypes for a given parameter.
  2. ParameterName: It is used to specify a parameter name.
  3. Direction: It is used for setting the direction of a SqlParameter. It is Input or Output or both (InputOutput).
  4. Size: It is used to set the maximum size of the value of the parameter.
  5. Value: It is used for assigning or getting the value of the parameter.
Now, take a Windows Forms application in Visual Studio 2010. Take some UI Controls and arrange them as shown in the figure below
 
Clipboard01.gif
 
Write the following code for saving a record into the database.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.ComponentModel;  
  4. using System.Data;  
  5. using System.Drawing;  
  6. using System.Linq;  
  7. using System.Text;  
  8. using System.Windows.Forms;  
  9. using System.Data.SqlClient;  
  10. namespace WorkWithSqlParameterClass {  
  11.     public partial class Form1: Form {  
  12.         public Form1() {  
  13.             InitializeComponent();  
  14.         }  
  15.   
  16.         SqlConnection conn;  
  17.         SqlCommand comm;  
  18.         string connstring = "database=student;server=.;user=sa;password=wintellect";  
  19.   
  20.         private void btnsave_Click(object sender, EventArgs e) {  
  21.             conn = new SqlConnection(connstring);  
  22.             conn.Open();  
  23.   
  24.             comm = new SqlCommand();  
  25.             comm.Connection = conn;  
  26.   
  27.             //Creating instance of SqlParameter  
  28.             SqlParameter PmtrRollNo = new SqlParameter();  
  29.             PmtrRollNo.ParameterName = "@rn"// Defining Name  
  30.             PmtrRollNo.SqlDbType = SqlDbType.Int; // Defining DataType  
  31.             PmtrRollNo.Direction = ParameterDirection.Input; // Setting the direction  
  32.   
  33.             //Creating instance of SqlParameter  
  34.             SqlParameter PmtrName = new SqlParameter();  
  35.             PmtrName.ParameterName = "@nm"// Defining Name  
  36.             PmtrName.SqlDbType = SqlDbType.VarChar; // Defining DataType  
  37.             PmtrName.Direction = ParameterDirection.Input; // Setting the direction  
  38.   
  39.             //Creating instance of SqlParameter  
  40.             SqlParameter PmtrCity = new SqlParameter();  
  41.   
  42.             PmtrCity.ParameterName = "@ct"// Defining Name  
  43.             PmtrCity.SqlDbType = SqlDbType.VarChar; // Defining DataType  
  44.             PmtrCity.Direction = ParameterDirection.Input; // Setting the direction  
  45.   
  46.             // Adding Parameter instances to sqlcommand  
  47.   
  48.             comm.Parameters.Add(PmtrRollNo);  
  49.             comm.Parameters.Add(PmtrName);  
  50.             comm.Parameters.Add(PmtrCity);  
  51.   
  52.             // Setting values of Parameter  
  53.   
  54.             PmtrRollNo.Value = Convert.ToInt32(txtrollno.Text);  
  55.             PmtrName.Value = txtname.Text;  
  56.             PmtrCity.Value = txtcity.Text;  
  57.   
  58.             comm.CommandText = "insert into student_detail values(@rn,@nm,@ct)";  
  59.   
  60.             try {  
  61.                 comm.ExecuteNonQuery();  
  62.                 MessageBox.Show("Saved");  
  63.             } catch (Exception) {  
  64.                 MessageBox.Show("Not Saved");  
  65.             } finally {  
  66.                 conn.Close();  
  67.             }  
  68.         }  
  69.     }  
Run the application.
 
Output
 
Clipboard02.gif
 
Fill in the form and click the "Save" button. The record will be saved to the database and a message box will be displayed with a confirmation message.
 
Clipboard03.gif
 
Now we retrieve records from the database. Take another button and set its text property as "Show". Add the following code for the "Show" button. 
  1. private void btnshow_Click(object sender, EventArgs e) {  
  2.     conn = new SqlConnection(connstring);  
  3.     conn.Open();  
  4.   
  5.     comm = new SqlCommand();  
  6.     comm.Connection = conn;  
  7.   
  8.     //Creating instance of SqlParameter  
  9.     SqlParameter PmtrRollNo = new SqlParameter();  
  10.     PmtrRollNo.ParameterName = "@rn"// Defining Name  
  11.     PmtrRollNo.SqlDbType = SqlDbType.Int; // Defining DataType  
  12.     PmtrRollNo.Direction = ParameterDirection.Input; // Setting the direction  
  13.   
  14.     //Creating instance of SqlParameter  
  15.     SqlParameter PmtrName = new SqlParameter();  
  16.     PmtrName.ParameterName = "@nm"// Defining Name  
  17.     PmtrName.SqlDbType = SqlDbType.VarChar; // Defining DataType  
  18.     PmtrName.Size = 30;  
  19.     PmtrName.Direction = ParameterDirection.Output; // Setting the direction  
  20.   
  21.     //Creating instance of SqlParameter  
  22.     SqlParameter PmtrCity = new SqlParameter("@ct", SqlDbType.VarChar, 20);  
  23.     PmtrCity.Direction = ParameterDirection.Output; // Setting the direction  
  24.   
  25.     // Adding Parameter instances to sqlcommand  
  26.   
  27.     comm.Parameters.Add(PmtrRollNo);  
  28.     comm.Parameters.Add(PmtrName);  
  29.     comm.Parameters.Add(PmtrCity);  
  30.   
  31.     // Setting values of Parameter  
  32.   
  33.     PmtrRollNo.Value = Convert.ToInt32(txtrollno.Text);  
  34.     PmtrName.Value = txtname.Text;  
  35.     PmtrCity.Value = txtcity.Text;  
  36.   
  37.     comm.CommandText = "select @nm=name,@ct=city from student_detail where rollno=@rn";  
  38.   
  39.     try {  
  40.         comm.ExecuteNonQuery();  
  41.         txtname.Text = PmtrName.Value.ToString();  
  42.         txtcity.Text = PmtrCity.Value.ToString();  
  43.     } catch (Exception) {  
  44.         MessageBox.Show("Not Found");  
  45.     } finally {  
  46.         conn.Close();  
  47.     }  
Run the application.
 
Output
 
Clipboard04.gif
 
Enter a roll number and click the "Show" button. It will show all the related information of the student having the given roll number.
 
Clipboard05.gif
 
Here are some related resources.