.NET Data Secrets, Part III

Monday Jun 2nd 2003 by Karl Moore

Learn about using a Web Grid for quick and easy data paging, fast sorting, exporting a Web grid to Excel, and using an amazing undocumented SQL Server tool for writing code.

Welcome to the third and final part of .NET Data Secrets!

I'm Karl Moore and today we'll continue last week's theme of top-notch data access through the Web DataGrid control, with even more tips and tricks to make your site look the business:

  • Quick and Easy Data Paging, with Your Web Grid
  • Sorting in Seconds, with Your Web Grid
  • Amazingly Simple Method for Exporting Your Web Grid to Excel
  • Get Microsoft to Write Your Code—Amazing Undocumented SQL Server Tool!

But, hold on, this is the final part of .NET Data Secrets? It certainly is—and the eleventh in the entire .NET secrets series we're running on this site. That means the next will be the twelfth and absolute final article in this series, our little look at the world of Web service secrets.

Don't forget: you've still got time to submit your favorite .NET snippets for publication. Simply mail your code and hot techniques to me—karl@karlmoore.com—and I'll publish the best, with full credit.

But enough small talk. Let's get on with those secrets!

Quick and Easy Data Paging, with Your Web Grid

Most Web developers tend to shiver at the mere mention of the word "paging." Even ASP.NET programmers are a little confused over its implementation. Many of the new .NET books devote a chapter or so to the subject—but it really isn't that complicated.

Paging is a method of splitting results in your DataGrid over multiple "pages." Google uses paging in its search results. Yahoo! uses paging if it can't fit all its matching sites onto one page. It's useful—and can be set up in seconds.

First off, setup your DataGrid as usual (see the "Nine Steps to a Quick, Editable Web Grid" tip in the last article if you're a little unsure). Add all the binding code to respond to the page Load event as normal. When you're ready, right-click the grid and select Property Builder. Choose the Paging property sheet and check 'Allow paging.' Alter the 'Page size' so it represents the number of items you want to display on any one 'page.' You may also want to change the navigation text just below. Click OK when finished.

Next, add code similar to the following to respond to the PageIndexChanged event of your DataGrid control:

    MyDataGrid.CurrentPageIndex = e.NewPageIndex
    ' Bind your data again here. You could load DataSet
    ' from ViewState, database, etc. For example:

And that's it—just fire off your DataGrid as normal and you've got instant paging!

Top Tip: If you're going to use editing with paging, watch out when updating your DataSet. If you're doing it purely on position, remember, after you move to a new page, your first item will not correlate with the first item in your DataSet. Remember that - you'll have to code around this. You can find out the current page by looking up the .CurrentPageIndex property, and find out the number of items displayed per page using the .PageSize property.

Sorting in Seconds, with Your Web Grid

When you're looking at a grid full of data, it's common to want to order it somehow. You may want to view employees by salary amount, from the highest paid to the lowest. Or view a customized query showing sales staff and their related telephone numbers, alphabetically.

That's all easy, with sorting. And the ASP.NET DataGrid includes built-in support for this excellent method. Here, I'm going to demonstrate the technique in its simplest form; however, you can greatly expand upon this to build a much more elegant solution.

First of all, set up your DataGrid as normal. When ready, open up the Property Builder and in the General property sheet, set the 'DataSource,' 'DataMember,' and optional 'Data key field' for your table. Moving on to the Columns property sheet, uncheck 'Create columns automatically at run time,' and then move all the fields you want to view in this DataGrid across to the list of selected columns.

Next—and this is the important bit—specify a 'Sort expression' value for each of the columns you're going to allow your users to sort by. When run, this will turn the column header into a hyperlink which, when clicked, will fire off an event. This event will receive your sort expression value as an argument, which you then can use to reorder the data appropriately. So, select a few values for the 'Sort expression:' for the purpose of this example, select the field names themselves (you'll see why it makes things easier next). Click OK when finished.

Next, we're ready to add code to handle this sorting ourselves. No, this process isn't automatic—we need to somehow reorder our data and then rebind to the DataGrid. I'm going to handle this by simply requerying my data source, though with an extra ORDER BY clause. Here's the code I'll be entering to respond to the SortCommand event of my DataGrid:

    ' Reset index, in case on a different page
    MyDataGrid.CurrentPageIndex = 0
    ' Change SELECT statement, using the passed in 'sort
    ' expression' we specified earlier
    MyDataAdapter.SelectCommand.CommandText = _
       "SELECT * FROM mytablename ORDER BY " & e.SortExpression
    ' Refresh data and rebind

Here, you can see my code initially resets the index, in case we're on another "page" of the DataGrid somewhere. It then uses our DataAdapter to alter the underlying SELECT statement, ordering by the current sort expression (the field name we specified earlier). Note that there are other, more elegant ways of handling this, rather than directly rewriting the SELECT text; however, for simplicity and demonstration purposes, this technique's a winner. Finally, we refill the DataSet, then bind it back to the DataGrid.

Almost there now. To activate all your hard work, all you need to do now is select your DataGrid and change its AllowSorting property to True.

