Database update with auto connection open/close

[Maziar Aflatoun]

Hi,

To update the database I first open a connection, update and then I close
it. Is there way to do this without opening the connection first? Such as
using the DataAdaptor (which manages the connection automatically)? Is that
efficient?

Thanks
Maz.

 

[Brock Allen]

DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select * from authors", "server=server;database=pubs;trusted_connection==yes;");
da.Fill(ds);

This opens and closes the connection for you. Is it better? It depens what
you do with the data and how long the DataSet lives.

-Brock
DevelopMentor
http://staff.develop.com/ballen

 

[tom pester]

As you say, the data-adaptor just does this in the background. There is no
way to prevent the opining of a connection of course so open it quick and
close it asap.
But you can use a library to make some things easier. Check out this article
on the Data Access Application Block :

http://aspnet.4guysfromrolla.com/articles/070203-1.aspx

Here is an extract :

=====
string strSql = "select * from products where categoryid = 1";
string strConnTxt = "Server=(local);Database=Northwind;Integrated Security=True;";

DataGrid4.DataSource = SqlHelper.ExecuteReader(strConnTxt, CommandType.Text,
strSql);
DataGrid4.DataBind();
=====

It may be strange that you dont see a connection closed somewhere and it
should ring an alarmbell.
What happens is that the datareader gets its date from the command object
with CommandBehavior.CloseConnection option so that the connection gets closed
as soon as the datareader is closed.
And the DataGrid4.DataBind() does close the datareader thus after that statement
the connection is closed.

It's bad practice I think to rely on such side effects just to let you know.


Cheers,
Tom Pester