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

Comments

Post a Comment

Popular posts from this blog

Auto Scroll in Common Controls

-
This example shows, how to programatically autoscroll WinForm controls TextBox, ListBox, ListView, TreeView and DataGridView. Autoscroll to the end of box or view is useful. For example, in situations when you use one of these components as the output log window. Usually you add the items to the box or view and you want to be sure that the recently added item is visible without the necessary to do it manually. Though it is not difficult to solve this problem, .NET doesn't provide any unified way how to do it. TextBox autoscroll Code: textBox1.SelectionStart = textBox1.Text.Length; textBox1.ScrollToCaret(); ListBox autoscroll Code: listBox1.SelectedIndex = listBox1.Items.Count - 1; listBox1.SelectedIndex = -1; ListView autoscroll Code: listView1.EnsureVisible(listView1.Items.Count - 1); TreeView autoscroll Code: treeView1.Nodes[treeView1.Nodes.Count - 1].EnsureVisible(); DataGridView autoscroll Code: dataGridView1.FirstDisplayedCell = ...
Read more

Convert typed library (.tlb) to .net assembly?

-
Sometimes we have to use the legacy code(C, C++) in .Net projects which is provided by third party vendors. Most of the times these guys will provide, equivalent .net framework typed library (.tlb) of that legacy code. Then, how to refer those in our .net projects and make use of functionality provided by it?   Follow the below steps, Keep ".tlb" file which is nothing but legacy typed library Open windows command prompt Go to directory, " C:\Program Files (x86)\Microsoft Visual Studio 8\SDK\v2.0\Bin " or     "C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin". It depends on which version of visual studio you had. Enter the following and execute, TlbImp.exe "E\MyLibrary.tlb" . Here, replace "E:\MyLibrary.tlb" with path to your tlb file. It will import the .net framework type library to assembly.i.e, .dll and it can be found at path "C:\Program Files (x86)\Microsoft Visual Studio 8\SDK\v2.0\Bin" with name ...
Read more

Disable close button on form - C#

-
In one of my projects, I had to implement a form with Close[X] button Hide, so that the user cannot leave the form until it finishes the data processing. From the form designer window in VS.Net 2005, it is possible to hide the Minimize box and Maximize box. But there is no property called Close or CloseBox. Then I had tried with by setting form property ControlBox = false . With this i am able to achieve hiding the Close/Minimize/Maximize buttons. But at the same time there is a side effect.i.e., Form icon(which is generally displayed on form title bar)  also hidden.  So after little bit investigation came up with the following hack to get,  i)  disable the close button  ii) displaying the form icon To achieve this, we have to override CreateParams property of Form base class as follows, Code: private const int CP_DISABLE_CLOSE_BUTTON = 0x200; protected override CreateParams CreateParams { get ...
Read more