Getting stored procedure script through C# in ASP .NET

Question

I am working on a DbCompre tool. Basically my requirement is that through C#, generate stored procedure script in web application. First of all is it possible?

In SQL Server we generate script easily through right click or with the help of command sp_helptext <ProcedureName>.

Here is my code:

public DataTable generateScripts()
{
      SqlCommand cmd = new SqlCommand("sys.sp_helptext dloc_GetTasksRewarehousePutaway", AppCon);
      cmd.CommandType = CommandType.StoredProcedure;
      SqlDataAdapter adp = new SqlDataAdapter(cmd);
      adp.Fill(DS);
      adp.SelectCommand = cmd;
      return DS.Tables[0];
}

When code execute I am getting this error:

Could not find stored procedure 'sys.sp_helptext dloc_GetTasksRewarehousePutaway'

But in database this procedure is available.

Answer 1

Absolutely its possible, you need to pass the SP name as a parameter though to the parameter objname:-

SqlCommand cmd= new SqlCommand("sys.sp_helptext", AppCon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@objname", "dloc_GetTasksRewarehousePutaway");

Answer 2

using (SqlConnection con = new SqlConnection ("Connection String Here"))
{
    using (SqlCommand cmd = con.CreateCommand())
    {
        cmd.CommandText = "sp_helptext @procName";//Stored Procedure name
        cmd.CommandType = CommandType.Text;

        cmd.Parameters.AddWithValue("procName", "dloc_GetTasksRewarehousePutaway");

        con.Open(); 

        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                /* 
                    You will get the CREATE PROC text here
                    Do what you need to with it. For example, write
                    to a .sql file
                */
            }
        }
    }
}