dataset designer - building data access layer - how to close connect on exception

[Microsoft]

I have been going through some tutorials on using the new dataset designer
in Visual Studio 2005 to build dataadapters which include inserts, updates,
and deletes...so I don't have to code it all by hand. This tutorial is
tutorial #1 located here
http://www.asp.net/learn/dataaccess/default.aspx?tabid=63.

When I call my dataadapter insert statement, and my insert is invalid (e.g.
wrong permissions on my database or trying to insert null value into column
that requires value input), I am able to log the exception that is thrown,
but my database then 'chokes' because it seems the connection did not get
closed properly. The databsae thinks it still has a connection to it.

Since I have not hand coded the connection, I don't know how to close it
when an exception is thrown (I don't think have access to the connection
object). Normally I would close a connection in my finally statement.

Please advise. Thanks.

 

[William \(Bill\) Vaughn]

Please change your "from" address--clearly you're not from "Microsoft"

Ah, that's the rub. When you create a DataReader (as I expect you're doing),
you must set the CommandBehavior.CloseConnection option so that when some
other routine finishes with the DataReader it can be closed--even in an
Exception handler.

See Chapter 6.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[Robin]

Ooops, nope, I'm not Microsoft; that was bad setup of my newsgroup
account...

Anyway, I am looking for specific information about working with the
dataset designer; I don't have access to the Command objects, that I can
see.

 

[Cor Ligthert [MVP]]

Microsoft,

I think that most of us use half the words as is written in that sample.
If you can find where you have to close than you can write

If connection not is nothing then conn.close
if (connection !=null) { conn.close }

I hate that one, but here it is maybe the solution.

By the way, this week I got Bill's book on my desk (work). What was not
expected by me, "He uses in my idea not a quarter of the words in that
article and tells more".

I seldom use those books because they are often so far from AdoNet and
written by typical SQL writters, but I am almost sure that there will be
situations that I will go read Bills book.

Cor

 

[WenYuan Wang]

Hi Robin,
Thanks for Bill and Cor's reply.

I'm sorry, I don't think you have to close the connection if TableAdatper
failed to execute update command. TableAdatper will automatically close the
connection. ( unless you call conection.open() method)

For example:
DataSet1 ds = new DataSet1();
DataSet1TableAdapters.Table1TableAdapter t1ta = new
DataSet1TableAdapters.Table1TableAdapter();
//t1ta.Connection.Open();
t1ta.Fill(ds.Table1);
try
{
//Modify your dataset here.
t1ta.Update(ds.Table1);
}
catch (System.Exception ex)
{
MessageBox.Show(t1ta.Connection.State.ToString());
//the state of connection is closed, unless you uncomment
"t1ta.Connection.Open();"
}

Additionally, I'm afraid there is no such option in Typed Dataset to change
this behavior. However, you can close the connection in Catch block if you
feel this is necessary.
Such as
catch (System.Exception ex)
{
if(t1ta.Connection.State!=ConnectionState.Closed)
t1ta.Connection.Close();
}
Please feel free to update here if you have anything unclear or any futher
issue. I'm glad to assist you.
Have a great day,
Sincerely,
Wen Yuan

 

[robin]

Yes, this is what I was looking for. Once I increased the scope of the
connection modifier on the table adapter, I was able to access
myTableAdapter.Connection. Yes, it is closed after an exception.

Thank you.

 

[WenYuan Wang]

Hi Robin,

I'm glad to hear my suggestion is helpful to you.
If you have any further issue please feel free to update here, too.

Have a great day,
Sincerely,
Wen Yuan