Developing Web Sites using Microsoft .NET Instructor Notes

For use with:

Programming Data-Driven Web Applications with ASP.NET

Author: Donny Mack and Doug Seven

Publisher: Sams

ISBN: 0-672-32106-8

Getting Started

Minimum Requirements

SQL Server 7 (or higher) or the Microsoft SQL Server Desktop Engine (MSDE)

Microsoft Visual Studio.NET 1.1 or SharpDevelop (http://icsharpcode.net)

Microsoft Internet Information Server 1.1 (or higher)

Setting up a Database

Before we can do anything else, we need a database with some information we can work with. We will be using the Microsoft SQL Server Desktop Engine (MSDE) and during this course. It is a scaled down version of SQL Server 2000. In essence, MSDE is the database engine from SQL Server, without any of the extra SQL Server tools, such as the Enterprise Manager. That means we will not have any administrative tools that we can use to help manage the databases stored in the MSDE server.

Therefore the first tool we will install is the SQL Web Data Administrator project that is provided freely by Microsoft. It is an ASP.NET project that runs under Microsoft Internet Information Services (IIS) and provides tools that can be used to manage either SQL Server 7 (or MSDE) databases via a web browser. The project also includes complete source code, which you can study to learn more about SQL Server and/or ASP.NET programming.

To download the project, visit http://www.microsoft.com/sql/msde/ and click on the 'Use the SQL Server Web Data Administrator' link.

In order to install the tool, follow these steps:

Download the project and save it to your hard disk by visiting the site above.

Open Windows Explorer and double-click on the file (setup.msi) you downloaded. This will begin the installation process.

After the project is installed, open up the Internet Services Manager by opening your control panel and then double-clicking the the 'Administrative Tools' icon.

Right-click on the 'Default Web Site' branch within the Internet Services Manager and select 'New -> Virtual Directory'.

Using the wizard, setup the new directory. Enter an alias of 'SqlAdmin' and then browse your computer and find the 'Program Files\Microsoft SQL Server Tools\Microsoft SQL Web Data Administrator\Web' folder that was just created.

The default permissions of 'Read' and 'Run scripts' should work just fine.

Before importing the sample database, we must make one small change. The sample database file is about 4.1 MB in size, but by default, IIS limits the size of files uploaded to the server to 4 MB or smaller. To correct this, edit the file named 'web.config' in the directory where you installed the tool ('Program Files\Microsoft SQL Server Tools\Microsoft SQL Web Data Administrator\Web').

Add the following line to the file (inside the <system.web> section):

<httpRuntime maxRequestLength=”8000” executionTimeout=”120” />

This will increase the maximum upload file size to 8 MB and also increase the default timeout setting for running scripts to 120 seconds.

Once those steps are completed, open your web browser and visit the URL: http://localhost/SqlAdmin/

You should now be able to login using 'Windows Integrated' authentication.

The first thing we want to do is to build the sample database we will be working with for this class by following these steps:

Select 'Import' from the menu on the left.

Browse to the file as directed by your instructor.

Click on the 'Import' button. (This will take a minute or so to complete...)

You should now find a new database named 'Music' that has the following tables (some of them do not have any data):

album

artist

customer

download

song

If desired, you may browse the database by clicking on 'Databases' under the Server Tools menu at the left and then clicking on the new 'Music' database. Take a few minutes to get familiar with the database information.

NOTE: The virtual directory we setup above links to the final compiled version of the project, which cannot be edited or viewed.

If you wish to view the source code and/or edit the project using Visual Studio.NET, you must create a virtual directory named 'SqlAdmin_1' that is connected to the 'Program Files\\Microsoft SQL Server Tools\Microsoft SQL Web Data Administrator\Samples\SqlWebAdmin' folder.

After doing that, you can open the project within Visual Studio.NET by using the 'Open Project' option and browsing to the same folder. The project file is named 'SqlWebAdmin.csproj'.

Using ASP.NET

Chapter 1: What is ASP.NET?

The Major Differences Between ASP and ASP.NET

While at first glance, ASP.NET seems to be similar to ASP, it is a total redesign in an object-oriented system. This makes it simpler, richer and more available to all programmers.

Strong-Typed Languages

A strongly typed language makes programming easier because instead of relying on hard-coded strings to identify fields, columns or records, the information can instead be organized into classes. This enables the .NET compiler to more easily perform error checking and also allows many operations to be automated. In addition, advanced features such as auto-completion can be added to the development environment which speeds development time.

Finally, the linear top-to-bottom programming style of standard ASP scripts can be replaced by event-driven objects. This allows you to separate code that deals with business logic from the presentation code much easier.

ADO vs. ADO.NET

While ADO was an improvement over older technologies such as RDO and DAO for accessing databases, it still suffered from one flaw, namely it uses a persistent connection to the database server. Since dynamic web pages often need to connect to the server repeatedly, techniques such as connection pooling were invented to relieve some of the burden on the database server.

However, what is really needed is a way for ASP to retrieve the data it needs and then use it to generate the web page. This is called operating in disconnected mode. That is the biggest benefit of ADO.NET over standard ADO. The DataSet class implements the disconnected model of database access which can speed up generating web pages from information stored in a database. Basically a DataSet is an in-memory copy of a portion (or all) of a database. The information in a DataSet can be synchronized with the real database as needed, but by caching the information within the web server, a tremendous speed boost can be achieved.

Server Controls

Traditional ASP programming required functions to be written to handle user generated events such as validating user input. Often time programmers had to use some kind of scripting language, such as JavaScript, to achieve this. This meant the programmer not only had to have a good understanding of ASP programming, but also scripting languages.

In ASP.NET, events can be sent back to the server where a server control manages the information flow. Since ASP.NET server controls can be used from any .NET compatible language, programmers will have less need to write functions in scripting languages, although that is still possible to do. These HTML server controls make the developers job easier.

XML and SOAP Support

In recent years, XML has become a widely used way to format information into a non-proprietary text format. Both ASP.NET and ADO.NET support using XML to persist and transmit data from component to component. This means that any operating system or programming language with support for XML can work with the data in that format.

The Simple Object Access Protocol (SOAP) uses XML formatted data to make function calls across distributed networks. XML is just the way the request is made and the response is formatted. Web Services make objects (and their functions) available by sending a SOAP request through a web server which allows a much easier way of granting access through firewalls and security devices than other distributed object technologies such as DCOM and CORBA.

Breaking Down the Parts of ASP.NET

Server Controls

ASP.NET includes a large number of stock server controls for performing common operations such as validating user input, database driven tables and so on. These server controls come in two major categories: HTML server controls and ASP.NET server controls

HTML server controls

All HTML elements can be converted into an HTML server control by adding the runat=”server” attribute to the control. When you that, ASP.NET will create an instance of the control for server-side processing. Some interesting things happen when you do that:

The HTML elements become visible to the server and can be access within your program.

The object model for the server control closely resembles the HTML element. This allows you to change control attributes by referencing its ID attribute directly.

Automatic statement management of the page elements is enabled. This means controls will retain their values on the round trip from the server to the client.

The element can be used with data binding to connect the control to values in a database.

The element can still be used with client-side scripting languages, such as JavaScript.

ASP.NET server controls

Some kinds of controls using within ASP.NET do not have an equivalent HTML counterpart. These kinds of controls are only useful within the ASP.NET program, but will translate themselves into the appropriate HTML elements on-the-fly. For example, the ASP.NET Calendar control must be translated into tables, rows, cells and hyperlinks that look like a calendar when viewed with a web browser.

The differences between ASP.NET controls and HTML controls are:

Template support for some controls.

A rich set of programming capabilities, including raising events, caching, and access to many properties, methods and events not supported by HTML controls.

Browser compatibility. The .NET Framework will examine the capabilities of the web browser on-the-fly and can render the output into either HTML 3.2, or HTML 4.0 and JavaScript.

ASP.NET server controls are subdivided into four groups:

Intrinsic controls which render themselves as basic HTML elements, such as <span> or <input type=”text”>. They will maintain their state automatically.

List controls that render themselves as a variety of lists. Using a data source they can be set to render lists or tables of data.

Validation controls which verify user input, such as required fields, range validation, or custom validation.

Rich controls that are rendered as complex HTML structures and can include client-side scripts, such as the Calendar control.

Server-Side Event Handling

Since ASP.NET pages are compiled at runtime using Just-In-Time (JIT) compilers, the page is really a structure of .NET objects. This provides rich features and control over all elements of the page, including accessing properties and methods of the .NET objects. Since the page itself is an object, it also has properties, methods and events. The developer can create custom event handlers and methods to be executed at the server much easier than the old ASP techniques.

Multiple Language Support

Since .NET supports multiple programming languages include VB.NET, C# and C++, developers are free to use the language they are most comfortable with. The language compiler will translate the code into MSIL, resulting in almost exactly the same output, no matter which language the page was written in.

Web Services

A web service allows large sites to provide a new way to access information from the web server. Instead of being forced to use a web browser to access the company's web pages, a web server allows companies to publish an alternate method of accessing the information.

By using a web services, the client can issue requests against the company's server and receive a response that is not a web page, but just the data. This supports developing richer client-side programs than can be created just using the web browser itself.

Although HTTP is still used to exchange data between client and server, the information itself is formatted using XML, most often with SOAP as the request/reply mechanism. The Web Services Description Language (WSDL) can be used to describe the services offered and can be used to automatically generate client-side code that can make use of the service.

This is an exciting new technology that is growing in popularity very quickly.

Session State Management

State information can be hard to manage in traditional web development projects. The creation of a web server farm complicates the problem of state management. Often times state is maintained using cookies, but since they are often blocked, some web pages manage state using hidden fields instead, but the programmer had to manage the state information in the code.

ASP.NET can manage state information automatically using a hidden ViewState field. This hidden field is sent back to the server by the client and is automatically updated to store the state of all controls marked as runat=”server”.

In addition, web server farms can use the ASP.NET State Store or an SQL Server database to manage state information. Both methods only require adding a minimal amount of code to your pages.

Caching

Caching can be used within ASP.NET to improve the web server's performance by locally caching dynamically generated web pages so they do not have to be recreated from scratch over and over again. A timeout settings tells the server how long to hold the page in cache before it needs to be rebuilt from scratch again.

ASP.NET has two types of caching:

Output caching—The caching of a dynamic response generated by a request.

Data caching—Programmatic caching of arbitrary objects across HTTP requests.

ASP.NET also has the ability to cache just a portion of a web page. This is called fragment caching and is useful for caching part of a page, but regenerating other parts every time.

Chapter 2: What is ADO.NET?

ADO.NET Explained

ADO.NET is a standards-based programming model for creating distributed, data-driven applications.

The heart of ADO.NET is the DataSet class. It is an in-memory database that is a collection of tables, records, and relationships. Internally, a DataSet stores its information formatted into XML. I like to think of the DataSet as a memory-only version of the Jet database engine used by Access.

The Benefits of ADO.NET

Interoperability

By using XML as the underlying data format, ADO.NET can be used by any programming language that can understand how to read XML data. Since all .NET languages support XML, ADO.NET can be used by any of them.

Maintainability

Three-tiered systems have become very popular in managing the complexities of a web site. One layer handles the data storage and retrieval, another layer handles business rules and logic, while the third layer converts the data and presents it to the users. Some sites use even more layers.

ADO.NET supports creating these layered systems nicely. They can communicate with each other by passing around XML formatted data.

Programmability

Programmers will find that ADO.NET is easier to program and use primarily because it is a strongly typed system. In addition, the compiler should notify developers of errors sooner in the development process, which should mean a more robust and stable result.

Performance

Part of the problem with the older ADO technology was the fact that information had to be passed from component to component using a system called COM marshaling. While this worked, the marshaling and unmarshaling of data hurts performance. By passing information around formatted using XML, hopefully performance will be improved. At a minimum, the fewer translations needed should increase performance.

Scalability

Many large sites have expanded so quickly that bottlenecks appear when the web site starting getting lots of hits. Often the bottleneck is the database engine being overloaded with record lookups, locking requests, etc... Since ADO.NET encourages working with disconnected data, the database engine should be able to scale better, since records will not be locked very long and the total number of record lookups should be reduced.

ADO.NET Document Object Model

ADO.NET is made from two major sets of components: the DataSet (and related) classes and Managed Providers. Remember, the DataSet is basically an in-memory database. Managed Providers are classes that allow you to connect to and work with the real database engine. At the current time, .NET supports two different providers, one for SQL Server and another for ODBC databases. Others will be almost certainly be created by 3rd party developers.

DataTables

A DataTable represents one table within a DataSet. It has a collection of DataColumn objects that represent the information stored in the table. It not only stores the records, but also tracks the current state of each record. This way changes made to the DataTable can be easily used to update the database engine on demand.

DataColumns

The DataColumns represent the database table schema. You can create a variety of different types of DataColumn objects, including:

Standard columns (text, integer, etc.)

Expression columns (used for filtering, calculation and aggregating information)

AutoIncrement columns

DataRows

Every DataTable is made of zero or more data rows (aka records). Every row of data also maintains a RowState property, which can have the values shown below:

RowState	Description
Unchanged	No changes have been made since the last call to AcceptChanges().
New	This row was added to the table, but AcceptChanges() has not been called.
Modified	One or more the columns in the row have been changed since the last call to AcceptChanges().
Deleted	This row has been deleted from the table.
Detached	Either the row has been deleted, but AcceptChanges() has not been called, or the row has been created but not added to the table.

DataRelation

A DataRelation connects to tables in the DataSet to each other. When you create a DataRelation object, you also specify which table and column acts as a primary key and which other table and column will act as a foreign key. This allows you to setup one-to-one or parent-to-child relationships that connect different tables together and can be used automatically by some parts of ADO.NET. Keep in mind that the primary and foreign keys can actually be a composite (combination) of multiple columns.

Constraints

Naturally when relationships are added to a DataSet, you want to make sure they stay valid and are not violated with corrupt information. This is were constraints are used.

ADO.NET support two types of constraints:

ForeignKeyConstraint

This controls what happens when a data row is added, changed or deleted and one of its columns is being used as a primary key connected to another table.

The possible actions are:

Action	Description
Cacade	Deletes or updates the related rows in the other table.
SetNull	Replaces the column in the other table with Null.
SetDefault	Replaces the column in the other table with its default value.
None	No action is taken. May result in data corruption.
Default	The default is Cascade.

UniqueConstraint

This kind of constraint prevents adding rows with duplicate column information. As a quick example, a customer ID or SSN column should probably always be unique.

Accessing Databases using ADO.NET

Chapter 3: ADO.NET Managed Providers

The Two Managed Providers

OleDb Managed Provider

This provider is used to connect to older COM-based database drivers such as ODBC. You access this provider by importing the System.Data.OleDb namespace into your projects.

SQL Managed Provider

This provider is designed to communicate directly with SQL Server 7.0 or higher databases. Since there is no COM interoperability layer involved, this provider has better performance than the OleDb provider. You access this provider by importing the System.Data.SqlClient namespace.

NOTE: Other managed providers for other databases are available from 3rd party developers. I generally use the open-source databases MySQL and PostGreSQL under Linux for my personal database projects. I have found managed providers for those databases from the following web sites:

MySQL: http://sourceforge.net/projects/mysqlnet/

PostGreSQL: http://gborg.postgresql.org/project/npgsql/projdisplay.php

Microsoft also offers a managed provider for Oracle databases that can be downloaded from their web site. It is named oracle_net.msi.

Managed Connections

All managed providers have a connection class (OleDbConnection, SqlConnection, NpgsqlConnection, etc.) that is used to establish a connection to the desired database. Here are the common properties provided by the classes (providers vary widely so check the documentation for details):

Property	Description
ConnectionString	Gets or sets the string used to open the database. Syntax required varies from provider to provider.
ConnectionTimeout	Gets or sets the time to wait while establishing a connection before returned an error.
Container	Returns the IContainer that contains the object.
Database	Gets or sets the name the of the current database, or the database to be used once the connection is opened.
DataSource	Gets or sets the name of the database to connect to.
PacketSize (SqlConnection)	Gets the size of the network packets the data is transferred in.
Provider (OldeDbConnection)	Gets or sets the name of the OLEDB provider.
ServerVersion	Gets a string containing the version of the connected server.
Site	Gets or sets the site of the component.
State	Gets the current state (open, closed, etc.) of the connection.

OleDbConnection

Example:

OleDbConnection myConnection;

myConnection = new OleDbConnection( @“Provider=Microsoft.Jet.OLDEDB.4.0;Data Source=C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb;” );

myConnection.Open();

// Work with database here

myConnection.Close();

This short example uses the 'Microsoft.Jet.OLDEDB.4' provider to open the Microsoft Access 2000 sample database named Northwind.mdb.

SqlConnection

Example:

SqlConnection myConnection;

myConnection = new SqlConnection( “server=localhost;database=Music;Integrated Security=SSPI;” );

myConnection.Open();

// Work with database here

myConnection.Close();

This short example opens the 'Music' database on the local machine, using the current user's login credentials to login to the database.

If you wish to login using a specific user name and password, replace the connection string with the following:

“server=localhost;database=Music;uid=user-name;pwd=password;”

NpgsqlConnection

Example:

NpgsqlConnection myConnection;

myConnection = new NpgsqlConnection( “Server=zev;Port=5432;User ID=randy;Password=top-secret;Database=music;” );

myConnection.Open();

// Work with database here

myConnection.Close();

This example demonstrates connecting to a PostGreSQL database named 'music' on the server named 'zev' via TCP/IP port number 5432. Notice that while the connection strings are different from provider to provider, the rest of the code is almost identical.

Managed Commands

Once you have established a connection to the database, you will want to begin retrieving, updating, inserting or deleting data. The most direct method of doing this is by using a managed command to execute an SQL statement.

As with Connection classes, there are different versions of Command classes that must be used with the correct provider. Microsoft provides to Command classes named SqlCommand and OleDbCommand. The PostGreSQL managed provider uses the NpgsqlCommand class instead. Since all of the classes implement the IDbCommand interface, they work much the same.

Example:

SqlConnection myConnection;

SqlCommand myCommand;

myConnection = new SqlConnection( “server=localhost;database=Music;Integrated Security=SSPI;” );

myCommand = new SqlCommand( “SELECT * FROM artist”, myConnection )

In order to execute the command, you must invoke one of the predefined methods as shown below:

Method	Description
ExecuteNonQuery	Executes statements that do not return records.
ExecuteReader()	Executes a SELECT statement and returns a DataReader object that can be used to retrieve the records.
ExecuteScaler()	Executes the SQL statement and returns the first column from the first matching row.
ExecuteXmlReader (SqlCommand)	Executes a SQL statement and returns the results formatted as an XML stream.

The DataReader

The DataReader provides a forward-only, read-only stream of data from the data store. Since it returns one record at a time, it is useful for saving memory when processing a large number of records.

The DataReader class implements a large number of methods you can use to access the columns of data found in each record. To move from record-to-record, the Read() method is called repeatedly. It will return false once the end of the records is reached.

Example:

using System;

using System.Data;

using System.Data.SqlClient;

namespace ShowArtists

{

/// <summary>

/// Summary description for Class1.

/// </summary>

class Class1

{

/// <summary>

/// The main entry point for the application.

/// </summary>

[STAThread]

static void Main(string[] args)

{

int numArtists = 0;

SqlConnection conn = new SqlConnection(

"server=ki;database=Music;Integrated Security=SSPI;" );

conn.Open();

if( conn.State == ConnectionState.Open )

{

SqlCommand cmd = new SqlCommand(

SELECT id,artist FROM artist", conn );

SqlDataReader reader = cmd.ExecuteReader();

while( reader.Read() )

{

// Use indexer to read "id" column

int artistId = (int) reader["id"];

// Use GetString() to read "artist" column

string artist = reader.GetString(1);

Console.WriteLine( "{0:D5} – {1}",

artistId,

artist );

numArtists++;

}

reader.Close();

conn.Close();

}

Console.WriteLine( "\nFound {0} artists", numArtists );

}

}

}

This example is a simple console application that queries the database for records in the “artist” table and displays the results on the screen. You can many things with the returned records, including adding them to a DataSet or binding them to a control such as a DataGrid on a web page. We will see an example of that shortly.

Managed Commands with Stored Procedures

You can also use an SqlCommand object to invoke a stored procedure. Stored procedures are functions that are added to the database directly and when called perform actions on records. This is very useful when the same code must be run repeatedly, since the database server can optimize the code internally.

Example:

using System;

using System.Data;

using System.Data.SqlClient;

namespace ShowArtistsStoredProcedure

{

/// <summary>

/// Summary description for Class1.

/// </summary>

class Class1

{

/// <summary>

/// The main entry point for the application.

/// </summary>

[STAThread]

static void Main(string[] args)

{

int numArtists = 0;

SqlConnection conn = new SqlConnection(

"server=ki;database=Music;Integrated Security=SSPI;" );

conn.Open();

if( conn.State == ConnectionState.Open )

{

SqlCommand cmd = new SqlCommand(

"GetAllArtists", conn );

cmd.CommandType = CommandType.StoredProcedure;

SqlDataReader reader = cmd.ExecuteReader();

while( reader.Read() )

{

// Use indexer[int] to read column 0

int artistId = (int) reader[0];

// Use indexer[string] to get “artist"

string artist = (string) reader["artist"];

Console.WriteLine( "{0:D5} - {1}",

artistId, artist );

numArtists++;

}

reader.Close();

conn.Close();

}

Console.WriteLine( "\nFound {0} artists", numArtists );

}

}

}

Notice that instead of hard coding the SELECT statement when creating the new SqlCommand object, we use the name of an existing stored procedure. The new SqlCommand object's CommandType property must be set to 'StoredProcedue' for this to work.

The GetAllArtists stored procedure is defined as:

CREATE PROCEDURE [dbo].[GetAllArtists] AS

SELECT id,artist FROM artist

Stored procedures can also include parameters that your program will pass to the procedure at run-time. Here is an example of passing a parameter to a stored procedure:

using System;

using System.Data;

using System.Data.SqlClient;

namespace AlbumByArtist

{

/// <summary>

/// Summary description for Class1.

/// </summary>

class Class1

{

/// <summary>

/// The main entry point for the application.

/// </summary>

[STAThread]

static void Main(string[] args)

{

string searchFor;

Console.WriteLine(

"Please enter the name of an artist to search for: " );

searchFor = Console.ReadLine();

int numAlbums = 0;

SqlConnection conn = new SqlConnection(

"server=ki;database=Music;Integrated Security=SSPI;" );

conn.Open();

if( conn.State == ConnectionState.Open )

{

SqlCommand cmd = new SqlCommand

( "GetAlbumsByArtistName", conn );

cmd.CommandType = CommandType.StoredProcedure;

SqlParameter param = cmd.Parameters.Add(

new SqlParameter("@artist",

SqlDbType.VarChar,

255) );

param.Direction = ParameterDirection.Input;

param.Value = searchFor;

SqlDataReader reader = cmd.ExecuteReader();

while( reader.Read() )

{

int albumId = reader.GetInt32(0);

string albumName = reader.GetString(1);

string artist = reader.GetString(2);

Console.WriteLine( "{0} - {1:D5} - {2}",

artist, albumId, albumName );

numAlbums++;

}

reader.Close();

conn.Close();

}

Console.WriteLine( "\nFound {0} albums by '{1}'",

numAlbums,

searchFor );

}

}

}

The GetAlbumsByArtistName stored procedure is defined as:

CREATE PROCEDURE [dbo].[GetAlbumsByArtistName]

@artist varchar (255)

AS

SELECT album.id,album.album,artist.artist FROM album

INNER JOIN artist

ON album.artist_id = artist.id

WHERE artist.artist LIKE '%' + @artist + '%'

The INNER JOIN section of the SQL statement connects the artist and album tables together so we can return information from both tables from the procedure. We will discuss joining tables in more detail later.

The LIKE test in the WHERE clause allows us to perform string wild card matches against fields. The '%' character is a wild card symbol in SQL syntax. Basically this statement finds an artists that have the supplied string anywhere in their name. You should probably avoid searching for artists named 'The', unless you want to see a lot of returned records.

The DataAdapter

Earlier we looked at using the DataSet class in order to store a collection of DataTable objects. This is by far the most commonly used technique for accessing databases from an ASP.NET web application. The biggest reason to use the DataSet instead of accessing the database directly is because the DataSet can be cached locally within the web server to avoid having to repeatedly query the database engine over and over.

For small web pages that only a few people will access, this is not important, but imagine what happens when you web pages are being accessed by thousands of users all at the same time. Your database engine may be fast enough to support a few users, but it will have a hard time keeping up when there are thousands.

When we are pulling information from the data store into a DataSet, often times we want more control over the process than the default methods. This is were the DataAdapter class comes into place. Basically, it can perform conversions on the tables as they are imported. In addition, you can run multiple queries against the data store and combine them all into one DataSet easily using a DataAdapter.

Remember, the primary job of a DataAdapter is to retrieve information from a data store (database engine) and push the records into one or more DataTables in a local DataSet.

using System;

using System.Data;

using System.Data.SqlClient;

namespace BuildDataSetUsingAdpater

{

/// <summary>

/// Summary description for Class1.

/// </summary>

class Class1

{

/// <summary>

/// The main entry point for the application.

/// </summary>

[STAThread]

static void Main(string[] args)

{

SqlDataAdapter adapter;

DataSet ds = new DataSet();

adapter = new SqlDataAdapter( "SELECT * FROM artist",

"server=ki;database=Music;Integrated Security=SSPI;" );

adapter.Fill( ds, "artist" );

int numArtists = 0;

foreach( DataRow row in ds.Tables["artist"].Rows )

{

Console.WriteLine( "{0:D5} – {1}",

row["id"],

row["artist"] );

numArtists++;

}

Console.WriteLine( "\nLoaded {0} artists into DataSet",

numArtists );

}

}

}

As you can see in the example above, the DataAdapter constructor accepts both an SQL query and the connection string used to access the data store. The Fill() method runs the query, reads all the returned records and adds them to a table in the DataSet, in this case the table named “artist”. You can add multiple tables, or run multiple queries and add the results to the same table (provided the query returns the same kind of records.)

Later we will learn how the DataSet information can be cached by the web server to improve performance.

DataAdapter.Fill() Method

The Fill() method is the workhorse of the DataAdapter class which performs the bulk of the work. The Fill() method comes in several flavors depending on what you are trying to do. You can reuse a DataAdapter over and over with different queries by changing its SelectCommand.CommandText property and calling Fill() again to append more records, or to add a different set of records to another table in the DataSet.

Table and Column Mappings

The DataAdapter also support modifying the schema of records added to the DataSet when you invoke the Fill() method. This is useful for changing table or column names on the fly for example. If the data store uses cryptic names, you can modify the column names in the DataSet results to more user-friendly names using this technique.

Here is another example:

using System;

using System.Data;

using System.Data.SqlClient;

namespace BuildDataSetWithMappings

{

/// <summary>

/// Summary description for Class1.

/// </summary>

class Class1

{

/// <summary>

/// The main entry point for the application.

/// </summary>

[STAThread]

static void Main(string[] args)

{

SqlDataAdapter adapter;

DataSet ds = new DataSet();

adapter = new SqlDataAdapter( "SELECT * FROM artist",

"server=ki;database=Music;Integrated Security=SSPI;" );

adapter.TableMappings.Add( "artist", "Performers" );

adapter.TableMappings["artist"].ColumnMappings.Add(

"id",

"Performer ID" );

adapter.TableMappings["artist"].ColumnMappings.Add(

"artist",

"Performer Name" );

adapter.Fill( ds, "artist" );

int numPerformers = 0;

foreach( DataRow row in ds.Tables["Performers"].Rows )

{

Console.WriteLine( "{0:D5} - {1}",

row["Performer ID"],

row["Performer Name"] );

numPerformers++;

}

Console.WriteLine( "\nLoaded {0} performers",

numPerformers );

}

}

}

This example renames the “artist” table to “Performers” during the Fill() operation. It also renames the “id” and “artist” columns to “Performer ID” and “Performer Name” at the same time.

The biggest advantage to this technique is when you bind the resulting DataSet directly to a control, such as a DataGrid. When you do this, users will see the more friendly column names instead of the cryptic ones from the data store itself.

Chapter 4: Basic ANSI-SQL

This chapter provides a quick overview of common SQL statements. It is not intended to make you into a database expert, but does cover the most commonly used SQL commands you are likely to need.

Setting Up Your Workspace

Let's start by creating a simple ASP.NET project that can be used to enter commands directly against a database. The SQL Web Administrator Tool project we installed earlier could also be used to do this, but it is about time we started learning how to create our own ASP.NET web applications, don't you think?

Follow these steps to create our ASP.NET project:

Select File -> New Project from within Visual Studio.NET.

Select ASP.NET Web Application from the Visual C# Projects template area.

Enter “http://localhost/MusicQuery” in the Location text box.

Drag and drop the following controls onto the web form: A TextBox, a Label, a Button and a DataGrid.

Rename the controls: TxtQuery, LblMessage, BtnRun, and GridResults. Change other properties as directed by your instructor.

Edit the code behind the web page to read as follows:

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

namespace MusicQuery

{

/// <summary>

/// Summary description for WebForm1.

/// </summary>

public class WebForm1 : System.Web.UI.Page

{

protected System.Web.UI.WebControls.TextBox TxtQuery;

protected System.Web.UI.WebControls.Button BtnRun;

protected System.Web.UI.WebControls.Label LblMessage;

protected System.Web.UI.WebControls.DataGrid GridResults;

private void Page_Load(object sender, System.EventArgs e)

{

try

{

if( Page.IsPostBack )

{

string sqlQuery = TxtQuery.Text;

SqlConnection conn = new SqlConnection(

"server=ki;database=Music;Integrated Security=SSPI;" );

SqlDataAdapter adapter;

adapter = new SqlDataAdapter( sqlQuery,

conn );

DataSet ds = new DataSet();

adapter.Fill( ds );

GridResults.DataSource =

ds.Tables[0].DefaultView;

Page.DataBind();

LblMessage.Text = "Query Returned "

+ ds.Tables[0].Rows.Count

+ " records";

}

}

catch( SqlException ex )

{

LblMessage.Text = "<b>Error: " +

ex.Message.ToString() +

" - Please try again.</b>";

}

}

#region Web Form Designer generated code

}

}

NOTE: If you save and run the ASP.NET application, you will find that it does not work. The problem is that by default, ASP.NET applications run as the user named ASPNET, which does not have permission to open SQL Server Databases. There are several solutions for this, most of which involve modifying the project settings to use a different user account that is authorized to connect to the database.

In this situation, let's use the SQL Web Administrator Tool previously installed to grant access to the database by the ASPNET user account. To do this, follow these steps:

Login to the WEB Data Administrator tool using an authorized account.

Click on the 'Security' icon on the left menu.

Click on 'Logins' to view the current list of authorized users.

Click the 'Create new Login' link in the upper-right corner.

Select 'Windows Integrated' as the Authentication Method, then enter {SERVERNAME}\ASPNET as the Login Name.

Create the new account.

Next, make sure you select “Grant access” for the new account.

Next, select 'Server Roles' and enable the 'sysadmin' checkbox.

Next, select 'Database Access' and click the checkbox to permit access to the 'Music' database.

Finally, select the 'Music' database by clicking on 'Databases' and visit the 'Users' link. Make sure the '{SERVERNAME}\ASPNET' account has at least 'Public' and 'db_owner' access.

NOTE: This is a lot easier to do if you working with SQL Server and not the MSDE. In that case, you can run the SQL Server Enterprise Manager tool and either create and grant access similar to the steps above, or you can change the security settings from 'Windows Integrated' to 'Mixed' mode. Mixed mode allows user accounts from the normal Windows login or SQL-only accounts to be used to login to the database server. The MSDE only allows integrated security mode by default. Granting access rights to databases must be performed however.

Getting Data from a Database

The SELECT statement is the commonly used statement for finding records in a database table. It is a complex command with many, many options.

Syntax:

SELECT select_list

[ INTO new_table ]

FROM table_source

[ WHERE search_condition ]

[ GROUP BY group_condition ]

[ HAVING search_condition ]

[ ORDER BY order_expression [ ASC | DESC ] ]

Try this simple statement in the MusicQuery web application:

SELECT *

FROM artist

or

SELECT title,filename

FROM song

The WHERE Clause

You will use the WHERE clause with conditions to filter the records returned by the SELECT query. There are many different ways to use the WHERE clause, but the most common thing is to only return a list of records where one or more of the columns matches a certain condition, such as equal to or less than a value.

Argument	Condition It Tests
=	Equality test.
<>	Inequality test.
!=	Inequality test.
>	Greater-than test.
>=	Greater-than-or-equal test.
!>	Not greater-than test. (Same as <=)
<	Less-than test.
<=	Less-than-or-equal test.
!<	Not less-than test. (Same as >=)
LIKE	String wild card matching test.*

*The '%' and '_' characters have special meaning for LIKE searches. The '_' character stands for any single character, while the '%' character matches zero or more characters.

You can also use the words OR or AND to form more complex search criteria.

Examples:

SELECT artist

FROM artist

WHERE id = 17802

or

SELECT album

FROM album

WHERE album LIKE '%Greatest Hits%'

SELECT album

FROM album

WHERE album LIKE '%Greatest%'

OR album LIKE '%Best%'

The SubQuery

In addition to using a constant value as shown in the examples above, you can also run another query, probably against another table, inside the WHERE clause. This is called using a sub query. The sub query result is then used to to complete the original WHERE filtering. Keep in mind that the sub query must return only one record, or an error will occur.

Examples:

SELECT album

FROM album

WHERE artist_id =

(SELECT id FROM artist WHERE artist = 'Foghat')

SQL Joins

The preceding examples only returned information from a single table in the database. Often times you may want to return information from two or more tables that are related somehow. This is where you want to perform a JOIN operation to connect the records from one table to the records in another table and return all the information at one time.

Join Type	Description
INNER JOIN	Uses a comparison operator (usually =) to match rows based matching columns from two tables.
OUTER JOIN (LEFT OUTER JOIN, RIGHT OUTER JOIN and FULL JOIN)	An outer join returns all the rows in the joined table, either the LEFT or RIGHT. The FULL JOIN returns all records from both rows.
CROSS JOIN	Returns all rows from the left table and then creates a new record in the output for every row in the right table. This has a multiplicative effect, so be careful.

Examples:

SELECT artist,album

FROM artist

INNER JOIN album ON artist.id = album.artist_id

or

SELECT artist,album,song

FROM artist

INNER JOIN album ON artist.id = album.artist_id

INNER JOIN song ON song.album_id = album.id

WHERE artist = 'Foghat'

Sorting Data

The ORDER BY Clause

This clause allows you to sort the records prior to returning the results. You can sort on multiple columns if desired. The DESC option can be appended to the column name to for a descending sort instead of the default ascending (ASC) sort order.

Example:

SELECT artist,album,title,track

FROM artist

INNER JOIN album ON artist.id = album.artist_id

INNER JOIN song ON album.id = song.album_id

WHERE artist = 'Foghat'

ORDER BY album,title

or

SELECT artist,album,title,track

FROM artist

INNER JOIN album ON artist.id = album.artist_id

INNER JOIN song ON album.id = song.album_id

WHERE artist = 'Foghat'

ORDER BY album DESC,CONVERT(integer,track)

NOTE: The CONVERT() function above is used to force sorting by numeric value. The “track” column is defined as a character column, which means the records would be sorted in ASCII sequence. Try the query with and without the CONVERT() function in place.

The GROUP BY Clause

This is mostly used when you want to get a summary with a count instead of all the records themselves. It is used quite often for preparing reports with a count of matching records instead of listing all the records on the report.

The GROUP BY clause is required when using an aggregate function. The most commonly used function is the COUNT() function. Other aggregate functions include: AVG(), FIRST(), LAST(), MIN(), MAX() and more.

SELECT artist AS [Artist Name], COUNT(album.album) AS [Number of Albums]

FROM artist

INNER JOIN album ON artist.id = album.id

GROUP BY artist.artist

ORDER BY artist

Updating Existing Data

The UPDATE statement is used to change records. One thing to keep in mind is by default UPDATE statements will change all the records in the table at same time, unless you include a WHERE clause to limit the matching records. It is usually best to use the primary key with the equality (=) operator so only a single record is modified.

Example:

UPDATE song

SET title = 'I Just Want To Make Love To You'

WHERE id = 118402

or

UPDATE song

SET title = 'I Just Want To Make Love To You'

WHERE title = 'I Just Want To Make Love To Yo';

Inserting New Data

The INSERT statement is used to add new records to a database table. You must provided values for every field that does not allow a NULL value and does not have a DEFAULT value. You must not provided a value for 'AutoIncrement' columns that are commonly used as primary key columns (many times named 'ID'). These options are set when columns are added to the table at creation time.

Example:

INSERT INTO artist

(artist) VALUES ('The Beatles')

Deleting Existing Data

The DELETE statement is used to remove records from a table. There is no undelete option, so make sure you know what you are deleting. Again, if you do not use a WHERE clause, all records in the table will be deleted. The safest way to delete records is one at a time using the primary key column in the WHERE clause. You have been warned.

Example:

DELETE FROM song

WHERE id = 118402

Chapter 5: Using a Basic DataGrid

The ASP.NET DataGrid

The DataGrid server control is a server control that gets rendered using HTML <table>, <tr> and <td> tags. The control supports selecting, editing, sorting and paging of the data. Its layout can also be customized to change its appearance.

Data Binding

If you are used to the way data binding worked with older Microsoft technologies, you will need to change your thinking a bit since .NET data binding is more powerful. In .NET, you can bind any property of any .NET control to any publicly available information. It this environment, data is a very broad term. It might be values from a data store, strings stored in a collection, or attribute values from an object property.

The DataGrid, like many other complex controls in the .NET framework, can bind to any class that supports the ICollection interface, including a DataTable, DataReader, array, collection, or an object's properties. As you probably recall, the ICollection interface defines size, enumeration and synchronization methods for all collections and it guarantees that the data provided by the class has as least a basic way defined for access that data.

The DataSet supports the ICollection interface which returns the table objects in the DataSet. Therefore to connect a DataSet to a DataGrid you follow these basic steps:

Define a data source.

Set the DataGrid's DataSource property to the data source you defined.

Call the DataBind() method.

Defining a Data Source

Generally you will create a data source by using a managed provider to select records from the data store into a DataSet object. The DataAdapter class is often used to perform some translations of column and/or table names from the cryptic codes used by database administrators into something more user-friendly.

See pages 102 and 103 for an example.

Set the DataSource of the DataGrid

See pages 103 and 104 for an example.

Call the DataBind() Method

The final step is the call the DataBind() method which forces the DataGrid to load and render the information from the data source into HTML output.

See page 104 for an example.

Binding to a DataTable in a DataSet

The example in the book binds the DataSet.Tables collection to a data grid. That is not very useful in the real world. More likely you want to bind one of the DataTables to the grid instead. When you do that, the records in the table will be displayed instead of the names of the tables.

See pages 105 and 106 for an example.

Hands-On Exercise: Create a web application using a DataGrid.

Example:

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

namespace SimpleGrid

{

/// <summary>

/// Summary description for WebForm1.

/// </summary>

public class WebForm1 : System.Web.UI.Page

{

protected System.Web.UI.WebControls.DataGrid outGrid;

private void Page_Load(object sender, System.EventArgs e)

{

DataSet ds = new DataSet();

SqlDataAdapter adapter;

string query = "SELECT artist,album,title,track " +

"FROM artist " +

"INNER JOIN album ON artist.id = album.artist_id " +

"INNER JOIN song ON song.album_id = album.id " +

"WHERE artist = 'Foghat' " +

"ORDER BY album,CONVERT(integer,track)";

adapter = new SqlDataAdapter( query,

"server=ki;database=Music;Integrated Security=SSPI;" );

adapter.Fill( ds, "Foghat Tunes" );

outGrid.DataSource = ds.Tables["Foghat Tunes"];

outGrid.DataBind();

}

#region Web Form Designer generated code

}

}

Binding to Arrays

You can also use a standard array as a data source for the control.

See pages 110 and 111 for an example.

Binding to Collections

Any control with a DataSource property can be bound to any object that supports the ICollection interface.

See pages 112 and 113 for an example.

Binding to Properties

When you wish to bind a property, you will not be able to bind the property's value directly to a DataSource. This is because most properties are a single value, not a collection.

So the DataGrid is not useful in those cases, but other controls such as the Label control can be used. Basically you can connect the property to the Label control's Text property in order to display the value. A boolean property might be connected to a CheckBox control instead. There are many different ways to bind properties to controls.

There are 2 ways to make this happen. You can set the binding in your own code, or define a special tag for the property you wish to bind, such as a Label control's Text property. The tag '<%# variable-name %>' creates a simple binding that connects the variable (or property) to the control.

See pages 114 and 115 for an example.

Binding to XML Files

The DataSet class has one other very important ability that we have not yet discussed. It supports reading and writing the data store to an XML file by called its ReadXml() or WriteXml() methods. I have used this in some of my own projects to setup a simple database that stores the information into a plain text file formatted as XML.

Naturally, you could also read and write XML files without using a DataSet, but then you will have to work harder to bind the data to a control. The DataSet was tailor-made for situations just like this, so why not use it?

See pages 117 and 118 for an example.

Chapter 6: Altering DataGrid Output

Understanding DataGrid Properties

Controlling the DataGrid appearance mostly involves adjusting some of its properties. That combined with a little bit of code allows you to change the look and feel of the output in several ways.

Let's first setup a basic web application for our music database and then customize it to get a different look and feel.

Source Code -- (MusicGrid1)

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

namespace MusicGrid1

{

/// <summary>

/// Summary description for WebForm1.

/// </summary>

public class WebForm1 : System.Web.UI.Page

{

protected System.Web.UI.WebControls.Label LblArtist;

protected System.Web.UI.WebControls.DropDownList LstArtist;

protected System.Web.UI.WebControls.Label LblAlbum;

protected System.Web.UI.WebControls.DropDownList LstAlbum;

protected System.Web.UI.WebControls.DataGrid SongGrid;

private static string MyConnection = "Server=KI;Database=Music;Integrated Security=SSPI;";

private void Page_Load(object sender, System.EventArgs e)

{

if( !Page.IsPostBack)

{

LoadArtists();

LoadAlbums();

LoadSongs();

}

}

protected void LoadArtists()

{

DataSet ds = new DataSet();

string query = "SELECT artist,id FROM artist ORDER BY artist";

SqlDataAdapter adapter = new SqlDataAdapter( query, MyConnection );

adapter.Fill( ds, "Artists" );

LstArtist.Items.Clear();

foreach( DataRow row in ds.Tables["Artists"].Rows )

LstArtist.Items.Add( new ListItem( row["artist"].ToString(), row["id"].ToString() ) );

}

protected void LoadAlbums()

{

DataSet ds = new DataSet();

string query;

query = string.Format( "SELECT album,id FROM album WHERE artist_id = {0} ORDER BY album",

LstArtist.SelectedItem.Value );

SqlDataAdapter adapter = new SqlDataAdapter( query, MyConnection );

adapter.Fill( ds, "Albums" );

LstAlbum.Items.Clear();

LstAlbum.Items.Add( new ListItem( "(all)", "-1" ) );

foreach( DataRow row in ds.Tables["Albums"].Rows )

LstAlbum.Items.Add( new ListItem( row["album"].ToString(), row["id"].ToString() ) );

}

protected void LoadSongs()

{

DataSet ds = new DataSet();

string query = "SELECT artist,album,title,genre,song_len FROM song " +

"INNER JOIN album ON album.id = song.album_id " +

"INNER JOIN artist ON artist.id = song.artist_id ";

if( LstArtist.SelectedItem != null )

if( LstAlbum.SelectedItem != null && LstAlbum.SelectedItem.Value != "-1" )

query += string.Format( "WHERE song.artist_id = {0} AND song.album_id = {1} ",

LstArtist.SelectedItem.Value,

LstAlbum.SelectedItem.Value );

else

query += string.Format( "WHERE song.artist_id = {0} ",

LstArtist.SelectedItem.Value );

query += "ORDER BY artist,album,track,title";

SqlDataAdapter adapter = new SqlDataAdapter( query, MyConnection );

adapter.Fill( ds, "Songs" );

SongGrid.DataSource = ds.Tables["Songs"];

SongGrid.DataBind();

}

#region Web Form Designer generated code

private void LstArtist_SelectedIndexChanged(object sender, System.EventArgs e)

{

LoadAlbums();

LoadSongs();

}

private void LstAlbum_SelectedIndexChanged(object sender, System.EventArgs e)

{

LoadSongs();

}

}

}

This project runs reasonably well, but the output is not very pretty. Let's make some changes and see if we can improve the look.

DataGrid Column Names

The first improvement we can make is to change the names of the column headings on the grid. This can be done 3 different ways.

Method #1: Adding Mappings to the DataAdapter (MusicGrid2)

Add this “using” statement:

using System.Data.Common;

And then revise the LoadSongs() method like this:

protected void LoadSongs()

{

DataSet ds = new DataSet();

string query = "SELECT artist,album,title,genre,song_len FROM song " +

"INNER JOIN album ON album.id = song.album_id " +

"INNER JOIN artist ON artist.id = song.artist_id ";

if( LstArtist.SelectedItem != null )

if( LstAlbum.SelectedItem != null && LstAlbum.SelectedItem.Value != "-1" )

query += string.Format( "WHERE song.artist_id = {0} AND song.album_id = {1} ",

LstArtist.SelectedItem.Value,

LstAlbum.SelectedItem.Value );

else

query += string.Format( "WHERE song.artist_id = {0} ",

LstArtist.SelectedItem.Value );

query += "ORDER BY artist,album,track,title";

SqlDataAdapter adapter = new SqlDataAdapter( query, MyConnection );

adapter.TableMappings.Add( "song", "Songs" );

DataTableMapping songMapping = adapter.TableMappings["song"];

songMapping.ColumnMappings.Add("artist","Performer/Band");

songMapping.ColumnMappings.Add("album","Album/CD");

songMapping.ColumnMappings.Add("title","Song Title");

songMapping.ColumnMappings.Add("genre","Music Style");

songMapping.ColumnMappings.Add("song_len","Length");

adapter.Fill( ds, "song" );

SongGrid.DataSource = ds.Tables["Songs"];

SongGrid.DataBind();

}

Method #2: Revise the SELECT statement (MusicGrid3)

Another way to do this is to modify the SELECT statement as shown below:

protected void LoadSongs()

{

DataSet ds = new DataSet();

string query = "SELECT artist AS [Performer/Band],” +

“album AS [Album/CD]," +

"title AS [Song Title],genre AS [Music Style]," +

"song_len AS Length FROM song " +

"INNER JOIN album ON album.id = song.album_id " +

"INNER JOIN artist ON artist.id = song.artist_id ";

if( LstArtist.SelectedItem != null )

if( LstAlbum.SelectedItem != null &&

LstAlbum.SelectedItem.Value != "-1" )

query += string.Format( "WHERE song.artist_id = {0} AND song.album_id = {1} ",

LstArtist.SelectedItem.Value,

LstAlbum.SelectedItem.Value );

else

query += string.Format( "WHERE song.artist_id = {0} ",

LstArtist.SelectedItem.Value );

query += "ORDER BY artist,album,track,title";

SqlDataAdapter adapter = new SqlDataAdapter( query, MyConnection );

adapter.Fill( ds, "song" );

SongGrid.DataSource = ds.Tables["song"];

SongGrid.DataBind();

}

Method #3: Using the DataGrid Columns Collection Property (MusicGrid4)

You can also control the column heading by modifying DataGrid properties, specifically, the Columns collection property.

To make this change, select the grid control from the form design view and select Columns in the property window. Next, add a Bound Column for each field in the data set you are displaying. At a minimum program the Header Text and Data Field properties and turn on the Read Only checkbox.

Using Basic DataGrid Properties to Alter the Rendered Table

Simple properties such as BackColor, BorderColor, BorderStyle, BorderWidth, CellPadding, CellSpacing, Font, ForeColor, GridLines, HorizontalAlign, ShowHeader and Width can be used at design time to change the appearance of the grid. Most of these properties have a one-to-one mapping to their HTML equivalents.

DataGrid Style Objects

The DataGrid also exposes more complex properties that change the appearance of the grid, but are difficult to do with standard HTML coding. These complex properties are called style properties. See the AlternatingItemStyle, EditItemStyle, FooterStyle, HeaderStyle, ItemStyle, PagerStyle and SelectedItemStyle properties. Each of these properties affects the appearance of the data grid in the browser that can be very difficult to control using standard ASP programming.

Example: MusicGrid5

Paging with the DataGrid

One drawback to the current web application is that is will retrieve and display all matching records, even if there are hundreds. Generally you will not display all the records at one time to a user, but instead provide a way for the user to browse a limited set of records, one page at a time. Again the DataGrid control makes this almost simple when compared to other solutions.

You can either use the built-in paging ability of the DataGrid, or implement custom paging using code for finer control.

Built-In Paging

Built-in paging is designed for small data sets. You do need to add an event handler that is invoked when the user pages forward or backward, but the code for this is very simple.

First, enable the AllowPaging property on the grid. Next, add a new handler for the PageIndexChanged event. In the event handler, copy the NewPageIndex property from the event data to the DataGrid object and rebind the data, like this:

private void SongGrid_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)

{

SongGrid.CurrentPageIndex = e.NewPageIndex;

LoadSongs();

}

You may also wish to examine the PagerStyle property. The built-in pager operates in 2 different modes, NextPrev and NumericPages. Try them both.

Example: MusicGrid6

Custom Paging

While the built-in paging works well, it does have one drawback. It needs to load all the records, even those that are not being displayed, from the data set every time the page is refreshed. If there are a large number of records, this can put a heavy load on the server.

This can be solved by implementing your own custom paging, by enabling the AllowCustomPaging property. Your event handlers can then bind the data grid to rows 0-n, instead of all rows in the data set. You then need to create your own page links and handle their OnClick events instead of the PageIndexChanged event. Inside your handler(s), you can then query just the records that will be displayed, saving memory and improving response time for the user.

The hardest part is figuring out exactly how to query the database correctly. Some databases (PostGreSQL and MySQL) allow you to add additional clauses to the SELECT statement to control the returned records, which makes implementing paging very easy.

For example:

SELECT * FROM song LIMIT 10 OFFSET 20;

Unfortunately, SQL Server does not support the LIMIT and OFFSET clauses, so we have to find an alternate method of doing this. Probably the easiest thing is to dynamically create a new temporary table using a stored procedure and then return the records from the temporary table instead.

Here is a stored procedure that will work for our example:

CREATE PROCEDURE [dbo].[GetSongsByPage]

@CurrentPage int,

@PageSize int,

@ArtistID int,

@AlbumID int,

@TotalRecs int output

AS

-- Create a temp table

-- Add an ID column to count the records

CREATE TABLE #TempTable

(

ID int IDENTITY PRIMARY KEY,

artist nvarchar(255),

album nvarchar(255),

title nvarchar(255),

genre nvarchar(80),

song_len nvarchar(10)

)

-- Fill the temporary table

IF @AlbumID <> -1

BEGIN

INSERT INTO #TempTable

(artist,album,title,genre,song_len)

SELECT artist,album,title,genre,song_len

FROM song

INNER JOIN artist ON artist.id = song.artist_id

INNER JOIN album ON album.id = song.album_id

WHERE song.artist_id = @ArtistID

AND song.album_id = @AlbumID

END

ELSE

BEGIN

INSERT INTO #TempTable

(artist,album,title,genre,song_len)

SELECT artist,album,title,genre,song_len

FROM song

INNER JOIN artist ON artist.id = song.artist_id

INNER JOIN album ON album.id = song.album_id

WHERE song.artist_id = @ArtistID

END

-- Create variables to identify the first

-- and last record that should be selected

DECLARE @FirstRec int, @LastRec int

SELECT @FirstRec = (@CurrentPage - 1) * @PageSize

SELECT @LastRec = (@CurrentPage * @PageSize + 1)

-- Select one page of records

SELECT artist,album,title,genre,song_len

FROM #TempTable

WHERE ID > @FirstRec AND ID < @LastRec

-- Return total number of records

IF @AlbumID <> -1

SELECT @TotalRecs = COUNT(*) FROM song WHERE artist_id = @ArtistID AND album_id = @AlbumID

ELSE

SELECT @TotalRecs = COUNT(*) FROM song WHERE artist_id = @ArtistID

GO

Next, we must modify our page and add some LinkButtons and event handlers to be called when the links are clicked. A private data variable will be used to store the current page and the event handler(s) will use it to query the database by calling the stored procedure. Our LoadSongs() function can now use our stored procedure instead of querying the database records directly.

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

namespace MusicGrid7

{

/// <summary>

/// Summary description for WebForm1.

/// </summary>

public class WebForm1 : System.Web.UI.Page

{

protected System.Web.UI.WebControls.Label LblArtists;

protected System.Web.UI.WebControls.DropDownList LstArtists;

protected System.Web.UI.WebControls.Label LblAlbums;

protected System.Web.UI.WebControls.DataGrid SongGrid;

protected System.Web.UI.WebControls.DropDownList LstAlbums;

private static string MyConnection = "Server=KI;Database=Music;Integrated Security=SSPI;";

protected System.Web.UI.WebControls.Label Label1;

protected System.Web.UI.WebControls.Label LblCurrentPage;

protected System.Web.UI.WebControls.Label Label2;

protected System.Web.UI.WebControls.Label LblTotalPages;

protected System.Web.UI.WebControls.LinkButton LnkFirstPage;

protected System.Web.UI.WebControls.LinkButton LnkPrevPage;

protected System.Web.UI.WebControls.LinkButton LnkNextPage;

protected System.Web.UI.WebControls.LinkButton LnkLastPage;

private int CurrentPageNumber;

private void Page_Load(object sender, System.EventArgs e)

{

if( !Page.IsPostBack )

{

CurrentPageNumber = 1;

LoadArtists();

LoadAlbums();

LoadSongs();

}

}

private void LoadArtists()

{

string query;

SqlDataAdapter adapter;

DataSet ds;

query = "SELECT artist,id FROM artist ORDER BY artist";

ds = new DataSet();

adapter = new SqlDataAdapter( query, MyConnection );

adapter.Fill( ds, "Artists" );

LstArtists.Items.Clear();

foreach( DataRow artist in ds.Tables["Artists"].Rows )

LstArtists.Items.Add(

new ListItem( artist["artist"].ToString(), artist["id"].ToString() ) );

}

private void LoadAlbums()

{

string query;

SqlDataAdapter adapter;

DataSet ds;

query = "SELECT album,id FROM album WHERE artist_id = " +

LstArtists.SelectedItem.Value + " ORDER BY album";

adapter = new SqlDataAdapter( query, MyConnection );

ds = new DataSet();

adapter.Fill( ds, "Albums" );

LstAlbums.Items.Clear();

LstAlbums.Items.Add( new ListItem( "(all)", "-1" ) );

foreach( DataRow album in ds.Tables["Albums"].Rows )

LstAlbums.Items.Add(

new ListItem( album["album"].ToString(), album["id"].ToString() ) );

}

private void LoadSongs()

{

SqlConnection conn = new SqlConnection( MyConnection );

SqlCommand cmd = new SqlCommand( "GetSongsByPage", conn );

SqlDataReader reader = null;

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add( new SqlParameter( "@CurrentPage", SqlDbType.Int ) );

cmd.Parameters["@CurrentPage"].Value = CurrentPageNumber;

cmd.Parameters.Add( new SqlParameter( "@PageSize", SqlDbType.Int ) );

cmd.Parameters["@PageSize"].Value = SongGrid.PageSize;

cmd.Parameters.Add( new SqlParameter( "@ArtistID", SqlDbType.Int ) );

cmd.Parameters["@ArtistID"].Value = Int32.Parse(LstArtists.SelectedItem.Value);

cmd.Parameters.Add( new SqlParameter( "@AlbumID", SqlDbType.Int ) );

cmd.Parameters["@AlbumID"].Value = Int32.Parse(LstAlbums.SelectedItem.Value);

cmd.Parameters.Add( new SqlParameter( "@TotalRecs", SqlDbType.Int ) );

cmd.Parameters["@TotalRecs"].Direction = ParameterDirection.Output;

try

{

conn.Open();

reader = cmd.ExecuteReader();

SongGrid.DataSource = reader;

SongGrid.DataBind();

}

finally

{

conn.Close();

}

LblCurrentPage.Text = CurrentPageNumber.ToString();

int TotalRecs = ((int) cmd.Parameters["@TotalRecs"].Value);

decimal TotalPages = Decimal.Parse(TotalRecs.ToString()) /

SongGrid.PageSize;

LblTotalPages.Text = (System.Math.Ceiling(Double.Parse(

TotalPages.ToString()))).ToString();

if( CurrentPageNumber == 1 )

{

LnkPrevPage.Enabled = false;

LnkNextPage.Enabled = true;

}

else

{

if( CurrentPageNumber == Int32.Parse(LblTotalPages.Text))

{

LnkNextPage.Enabled = false;

LnkPrevPage.Enabled = true;

}

else

{

LnkNextPage.Enabled = true;

LnkPrevPage.Enabled = true;

}

}

}

#region Web Form Designer generated code

private void LstArtists_SelectedIndexChanged(object sender, System.EventArgs e)

{

CurrentPageNumber = 1;

LoadAlbums();

LoadSongs();

}

private void LstAlbums_SelectedIndexChanged(object sender, System.EventArgs e)

{

CurrentPageNumber = 1;

LoadSongs();

}

private void PageLink_OnClick(object sender, System.Web.UI.WebControls.CommandEventArgs e)

{

switch( e.CommandName )

{

case "First":

CurrentPageNumber = 1;

break;

case "Last":

CurrentPageNumber = Int32.Parse(LblTotalPages.Text);

break;

case "Next":

CurrentPageNumber = Int32.Parse(LblCurrentPage.Text) + 1;

break;

case "Previous":

CurrentPageNumber = Int32.Parse(LblCurrentPage.Text) - 1;

break;

}

LoadSongs();

}

}

}

Sorting Columns

The DataGrid also support sorting by clicking on the column headings. Again, you can use the built-in support for sorting to quickly add this capability to your web pages that use the DataGrid.

To enable sorting, perform these 2 actions:

1—Enable the AllowSorting property for the DataGrid.

2—Enter a SortExpression if you are using the DataGrid ColumnsCollection property.

3—Setup an event handler for the SortCommand event from the grid and query the database again as needed.

See example: MusicGrid8

You may want to consider adding a Label control to the web form with Visible property set to false and use it to store the desired sort sequence. This will come in handy if you try to add paging and sorting support to the same grid since the ViewState will track the value in the hidden label for you.

See example: MusicGrid9

This example requires a new stored procedure that accepts one additional parameter, the sort order expression. See GetSongsByPageSorted.sql in the MusicGrid9 example folder.

GetSongsByPageSorted Procedure:

CREATE PROCEDURE [dbo].[GetSongsByPageSorted]

@CurrentPage int,

@PageSize int,

@ArtistID int,

@AlbumID int,

@SortExpression nvarchar(100),

@TotalRecs int output

AS

-- Create a temp table

-- Add an ID column to count the records

CREATE TABLE #TempTable

(

ID int IDENTITY PRIMARY KEY,

artist nvarchar(255),

album nvarchar(255),

title nvarchar(255),

genre nvarchar(80),

song_len nvarchar(10)

)

-- Fill the temporary table

DECLARE @QueryStatement varchar(4000)

-- Build the query string

-- We must do things this way because SQL Server

-- does not support SELECT ... ORDER BY @SortExpression

IF @AlbumID <> -1

BEGIN

SELECT @QueryStatement = 'INSERT INTO #TempTable ' +

'(artist,album,title,genre,song_len) ' +

'SELECT artist,album,title,genre,song_len ' +

'FROM song ' +

'INNER JOIN artist ON artist.id = song.artist_id ' +

'INNER JOIN album ON album.id = song.album_id ' +

'WHERE song.artist_id = ' + CAST(@ArtistID AS char(10)) +

'AND song.album_id = ' + CAST(@AlbumID AS char(10)) +

'ORDER BY ' + @SortExpression

END

ELSE

BEGIN

SELECT @QueryStatement = 'INSERT INTO #TempTable ' +

'(artist,album,title,genre,song_len) ' +

'SELECT artist,album,title,genre,song_len ' +

'FROM song ' +

'INNER JOIN artist ON artist.id = song.artist_id ' +

'INNER JOIN album ON album.id = song.album_id ' +

'WHERE song.artist_id = ' + CAST(@ArtistID AS char(10)) +

'ORDER BY ' + @SortExpression

END

-- Run the query using the EXEC command

EXEC(@QueryStatement)

-- Create variables to identify the first

-- and last record that should be selected

DECLARE @FirstRec int, @LastRec int

SELECT @FirstRec = (@CurrentPage - 1) * @PageSize

SELECT @LastRec = (@CurrentPage * @PageSize + 1)

-- Select one page of records

SELECT artist,album,title,genre,song_len

FROM #TempTable

WHERE ID > @FirstRec AND ID < @LastRec

-- Return total number of records

SELECT @TotalRecs = COUNT(*) FROM #TempTable

GO

BoundColumns

As we saw earlier, you can bind database fields to data grid columns by modifying the Columns collection of the DataGrid object. This allows to to further customize the grid.

Separating Code from Presentation

Chapter 7: Working with ASP.NET Server Controls Templates

Templates provide you an easy way to control the appearance of some of the controls on a web page. Not all controls support using templates. Only the DataGrid, DataList and Repeater controls provided with .NET can use templates.

What Is a Template?

A template is a repeating or non-repeating set of HTML or .NET controls mixed with the data you are displaying. While style sheets can be used to change things like font, colors or other properties, templates are used to inject a pattern into the final HTML output. Naturally you can combine templates with style sheets.

You can create your own server controls that support templates, but for now let's see how to use a template with the 3 standard .NET controls that support them.

DataGrid .NET Server Control

As you have seen already, the DataGrid control provides a multicolumn, data-bound grid in which you can display information in a table format. It can be bound to many different kinds of collections including an ArrayList, DataView or DataSet objects.

The HTML table that the DataGrid generates is made up from four parts:

Table Header—The top row of the table.

Table Row—Spans the width of the table, each containing one or more columns.

Table Column—Spans the height of the table, containing one or more rows.

Table Footer—The last row in the table.

By default, the DataGrid will display all information from the object it is bound to, by creating one row per record (or element in an ArrayList), and a new column for each column (or public property in an ArrayList).

You can customize this behavior by creating column objects as we have already seen. The following types of columns are supported:

BoundColumn—Allows you to explicitly connect a particular field from the data source to the table's column, with options to control style and formatting of the data.

HyperLinkColumn—Enables you to create a link using one of the columns from the data source. Useful for creating 'Edit' or 'Details' links.

ButtonColumn—Similar to the HyperLinkColumn, but displayed as a button to the user. The text for the button can be static or dynamic.

EditCommandColumn—Allows you to add editing features to a DataGrid. This type of column will change the behavior of the DataGrid from a read-only table to a table with text boxes, drop-down lists or check boxes which allows the user to change the records. We will examine this in the next chapter.

TemplateColumn—Gives you precise control over what is rendered by the DataGrid. You can combine HTML and server controls to design a custom layout for the column.

When you create a TemplateColumn, it is made up from 4 different parts:

HeaderTemplate—Used to control the rendering of the header row for the column.

FooterTemplate—Used to control the rendering of the footer row.

ItemTemplate—Controls the rendering of the individual cells in the column.

EditItemTemplate—Defines how items are rendered in Edit Mode.

The Basic DataGrid

We have already learned how to use the DataGrid to display rows and columns of information.

Getting More Control Using Column Objects

You can use the Columns collection property of the DataGrid to customize the appearance of the columns shown on the grid.

The Ultimate Control: Template Columns

When you add one or more template columns to a DataGrid, the column will no longer be connected directly to a field in the data source. Instead, you will now add your own text, formatting and data fields using code. You can customize the appearance by entering static HTML codes or inserting .NET code (Visual Basic or C# usually) into the template that will be executed by the web server when the page is rendered.

Code can be inserted into the template by enclosing the code inside special “<%#” and “%>” tags. This is very similar to the way regular ASP programming works and is also the way many other popular web application languages such as PHP work. Remember that code inserted this way will not be checked at compile time. Instead, the web server will compile and execute that code on the fly as needed. These special tags can be used in many places, not just in templates.

The only disadvantage to using templates is that you must enter the template definitions yourself. If you attempt to enter code graphically using Visual Studio.NET, you will find that the code you type in will be converted to standard HTML and will not be executed. In order to enter you template code, you need to right-click on your form and select the “View HTML Source” option.

So how do you access the information from the data source from within these special code sections you may ask? The Container object of the DataGrid has a DataItem property that holds the current row. Since HTML is a text-only format, you must convert the data into a string before attempting to insert the information into the rendered output. This can be awkward in some cases. The DataBinder class can be used to make these conversions to string easy, at the price of a small performance hit.

Example:

<%@ Page language="c#" Codebehind="WebForm1.aspx.cs" AutoEventWireup="false" Inherits="MusicGrid7.WebForm1" %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >

<HTML>

<HEAD>

<title>WebForm1</title>

<meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">

<meta name="CODE_LANGUAGE" Content="C#">

<meta name="vs_defaultClientScript" content="JavaScript">

<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">

</HEAD>

<body>

<form id="Form1" method="post" runat="server">

<P>

<TABLE id="TblLists" cellSpacing="1" cellPadding="1" width="50%" align="center" border="0">

<TR>

<TD align="right">

<asp:Label id="LblArtists" runat="server" Font-Bold="True">Artists:</asp:Label></TD>

<TD>

<asp:DropDownList id="LstArtists" runat="server" Width="147" Height="22" AutoPostBack="True"></asp:DropDownList></TD>

<TD></TD>

</TR>

<TR>

<TD align="right">

<asp:Label id="LblAlbums" runat="server" Font-Bold="True">Albums:</asp:Label></TD>

<TD>

<asp:DropDownList id="LstAlbums" runat="server" Width="147px" Height="22px" AutoPostBack="True"></asp:DropDownList></TD>

<TD></TD>

</TR>

<TR>

<TD align="right"></TD>

<TD></TD>

<TD></TD>

</TR>

</TABLE>

</P>

<center>

<asp:DataGrid id="SongGrid" runat="server" AllowPaging="True" AllowCustomPaging="True" AutoGenerateColumns="False"

BackColor="#C0FFC0" Width="90%">

<AlternatingItemStyle BackColor="#C0C0FF"></AlternatingItemStyle>

<HeaderStyle Font-Size="Large" Font-Italic="True" Font-Bold="True" ForeColor="Yellow" BackColor="Blue"></HeaderStyle>

<Columns>

<asp:TemplateColumn>

<HeaderTemplate>

<CENTER><B>Artist: <%# LstArtists.SelectedItem.Text + "<br>Album: " + LstAlbums.SelectedItem.Text %></B></CENTER>

</HeaderTemplate>

<ItemTemplate>

<TABLE width="100%">

<TR>

<TD align="left" colspan="2"><B>

<%# (LstAlbums.SelectedItem.Text == "(all)" ? (DataBinder.Eval(Container.DataItem,"album") + " -- ") : "") + "<i>" + DataBinder.Eval(Container.DataItem, "title") + "</i>" %>

</B>

</td>

</tr>

<tr>

<TD align="left"><B>

<%# DataBinder.Eval(Container.DataItem, "genre")%>

</B>

</td>

<td align="right"><B>

<%# string.Format("{0}:{1:D2}", Int32.Parse(DataBinder.Eval(Container.DataItem,"song_len").ToString())/60, Int32.Parse(DataBinder.Eval(Container.DataItem,"song_len").ToString())%60) %> m:ss

</B>

</TD>

</TR>

</TABLE>

</ItemTemplate>

</asp:TemplateColumn>

</Columns>

<PagerStyle Visible="False"></PagerStyle>

</asp:DataGrid>

</center>

<CENTER>&nbsp;</CENTER>

<CENTER>

<asp:Label id="Label1" runat="server" Font-Bold="True">Page</asp:Label>

<asp:Label id="LblCurrentPage" runat="server" Font-Bold="True">1</asp:Label>

<asp:Label id="Label2" runat="server" Font-Bold="True">of</asp:Label>

<asp:Label id="LblTotalPages" runat="server" Font-Bold="True"></asp:Label></CENTER>

<CENTER>&nbsp;</CENTER>

<CENTER>

<asp:LinkButton id="LnkFirstPage" runat="server" CommandName="First">[First Page]</asp:LinkButton>&nbsp;

<asp:LinkButton id="LnkPrevPage" runat="server" CommandName="Previous">[Previous Page]</asp:LinkButton>&nbsp;

<asp:LinkButton id="LnkNextPage" runat="server" CommandName="Next">[Next Page]</asp:LinkButton>&nbsp;

<asp:LinkButton id="LnkLastPage" runat="server" CommandName="Last">[Last Page]</asp:LinkButton></CENTER>

<CENTER>&nbsp;</CENTER>

<CENTER>

<asp:Label id="SortOrder" runat="server" Visible="False">artist,album,title</asp:Label></CENTER>

</form>

</body>

</HTML>

CodeBehind:

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

namespace MusicGrid7

{

/// <summary>

/// Summary description for WebForm1.

/// </summary>

public class WebForm1 : System.Web.UI.Page

{

protected System.Web.UI.WebControls.Label LblArtists;

protected System.Web.UI.WebControls.DropDownList LstArtists;

protected System.Web.UI.WebControls.Label LblAlbums;

protected System.Web.UI.WebControls.DropDownList LstAlbums;

private static string MyConnection = "Server=KI;Database=Music;Integrated Security=SSPI;";

protected System.Web.UI.WebControls.Label Label1;

protected System.Web.UI.WebControls.Label LblCurrentPage;

protected System.Web.UI.WebControls.Label Label2;

protected System.Web.UI.WebControls.Label LblTotalPages;

protected System.Web.UI.WebControls.LinkButton LnkFirstPage;

protected System.Web.UI.WebControls.LinkButton LnkPrevPage;

protected System.Web.UI.WebControls.LinkButton LnkNextPage;

protected System.Web.UI.WebControls.LinkButton LnkLastPage;

protected System.Web.UI.WebControls.DataGrid SongGrid;

protected System.Web.UI.WebControls.Label SortOrder;

private void Page_Load(object sender, System.EventArgs e)

{

if( !Page.IsPostBack )

{

LoadArtists();

LoadAlbums();

LoadSongs();

}

}

private void LoadArtists()

{

string query;

SqlDataAdapter adapter;

DataSet ds;

query = "SELECT artist,id FROM artist ORDER BY artist";

ds = new DataSet();

adapter = new SqlDataAdapter( query, MyConnection );

adapter.Fill( ds, "Artists" );

LstArtists.Items.Clear();

foreach( DataRow artist in ds.Tables["Artists"].Rows )

LstArtists.Items.Add(

new ListItem( artist["artist"].ToString(), artist["id"].ToString() ) );

}

private void LoadAlbums()

{

string query;

SqlDataAdapter adapter;

DataSet ds;

query = "SELECT album,id FROM album WHERE artist_id = " +

LstArtists.SelectedItem.Value + " ORDER BY album";

adapter = new SqlDataAdapter( query, MyConnection );

ds = new DataSet();

adapter.Fill( ds, "Albums" );

LstAlbums.Items.Clear();

LstAlbums.Items.Add( new ListItem( "(all)", "-1" ) );

foreach( DataRow album in ds.Tables["Albums"].Rows )

LstAlbums.Items.Add(

new ListItem( album["album"].ToString(), album["id"].ToString() ) );

}

private void LoadSongs()

{

SqlConnection conn = new SqlConnection( MyConnection );

SqlCommand cmd = new SqlCommand( "GetSongsByPageSorted", conn );

SqlDataReader reader = null;

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add( new SqlParameter( "@CurrentPage", SqlDbType.Int ) );

cmd.Parameters["@CurrentPage"].Value = Int32.Parse(LblCurrentPage.Text);

cmd.Parameters.Add( new SqlParameter( "@PageSize", SqlDbType.Int ) );

cmd.Parameters["@PageSize"].Value = SongGrid.PageSize;

cmd.Parameters.Add( new SqlParameter( "@ArtistID", SqlDbType.Int ) );

cmd.Parameters["@ArtistID"].Value = Int32.Parse(LstArtists.SelectedItem.Value);

cmd.Parameters.Add( new SqlParameter( "@AlbumID", SqlDbType.Int ) );

cmd.Parameters["@AlbumID"].Value = Int32.Parse(LstAlbums.SelectedItem.Value);

cmd.Parameters.Add( new SqlParameter( "@SortExpression", SqlDbType.NVarChar ) );

cmd.Parameters["@SortExpression"].Value = SortOrder.Text;

cmd.Parameters.Add( new SqlParameter( "@TotalRecs", SqlDbType.Int ) );

cmd.Parameters["@TotalRecs"].Direction = ParameterDirection.Output;

try

{

conn.Open();

reader = cmd.ExecuteReader();

SongGrid.DataSource = reader;

SongGrid.DataBind();

}

finally

{

conn.Close();

}

int TotalRecs = ((int) cmd.Parameters["@TotalRecs"].Value);

decimal TotalPages = Decimal.Parse(TotalRecs.ToString()) /

SongGrid.PageSize;

LblTotalPages.Text = (System.Math.Ceiling(Double.Parse(

TotalPages.ToString()))).ToString();

if( LblCurrentPage.Text == "1" )

{

LnkPrevPage.Enabled = false;

LnkNextPage.Enabled = true;

}

else

{

if( LblCurrentPage.Text == LblTotalPages.Text)

{

LnkNextPage.Enabled = false;

LnkPrevPage.Enabled = true;

}

else

{

LnkNextPage.Enabled = true;

LnkPrevPage.Enabled = true;

}

}

}

#region Web Form Designer generated code

private void LstArtists_SelectedIndexChanged(object sender, System.EventArgs e)

{

LblCurrentPage.Text = "1";

LoadAlbums();

LoadSongs();

}

private void LstAlbums_SelectedIndexChanged(object sender, System.EventArgs e)

{

LblCurrentPage.Text = "1";

LoadSongs();

}

private void PageLink_OnClick(object sender, System.Web.UI.WebControls.CommandEventArgs e)

{

switch( e.CommandName )

{

case "First":

LblCurrentPage.Text = "1";

break;

case "Last":

LblCurrentPage.Text = LblTotalPages.Text;

break;

case "Next":

LblCurrentPage.Text = (Int32.Parse(LblCurrentPage.Text) + 1).ToString();

break;

case "Previous":

LblCurrentPage.Text = (Int32.Parse(LblCurrentPage.Text) - 1).ToString();

break;

}

LoadSongs();

}

private void SongGrid_SortCommand(object source, System.Web.UI.WebControls.DataGridSortCommandEventArgs e)

{

SortOrder.Text = e.SortExpression;

LoadSongs();

}

}

}

DataList .NET Server Control

In many ways, the DataList control is similar to the DataGrid control, however a DataList requires a template and it must be bound to a data source that supports the ICollection interface.

A DataList supports 7 different templates:

HeaderTemplate—Defines how the header is rendered.

ItemTemplate—Defines how items are rendered.

AlternatingItemTemplate—Defines how alternating rows are rendered.

EditItemTemplate—Defines how things are rendered in edit mode.

SelectedItemTemplate—Defines how selected items are rendered.

SeparatorTemplate—Defines a separator for each item.

FooterTemplate—Defines how the footer is rendered.

By default, a DataList displays items in a single vertical list, but you can set the RepeatColumns property to control the number of columns across you want to see. When you set this to a value greater than 1, you also can control some other properties including a RepeatLayout (Flow or Table) and a RepeatDirection (Vertical or Horizontal).

Example: MusicGrid11

<%@ Page language="c#" Codebehind="WebForm1.aspx.cs" AutoEventWireup="false" Inherits="MusicGrid7.WebForm1" %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >

<HTML>

<HEAD>

<title>WebForm1</title>

<meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">

<meta name="CODE_LANGUAGE" Content="C#">

<meta name="vs_defaultClientScript" content="JavaScript">

<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">

</HEAD>

<body>

<form id="Form1" method="post" runat="server">

<P>

<TABLE id="TblLists" cellSpacing="1" cellPadding="1" width="50%" align="center" border="0">

<TR>

<TD align="right">

<asp:Label id="LblArtists" runat="server" Font-Bold="True">Artists:</asp:Label></TD>

<TD>

<asp:DropDownList id="LstArtists" runat="server" Width="147" Height="22" AutoPostBack="True"></asp:DropDownList></TD>

<TD></TD>

</TR>

<TR>

<TD align="right">

<asp:Label id="LblAlbums" runat="server" Font-Bold="True">Albums:</asp:Label></TD>

<TD>

<asp:DropDownList id="LstAlbums" runat="server" Width="147px" Height="22px" AutoPostBack="True"></asp:DropDownList></TD>

<TD></TD>

</TR>

<TR>

<TD align="right"></TD>

<TD></TD>

<TD></TD>

</TR>

</TABLE>

</P>

<center>

<asp:DataList id="SongList" runat="server" RepeatColumns="1" BorderWidth="5px" BorderStyle="Double"

GridLines="Both">

<HeaderTemplate>

<center><b>Artist:

<%# LstArtists.SelectedItem.Text + "<br>Album: " + LstAlbums.SelectedItem.Text %>

</b>

</center>

</HeaderTemplate>

<AlternatingItemStyle BackColor="#FFE0C0"></AlternatingItemStyle>

<ItemTemplate>

<font color="#008000">

<%# (LstAlbums.SelectedItem.Value == "-1" ? (DataBinder.Eval(Container.DataItem,"album") + " -- ") : "") + "<i>" + DataBinder.Eval(Container.DataItem,"title") + "</i>" %>

<br>

<%# DataBinder.Eval(Container.DataItem,"genre") + " -- " + string.Format( "{0}:{1:D2}", Int32.Parse(DataBinder.Eval(Container.DataItem, "song_len").ToString())/60, Int32.Parse(DataBinder.Eval(Container.DataItem, "song_len").ToString())%60) %>

</font>

</ItemTemplate>

<SeparatorTemplate>

<P>&nbsp;</P>

</SeparatorTemplate>

<AlternatingItemTemplate>

<font color="#000080">

<%# (LstAlbums.SelectedItem.Value == "-1" ? (DataBinder.Eval(Container.DataItem,"album") + " -- ") : "") + "<i>" + DataBinder.Eval(Container.DataItem,"title") + "</i>" %>

<br>

<%# DataBinder.Eval(Container.DataItem,"genre") + " -- " + string.Format( "{0}:{1:D2}", Int32.Parse(DataBinder.Eval(Container.DataItem, "song_len").ToString())/60, Int32.Parse(DataBinder.Eval(Container.DataItem, "song_len").ToString())%60) %>

</font>

</AlternatingItemTemplate>

</asp:DataList>

</center>

<CENTER>&nbsp;</CENTER>

<CENTER>

<asp:Label id="Label1" runat="server" Font-Bold="True">Page</asp:Label>

<asp:Label id="LblCurrentPage" runat="server" Font-Bold="True">1</asp:Label>

<asp:Label id="Label2" runat="server" Font-Bold="True">of</asp:Label>

<asp:Label id="LblTotalPages" runat="server" Font-Bold="True"></asp:Label></CENTER>

<CENTER>&nbsp;</CENTER>

<CENTER>

<asp:LinkButton id="LnkFirstPage" runat="server" CommandName="First">[First Page]</asp:LinkButton>&nbsp;

<asp:LinkButton id="LnkPrevPage" runat="server" CommandName="Previous">[Previous Page]</asp:LinkButton>&nbsp;

<asp:LinkButton id="LnkNextPage" runat="server" CommandName="Next">[Next Page]</asp:LinkButton>&nbsp;

<asp:LinkButton id="LnkLastPage" runat="server" CommandName="Last">[Last Page]</asp:LinkButton></CENTER>

<CENTER>&nbsp;</CENTER>

<CENTER>

<asp:Label id="SortOrder" runat="server" Visible="False">artist,album,title</asp:Label>

<asp:Label id="LblPageSize" runat="server" Visible="False">12</asp:Label></CENTER>

</form>

</body>

</HTML>

Code Behind:

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

namespace MusicGrid7

{

/// <summary>

/// Summary description for WebForm1.

/// </summary>

public class WebForm1 : System.Web.UI.Page

{

protected System.Web.UI.WebControls.Label LblArtists;

protected System.Web.UI.WebControls.DropDownList LstArtists;

protected System.Web.UI.WebControls.Label LblAlbums;

protected System.Web.UI.WebControls.DropDownList LstAlbums;

private static string MyConnection = "Server=KI;Database=Music;Integrated Security=SSPI;";

protected System.Web.UI.WebControls.Label Label1;

protected System.Web.UI.WebControls.Label LblCurrentPage;

protected System.Web.UI.WebControls.Label Label2;

protected System.Web.UI.WebControls.Label LblTotalPages;

protected System.Web.UI.WebControls.LinkButton LnkFirstPage;

protected System.Web.UI.WebControls.LinkButton LnkPrevPage;

protected System.Web.UI.WebControls.LinkButton LnkNextPage;

protected System.Web.UI.WebControls.LinkButton LnkLastPage;

protected System.Web.UI.WebControls.DataList SongList;

protected System.Web.UI.WebControls.Label LblPageSize;

protected System.Web.UI.WebControls.Label SortOrder;

private void Page_Load(object sender, System.EventArgs e)

{

if( !Page.IsPostBack )

{

LoadArtists();

LoadAlbums();

LoadSongs();

}

}

private void LoadArtists()

{

string query;

SqlDataAdapter adapter;

DataSet ds;

query = "SELECT artist,id FROM artist ORDER BY artist";

ds = new DataSet();

adapter = new SqlDataAdapter( query, MyConnection );

adapter.Fill( ds, "Artists" );

LstArtists.Items.Clear();

foreach( DataRow artist in ds.Tables["Artists"].Rows )

LstArtists.Items.Add(

new ListItem( artist["artist"].ToString(), artist["id"].ToString() ) );

}

private void LoadAlbums()

{

string query;

SqlDataAdapter adapter;

DataSet ds;

query = "SELECT album,id FROM album WHERE artist_id = " +

LstArtists.SelectedItem.Value + " ORDER BY album";

adapter = new SqlDataAdapter( query, MyConnection );

ds = new DataSet();

adapter.Fill( ds, "Albums" );

LstAlbums.Items.Clear();

LstAlbums.Items.Add( new ListItem( "(all)", "-1" ) );

foreach( DataRow album in ds.Tables["Albums"].Rows )

LstAlbums.Items.Add(

new ListItem( album["album"].ToString(), album["id"].ToString() ) );

}

private void LoadSongs()

{

SqlConnection conn = new SqlConnection( MyConnection );

SqlCommand cmd = new SqlCommand( "GetSongsByPageSorted", conn );

SqlDataReader reader = null;

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add( new SqlParameter( "@CurrentPage", SqlDbType.Int ) );

cmd.Parameters["@CurrentPage"].Value = Int32.Parse(LblCurrentPage.Text);

cmd.Parameters.Add( new SqlParameter( "@PageSize", SqlDbType.Int ) );

cmd.Parameters["@PageSize"].Value = Int32.Parse(LblPageSize.Text);

cmd.Parameters.Add( new SqlParameter( "@ArtistID", SqlDbType.Int ) );

cmd.Parameters["@ArtistID"].Value = Int32.Parse(LstArtists.SelectedItem.Value);

cmd.Parameters.Add( new SqlParameter( "@AlbumID", SqlDbType.Int ) );

cmd.Parameters["@AlbumID"].Value = Int32.Parse(LstAlbums.SelectedItem.Value);

cmd.Parameters.Add( new SqlParameter( "@SortExpression", SqlDbType.NVarChar ) );

cmd.Parameters["@SortExpression"].Value = SortOrder.Text;

cmd.Parameters.Add( new SqlParameter( "@TotalRecs", SqlDbType.Int ) );

cmd.Parameters["@TotalRecs"].Direction = ParameterDirection.Output;

try

{

conn.Open();

reader = cmd.ExecuteReader();

SongList.DataSource = reader;

SongList.DataBind();

}

finally

{

conn.Close();

}

int TotalRecs = ((int) cmd.Parameters["@TotalRecs"].Value);

decimal TotalPages = Decimal.Parse(TotalRecs.ToString()) /

Int32.Parse(LblPageSize.Text);

LblTotalPages.Text = (System.Math.Ceiling(Double.Parse(

TotalPages.ToString()))).ToString();

if( LblCurrentPage.Text == "1" )

{

LnkPrevPage.Enabled = false;

LnkNextPage.Enabled = true;

}

else

{

if( LblCurrentPage.Text == LblTotalPages.Text)

{

LnkNextPage.Enabled = false;

LnkPrevPage.Enabled = true;

}

else

{

LnkNextPage.Enabled = true;

LnkPrevPage.Enabled = true;

}

}

}

#region Web Form Designer generated code

private void LstArtists_SelectedIndexChanged(object sender, System.EventArgs e)

{

LblCurrentPage.Text = "1";

LoadAlbums();

LoadSongs();

}

private void LstAlbums_SelectedIndexChanged(object sender, System.EventArgs e)

{

LblCurrentPage.Text = "1";

LoadSongs();

}

private void PageLink_OnClick(object sender, System.Web.UI.WebControls.CommandEventArgs e)

{

switch( e.CommandName )

{

case "First":

LblCurrentPage.Text = "1";

break;

case "Last":

LblCurrentPage.Text = LblTotalPages.Text;

break;

case "Next":

LblCurrentPage.Text = (Int32.Parse(LblCurrentPage.Text) + 1).ToString();

break;

case "Previous":

LblCurrentPage.Text = (Int32.Parse(LblCurrentPage.Text) - 1).ToString();

break;

}

LoadSongs();

}

private void SongGrid_SortCommand(object source, System.Web.UI.WebControls.DataGridSortCommandEventArgs e)

{

SortOrder.Text = e.SortExpression;

LoadSongs();

}

}

}

Repeater .NET Server Control

The Repeater control is similar to the DataList in that it requires a DataSource and some templates to work. It also applies its templates over and over to the rows in the data source. Unlike the DataList or DataGrid however, the Repeater control does not have predefined look at all. This control is useful when speed is more important a polished look.

Exercise: Build a web application with a Repeater control.

Using Web Controls to Build HTML Forms

Chapter 8: Gathering Data with ASP.NET Web Forms

So far we have mostly used the DataGrid to display information to users. The next step is to learn how to gather information from the user. We will be using a number of ASP.NET server controls to build input forms easily.

Button ASP.NET Server Control

There are 3 different flavors of buttons supported by ASP.NET:

Button—A typical HTML pushbutton.

LinkButton—A hyperlink button displayed underlined usually.

ImageButton—A button with an image.

Each button has basically the same purpose, namely to generate an event that posts the data back to the server where the code behind can process it.

Button

Standard HTML-style pushbutton.

LinkButton

Same as a standard button except it uses JavaScript to send the Click event back to the web server.

ImageButton

Very similar to the other buttons, with these extra properties:

AlternateText—Gets or sets the alternate text to be displayed when the image cannot be shown. Some browsers also use this to popup a tooltip.

ImageAlign—Controls alignment of the image.

ImageUrl—Specifies the path to the image to be displayed on the button.

Handling Button Events

Just hookup the Click event to a new event handler in the code behind. The ImageButton's handler will receive an ImageClickEventArgs object instead of the simpler EventArgs. The ImageClickEventArgs object holds the X and Y coordinates where the user clicked within the image. It is useful for handling image maps, where clicking on different areas within the image has different results.

Binding a Button to a Data Source

While you cannot directly bind a data source to a button like you can with a DataGrid, you can bind one of the attributes of a button to a field in the data source.

TextBox ASP.NET Server Control

This control is used when users must enter text, dates or numbers. There are 3 flavors of TextBox controls:

Single-Line

Multi-Line

Password

Single-Line TextBox

This control is generally used when the user must enter a short amount of text, such as first name, last name, zip code, etc. It has many properties that can be used to change its appearance including, ForeColor, BackColor, Font, Columns, and MaxLength. The Columns property determines the display width of the TextBox, while the MaxLength property controls how many characters the user can enter into the field.

Multi-Line TextBox

This control allows the user to enter more than one line of text. When using this control, the Rows property determines how tall the text box is, while the Columns property control the width of the text box. The MaxLength property is ignored by this control.

Password TextBox

This is basically the same as a Single-Line TextBox, except it displays an '*' instead of the characters the user types in.

Raising and Handling Events With the TextBox

If you sent a TextBox control's AutoPostBack property to true, then there are many events generated that you can handle, including TextChanged and others.

Binding a TextBox to a Data Source

Just like with buttons, you can bind a field from your data source to one of the properties on a text box using the data binding procedures we previously discussed.

DropDownList ASP.NET Server Control

The DropDownList control enables users to select an item from a list. The control will hold an array of ListItem objects. There are 3 ways to add a ListItem to a DropDownList control.

Add text as nested element: <asp:ListItem Value=”1”>Item Text Here</asp:ListItem>

Add text as a property: <asp:ListItem Value=”2” Text=”Item Text Here” />

Add a new ListItem using code: list.Items.Add( new ListItem(“Item Text Here”,”3”))

Raising and Handling Events and Determining Item Selection

The most common event you want to handle is probably the SelectedIndexChanged event. Again, the AutoPostBack property must be set to true for this event to automatically send the page back to the user if you want to handle the event immediately.

NOTE: Remember that DropDownList controls number selected items starting with zero.

Binding a DropDownList to a Data Source

If you wish, you can bind a data source to a DropDownList in a way that is similar to a DataGrid. You will have to use the DataTextField and DataValueField properties to connect the correct data source columns to the DropDownList. When doing this, the ListItem objects are automatically created for you.

Example: MusicGrid12

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

namespace MusicGrid7

{

/// <summary>

/// Summary description for WebForm1.

/// </summary>

public class WebForm1 : System.Web.UI.Page

{

protected System.Web.UI.WebControls.Label LblArtists;

protected System.Web.UI.WebControls.DropDownList LstArtists;

protected System.Web.UI.WebControls.Label LblAlbums;

protected System.Web.UI.WebControls.DropDownList LstAlbums;

private static string MyConnection = "Server=KI;Database=Music;Integrated Security=SSPI;";

protected System.Web.UI.WebControls.Label Label1;

protected System.Web.UI.WebControls.Label LblCurrentPage;

protected System.Web.UI.WebControls.Label Label2;

protected System.Web.UI.WebControls.Label LblTotalPages;

protected System.Web.UI.WebControls.LinkButton LnkFirstPage;

protected System.Web.UI.WebControls.LinkButton LnkPrevPage;

protected System.Web.UI.WebControls.LinkButton LnkNextPage;

protected System.Web.UI.WebControls.LinkButton LnkLastPage;

protected System.Web.UI.WebControls.DataList SongList;

protected System.Web.UI.WebControls.Label LblPageSize;

protected System.Web.UI.WebControls.Label SortOrder;

private void Page_Load(object sender, System.EventArgs e)

{

if( !Page.IsPostBack )

{

LoadArtists();

LoadAlbums();

LoadSongs();

}

}

private void LoadArtists()

{

string query;

SqlDataAdapter adapter;

DataSet ds;

query = "SELECT artist,id FROM artist ORDER BY artist";

ds = new DataSet();

adapter = new SqlDataAdapter( query, MyConnection );

adapter.Fill( ds, "Artists" );

LstArtists.DataSource = ds.Tables["Artists"];

LstArtists.DataBind();

}

private void LoadAlbums()

{

string query;

SqlDataAdapter adapter;

DataSet ds;

query = "SELECT album,id FROM album WHERE artist_id = " +

LstArtists.SelectedItem.Value + " ORDER BY album";

adapter = new SqlDataAdapter( query, MyConnection );

ds = new DataSet();

adapter.Fill( ds, "Albums" );

LstAlbums.DataSource = ds.Tables["Albums"];

LstAlbums.DataBind();

}

private void LoadSongs()

{

SqlConnection conn = new SqlConnection( MyConnection );

SqlCommand cmd = new SqlCommand( "GetSongsByPageSorted", conn );

SqlDataReader reader = null;

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add( new SqlParameter( "@CurrentPage", SqlDbType.Int ) );

cmd.Parameters["@CurrentPage"].Value = Int32.Parse(LblCurrentPage.Text);

cmd.Parameters.Add( new SqlParameter( "@PageSize", SqlDbType.Int ) );

cmd.Parameters["@PageSize"].Value = Int32.Parse(LblPageSize.Text);

cmd.Parameters.Add( new SqlParameter( "@ArtistID", SqlDbType.Int ) );

cmd.Parameters["@ArtistID"].Value = Int32.Parse(LstArtists.SelectedItem.Value);

cmd.Parameters.Add( new SqlParameter( "@AlbumID", SqlDbType.Int ) );

cmd.Parameters["@AlbumID"].Value = Int32.Parse(LstAlbums.SelectedItem.Value);

cmd.Parameters.Add( new SqlParameter( "@SortExpression", SqlDbType.NVarChar ) );

cmd.Parameters["@SortExpression"].Value = SortOrder.Text;

cmd.Parameters.Add( new SqlParameter( "@TotalRecs", SqlDbType.Int ) );

cmd.Parameters["@TotalRecs"].Direction = ParameterDirection.Output;

try

{

conn.Open();

reader = cmd.ExecuteReader();

SongList.DataSource = reader;

SongList.DataBind();

}

finally

{

conn.Close();

}

int TotalRecs = ((int) cmd.Parameters["@TotalRecs"].Value);

decimal TotalPages = Decimal.Parse(TotalRecs.ToString()) /

Int32.Parse(LblPageSize.Text);

LblTotalPages.Text = (System.Math.Ceiling(Double.Parse(

TotalPages.ToString()))).ToString();

if( LblCurrentPage.Text == "1" )

{

LnkPrevPage.Enabled = false;

LnkNextPage.Enabled = true;

}

else

{

if( LblCurrentPage.Text == LblTotalPages.Text)

{

LnkNextPage.Enabled = false;

LnkPrevPage.Enabled = true;

}

else

{

LnkNextPage.Enabled = true;

LnkPrevPage.Enabled = true;

}

}

}

#region Web Form Designer generated code

private void LstArtists_SelectedIndexChanged(object sender, System.EventArgs e)

{

LblCurrentPage.Text = "1";

LoadAlbums();

LoadSongs();

}

private void LstAlbums_SelectedIndexChanged(object sender, System.EventArgs e)

{

LblCurrentPage.Text = "1";

LoadSongs();

}

private void PageLink_OnClick(object sender, System.Web.UI.WebControls.CommandEventArgs e)

{

switch( e.CommandName )

{

case "First":

LblCurrentPage.Text = "1";

break;

case "Last":

LblCurrentPage.Text = LblTotalPages.Text;

break;

case "Next":

LblCurrentPage.Text = (Int32.Parse(LblCurrentPage.Text) + 1).ToString();

break;

case "Previous":

LblCurrentPage.Text = (Int32.Parse(LblCurrentPage.Text) - 1).ToString();

break;

}

LoadSongs();

}

private void SongGrid_SortCommand(object source, System.Web.UI.WebControls.DataGridSortCommandEventArgs e)

{

SortOrder.Text = e.SortExpression;

LoadSongs();

}

}

}

ListBox ASP.NET Server Control

The ListBox control is very similar to the DropDownList control, except the items are always displayed and it supports either single or multiple selection modes. Use the Rows attribute to control the number of items displayed at all times. Scroll bars are added automatically if the number of items exceeds the number of displayed rows.

Raising and Handling Events and Determining Item Selection

When the ListBox selection mode is set to single, things work very much like the DropDownList control. However, when set to multiple selection mode, then you have to handle events a bit differently.

Example: MusicGrid13

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

namespace MusicGrid13

{

/// <summary>

/// Summary description for WebForm1.

/// </summary>

public class WebForm1 : System.Web.UI.Page

{

protected System.Web.UI.WebControls.Label ArtistLabel;

protected System.Web.UI.WebControls.ListBox ArtistList;

protected System.Web.UI.WebControls.Button ArtistRefreshBtn;

protected System.Web.UI.WebControls.Label AlbumLabel;

protected System.Web.UI.WebControls.ListBox AlbumList;

protected System.Web.UI.WebControls.Button AlbumRefreshBtn;

protected System.Web.UI.WebControls.DataGrid SongGrid;

private static string MyConnection = "Server=KI;Database=Music;Integrated Security=SSPI";

private void Page_Load(object sender, System.EventArgs e)

{

if( !Page.IsPostBack )

{

LoadArtists();

LoadAlbums();

LoadSongs();

}

}

private void LoadArtists()

{

string query = "SELECT artist,id FROM artist ORDER BY artist";

SqlDataAdapter adapter = new SqlDataAdapter( query, MyConnection );

DataSet ds = new DataSet();

adapter.Fill( ds, "Artists" );

ArtistList.DataSource = ds.Tables["Artists"];

ArtistList.DataBind();

}

private void LoadAlbums()

{

string query = "SELECT album,id FROM album ";

bool first = true;

for( int i = 0; i < ArtistList.Items.Count; i++ )

{

if( ArtistList.Items[i].Selected )

{

if( first )

{

query += "WHERE ";

first = false;

}

else

query += " OR ";

query += string.Format( "artist_id = {0}", ArtistList.Items[i].Value );

}

}

query += " ORDER BY album";

SqlDataAdapter adapter = new SqlDataAdapter( query, MyConnection );

DataSet ds = new DataSet();

adapter.Fill( ds, "Albums" );

AlbumList.DataSource = ds;

AlbumList.DataBind();

}

private void LoadSongs()

{

string query = "SELECT artist,album,title,genre FROM song " +

"INNER JOIN artist ON song.artist_id = artist.id " +

"INNER JOIN album ON song.album_id = album.id ";

bool first = true;

for( int i = 0; i < AlbumList.Items.Count; i++ )

{

if( AlbumList.Items[i].Selected )

{

if( first )

{

query += "WHERE ";

first = false;

}

else

query += " OR ";

query += string.Format( "album_id = {0}", AlbumList.Items[i].Value );

}

}

query += " ORDER BY artist,album,title";

SqlDataAdapter adapter = new SqlDataAdapter( query, MyConnection );

DataSet ds = new DataSet();

adapter.Fill( ds, "Songs" );

SongGrid.DataSource = ds;

SongGrid.DataBind();

}

#region Web Form Designer generated code

private void ArtistRefreshBtn_Click(object sender, System.EventArgs e)

{

SongGrid.CurrentPageIndex = 0;

LoadAlbums();

}

private void AlbumRefreshBtn_Click(object sender, System.EventArgs e)

{

SongGrid.CurrentPageIndex = 0;

LoadSongs();

}

private void SongGrid_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)

{

SongGrid.CurrentPageIndex = e.NewPageIndex;

LoadSongs();

}

}

}

Binding a ListBox to a Data Source

Just as with the DropDownList control, the DataSource, DataTextField and DataValueField properties can be used to automatically bind a data source to the list box items.

CheckBox ASP.NET Server Control

This is a simple on/off control whose usage should be straightforward.

Raising and Handling Events and Determining Item Selection

As with the other controls, make sure the AutoPostBack property is set to true to receive events, or add a submit button to the form.

Binding a CheckBox to a Data Source

While you cannot bind a data source directly to a CheckBox control, you can use field binding to connect a field to one of the properties such as the Checked property.

CheckBoxList ASP.NET Server Control

This control is a mix between a CheckBox and a ListBox that displays a list of checkboxes.

Property	Description
RepeatColumns	The number of columns to display.
RepeatDirection	Can be set to Horizontal or Vertical.
RepeatLayout	Can be set to Table or Flow mode.

NOTE: When RepeatLayout is set to Table mode, you can also control the CellPadding and CellSpacing properties.

Raising and Handling Events and Determining Item Selection

Events are basically the same as with the other List controls.

RadioButton ASP.NET Server Control

This control is very similar to a CheckBox, except it is used to force the user to select one of several options. You have probably seen it used on web pages to select the type of credit card you are using to place an order.

Remember that you must group the radio buttons together using the GroupName property. When one button in the group is selected, all the other buttons with the same GroupName will be automatically unselected.

Raising and Handling Events and Determining Item Selection

The most important event is the CheckedChanged event. It is fired whenever the button is selected or unselected.

RadioButtonList ASP.NET Server Control

This is almost identical to the CheckBoxList control. It can be useful when you have a large number of radio buttons to display since the buttons can be arranged into kind of multicolumn grid or list view.

Chapter 10: Inputting and Editing Data Using the DataGrid and DataList

In many cases, you may want to allow users to edit the information being displayed on the DataGrid. The DataGrid and the DataList both support editing records, in slightly different ways.

An Overview of Editing Functions

In order to allow editing in a DataGrid, you must add an EditCommandColumn to the grid. A DataList requires you to create a button or link in the template to do the same thing.

Once you have added the EditCommandColumn to the DataGrid, you add an event handler for the EditCommand and put the grid into edit mode. Then you must also handle the UpdateCommand and CancelCommand events. The UpdateCommand handler should save the new values to the data source, while the CancelCommand handler will exit the edit mode.

Instead of displaying simple text values, when in editing mode the grid will display text boxes, drop down lists or other controls that allow the user to modify the information.

Event	Property	Description
EditCommand	OnEditCommand	Raised when the user clicks on the Edit button.
UpdateCommand	OnUpdateCommand	Raised when the Update button is clicked.
DeleteCommand	OnDeleteCommand	Raised when the Delete button is clicked.
CancelCommand	OnCancelCommand	Raised when the Cancel button is clicked.

Enabling Editing Using the DataGrid

You must add an EditCommandColumn to the DataGrid in order to activate editing mode. Then at a minimum, add handlers for the EditCommand, UpdateCommand and CancelCommand events.

Putting the DataGrid into Edit Mode

In your EditCommand event handler, you must specify which row is to be edited by setting the EditItemIndex property. The arguments passed to your event handler allows you to do this quite easily.

Updating Data with the DataGrid

The UpdateCommand event handler must issue the appropriate SQL UPDATE statement to save the changes to the data source.

Canceling Out of Edit Mode

Finally, if the user decides not to save the changes, they can click on the “Cancel” link, which invokes your CancelCommand event handler. That handler should set the EditItemIndex property to -1 to disable editing of the row.

Example: MusicGrid14

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Text;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

namespace MusicGrid13

{

/// <summary>

/// Summary description for WebForm1.

/// </summary>

public class WebForm1 : System.Web.UI.Page

{

protected System.Web.UI.WebControls.Label ArtistLabel;

protected System.Web.UI.WebControls.Label AlbumLabel;

protected System.Web.UI.WebControls.DataGrid SongGrid;

protected System.Web.UI.WebControls.DropDownList ArtistList;

protected System.Web.UI.WebControls.DropDownList AlbumList;

private static string MyConnection = "Server=KI;Database=Music;Integrated Security=SSPI";

private void Page_Load(object sender, System.EventArgs e)

{

if( !Page.IsPostBack )

LoadArtists();

}

private void LoadArtists()

{

string query = "SELECT artist,id FROM artist ORDER BY artist";

SqlDataAdapter adapter = new SqlDataAdapter( query, MyConnection );

DataSet ds = new DataSet();

adapter.Fill( ds, "Artists" );

ArtistList.DataSource = ds.Tables["Artists"];

ArtistList.DataBind();

ArtistList.SelectedIndex = 0;

LoadAlbums();

}

private void LoadAlbums()

{

string query = "SELECT album,id FROM album ";

bool first = true;

for( int i = 0; i < ArtistList.Items.Count; i++ )

{

if( ArtistList.Items[i].Selected )

{

if( first )

{

query += "WHERE ";

first = false;

}

else

query += " OR ";

query += string.Format( "artist_id = {0}", ArtistList.Items[i].Value );

}

}

query += " ORDER BY album";

SqlDataAdapter adapter = new SqlDataAdapter( query, MyConnection );

DataSet ds = new DataSet();

adapter.Fill( ds, "Albums" );

AlbumList.DataSource = ds;

AlbumList.DataBind();

AlbumList.SelectedIndex = 0;

LoadSongs();

}

private void LoadSongs()

{

string query = "SELECT song.id,artist,album,title,track,song_len,genre,filename,date_added,date_changed,date_filemod FROM song " +

"INNER JOIN artist ON song.artist_id = artist.id " +

"INNER JOIN album ON song.album_id = album.id ";

bool first = true;

for( int i = 0; i < AlbumList.Items.Count; i++ )

{

if( AlbumList.Items[i].Selected )

{

if( first )

{

query += "WHERE ";

first = false;

}

else

query += " OR ";

query += string.Format( "album_id = {0}", AlbumList.Items[i].Value );

}

}

query += " ORDER BY artist,album,title";

SqlDataAdapter adapter = new SqlDataAdapter( query, MyConnection );

DataSet ds = new DataSet();

adapter.Fill( ds, "Songs" );

SongGrid.DataSource = ds;

SongGrid.DataBind();

}

#region Web Form Designer generated code

private void SongGrid_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)

{

SongGrid.CurrentPageIndex = e.NewPageIndex;

LoadSongs();

}

private void ArtistList_SelectedIndexChanged(object sender, System.EventArgs e)

{

SongGrid.CurrentPageIndex = 0;

LoadAlbums();

}

private void AlbumList_SelectedIndexChanged(object sender, System.EventArgs e)

{

SongGrid.CurrentPageIndex = 0;

LoadSongs();

}

private void SongGrid_EditCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)

{

SongGrid.EditItemIndex = e.Item.ItemIndex;

LoadSongs();

}

private void SongGrid_CancelCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)

