Create a stored procedure with SMO
Once you are connected to SQL Server 2005 with a SMO connection, you can start manage and create object. Here is an example on how you can create a stored procedure, we imagine that we have the code of a stored procedure in an embeded resources file. (this sample is written in C# and use a beta version of SQL Server 2005 and of the .Net Framework 2.0).
For this sample you need to use the following name space :
using
Microsoft.SqlServer.Management.Smo;
using
Microsoft.SqlServer.Management.Smo.Agent;
using
Microsoft.SqlServer.Management.Common;
And here is the code of the sample :
StoredProcedure
mySP;
string myResourceFile;
mySP =
new StoredProcedure(sqlserver.database, "createNewYear");
myResourceFile =
"MyAssemblieName.StoredProc.sql";
StreamReader sr = new StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream(myResourceFile), System.Text.Encoding.Default);
mySP.TextBody = sr.ReadToEnd();
mySP.Create();