Embedded Resource Queries or How to Manage SQL Code in Your .NET Projects

I developed a technique for managing SQL code in my .NET projects using the Embedded Resource “Build Action” back in the .NET 1.0 days that I still use to this day. I’ve been meaning to share this with the world for several years now and I’m finally getting around to it.

The benefits of this technique are many. Your SQL queries are contained in individually-editable, properly-formatted files in your .NET project instead of constructed as nasty concatenated strings in your code.

However, at run-time, you don’t have to worry about distributing extra files with your application, the SQL text is an embedded resource in your executable or class libary. You get the best of both worlds. You have source control diffs available but no additional distribution burden.

Before I go further, let me address the issue of stored procedures. Stored procedures have their place. In the past, I’ve maintained 2000+ line stored procedures. I’m ashamed to admit that I’ve even written some 2000+ line stored procedures back in the heyday of client server. Stored procedures (not 2000+ line long ones, surely) have their place and can be a great way to minimize network round trips. However, for purely select-oriented SQL, I think stored procedures are an unecessary administrative burden on most .NET projects. By using this technique, you never have to worry about your .net code being out of synch with the stored procedure.

Modern database servers cache query plans equally well for parameterized ad hoc queries as they do for stored procedures. As a result, unless you are doing some strange things, you will not find a measurable performance difference between using a stored procedure and the embedded resource technique I am about to demonstrate for select-oriented SQL.

Finally, let me say that if you work in a change management environment where it is easier to recompile stored procedures than it is to push out DLLs, then this technique may not be for you. Also, if you expect your query to be re-used as is by many different types of non .NET clients (for example, an excel spreadsheet or other stored procedures) then you may want to stick with a stored procedure. Keep in mind that you can easily re-use SQL stored as embedded resources in .NET DLLs among different .NET projects.

Many times I find myself in a query tool such as Query Analyzer, SSMS Query Editor, or Oracle SQL Developer iteratively developing a SQL query that returns a single recordset of results. Once I’ve got the query working reasonably, I create a new text object in under a folder named SqlQueries in Visual Studio in my project, rename it to something.sql and paste in the SQL from the query tool unaltered. I then comment out the section at the top of the query that declares the parameter variables (if I’m using SQL server). Notice that I don’t delete them. This allows me to easily run the query in a query tool if I need to refine or debug the query.

Once the file is in the project, you have to set the build action to embedded resource as shown below:

Setting Build Action to Embedded Resource

We need a class that helps us conveniently access our embedded resources. I’ve always used a class with static methods like this one:

public class EmbeddedResource {
	private EmbeddedResource() {
	}

	public static StreamReader GetStream(System.Reflection.Assembly assembly, string name) {
		foreach (string resName in assembly.GetManifestResourceNames()) {
			if (resName.EndsWith(name)) {
				return new System.IO.StreamReader(assembly.GetManifestResourceStream(resName));
			}
		}
		return null;
	}

	public static string GetString(System.Reflection.Assembly assembly, string name) {
		System.IO.StreamReader sr = EmbeddedResource.GetStream(assembly, name);
		string data = sr.ReadToEnd();
		sr.Close();
		return data;
	}

	public static string GetString(string name)
	{
		return EmbeddedResource.GetString(typeof(EmbeddedResource).Assembly, name);
	}
}

If I wanted to retreive some embedded sql in a file named SelectOrderWithExpensiveItemInfo.sql under a project directory directory named SqlQueries as a string to a variable named sql I would do this:

string sql = EmbeddedResource.GetString("SqlQueries.SelectOrderWithExpensiveItemInfo.sql");
SqlCommand cmd = new SqlCommand(sql);

You may be wondering about the for each loop inside EmbeddedResource.GetStream. Initially, I specified the whole resource name which included what I assumed was the assembly name at the beginning of the resource specifier. Unfortunately, the default namespace from project properties is what is actually used (at least when I first wrote this way back when). I couldn’t find a reliable way to retreive the default namespace so I added the search logic. Also keep in mind that you can only use GetString without passing the assembly in if your code is executing in the same assembly in which the embedded resource is housed.

That’s all there is to it. Here’s a fully-functional demo solution showing this technique in action using the AdventureWorks database. Enjoy. I hope that you find embedded resource SQL queries as useful as I have.

Advertisements

Resharper 3.x is Buggy as Hell

I was a huge resharper fan…until I installed 3.x. I have experienced nothing but bugs. The little bug/feedback box pops up 5 times a day on average. I have to delete all the resharper files to get the arrow keys working in one of my projects. I’ve submitted numerous bug reports and even gone to the trouble of providing screenshots and bug-demoing projects. None of them have been addressed yet.

I have read a lot of positive posts about 3.x but I really find it hard to believe that I’m the only one out there having these problems. So, as unpopular is it may make me, I’m making this post giving Resharper 3.x a big thumbs down.

P.S. I recently installed 3.0.3 build 3.0.520.30 and things are working much better.

Why Are Code Snippets XML?

Today I was going to write my first code snippet. I was tired of writing out the null argument check code in constructors. I figured it would be as easy as just taking some working code, replacing the variable name with a variable, and saving this somewhere. When I open the help….which takes forever and defaults to going on-line which makes me wish I had just googled what I was interested in except that the first link would probably have been a site wanting me to register before showing me the answer…I see that code snippets are effing XML. WHYYYYYY????? If there was ever a case of the misuse of XML to overcomplicate and obfuscate something simple. So my simple little code snippet which should have been something like:

if ($variableName == null)
{
throw new ArgumentNullException(“$variableName”);
}

Is this ridiculous XML monstrosity which I won’t even bother to list. It has the big long namespace and the oh-so-familiar overly verbose XML layout that completely sucks for authoring a code snippet. The XML is such a tedious pain in the ass I’m not even going to bother to create the snippet. Instead I spent the time posting this rant. :)

Missing “Immediate” Window in Visual Studio 2005

I’ve done two fresh installs of VS 2005 in the last 9 months. Both times, the “immediate” window wasn’t where it was supposed to be. Normally, you start a project in debug mode, hit a break point, and then in the menu, Debug|Windows, you will see Immediate.

I don’t know why, but twice now, mine’s been gone. You can find out how to open the “Command” — ctrl-alt-A. But that doesn’t have intellisense. To get the to the immediate window if it’s not showing up in your Debug|Window menu, open the “command” window and type immed and press enter.

P.S.
Or, as Sanjot points, out just press Ctrl-Alt-I. This appears to work as well.

Visual Studio 2005 Help So SLOW!

I’m sure I’m the just the one millionth person to say this, but I dread pressing F1 that first time in Visual Studio 2005.  It takes forever even when you turn off the internet related stuff.  Why did they do this?  VS 2003’s help was lightning fast.  What were they thinking?  I know with intellisense, you really don’t need to go to the help that often.  But when you do, it shouldn’t be coffee break time.

PS. Thanks to Rich A we have a solution!