Web Deployment Tool - RC1 - x86 : Download : The Official Microsoft IIS Site
Retrieve Stored Procedure's parameters using SMO
I was trying to find something that I could use to retrieve stored procedure's parameters so that I won't have to write out the statements for my sproc parameters every time I have to create one. Here is something that I just did to create statements to add parameters for stored procedures that could be copied over and dumped in a method.
using System;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
namespace SMOApp
{
class Program
{
static void Main(string[] args)
{
ServerConnection conn = new ServerConnection("_dev");
conn.LoginSecure = true;
//conn.Login = "";
//conn.Password = "";
Server svr = new Server(conn);
Database myDB = svr.Databases["_TOOLS"];
foreach (StoredProcedure sp in myDB.StoredProcedures)
{
if (sp.Name == "s_insert")
{
Console.WriteLine("public static IDataReader SelectRecords(string userEmail)");
Console.WriteLine("{");
Console.WriteLine("string sqlCommand = \"" + sp.Name + "\";");
Console.WriteLine("DbCommand dbCommandWrapper = database.db.GetStoredProcCommand(sqlCommand);");
foreach (StoredProcedureParameter param in sp.Parameters)
{
Console.WriteLine(param.Name);
Console.WriteLine("database.db.AddInParameter(dbCommandWrapper, \"" + param +
"\", DBType." + param.DataType + ", " + param.ToString().Replace("@", "") + ");");
}
Console.WriteLine("return database.db.ExecuteReader(dbCommandWrapper);");
Console.WriteLine("}");
}
}
}
}
}
Scripting Database Objects using SMO (Updated) - SQLTeam.com
- Microsoft.SqlServer.ConnectionInfo.dll
- Microsoft.SqlServer.Smo.dll
- Microsoft.SqlServer.SmoEnum.dll
Scripting Database Objects using SMO (Updated) - SQLTeam.com
Subscribe to:
Posts (Atom)