Introduction To Data Access With Aspnet

Data access is an intergral part of creating dynamic web content. rDbMS (Relational Database Management System) storage is very commonly used in Web applications. ASP.NET provides data access to RDBMS storage via ADO.NET.

BASICS OF DATA ACCESS

In this chapter, you will look at how to work with databases in ASP.NET using ADO.NET technology. The origin of where the data is provided is called the data source. To get to this data, you can use a couple of controls that are supplied by ADO.NET. These controls include a Connection object, which is used to make the connection to the database. An important property of the Connection object is the Connection string, which is used to specify how to open the database. Another control used for data access is the Command object, which is especially useful when executing a stored procedure. The Command object enables you to specify the parameters for the stored procedure. When you have a connection, you can then use a control called a Data Adapter to retrieve the data. You can use the DataSet object as the target for this data.

After you have the data from the data source, you will probably want to display this data on the Web page. To do this, you can bind the data to a control. There are several controls to which you can bind data. These include the Repeater, DataList, and DataGrid controls. The simplest control is the Repeater control. It enables you to create simple header, footer, and item templates for data. For more complex lists, you can work with the DataList control. This control allows for more complex formatting of your data. The DataGrid control is the most functional control available in ASP.NET for dealing with data. You have many formatting options for the DataGrid control, including paging and support for editable columns.

CONTROLS USED TO ACCESS A DATABASE

Connection

Connections are the starting point to data access. You need to give connection parameters to establish a connection. After those parameters are set (typically through the ConnectionString property), you will invoke the Open method to create an active connection. The following table provides Key Properties and Methods of the SQLConnection class, which is the class for connections with Microsoft SQL Server databases.

PROPERTY

DESCRIPTION

ConnectionString

(read/write) String used to open a SQL Server database.

Database

(read) Name of the current (or soon to be) connected database.

DataSource

(read) Name of SQL Server instance with which to connect.

ACCESS DATA WITH ASP.NET

CONTROLS USED TO ACCESS A DATABASE (CONTINUED)

Command

The Command object in ADO.NET is very similar to its cousin ADO. Commands are important for stored procedures and you still want to use stored procedures in your data access routines (for both security and performance reasons). The following table provides Key Properties and Methods of the SQLCommand class, which is the class for commands with Microsoft SQL Server databases.

PROPERTY

DESCRIPTION

CommandText

(read/write) The T-SQL statement or stored procedure to execute at the data source.

CommandType

(read/write) A value indicating how the CommandText property is to be interpreted.

Connection

(read/write) The SqlConnection used by this instance of the

SqlCommand.

Parameters

(read) The SqlParameterCollection.

METHOD

DESCRIPTION

Cancel

Cancels the execution of a SqlCommand.

CreateParameter

Creates a new instance of a SqlParameter object.

ExecuteNonQuery

Executes a T-SQL statement against the Connection and returns the number of rows affected.

DataAdapter

A DataAdapter object bridges the source data and the DataSet so that retrievals and updates can occur. The following table provides Key Properties and Methods of the DataAdapter class.

PROPERTY

DESCRIPTION

AcceptChangesDuringFill

(read/write) A value indicating whether AcceptChanges is called on a DataRow after it is added to the DataTable.

TableMappings

(read) A collection that provides the master mapping between a source table and a DataTable.

METHOD

DESCRIPTION

Fill

Adds or refreshes rows in the DataSet to match those in the data source using the DataSet name, and creates a DataTable named Table.

GetFillParameters

Retrieves the parameters set by the user when executing a SQL SELECT statement.

Update

Calls the respective INSERT, UPDATE, or DELETE statements for respective action in the specified DataSet from a DataTable named Table.

CONTROLS USED TO DISPLAY DATA

Repeater Control

The simplest control for binding data is the Repeater control that enables you to create simple header, footer, and item templates for displaying data.

DataGrid Control

The DataGrid control is the most functional control available in ASP.NET for dealing with data. You can define different types of columns with the DataGrid.

DataList Control

For more complex lists, you can work with the DataList control. Like the Repeater control, it uses templates for specifying how to display data.

You can use the Repeater Web server control to format data into custom lists. To format the lists, you can use templates to define the layout of the data. This includes headers and footers, as well as alternating or separate rows of data.

You can work with the Repeater control by adding <ASP:REPEATER RUNAT="Server"> to your ASP.NET Web page. You should give the control an id so that you can reference it in code. To bind the control to the data source, you must first set the DataSource property and then call the DataBind method. You can then use the templates to format the output.

The Repeater control can support several different types of templates. Use an itemTemplate when you want to format output for each row in the data source. The ItemTemplate is a required template. The AlternatingltemTemplate formats output as well, although the template is applied only to every other row of the data. You can use the HeaderTemplate and FooterTemplate for output that comes before the items and after the items, respectively. The SeparatorTemplate is used to specify formatting between rows of data. For example, you can specify a horizontal line in HTML.

DISPLAY REPEATING DATA

DISPLAY REPEATING DATA

D Open

GenericTemplate.aspx from the Code Templates directory.

0 Add a message to the user.

-0 Add the header template tags and begin a new table with a heading.

0 Add the footer template tags and end the table.

D Open

GenericTemplate.aspx from the Code Templates directory.

0 Add a message to the user.

^0 Declare a Repeater control on the page and give it an ID.

-0 Add the header template tags and begin a new table with a heading.

'—0 Add the item template tags and output the data items from the Repeater control.

0 Add the footer template tags and end the table.

ACCESS DATA WITH ASP.NET

