Database Connection

[Guest]

Hello, Newsgroupians:

Recently I asked a question regarding database connections and how to
properly close the connection upon the object going out of scope. After much
research, I've come up with a solution -- I believe -- should work, but
doesn't work. I'd like to ask for your continued feedback.

I create a class that wraps the DBConnection. Here's a small sample...

class CDB : IDispose
{
...
protected System.Data.Common.DBConnection m_conn = null;

public CDB()
{
...
this.m_conn.Open();
System.GC.SuppressFinalize(this.m_conn); // IMPORTANT
}
public Disconnect()
{
if (this.m_conn.State == System.Data.ConnectionState.Open)
{
this.m_conn.Close();
}
}
...
}

Now for my Dispose() and destructor, which is a Finalize() method, I have
the following...

~CDB()
{
this.Disconnect();
this.m_conn.Dispose();
}

public void Dispose()
{
this.Disconnect();
this.m_conn.Dispose();
System.GC.SuppressFinalize(this);
}


Now, when I create an instance of my wrapper, it can connect and perform
queries to the specified database. However, when the object goes out of
scope, it calls the destructor. In the destructor, it calls Disconnect(),
where I get an error on the line that is "this.m_conn.Close()" stating the
handle is not initialized, but I told the garbage collector to NOT call
Finalize() on the this.m_conn. I told it not to do this in the constructor
of my wrapped object. So why is the m_conn being GC when I told it not to?

Thank you all for your continued support and patience.


Trecius

 

