Forum open now

I set up a forum for all of my software that I describe in this blog:

• dbscript
• graspx
• automssqlbackup
• SMOscript

Please feel free to register and post.

Finding SELECT Commands in .aspx Files

During tests of my most recent versions of dbscript I ran into problems with a SELECT statement that works fine on SQL Server 2000, but raises an error on SQL Server 2005.

The statement is in the form of

SELECT NULL AS OID, NULL AS ID
UNION ALL
SELECT MyTable.OID, MyTable.ID
FROM MyTable
ORDER BY MyTable.ID

The statement retrieves all records of MyTable, and adds an empty record at the beginning. The problem seems to be that while SQL2000 only considers the column name of the ORDER BY clause, SQL2005 cannot find the column MyTable.ID in the result set, as it is only defined as ID in the first SELECT.

After fixing the statement, the obvious question was where else in the source code such statements occurred.

A single line on the cmd prompt invoking my graspx tool showed the occurrences:

graspx l SelectCommand *.aspx | find "UNION" | find "ORDER"

One Year devioblog – a Summary

I started this blog one year ago to write about topics that I deal with in my software projects, mostly about MS SQL Server and Asp.Net programming.

Since September 2007, this activity generated 71 posts (I did not realize I was publishing an article about every 5 days!) and 20.000 views (says my stats page).

In this time, I also released 3 freeware programs to the public: SchemaFind, graspx, and SMOscript (downloads here).

From the list of Top Posts, my personal favorites are those about automatically building Visual Studio solutions and automated project releases here, here, here, and here.

Sometimes I also documented software installation procedures if I thought I had run into unusual problems: TRAC, Bugzilla, or GForge.

And occasionally I was simply enjoying working with Visual Studio (2005), SQL Server (2005), and C#.

To be continued…

Updating graspx (version 0.15)

I fixed a bug generating the correct identifier of asp:ListItems when using the -nc (naming container) option.

The latest version of graspx if available for download here.

Extended Functionality in graspx

To fine-tune the steps to automatically build a Visual Studio solution, I needed to implement some long-planned features: One of the targets in writing graspx was to extract all displayed text from the various controls on each ASP.Net form.

UI texts can be stored in a wide array of different controls and their attributes: label Text, hyperlink innerText, page Title, gridview EmptyDataText, validators ErrorMessage, and so on.

To make things more complicated, a control may not be found differently by its ID, but rather one needs to walk the form’s control hierarchy, if a control is placed within a FormView or GridView. Some texts may even be stored in a tag without an identifier, as is the case with ListItems in a DropDownList, or Columns within a GridView.

The new functionality in graspx covers these cases:

The LL command lists the value of all tag/attribute combinations listed in a separate parameter file, and thus equals the sequential execution of single L commands (uppercase “L” is used here for clarity).

The option -nc allows to define naming containers. If a control is found, the ID of the control is composed of the IDs of the parent controls. As an example from the setup files:

asp:GridView    .id
Columns         ix
asp:FormView    .id
ItemTemplate    tag
asp:Content     .ContentPlaceHolderID

For a GridView, the value of the ID attribute is used as naming ID, whereas for the Content (masterpage mechanism), it is ContentPlaceHolderID. Columns within a GridView are addressed by their index. A FormView may hold 3 different templates which are distinguished by their tag.

The working directory, which was the current directory in previous versions, can be set using the -d option; -r allows recursive searches through subdirectories.

