CODEWITHSUNDEEP
c#asp.net-mvcormsqlconnection
aherrick
aherrick

Reputation: 20169

ASP.NET MVC Manage SQLConnection with Dapper

I am giving the new Dapper Micro ORM released by Stack Overflow/Sam Saffron a quick go using MVC. I'm wondering what is the simplest way to manage a SQLConnection object inside my controller? I am doing something simple like this just to spin through some data and test out Dapper, but is it idea to be opening/closing the connection like so?

public class HomeController : Controller
{
    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ajh"].ConnectionString);

    public HomeController()
    {
    }

    public ActionResult Index()
    {
        // get me all comments
        conn.Open();
        var comments = conn.ExecuteMapperQuery<Comment>("select * from Comment");
        conn.Close();

        return View(comments);
    }
}

Upvotes: 7

Views: 5286

Answers (2)

LukeH
LukeH

Reputation: 269368

Just create, open and close the connection as locally as possible:

public class HomeController : Controller
{
    public HomeController()
    {
    }

    public ActionResult Index()
    {
        List<Comment> comments;
        using (var conn = new SqlConnection(/* ... */))
        {
            conn.Open();
            comments = conn.ExecuteMapperQuery<Comment>("select * from Comment");
        }
        return View(comments);
    }
}

Although it's best practice to avoid direct data-access in your controllers. Bury your data-access methods inside a CommentsService class or similar and call that from your controller.

Upvotes: 9

Joel Martinez
Joel Martinez

Reputation: 47749

I've never used it, but of var comments is deferred in its execution, then you'll have a problem and will have to use something like .ToList to fully enumerate the results before closing the connection. Otherwise, if .ExecuteMapperQuery fully enumerates the results before returning them you will be fine

Upvotes: 0

Related Questions