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. }
  16. SqlConnection conn;
  17. SqlCommand comm;
  18. string connstring = "database=student;server=.;user=sa;password=wintellect";
  20. private void btnsave_Click(object sender, EventArgs e) {
  21. conn = new SqlConnection(connstring);
  22. conn.Open();
  24. comm = new SqlCommand();
  25. comm.Connection = conn;
  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
  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
  39. //Creating instance of SqlParameter
  40. SqlParameter PmtrCity = new SqlParameter();
  42. PmtrCity.ParameterName = "@ct"; // Defining Name
  43. PmtrCity.SqlDbType = SqlDbType.VarChar; // Defining DataType
  44. PmtrCity.Direction = ParameterDirection.Input; // Setting the direction
  46. // Adding Parameter instances to sqlcommand
  48. comm.Parameters.Add(PmtrRollNo);
  49. comm.Parameters.Add(PmtrName);
  50. comm.Parameters.Add(PmtrCity);
  52. // Setting values of Parameter
  54. PmtrRollNo.Value = Convert.ToInt32(txtrollno.Text);
  55. PmtrName.Value = txtname.Text;
  56. PmtrCity.Value = txtcity.Text;
  58. comm.CommandText = "insert into student_detail values(@rn,@nm,@ct)";
  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. }
  70. }
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();
  5. comm = new SqlCommand();
  6. comm.Connection = conn;
  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
  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
  21. //Creating instance of SqlParameter
  22. SqlParameter PmtrCity = new SqlParameter("@ct", SqlDbType.VarChar, 20);
  23. PmtrCity.Direction = ParameterDirection.Output; // Setting the direction
  25. // Adding Parameter instances to sqlcommand
  27. comm.Parameters.Add(PmtrRollNo);
  28. comm.Parameters.Add(PmtrName);
  29. comm.Parameters.Add(PmtrCity);
  31. // Setting values of Parameter
  33. PmtrRollNo.Value = Convert.ToInt32(txtrollno.Text);
  34. PmtrName.Value = txtname.Text;
  35. PmtrCity.Value = txtcity.Text;
  37. comm.CommandText = "select @nm=name,@ct=city from student_detail where rollno=@rn";
  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. }
  48. }
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.