[Jon Skeet [C# MVP]]

Recently I asked a question regarding database connections and how to
properly close the connection upon the object going out of scope. After much
research, I've come up with a solution -- I believe -- should work, but
doesn't work. I'd like to ask for your continued feedback.

One reason it shouldn't work is your claim here:

Now, when I create an instance of my wrapper, it can connect and perform
queries to the specified database. However, when the object goes out of
scope, it calls the destructor.

An object doesn't go out of scope: a variable does. That, in itself,
won't cause the finalizer to be called. The finalizer will be called
*some point* after the object is no longer referenced.

As to the rest of your post, I'd be interested in seeing a short but
complete example demonstrating the problem. I can't say I've ever seen
SuppressFinalize being called on anything other than "this" - it seems
like a bad idea to me.

 

[Guest]

This code is "overkill". All you need to do is make a call to Close or
Dispose on your connection instance as soon as you are finished using it, and
allow it to go back to the ADO.NET connection pool. Forget about all the
finalizer / destructor stuff.
Peter

 

[Alvin Bruney [MVP]]

Why not use the MS DAAB? It already works out all that stuff properly.

--
Regards,
Alvin Bruney
------------------------------------------------------
Shameless author plug
Excel Services for .NET - MS Press
Professional VSTO 2005 - Wrox/Wiley
OWC Black Book www.lulu.com/owc

 

[ramesh lal]

try this one it may help you..
http://infynet.wordpress.com/2010/09/24/database-connection-with-c/

Hello, Newsgroupians:

Recently I asked a question regarding database connections and how to
properly close the connection upon the object going out of scope. After much
research, I've come up with a solution -- I believe -- should work, but
doesn't work. I'd like to ask for your continued feedback.

I create a class that wraps the DBConnection. Here's a small sample...

class CDB : IDispose
{
...
protected System.Data.Common.DBConnection m_conn = null;

public CDB()
{
...
this.m_conn.Open();
System.GC.SuppressFinalize(this.m_conn); // IMPORTANT
}
public Disconnect()
{
if (this.m_conn.State == System.Data.ConnectionState.Open)
{
this.m_conn.Close();
}
}
...
}

Now for my Dispose() and destructor, which is a Finalize() method, I have
the following...

~CDB()
{
this.Disconnect();
this.m_conn.Dispose();
}

public void Dispose()
{
this.Disconnect();
this.m_conn.Dispose();
System.GC.SuppressFinalize(this);
}


Now, when I create an instance of my wrapper, it can connect and perform
queries to the specified database. However, when the object goes out of
scope, it calls the destructor. In the destructor, it calls Disconnect(),
where I get an error on the line that is "this.m_conn.Close()" stating the
handle is not initialized, but I told the garbage collector to NOT call
Finalize() on the this.m_conn. I told it not to do this in the constructor
of my wrapped object. So why is the m_conn being GC when I told it not to?

Thank you all for your continued support and patience.


Trecius

On Tuesday, August 21, 2007 2:23 PM Jon Skeet [C# MVP] wrote:

One reason it shouldn't work is your claim here:


An object doesn't go out of scope: a variable does. That, in itself,
won't cause the finalizer to be called. The finalizer will be called
*some point* after the object is no longer referenced.

As to the rest of your post, I'd be interested in seeing a short but
complete example demonstrating the problem. I can't say I've ever seen
SuppressFinalize being called on anything other than "this" - it seems
like a bad idea to me.

 

[Arne Vajhøj]

try this one it may help you..
http://infynet.wordpress.com/2010/09/24/database-connection-with-c/

Why reply to such an old post with a link to
an article that is horrible wrong??

qry = "insert into customer values('" + textBox1.Text + "','" +
textBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text + "')";

COmpletely open to SQL injection.

con = new SqlConnection(connection);
cmd = new SqlCommand(qry, con);
if (con.State == ConnectionState.Open)
con.Close();
else
con.Open();
cmd.ExecuteNonQuery();
con.Close();

That test if open is seriously flawed.

Arne

 

[Arne Vajhøj]

A more interesting question (to me ): why should we reply to any of
these posts that claim to be a reply to some very old post, but which
often have useless information, or just reiterate the original question,
or are complete non-sequiturs, and which always have filled the
"reply-to" field with the address of the poster to whom they are replying?

In other words, these posts are clearly some strange form of spam. I
don't know exactly their purpose, not being a spammer myself. But they
don't seem like legitimate contributes or questions.

I suspect that they are related to some kind of anti-spam logic that
limits the access a person has to posting based on how many apparently
"legitimate" posts they've made, but can't say for sure. Maybe instead
it's just a way for Egghead cafe to try to boost their links (I've
noticed the replies seem to respond to threads that were initiated there).

Regardless, it's pretty obvious to me that the posts are just noise. I
doubt that there's any point in responding to them. The person who
posted the message isn't reading, and most other people can easily see
how useless any information (if any) in the message is.

Based on the format I suspect that the posts comes from
a eggheadcafe web forum to usenet gateway.

I see no reason to suspect intentions to spam usenet. Maybe
intention to get their name out, but that is seen on usenet
as well.

I do not see any reason to suspect that they do not read
replies at egghead cafe.

The fact that the content is usually not so good is not surprising -
it must be a reasonable expectation that the average C# skills
among people not noting the date of the thread is lower than
the average among people that do note.

And I don't consider it unnecessary to warn about SQL injection
or wrong ways of opening and closing database connections.

Arne

 

[Arne Vajhøj]

The headers are not consistent with other posts that come from Egghead
Cafe. Even a site as poorly constructed as Egghead Cafe doesn't add a
"reply-to" for the _previous_ post's sender, and the headers are from
regular NNTP servers (e.g. Giganews, TeraNews, to name a couple), not
Egghead Cafe's.

That is a bit weird.

Also, if the people were replying as _users_ of Egghead Cafe, that would
not explain why the only threads involved are those that _originate_ at
Egghead Cafe. After all, web sites like that carry _all_ the threads,
not just the ones that were started there.

This thread did not start at egghead.

At least not if headers can be trusted.

The posts themselves are clearly not spam, agreed. And of course since I
have no specific knowledge of the purpose, I can't say for sure they are
related to spam activity. But it's hard to see what other purpose the
posts would have. And whenever I see odd messaging patterns on the
Internet, it generally turns out to be spam-related _somehow_.

If the content is not spam then the content is not spam.

I do.

I think I once got a reply from the guy replying to something very old.

But I can not remember if his post fit the modus operandi seen here.

From the pattern of the posts (there have been dozens, if not a
hundred, of these kinds of posts over the past several months…and we've
seen them to a lesser degree for much more time than that), it's clear
to me that the purpose of the posts is something unrelated to the actual
purpose of this newsgroup, and to the apparent content and intent of the
post.

There's an ulterior motive. I just don't have the specific information
as to what that motive is.

Could be.

But there is not many facts backing it.

Arne