{

SongGrid.EditItemIndex = -1;

LoadSongs();

}

private void SongGrid_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)

{

string title, track, song_len, genre, filename;

int songID;

// Extract new data from grid

title = ((TextBox)e.Item.Cells[4].Controls[0]).Text;

track = ((TextBox)e.Item.Cells[5].Controls[0]).Text;

song_len = ((TextBox)e.Item.Cells[6].Controls[0]).Text;

genre = ((TextBox)e.Item.Cells[7].Controls[0]).Text;

filename = ((TextBox)e.Item.Cells[8].Controls[0]).Text;

songID = Int32.Parse( ((TextBox)e.Item.Cells[1].Controls[0]).Text );

StringBuilder sqlCmd = new StringBuilder();

sqlCmd.Append( "UPDATE song " );

sqlCmd.Append( "SET title = @Title," );

sqlCmd.Append( "track = @Track," );

sqlCmd.Append( "song_len = @SongLen," );

sqlCmd.Append( "genre = @Genre," );

sqlCmd.Append( "filename = @Filename " );

sqlCmd.Append( "WHERE id = @SongId" );

SqlConnection conn = new SqlConnection( MyConnection );

SqlCommand cmd = new SqlCommand( sqlCmd.ToString(), conn );

cmd.Parameters.Add( new SqlParameter("@Title",SqlDbType.NVarChar,255) );

cmd.Parameters.Add( new SqlParameter("@Track",SqlDbType.Char,10) );

cmd.Parameters.Add( new SqlParameter("@SongLen",SqlDbType.Char,10) );

cmd.Parameters.Add( new SqlParameter("@Genre",SqlDbType.NVarChar,50) );

cmd.Parameters.Add( new SqlParameter("@Filename",SqlDbType.NVarChar,255) );

cmd.Parameters.Add( new SqlParameter("@SongId",SqlDbType.Int) );

cmd.Parameters["@Title"].Value = title;

cmd.Parameters["@Track"].Value = track;

cmd.Parameters["@SongLen"].Value = song_len;

cmd.Parameters["@Genre"].Value = genre;

cmd.Parameters["@Filename"].Value = filename;

cmd.Parameters["@SongId"].Value = songID;

conn.Open();

cmd.ExecuteNonQuery();

conn.Close();

SongGrid.EditItemIndex = -1;

LoadSongs();

}

}

}

