Parameterized Queries

Parameterized Queries are those in which values are passed using SQL Parameters.

 

Benefits

The prime benefit of parameterized Queries is to protect the database 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

You will need to import the following two namespaces

    

   

C#

using System.Data;

using System.Data.SqlClient;

 

VB

Imports System.Data

Imports System.Data.SqlClient

 

 

Select Queries

                       

The following function will be used to execute the select queries.

C#

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();

    }

}

 

 

                      

  VB.Net

 

Public Function GetData(ByVal cmd As SqlCommand) As DataTable

        Dim dt As New DataTable

        Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings("conString").

ConnectionString

        Dim con As New SqlConnection(strConnString)

        Dim sda As New SqlDataAdapter

        cmd.CommandType = CommandType.Text

        cmd.Connection = con

        Try

            con.Open()

            sda.SelectCommand = cmd

            sda.Fill(dt)

            Return dt

        Catch ex As Exception

            Response.Write(ex.Message)

            Return Nothing

        Finally

            con.Close()

            sda.Dispose()

            con.Dispose()

        End Try

 End Function

 

 

The function executes the SQL Query and then returns the DataTable.

 

Execute a Simple Select Query

 

 

C#

 

string strQuery = "select * from customers";

SqlCommand cmd = new SqlCommand(strQuery);

DataTable dt = GetData(cmd);

GridView1.DataSource = dt;

GridView1.DataBind();

 

VB.Net

Dim strQuery As String = "select * from customers"

Dim cmd As New SqlCommand(strQuery)

Dim dt As DataTable = GetData(cmd)

GridView1.DataSource = dt

GridView1.DataBind()

 

The above code executes the Query and binds the result to the GridView.

   

  

Execute SQL Query with Filter Condition

 

C#

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();

 

VB.Net

Dim strQuery As String = "select * from customers where city = @city"

Dim cmd As New SqlCommand(strQuery)

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

Dim dt As DataTable = GetData(cmd)

GridView1.DataSource = dt

 

The above query executes the SQL Query that filters the record based on City.

You will notice that the @city which is the parameter for the query.

 

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

 

The statement assigns the value of textbox txtCity to the parameter @City

 

Insert - Update Queries

The following functions will be used to execute Insert and Update Queries.

C#

            

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();

    }

}

 

VB.Net

Public Function InsertUpdateData(ByVal cmd As SqlCommand) As Boolean

        Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings("conString").

ConnectionString

        Dim con As New SqlConnection(strConnString)

        cmd.CommandType = CommandType.Text

        cmd.Connection = con

        Try

            con.Open()

            cmd.ExecuteNonQuery()

            Return True

        Catch ex As Exception

            Response.Write(ex.Message)

            Return False

        Finally

            con.Close()

            con.Dispose()

        End Try

End Function

 

 

 

 

Execute Insert Queries

 

C#

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);

 

VB.Net

Dim strQuery As String

Dim cmd As SqlCommand

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

cmd = New SqlCommand(strQuery)

cmd.Parameters.AddWithValue("@CustomerID", "AZNL")

cmd.Parameters.AddWithValue("@CompanyName", "ABC")

InsertUpdateData(cmd)

 

 

Executing Update Queries

 

C#

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);

 

VB.Net

Dim strQuery As String

Dim cmd As SqlCommand

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

cmd = New SqlCommand(strQuery)

cmd.Parameters.AddWithValue("@CustomerID", "AZNL")

cmd.Parameters.AddWithValue("@CompanyName", "XYZ")

InsertUpdateData(cmd)

 

You can download the Sample source code in VB.Net And C# here

SQLQeriesADO.Net.zip (536.56 kb)