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.

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());

ASP.Net MVC Framework Versions and .Net Versions Overview

Trying to figure out which version of the MVC framework runs on which .Net version, I compiled this table:

ASP.Net MVC	.Net Version	Visual Studio Version	Comments
1.0	3.5	2008	2.0 SP 1 unsupported
2.0	3.5	2008, installs with 2010	ildasm says 3.5, MSDN defaults to 4.0
3.0	4.0	2010
4.0	4.0	2010 SP1, installs with 2012	Release Notes

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"]);
      }
    }
  }
}

List Invalid Guids of Referenced Projects in VS Solution Dependency Visualizer 0.92

While preparing one of my projects for production, I came across a “funny” error:

While Visual Studio builds a solution without errors, msbuild raised the following error for an ASP.Net web application:

The type or namespace name ‘foo.bar’ does not exist in the namespace ‘foo’ (are you missing an assembly reference?)

I checked in Solution Explorer, and the project reference was clearly there, the assembly showed the namespace in ILSpy, and VS built the solution.

A little panicky, I removed the project reference and added it again. msbuild was now able to build the web application, but the build process raised solution file warnings:

c:\path\to\solution.sln : Solution file warning MSB4051: Project {Some-GUID-0001} is referencing a project with GUID {Other-GUID-0002}, but a project with this GUID was not found in the .SLN file.

Notice that the error message does NOT inform us while project contains the reference, and which other project was meant to be referenced, even though VS has all the necessary information to display the projects.

So I started another instance of VS, opened the VSSlnDep project, and added a new function, “List Invalid Guids of Project References”.

This function iterates through the project references of each project file (*.csproj, *.vbproj), and checks whether the referenced Guid is the project Guid of any project in the solution. If not, the reference is displayed using the referring and the referred project file names, along with both Guids.

To solve the invalid Guids, you need to navigate to the referring project in Solution Explorer, delete the project reference and add it again.

Conclusion 1: Visual Studio seems to resolve references using the project file name, whereas msbuild resolves using the project Guids.

Conclusion 2: Nobody knows why VS changes project Guids. Not even Microsoft.

As usual, the latest release of VS Solution Dependency Viewer is available for download here.

List Shared Project Files in VS Solution Dependency Visualizer 0.91

A user of VS Solution Dependency Visualizer inherited a couple of copy-and-paste projects and asked whether it was possible to detect which files and directories are used by which projects.

As for directories, if you check the “Include Files” options, click “Analyze”, and switch to the “Analysis” tab, and invoke “List Directories” or “List Project Directories”,  VS Solution Dependency Visualizer displays all directories used by files in any project of the opened solution, either for the whole solution, or per project.

The updated version 0.91 also implements the options “List Files” and “List Project Files”, again displaying the list of files in the whole solution, or per project. (This List Files function was implemented in earlier versions as part of the display of a solution or project node in the tree view, but removed in the meantime)

I also added an analysis function “List Shared Project Files” which displays all files that are part of at least 2 projects in a solution (i.e. files that have been added using the “Add As Link” button).

In the tree view display of the Analysis tab, the names of the shared (or linked) files are displayed as root nodes, and the projects that contain them as child nodes.

As usual, the latest release of VS Solution Dependency Viewer is available for download here.

Fixing the Blob Truncation problem with NHibernate 3.2.0 GA

In one of my NH ClassMaps, I map a VARBINARY(MAX) column like this

Property(x => x.OriginalImage, map =>
  {
    map.Column("OriginalImage");
    map.NotNullable(true);
    map.Lazy(true);
  });

but when I save an uploaded image, the binary data in the database is truncated to 8000 bytes.

Adding a

    map.Length(int.MaxValue);

causes NHibernate to expect an IMAGE column rather than VARBINARY(MAX) (using the MsSql2005Dialect dialect).

After a short search I found two solutions for this problem:

• Explicitly define a SqlType():

Property(x => x.OriginalImage, map =>
{
map.Column(c => { c.Name(“OriginalImage”); c.SqlType(“VARBINARY(MAX)”); });
map.NotNullable(true);
map.Lazy(true);
map.Length(int.MaxValue);
});

based on this SO answer referring to Fluent rather than Loquacious.

• Subclass the dialect

As illustrated in this SO answer

public class MyMsSql2008Dialect : MsSql2008Dialect
{
    protected override void RegisterLargeObjectTypeMappings()
    {
        base.RegisterLargeObjectTypeMappings();
        base.RegisterColumnType(DbType.Binary, 2147483647, "VARBINARY(MAX)");
    }
}

NHibernate 3 Data Type Mapping for SQL Server

When I recently tried to add a DOUBLE column in a project using NHibernate 3.2.0 GA to connect to a SQL Server database, the schema validation failed since the T-SQL DOUBLE/C# double mapping did not work out. So I wondered how the NHibernate schema validator decides which column data types are supported for each of the C# data types.

So I checked the following sources:

• the MsSql****Dialect.cs files in the \src\NHibernate\Dialect\ directory of 3.2.0GA

• the DbType mapping of C#/.Net types (MSDN)

• the history of DbType values (MSDN)

• SQL Server data type mappings (MSDN)

The result, sorted by the DbType enumeration, looks like this:

DbType	.Net version	C#	NH Mapping 7/2000	2005	2008
AnsiString	1.1		VARCHAR(255)
			VARCHAR(n)
			TEXT
				VARCHAR(MAX)
AnsiString- FixedLength	1.1		CHAR(255)
			CHAR(n)
Binary	1.1	byte[]	VARBINARY(8000)
			VARBINARY(n)
			IMAGE
				VARBINARY(MAX)
Boolean	1.1	bool	BIT
Byte	1.1	byte	TINYINT
Currency	1.1		MONEY
Date	1.1		DATETIME
					DATE
DateTime	1.1	DateTime	DATETIME
DateTime2	3.5				DATETIME2
DateTime- Offset	3.5	DateTime- Offset			DATETIME- OFFSET
Decimal	1.1	decimal	DECIMAL(19,5)
			DECIMAL(n,n)
Double	1.1	double	DOUPLE PRECISION (synonym for FLOAT(53))
Guid	1.1	Guid	UNIQUEIDENTIFIER
Int16	1.1	short	SMALLINT
Int32	1.1	int	INT
Int64	1.1	long	BIGINT
Single	1.1	float	REAL (synonym for FLOAT(24))
String	1.1	string	NVARCHAR(255)
			NVARCHAR(n)
			NTEXT
				NVARCHAR(MAX)
String- FixedLength	1.1		NCHAR(255)
			NCHAR(n)
Time	1.1	TimeSpan	DATETIME
					TIME
Xml	2.0			XML

Note that some data types are not supported:

• DbType: Object, Sbyte, UInt16, UInt32, UInt64, VarNumeric

• C# data types: char, object

• T-SQL data types: sql_variant, rowversion, smalldatetime, timestamp, and CLR types