Aaquagua!: Using NAnt to Traverse Directories and Execute SQL

In the latest version of Ensemble Video I had a need to migrate the old mechanism of showing content to new way that involved a single page and some URL rewriting (more on that another time). The basics of the old mechanism were that for each publishing point used by the application, a separate directory with specific “index.aspx” was created. That was always a major pain when updates had to be applied since I always had to preserve the old files but still update existing content. Long story short, I got away from that but I still needed to figure out what publishing points were in use so I can set the database flag for the new mechanism. So, the NAnt fanatic that I am, I build a NAnt script to do that. In this example, you will learn:

How to parse XML for a certain value
How to traverse directories with NAnt
How to execute an “if” statement in NAnt
How to get file and directory names using the NAnt built-in functions
How to execute SQL script with SQL task from NAntContrib

The prerequisites are that 1). You have NAnt installed and 2). You have NAntContrib installed.

So let’s go one by one from the list above and you will see the final script at the end.

How to parse XML for certain value
```
<xmlpeek
file="${path::combine(path::get-full-path(webApplicationDirectory), 'config\connectionStrings.config')}"
xpath="/connectionStrings/add[@name = 'ensembleVideoConnection']/@connectionString"
property="connectionString" />
```
This parses the XML file under webApplicationDirectory\config\connectionStrings.config and uses an XPath expression to get the property of the connectionString with the name ensembleVideoConnection. The value is stored in the NAnt property "connectionString"
How to traverse directories with NAnt
```
<foreach item="Folder" in="${sitesDirectory}" property="orgDirectoryName">
	<echo message="${orgDirectoryName}" />
</foreach>
```
This task traverses through the directories inside the directory specified by "sitesDirectory", stores the current directory in orgDirectoryName and then echoes the current directory.

How to execute an “if” statement in NAnt

<if test="${string::to-lower(path::get-file-name(orgDirectoryName)) != '_svn'}">
	<echo message="${path::get-file-name(orgDirectoryName)}" />
</if>

Here we test if the current directory name is equal to "_svn". Here is another example where we test if the current directory contains a desired file:

<if test="${file::exists(path::combine(webSiteDirectoryName, 'index.aspx'))}">
	<property name="enableQuickPublish" value="1" />
</if>

How to get file and directory names using the NAnt built-in functions

<-- Get only the directory name -->
path::get-file-name(orgDirectoryName)
<-- Get the directory name from the path -->
path::get-directory-name(orgDirectoryName)
<-- Create a full path based on the directory name and a file name -->
path::combine(webSiteDirectoryName, 'index.aspx')

You can find out more about NAnt built-in functions at NAnt Functions

How to execute SQL script with SQL task from NAntContrib

<sql
  connstring="Provider=SQLOLEDB;${connectionString}"
  transaction="true"
  delimiter=";"
  delimstyle="Normal">
	update	dbo.webSites
	set	isQuickPublished = ${enableQuickPublish}
	where	webSiteID = (
			select	webSiteID
			from	dbo.listWebSites
			where	webSafeOrganizationName = '${path::get-file-name(orgDirectoryName)}'
				and webSafeDepartmentName = '${path::get-file-name(departmentDirectoryName)}'
				and webSafeWebSiteName = '${path::get-file-name(webSiteDirectoryName)}'
		);
</sql>

There is not much to the SQL statement execution. As you can see you can use NAnt properties and function inside the SQL script. You can check out all of NAntContrib's tasks at NAntContrib Tasks

As promised here is the full contents of the build script:

<project name="renameSqlToText" default="run" xmlns="http://nant.sf.net/release/0.86-beta1/nant.xsd">
	<!-- Set the path to the web application -->
	<property name="webApplicationDirectory" value="D:\_Boyan's Documents\_Projects\_ensembleVideo\trunk\webApplication\app" />

	<!-- Set the name of the sites directory (under the applicaiton directory) -->
	<property name="sitesDirectoryName" value="sites" />

	<property name="sitesDirectory" value="${path::combine(path::get-full-path(webApplicationDirectory), sitesDirectoryName)}" overwrite="true" />

	<target name="run">
		<!-- Get the database connection string from the web application -->
		<xmlpeek
			file="${path::combine(path::get-full-path(webApplicationDirectory), 'config\connectionStrings.config')}"
			xpath="/connectionStrings/add[@name = 'ensembleVideoConnection']/@connectionString"
			property="connectionString" />

		<!-- For each directory in the sitesDirectory -->
		<foreach item="Folder" in="${sitesDirectory}" property="orgDirectoryName">
			<if test="${string::to-lower(path::get-file-name(orgDirectoryName)) != '_svn'}">
				<echo message="${path::get-file-name(orgDirectoryName)}" />

				<foreach item="Folder" in="${orgDirectoryName}" property="departmentDirectoryName">
					<echo message="		${path::get-file-name(departmentDirectoryName)}" />

					<foreach item="Folder" in="${departmentDirectoryName}" property="webSiteDirectoryName">
						<echo message="				${path::get-file-name(webSiteDirectoryName)}" />

						<!-- Set the 'enableQuickPublish' property to false -->
						<property name="enableQuickPublish" value="0" />

						<!-- If a index.aspx file exists, set the 'enableQuickPublish' property to true -->
						<if test="${file::exists(path::combine(webSiteDirectoryName, 'index.aspx'))}">
							<property name="enableQuickPublish" value="1" />
						</if>

						<!-- Run a database query to update the flag in the database -->
					      	<sql
						        connstring="Provider=SQLOLEDB;${connectionString}"
						        transaction="true"
						        delimiter=";"
						        delimstyle="Normal">
							update	dbo.webSites
							set	isQuickPublished = ${enableQuickPublish}
							where	webSiteID = (
									select	webSiteID
									from		dbo.listWebSites
									where		webSafeOrganizationName = '${path::get-file-name(orgDirectoryName)}'
											and
											webSafeDepartmentName = '${path::get-file-name(departmentDirectoryName)}'
											and
											webSafeWebSiteName = '${path::get-file-name(webSiteDirectoryName)}'
									);
					      </sql>
					</foreach>
				</foreach>
			</if>
		</foreach>
	</target>
</project>