What is the best practice to manage stored procedures in C# code?

Question

I'm quite new here, so please forgive me if I made any deviation from the rules of this website.

I'm trying to find the best way possible to manage the names of a stored procedure in code.

Currently when I'm calling a stored procedure I'm using this code:

public static DataSet GetKeyTables()
{
    DataSet ds = new DataSet();
    ds = SqlDBHelper.ExecuteMultiSelectCommand("Sp_Get_Key_Tables", 
        CommandType.StoredProcedure);
    return ds;
}

But I don't think that stating the name of the stored procedure in code is a wise idea, since it will be difficult to track.

I thought about Enum or app.config solutions, but I'm not sure these are the best ways.

Any idea will be highly appreciated.

Answer 1

You can have a class with constant properties having names of the SPs.And have this class in a seperate class library (dll). Also it is not good to have sp_ as start of procedure see the link http://msdn.microsoft.com/en-us/library/dd172115(v=vs.100).aspx

public class StoredProcedures
{
    public const string GetKeyTables = "Sp_Get_Key_Tables";
}

Answer 2

In the end, it always boils down to the concrete name string of the SP, no matter what you do. You have to keep them in sync manually. - No way around it...

You could use configuration files for that, but that additional effort will only pay when the names change frequently or they need to remain changeable after compilation.