Sort Data From A Sql Database

ASP.NET Web pages provide some nice sorting features that you can use when working with SQL Data. When you work with large sets of data, it is very important to have sorting capabilities.

First, you need to retrieve the desired data into a DataView using a DataSet. See page 132 to see how a DataSet is created. After you have this data in your DataView, you can use the Sort property to specify the column on which to sort. After you sort the data, you can set the data source for the control to the sort data and bind the data.

The DataGrid control provides the ability to sort data by clicking on column headers. This is a nice feature, and can easily be done by creating an event procedure for the DataGrid's OnSortCommand event. In this event, you capture the SortExpression on the DataGrid control and pass it to a function that would sort the data and rebind to the DataGrid control. You can control the direction of the sort by appending ASC (for ascending order) and desc (for descending order) to the end of the Sort property of the DataView that you use for binding to the DataGrid control.

^ORj^Aü^ROM^OLiATABASE

% Untitled - Notepad

^ORj^Aü^ROM^OLiATABASE

% Untitled - Notepad

D Open the

GenericTemplate.aspx from the Code Templates directory.

□ Add a DataGrid control to the page and set its properties.

—0 Import the System.Data and System.Data. SqlClient namespaces.

^O Create a SQLConnection object and use a connection string to connect to the database.

Create a SQLDataAdapter and set the SQL statement to retrieve business-type books using the SQLConnection object.

D Open the

GenericTemplate.aspx from the Code Templates directory.

□ Add a DataGrid control to the page and set its properties.

—0 Import the System.Data and System.Data. SqlClient namespaces.

^O Create a SQLConnection object and use a connection string to connect to the database.

Create a SQLDataAdapter and set the SQL statement to retrieve business-type books using the SQLConnection object.

—0 Create a DataSet object.

'""E Populate the DataSet object.

ACCESS DATA WITH ASP.NET

You can sort data by using the SortExpression Property on the DataGrid control. The first set of code goes into a server-side form and the second part goes into a server-side script block. Please see Chapter06/Code/ASPSort_ai.aspx for the full source.

TYPE THIS:

<ASP:DATAGRID ID="datagridTitles" RUNAT="Server" ALLOWSORTING="True" OnSortCoKmand="datagridTitles_Sort"/> protected void datagridTitles_Sort (Object sender, DataGridSortCommandEventArgs e) { BindGrid(e.SortExpression);

public void BindGrid(String sortfield) {

SqlDataAdapter sqldataadapterTitles = new SqlDataAdapter("select title, "from titles where type='business'", sqlconnectionPubs); DataSet datasetTitles = new DataSet(); sqldataadapterTitles.Fill(datasetTitles, "titles"); DataView Source = datasetTitles.Tafoles["titles"].DefaultView; Source.Sort = sortfield; datagridTitles.DataSource=Source; datagridTitles.DataBind();

notes, price"+

RESULT:

This produces a DataGrid control that sorts the table by the column that is clicked.

% Untitled Notepad

File Edit Format Help

■"System.Data" %=■ :"System Data.SqICIient"

<%@ Import Namespace^ <%@ Import Namespace= <HTML=-<HEAD>

«SCRIPT LANGUAGE-"C#" RUNAT="Seiver"> protected void Page_Load(Object sender, EventArqs e) {

SqIConnection sqlconnectionPubs = new SqlConnection("server-(local)\\NetSDK;uid-QSUser;pwd-QSPassword;database-pub

SqlDataAdapter sqldataadapterTitles = new SqIDataAdapterf'select title, notes, price from titles where type-business"', sqlconnectionPubs);

DataSet datasetTitles = new DataSet(); sqldataadapterTltles.FIII(datasetTltles, "titles");

DataView dataviewTitle = datasetTitles.Tables["titles"1.DefaultView; dataviewTitle Sort - "title"; I

datagridTitles.DataSource=dataviewTitle; datagridTitles.C'ataBindOj

b— Create a DataView based on the DataSet that was filled.

Sort the DataView on the title column with the Sort command.

1 http^/localhost/ASPSoit.aspH Microsoft Internet Explorer

File Edit View Favorites Tools Help

+>B«k - * • J Ü ál [T Personal Bar J>jSearch _*J Favorites & íi" J SÍ si S »

Address http://looalhostMSPSort.aspK

Welcome to mylifetimegoals.com

Here are the are some books that will help you reach your career goals.

Cooking with Computers: Surreptitious Balance Sheets

Helpful hints on how to use your electronic resources to the best advantage.

11.95

Straight Talk About Computers

Annotated analysis of what computers can do for you: a no-hype guide for the critical user,

19.99

The Busy Executive's Database Guide

An overview of available database systems with emphasis on common business applications. Illustrated.

19.99

You Can Combat Computer Stress!

The latest medical and psychological techniques for living with the electronic office, Easy-to-understand explanations.

S] Done

E Set the DataSource and DataBind properties of the DataGrid on the page.

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

■ The table is sorted based on the Title column.

Was this article helpful?

0 0

Post a comment