May 29, 2012

ASP.NET: Database Access

1 comment
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. We will come to ADO.Net in due time. In this tutorial, we will look at data access and working with the data without going into the details of its inner workings.

Retrieve and display data:

It takes two types of data controls to retrieve and display data in ASP.Net:
  1. A data source control . it manages the connection to the data, selection of data and other jobs like paging and caching of data etc.
  2. 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 details later. In this section, we will use a SqlDataSource control to access data and a GridView control to display and manipulate data.
We will also use an Access database, which has 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:
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 Link.
Configure Data Source
(3) Click on the New Connection button to establish connection with a database.
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:
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 run.
GridView control
(8) Finally Run the application
Database Result
The content file code:
<%@ Page Language="C#" 
         Inherits="datacaching.WebForm1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 

<html xmlns="" >
<head runat="server">
<title>Untitled Page</title>
<form id="form1" runat="server">
<asp:SqlDataSource ID="SqlDataSource1" 
   "<%$ ConnectionStrings:ASPDotNetStepByStepConnectionString%>" 
   "<%$ ConnectionStrings:
        ASPDotNetStepByStepConnectionString.ProviderName %>" 
   SelectCommand="SELECT [Title], [AuthorLastName], 
                         [AuthorFirstName], [Topic] 
                  FROM [DotNetReferences]">
<asp:GridView ID="GridView1" 
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<asp:BoundField DataField="Title" HeaderText="Title" 
SortExpression="Title" />
<asp:BoundField DataField="AuthorLastName" 
SortExpression="AuthorLastName" />
<asp:BoundField DataField="AuthorFirstName" 
SortExpression="AuthorFirstName" />
<asp:BoundField DataField="Topic" 
HeaderText="Topic" SortExpression="Topic" />
<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" />

1 comment :