Enabling Editing Using the DataList

Remember the DataList cannot be bound directly to a data source, but instead uses templates to define the appearance of the list. You will need to define both an <ItemTemplate> section and an <EditTemplate> section to allow editing records in a DataList control.

Putting the DataList into Edit Mode

Here is where the biggest difference between the DataList and DataGrid is found. You must manually add a link or button to the <ItemTemplate> that allows the user to activate edit mode. The button or link will need to have its CommandName property setup correctly as shown below:

DataList Method	Event Button/Link	DataList CommandName
EditCommand	OnEditCommand	edit
DeleteCommand	OnDeleteCommand	delete
CancelCommand	OnCancelCommand	cancel
UpdateCommand	OnUpdateCommand	update

Updating Data with the DataList

This works very similar to how you update data from a grid. The only small difference is that you will probably need to use the e.Item.FindControl() method to locate the appropriate control and extract the new values from it.

Canceling Out of Edit Mode

To cancel an edit command, just set the EditItemIndex value to -1. This is the same way canceling is done with the grid control.

See example: Pages 301 to 307.

Changing Output with the ItemCreated Event Using the DataGrid and DataList

The ItemCreated event is useful when you want to have extra control over the way a column in a grid or list is displayed. For example, we could this to convert our song lengths from raw seconds into an mm:ss display.

