T-SQL ‘GO’ and SqlCommand

Try to execute a T-SQL script which contains several SQL batches separated by the GO keyword using .Net’s SqlCommand, and you will receive an error message

Incorrect syntax near ‘GO’.

What’s going on here? Why does it work from SSMS or sqlcmd, but not in SqlCommand?

The reason for this behavior is that ‘GO’ is not a statement of T-SQL, but rather

a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor

(MSDN). You think you might simply remove all occurrences of GO inside your statement, but that will not always work: Some statements require to be the first statement in a batch

‘CREATE/ALTER PROCEDURE’ must be the first statement in a query batch.

So how do you execute a set of SQL statements containing ‘GO’ in C#?

The “lazy” way is to simply split the SQL string at each occurrence of ‘GO’ using string.Split(), but this is really lazy.

Why? Because of the complexity of the language that the T-SQL shells are capable of processing:

• string literals ” and N”
• single line comments –
• multi-line comments /* */
• nested multi-line comments (!)

The characters ‘GO’ must not be recognized if they occur inside any of these constructs, and they need not be the first characters in a line – the line may also contain spaces or whitespace or comments (just give it a try in SSMS, it’s truly amazing).

The more practical way (mentioned here) to execute an arbitrary SQL string is to use SMO’s Server.ConnectionContext.ExecuteNonQuery().

Or, you write a parser using ANTLR, define the correct grammar to split at the relevant GO keywords, and execute the list of resulting SQL batches separately.

NHibernate OneToMany surprises

Up to now, I did not care about NHibernate’s Sets and Bags, as my queries would target the details tables directly, and filter to the parent’s PK.

Now I tried to extend my Classes and ClassMaps generation code for Mapping.By.Code to Bags, and started out with something like this:

public partial class Foo
{
  public virtual int Id { get; protected set; }
  public virtual ICollection<Bar> Bars { get; set; }
}
public partial class Bar
{
  public virtual long Id { get; protected set; }
  public virtual Foo Foo { get; set; }
}

The mapping for Bar is not affected by the new Bars property:

public partial class Bar_Map: ClassMapping<Bar>
{
  public Bar_Map()
  {
    Table("Bar");
    Id(x => x.Id, map => ...);
    ManyToOne(x => x.Foo, map => ...);
  }
}

The master table gets a new collection property, depending on which NH mapping you use (SO):

Bag(x => x.Bars, bag =>
  {
    bag.Table("Bar");
    bag.Inverse(true);
    bag.Lazy(CollectionLazy.Lazy);
    bag.Cascade(Cascade.DeleteOrphans);
    bag.Key(k => {
      k.Column(col => col.Name("FooId"));  
          // this is the SQL column name
    });
  }, map => map.OneToMany());

So I try this code, retrieve a Foo, and count its Bars:

var foo = session.Get<Foo>(fooId);
Console.WriteLine(foo.Id);
Console.WriteLine(foo.Bars.Count());

Surprisingly, NH selects ALL Bar records into the collection, and counts the elements in the collection. That’s not what I expected.

On the NHibernate Pitfalls blog I found the hint to change the Lazy() setting to Lazy(CollectionLazy.Extra). And indeed, only a SELECT COUNT(*) was executed.

Somehow I was expecting the collection properties to be an alias for SELECT WHERE statements, so I tried things like foo.Bars.FirstOrDefault(), foo.Bars[0] (for IList) or foo.Bars.Take(1), but each of them always first populated the Bars collection in .Net, and only then retrieved the requested object from the collection, rather than issuing a separate SELECT.

For a complete list of documented surprises, see the NHibernate Pitfalls Index.

Cancelling a Sleeping Task

Preparations for an executable (application or service) that will implement different Tasks. Some of the tasks will work continuously, while other will only perform their operations periodically with long intervals (hours) betwen the operations.

Of course, simple techniques such as Busy waiting must be avoided, and Sleep looping (why isn’t that a Wikipedia entry?) is not what I am looking for. And calling Thread.Sleep() with a long timeout value cannot be cancelled while the thread is sleeping.

