how to check if stored procedure exists or not in sql server using c# code

Question

I tried below code for cheking SP is alredy exist or not. if not exist i am creating..

But every time it is showing sp is not created.....But my database already have this sp.

Let me know where i am doing mistake.

string checkSP = String.Format(
  "IF OBJECT_ID('{0}', 'U') IS NOT NULL SELECT 'true' ELSE SELECT 'false'", 
  "GP_SOP_AdjustTax");

SqlCommand command = new SqlCommand(checkSP, myConnection);
command.CommandType = CommandType.Text;

if (myConnection == null || myConnection.State == ConnectionState.Closed)
{
    try
    {
        myConnection.Open();
    }
    catch (Exception a)
    {
        MessageBox.Show("Error " + a.Message);
    }
}

bool Exist = false;
Exist = Convert.ToBoolean(command.ExecuteScalar());
if (Exist == false)   //false : SP does not exist
{ 
    // here i am writing code for creating SP
}

Answer 1

For those who use Entity Framework and a DbContext:

create an extension class for DbContext:

internal static class DbContextExtensions
{
    public static bool StoredProcedureExists(this DbContext context,
        string procedureName)
    {
        string query = String.Format(
            @"select top 1 from sys.procedures " +
              "where [type_desc] = '{0}'", procedureName);
        return dbContext.Database.SqlQuery<string>(query).Any();
    }
}

As robIII remarked, this code should not be published to the outside world as it makes the database vulnerable for hackers (thank you RobIII!). To prevent this use a parameterized statement. The problem with the above mentioned method is described here

The solution is to put procedureName as a parameter in an SQL statement. SQL will check if the string parameter has the desired format, thus inhibiting malicious calls:

public static bool ImprovedExists(this DbContext dbContext, string procedureName)
{
    object[] functionParameters = new object[]
    {
        new SqlParameter(@"procedurename", procedureName),
    };
    const string query = @"select [name] from sys.procedures where name= @procedurename";
    return dbContext.Database.SqlQuery<string>(query, functionParameters).Any();
}