Keep in mind that this event will be fired for everything that is added to the grid or list, so you will need to make sure that you are only processing data when the ItemType contains the value Item or AlternatingItem. In addition, if you are accessing the underlying data using the DataBinder class and DataItem property, you should ensure it is not a null value.

Example: MusicGrid15

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Text;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

namespace MusicGrid13

{

class WebForm1 : System.Web.UI.Page

{

/// ...Same as MusicGrid14...

private void SongGrid_ItemCreated(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)

{

if( e.Item == null || e.Item.DataItem == null )

return;

if( e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem )

{

string len = DataBinder.Eval(e.Item.DataItem,"song_len").ToString();

if( len != null )

{

int time = Int32.Parse( len );

int m = time / 60;

int s = time % 60;

e.Item.Cells[6].Text = string.Format( "{0}:{1:D2}", m, s );

}

}

}

}

}

Enabling Item Selection Using the DataGrid and DataList

One other nice feature you can turn on for users is the ability to select an item and then highlight the item or row. In the DataGrid, you can modify the SelectedItemStyle property to control how the selected row is drawn, while the DataList requires a <SelectedItemTemplate> section to define this.

In both cases, just like with Edit and Delete commands, you will need to add a button or link to the grid or list and setup an event handler. The event handler should be connected to the ItemCommand event and it should set the grid's SelectedIndex property to the value from the event arguments.

