none
ADO C# how to tell if a sproc exists RRS feed

  • Question

  • We have a system that allows users to create custom sprocs.  Is there a way in 2.0 framework with ADO to tell if a sproc exists?
    joe alt
    Friday, January 15, 2010 1:33 PM

Answers

  • Not as far as I know, you could check for it by using a simple SQL against the database. Like so:

                String spName = "ProcedureName";
                using (SqlConnection con = new SqlConnection(cs))
                {
                    try
                    {
                        con.Open();
                        SqlCommand cmd = con.CreateCommand();
                        cmd.CommandText = String.Format("select count(name) from sys.objects where name = '{0}' and type = 'P'", spName);
                        if ((int)cmd.ExecuteScalar() > 0)
                        {
                            cmd.CommandText = spName;
                            cmd.CommandType = System.Data.CommandType.StoredProcedure;
    
                            // Use it and do work. Ie.
                            cmd.ExecuteNonQuery();
                        }
                        else 
                        {
                            Console.WriteLine("Procedure not existent");
                        }
                        con.Close();
                    }
                    catch (Exception)
                    {
                        
                    }
                }

    There will obviously be an extra trip to the database when checking for the procedure.

    HTH
    //Michael
    This posting is provided "AS IS" with no warranties.
    • Marked as answer by altja Friday, January 15, 2010 2:48 PM
    Friday, January 15, 2010 2:34 PM