I was trying to overcome one of the shortfalls in ASP.NET 2.0's built-in controls today and stumbled on a gem: an extended GridView control that was built by Bilal Haidar.

The problem I ran into came up when I defined a fixed height for a GridView control. If you don't define a fixed height, the GridView dynamically changes its height when you page/sort, etc. This can obviously be a design problem, so I definitely like to avoid it. The thing is, if you go ahead and define a fixed height and then page to a view with only a couple of rows, the rows that do exist expand in height to fill up to the defined height. This can also be a major design problem.

Well, the search for a solution led me to XGrid.

First of all, and most importantly, this is not a completely rewritten control; it, rather, extends on the core capabilities of ASP.NET 2.0's GridView control. There are lots of custom Grid controls out there, including Telerik's AJAX-enabled r.a.d.grid and ComponentArt's Grid for ASP.net, but most of them are rewritten from scratch.

Here's a highlight [at least for me] of the added features; a complete list of the XGrid control's features is available at ASPAlliance.com:

• Single row and double row click events
• Context menu for each row
• GridView height fixed when number of rows is less than fixed height
• Built-in filter textbox

You can download the new control from the following link: http://authors.aspalliance.com/bhaidar/946.zip. To install, grab the CustomControls.dll from the bin directory, put it in your site's bin directory, and add the controls to your toolbox in Visual Studio.

Happy gridding!

A short pointer: IIS 6 will - by design - only serve .exe files if the following conditions are met:

1. The directory that is serving the executable(s) must NOT have "Scripts and Executables" permission
2. The .exe MIME type must be registered with IIS (it is registered, by default)

I'm writing this because I've been through this issue before, but forgot about the "Scripts and Executables" permission - one of those 'duh' moments. Maybe this will help someone else remember.

For organizations that have adopted Microsoft Office as their standard office suite, working with Office formats (Access, Excel, and Word) in web applications is often a necessity. Users are familiar and comfortable with Office, and with added support for Excel spreadsheets in ArcGIS Desktop 9.2 exporting to Excel from database controls (GridView, DetailsView, etc.) is a great way to make your data more accessible to GIS users.

My group provides access to several organization-wide databases through our enterprise GIS intranet web portal, and we wanted to allow users to query out data and then export the data to an Excel spreadsheet. They would then be able to bring the spreadsheet into ArcMap and link it with their existing spatial data. Looking at the larger picture, this is a great - albeit not that elegant - way to tie disparate systems together and allow data developers to error-check their data by providing crosswalks between databases.

Although exporting the contents of an ASP.NET 2.0 GridView control is simple, I ran into a couple of issues right off the bat:

• With paging enabled on my GridView control (which is a necessity for our web application), when the contents of the GridView were rendered in Excel the page links at the bottom were preserved and passed into the spreadsheet.
• With sorting enabled on my GridView control (which is, again, a necessity for our web application), the column headers were passed as hyperlinks into the Excel spreadsheet. When they were clicked on an error message displayed.

Thankfully these problems were easily solved. As you'll see in the code below, the solution simply involved turning paging and sorting off temporarily and then turning it back on after the "Export to Excel" is completed.

Note a couple of caveats:

1. This will only work for exports of 65,000 or less records. Even though you can get up to 65,000 records returned, be aware that there is a performance hit on the server. It is a good idea to build the interface so that users have to first query out records before exporting to Excel.
2. I have tested this in Internet Explorer 7.0, Mozilla Firefox 2.0, and Opera 9.0 with both Office 2003 and Office 2007. When opening the exported .xls with Excel 2007, the following warning comes up:

When you click on the "Yes" button, the spreadsheet opens fine. This is not much of an issue for our organization, as we are still using Office 2003 and likely will be for at least another year. I found a reference to this problem in the ASP.NET forums, but was unable to find a solution. If anyone runs into a solution, please let me know.

With those caveats in mind, here's the code:

ASPX - Assuming that you are have a valid SqlDataSource set up:

<asp:Button ID="btnExport" runat="server" OnClick="btnExport_Click" Text="Export to Excel" /><br />