And that's it! Your grid will now allow you to click on the header text of any of your 'sort-aware' columns and put them in correct, ascending order. With a little imagination, you also can expand on this code base to allow 'reverse sorts' too. Have a play around!

Click and sort, in just a few lines of code

Amazingly Simple Method for Exporting Your Web Grid to Excel

There are some things developers just won't tell you about—little secrets, hidden developer tricks that take weeks to find out about, but just minutes to pass on. My book is all about exposing such golden nuggets—and this next baby is a prime example.

Over the next few paragraphs, I'll demonstrate how to take a regular ASP.NET DataGrid and feed it back down to your user as though it were an actual Excel spreadsheet—and you don't even need Excel on your server. Best of all, it only takes just a few extra lines of code.

Firstly, work on getting information to your DataGrid as normal; however, ensure you don't incorporate any special features such as Select buttons or paging. You can, however, apply as much color or text formatting as you wish—right-click, select 'Auto Format,' and go wild.

Next, add code similar to the following to respond to the page Load event:

    ' Put information into your DataGrid, for example:
    MyDataGrid.DataSource = MyDataSet.Tables(0)

    ' Tell browser this is 'Excel' information
    ' and prepare to send down info
    Response.ContentType = "application/vnd.ms-excel"
    Response.Charset = ""
    Me.EnableViewState = False

    ' Get the DataGrid HTML from the control,
    ' then write straight to the browser
    Dim objSW As New System.IO.StringWriter()
    Dim objHTW As New System.Web.UI.HtmlTextWriter(objSW)

Here, our code fills the DataGrid, and then tells the browser it'll be sending down Excel information. The HTML for the DataGrid is then retrieved and pushed down. Excel then translates this into its own cells—and your user simply sees an Excel spreadsheet containing your data embedded in their browser.

Top Tip: A word of warning, folks. A bug in the release version of Office 2000 means that accessing an .ASP or .ASPX page (i.e., "MyForm.aspx") with an embedded Excel resource like this results in a blank page (see Knowledge Base article KB266263). The workaround is that when linking to your Excel page, add a placebo query string or just a simple question mark (i.e., "MyForm.aspx?") at the end of the filename. Alternatively, read on for another solution.

What if you want to create an Excel page that will actually open in Excel—or prompt the user to save it as an actual file? Easy; just add the following line to your code above, around where you set the .ContentType property:

Response.AddHeader("content-disposition", _

This prompts the user to either open, or save with a default name of 'myfile.xls'. It also has the benefit of not suffering from the bug mentioned in the above tip.

My Web grid, embedded as an Excel sheet within Internet Explorer

Get Microsoft to Write Your Code—Amazing Undocumented SQL Server Tool!

Truth be known, a lot of development work is pretty repetitive stuff. You write a routine once, and then need to write a similar routine for another table. So you copy-and-paste. Ten times. Then you find an error in the original routine and begin editing. Again.

Don't know about you, but I hold my hands up and confess that I've fallen foul of this madness.

But there's one little solution that only a tiny bunch of French developers know about. It's a tool called SQL Server Centric .NET Code Generator, created by a Senior Developer Evangelist at Microsoft France, Pascal Belaud. And the program is a cure for the common code.

Bundled with 150 enterprise templates, it generates stored procedures, three-tier data access classes (including a basic user interface), even documentation—in seconds.

And, with continuous debugging from Microsoft themselves, you can be assured you're working with the neatest code and adopting the latest practices around. And, to keep you up-to-date, Belaud and his team are making constant improvements and post a fresh release every sixty days or so.

How come you've never heard about it? Because it's officially an "unsupported" software tool from Microsoft France. The truth of the matter, however, is that it's the main tool used by Microsoft Consulting Services, the division that implements Microsoft solutions for customers with more than 1,000 base users. Supposedly "normal" developers don't usually get to hear about such power tools.

How can you get your hands on a copy? Surf down to www.microsoft.fr/olymars/webupdate.xml and begin playing with your own copy (see the following figure) in minutes. And yes, to cap it all, it's absolutely free!

A word of warning: whilst amazingly detailed and highly powerful, this program is not always the most user-friendly of developer tools. I'd definitely recommend you download the English tutorial and spend time becoming acquainted with how everything fits together before generating any real-life code.

It'll pay off, no doubt.

My copy of SQL Server Centric .NET Code Generator in action

Next Time

Coming up next time, in Web Service Secrets:

  • Five Things You Need to Do Before Publicizing Your Web Service
  • Improving Performance with Easy Caching
  • Adding Google Search to Your Programs
  • View the Real World in Your Application, with TerraServer

Want more? Then check out my new book, VB.NET and ASP.NET Secrets, which hosts full versions of all these, plus many, many more!

See you then!

About the Author

Karl Moore is a technology author living in Yorkshire, England. He runs his own consultancy group, White Cliff Computing Ltd, and is author of two best-selling books exposing the secrets behind Visual Basic .NET. When he's not writing for magazines, speaking at conferences, or making embarrassing mistakes on live radio, Karl enjoys a complete lack of a social life. Check out Karl's newest book, VB.NET and ASP.NET Secrets.

# # #

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved