none
Where is the best place to secure stored procedure names.. RRS feed

  • Question

  • Our datalayer consists of many methods that are merely specialized wrappers for Command Objects that in turn call stored procs. Each method provides for the passing of required parameters in the parameter string and the name of the stored procedure is contained (hard coded) in the method body. This is not unusual.

    The UI calls a service tier method. The service tier method provides the logic for gathereing and processing the parameters and the assembling a request from the user interface and then forwarding the request to the datalayer. The Data layer defines the command object, the name of the stored proc is hard coded into the method call, the parameters are wrapped up in a parameter collection and the request is handed of the the dtabase. I think this is pretty straight forward.

    As a next step in our code rehabilitation effort We would like to generalize the data layer so that we have only a few methods to handle special more complex requests and a generalized method that handles straight forward data requests. The problem we encounter is some of my colleagues don't like the idea, they think that we'll have to move the proc names up a tier into the service tier.

    One solution is to put the proc names in a string table or even in the database.

    To me it's pretty simple but some of my colleagues don't agree. Does anyone have any other suggestions  or opinions on how to approach this effort. There is a lot of code involved and a lot of old schoolers of which I am one. Any help would be apreciated.    

    Monday, September 24, 2012 8:01 PM

All replies

  • Hi Williammz,

    Welcome to MSDN Forum.

    I will involve senior workmates to research on this issue, it may need some time, thanks for your understanding.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, September 26, 2012 3:47 AM
    Moderator
  • As a next step in our code rehabilitation effort We would like to generalize the data layer so that we have only a few methods to handle special more complex requests and a generalized method that handles straight forward data requests. The problem we encounter is some of my colleagues don't like the idea, they think that we'll have to move the proc names up a tier into the service tier.


    It sounds to me like the current approach is the correct one at runtime and the proposed optimization might improve the design time experience, but make the performance, debugging, etc. worse.  Instead, look to generate the standard stored procedure invocations using T4 templates (http://msdn.microsoft.com/en-us/library/bb126445.aspx ) or some other code-gen tool.

    Remember a stored procedure name is like a method name in .NET.  You can store a list of method names and invoke them through reflection in .NET but it’s not good design to go replacing all your hard-coded method names by lookups and reflection.

    Thanks,

    Cathy Miller

    Wednesday, October 3, 2012 7:11 PM
    Moderator