<br />

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
   AutoGenerateColumns="False" CellPadding="4" DataSourceID="SqlDataSource1"
   ForeColor="#333333" PageSize="50" BorderColor="Black" BorderStyle="Solid" BorderWidth="1px">
     <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
     <Columns>
          <asp:BoundField DataField="DataField1" HeaderText="Data Field 1" SortExpression="DataField1" />
          <asp:BoundField DataField="DataField2" HeaderText="Data Field 2" SortExpression="DataField2" />
          <asp:BoundField DataField="DataField3" HeaderText="Data Field 3" SortExpression="DataField3" />
     </Columns>
     <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
     <EditRowStyle BackColor="#999999" />
     <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
     <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
     <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
     <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>

Code-Behind:

public partial class ExporttoExcel : System.Web.UI.Page
{
    protected void btnExport_Click(object sender, EventArgs e)
    {
        GridView1.AllowPaging = false;
        GridView1.AllowSorting = false;
        GridView1.DataBind();

        Response.Clear();
        Response.AddHeader("content-disposition", "attachment;filename=ExcelSpreadsheet.xls");
        Response.Charset = "";
        Response.ContentType = "application/vnd.xls";

        StringWriter stringWrite = new StringWriter();
        HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

        GridView1.RenderControl(htmlWrite);

        Response.Write(stringWrite.ToString());
        Response.End();

        GridView1.AllowPaging = true;
        GridView1.AllowSorting = true;
        GridView1.DataBind();
    }
    public override void VerifyRenderingInServerForm(Control control)
    {

    }
}

The code is pretty straightforward. On the btnExport_Click event, paging and sorting are turned off on GridView1. Next, the content type is set and passed to the browser as HTML and the download is initiated. After the transfer is completed, paging and sorting are turned back on. Finally, VerifyRenderingInServerForm is overridden, confirming that an HtmlForm control is rendered for the server control at run time.

Hope this helps...

There's lots of guidance available on the web for encrypting configuration sections of your ASP.NET 2 application's web.config, but here's a quick reference:

You can store your key in either a machine store or a user store. If your application runs on a dedicated server or if you want to share the sensitive information between multiple applications, use the machine store. If your application runs in a shared hosting environment or you want to make sure that no other applications are able to access the sensitive information, use a user store. Note, however, that more complexity is introduced when using a user store. For more information, see "How To: Encrypt Configuration Sections in ASP.NET 2.0 Using DPAPI", available here: http://msdn2.microsoft.com/en-us/library/ms998280.aspx.

Note that some sections cannot be encrypted using ASP.NET 2's encryption mechanism:

• <processModel>
• <runtime>
• <mscorlib>
• <startup>
• <system.runtime.remoting>
• <configProtectedData>
• <satelliteassemblies>
• <cryptographySettings>
• <cryptoNameMapping>
• <cryptoClasses>

They can, however, be encrypted using the method that was around for ASP.NET 1.0 and 1.1 (and is still around for ASP.NET 2). More information is available in Microsoft Knowledge Base article 329250, available here: http://support.microsoft.com/kb/329290.

To encrypt a connectionStrings section using the machine store:

aspnet_regiis -pe "connectionStrings" -app "/YourApplication" -prov "DataProtectionConfigurationProvider"

To encrypt a connectionStrings section using the user store:

aspnet_regiis -pe "connectionStrings" -app "/YourApplication" -prov "MyUserDataProtectionConfigurationProvider"

In these example, aspnet_regiis.exe is located at %System%\WINDOWS\Microsoft.NET\Framework\<versionnumber>. The -pe switch assumes that you're using IIS and that YourApplication is the name of a Virtual Directory on your server. If you're using Visual Studio 2005's development web server, you can use the -pef switch to specify the physical directory location of your web.config.

To decrypt a connectionStrings section using the machine store or the user store:

aspnet_regiis -pd "connectionStrings" -app "/YourApplication"

Again, the -pd switch assumes that you're using IIS and that YourApplication is the name of a Virtual Directory on your server. If you're using Visual Studio 2005's development web server, you can use the -pdf switch to specify the physical directory location of your web.config.