Configuring Parameters and Parameter Data Types (ADO.NET)
Command objects use parameters to pass values to SQL statements or stored procedures, which facilitates type checking and validation.
Command text, parameter input is treated as a constant (literal value) not as executable code.
Command text as constant guard against "SQL injection" attacks, in which an attacker inserts a command that compromises security on the server into an SQL statement.
See the below code to understand about parameterized query its other important related topics.
Begin your application by launching VS.NET and creating a new project using File - > Project and choose C# Windows Application template as shown in figure below:
Add a new item application configuration file as shown below from Project -> Add New Item
The code for the application configuration file:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="constr" connectionString="initial catalog=puran; data source=MCN002; integrated security=sspi"/>
</connectionStrings>
</configuration>
Note: In the above code I have made a connection using windows authentication.
In the form design and button, textboxes and labels as follow:
Add the necessary reference file from the solution explorer, Reference as below:
Code for adding records in a database through textbox using parameterized query in disconnected model.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.Common;
using System.Configuration;
using System.Windows.Forms;
namespace adding_Records_disconnected
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
SqlConnection con;
SqlCommand cmd;
private void button1_Click(object sender, EventArgs e)
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString);
cmd = new SqlCommand();
cmd.Connection = con;
cmd.Parameters.Add(new SqlParameter("@RollNo", SqlDbType.Int));
cmd.Parameters["@RollNo"].Value = textBox1.Text;
cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar));
cmd.Parameters["@Name"].Value = textBox2.Text;
cmd.Parameters.Add(new SqlParameter("@Fees", SqlDbType.Float));
cmd.Parameters["@Fees"].Value = textBox3.Text;
cmd.CommandText="insert into student values(@RollNo, @Name, @Fees)";
con.Open();
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Record inserted");
}
}
}
The above code explanation will be clearer with the theoretical aspect below. Read the following paragraphs for better understanding of the code listed in the article.
DbParameter Object
A DbParameter object can be created by using its constructor, or by adding it to the DbParameterCollection by calling the Add method of the DbParameterCollection collection.
The Add method will take as input either constructor arguments or an existing parameter object, depending on the data provider.
ParameterDirection Property
When adding parameters, you must supply a ParameterDirection property for parameters.
The following table shows the ParameterDirection values that you can use with the ParameterDirection enumeration.
| Member Name | Description |
| Input | The parameter is an input parameter. This is the default. |
| Output | The parameter is an output parameter. |
| Input/Output | The parameter can perform both input and output. |
| Return Value | The parameter represents a return value from an operation such as a stored procedure, built-in function, or user-defined function. |
Note: SQL Server has only two-parameter direction Input and Output.
Parameter Data Types
In the above code I have used SqlDbType of the Parameter object.
The data type of a parameter is specific to the .NET Framework data provider. Specifying the type converts the value of the Parameter to the .NET Framework data provider type before passing the value to the data source.
Begin your application by launching VS.NET and creating a new project using File - > Project and choose C# Windows Application template as shown in figure below:
Add a new item application configuration file as shown below from Project -> Add New Item
The code for the application configuration file:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="constr" connectionString="initial catalog=puran; data source=MCN002; integrated security=sspi"/>
</connectionStrings>
</configuration>
Note: In the above code I have made a connection using windows authentication.
Design the form as below:
Add the necessary reference file from the solution explorer, Reference as below:
Code for displaying records in a database through textbox using parameterized query in disconnected model.
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.Configuration;
using System.Data.Common;
using System.Data.SqlClient;
namespace shwing_records_disconnected
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
SqlConnection con;
SqlCommand cmd;
private void button1_Click(object sender, EventArgs e)
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString);
cmd = new SqlCommand();
cmd.Connection = con;
cmd.Parameters.Add(new SqlParameter("@RollNo", SqlDbType.Int));
cmd.Parameters["@RollNo"].Value = textBox4.Text;
cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar,50));
cmd.Parameters["@Name"].Direction = ParameterDirection.Output;
cmd.Parameters.Add(new SqlParameter("@Fees", SqlDbType.Float));
cmd.Parameters["@Fees"].Direction = ParameterDirection.Output;
cmd.CommandText = "select @Name=Name, @Fees=Fees from student where RollNo=@RollNo";
con.Open();
cmd.ExecuteNonQuery();
textBox1.Text = cmd.Parameters["@RollNo"].Value.ToString();
textBox2.Text = cmd.Parameters["@Name"].Value.ToString();
textBox3.Text = cmd.Parameters["@Fees"].Value.ToString();
con.Close();
MessageBox.Show("Record Displayed");
}
}
}
Advantages of parameterized commands:
-
Parameterized commands can also improve query execution performance, because they help the database server accurately match the incoming command with a proper-cached query plan.
-
Parameterized command provide security and performance benefits.
-
Parameterized commands provide a convenient method for organizing values passed to a data source.
Note: I have attached code of the above program. You have to create table and change the necessary database connection string in configuration file.
Conclusion
After reading this article you might be clear about command object, benefits of parameterized command, DbParameter Object, ParameterDirection Property and Parameter Data Types in ADO.NET using SQL Server as database.
Your feedback and constructive contributions are welcome. Please feel free to contact me for feedback or comments you may have about this article.