Google Choice: ASP.NET - Database Access

Sunday, February 12, 2017

ASP.NET - Database Access

ASP.NET allows the following sources of data to be accessed and used:

Databases (e.g., Access, SQL Server, Oracle, MySQL)
XML documents
Business Objects
Flat files

ASP.NET hides the complex processes of data access and provides much higher level of classes and objects through which data is accessed easily. These classes hide all complex coding for connection, data retrieving, data querying, and data manipulation.
ADO.NET is the technology that provides the bridge between various ASP.NET control objects and the backend data source. In this tutorial, we will look at data access and working with the data in brief.

Retrieve and display data

It takes two types of data controls to retrieve and display data in ASP.NET:

A data source control - It manages the connection to the data, selection of data, and other jobs such as paging and caching of data etc.
A data view control - It binds and displays the data and allows data manipulation.

We will discuss the data binding and data source controls in detail later. In this section, we will use a SqlDataSource control to access data and a GridView control to display and manipulate data in this chapter.
We will also use an Access database, which contains the details about .Net books available in the market. Name of our database is ASPDotNetStepByStep.mdb and we will use the data table DotNetReferences.
The table has the following columns: ID, Title, AuthorFirstName, AuthorLastName, Topic, and Publisher.
Here is a snapshot of the data table:

Let us directly move to action, take the following steps:
(1) Create a web site and add a SqlDataSourceControl on the web form.

(2) Click on the Configure Data Source option.

(3) Click on the New Connection button to establish connection with a database.

(4) Once the connection is set up, you may save it for further use. At the next step, you are asked to configure the select statement:

(5) Select the columns and click next to complete the steps. Observe the WHERE, ORDER BY, and the Advanced buttons. These buttons allow you to provide the where clause, order by clause, and specify the insert, update, and delete commands of SQL respectively. This way, you can manipulate the data.
(6) Add a GridView control on the form. Choose the data source and format the control using AutoFormat option.

(7) After this the formatted GridView control displays the column headings, and the application is ready to execute.

(8) Finally execute the application.
Database Result

The content file code is as given:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="dataaccess.aspx.cs" 
   Inherits="datacaching.WebForm1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >

   <head runat="server">
      <title>
         Untitled Page
      </title>
   </head>
   
   <body>
      <form id="form1" runat="server">
         <div>
         
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
               ConnectionString= "<%$   ConnectionStrings:ASPDotNetStepByStepConnectionString%>" 
               ProviderName= "<%$ ConnectionStrings:
                  ASPDotNetStepByStepConnectionString.ProviderName %>" 
               SelectCommand="SELECT [Title], [AuthorLastName], 
                  [AuthorFirstName], [Topic] FROM [DotNetReferences]">
            </asp:SqlDataSource>
            
            <asp:GridView ID="GridView1" runat="server" 
               AutoGenerateColumns="False" CellPadding="4" 
               DataSourceID="SqlDataSource1" ForeColor="#333333" 
               GridLines="None">
               <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
            
               <Columns>
                  <asp:BoundField DataField="Title" HeaderText="Title" 
                     SortExpression="Title" />
                  <asp:BoundField DataField="AuthorLastName" 
                     HeaderText="AuthorLastName" SortExpression="AuthorLastName" />
                  <asp:BoundField DataField="AuthorFirstName" 
                     HeaderText="AuthorFirstName" SortExpression="AuthorFirstName" />
                  <asp:BoundField DataField="Topic" 
                     HeaderText="Topic" SortExpression="Topic" />
               </Columns>
               <FooterStyle BackColor="#5D7B9D" 
                  Font-Bold="True" ForeColor="White" />
               <PagerStyle BackColor="#284775" 
                  ForeColor="White" HorizontalAlign="Center" />
               <SelectedRowStyle BackColor="#E2DED6" 
                  Font-Bold="True" ForeColor="#333333" />
               <HeaderStyle BackColor="#5D7B9D" Font-Bold="True"  
                  ForeColor="White" />
               <EditRowStyle BackColor="#999999" />
               <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
            </asp:GridView>
         </div>
      </form>
   </body>
</html>