This is very managed and safe way to use parameter to execute SQL query and Stored Procedure. By doing this you can avoid
sql injection type of problems. We need to set the argument of sql query or stored procedure to use parameters in a SQL command object.You need to create a parameter object for this.
All parameter objects implement the
System.Data.IDataParameter interface.
After that you can configure the parameter object’s values, data types, and directions, and add them to the command object’s parameter collection using the IDbCommand.Parameters.Add method.
The
ODBC,
OLE DB, and
SQL Server CE data providers support positional parameters, the location of each argument is identified by a question mark
"?".
For example, the below query identifies two locations to be substituted with parameter values:
UPDATE product_master SET product_name= ? WHERE product_id = ?
The
SQL Server and
Oracle data providers support named parameters, which allow to identify
each parameter location using a name preceded by the at symbol
"@".
For example :
UPDATE product_master SET product_name= @product_name= WHERE product_id = @product_id
To specify the parameter values into a command, we must create parameter objects of
the specific type and add them to the command object’s parameter collection accessible through the Parameters property.
Here is example for this.
In this example we take two functions , first function is for to execute sql query and second function is for execute stored procedure.
C# Examples :
public void ParameterizedCommand(SqlConnection objCon, int product_id, string product_name)
{
// Create a new command.
using (SqlCommand com = objCon.CreateCommand())
{
com.CommandType = CommandType.Text;
com.CommandText = "UPDATE product_master SET product_name = @product_name WHERE product_id = @product_id";
// Create a SqlParameter object.
SqlParameter objParam = com.CreateParameter();
objParam.ParameterName = "@product_name";
objParam.SqlDbType = SqlDbType.VarChar;
objParam.Value = product_name;
com.Parameters.Add(objParam);
com.Parameters.Add("@product_id", SqlDbType.Int).Value = product_id;
//Execute command
int result = com.ExecuteNonQuery();
if (result == 1)
{
Response.Write("Product title updated.");
}
else
{
Response.Write("Product title not updated.");
}
}
}
public void ParameterizedStoredProcedure(SqlConnection objCon, string product_name)
{
// Create and configure a new command.
using (SqlCommand com = objCon.CreateCommand())
{
com.CommandType = CommandType.StoredProcedure;
com.CommandText = "sp_product_details_by_product_name";
// Create a SqlParameter object .
com.Parameters.Add("@product_id", SqlDbType.VarChar).Value =product_name;
// Execute command.
using (IDataReader objReader = com.ExecuteReader())
{
Response.Write( string.Format( "Product Name ({0}).", product_name));
while (objReader.Read())
{
// Display the product details.
Response.Write( string.Format( " {0} = {1}", objReader["product_id"], objReader["product_name"]));
}
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
// Create a new SqlConnection object.
using (SqlConnection objCon = new SqlConnection())
{
// Configure the SqlConnection object's connection string.
objCon.ConnectionString = @"Data Source=.\SQLEXPRESS;" + // SQL Server instance , You can also set remote database
"Initial Catalog=TempDatabase;" + // the sample TempDatabase DB
"User ID=sa;Password=ibmx206;"; //Set Credential
// Open the database connection and execute the example
// commands through the connection.
objCon.Open();
ParameterizedCommand(objCon, 8,"KeyBoard");
ParameterizedStoredProcedure(objCon, "CPU");
};
}
VB.net Examples :
Public Sub ParameterizedCommand(ByVal objCon As SqlConnection, ByVal product_id As Integer, ByVal product_name As String)
' Create a new command.
Using com As SqlCommand = objCon.CreateCommand()
com.CommandType = CommandType.Text
com.CommandText = "UPDATE product_master SET product_name = @product_name WHERE product_id = @product_id"
' Create a SqlParameter object.
Dim objParam As SqlParameter = com.CreateParameter()
objParam.ParameterName = "@product_name"
objParam.SqlDbType = SqlDbType.VarChar
objParam.Value = product_name
com.Parameters.Add(objParam)
com.Parameters.Add("@product_id", SqlDbType.Int).Value = product_id
'Execute command
Dim result As Integer = com.ExecuteNonQuery()
If result = 1 Then
Response.Write("Product title updated.")
Else
Response.Write("Product title not updated.")
End If
End Using
End Sub
Public Sub ParameterizedStoredProcedure(ByVal objCon As SqlConnection, ByVal product_name As String)
' Create and configure a new command.
Using com As SqlCommand = objCon.CreateCommand()
com.CommandType = CommandType.StoredProcedure
com.CommandText = "sp_product_details_by_product_name"
' Create a SqlParameter object .
com.Parameters.Add("@product_id", SqlDbType.VarChar).Value = product_name
' Execute command.
Using objReader As IDataReader = com.ExecuteReader()
Response.Write(String.Format("Product Name ({0}).", product_name))
While objReader.Read()
' Display the product details.
Response.Write(String.Format(" {0} = {1}", objReader("product_id"), objReader("product_name")))
End While
End Using
End Using
End Sub
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
' Create a new SqlConnection object.
Using objCon As New SqlConnection()
' Configure the SqlConnection object's connection string.
objCon.ConnectionString = "Data Source=.\SQLEXPRESS;" & _
"Initial Catalog=TempDatabase;" & _
"User ID=sa;Password=sa;"
' Open the database connection and execute the example
' commands through the connection.
objCon.Open()
ParameterizedCommand(objCon, 8, "KeyBoard")
ParameterizedStoredProcedure(objCon, "CPU")
End Using
End Sub
You can also directly use SQLParameter class object with SQLCommand and SQLConnection objects.
In this article we are using both Text and StoredProcedure as command type.
Note : Give Us your valuable feedback in comments. Give your suggestions in articles so we can update our articles accordingly that.
