Also, send e-mail from an ASP.NET app, know where to
store your images, and convert a DataReader into a DataSet.

By Jeff Prosise

Q. How do I configure a DataGrid to do
alternating ascending and descending sorts?

A. You do alternating sorts by
programmatically modifying the sort expressions assigned to sortable columns.
This page demonstrates how (see Figure 1). It initializes sortable columns with
the sort expressions "title ASC" and "price ASC" to do ascending sorts. The
OnSort method that's called when the user clicks a sortable column header does
the sort and then toggles "ASC" to "DESC" or vice versa, in the corresponding
column's SortExpression property. The result is alternating sorts (see Figure
2).

A. It's easy - thanks to a pair of
classes named MailMessage and SmtpMail in the .NET Framework Class Library's
System.Web.Mail namespace. Here's a code snippet that sends an e-mail message
using the local SMTP service:

Due to a security change made to
ASP.NET just before it shipped, you need to set SmtpMail's SmtpServer property
to "localhost" even though "localhost" is the default. In addition, you must
configure IIS to allow localhost (127.0.0.1) to relay messages through the
local SMTP service. An alternative is to configure ASP.NET to run as SYSTEM,
which alleviates this security issue but raises larger security issues of its
own.

Q.
If an ASP.NET app stores and displays thousands of images, which is better:
storing the images in a database or in the local file system?

A. The answer is an unequivocal "it
depends." Both storage methods have pros and cons.

Images stored in the local file
system can be displayed with simple tags, and file-system retrieval
tends to be faster than database retrieval - especially if the database server
is remote, causing the bits to have to travel over the wire to the Web server
before making their way back to the client. Plus, storing images on a database
server increases the load on that server, which could impede the performance of
data-driven apps.

On the other hand, storing images
in a database makes the data self-contained. Backing up the database backs up
the images, too. Fetching the images from the database tends to require a
little more coding effort (typically the tags reference an HTTP
handler that does the image retrieval), but the coding isn't difficult.

The good news is that it doesn't
really matter where the images are stored if you cache them. When I design apps
that rely on stored images, I tend to put the images in a back-end database and
build an HTTP handler that both retrieves them and caches them. The app
requests an image through the HTTP handler. The handler, in turn, grabs the
image from the database if the image hasn't been retrieved before, or from the
ASP.NET application cache if it has. This improves performance by eliminating
unnecessary round trips to the database server. It also decreases the load on
the database and centralizes the application's data.

Because a sample's worth a thousand
words, I've included a page named NorthwindGrid.aspx that uses a DataGrid to
display employee photos obtained from SQL Server's Northwind database (see
Figure 3). The grid's leftmost column contains tags referencing an
HTTP handler named CachingNorthwindImageGrabber.ashx. Here's an example of one
of those tags:

The HTTP handler retrieves employee
photos based on employee IDs passed in query strings. The first time a given
photo is requested, the handler executes a database query and inserts the
returned photo into the application cache. If the same photo is requested
again, it's delivered straight from the cache. Amortized over thousands of
requests, the cost of the database accesses is virtually nil because no photo
is read from the database more than once. The only real cost is increased RAM
utilization on the Web server.

Figure 3. The images on the
left-hand side of the page are retrieved by an HTTP handler that has caching
logic built in.

Consider an excerpt from
CachingNorthwindImageGrabber.ashx - specifically, the code that checks the
cache for an image and goes to the database if the image is not there (see
Figure 4). This implementation permits images to remain in the cache for up to
30 minutes. You can increase or decrease that amount by changing the fourth
parameter to Cache.Insert. And, of course, you can go to asp.netPRO's
Web site to download the complete ASHX file as well as the accompanying ASPX
file (see the Download box accompanying this article).

// Attempt to read the image from the
cache

string key =
"NorthwindEmployeePhoto" + id;

byte[] bits = (byte[]) context.Cache[key];

// If the image isn't cached, retrieve it
from the

// database and cache it

if (bits == null) {

SqlConnection connection = new SqlConnection

("server=localhost;database=northwind;uid=sa");

MemoryStream stream1 = new MemoryStream ();

MemoryStream stream2 = new MemoryStream ();

Bitmap bitmap = null;

Image image = null;

try {

// Fetch the image bits from the database

connection.Open ();

SqlCommand cmd = new SqlCommand

("select photo from employees where
employeeid='" +

id + "'", connection);

byte[] blob = (byte[]) cmd.ExecuteScalar ();

// Convert the raw bits into an image

stream1.Write (blob, 78, blob.Length - 78);

bitmap = new Bitmap (stream1);

// Shrink the image, but maintain its aspect ratio

int width = 48;

int height =

(int) (width * ((double) bitmap.Height /

(double) bitmap.Width));

image = bitmap.GetThumbnailImage (width, height,

null, IntPtr.Zero);

// Write the image bits into a byte array

image.Save (stream2, ImageFormat.Jpeg);

bits = stream2.ToArray ();

// Insert the byte array into the cache

context.Cache.Insert (key, bits, null,

DateTime.Now.AddMinutes (30),

Cache.NoSlidingExpiration);

}

finally {

if (image != null)

image.Dispose ();

if (bitmap != null)

bitmap.Dispose ();

connection.Close ();

stream1.Close ();

stream2.Close ();

}

}

// Stream the image back to the client

context.Response.ContentType =
"image/jpeg";

context.Response.OutputStream.Write (bits,
0, bits.Length);

Figure
4. This except is the logic used by CachingNorthwindImageGrabber.ashx to
fetch an employee photo from the Northwind database or from the cache.

Q.
How do I convert a DataReader into a DataSet? I'm working with a data layer
that returns a SqlDataReader, but I want to bind the DataReader to a pageable
DataGrid. Trouble is, pageable DataGrids can't bind to SqlDataReaders unless
custom paging is enabled, too. Converting the SqlDataReader into a DataSet
would solve my problem nicely.

A. I started to write up a routine
for converting a DataReader into a DataSet, but then I found one already
written by ASPAlliance.com's Steve Smith. His code is clean, concise, and hard
to improve upon, so why reinvent the wheel? Check out Steve's code at http://authors.aspalliance.com/stevesmith/articles/convertReadertoSet.asp,
and add a handy new arrow to your quiver for turning those pesky DataReaders
into DataSets.

Jeff
Prosise is the author of several books, including Programming Microsoft .NET (Microsoft
Press, 2002). He's also a cofounder of Wintellect (http://www.wintellect.com), a software
consulting and education firm that specializes in .NET. Have a question for
this column? Submit queries to [email protected].