You can use the following Repeator control to display the data in comma delimited format by binding the Reaper control to an array list and using a comma as the SeparatorTemplate. The first set of code can be put into the Page_Load event and the second set into a server-side form to perform the databinding. See the full source code at Chapter06/Code/ASPRepeater_ai.aspx.

-0 Add the Page_Load function to the page.

™ Save the file and request it from the Web server.

■ A properly formatted table appears with the data from the array list.

-0 Add the Page_Load function to the page.

Create an array list.

I Add some travel goals to the array list.

—± Set the data source for the Repeater control to the array list.

L-E b ind the Repeater control to the array list data.

™ Save the file and request it from the Web server.

■ A properly formatted table appears with the data from the array list.

You can use the DataList Web server control to format complex lists. This control shares many similar features with the Repeater control, but it has additional features, such as specifying the direction of the list as well as some additional template-formatting options.

You can work with the DataList control by adding <ASP:DATALIST RUNAT="Server"> to your ASP.NET Web page. Give the control an id attribute so that you can reference it in code. To bind the control to the data source, you should first set the DataSource property and then call the DataBind method. You can then use the templates to format the output.

The DataList supports a number of templates. For example, each row in the data source utilizes the itemTemplate. The AlternatingltemTemplate formats every other row in the datasource. If you want to have a table use different colors on alternating rows to aid in the readability of the table, you can use the AlternatingltemTemplate. You can use the HeaderTemplate and FooterTemplate to format a header and footer. You can specify what should be output between each row with the Separatoritem template. SelecteditemTemplate describes the format when a user selects an item. EdititemTemplate describes the format when a user edits an item.

DISPLAY COMPLEX LISTS

DISPLAY COMPLEX LISTS

il Untitled - Notepad

File Edit Format Help

^SCRIPT LANGUAGE-'C#' RUNAT="Server":-voldlPage Load|Qbjeot Sender, EventArgs E) {

Italy",

cBODYs

<FONT FACE-'Verdana">

D Open the

GenericTemplate.aspx from the Code Templates directory.

□ Declare a DataList control on the page and give it an ID.

il Untitled - Notepad

File Edit Format Help

^SCRIPT LANGUAGE-'C#' RUNAT="Server":-voldlPage Load|Qbjeot Sender, EventArgs E) {

DataTable datatableGoals - new DataTablef); |

DataRow datarowGoals!

datatableGoals.Columns.Addtnew DataColumnf'Goal". typeof(string))),r

Italy", datarowGoals = datatableGoals.NewRowf); datarowGoals[0] - "Travel to all seven continents"; datatableGoals.Rows.Add(datarowGoals), datarowGoals = datatableGoals.NewRowf); datarowGoals[0] = "Travel to Asia, Including Japan and China"; datatableGoals.Rows.Add(datarowGoals), datarowGoals = datatableGoals.NewRowf);

datarowGoals[0] - "Travel to Europe, Including England, France, Spain, and datatableC-oals.Rows.AddldatarowGoals);_

cBODYs

<FONT FACE-'Verdana">

D Open the

GenericTemplate.aspx from the Code Templates directory.

—0 Add a heading and a message to the user.

□ Declare a DataList control on the page and give it an ID.

—0 Add an item template to the page and output the goal items.

-0 Import the System.Data namespace.

-O Add the Page_Load function.

Create a data table variable.

Create a data row variable.

-E Add a column to the data table.

For each row, add a row and set the value for the Goal column.

ACCESS DATA WITH ASP.NET

You can select from many options when formatting the DataList. Type the following code into a server-side form. See the full source code at

Chapter06/Code/ASPDataList_ai.aspx.

TYPE THIS:

<ASP:DATALIST ID="datalistTravelGoals" RUNAT="Server" BORDERCOLOR="Black" CELLPADDING="5"

FONT-NAME="Verdana" FONT-SIZE="12px"

HEADERSTYLE-FORECOLOR="White"

HEADERSTYLE-FONT-BOLD="True"

HEADERSTYLE-BACKCOLOR="Navy"

ALTERNATINGITEMSTYLE-BACKCOLOR="LightBlue">

RESULT:

This produces a formatted list of the data provided.

% Untitled - Notepad

File Edit Format Help

<%@ImportNamespace-'System.Data" %>

<SCRIPT LANGUAGE="C#" RUNAT="Server"> void Page_Load(Object Sender, EventArgs E) {

DataTable datatableGoals - new DataTable(); DataRow datarowGoals;

datatableGoals.Columns. Add(newDataColumn("Goal",lypeof( string)));

datarowGoals - datatableGoals.NewRowQ;

datarowGoals[0] = "Travel to all seven continents";

datatableGoals.Rows. Add(datarowGoals);

datarowGoals - datatableGoals.NewRow();

datarowGoals[0] = "Travel to Asia, including Japan and China";

datatableGoals. Rows. Add(datarowGoals);

datarowGoals - datatableGoals.NewRow();

datarowGoals[0] = "Travel to Europe, including England. France. Spain, and

Italy";

datatableGoals .Rows . Add(datarowGoals);

DataView dataviewGoals ~ new DataView(datatableGoals);|

datalistTravelGoals.DataSource = dataviewGoals ;

datali stTravelGoals DataBi nd();|

Create a DataView from the table that was created.

—E Set the data source for the data list.

™ Bind the data list to the data source.

£ Save the file and request it from the Web server.

■ A properly formatted table appears with the data from the data table.

Was this article helpful?

0 0

Post a comment