Execute SQL Select Query using ADO.Net

Here I will discuss how to get data using SQL Select Query in ADO.Net

Here’s the Function

 

First import the following Namespaces

 

using System.Data;

using System.Data.SqlClient;

 

Now define your Connection string in the Web.Config

<connectionStrings>

<add name=“conString“

 connectionString=“DataSource=.\SQLEXPRESS;Database=Northwind;Integrated Security=true“/>

</connectionStrings>

 

 

Here’s the function

public DataTable GetData(SqlCommand cmd)

{

    DataTable dt = new DataTable();

   String strConnString  

   =System.Configuration.ConfigurationManager.ConnectionStrings[“conString”].ConnectionString;

    SqlConnection con = new SqlConnection(strConnString);

    cmd.Connection = con;

    SqlDataAdapter sda = new SqlDataAdapter();

    cmd.CommandType = CommandType.Text;

    try

    {

        con.Open();

        sda.SelectCommand = cmd;

        sda.Fill(dt);

    }

    catch

    {

        return dt;

    }

    finally

    {

        con.Close();

        cmd.Dispose();

        sda.Dispose();

    }

    return dt;

}

 

 

To call the above function without any parameters

 

protected void Button1_Click(object sender, EventArgs e)

{

    String strQuery = “SELECT * FROM Customers”;

    SqlCommand cmd = new SqlCommand(strQuery);

    DataTable dt = GetData(cmd);

}

 

 

And Now to call the above query using parameters. That I need only those records which have CustomerID as ALFKI. Here I am using Parameterized Queries So that SQL Injections can be avoided.

 

So the function will be called this way.

 

protected void Button1_Click(object sender, EventArgs e)

{

    String strQuery = “SELECT * FROM Customers WHERE CustomerID=@CustomerID”;

    SqlCommand cmd = new SqlCommand(strQuery);

    cmd.Parameters.AddWithValue(“@CustomerID”, “ALFKI”); 

    DataTable dt = GetData(cmd);

}

 

 

You can bind the Returned DataTable to any DataBound Controls like Gridview, DataGrid, DropDownList and others in the following way

DataTable dt = GetData(cmd);

GridView1.DataSource = dt;

GridView1.DataBind();

 

Please do comment If you want something to be added in the Blog which you want to learn.