Deploying database with .NET assemblies?

We're going to release a .NET 2.0 application soon (3-4 months) that uses SQL Express 2005 as the backend. We'd like to write some of our SPROC in C# (for obvious reasons!). Our .NET 2.0 app will be deployed using ClickOnce and each customer will have one instance of SQL 2005 Express installed locally.
Is there a way for be to deploy the database with all the C# SPROC "embedded" in it, or I'll have to register assemblies individually? I'm concerned about registering assemblies during installation, especially since we're deploying the application using ClickOnce (but not the database).
Also, from time to time, we need to add stored procedure or modify existing ones. In the "old" .NET 1.1 days, it was easy to do this. We had an assembly which main purpose was to update the database structure and SPROC with simple T-SQL commands (ie: ALTER PROCEDURE). Is there a way for us to achieve something similar with our application if we use C# SPROC?
Another concern I have is that the SQL database will most likely NOT be installed on the same machine as the client application (it will be installed on a server). Sending T-SQL queries worked fine with this scenario before, but now that things have changed, I'd like to know how to achieve such a thing.
Thanks for your help!
Carl
PS: any good book on Yukon out there yet? I can't find any!
Carl,

The best book out there on SQL Server 2005 is "A First Look at SQL Server 2005 for Developers" by Bob Beauchemin, Niels Berglund and Dan Sullivan.

To reload a new version of the assembly, using ALTER ASSEMBLY will work as long as the existing sprocs/functions have the same method signatures.

Deploying the database with the sprocs embedded is no problem. They are just held in varbinary form in a database level system table.

HTH,
Christian|||Christian,
Thanks for your answer! I looked into the Beauchemin's book on Amazon a few days ago and it looks interresting. What is holding me a little is that it's based on a 1 year old beta. Things have probably changed a lot since and might also change again for RTM.
About ALTER ASSEMBLY, do you know if it's possible to ADD managed procedures, functions or modify the signatures in some way? Also, do I need to use ALTER ASSEMBLY from the server machine or it could also work remotely? I assume it would, but I think I'm missing pieces of how this new managed SQL works right now...
Thanks a lot again!
Carl
|||

Carl Mercier wrote:

Christian,
Thanks for your answer! I looked into the Beauchemin's book on Amazon a few days ago and it looks interresting. What is holding me a little is that it's based on a 1 year old beta. Things have probably changed a lot since and might also change again for RTM.

As a co-author of the book, I can definitely say it is interesting :-). Yes, it is based on a one year old beta, and much have changed. However, we do have an errata page as well as a page for changes, so you'll always be able to see the latest info.

Carl Mercier wrote:

About ALTER ASSEMBLY, do you know if it's possible to ADD managed procedures, functions or modify the signatures in some way? Also, do I need to use ALTER ASSEMBLY from the server machine or it could also work remotely? I assume it would, but I think I'm missing pieces of how this new managed SQL works right now...

Sure you can add managed procedures, after running ALTER ASSEMBLY you just run CREATE FUNCTION/PROCEDURE/TRIGGER against your new methods. You are NOT allowed to modify signatures.
ALTER will work remotely, BUT; notice that the SqlServer project (which are used to create assemblies for SQL Server) in Visual Studio does not have functionality for ALTER assembly. That's one of the reasons I created my "yukondeployment" MSBUILD tasks and templates for Visual Studio. You can download it and read more about it here.
Niels|||Thanks for your help! I will definately check out Yukon Deployment!
I didn't know you were co-author of the book. I thought you deserved one more sale so I went ahead and ordered the book. Hope it arrives in time for my vacation! I'll read it on the beach in a few days! I hope this sale will at least buy you a cold one.
Your help is appreciated!
Carl