I mentioned awhile ago that I built a simple Database Explorer and Code Generator Tool as a pet project for use on my projects. It is not as flexible as CodeSmith, but I happen to like the interface much more for simple needs because it 1) offers point and click code generation and 2) saves property values for re-use based on a project. Right now it generates queries and stored procedures as well as your basic classes you may use in your applications ( simple business classes, active data record classes, data access object classes, table data gateway classes, provider classes, etc. ). Here is an old picture, but it gives you an idea of what I am talking about:
Last night as I was using it on a project, I realized that I needed to do more than just spit out Stored Procedure Create Scripts with this code generator. I needed to have an option to create the stored procedures in the Database itself to save me the effort of having to copy and paste the scripts. This works well for me as I typically develop in an iterative fashion, creating stored procedures one at a time as I need them as opposed to generating every stored procedure known to mankind in the database at once.
Creating a Stored Procedure Example using SQL Server Management Objects (SMO) and SQL Server 2005
After doing a little investigation, SMO allows me to create stored procedures in SQL Server 2005 programatically in my code generator to keep me from having to manually copy and paste the scripts.
SMO, a .NET based object model, ships with SQL Server 2005 in an assembly named Microsoft.SqlServer.Smo.dll. Some other supporting DLLs are also included in C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder. These include Microsoft.SqlServer.ConnectionInfo.dll and Microsoft.SqlServer.SmoEnum.dll. Like any other assembly, to use SMO in your application, add reference to Microsoft.SqlServer.Smo.dll (and Microsoft.SqlServer.ConnectionInfo.dll) and begin using the SMO classes.
Shown below is an example of creating a stored procedure, called GetClubByID, in a database, called MyDatabase. The comments pretty much tell the story.
// Create an instance of the server
string connectionString = "...Connection String...";
SqlConnection connection = new SqlConnection(connectionString);
Server server = new Server(new ServerConnection(connection));
// I want to add the stored procedure to the "MyDatabase" Database
Database db = server.Databases["MyDatabase"];
// Create a Stored Procedure called "GetClubByID" in "MyDatabase"
StoredProcedure mySP = new StoredProcedure(db, "GetClubByID");
mySP.TextMode = false;
mySP.AnsiNullsStatus = false;
mySP.QuotedIdentifierStatus = false;
// GetClubByID requires the ID of the Club as an Input Parameter
StoredProcedureParameter idParam =
new StoredProcedureParameter(mySP, "@ID", DataType.Int);
mySP.Parameters.Add(idParam);
// The SQL Text
mySP.TextBody = "Select [ID], [Title] FROM [Club] WHERE [ID] = @ID";
// Create the stored procedure in the database
mySP.Create();
Conclusion
SMO can help you create objects, like stored procedures, in SQL Server in your code generators and o/r mappers.
SQL Server Tutorials