[NAntC-Dev] SqlTask Suggestion

[Jake Foster]

Hey All-

 

Out of neccessity I have tweaked the SQL task type in NAntContrib to provide a mechanism to return (in a very limited way) results from a query executed in a SQL task to the NAnt script executing it.  For obvious reasons I couldn't really return an entire resultset, but for my purposes being able to return a single value was enough (I'm basically just interested in the results of a SELECT COUNT(*) ... query.)  What I did is the following:

 

1. I created a private string member and corresponding public property called 'property' and marked it as a task attribute.

 

private string _property;

...

 

/// <summary>

/// If specified, this is the NAnt property that will be populated with

/// the results returned in the first column, of the first row in the

/// first resultset generated by the SQL. This is similar to returning

/// a scalar value from a query.

/// </summary>

[TaskAttribute("property")]

[StringValidator(AllowEmpty=false)]

public string @Property

{

    get { return _property; }

    set { _property = value; }

}

 

2. I added a bit of code to ProcessResults() that grabs the very first value from the results as a string and sticks it into the PropertiesDictionary using the supplied property name.

 

...

bool isFirstResult = true;

...

if (_property != string.Empty && isFirstResult) {

    if (Project.Properties.Contains(_property)) {

        Project.Properties[_property] = result;

    }

    else {

    Project.Properties.Add( _property, result );

    }

}

I'm curious to know what everyone thinks of this approach (sort of like .ExecuteScalar() ) or if there are any ideas about a better way to enable returning results into the NAnt script that is executing the SqlTask.  CVS Patch below (sorry if this isn't the normal approach but I'm not sure what the protocol is here so I figured I'd just toss it in the body of this e-mail.)

Thanks - Jake

Index: src/Tasks/SqlTask.cs
===================================================================
RCS file: /cvsroot/nantcontrib/NAntContrib/src/Tasks/SqlTask.cs,v
retrieving revision 1.12
diff -u -r1.12 SqlTask.cs
--- src/Tasks/SqlTask.cs 16 Oct 2003 08:00:27 -0000 1.12
+++ src/Tasks/SqlTask.cs 12 Apr 2004 17:35:42 -0000
@@ -108,6 +108,7 @@
         private DelimiterStyle _delimiterStyle = DelimiterStyle.Normal;
         private bool _print;
         private bool _useTransaction = true;
+  private string _property;
         private string _output;
         public string _embeddedSqlStatements;
         private bool _batch = true;
@@ -230,6 +231,20 @@
             set { _useTransaction = value; }
         }
 
+  /// <summary>
+  /// If specified, this is the NAnt property that will be populated with
+  /// the results returned in the first column, of the first row in the
+  /// first resultset generated by the SQL.  This is similar to returning
+  /// a scalar value from a query.
+  /// </summary>
+  [TaskAttribute("property")]
+  [StringValidator(AllowEmpty=false)]
+  public string @Property
+  {
+   get { return _property; }
+   set { _property = value; }
+  }
+
         #endregion Public Instance Properties
 
         #region Protected Instance Properties
@@ -390,7 +405,8 @@
         /// <param name="writer"><see cref="TextWriter" /> to write output to.</param>
         private void ProcessResults(IDataReader results, TextWriter writer) {
             try {
-                do {
+    bool isFirstResult = true;
+    do {
                     // output header
                     DataTable schema = results.GetSchemaTable();
                     if (schema != null) {
@@ -413,7 +429,17 @@
                     // output results
                     while (results.Read()) {
                         for (int i = 0; i < results.FieldCount; i++ ) {
-                            writer.Write(results[i].ToString() + new string(' ', 2));
+       string result = results[i].ToString();
+                            writer.Write(result + new string(' ', 2));
+       if (_property != string.Empty && isFirstResult)
+       {
+        if (Project.Properties.Contains(_property)) {
+         Project.Properties[_property] = result;
+        }
+        else {
+         Project.Properties.Add( _property, result );
+        }
+       }
                         }
                         writer.WriteLine();
                     }