how to Adding Parameters to Commands | Trickcode

Introduction

You can use command parameters when specifying commands to the CommandText property of the Command class. Without command parameters, we need to manually embed the values in our string. For example, if we want to select all the employees whose id is of a certain value, we can ask the user of the id, then the program will construct the SQL command to get the specified employee.

 // 3. add new parameter to command object
 int employeeId = Int32.Parse(Console.ReadLine());
SqlCommand command = new SqlCommand();
command.CommandText = "SELECT * FROM Employees WHERE EmployeeId = " + employeeId;;

How To Create an SQL parameter

The code above will produce an acceptable SQL command given that the user enters a valid employeeId. This approach is easy, and convenient for writing data access code. However, it also has significant drawbacks that make it unsuitable for a production-level application. These include inflexibility, poor performance, and potential security problems when using user-supplied values.

Therefore, it is recommended to construct SQL commands with parameters. To create a parameter, we can use the Parameter class. Each data provider has its own Parameter class, SqlParameter for SQL Server, or OleDbParameter for OLE DB Providers. When specifying a parameter, we need to provide the name and the data type of the data it will contain. ADO.NET has available data types for each of the data providers. For example, you can use the System.Data.SqlDbType the enumeration contains several data types for the SQL Server or you can use System.Data.OleDbType  enumeration which enlists the data types for OLE DB databases. When naming the parameter, you must precede the name with the @ symbol. Here’s an example of how to create an SQL parameter.

SqlParameter parameter1 = new SqlParameter("@EmployeeId", SqlDbType.Int);

The constructor of the Parameter class used above accepts the name and the data type of the value to be stored in the parameter. If you are using a variable-length data type such as nvarchar(x), then you can use a different constructor.

SqlParameter parameter2 = new SqlParameter("@FirstName", SqlDbType.NVarChar, 100);



Once you created the Parameter object, you can then specify the value it will contain using the Value property.


parameter1.Value = 10;
You can now add the created Parameter object to the Parameters property of the Command object.


command.Parameters.Add(parameter1);

The following code demonstrates the use of parameters. The following program accepts an employee ID from the user and prints the full name of the employee with that ID.

int id;
SqlConnection connection = new SqlConnection();
SqlCommand command = new SqlCommand();
SqlParameter parameter1 = new SqlParameter("@EmployeeId", SqlDbType.Int);
SqlDataReader reader;

connection.ConnectionString =
    @"Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;" +
    "Integrated Security=SSPI";

command.CommandText = "SELECT * FROM Employees WHERE EmployeeId = @EmployeeId";
command.Connection = connection;

Console.Write("Enter an EmployeeId: ");
id = Int32.Parse(Console.ReadLine());

parameter1.Value = id;

command.Parameters.Add(parameter1);

try
{
    connection.Open();

    reader = command.ExecuteReader(CommandBehavior.SingleRow);
    reader.Read();
    Console.WriteLine(reader["FirstName"].ToString() + " " +
        reader["LastName"].ToString());
    reader.Close();
}
catch
{
    Console.WriteLine("An error occured.");
}
finally
{
    connection.Close();
}

OutPut:- Enter an EmployeeId: 3
 Janet  Leveling

Notice the SQL command given for the CommandText property of the Command.

"SELECT * FROM Employees WHERE EmployeeId = @EmployeeId" 


The statement contains an input parameter named @EmployeeId. This is the name we have given the Parameter object. This must exactly match the name of the parameter. We have assigned the Value property of the Parameter with the value of the id variable given by the user. After that, we added the Parameter object to the Parameters property of the Command class by using the Add method. 

The Parameter object will now replace the matching parameter in the CommandText with the value of its Value property. Another way to add commands is by using the AddWithValue method of the Parameters property of the Command class. 

This technique doesn’t require you to create a Parameter object nor specify a DbType.
  command.Parameters.AddWithValue("@EmployeeId", id);

The first argument is the name of the parameter and the second one is the value that will replace that parameter.


Read More

• how to get return value from stored procedure in sql server
• What is data provider in C#?
• What is connection string C#?
• [SQL] SELECT Statement | Example -trickcode
• SQL vs NoSQL or MySQL vs MongoDB
• how to prevent sql injection attacks
• What is Database Preparations
• What is Database? What is SQL?
• SQL Basics?

                                                        Share this article with your friends