CODEWITHSUNDEEP
c#.netsql-server
sharptooth
sharptooth

Reputation: 170479

How do I use SqlConnection and DataContext together?

I have a slightly overengineered code and need to use DataContext together with SqlConnection. This MSDN article says

If you provide an open connection, the DataContext will not close it. Therefore, do not instantiate a DataContext with an open connection unless you have a good reason to do this.

In my code I'll effectively have this:

using( var connection = new SqlConnection( connectionString ) ) {
    connection.Open();
    // some action
    using( var context = new DataContext( connection ) ) {
       //some action with the context object
    }
    //more action with the connection
}

To me it looks fine - the connection will get closed when the outer using collapses. Yet there's that phrasing that I shouldn't do so.

Is the snippet above using the two classes together correctly? Should I expect any problems?

Upvotes: 3

Views: 1935

Answers (2)

MiguelSlv
MiguelSlv

Reputation: 15103

Suppose you have this generated DataContext class:

public partial class MyDataContext: System.Data.Linq.DataContext
{
 ...
}

To use with a SqlConnection simple do this:

 using (var cnn = new SqlConnection("my connection string"))
 {
     cnn.Open();

     var dc= new MyDataContext(cnn);
     // enjoy
     ...
     cnn.Close();
 }

Upvotes: 0

Sean
Sean

Reputation: 3042

your intention should be to keep the connection living, and pass the connection to all the methods, open it at the beginning and close it at the end, in order to improve the proformance, it's fine, but looks cumbersome. and the "connection" will be passed around, even be referenced by the business logic layer.

Maybe you should find a better approach. :)

Upvotes: 3

Related Questions