Example: MusicGrid16

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Text;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

namespace MusicGrid13

{

public class WebForm1 : System.Web.UI.Page

{

// ...Same as MusicGrid15

private void SongGrid_ItemCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)

{

if( e.CommandName == "Select" )

{

SongGrid.SelectedIndex = (SongGrid.SelectedIndex == e.Item.ItemIndex) ? -1 : e.Item.ItemIndex;

LoadSongs();

}

}

}

}

Tip—Using a DropDownList in a DataGrid

You can add a drop down list to a grid or list by setting up a custom column, usually with a template.

See example: Pages 328 to 332.

Chapter 11: Editing and Filtering Data

So far we have been using the DataSet in raw mode to access the underlying records. The DataSet is quite powerful and can be used to filter, edit, delete and new records to the data source. Let's see how this all works.

The DataTable Versus DataView

A DataTable and DataView classes appear to be quite similar at first glance, but a DataView is a customized view of the DataTable it is connected to. By default, all of our examples have used the default view created automatically by .NET for the DataTable objects. However, we can create additional DataView objects and connect them to the same DataTable in order to filter and edit the records in different ways.

DataRow and DataRowView

The DataTable has a collection called the DataRowCollection which consists of all the records in the DataTable.

When you create a DataView and connect it to a DataTable, the DataView will contain a collection of DataRowView objects. The records in the DataView are connected to the records in the DataTable, so if you modify, delete or insert rows in the DataView, you will also be modifying the underlying DataTable rows, unless you take special steps to prevent this from happening.

