BLToolkit with Linq -- Why is a `using` statement required?

Question

I light of recent (extreme) performance issues with SubSonic 3, we are looking to migrate ORMs, preferably rewriting as little code as possible(which is mostly Linq).

So I am looking at BLToolkit. One of the major differences I see between SubSonic and BLToolkit though is that BLToolkit always requires a using statement. For instance:

static void SingleTableTest()
{
    using (var db = new NorthwindDB()) //This
    {
        var query =
            from e in db.Employee
            where e.EmployeeID > 5
            orderby e.LastName, e.FirstName
            select e;

        foreach (var employee in query)
        {
            Console.WriteLine("{0} {1}, {2}", employee.EmployeeID, employee.LastName, employee.FirstName);
        }
    }
}

What exactly does this do? When you create a new instance of the database, does it create a new connection? Would it be reasonable to "wrap" this into a static class so that I could do from anywhere var q=from e in Database.Employee ...? What repercussions would this have in the context of a web application?

It has to do with the idea that the connections should be scoped to the smallest unit of work possible. I'm not familiar with this framework in particular, but it's likely that they are managing your connections for you much like EF or LinqToSql. — Brian Dishaw, Jun 28 '11 at 16:21
According to my view of the code, a new connection is made for each `new db` statement. I looked in SubSonic and they used a homemade "SharedConnectionScope" so that connections were open and closed automatically. — Earlz, Jun 28 '11 at 16:28
This may be creating a new instance of a database-connection object each time, but it shouldn't be creating a brand-new network connection to the database server. .NET does connection pooling by default. — Joe White, Jun 28 '11 at 18:13
@Joe so it's not a huge thing to worry about then? That is, making multiple database objects per HTTP request? (of course, using the same connection when possible is preferred though) — Earlz, Jun 28 '11 at 18:48
Connection pooling will take care of using the same network connection when possible. But you need your `using` block so that the connection pool knows you're done with the first connection object (and the network connection it encapsulates) and it's ready to be reused. — Joe White, Jun 28 '11 at 19:20

IT. · Accepted Answer · 2011-06-29T13:18:06.933

4

I guess the NorthwindDB class in your example is based on DbManager. DbManager is a wrapper around Connection and behaves like a connection. You should try another class - DataContext. It's designed exactly for your scenario.

edited Jun 29 '11 at 13:18

answered Jun 29 '11 at 02:29

IT.

859
4
11

Is there a way to make the T4 templates(the ones included with BLtoolkit) generate an extended DataContext similar to how the DbManager is extended? – Earlz Jun 29 '11 at 20:11
In your template just add BaseDataContextClass = "DataContext"; before LoadMetadata(); / GenerateModel(); – IT. Jun 30 '11 at 06:09

score 0 · Answer 2 · answered Jun 28 '11 at 19:58

I don't know BLToolkit, but from your comment that said you wanted to know if it was possible to just use one object per HTTP request, with something like Entity Framework you can. Instead of a using statement, you create a db object in global.asax's Application_BeginRequest event. You dispose of it in Application_EndRequest. You can store the object in HttpContext.Current.Items, which is a handy per-request collection.

As I said I don't know if that applies to BLToolkit specifically because I don't know anything about it, but hopefully its enough to point you in the right direction. :)

Well, it looks like you can do that.. maybe. The problem is only if it keeps it's SqlReader's open. I may just have better luck attempting it and seeing if anything breaks — Earlz, Jun 28 '11 at 21:07

BLToolkit with Linq -- Why is a `using` statement required?

2 Answers2