What's The Best Way To Deploy CLR Extensions To SQL Express?

The project I'm working on has been developed using SQL 2005 Developer Edition and VS 2005, so up until now VS has been handling the task of deploying my C# managed code stored procedures to the database.

Now it's time to deploy a beta of the product, and we want to use SQL 2005 Express, and take advantage of it's new XCOPY deployment mode which in turn means we can also take full advantage of Click Once for our software updates. The idea here is that we can deploy new or updated stored procedures in a managed DLL using Click Once, making updates presumably more simple and robust.

So the steps we need to complete at runtime are:

1. Dynamically load and access the database MDF file using SQL Express (done).

2. Enable CLR Integration for the dynamically loaded DB (done)

3. Instantiate all of the stored procedures - help!

Looking at the docs, it seems we have to execute the following SQL for each and every SP:

CREATE PROCEDURE <Procedure Name>

@inputArg2 type, @inputArg2 type, @inputArg3 type OUTPUT

AS EXTERNAL NAME <Assembly Identifier>.<Type Name>.<Method Name>

But, we're talking about literally hundreds of stored procedures that need to be instantiated here, which obviously means a lot of double-definitions and a fairly high chance of bugs creeping in.



Surely there has to be a better way?