The DataColumn Object

Both the DataRow and DataRowView classes use DataColumn objects to store information about tables columns, or schema. This includes the data type, whether it allows null values, if the column is read-only and so on.

The DataTable: Filtering, Adding, Editing, Sorting and Deleting DataRows

See example: Pages 335 to 338 (Building a DataTable from “scratch”)

Filtering Rows in a DataTable

Often you may want to restrict list of matching records in order to filter the data. The DataTable.Select() method is made for just such requirements. It comes in four different flavors:

DataTable.Select() – Returns all the records in the table ordered by the Primary Key field, if available. If not available, records are returned in whatever order they are stored in the DataRowCollection.

DataTable.Select(FilterExpression) – This allows you filter the records in some way. The FilterExpression looks something like “Artist = 'Foghat'”.

DataTable.Select(FilterExpression, Sort) – This allows us not only to filter the returned records, but to also sort them.

DataTable.Select(FilterExpression, Sort, DataViewRowState) – This version of the method allows us to retrieve only those matching records that have a specific state, such as Added, Deleted, or Modified.

DataViewRowState options:

CurrentRows—The current rows

Deleted—All deleted rows

ModifiedCurrent—The current version of modified rows.

ModifiedOriginal—The original version of modified rows.

Added—New rows.

None—None.

