Dave and I have written a T4 template to generate Entity Framework-friendly INSERT, UPDATE, and DELETE stored procedures. I’ll detail the template in a future post. I had the crazy idea of implementing something similar to C#’s partial methods in SQL, which I named “partial stored procedures.” The reason behind the idea is the same reason partial methods exist: a code generator (a T4 template in our case) generates the same code every time, overwriting any changes someone might have made to the generated code. The Entity Framework and LINQ to SQL designers create partial methods in the generated code to provide points where a developer can execute code, such as before and after a property changes. It’s possible to do something similar when generating stored procedures, but the performance hit might be discouraging. Here’s an example of what the code generator might create:
CREATE PROCEDURE [dbo].[Person_Insert]
IF OBJECT_ID(‘[dbo].[usp_BeforePerson_Insert]’) IS NOT NULL
EXEC [dbo].[usp_BeforePerson_Insert] @name
INSERT INTO [Test] VALUES (@name)
IF OBJECT_ID(‘[dbo].[usp_AfterPerson_Insert]’) IS NOT NULL
EXEC [dbo].[usp_AfterPerson_Insert] @name
I did a small amount of testing while I played around with the idea. Inserting 100,000 rows into the two-column table using a regular stored procedure took 31 seconds. After adding the additional SQL, but without creating the “partial” stored procedures, 100,000 executions took 35 seconds to complete. When I created the “before” stored procedure with a simple SELECT in it, the time rose to 54 seconds. Although it’s an interesting idea, there isn’t really any need for something like partial methods in SQL. The same thing can be accomplished by adding INSTEAD OF and AFTER triggers to the table.
I ran into an interesting situation the other day while using the InternalsVisibleToAttribute. Our team is developing three libraries as part of our current project, and all three of them are signed with the same key. Assembly A makes its internals visible to Assembly B and C. Assembly A and B build successfully, but Assembly C fails with the error:
Friend access was granted to ‘AssemblyC, PublicKey=0024000004800000…’, but the output assembly is named ‘AssemblyC, Version=22.214.171.124, Culture=neutral, PublicKeyToken=null’. Try adding a reference to ‘AssemblyC, PublicKey=0024000004800000…’ or changing the output assembly name to match.
We obviously can’t add a reference to Assembly C in Assembly A, as that would cause a circular dependency. The only clue we could get out of that error message is the PublicKeyToken=null bit. Why would the PublicKeyToken be null if the assembly is signed? I wanted to confirm that the assembly was signed with Reflector or sn.exe, but there was no dll to check because the build failed. After commenting out all of the code that accessed the internal parts of Assembly A, we got Assembly C to build and confirmed that it was indeed signed. From the output window, we saw that the “WorkflowCompilation” task was executing and failing, and that the “CoreCompile” wasn’t even running. It seems that the compilation fails because Assembly C is not yet signed when the compiler attempts to verify the InternalsVisibleTo attribute on Assembly A. We managed to get the project to build by adding the AssemblyKeyFile attribute to it:
This produces the warnings “Use command line option ‘/keyfile’ or appropriate project settings instead of ‘AssemblyKeyFile'” and “Option ‘keyfile’ overrides attribute ‘System.Reflection.AssemblyKeyFileAttribute’ given in a source file or added module” that we have to ignore. I’ve found this problem to happen to any workflow assemblies that attempt to access the internals of another assembly. I’ve submitted a bug report about it here.
I recently wrote an extension method named IsNullOrEmpty() for IEnumerable<> types. Dave had already written one all IEnumerable types:
public static bool IsNullOrEmpty(this IEnumerable source)
if (source != null)
IEnumerator enumerator = source.GetEnumerator();
This method has been useful countless times, but Bek realized this could leave open database connections, or cause other problems related to not calling Dispose. I added the overload:
public static bool IsNullOrEmpty<T>(this IEnumerable<T> source)
if (source != null)
using (IEnumerator<T> enumerator = source.GetEnumerator())
A question was raised about whether the generated finally block would be executed if the method returned inside the try block. I couldn’t remember the answer, but a quick test with LINQPad gave us our answer:
“Hello world” was displayed in the output box. If the finally block weren’t executed before the method returned, we’d have to store the return value in a temporary variable and place the return statement after the end of the using/finally block. It’s one of the small tidbits that are nice to know.
The other day my team leader, Mark, pointed me to a great site for testing your problem solving skills. Project Euler contains over 180 mathematical or computer programming related problems that you can solve yourself. You can create an account and submit your answers to track your progress. Once you’ve solved the problem, you gain access that problems thread where you can discuss it. I’ve started from the beginning using C# in LINQPad. I do one or two each day as a brain exercise before I begin work. I’m considering creating a new page to house my solutions, beginning with the nine I’ve solved so far. In any case, check out the site and try your hand at some of the problems.