The alternative I found was to use WaitHandle.Wait(), which waits for an event and also accepts a timeout as the maximum time to wait for the event.

My first approach was to create a ManualResetEvent that the task is Wait()ing for, and trigger that event when the cancellation is triggered:

static void SleepTaskMethod(int s, CancellationToken t)
{
  var mre = new ManualResetEvent(false);

  // cancellation fires MRE
  t.Register(() =>
  {
    Console.WriteLine("Cancellation " + s);
    mre.Set();
  }, true);

  while (!t.IsCancellationRequested)
  {
    Console.WriteLine("loop/sleep " + s);

    // wait for MRE (cancellation) or timeout
    Console.WriteLine("waitone " + s + " " + mre.WaitOne(5000));
  }

  Console.WriteLine("\nEnd " + s);
}

Then I found that a CancellationToken also exposes a WaitHandle, and rewrote the code as

static void SleepTaskMethod(int s, CancellationToken t)
{
  while (!t.IsCancellationRequested)
  {
    Console.WriteLine("loop/sleep " + s);

    Console.WriteLine("waitone " + s + " " + t.WaitHandle.WaitOne(5000));
  }

  Console.WriteLine("\nEnd " + s);
}

For both solutions, the task is invoked and cancelled using the same code:

var cts = new CancellationTokenSource();
var tasks = new List();
tasks.Add(new Task(() => { SleepTaskMethod(dummy, cts.Token); }));
foreach (var t in tasks)
  t.Start();

Console.WriteLine("press key to stop tasks");
Console.ReadKey();
Console.WriteLine("\nstopping task");

cts.Cancel();
Task.WaitAll(tasks.ToArray());

Invoking Stored Procedures generating C# Code using T4 Templates

When developing database applications, I usually start out with the database schema and have the necessary C# code (data classes, procedure calls, application-specific constants) generated by a couple of stored procedures. For my own projects, I create a batch file to build the various parts of the solution, all the way from code generation to publishing the application.

Maybe not everybody shares my preference for building-by-batch, so I was looking for an alternative way to invoke code generation in stored procedures, and I remembered working on a project a couple of years ago that used T4 Text Templates to generate C# code.

The first component of the T4 solution is MultipleOutputHelper.ttinclude which implements features such saving only changed files, handling check-out if connected to TFS, and much more.

Since I want to access the connection strings stored in web.config or app.config, I searched for sample code and found ConfigurationAccessor.tt.

The final T4 Template looks like this. The header includes both libraries and declares .Net namespaces:

<#@ template debug="True" hostspecific="True" language="C#" 
#><#@ output extension=".cs"
#><#@ include file="MultipleOutputHelper.ttinclude"
#><#@ include file="ConfigurationAccessor.ttinclude"
#><#@ assembly name="System.Data"
#><#@ import namespace="System.Data"
#><#@ import namespace="System.Data.SqlClient"
#><#

Next, we instantiate the objects declared in both libraries

  var manager = Manager.Create(Host, GenerationEnvironment); 
  manager.StartNewFile("Classes.generated.cs"); 

  var config = new ConfigurationAccessor((IServiceProvider)this.Host);
  var connStrEntry = config.ConnectionStrings["default"];

Depending on your build process, you may want to cause database errors (connection string, connectivity, execution) to raise either warnings or compiler errors:

  var warnings = true;
  var errors = new List<string>();

  if (connStrEntry == null)
  {
    errors.Add("Connection string named 'default' not found");
  }
  else
  {
    var connStr = connStrEntry.ConnectionString;

After checking the connection string, let’s connect to the database and execute the stored procedure.

The output of PRINT commands is retrieved using the InfoMessage event:

    try
    { 
      using (var conn = new SqlConnection(connStr))
      {
        conn.Open();
        conn.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs e)
        {                                    
            this.WriteLine(e.Message);
        };

        SqlCommand cmd = new SqlCommand("dev_Generate_DAL_Classes", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.ExecuteNonQuery();
      }
    }
    catch(Exception ex)
    {
      errors.Add(ex.Message);
    }
  }

  manager.EndBlock();

