Insert, Update, Select Using SqlCommand Parameters

Spread the word

This article explains how to query the SQL Server Database using C# and VB.Net using parameterized queries that allows to prevent SQL Injetion attacks.

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.NET

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)

Download the code sample in C# and VB.NET

Download C# & VB.NET Code Sample