Parameterized Queries:

Queries that use SQL parameters to pass values are known as parameterized queries.

Benefits

Parameterized Queries’ main advantage is its ability to safeguard databases from SQL Injection.

Connection String

Set the connection string in Web.Config

<connectionStrings>

<add name="conString"

connectionString="Data Source=.\SQLEXPRESS;database=Northwind;

AttachDbFileName=|DataDirectory|\NORTHWND.MDF;Integrated Security=true"/>

</connectionStrings>

Namespaces

The next two namespaces must be imported.

using System.Data;

using System.Data.SqlClient;

Select Queries

The select queries will be run using the following function.

private DataTable GetData(SqlCommand cmd)

{

    DataTable dt = new DataTable ();

    String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["conString"].

ConnectionString;

    SqlConnection con =  new SqlConnection(strConnString);

    SqlDataAdapter sda = new SqlDataAdapter();

    cmd.CommandType = CommandType.Text;

    cmd.Connection = con;

    try

    {

        con.Open();

        sda.SelectCommand = cmd;

        sda.Fill(dt);

        return dt;

    }

    catch (Exception ex)

    {

        Response.Write(ex.Message);

        return null;

    }

    finally

    {

        con.Close();

        sda.Dispose();

        con.Dispose();

    }

}

Following the completion of the SQL Query, the function returns the DataTable.

Execute a Simple Select Query

string strQuery = "select * from customers";

SqlCommand cmd = new SqlCommand(strQuery);

DataTable dt = GetData(cmd);

GridView1.DataSource = dt;

GridView1.DataBind();

The Query is run in the code above, and the output is then bound to the GridView.

Execute SQL Query with Filter Condition

string strQuery = "select * from customers where city = @city";

SqlCommand cmd = new SqlCommand(strQuery);

cmd.Parameters.AddWithValue("@city", txtCity.Text.Trim());

DataTable dt = GetData(cmd);

GridView1.DataSource = dt;

GridView1.DataBind();

The aforementioned query runs a SQL statement that filters records based on city.

You’ll see that the query’s @city parameter is highlighted.

cmd.Parameters.AddWithValue("@city", txtCity.Text.Trim())

The sentence sets the parameter @City to the value of the textbox txtCity.

Insert – Update Queries

Insert and Update Queries will be run using the following functions.

private Boolean InsertUpdateData(SqlCommand cmd)

{

    String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["conString"].

ConnectionString;

    SqlConnection con = new SqlConnection(strConnString);

    cmd.CommandType = CommandType.Text;

    cmd.Connection = con;

    try

    {

        con.Open();

        cmd.ExecuteNonQuery();

        return true;

    }

    catch (Exception ex)

    {

        Response.Write(ex.Message);

        return false;

    }

    finally

    {

        con.Close();

        con.Dispose();

    }

}

Execute Insert Queries

string strQuery;

SqlCommand cmd;

strQuery = "insert into customers (CustomerID, CompanyName) values(@CustomerID, @CompanyName)";

cmd = new SqlCommand(strQuery);

cmd.Parameters.AddWithValue("@CustomerID", "A234");

cmd.Parameters.AddWithValue("@CompanyName", "DCB");

InsertUpdateData(cmd);

Executing Update Queries

string strQuery;

SqlCommand cmd;

strQuery = "update customers set CompanyName=@CompanyName where CustomerID=@CustomerID";

cmd = new SqlCommand(strQuery);

cmd.Parameters.AddWithValue("@CustomerID", "A234");

cmd.Parameters.AddWithValue("@CompanyName", "BCD");

InsertUpdateData(cmd);