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)