User Controls and ADO Connections

I have 3 User Control .ascx files that are included in a base .aspx file.

In each ascx file, as well as the base aspx file, I make ADO.Net calls that
require a datatbase connection.

It appears that I have to open and close the connection in each file (i.e. I
can not reuse the open connection between all 4 pages)? This means that I
must open and close the connection 4 times when servering one page (base aspx
and 3 ascx files).

Is there any way that I can accomplish this without so opening and closing
the connection so many times?

Wouldn't this become a scalability problem if I continually have to open
and close connections multiple times on each page?

Cor
Thu Oct 27 09:32:10 CDT 2005

Paul,

Why do you do this database handling in your controls

In my opinion should you seperate that, just create a seperate component for
it.

Open that as new Item in your solution explorer and drag by instance from
your toolbox a connection for that.

This is all the information I can reply to you, given the information you
have given to us.

I hope this helps anyway.

Cor

"Paul" <Paul@discussions.microsoft.com> schreef in bericht
news:CF238E21-6C69-4BDA-A28E-112E0A2606AF@microsoft.com...
>I have 3 User Control .ascx files that are included in a base .aspx file.
>
> In each ascx file, as well as the base aspx file, I make ADO.Net calls
> that
> require a datatbase connection.
>
> It appears that I have to open and close the connection in each file (i.e.
> I
> can not reuse the open connection between all 4 pages)? This means that I
> must open and close the connection 4 times when servering one page (base
> aspx
> and 3 ascx files).
>
> Is there any way that I can accomplish this without so opening and closing
> the connection so many times?
>
> Wouldn't this become a scalability problem if I continually have to open
> and close connections multiple times on each page?
>

KerryMoorman
Thu Oct 27 09:48:06 CDT 2005

Paul,

Just the opposite. It will become a scalabiliy problem if you don't open and
close connections each time you use them.

Plus, with connection pooling, one connection is getting reused several
times behind the scenes.

Kerry Moorman


"Paul" wrote:

>
> Wouldn't this become a scalability problem if I continually have to open
> and close connections multiple times on each page?
>

Cor
Thu Oct 27 09:52:38 CDT 2005

Kerry,

>
> Just the opposite. It will become a scalabiliy problem if you don't open
> and
> close connections each time you use them.
>
However, if you have two controls on your form which are using the same part
of the data, than it is in my opinion a waste of time to get them 2 times.

Maybe do I misunderstand the problem.

Cor

KerryMoorman
Thu Oct 27 11:40:11 CDT 2005

Cor,

Yes, getting the data from the database once is the way to go.

However, if for whatever reason, the data is retrieved more than once, then
a connection needs to be opened and closed for each database access.

Hanging on to an open connection so that it can be used repeatedly is the
scalability problem that I meant.

Kerry Moorman


"Cor Ligthert [MVP]" wrote:

> Kerry,
>
> However, if you have two controls on your form which are using the same part
> of the data, than it is in my opinion a waste of time to get them 2 times.
>
> Maybe do I misunderstand the problem.
>
> Cor
>
>
>

Cor
Thu Oct 27 12:04:04 CDT 2005

Kerry,

> However, if for whatever reason, the data is retrieved more than once,
> then
> a connection needs to be opened and closed for each database access.
>
> Hanging on to an open connection so that it can be used repeatedly is the
> scalability problem that I meant.
>

You will never find it written in another way here by me. (With the
exception from an Access Database, in that you can use it to lock the Access
Database on its place)

I knew that you did mean that, my message was to overcome confusions by the
OP.

Cor

Paul
Thu Oct 27 14:39:03 CDT 2005

The reason that I do database calls in the UserControl is because I need to
display data (retrieved from the database) in each of those controls.

I do not understand what you mean by create a seperate component?




"Cor Ligthert [MVP]" wrote:

> Paul,
>
> Why do you do this database handling in your controls
>
> In my opinion should you seperate that, just create a seperate component for
> it.
>
> Open that as new Item in your solution explorer and drag by instance from
> your toolbox a connection for that.
>
> This is all the information I can reply to you, given the information you
> have given to us.
>
> I hope this helps anyway.
>
> Cor
>
> "Paul" <Paul@discussions.microsoft.com> schreef in bericht
> news:CF238E21-6C69-4BDA-A28E-112E0A2606AF@microsoft.com...
> >I have 3 User Control .ascx files that are included in a base .aspx file.
> >
> > In each ascx file, as well as the base aspx file, I make ADO.Net calls
> > that
> > require a datatbase connection.
> >
> > It appears that I have to open and close the connection in each file (i.e.
> > I
> > can not reuse the open connection between all 4 pages)? This means that I
> > must open and close the connection 4 times when servering one page (base
> > aspx
> > and 3 ascx files).
> >
> > Is there any way that I can accomplish this without so opening and closing
> > the connection so many times?
> >
> > Wouldn't this become a scalability problem if I continually have to open
> > and close connections multiple times on each page?
> >
>
>
>

Paul
Thu Oct 27 14:43:11 CDT 2005

I understand the importance of closing database connections.

What I am struggling with is the need to open and close them multiple times
(within multiple UserControls) on the same page.

Maybe it is because in Classic ASP, I used to use include files where I
would open the connection once at the beginning of the base page, then make
database calls in multiple include files (not having to reopen or close the
connection), then close the connection at the end of the base page.

So I only had to open/close the connection once per page.

I have read that opening/closing the db connection is an "expensive"
transaction that should not be done often (only once per page). But now I am
doing it multiple times per page and that concerns me.

It comes down to the difference be Classic ASP include files to user
controls I guess.


"Kerry Moorman" wrote:

> Paul,
>
> Just the opposite. It will become a scalabiliy problem if you don't open and
> close connections each time you use them.
>
> Plus, with connection pooling, one connection is getting reused several
> times behind the scenes.
>
> Kerry Moorman
>
>
> "Paul" wrote:
>
> >
> > Wouldn't this become a scalability problem if I continually have to open
> > and close connections multiple times on each page?
> >

Cor
Thu Oct 27 15:11:38 CDT 2005

Paul,

You can of course as well use for your data by instance a dataset/datatable.

Than you can get the data by making in your usercontrol a property for that
datatable as what it is everywhere DataSource.

Just a thought,

Cor