How to use sql server 2005 stored procedures in asp.net mvc?

Question

I have just started a new web application with asp.net mvc and sql server 2005... Thus far in my webform applications i used ADO.NET... I want the same in my asp.net MVC apllication...

I have a database in sql server 2005 with all the stored procedures... Now i want to use those stored procedures in my MVC application... I dont use any ORM... I want to use ADO.NET..

Any sample controller that calls a model which uses a stored procedure and returns a Dataset to the controller and then the controller to View to display records... Any suggestions...

Answer 1

I would recommenced using a service or a repository that is responsible for populating the model with the data from the stored procedure that the controller sends to the view. I'm not sure on the reasoning for avoiding ORMs with the desire to use ADO.NET. The reality is entity framework, Linq2Sql, SubSoncic, and NHibernate are very proven and reliable.

Here is a quick sample I put together... It uses the SqlConnection and SqlCommand to call the stored procedure... Put this code in a separate class that the controller calls to get the Model.

    public BlogEntry GetBlogEntry(int blogId)
    {
        SqlConnection sqlConnection = new SqlConnection();
        sqlConnection.ConnectionString = MyConnectionString;
        SqlCommand sqlCommand = new SqlCommand();
        sqlCommand.CommandText = "mySproc";
        sqlCommand.Connection = sqlConnection;
        sqlConnection.Open();
        var reader = sqlCommand.ExecuteReader();.
        var blogEntry = new BlogEntry();

        while (reader.Read())
        {
            //do something to fill your model...
        }
        return blogEntry;   
    }