ASP.NET MVC: Best Way To Call Stored Procedure

Question

I'm trying to decide which is the best way to call a stored procedure.

I'm new to ASP.NET MVC and I've been reading a lot about Linq to SQL and Entity Framework, as well as the Repository Pattern. To be honest, I'm having a hard time understanding the real differences between L2S and EF... but I want to make sure that what I'm building within my application is right.

For right now, I need to properly call stored procedures to: a) save some user information and get a response and, b) grab some inforation for a catalog of products.

So far, I've created a Linq to SQL .dbml file, selected the sotred procedure from the Server Explorer and dragged that instance into the .dbml. I'm currently calling the Stored Procedure like so:

MyLinqModel _db = new MyLinqModel();
_db.MyStoredProcedure(args);

I know there's got to be more involved... plus I'm doing this within my controller, which I understand to be not a good practice.

Can someone recognize what my issues are here?

Answer 1

LINQ and EF are probably overkill if all you're trying to do is call a stored proc.

I use Enterprise Library, but ADO.NET will also work fine.

See this tutorial.

Briefly (shamelessly copied-and-pasted from the referenced article):

    SqlConnection conn = null;
    SqlDataReader rdr  = null;

    // typically obtained from user
    // input, but we take a short cut
    string custId = "FURIB";

    Console.WriteLine("\nCustomer Order History:\n");

        // create and open a connection object
        conn = new SqlConnection("Server=(local);DataBase=Northwind; Integrated Security=SSPI");
        conn.Open();

        // 1.  create a command object identifying
        //     the stored procedure
        SqlCommand cmd  = new SqlCommand(
            "CustOrderHist", conn);

        // 2. set the command object so it knows
        //    to execute a stored procedure
        cmd.CommandType = CommandType.StoredProcedure;

        // 3. add parameter to command, which
        //    will be passed to the stored procedure
        cmd.Parameters.Add(
            new SqlParameter("@CustomerID", custId));

        // execute the command
        rdr = cmd.ExecuteReader();

        // iterate through results, printing each to console
        while (rdr.Read())
        {
            Console.WriteLine(
                "Product: {0,-35} Total: {1,2}",
                rdr["ProductName"],
                rdr["Total"]);
        }
    }

Update

I missed the part where you said that you were doing this in your controller.

No, that's not the right way to do this.

Your controller should really only be involved with orchestrating view construction. Create a separate class library, called "Data Access Layer" or something less generic, and create a class that handles calling your stored procs, creating objects from the results, etc. There are many opinions on how this should be handled, but perhaps the most common is:

View
|
Controller
|
Business Logic
|
Data Access Layer
   |--- SQL (Stored procs)
           -Tables
           -Views
           -etc.
   |--- Alternate data sources
           -Web services
           -Text/XML files
           -blah blah blah.

MSDN has a decent tutorial on the topic.

Answer 2

Try this:

Read:

var authors = context.Database.SqlQuery<Author>("usp_GetAuthorByName @AuthorName", 
new SqlParameter("@AuthorName", "author"));

Update:

var affectedRows = context.Database.ExecuteSqlCommand
("usp_CreateAuthor @AuthorName = {0}, @Email= {1}", 
"author", "email");

From this link: http://www.dotnetthoughts.net/how-to-execute-a-stored-procedure-with-entity-framework-code-first/

And I would go with the framework David Lively mentioned, instead of having the routines in the controller. Simply pass the results back as IEnumerable<blah> from a function in a separate repository class for an edit, pass a boolean back for if the update succeeded for an update.

Answer 3

LINQ to SQL and ADO.NET EF attach read stored procs to the data/object context class that you use to go against its various entities. For create, update, and delete, you can create a proc that maps the properties of an entity that the model generates, and using the entity mapping window (forget the exact name right now), you can map an entities fields with the proc parameters. So, say you have a Customers table, EF generates a Customers Entity, and you can map the proc parameters to the properties of the Customer entity when attempting to update/insert/delete.

Now, you can map a CUD proc to a function, but I don't know all the repercussions; I like the way I just mentioned the best.

HTH.