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

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
Fri Mar 16 19:45:16 CDT 2007

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)
-----------------------------------------------------------------------------------------------------------------------

"Microsoft" <robinne123@newsgroups.nospam> wrote in message
news:OkBEWz$ZHHA.208@TK2MSFTNGP05.phx.gbl...
>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.
>
>
>

Robin
Fri Mar 16 20:35:47 CDT 2007

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.

"William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message
news:%2389BK2CaHHA.4552@TK2MSFTNGP05.phx.gbl...
> 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)
> -----------------------------------------------------------------------------------------------------------------------
>
> "Microsoft" <robinne123@newsgroups.nospam> wrote in message
> news:OkBEWz$ZHHA.208@TK2MSFTNGP05.phx.gbl...
>>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.
>>
>>
>>
>
>

Cor
Sun Mar 18 07:00:49 CDT 2007

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


"Microsoft" <robinne123@newsgroups.nospam> schreef in bericht
news:OkBEWz$ZHHA.208@TK2MSFTNGP05.phx.gbl...
>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.
>
>
>

v-wywang
Mon Mar 19 07:10:22 CDT 2007

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
Mon Mar 19 13:13:26 CDT 2007

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"" <v-wywang@online.microsoft.com> wrote in message
news:MkRjX%23haHHA.4084@TK2MSFTNGHUB02.phx.gbl...
> 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
>

v-wywang
Tue Mar 20 06:00:07 CDT 2007

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