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.

connection string working perfectly, because i use same connection string in entire application. — ANJYR
– ANJYR, Commented Oct 6, 2015 at 11:23

Rahul Singh · Accepted Answer · 2015-10-06 11:15:09Z

7

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

answered Oct 6, 2015 at 11:15

Rahul Singh

21.8k6 gold badges43 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mainak · Accepted Answer · 2015-10-06 11:21:51Z

3

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
                */
            }
        }
    }
}

edited Oct 6, 2015 at 11:21

Mainak

4683 gold badges10 silver badges33 bronze badges

answered Oct 6, 2015 at 11:14

Rakin

1,28914 silver badges31 bronze badges

Comments

marc_s · Accepted Answer · 2022-08-11 20:10:52Z

Whole example to get stored procedure T-SQL text from database

// add connection string 
var conString = "Db Connection String";

// add SP name
var spName = "DbSpName";
var sb = new StringBuilder();

try
{
    using (var conn = new SqlConnection(conString))
    {
        DataSet ds = new DataSet();

        SqlCommand cmd = new SqlCommand("sys.sp_helptext", conn);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@objname",spName );

        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        sda.Fill(ds);
        sda.SelectCommand = cmd;

        var dt = ds.Tables[0];
                            
        foreach (DataRow dtRow in dt.Rows)
        {
            sb.Append(dtRow["Text"].ToString());
        }
    }
}
catch (Exception ex)
{
    // log if something went wrong ex.Message
}

Collectives™ on Stack Overflow

Getting stored procedure script through C# in ASP .NET

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related