Managed Stored Procs in vs 2005

Hmmm. This is a combination of quite neat and downright weird. I've been writing a throw away Library Management system to test out the beta, and I was about to create a load of stored procs - I was all geared up to write a little bit of Sql to get what I wanted out of the database (I used Studio to create tables and add reference data - nothing earth shatteringly different in the tools at this point; I was a little concerned I couldn't create a database diagram, though). So, I added a data project to my solution, and chose new Stored proc from the context menu. Except instead of the little Sql script file I was expecting, Studio inserted a class file into the project.

Immediately, I balked. I knew all about this; I mean I'd read about it being a big new feature in "Yukon"; but suddenly I felt like a fish out of water. I had to delve into the flaky, incomplete & inaccurate documentation ("[This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]") to work out where everything was.

Basically, all the action seems to sit in two namespaces: System.Data.SqlServer, and System.Data.SqlTypes; there's a [SqlProcedure] attribute that goes on your class. Then you just create a method in the usual way. Only I've got big problems with this... unless I'm missing somethng fundamental (I'd thank anyone who has a better idea to illuminate me...).

I mean, first of all, the name of your method automatically becomes the name of your procedure; in Sql Server, the hierarchy of namespaces and the class name is not important. Although Studio manages this for you (right-click the project and select Deploy), you can control it by executing a Sql CREATE PROCEDURE command, using the new EXTERNAL NAME clause to associate your chosen proc name with a method in your assembly. Trouble is, try to create overloaded methods and you wind up with an error:

"Error: More than one method, property or field was found with name 'Fetch' in class 'ReferenceData.SubCategory' in assembly 'Codex.SqlServer'. Overloaded methods, properties or fields are not supported."

So, here's me trying to create some object oriented code to manage my data in Sql Server, using C# (I mean - the point of using C# to write your procs is that it's an OO language, right?), and it turns out that managed stored procs won't support this.

The other problem I have is that, in all the docs I've been able to dig up, in order to fetch data (for example), you create a SqlCommand object and pass it a string containing your query. Hang on - run that by me again? You hardcode Sql into a C# stored procedure? Right. Surely, you can access tables and fields programmatically? There must be a way, and I'm going to see if I can find it - otherwise, well, it just doesn't make sense, and this lot just ain't gonna fly.

In the meantime, I'm reverting to traditional Sql stored procs.