If no errors occurred, save the generated file. Otherwise, either raise warnings or errors.

  if (errors.Count == 0)
  {
    manager.Process(true); 
  }
  else 
  {
    foreach(var error in errors)
    {
      if (warnings)
        this.Warning(Host.TemplateFile + ": " + error);
      else
        this.Error(Host.TemplateFile + ": " + error);
    }
  }
#>

I originally tried to store warnings and errors using the Host.LogErrors method. However, no matter which Error Code I set in CompilerError, the result would always be errors rather than warnings. Using the TextTransformation‘s Warning() and Error() methods did the trick.

List of C# Compiler Errors

MSDN provides a list of C# Compiler Error Codes (VS 2003, VS 2005, VS 2008, VS 2010, VS 2012), but I could not find a complete list of error messages and descriptions.

So, for example, if you are looking for an appropriate error code you want to issue, you’d need to click through every error code until you find the correct one.

Fortunately, somebody (softwareAB, no affiliation) already compiled that data into a readable list, and provides the list of C# compiler error messages for download. (download saves as .txt, you need to rename the file to .html before viewing)

Thank you!

Response to “C# Wishlist: Null-Safe Dereferencing”

Reader Alex D commented on one of my C# Wishlist posts providing more elaborate code on this topic.

However, WordPress loves to eat angle brackets found in comments, leaving mangled code. I tried to restore the original code which probably looked like this:

public static TOut NullSafe<TIn, TOut>(this TIn obj, 
    Func<TIn, TOut> memberAction)
    where TIn : class
    where TOut : class
{
    return !ReferenceEquals(obj, null) ? memberAction(obj) : null;
}

public static TOut NullSafe<TIn, TOut>(this TIn? obj, 
    Func<TIn, TOut> memberAction)
    where TIn : struct
    where TOut : class
{
    return obj.HasValue ? memberAction(obj.Value) : null;
}

// If the return type of the lambda is not a nullable type but a value type
// you should write a cast to nullable in the lambda

public static TOut? NullSafe<TIn, TOut>(this TIn obj, 
    Func<TIn, TOut> memberAction)
    where TIn : class
    where TOut : struct
{
    return !ReferenceEquals(obj, null) ? memberAction(obj) : (TOut?)null;
}

public static TOut? NullSafe<TIn, TOut>(this TIn? obj, 
    Func<TIn, TOut> memberAction)
    where TIn : struct
    where TOut : struct
{
    return obj.HasValue ? memberAction(obj.Value) : (TOut?)null;
}

// Or use the following functions
// Unfortunately, they are named differently because the compiler doesn't allow
// overloads that differ only in their generic type constraints

public static TOut? NullSafeV<TIn, TOut>(this TIn obj, 
    Func<TIn, TOut> memberAction)
    where TIn : class
    where TOut : struct
{
    return !ReferenceEquals(obj, null) ? memberAction(obj) : (TOut?)null;
}

public static TOut? NullSafeV<TIn, TOut>(this TIn? obj, 
    Func<TIn, TOut> memberAction)
    where TIn : struct
    where TOut : struct
{
    return obj.HasValue ? memberAction(obj.Value) : (TOut?)null;
}

AssemblyVersion and AssemblyFileVersion

Every C# project typically contains a prefab file called AssemblyInfo.cs which contains the lines

// You can specify all the values or you can default the Revision and Build Numbers 
// by using the '*' as shown below:
[assembly: AssemblyVersion("1.2.*")]
[assembly: AssemblyFileVersion("1.2.0.0")]

Though these assembly attributes are named quite similarly, they must be retrieved in different ways.

The AssemblyFileVersion attribute is a list element of the return value of GetCustomAttributes():

string fileversion = null;
var a = Assembly.GetExecutingAssembly()
  .GetCustomAttributes(typeof(AssemblyFileVersionAttribute), true)
  .Cast<AssemblyFileVersionAttribute>()
  .FirstOrDefault();
if (a != null)
  fileversion = a.Version;

Compare this to the processing of AssemblyVersion, which can be retrieved from the current assembly using

string assemblyversion =
  Assembly.GetExecutingAssembly().GetName().Version.ToString();

