ADO.NET Gotcha When Calling Stored Procedures Make Sure You Remember the CommandType by William Ryan

When I was first learning ADO.NET, I made a few mistakes repeatedly.  If I earned a dollar each time I forgot to close a DataReader or tried calling the ExecuteReader method of the Command object when I still had an open Connection, I'd be a rich guy.  Anyway, there was a more elusive bug that haunted me, even more elusive than this one.  I started using Stored Procedures with ADO.NET as soon as I could and at the time.   I'd walk through the examples in my favorite ADO.NET book and everything worked fine.  Then I started writing my own classes and most of the time, everything worked, but I occassionally got this bizarre error "Syntax Error new 'usp_SomeProc'.  So I verified that my stored procedure was spelled correctly but that didn't fix it.  I always use Option Strict and Option Explicit when I'm programming VB.NET (about the only thing I really dislike about VB.NET is that Microsoft left Option Strict Off by default) so I was stumped.  I did the usual thing and turned on SQL Profiler, verified that I could run the command in Query Analyzer and all the usual tricks.  I also had enough experience with not properly permissioning my procs that I knew what that error looked like (Execute Premission on SomeObject...).  Being that I had a lot more experience with T-SQL, I decided to write out some error codes and see what that could tell me.  However, I never got that far..... So what was wrong?  After spending about 30 minutes trying to figure it out, I looked at a previous code snippet I had that called a Stored Procedure and worked correctly.  After looking at the two side by side, I realized there was only one difference but I didn't think it would cause a Syntax error.  I didn't set the CommandType property of the command.  At first I thought, "No, that can't be it...." (after all, it's a lot easier to think it's Microsoft's bug than your own, except for the fact that's seldom the case), but I exhausted all of my other options.  Well, I changed it and everything worked fine.

So, why does not setting CommandType cause an exception that indicates a Syntax error? As I discoverd later on, CommandType is the property ADO.NET looks to when interpreting the CommandText property.  This might seem irrelevant, after all, you can call 'usp_SomeProcedure' in query analyzer just as easily as you can call exec 'usp_SomeProcedure'.  But try doing the same in Enterprise Manager and you'll see it won't fly.  From what I've been told, there are some optimizations that happen behind the scenes that need you to set the command type.  Moreover, if you use 'exec usp_SomeProc' without setting the CommandType, you'll be ok.  However your proc will be treated like dynamic sql and we all know how evil that is.

So why do I mention all of this?  For two reasons.  1)  If you get this exception, it's not intuitive that CommandType and Syntax errors have anything to do with each other.  2)  I probably did this once every four weeks, and the first few times, I forgot what the solution was the time before (at least for 30 seconds or so).  As such, it's worth hard coding into your keystrokes....ALWAYS USE STORED PROCS and ALWAYS EXPLICITLY SET THE COMMANDTYPE PROPERTY.