Connection.State in VS2005

[Guest]

Hello,

I am just checking if VS2005/VB2005 automatically opens and closes
sqlConnections for you. I noticed that if I just invoke the connectin

conn = new SqlConnection
conn.ConnectionString = "..."
cmd.Connection = conn
....
dataAdapter.SelectCommand = cmd
dataAdapter.Fill(ds, "tbl1")
Console.Writeline(cmd.Connection.State.ToString)

'--the connection is always closed -- unless I specifically say conn.Open

I never say conn.Open or conn.Close or cmd.Connection.Close and I don't get
any Exceptions. I only get an exception if I say conn.Open without a
conn.Close and then try to re-open conn.Open - error says connection not
closed. So it looks like if I leave out conn.Open, then I don't need to
worry about conn.Close. Is this a feature of VS2005?

Thanks,
Rich

 

[Chris Jobson]

Hello,

I am just checking if VS2005/VB2005 automatically opens and closes
sqlConnections for you. I noticed that if I just invoke the connectin

conn = new SqlConnection
conn.ConnectionString = "..."
cmd.Connection = conn
...
dataAdapter.SelectCommand = cmd
dataAdapter.Fill(ds, "tbl1")
Console.Writeline(cmd.Connection.State.ToString)

'--the connection is always closed -- unless I specifically say conn.Open

I never say conn.Open or conn.Close or cmd.Connection.Close and I don't
get
any Exceptions. I only get an exception if I say conn.Open without a
conn.Close and then try to re-open conn.Open - error says connection not
closed. So it looks like if I leave out conn.Open, then I don't need to
worry about conn.Close. Is this a feature of VS2005?

I think that all the operations like dataAdapter.Fill preserve the state of
the connection, i.e. if the connection is already open they leave it open,
otherwise they open the connection to perform the operation then close it
again. Thus if you're doing everything through DataAdapters you never need
to either open or close connections. (By the way, this is a feature of
ADO.NET, not of VS or VB.)

Chris Jobson

 

[Guest]

Thanks for the explanation. Now it all makes sense. I am really starting
to like dataAdapters except for action stuff (insert/update/delete). I
understand that commandbuilder has a little more overhead than writing your
own action queries. Someone mentioned to me that a benefit of the
dataAdapter is in dealing with contention. But articles I have read suggest
that the default commands for DataAdapter that you get from the wizards or
commandbuilder are pretty inefficient compared to writing your own
commandText. I prefer to write my own code rather than have the wizard or
commandbuilder do it. Any thoughts on ways to deal with contention issues?

Thanks,
Rich

Rich

 

[Guest]

Rich,

I am going to assume that by contention you mean concurrency issues.

If you are using SQL Server and you have control over the table structures
then by far the easiest way to deal with concurrency issues is to use a
timestamp column.

Kerry Moorman

 

[Cor Ligthert [MVP]]

Rich,

In addition to the otherss, it is a feature of the DataAdapter that has
always been, be aware that if you open yourself a connection in advance than
you have to close it as well yourself. The reason is the next construction.

conn.open
da1.fill(a)
da2.fill(b)
da3.fill(c)
conn.close

This can give a slightly better performance.

I hope this helps,

Cor

 

[Cor Ligthert [MVP]]

Rich,

It is maybe a litle bit hug example, but in my idea is everything you ask in
it.

http://www.vb-tips.com/default.aspx?ID=8c3dc2d7-1232-4dd1-817e-22eaaebb2723

It is not using the try of the dataadapter, but let the errors be showed in
the errorstate of the datarows.

It is not correctiong the concurrency errors, that should be done conform
your or your organisations policy.

(be aware that we still are updating frequently this site so it can be
temporaly not be available)

I hope this helps,

Cor