(I noticed that I wrote about this topic many years ago, but I didn’t remember, and probably you didn’t either )

Browsing Web Directories using C#

To allow directory browsing in IIS, you need to navigate to the web site in IIS Manager, click the Browse Directory icon, and click the Activate button to the right.

Directory browsing generates an HTML view on the file system. Unfortunately, the HTML is not standardized and may different between different versions of IIS, and other web servers.

In case of IIS 7.5, the typical output looks like this:

<A HREF="/path/">[To Parent Directory]</A><br>
18.09.2011 13:44 25266 <A HREF="/path/images/foo.jpg">foo.jpg</A><br>
18.09.2011 13:44 &lt;dir&gt; <A HREF="/path/images/subdir/">subdir</A><br>

• The first line links to the parent directory.
• The seconds line is a file contained in the requested directory.
• The third line is a subdirectory below the requested directory.

To parse the files and directories, we need two separate regular expressions:

&lt;dir&gt;\s*\<A\s+HREF=""(?<url>.+?)""\>(?<name>.+?)\</A\>
\d+\s*\<A\s+HREF=""(?<url>.+?)""\>(?<name>.+?\.(png|gif|jpg|jpeg))\</A\>

This solution is based on an answer on SO, but with the following changes

• added the distinction between files and directories
• fixed the regex to non-greedy
• added “name” and “url” group names

var request = (HttpWebRequest)WebRequest.Create(url);
using (var response = (HttpWebResponse)request.GetResponse())
{
  using (var reader = new StreamReader(response.GetResponseStream()))
  {
    string html = reader.ReadToEnd();

    var rexDir = new Regex(
      @"&lt;dir&gt;\s*\<A\s+HREF=""(?<url>.+?)""\>(?<name>.+?)\</A\>", 
      RegexOptions.IgnoreCase);
    var rexFile = new Regex(
      @"\d+\s*\<A\s+HREF=""(?<url>.+?)""\>(?<name>.+?\.(png|gif|jpg|jpeg))\</A\>", 
      RegexOptions.IgnoreCase);

    var matchedDirs = rexDir.Matches(html);
    if (matchedDirs.Count > 0)
    {
      Console.WriteLine("dirs");
      foreach (Match m in matchedDirs)
      {
        Console.WriteLine(m.Groups["name"] + ": " + m.Groups["url"]);
      }
    }

    var matchedFiles = rexFile.Matches(html);
    if (matchedFiles.Count > 0)
    {
      Console.WriteLine("files");
      foreach (Match m in matchedFiles)
      {
        Console.WriteLine(m.Groups["name"] + ": " + m.Groups["url"]);
      }
    }
  }
}

Parsing and Analyzing OpenStreetMap Export Files

OpenStreetMap provides geo-coded information in the form of extracted .osm files for download. .osm files are XML files with a defined schema, but this information alone does not really help in analyzing the files. The reason is that the simple structure <node><tag /></node> distinguishes its data by the tag’s k attribute value:

  <node id="567053682" [...] lat="48.2066502" lon="16.3707086">
    <tag k="addr:postcode" v="1010"/>
    <tag k="addr:country" v="AT"/>
    <tag k="addr:street" v="Neuer Markt"/>
    <tag k="addr:city" v="Wien"/>
    <tag k="addr:housenumber" v="16"/>
  </node>

Therefore we first need to know which tag@k attributes are used in the XML. Given the size of .osm files (the extract for Austria is currently 3GB), a simple XmlDocument.Load() is bound to fail due to memory restrictions. Remember that both XmlDocument and XDocument are in-memory representations of the XML tree. See also my earlier comparison of XML access methods in .Net.

Fortunately, .Net also provides a way to a access an XML document without loading it into memory completely, namely by streaming it using XmlReader as illustrated in an MSDN sample.

Modifying this sample to process only nodes containing at least 1 tag, the enumerator to return such nodes looks like this:

