Accessing SQL Server on ASP.NET Web Application using ADO.NET

Accessing SQL Server on ASP.NET using ADO.NET

ADO.NET is a set of components that can be used to access data and data services. The objects of ADO.NET that will be used in this post are:

  • Connection: Provides a connection used to communicate with the data source.
  • DataAdapter: A bridge used to transfer data between a Data source and a DataSet object.
  • DataSet: Stores query result from DataAdapter.

On this post, you will see how to develop an ASP.NET web application that query data from SQL Server 2005 and display the result on a GridView object. I have provided both VB.NET and C# programming languages.

Step-by-step

  1. Create a connection string in web.config. I decide to put the connection string in web.config so that I can re-use the connection string in other web form.
    <connectionstrings>
    		<add name="Northwind" 
           connectionString="Data Source=BKKSQL001\INSTANCE01;
           Initial Catalog=Northwind;
           Integrated Security=True" 
          providerName="System.Data.SqlClient"/>
    </connectionstrings>

    Attributes explanation:

    • name: A connection string name.
    • connectionString: A connection string which is used to connect to a database. On this example, it connects to Northwind database on BKKSQL001\INSTANCE01.
    • providerName: A provider name that is used.

    A Connection String

  2. On a Web Form, imports some namespaces which are required for working with SQL Server. System.Web.Configuration is use to read values from web.config.
    'Visual Basic
    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Web.Configuration
    //C#
    using System.Data;
    using System.Data.SqlClient;
    using System.Web.Configuration;
  3. Use a GridView object to display the query result. Set the name of GridView to “gridView1“. Copy and paste the code below to Page_Load method.
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    
    'Visual Basic
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
     
            Dim connStr As String = WebConfigurationManager.ConnectionStrings("Northwind").ConnectionString
            Dim conn As New SqlConnection(connStr)
            conn.Open()
     
            Dim sqlProducts As String = "SELECT ProductID, ProductName, UnitPrice FROM Products"
     
            Dim da As New SqlDataAdapter(sqlProducts, conn)
            Dim ds As New DataSet()
            da.Fill(ds, "Products")
     
            gridView1.DataSource = ds.Tables("Products")
            gridView1.Databind()
    End Sub
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    
    //C#
    protected void Page_Load(object sender, EventArgs e)
        {
            string connStr = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
     
            string sqlProducts = "SELECT ProductID, ProductName, UnitPrice FROM Products";
     
            SqlDataAdapter da = new SqlDataAdapter(sqlProducts, conn);
            DataSet ds = new DataSet();
            da.Fill(ds, "Products");
     
            gridView1.DataSource = ds.Tables["Products"];
            gridView1.DataBind();
        }

    Code Explanation:

    • Line 4-6: Get a connection string value from web.config. Then, open a connection using the connection string to a SQL Server.
    • Line 8: Create a variable “sqlProducts” which stores SQL query from Products table.
    • Line 10-12: Create a SqlDataAdapter object “da” to execute the query. Then, create a DataSet object “ds” to hold the result from object “da” and set name to “Products“.
    • Line 14-15: Display data from object “ds” on a GridView object “gridView1“.
  4. Run the project. You will see the result as figure below.
    Northwind's Products table

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.