Beginning .Net : Use Parameter to Execute SQL query or Stored Procedure on database in C# Examples and VB.Net Examples

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.