static IEnumerable<XElement> GetNextNode(TextReader stringReader)
{
  using (XmlReader reader = XmlReader.Create(stringReader)) {
    reader.MoveToContent();
    while (reader.Read()) {
      switch (reader.NodeType) {
        case XmlNodeType.Element:
          if (reader.Name == "node") {
            XElement el = XElement.ReadFrom(reader) as XElement;
            if (el != null)
              if (el.Element("tag") != null)
                yield return el;
          }
          break;
      }
    }
  }
}

The code that calls this enumerator collects the values of the tag/@k attribute using a dictionary

var tagKeys = new Dictionary<string, int>();
using (var reader = new StreamReader(@"path\to\austria.osm", Encoding.UTF8)) {
  var i = 0;
  var dt = DateTime.Now;
  foreach (var el in GetNextNode(reader))  {
    i++;
    var keys = new List<string>();

    foreach (var tag in el.Elements("tag")) {
      var ka = tag.Attribute("k");
      if (ka != null) {
        var key = ka.Value;
        keys.Add(key);
        if (tagKeys.ContainsKey(key))
          tagKeys[key] = tagKeys[key] + 1;
        else
          tagKeys.Add(key, 1);
    }
  }
}

Finally, we can print the statistics on attributes and their numbers of occurrence:

foreach (var k in tagKeys.Keys.OrderBy(kT => kT))
  Console.WriteLine("{0} ({1})", k, tagKeys[k]);

After I found the various addr: attributes (see list with most frequent entries),

addr:city (44936)
addr:country (36215)
addr:housenumber (54034)
addr:postcode (45284)
addr:street (50469)

I wanted to know how often their combinations occurred:

if (keys.Contains("addr:postcode") && keys.Contains("addr:city")) {
  cPCC++;
  if (keys.Contains("addr:street")) {
    cPCCS++;
    if (keys.Contains("addr:housenumber"))
      cPCCSH++;
  }
}

resulting in

postcode+city 42352 
postcode+city+street 39864 
postcode+city+street+housenumber 39338

NHibernate String Comparison

If you try to compare strings in NHibernate Linq using string.Compare() or [string property].CompareTo(), the query evaluation will throw a NotSupportedException.

After a bit of searching I found various NHibernate extensions that add TSQL functionality to NH:

• Like

• Cast, Coalesce

• .Equals()

• string.Equals(StringComparison)

• User Defined Functions

• more on StackOverflow

For the 4 string comparison operations (GreaterThan, GreaterThanOrEqual, LessThan, LessThanOrEqual) we define a C# extension method:

public static class StringComparison
{
  public static bool GreaterThan(this string s, string other)
  {
    return string.Compare(s, other) > 0;
  }
}

Next, we define a BaseHqlGeneratorForMethod class for each of these extension methods which generates the HqlTree for the new operation:

public class StringGreaterThanGenerator 
  : BaseHqlGeneratorForMethod
{
  public StringGreaterThanGenerator()
  {
    SupportedMethods = new[]
    {
       ReflectionHelper.GetMethodDefinition<string>(x => x.GreaterThan(null))
    };
  }

  public override HqlTreeNode BuildHql(MethodInfo method, 
    System.Linq.Expressions.Expression targetObject, 
    ReadOnlyCollection<System.Linq.Expressions.Expression> arguments, 
    HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
  {
    return treeBuilder.GreaterThan(
      visitor.Visit(targetObject).AsExpression(),
      visitor.Visit(arguments[0]).AsExpression());
  }
}

Finally, the extensions have to be registered by a registry helper class which is added to the NH configuration:

public class StringComparisonLinqtoHqlGeneratorsRegistry 
  : DefaultLinqToHqlGeneratorsRegistry
{
  public StringComparisonLinqtoHqlGeneratorsRegistry()
  {
    this.Merge(new StringGreaterThanGenerator());
    this.Merge(new StringGreaterThanOrEqualGenerator());
    this.Merge(new StringLessThanGenerator());
    this.Merge(new StringLessThanOrEqualGenerator());
  }
}

In the code building configuration and session factory, add:

configuration.LinqToHqlGeneratorsRegistry
  <StringComparisonLinqtoHqlGeneratorsRegistry>();

The full code for all string comparison operations is available for download here.