OriginalRows—The original rows.

Unchanged—Unchanged rows.

The above methods all return and array of DataRow objects. The result can be bound to a DataGrid if desired.

Adding New Rows to a DataTable

This is a 3 step process:

Call the DataTable.NewRow() method to create an empty record.

Set the data in each column to the desired values.

Call the DataTable.Add() or DataTable.Insert() methods to store the new row in the table.

Editing Rows in a DataTable

To edit a row in a DataTable, perform these steps:

Find the DataRow you want to edit in the DataRowCollection.

Put the row into edit mode by calling the DataRow.BeginEdit() method.

Edit the data in the columns you wish to modify.

Call the DataRow.EndEdit() method to save the changes, or call DataRow.CancelEdit() to abort the modification.

Sorting Rows in a DataTable

You can either sort when you first add the records, perhaps using the ORDER BY clause in your SQL SELECT statement, or you can sort the DataTable later using its Select() method.

Deleting Rows in a DataTable

This is pretty easy. Just locate the row you want to delete and call the Delete() method. Alternately to delete all of the records, you can call the Clear() method of the DataTable object.

The DataView: Filtering, Adding, Editing, Sorting, and Deleting DataRowViews

A DataView is just a new view connected to a DataTable. The view can be used to sort, filter or process records differently than the table it is connected to. Remember that the view's rows are still connected to the table's rows however, so changes made to view records also update the table records.

