One of the great improvements introduced with ASP.NET 2.0 was in the Datasource controls. For those of us who’ve been around the web development game for a few years, one of the most tedious aspects of database driven web site development was the actual business of setting up the code required to access a data source and display data for viewing or editing.
The Datasource control family handles the management of this particular aspect of web site development. I say ‘family’ because there are a number of Datasource controls available, each designed to act as an intermediary between ASP.NET data-capable controls and the particular data sources of interest. For example, there is a SQLDatSource, AccessDataSource, one for XML, etc. In a few lines of code it allows a basic but highly functional data management screen to be put together for any single table in a database.
In this article we’ll look at the basic functionality offered by the SQLDatasource control, and examine the role of the DataSource control in linking data resources to a user interface.
The first requirement is a database table in a SQL Server database. The SQL Server Express Edition is perfect for this. Create a database and a table called ‘names’ with the following structure:
| Field Name | Description | Type | Length |
|---|---|---|---|
| ID | Identity field (Primary Key) | Int | Identity |
| FirstName | String field holding first name | Varchar | 30 |
| LastName | String Field holding last name | Varchar | 30 |
You’ll also need the connection string for the database. Populate the table with a few records.
To be useful, a page containing a SQLDataSource control will also contain a presentation control – such as a GridView, FormView or DetailsView control.
Setting up such a page involves:
The DataSource controls act as adaptors between the user interface and the data sources. There is nothing that is done with DataSource controls that can’t be done with ADO.NET code called explicitly from within your web page – similar to the traditional method of accessing databases via ADO in Classic ASP.
The first piece of code, below, gets data back from the table and displays it in the gridview control. I’ve called the Connection String ‘Demo-SQL’. In this example, the DataSource control performs the select statement used to populate a GridView.
<%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server"> </script> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <asp:SqlDataSource ID="dsNames" runat="server" ConnectionString="<%$ ConnectionStrings:Demo-SQL %>" SelectCommand="SELECT [ID], [FirstName], [LastName] FROM [names]"> </asp:SqlDataSource> <asp:GridView id="grdNames" RunAt="Server" DataSourceID="dsNames" AutoGenerateColumns="False"> <columns> <asp:BoundField HeaderText="ID" DataField="ID" /> <asp:BoundField HeaderText="First Name" DataField="FirstName" /> <asp:BoundField HeaderText="Last Name" DataField="LastName" /> </columns> </asp:GridView> </div> </form> </body> </html>
Let’s take a closer look at the SqlDataSource. Like any control it needs a name – dsNames – and we specify that it is to be run at the server. The ConnectionString property uses a connection string from the web.config file, and finally the SelectCommand property specifies the SQL statement that is executed to perform a SELECT statement against the database.
So far, so good. Setting up a SQLDataSource like this – what’s called declaratively – is the usual way of working with SQLDataSource controls.
But it is possible to work with the DataSource controls programmatically – through code. Take a look at the following listing:
<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
' This routine runs when the Page is initialised and has access to the asp: namespace controls
' on the page.
' First of all get the connection string from the web.config file and assign it to the SQLDataSource
Dim sConnString As String
sConnString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("Demo-SQL").ConnectionString
dsNames.ConnectionString = sConnString
' Now configure the SEECT statement.
dsNames.SelectCommand = "SELECT [ID], [FirstName], [LastName] FROM [names] "
End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="dsNames" runat="server" />
<asp:GridView id="grdNames" RunAt="Server" DataSourceID="dsNames" AutoGenerateColumns="False">
<columns>
<asp:BoundField HeaderText="ID" DataField="ID" />
<asp:BoundField HeaderText="First Name" DataField="FirstName" />
<asp:BoundField HeaderText="Last Name" DataField="LastName" />
</columns>
</asp:GridView>
</div>
</form>
</body>
</html>
Here we simply put a ‘placeholder’ control on the page with the essential attributes set – the runat attribute and a suitable ID. The rest of the configuration is done within the Page_Init subroutine, thus configuring the DataSource programmatically.
You may well wonder what advantage this gives us. The main advantage is that it allows us to configure the DataSource at run time – and so we can change the characteristics of the control – including connection string, database tables accessed, etc. at run time thus allowing us maximum flexibility.
A simple example of this is given below. Here, the buttons determine what order the data is displayed in by changing the SelectCommand. NOTE – it is possible to do this within the GridView, but this shows that there is always more than one way to solve any ASP.NET problem!
<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
' This routine runs when the Page is initialised and has access to the asp: namespace controls
' on the page.
' First of all get the connection string from the web.config file and assign it to the SQLDataSource
Dim sConnString As String
sConnString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("Demo-SQL").ConnectionString
dsNames.ConnectionString = sConnString
' Now configure the SELECT statement.
dsNames.SelectCommand = "SELECT [ID], [FirstName], [LastName] FROM [names] "
End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
' When pressed, re-populate the SelectCommand property with a new SQL statement.
dsNames.SelectCommand = "SELECT [ID], [FirstName], [LastName] FROM [names] ORDER BY [FirstName]"
End Sub
Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs)
' When pressed, re-populate the SelectCommand property with a new SQL statement.
dsNames.SelectCommand = "SELECT [ID], [FirstName], [LastName] FROM [names] ORDER BY [LastName]"
End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="dsNames" runat="server" />
<asp:GridView id="grdNames" RunAt="Server" DataSourceID="dsNames" AutoGenerateColumns="False">
<columns>
<asp:BoundField HeaderText="ID" DataField="ID" />
<asp:BoundField HeaderText="First Name" DataField="FirstName" />
<asp:BoundField HeaderText="Last Name" DataField="LastName" />
</columns>
</asp:GridView>
</div>
<p>
<asp:Button ID="Button1" runat="server" onclick="Button1_Click"
Text="Order by First Name" Width="127px" />
<asp:Button ID="Button2" runat="server" onclick="Button2_Click"
Text="Order by Last Name" Width="135px" />
</p>
</form>
</body>
</html>
The natural progression of this is to remove ALL declarative code from the setting up of the page, and make the whole set up of the SQLDataSOurce programatic. This is shown below – the code does the same as the last example but with no Declarative set up of the DataSource. We still add the SQLDataSource to the page as an ASP.NET control, as the declarative set up of the GridView we have here does require the DataSourceID attribute to be that of a SQLDataSource control.
<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Dim dsNames As SqlDataSource
Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
dsNames = New SqlDataSource
' This routine runs when the Page is initialised and has access to the asp: namespace controls
' on the page.
' First of all get the connection string from the web.config file and assign it to the SQLDataSource
Dim sConnString As String
sConnString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("Demo-SQL").ConnectionString
dsNames.ConnectionString = sConnString
dsNames.ID = "dsNames"
' Now configure the SELECT statement.
dsNames.SelectCommand = "SELECT [ID], [FirstName], [LastName] FROM [names] "
' Now add the created SQLDataSource to the page
Me.Controls.Add(dsNames)
End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
' When pressed, re-populate the SelectCommand property with a new SQL statement.
dsNames.SelectCommand = "SELECT [ID], [FirstName], [LastName] FROM [names] ORDER BY [FirstName]"
End Sub
Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs)
' When pressed, re-populate the SelectCommand property with a new SQL statement.
dsNames.SelectCommand = "SELECT [ID], [FirstName], [LastName] FROM [names] ORDER BY [LastName]"
End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView id="grdNames" RunAt="Server" DataSourceID="dsNames" AutoGenerateColumns="False">
<columns>
<asp:BoundField HeaderText="ID" DataField="ID" />
<asp:BoundField HeaderText="First Name" DataField="FirstName" />
<asp:BoundField HeaderText="Last Name" DataField="LastName" />
</columns>
</asp:GridView>
</div>
<p>
<asp:Button ID="Button1" runat="server" onclick="Button1_Click"
Text="Order by First Name" Width="127px" />
<asp:Button ID="Button2" runat="server" onclick="Button2_Click"
Text="Order by Last Name" Width="135px" />
</p>
</form>
</body>
</html>
In Part 2 read about using SQLDatasource parameters programatically and declaratively.
Get the best ASP hosting with DiscountASP.NET - great value, money back guarantee.
Plug and play ASP membership script that integrates with PayPal to let you charge recurring membership fees.
Find the right ASP.NET Web hosting for you!
About | Contact | Advertise | Feedback | Hire Us | Link
Site developed by Michael Wall - Web Design Belfast N.Ireland.
Copyright © 2001-2012. All rights reserved.