Sorting DataGrids

Also, Sending E-mail from an ASP.NET App, Storing Images, & Converting a DataReader to a DataSet





Sorting DataGrids

Also, Sending E-mail from an ASP.NET App, Storing Images, & Converting a DataReader to 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. The page shown in Figure 1 demonstrates how. 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 performs 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" %>





        AutoGenerateColumns="false" CellPadding="2"

        Width="100%" Font-Name="Verdana" Font-Size="8pt"

        AllowSorting="true" OnSortCommand="OnSort">



            DataField="title_id" />



            SortExpression="title ASC" />


            DataField="price" SortExpression="price ASC"


            ItemStyle-HorizontalAlign="right" />



          Font-Bold="true" HorizontalAlign="center" />






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 for 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";



Because of 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 ( 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, is it better to store 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 and the bits 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 database and build an HTTP handler that 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 end of article for download details).


// 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(


  MemoryStream stream1 = new MemoryStream();

  MemoryStream stream2 = new MemoryStream();

  Bitmap bitmap = null;

  Image image = null;




    // Fetch the image bits from the database.


    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,






    if (image != null)


    if (bitmap != null)







// Stream the image back to the client.

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

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

Figure 4: This code excerpt shows 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. The 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's Steve Smith. His code is clean, concise, and hard to improve upon, so why reinvent the wheel? Check out Steve's code at, 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 (, a software consulting and education firm that specializes in .NET. Have a question for this column? Submit queries to [email protected].




Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.