New Document
ASP.NET Data Access

ASP.NET includes features that enable you to add data access to your ASP.NET Web pages with little or no code. You can connect to databases, XML data and files, and business objects as data sources. You can then display data by using a variety of controls that provide great flexibility in how you present data on the page. We suggest the following progression of documentation to help you navigate through the related topics.

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.

First design the web page using web server controls, for example form, button, labels. Then connect your buttons to subroutines, using onclick="name of subroutine". After that Import the libraries, for example < %@ Import Namespace="System.Data" %>


At the end make a script block and put your sub routines inside, like this

< script runat="server">


sub AddProd(Sender as object, e as EventArgs)


'All programming goes here


end sub

Add following 5 steps to above mentioned sub routine

5 step to make any programming logic in a sub routines

'Step1: Make a connection to databse, declare an object of SQLConnection Class and pass it's argument

'Step2: Pass a query to database, declare an object of SQLCommand Class and pass it's argument

'Step3: Open the connection

'Step4: Run your query

'Step5: Close your connection

Retrieve and display data:

The technology you use to connect to a relational database in ASP.NET code that runs on a server is ADO.NET. ADO.NET communicates with a database management system (DBMS) such as SQL Server or Oracle by using data provider software. Microsoft data providers enable you to connect to the following databases:

SQL Server, including SQL Server Express and LocalDB

SQL Server Compact

Any database that supports ODBC or OLEDB and is suitable for use in a web application

You can also get data providers from third-party software vendors. Some popular relational databases that you can get data providers for include MySQL, SQLite, Oracle, and DB2. For information about data providers that are available, see .NET Framework Data Providers and ADO.NET Data Providers.

If you're choosing a relational database for an ASP.NET web application and you don't have special needs that dictate a different choice, choose SQL Server. Some reasons for choosing SQL Server include the following:

SQL Server is supported by Microsoft.

SQL Server is integrated with other Microsoft data access technologies such as the Entity Framework. See Object-Relational Mappers later in this topic.

Visual Studio provides built-in tools for working with SQL Server. SQL Server Data Tools (SSDT), enables you to create databases, manipulate schema and data, generate and run scripts, debug, and deploy databases and database updates.

Visual Studio includes web deployment features that are designed to facilitate deploying SQL Server databases along with web projects. For more information, see Configuring Database Deployment in Visual Studio in ASP.NET Web Site Project Deployment Overview.

The Visual Studio web project templates use SQL Server for the ASP.NET membership database by default.

Nearly all ASP.NET documentation on the MSDN web site, ASP.NET web sites, and in blog posts uses SQL Server. Relatively little documentation that would help you get started and resolve difficulties is available for other databases.

NoSQL databases are alternatives to relational databases. NoSQL databases can provide advantages over relational databases for applications that manage large volumes of unstructured data. The Windows Azure Table storage service is an example of a NoSQL database management system. Providing guidance on choosing a NoSQL database is outside of the scope of this document.

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.

SqlDataSourceControl

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

AutoFormat

(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#" 
         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>


Previous                                                                                                                                                       Next

Back to Top