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("}");
                }
            }
        }

    }

}