The -nodyn option excludes all dynamic expressions ( < % # … % > ) from the search result.

The -utf8 option forces output in UTF8 encoding, which is not the default even for .Net console applications.

graspx is available for download here.

xmlns + SelectNodes = empty XmlNodeList

Trying to parse web.config files using SelectNodes, I found that I have two kinds of web.config files on my development PC, one with an xmlns declaration and one without.

<configuration>
<configuration
    xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">

This should not really bother me, but it turns out that the SelectNodes method does not work as expected when an xmlns declaration is present. As this comment points out, a namespace manager definition is needed, and the namespace needs to be added to the manager. That’s because, the .Net runtime cannot do this for you.

The namespace of the XML document can be retrieved from DocumentElement.NamespaceURI.

As a ridiculous consequence, every XPath query has to include the namespace in every tag!

So, my previously single-line solution to iterate all user control namespaces in the web.config gained some weight, but fortunately it works:

XmlDocument docWC = new XmlDocument();
docWC.Load("web.config");

XmlNamespaceManager mgr =
    new XmlNamespaceManager(docWC.NameTable);
XmlNodeList xnl = null;
if (string.IsNullOrEmpty(docWC.DocumentElement.NamespaceURI))
{
    xnl = docWC.SelectNodes(
        "/configuration/system.web/pages/controls/add", mgr);
}
else
{
    mgr.AddNamespace("gr", docWC.DocumentElement.NamespaceURI);
    xnl = docWC.SelectNodes(
        "/gr:configuration/gr:system.web/gr:pages/gr:controls/"
        +"gr:add", mgr);
}

Building Visual Studio Solutions from the Command Line

Sometimes there are solutions that are technologically so simple that you don’t even think about them.

I develop a couple of projects, some of which are rather complex to build, as they require to generate code from database content.

Build tools can be complex and have a steep learning curve, Powershell might be an overkill since it’s useful if you handle .Net objects and containers, and I found it amazing what you can achieve with a little batch file.

This is a sketch of a project-specific build script:

First, declare log files and required tools. I found tee.pl to both display output on screen and write it to a log file.

set log=C:\path to build\build.log
set tee=perl C:\path to script\tee.pl
set osql=osql -S host -U user -P password -d database -n -w 1000

echo. > %log%

Next, check ASP.Net code for XHTML compliance and other criteria using graspx:

pushd \inetpub\wwwroot\path to web
(call ..\check2) | %tee% %log%
popd

One of my projects uses a metamodel to generate triggers, so generate a T-SQL script to drop and create triggers, and run that script using osql (SQL 2000) or sqlcmd (SQL 2005):

echo generating database stuff from metamodel | %tee% %log%
%osql% -Q "exec Generate_SQL_Code" -o sqlcode.sql
echo run generated code | %tee% %log%
%osql% -i sqlcode.sql | %tee% %log%

Next, generate C# const declarations from database content. (I find it’s a good idea to keep generated in a separate directory):

echo generating Consts.cs | %tee% %log%
%osql% -Q "exec dev_Generate_Consts" -o "C:\path to project\Generated\Consts.cs

The C# source code now matches the constants defined in the database. So we can now initialize the VS environment and build the project:

setlocal
call "C:\path to\Microsoft Visual Studio 8\VC\vcvarsall.bat" x86
pushd c:\temp
msbuild "C:\path to solution\project.sln"
popd
endlocal

If we build a web project, delete the PrecompileWeb directory that msbuild created:

rmdir /s /q "C:\path to\Visual Studio 2005\Projects\project\PrecompiledWeb"

Finally, update the online help wiki to include new aspx pages:

call createwiki.cmd

What have we got now?

• checked the source code
• generated database objects
• generated C# const definitions
• built VS projects

Next steps are backing up the development database and publishing the application.

Checking NavigateUrls in ASP.Net

In previous posts, I wrote how you can analyze your ASP.Net application using graspx.

Now, I want to check whether the links in my aspx files point to existing files, i.e. the values of NavigateUrl and DataNavigateUrlFormatString must refer to existing filenames. This is achieved with a little shell magic:

@for /f "delims=?: tokens=1 usebackq" %f in
  (`graspx -count -col 5 l DataNavigateUrlFormatString *.aspx`) do
    @if not exist %f (echo %f does not exist)

The graspx command lists all XHTML elements with a DataNavigateUrlFormatString attribute. The for commands iterates through this list (rather, through the list of attribute values), and outputs a message if the referenced file does not exist. The “delims=?” option splits the URL arguments from the URL filename.

Use graspx with the f (find) parameter to find out which files are referencing the missing file.

The same operation can be used to identify wrong URLs or missing files for NavigateUrl:

@for /f "delims=?: tokens=1 usebackq" %f in
  (`graspx -count -col 5 l NavigateUrl *.aspx`) do
    @if not exist %f (echo %f does not exist)

Note that the NavigateUrl value may include a bind expression (Eval()), which is not handled separately in this example.

graspx is available for download here.

Analyze User Control usage and Hyperlinks

This series describes how to analyze ASP.Net source code.

Which page links where

To find hard-coded links in your ASP.Net pages, retrieve the NavigateUrl and DataNavigateUrlFormat properties, like this:

graspx -col 1,5 l NavigateUrl
graspx -col 1,5 l DataNavigateUrlFormatString

Which User Controls are used in my application

To list which page registered which user control, run:

graspx -col 1,5 l Src

If you omit the page name in the output, you can count how many pages reference a given User Control:

graspx -col 5 -count l Src

Which assemblies do your pages rely on?

graspx -col 5 l Assembly

Which Session Fields does your application reference?

graspx -col 5 -count l SessionField

graspx is available for download here.

Analyze ASP.Net Source Code with graspx

This series describes how to analyze ASP.Net source code. Follow the graspx category for more articles.

QueryString Parameters

To find out which query string parameters your pages are expecting, run:

graspx -col 1,5 l QueryStringField

Database Statements

To retrieve all SQL statements your application is issuing, use the following commands:

graspx -col 1,5 l SelectCommand
graspx -col 1,5 l InsertCommand
graspx -col 1,5 l UpdateCommand
graspx -col 1,5 l DeleteCommand

This also lists the aspx file name. If you want the SQL statements only, set the -col parameter to 5.

The output of these commands can be further analyzed to check whether the referenced tables and stored procedures are still in use.

graspx is available for download here.