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.

Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Dec 21 '13 at 16:47
"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 have never had any such difficulty. If you're calling the same SP from more than one place, you're probably making s different error. In other news: I also would not recommend using SPs unless you absolutely positively have no alternative. They buy you virtually nothing compared to direct (but parameterized) sql. — Marc Gravell, Dec 21 '13 at 17:49

score 7 · Accepted Answer · answered Dec 21 '13 at 15:24

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

score 6 · Answer 2 · answered Dec 21 '13 at 15:17

6

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.

answered Dec 21 '13 at 15:17

Thomas Weller

11,631
3
26
34

I agree. Nevertheless, I like the idea of Priyank (hope it's ok to use names) – Rotem Orbach Dec 21 '13 at 16:01

Sklivvz · Answer 3 · 2013-12-22T00:29:49.823

You can wrap the calls in a simple gateway class:

public static class StoredProcedures
{
    public static DataSet GetKeyTables()
    {
        return SqlDBHelper.ExecuteMultiSelectCommand(
                "Sp_Get_Key_Tables", 
                CommandType.StoredProcedure);
    }

    public static DataSet GetFoobars()
    {
        return SqlDBHelper.ExecuteMultiSelectCommand(
                "Sp_Get_Foobars", 
                CommandType.StoredProcedure);
   }
}

Alternatively you can have POCOs that know how to interact with the database:

public class KeyTable
{
   public int Id { get; set; }
   // whatever data you need

   public static List<KeyTable> GetKeyTables
   {
      var ds = SqlDBHelper.ExecuteMultiSelectCommand(
                "Sp_Get_Key_Tables",
                CommandType.StoredProcedure);

      foreach (var dr in ds.Tables[0].Rows)
      {
          // build the POCOs using the DataSet
      }
   } 
}

The advantage of this is that not only the SP name is kept in a unique place, but also the logic of how to extract data out of the dataset is in the same place.

-1 for `DataSet ds = new DataSet(); ds = new value;` – John Saunders Dec 21 '13 at 23:37 — John Saunders, Dec 21 '13 at 23:37

score 1 · Answer 4 · answered Dec 21 '13 at 15:24

1

I don't see a huge issue with what you are doing. You would need to store the SP name somewhere, so either in the query or in another config or helper function.

Depending on the specification, I tend towards a repository for CRUD operations, so I know all data access, including any SP calls, are in the ISomeRepository implementation.

answered Dec 21 '13 at 15:24

Chris W

1,792
15
32

I conquer. I have such a repository - SqlDBHelper – Rotem Orbach Dec 21 '13 at 16:03

score 0 · Answer 5 · edited May 23 '17 at 12:31

When I work with stored procedures in C# I follow the following rules.

Every stored procedure is used only once in the code so that I have only one place to update. I do hate Magic strings and avoid them but stored procedures are used in Data Access Layer only once (e.g. Repositories, ReadQueries, etc).
For CRUD operations the pattern "sp_{Create/Read/Update/Delete}{EntityName}" is used.

If you want to have single place with all your stored procedures, you can create a static class with logic to create stored procedure's names.

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

5 Answers5