Using Parameters in Queries

Using parameters in SQL queries is important for several reasons, including preventing SQL injection attacks and improving query performance. In ASP.NET, you can use parameters in your SQL queries by specifying them in the CommandText property of a SqlCommand object.

Here’s an example of using parameters in a SQL query to retrieve the details of a customer from the Northwind database:

string connectionString = ConfigurationManager.ConnectionStrings[“NorthwindConnectionString”].ConnectionString;

string customerId = “ALFKI”;

string query = “SELECT CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE CustomerID = @CustomerID”;

using (SqlConnection connection = new SqlConnection(connectionString))

{

SqlCommand command = new SqlCommand(query, connection);

command.Parameters.AddWithValue(“@CustomerID”, customerId);

connection.Open();

SqlDataReader reader = command.ExecuteReader();

if (reader.Read())

{

string companyName = reader.GetString(0);

string contactName = reader.GetString(1);

string contactTitle = reader.GetString(2);

string address = reader.GetString(3);

string city = reader.GetString(4);

string region = reader.IsDBNull(5) ? null : reader.GetString(5);

string postalCode = reader.IsDBNull(6) ? null : reader.GetString(6);

string country = reader.GetString(7);

string phone = reader.GetString(8);

string fax = reader.IsDBNull(9) ? null : reader.GetString(9);

// Do something with the retrieved data

}

reader.Close();

}

In this example, the query string contains a parameter placeholder (@CustomerID) that will be replaced with the value of the customerId variable. The SqlCommand object is created with the query string and the SqlConnection object, and the parameter value is added using the Parameters.AddWithValue method. The SqlDataReader object is used to read the results of the query, and the values of the retrieved columns are retrieved using the GetString method. In cases where the column can contain null values, the IsDBNull method is used to check for nulls before retrieving the value.