AskthePRO
LANGUAGES: C#
ASP.NET VERSIONS: 1.0 | 1.1
Configure DataGrids to Perform Alternating Sorts
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).
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
Figure 1. To do alternating ascending and descending sorts, toggle a DataGrid column's SortExpression property.
Figure 2A. Click once to perform an
ascending sort ...
Figure 2B. ... and again to do a descending sort.
Q. How do I send e-mail from an ASP.NET app?
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:
MailMessage message = new MailMessage ();
message.From = "[email protected]";
message.To = "[email protected]";
message.Subject = "Scheduled Power Outage";
message.Body = "Our servers will be down tonight.";
SmtpMail.SmtpServer = "localhost";
SmtpMail.Send (message);
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.
The sample code in this article is available for download.
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].
