Why Using during Sql Server Connection

[munishk]

When we create database connection in C# lets say to SQl server, why it is recommended to use connection statements within using? I know using makes CLR reclaims memory when control is coming outside of using by calling Dispose(). But SQL server connection should be a managed code and using is usually used for unmanaged code. I mean we create SQL Server connection objects from .NET libraries, so why is unmanaged? Really looking for answer.

Favourite quote: In youth we learn, In age we understand.

[Lost User]

munishk wrote:

using is usually used for unmanaged code.

Hehe, as you said yourself, it's not limited to unmanaged code.

munishk wrote:

I mean we create SQL Server connection objects from .NET libraries, so why is unmanaged?

Unmanaged code often holds handles, and other stuff that needs to get rid of "exactly once". A library is loaded once, and unloaded once. For a SqlConnection it adds other benefits; not only does it close the connection once the object gets disposed (something that should be done "once" again, in a destructor-type method), it also clears the largest fields (and disposes embedded disposables), helping the GC.

Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]

[Lost User]

munishk wrote:

SQL server connection should be a managed code

Why should the connection be managed code?

Why is common sense not common? Never argue with an idiot. They will drag you down to their level where they are an expert. Sometimes it takes a lot of work to be lazy Please stand in front of my pistol, smile and wait for the flash - JSOP 2012

[OriginalGriff]

using is not just for unmanaged code - in fact I almost never write unmanaged code, and I use using all the time. What is does is ensure that the object created in the using block header is Disposed at the end, exactly as if it had been written as

MyObject mo = new MyObject();
...
mo.Dispose():

In fact, a using block is just a syntactic sugar for just that, but with the added advantage that it terminates the scope of the variable as well, so you can't accidentally use the Disposed object. Why do I use it? It's clean, it's clear, and it de-scopes a variable when I can't use it again. Why is it important to Dispose managed objects? Because some of them hold resources: for example, Bitmaps, Files and so forth hang on to resources such as handles and file access locks until the object is disposed - in the case of a Bitmap it is not at all obvious that:

Image i = Image.FromFile(path);

puts a file access lock on the image source file until the image is Disposed. So if you use this to load an image, let the user modify it and then try to save it again, the file may or may not still be in use...annoying. Surrounding the Image creating with a using block removes this problem. If an object supports IDisposable, then Dispose should be called on it. And a using block is the easiest, cleanest way to do that.

Ideological Purity is no substitute for being able to stick your thumb down a pipe to stop the water

[jschell]

Eddy Vluggen wrote:

For a SqlConnection it adds other benefits; not only does it close the connection...

Not generally it doesn't. It returns the connection to the pool. If the pool is configured explicitly (non-default settings) then it will.

[Luc Pattyn]

:thumbsup:

Luc Pattyn [My Articles] Nil Volentibus Arduum

[munishk]

But can we say that creating sqlconnection is unmanaged resource? This is even stated in Albhahari book too. I am still looking for clear answers.

Favourite quote: In youth we learn, In age we understand.

[jschell]

munishk wrote:

But can we say that creating sqlconnection is unmanaged resource?

No you can't say that because it is managed class. At some point that class or some other part of the .Net API (not visible) is interacting with unmanaged resources. Just as many other managed classes do. However that has nothing to do with your use of it.

[PIEBALDconsult]

There's got to be unmanaged objects deep in there somewhere -- named pipes, network sockets, I don't know.

[PIEBALDconsult]

munishk wrote:

why it is recommended to use connection statements within using?

It isn't; I don't do it that way anyway. I dislike code that keeps instantiating and disposing Connections and Commands over and over again for no good reason. I prefer to instantiate each once and hold onto it for the duration -- opening and closing the Connection as needed, setting the CommandText, etc. But that's because of the type of systems I write; your systems may not benefit from it. Additionally, I wrap Connections and Commands in my own classes which implement IDisposable, so when it makes sense to (mostly console utilities) I instantiate my class with a using statement:

using
(
PIEBALD.Data.IDatabase db

new PIEBLAD.Data.SqlServerDatabase 
(
    ... 
) 

)
{
...
}