Calling stored procedures

Question

I have a c# application that interfaces with the database only through stored procedures. I have tried various techniques for calling stored procedures. At the root is the SqlCommand class, however I would like to achieve several things:

• make the interface between c# and sql smoother, so that procedure calls look more like c# function calls
• have an easy way to determine whether a given stored procedure is called anywhere in code.
• make the creation of a procedure call quick and easy.

I have explored various avenues. In one, I had a project that with its namespace structure mirrored the name structure of stored procedures, that way I could generate the name of the stored procedure from the name of the class, and I could tell whether a given stored procedure was in use by fining it in the namespace tree. What are some other experiences?

Answer 1

You should try LINQ to SQL.

Answer 2

When stored procedures are the interface to the database, I tend to wrap them in classes which reflect the problem domain, so that most of the application code is using these objects and not calling stored procedures, and not even knowing about the stored procedures or the database connection. The application objects, typically play amongst themselves.

I think it's a mistake to mirror the SPs in your application, as, typically, your relational model is not 1-1 with your application domain object model.

For example, typically I do not have application objects which represent link tables or other artifacts of database design and normalization. Those are collections of objects either contained in or returned by other objects.

A lot is made of the impedance mismatch, but I think it's horses for courses - let databases do what they are good at and OO models do what they are good at.

Answer 3

Have you looked into using the Enterprise Library from MS? It allows you to easily call stored procedures. I generally setup a class per database that is only for calling these stored procs. You can then have something similar to this (sorry it's vb.net and not c#):

Public Shared Function GetOrg(ByVal OrgID As Integer) As System.Data.DataSet
    Return db.ExecuteDataSet("dbo.cp_GetOrg", OrgID)
End Function

Where db is defined as:

Dim db As Microsoft.Practices.EnterpriseLibrary.Data.Database = DatabaseFactory.CreateDatabase()

You then have this one function that is used to call the stored procedure. You can then search your code for this one function.

Answer 4

the simplest solution for what you want [and i'm not saying that it is better or worse than the other solutions] is to create a dataset and drag the stored procedures from the server explorer onto the dataset designer surface. This will create methods in the adapter that you can call and check for references.

Answer 5

When building my current product, one of the tools that I very much wanted to implement was a database class (like DatabaseFactory - only I didn't care for that one) that would simplify my development and remove some of the "gotchas." Within that class, I wanted to be able to call stored procedures as true C# functions using a function-to-sproc mapping like this:

public int Call_MySproc(int paramOne, bool paramTwo, ref int outputParam)

{

...parameter handling and sproc call here

}

The biggest issue you face when trying to do this, however, lies in the work needed to create C# functions that implement the sproc calls. Fortunately, it is easy to create a code generator to do this in T-SQL. I started with one created originally by Paul McKenzie and then modified it in various ways to generate C# code as I wanted it.

You can either Google Paul McKenzie and look for his original code generator or, if you'd like to write to me at mark -at- BSDIWeb.com, I'll bundle up the source for my SQL class library and the associated sproc code generator and place it on our web site. If I get a request or two, I'll post it and then come back and edit this response to point others to the source as well.

Answer 6

Although they aren't very fashionable, we use Typed DataSets as a front-end to all of our stored procedures.

Answer 7

Microsoft's new Entity Framework provides just what you're asking for. EF is normally used to create proxy classes for database objects, but one thing a lot of people don't realize is that it also creates proxy methods for stored procedures (auto-generated, of course). This allows you to use your SPs just as though they were regular method calls.

Check it out!