A DataView can be used for binding to controls such as the DataGrid.

To create a new DataView:

DataView myView = new DataView(DataTableObject);

Filtering Rows in a DataView

Use the RowFilter property to filter the records. It looks like this:

myView.RowFilter = “Artist = 'Foghat'”;

In addition, the RowStateFilter property can be used to filter records based on their state, much like one of the Select() methods in the DataTable allowed us to filter based on state.

Example:

myView.RowStateFilter = DataViewRowState.Added;

Adding a New Row to a DataView

This works very similar to the way we added rows to a DataTable. There are a couple of differences however.

First, the DataView.AllowNew property must be set to true to allow creating new rows.

Second, you will call the DataView.AddNew() method to create an empty row. It returns a DataRowView object instead of a DataRow object, but otherwise things work the same.

NOTE: When you call the AddNew() method, a new row is immediately added to the table. Even if you do not set any values for the columns, the new row is created and stored.

See example: Pages 358 to 360.

Editing Rows in a DataView

This is again very similar to how you edit a DataRow in a table. Before you can edit a row, make sure the DataView.AllowEdit property is set to true.

Finding DataRowViews

The DataView has a method called Find() that locates a record based on a primary key value and returns an index to the row. Before you can use the Find() method, you must set the DataView.Sort property.

See example: Pages 364 to 366.

Deleting DataRowViews

There are 2 ways to do this:

Call DataView.Delete(int rowIndex) to delete by index number.

Call DataRowView.Delete() to delete the row when you have a row reference.

See example: Pages 366 to 369.

Using the SqlCommandBuilder to Save Changes Back To Your Database

We have been using data adapter classes in many of our examples, but only for reading in records from a data source and filling in a DataSet object. The adapter classes can also be used to delete, insert or update records in the data source.

Adapter Property	Description
SelectCommand	The SQL SELECT statement used to retrieve records from the data store.
DeleteCommand	The SQL DELETE statement used to delete records in the data store.
UpdateCommand	The SQL UPDATE statement used to update records in the data store.
InsertCommand	The SQL INSERT statement used to create new records in the data store.

While you can build these commands yourself, often it is quicker and easier to use the SqlCommandBuilder class to generate the proper statements at runtime. You will still need to create the SELECT statement used by the SqlDataAdapter, but then you can build an SqlCommandBuilder object and pass the SqlDataAdapter to it. The SqlCommandBuilder will create the other command statements by analyzing the underlying data structures.

Now you can call the SqlDataAdapter.Update() method to save changes made to the local data set back to the data store. It will process every row that was added, deleted or modified and use the appropriate command to insert, delete or update the record in the data store.

See example: Pages 371 to 373.

Bringing It All Together

See complete example: Pages 375 to 388.

Chapter 13: BLOB Uploading and Displaying

Databases have the ability to store binary data called BLOBs (Binary Large Objects). These objects could also be stored as files on the hard disk.

Advantages -

All information is stored in one place. Deleting a member's picture can be done by deleting the database record instead of deleting the database record and the file that stores the image.

Updating information can be made in one central location.

Retrieving information can be done in one central location.

Accidently renaming a directory will not break access to the binary data.

Disadvantages -

It is usually slower to retrieve binary data from databases than from files.

Higher memory requirements.

All pages that contain BLOBs need to server pages and must be dynamically created.

Increased database storage requirements.

Uploading Files Using HtmlInputFile

You can drag an HtmlInputFile object directly to your web page design. It will be rendered as a text box and a browse button.

You must ensure the following rules are met:

The runat=”server” attribute is set.

A name must be assigned to the ID attribute. It is needed later so your code can access the control.

The enctype property of the control must be set to “multipart/form-data”.

Using the HttpPostedFile

Once the file has been uploaded, you need a way to access the uploaded data so you can store it in a database, move it to a new directory or rename it.

Property	Description
ContentLength	The size of the uploaded object in bytes.
ContentType	Holds the MIME type of the uploaded object.
FileName	This accesses the full path of the file in the client's machine. You can break this down to get the filename and extension.
InputStream	This creates a Stream object connected to the uploaded data. You can then read from the stream and store the uploaded information to a file or database or load it into an array.

See example: Pages 432 to 437.

Working with the In Memory File—Saving it to a Database

See example: Pages 439 to 443.

Retrieving and Showing an Image from a Database

See example: Pages 444 to 445.

Chapter 16: Data Caching

What is Caching?

Caching involves storing the results of a query in the web server's memory for a short period of time and reusing the cached data to build web pages. This should help reduce the amount of work that the database engine must do, since not every web request will result in a database lookup.

Example:

<%@ OutputCache Duration=”60” VaryByParam=”none”%>

<html>

<body>

This page was cached at:

<b>

<%=DateTime.Now.ToString(“G”)%>

</b>

</body>

</html>

As you can see, the OutputCache directive indicates that this particular page should be cached for 60 seconds and only one version of the page should be cached. If you have different input parameters then the VaryByParam attribute can be used to generate multiple versions of the same page and cache each one.

Using Page Output Cache

There are 2 ways to enable caching of pages. The first is through the OutputCache directive as shown earlier. The second requires using the HttpCachePolicy class.

Using the @OutputCache Directive to Enable Caching

Attribute	Description
Duration*	This sets the number of seconds in which the page should be held in cache memory.
Location	This controls where the page can be cached. Valid values are: Any, Client, Downstream, None or Server.
VaryByParam	This attribute allows you to create multiple versions of the cached page, one for each different query string parameter. Multiple parameters can be specified using “Param1;Param2” syntax, or an “*” will setup automatic caching when any parameter changes. These parameters must be encoded as part of the URL like this: http://server/page.aspx?Param1=1+Param2=2
VaryByCustom	Sometimes you may need to cache documents based on other things besides just parameters. By default, only one custom attribute is available, called “Browser”. This will trigger the server to build and cache a new version of the page for every different web browser that accesses the page. If desired, you can implement your own VaryByCustom attribute. To do so you must override the HttpApplication.GetVaryByCustomString() method in the Global.asax file that is part of your project.
VaryByHeader	This gives you fine control over caching based on any of the HTTP header fields: Accept Accept-Charset Accept-Encoding Accept-Language Authorization Content-Encoding Expect From Host If-Match If-Modified-Since If-None-Match If-Range If-Unmodified-Since Max-Forwards Proxy-Authorization Range Referer TE User-Agent (Browser)

Using the HttpCachePolicy Class to Enable Output Caching

The OutputCache directive gives you a quick and dirty way to turn on caching, but for complete control you will need to use the HttpCachePolicy class at runtime. It will allow to do things like set an explicit expiration time for the cached page.

See example: Page 548.

Using Fragment Caching

Not only can you enable caching for an entire web page, but you can also enable caching on a control by control basis. This is handy when parts of your page should be regenerated every time, while other parts can be cached for speed.

See example: Pages 551 to 554.

Using the Cache APIs

While the caching techniques discussed so far are useful for caching the generated output from a web page (or fragment), this does nothing to help with caching of non-HTML content, such as data sources or arrays. In order to do this, you must work with another API called the Cache API. It allows you to add or remove just about any kind of object to the cache and control expiration times over those objects.

The Insert Method

The Insert() method adds new objects to the cache. There are several flavors of this method you can call:

Cache.Insert( String, Object )

Cache.Insert( String, Object, CacheDependency)

Cache.Insert( String, Object, CacheDependency, DateTime, TimeSpan)

Cache.Insert( String, Object, CacheDependency, DateTime, TimeSpan, CacheItemPriority, CacheItemPriorityDecay, CacheItemRemovedCallback )

The parameters are as follows:

String—Names the object in the cache.

Object—The object to be added to the cache.

CacheDependency—A file or additional key of a cache item that this object depends on. If a change is made to the dependent item, then this object will be invalidated and removed from the cache.

DateTime—The date and time when this object will expire and be removed from the cache.

TimeSpan—A time period after which the object will be expired.

CacheItemPriority—A priority value assigned to this object. Lower priority objects are removed first if the server runs low on memory.

CacheItemPriorityDecay—Determines how fast the priority level is reduced the longer the object is held in the cache. A slower decay means the object will be cached longer than an object with a fast decay.

CacheItemRemovedCallback—This allows you to setup a delegate that will be called when the object is removed from the cache. The delegate may recreate the object if needed.

CacheDependency objects allow you to monitor either files and/or directories or to monitor other objects in the cache. When the files/directories or other cache objects are modified, then this object will be removed too.

CacheDependency(String)—Monitors a single file or directory for changes.

CacheDependency(String[])—Monitors an array of files or directories for changes.

CacheDependency(String[],String[])—Monitors both files/directories and other objects in the cache. To monitor only items in the cache, use null for the first parameter.

The Get Method

The Cache.Get() method locates the named object in the cache and returns a reference to it. If the object was not found, a null reference is returned. Remember, you must cast the returned object to the correct type before attempting to use it.

The GetEnumerator Method

This method allows you iterate over the entire cache and get access to all the objects currently in the cache.

The Remove Method

It is pretty obvious what this does.

Caching in Web Services

With web services, you can use the [WebMethod(CacheDuration=XX)] attribute to control how long the output data from a method should be cached.

See example on Page 571.

Example Application: MusicBrowser

Validating Input Data

Chapter 9: Evaluating Data with ASP.NET Validation Controls

Validation Controls and How They Work

Shared ValidatorAttributes

-- What Controls Can Be Validated

-- Server-Side Validation

-- Client-Side Validation

The RequiredFieldValidator Control

The RegularExpressionValidator Control

The CompareValidator Control

The RangeValidator Control

The CustomValidator Control

The ValidationSummary Control

One Control, Multiple Validators

Chapter 15: Authentication

Security Configuration

Types of Authentication

-- Windows Authentication

-- Passport Authentication

-- Forms Authentication

Using Forms Authentication

-- Creating an Authentication Ticket Cookie

-- Manually Creating a Forms Authentication Ticket

Role-based Forms Authentication

Creating XML Web Services

Chapter 12: XML and SOAP

What is XML? A Quick Refresher

XmlServer Control

XmlDocument and XslTransform

-- XmlDataDocument

Working with XML Elements

Reading and Writing XML Files with the DataSet

SOAP (Simple Object Access Protocol)

-- The Structure of SOAP

-- SOAP Abstraction in .NET

Chapter 14: Exposing Data Through Web Services

What Are Web Services?

-- Common Standards

-- Usable Protocols

Building Simple Output Web Services

-- WebMethod

Building Input/Output Web Services

-- Building the GetProducts() WebMethod

-- Building the PlaceOrder() WebMethod

-- Building the GetOrderDetails() WebMethod

Consuming Web Services

-- WSDL.exe

-- Building the Consumer Web Form

Creating Custom Controls

{TODO}

Randy Pearson

2004-06-02T11:13:28

Randy Pearson