Hello people!



I am eager to see just how fast the SQL Server specific namespace is in 

the .NET framework when compared to the older ADODB COM components. Thus 

I decided to write a simple performance test where ASPx and ASP was 

supposed to compete.



Dual PII-350Mhz, 256Mb, 100Gb UW and Win2K Server/SQL Server 7.0



This is a long letter but I think it covers some questions a lot of 

current and prospective db-programmers ask: Which is faster, when and 

why - and how can I take advantage of each?!



I have a question in the bottom of this mail, so please take your time 

to read this throroughly and try to answer my question if possible!



-- =3D --



Test #1:

Pseudocode:

a. Do a loop with 1000 iterations

b. Open db-connection to SQL Server

c. Close db-connection

d. Loop next

e. Calculate time



Result:

ASP+: 8.0 secs.

ASP: 0.64 secs.



Comments on test #1:

This is very strange. I know ASP is really good at communicating with 

the ODBC layer, but this is scarry. The only answer I can come up with 

is, the ASP Connection object isn't opening a 'real' connection to the 

SQL Server - just a 'proxy' of that connection. Once the first data 

starts to flow to/from through that proxy, ASP will ask for a 'real' 

connection.



The other answer is, that the .NET framework haven't incorporated proper 

connection-pooling for the SQL Server namespace yet (which I really hope 

- look at those numbers!). Thus, the ASPx example opens a 'real' 

database-connection to the SQL Server, which naturally is heavier (the 

below test kinda supports that theory).



No-one would - under all circumstances - open/close a connection without 

doing anything with it first! Only me, I guess! :)



-- =3D --



Test #2:

Pseudocode:

a. Do a loop with 1000 iterations

b. Open db-connection to SQL Server

c. Create a recordset and perform a simple select.

d. Loop through all records in each recordset (no display)

e. Close current recordset

f. Close db-connection

g. Loop next

h. Calculate time



Result:

ASP+: 18.0 secs.

ASP: 18 secs.



Comments on test #2:

It clearly shows that the SQLDataReader is outperforming the ADODB 

Records by atleast 50%. Nice, but not as fast as one would want - we're 

talking SQL Server optimized code here .. come on! ASP is clearly 

getting a lot slower when it's going to do something with that 

connection, instead of just using the 'proxy' (or whatever).



-- =3D --



Test #3:



Please notice that I've left the opening/closing of the db-connection 

outside of the loop - just to see the difference. You'll like the 

outcome:



Pseudocode:

a. Open db-connection to SQL Server

b. Do a loop with 1000 iterations

c. Create a recordset and perform a simple select.

d. Loop through all records in each recordset (no display)

e. Close current recordset

f. Loop next

g. Close db-connection

h. Calculate time



Result:

ASP+: 4.0 secs.

ASP: 16 secs.



Comments on test #3:

The SQL Server namespace is obviously gaining a lot of performance once 

it got rid of the open/close connection code in each iteration. But, how 

come it's really that slow on it? I'd expected the SQL Server namespace 

to perform just as fast as the ASP, using the proxy method and only take 

a performance-hit once data starts to flow.



Question (and discussion):

When programming datalayer-components using the .NET framework and the 

specific SQL Server optimized namespace, it would come with a big 

performance-penalty if you were to open a db-connection in each and 

every function of your datalayer.



Unlike regular ASP - you can't (in ASPx) declare a set of global 

variables for your page (e.g. oConn =3D valid database-connection) 

because everything actually is a self-contained assembly. Thus, 

variables have assembly level scope.



Compared to regular ASP, I can't use the global variable holding the 

connection for the entire page (e. g. oConn) and use that very 

connection in an include-file or pagelet. I'll have to create my own 

connection in each and every part of the assembly.



As the above test shows, then you're actually making your application 

'N' times slower for each function you're calling in the code (provided 

those functions need database-connections), because each function would 

need a database-connection. The same goes for ASP, but you can still 

rely on the 'global' connection-variable. So, what do we do? We need to 

eliminate the overhead and look at a solution.



Theory:

I've given this a lot of thought and the only solution would be to make 

a 'rule', which says that the calling function should always provide a 

valid database-connection to the data-layer when it requests data. This 

would ensure multi-use of the same db-connection (when it's passed as an 

function-argument like the following):



(C#):

// Master function always providing a 'global' connection as 

function-argument

public void Master() {

    SQLConnection oConn =3D new SQLConnection

    // More oConn code here gives a valid connection

    string strPage =3D GetPages(oConn, 20, 10);

    string strColumn =3D GetColumns(oConn, 20);

}



public void GetPages(SQLConnection oConn, int intPageId, int 

intRowCount) {

    // Use oConn as provided connection and remember to check for 

validity

}



public void GetColumns(SQLConnection oConn, int intNumCols) {

    // Use oConn as provided connection and remember to check for 

validity

}



It just seems really *weird* to always pass a db-connection as a 

argument when you've been used to having global variables available. I 

know, globals are bad, but for db-connections they're really nice.



I really hope you'll willing to join the discussion. We must adhere to 

the best solution at all times - which is why I'm asking this before 

starting to do the work here.



Thanks in advance!



With Regards

Anders Lundholm - lundholm@s...

--

Your conclusion is correct.  We've been working on a rather large project 

here using ASP.NET and C# for the past 3 months.  This is exactly what we 

are doing.  You ALWAYS want to limit the amount of times that you are 

opening and closing a connection to a database, as your tests show, it is 

rather expensive.  All of our data-layer components either take a 

SQLConnection object as a parameter in the constructor or have methods 

that take a SQLConnection as a parameter.



You really have to make a paradigm shift towards true object-oriented 

programming to take advantage of what ASP.NET has to offer over ASP.



-----Original Message-----

From: Anders Lundholm [mailto:lundholm@s...]

Sent: Monday, November 27, 2000 11:03 AM

To: ASP+

Subject: [aspx] SQLConnection speed vs. ADODB.Connection





Hello people!



I am eager to see just how fast the SQL Server specific namespace is in 

the .NET framework when compared to the older ADODB COM components. Thus I 

decided to write a simple performance test where ASPx and ASP was supposed 

to compete.



Dual PII-350Mhz, 256Mb, 100Gb UW and Win2K Server/SQL Server 7.0



This is a long letter but I think it covers some questions a lot of 

current and prospective db-programmers ask: Which is faster, when and why - and how can I take advantage of each?!





I have a question in the bottom of this mail, so please take your time to 

read this throroughly and try to answer my question if possible!



-- = --



Test #1:

Pseudocode:

a. Do a loop with 1000 iterations

b. Open db-connection to SQL Server

c. Close db-connection

d. Loop next

e. Calculate time



Result:

ASP+: 8.0 secs.

ASP: 0.64 secs.



Comments on test #1:

This is very strange. I know ASP is really good at communicating with the 

ODBC layer, but this is scarry. The only answer I can come up with is, the 

ASP Connection object isn't opening a 'real' connection to the SQL Server -

 just a 'proxy' of that connection. Once the first data starts to flow 

to/from through that proxy, ASP will ask for a 'real' connection.



The other answer is, that the .NET framework haven't incorporated proper 

connection-pooling for the SQL Server namespace yet (which I really hope - 

look at those numbers!). Thus, the ASPx example opens a 'real' database-

connection to the SQL Server, which naturally is heavier (the below test 

kinda supports that theory).



No-one would - under all circumstances - open/close a connection without 

doing anything with it first! Only me, I guess! :)



-- = --



Test #2:

Pseudocode:

a. Do a loop with 1000 iterations

b. Open db-connection to SQL Server

c. Create a recordset and perform a simple select.

d. Loop through all records in each recordset (no display)

e. Close current recordset

f. Close db-connection

g. Loop next

h. Calculate time



Result:

ASP+: 18.0 secs.

ASP: 18 secs.



Comments on test #2:

It clearly shows that the SQLDataReader is outperforming the ADODB Records 

by atleast 50%. Nice, but not as fast as one would want - we're talking 

SQL Server optimized code here .. come on! ASP is clearly getting a lot 

slower when it's going to do something with that connection, instead of 

just using the 'proxy' (or whatever).



-- = --



Test #3:



Please notice that I've left the opening/closing of the db-connection 

outside of the loop - just to see the difference. You'll like the outcome:



Pseudocode:

a. Open db-connection to SQL Server

b. Do a loop with 1000 iterations

c. Create a recordset and perform a simple select.

d. Loop through all records in each recordset (no display)

e. Close current recordset

f. Loop next

g. Close db-connection

h. Calculate time



Result:

ASP+: 4.0 secs.

ASP: 16 secs.



Comments on test #3:

The SQL Server namespace is obviously gaining a lot of performance once it 

got rid of the open/close connection code in each iteration. But, how come 

it's really that slow on it? I'd expected the SQL Server namespace to 

perform just as fast as the ASP, using the proxy method and only take a 

performance-hit once data starts to flow.



Question (and discussion):

When programming datalayer-components using the .NET framework and the 

specific SQL Server optimized namespace, it would come with a big 

performance-penalty if you were to open a db-connection in each and every 

function of your datalayer.



Unlike regular ASP - you can't (in ASPx) declare a set of global variables 

for your page (e.g. oConn = valid database-connection) because everything 

actually is a self-contained assembly. Thus, variables have assembly level 

scope.



Compared to regular ASP, I can't use the global variable holding the 

connection for the entire page (e. g. oConn) and use that very connection 

in an include-file or pagelet. I'll have to create my own connection in 

each and every part of the assembly.



As the above test shows, then you're actually making your application 'N' 

times slower for each function you're calling in the code (provided those 

functions need database-connections), because each function would need a 

database-connection. The same goes for ASP, but you can still rely on 

the 'global' connection-variable. So, what do we do? We need to eliminate 

the overhead and look at a solution.



Theory:

I've given this a lot of thought and the only solution would be to make 

a 'rule', which says that the calling function should always provide a 

valid database-connection to the data-layer when it requests data. This 

would ensure multi-use of the same db-connection (when it's passed as an 

function-argument like the following):



(C#):

// Master function always providing a 'global' connection as function-argument

public void Master() {

    SQLConnection oConn = new SQLConnection

    // More oConn code here gives a valid connection

    string strPage = GetPages(oConn, 20, 10);

    string strColumn = GetColumns(oConn, 20);

}



public void GetPages(SQLConnection oConn, int intPageId, int intRowCount) {

    // Use oConn as provided connection and remember to check for validity

}



public void GetColumns(SQLConnection oConn, int intNumCols) {

    // Use oConn as provided connection and remember to check for validity

}



It just seems really *weird* to always pass a db-connection as a argument 

when you've been used to having global variables available. I know, 

globals are bad, but for db-connections they're really nice.



I really hope you'll willing to join the discussion. We must adhere to the 

best solution at all times - which is why I'm asking this before starting 

to do the work here. 



Thanks in advance!



With Regards

Anders Lundholm - lundholm@s...

--

> Your conclusion is correct.  We've been working on a rather large project

> here using ASP.NET and C# for the past 3 months.  This is exactly what we

> are doing.  You ALWAYS want to limit the amount of times that you are 

> opening and closing a connection to a database, as your tests show, it is

> rather expensive.  All of our data-layer components either take a 

> SQLConnection object as a parameter in the constructor or have methods 

> that take a SQLConnection as a parameter.



You mean you pass a SQLConnection from the business layer ?

If so you will have a data dependency in the business layer and it's not

very nice. The business layer should not know where the data comes from.

And even if you do so, the connection must be maid somewhere else and what

we have seen is that ADO handles connections much faster then ADO.NET.



A connection in ADO.Net must be faster than a connection in ADO, or

Microsoft will have problems with this, because if you have 100 users at

the same time they will each do a connection with ADO.NET, and if we look

at the test this is very time consuming. To increase scalability and

performance in ASP.NET the ADO.NET must be much faster, even if we think

in terms of OO.



I will never tolerate this bad result, in the final release of ADO.Net I

wan't to see big changes, I want to se that ADO.NET is much faster than

ADO or Microsoft have failed with ADO.NET.



/Fredrik Normén









> 

> -----Original Message-----

> From: Anders Lundholm [mailto:lundholm@s...]

> Sent: Monday, November 27, 2000 11:03 AM

> To: ASP+

> Subject: [aspx] SQLConnection speed vs. ADODB.Connection

> 

> 

> Hello people!

> 

> I am eager to see just how fast the SQL Server specific namespace is in

> the .NET framework when compared to the older ADODB COM components. Thus I

> decided to write a simple performance test where ASPx and ASP was supposed

> to compete.

> 

> Dual PII-350Mhz, 256Mb, 100Gb UW and Win2K Server/SQL Server 7.0

> 

> This is a long letter but I think it covers some questions a lot of 

> current and prospective db-programmers ask: Which is faster, when and why - and how can I take advantage of each?!

> 

> 

> I have a question in the bottom of this mail, so please take your time to

> read this throroughly and try to answer my question if possible!

> 

> -- = --

> 

> Test #1:

> Pseudocode:

> a. Do a loop with 1000 iterations

> b. Open db-connection to SQL Server

> c. Close db-connection

> d. Loop next

> e. Calculate time

> 

> Result:

> ASP+: 8.0 secs.

> ASP: 0.64 secs.

> 

> Comments on test #1:

> This is very strange. I know ASP is really good at communicating with the

> ODBC layer, but this is scarry. The only answer I can come up with is, the

> ASP Connection object isn't opening a 'real' connection to the SQL Server -

>  just a 'proxy' of that connection. Once the first data starts to flow 

> to/from through that proxy, ASP will ask for a 'real' connection.

> 

> The other answer is, that the .NET framework haven't incorporated proper

> connection-pooling for the SQL Server namespace yet (which I really hope -

> look at those numbers!). Thus, the ASPx example opens a 'real' database-

> connection to the SQL Server, which naturally is heavier (the below test

> kinda supports that theory).

> 

> No-one would - under all circumstances - open/close a connection without

> doing anything with it first! Only me, I guess! :)

> 

> -- = --

> 

> Test #2:

> Pseudocode:

> a. Do a loop with 1000 iterations

> b. Open db-connection to SQL Server

> c. Create a recordset and perform a simple select.

> d. Loop through all records in each recordset (no display)

> e. Close current recordset

> f. Close db-connection

> g. Loop next

> h. Calculate time

> 

> Result:

> ASP+: 18.0 secs.

> ASP: 18 secs.

> 

> Comments on test #2:

> It clearly shows that the SQLDataReader is outperforming the ADODB Records

> by atleast 50%. Nice, but not as fast as one would want - we're talking

> SQL Server optimized code here .. come on! ASP is clearly getting a lot

> slower when it's going to do something with that connection, instead of

> just using the 'proxy' (or whatever).

> 

> -- = --

> 

> Test #3:

> 

> Please notice that I've left the opening/closing of the db-connection 

> outside of the loop - just to see the difference. You'll like the outcome:

> 

> Pseudocode:

> a. Open db-connection to SQL Server

> b. Do a loop with 1000 iterations

> c. Create a recordset and perform a simple select.

> d. Loop through all records in each recordset (no display)

> e. Close current recordset

> f. Loop next

> g. Close db-connection

> h. Calculate time

> 

> Result:

> ASP+: 4.0 secs.

> ASP: 16 secs.

> 

> Comments on test #3:

> The SQL Server namespace is obviously gaining a lot of performance once it

> got rid of the open/close connection code in each iteration. But, how come

> it's really that slow on it? I'd expected the SQL Server namespace to 

> perform just as fast as the ASP, using the proxy method and only take a

> performance-hit once data starts to flow.

> 

> Question (and discussion):

> When programming datalayer-components using the .NET framework and the 

> specific SQL Server optimized namespace, it would come with a big 

> performance-penalty if you were to open a db-connection in each and every

> function of your datalayer.

> 

> Unlike regular ASP - you can't (in ASPx) declare a set of global variables

> for your page (e.g. oConn = valid database-connection) because everything

> actually is a self-contained assembly. Thus, variables have assembly level

> scope.

> 

> Compared to regular ASP, I can't use the global variable holding the 

> connection for the entire page (e. g. oConn) and use that very connection

> in an include-file or pagelet. I'll have to create my own connection in

> each and every part of the assembly.

> 

> As the above test shows, then you're actually making your application 'N'

> times slower for each function you're calling in the code (provided those

> functions need database-connections), because each function would need a

> database-connection. The same goes for ASP, but you can still rely on 

> the 'global' connection-variable. So, what do we do? We need to eliminate

> the overhead and look at a solution.

> 

> Theory:

> I've given this a lot of thought and the only solution would be to make

> a 'rule', which says that the calling function should always provide a 

> valid database-connection to the data-layer when it requests data. This

> would ensure multi-use of the same db-connection (when it's passed as an

> function-argument like the following):

> 

> (C#):

> // Master function always providing a 'global' connection as function-argument

> public void Master() {

>     SQLConnection oConn = new SQLConnection

>     // More oConn code here gives a valid connection

>     string strPage = GetPages(oConn, 20, 10);

>     string strColumn = GetColumns(oConn, 20);

> }

> 

> public void GetPages(SQLConnection oConn, int intPageId, int intRowCount) {

>     // Use oConn as provided connection and remember to check for validity

> }

> 

> public void GetColumns(SQLConnection oConn, int intNumCols) {

>     // Use oConn as provided connection and remember to check for validity

> }

> 

> It just seems really *weird* to always pass a db-connection as a argument

> when you've been used to having global variables available. I know, 

> globals are bad, but for db-connections they're really nice.

> 

> I really hope you'll willing to join the discussion. We must adhere to the

> best solution at all times - which is why I'm asking this before starting

> to do the work here. 

> 

> Thanks in advance!

> 

> With Regards

> Anders Lundholm - lundholm@s...

> --

Hi,



Hmm this is strange, I have done a simular test on my computer:



PIII 500 Mhz 196 Mb Ram, Win2k Server/SQL Server 2000.



TEst #1

1. Open a connection  ( Northwind DB )

2. Select all customers

3. Do a loop and display each customer

4. close the connection



ASP.NET:

I use SQLConnection, SQLCommand and SQLDataReader



ASP v 3.0:

I use ADO 2.6 with SQLOLEDB provider, Connection object, Recordset



Result:

ASP.NET	0,01 s

ASP		0,01 s



This is very strange.



Test #2

1. Do a loop with 0-1000

1. Open a connection  ( Northwind DB )

2. Select all customers

4. close the connection

5. Next



ASP.NET	10.6 s

ASP		2.2



I don't believe this, ASP v 3,0 with ADO 2.6 and SQLOLEDB provider is much

faster than

ASP.NET with SQLCommand.

NOTE: This is only a test, I will never use this in a real application, but

it's fun to compare how long it takes for a connection to be open with ASP

vs ASP.NET.



Maybe this result will be change when we leave the beta stage of VS.nET.

All the test here is done with Beta 1 of .NET so I think we should wait and

see if there is any changes when beta 2 and 3 is released.



/Frerik Normen







-----Original Message-----



From: Anders Lundholm [mailto:lundholm@s...]

Sent: den 27 november 2000 17:03

To: ASP+

Subject: [aspx] SQLConnection speed vs. ADODB.Connection





Hello people!



I am eager to see just how fast the SQL Server specific namespace is in the

.NET framework when compared to the older ADODB COM components. Thus I

decided to write a simple performance test where ASPx and ASP was supposed

to compete.



Dual PII-350Mhz, 256Mb, 100Gb UW and Win2K Server/SQL Server 7.0



This is a long letter but I think it covers some questions a lot of current

and prospective db-programmers ask: Which is faster, when and why - and how

can I take advantage of each?!



I have a question in the bottom of this mail, so please take your time to

read this throroughly and try to answer my question if possible!



-- = --



Test #1:

Pseudocode:

a. Do a loop with 1000 iterations

b. Open db-connection to SQL Server

c. Close db-connection

d. Loop next

e. Calculate time



Result:

ASP+: 8.0 secs.

ASP: 0.64 secs.



Comments on test #1:

This is very strange. I know ASP is really good at communicating with the

ODBC layer, but this is scarry. The only answer I can come up with is, the

ASP Connection object isn't opening a 'real' connection to the SQL Server -

just a 'proxy' of that connection. Once the first data starts to flow

to/from through that proxy, ASP will ask for a 'real' connection.



The other answer is, that the .NET framework haven't incorporated proper

connection-pooling for the SQL Server namespace yet (which I really hope -

look at those numbers!). Thus, the ASPx example opens a 'real'

database-connection to the SQL Server, which naturally is heavier (the below

test kinda supports that theory).



No-one would - under all circumstances - open/close a connection without

doing anything with it first! Only me, I guess! :)



-- = --



Test #2:

Pseudocode:

a. Do a loop with 1000 iterations

b. Open db-connection to SQL Server

c. Create a recordset and perform a simple select.

d. Loop through all records in each recordset (no display)

e. Close current recordset

f. Close db-connection

g. Loop next

h. Calculate time



Result:

ASP+: 18.0 secs.

ASP: 18 secs.



Comments on test #2:

It clearly shows that the SQLDataReader is outperforming the ADODB Records

by atleast 50%. Nice, but not as fast as one would want - we're talking SQL

Server optimized code here .. come on! ASP is clearly getting a lot slower

when it's going to do something with that connection, instead of just using

the 'proxy' (or whatever).



-- = --



Test #3:



Please notice that I've left the opening/closing of the db-connection

outside of the loop - just to see the difference. You'll like the outcome:



Pseudocode:

a. Open db-connection to SQL Server

b. Do a loop with 1000 iterations

c. Create a recordset and perform a simple select.

d. Loop through all records in each recordset (no display)

e. Close current recordset

f. Loop next

g. Close db-connection

h. Calculate time



Result:

ASP+: 4.0 secs.

ASP: 16 secs.



Comments on test #3:

The SQL Server namespace is obviously gaining a lot of performance once it

got rid of the open/close connection code in each iteration. But, how come

it's really that slow on it? I'd expected the SQL Server namespace to

perform just as fast as the ASP, using the proxy method and only take a

performance-hit once data starts to flow.



Question (and discussion):

When programming datalayer-components using the .NET framework and the

specific SQL Server optimized namespace, it would come with a big

performance-penalty if you were to open a db-connection in each and every

function of your datalayer.



Unlike regular ASP - you can't (in ASPx) declare a set of global variables

for your page (e.g. oConn = valid database-connection) because everything

actually is a self-contained assembly. Thus, variables have assembly level

scope.



Compared to regular ASP, I can't use the global variable holding the

connection for the entire page (e. g. oConn) and use that very connection in

an include-file or pagelet. I'll have to create my own connection in each

and every part of the assembly.



As the above test shows, then you're actually making your application 'N'

times slower for each function you're calling in the code (provided those

functions need database-connections), because each function would need a

database-connection. The same goes for ASP, but you can still rely on the

'global' connection-variable. So, what do we do? We need to eliminate the

overhead and look at a solution.



Theory:

I've given this a lot of thought and the only solution would be to make a

'rule', which says that the calling function should always provide a valid

database-connection to the data-layer when it requests data. This would

ensure multi-use of the same db-connection (when it's passed as an

function-argument like the following):



(C#):

// Master function always providing a 'global' connection as

function-argument

public void Master() {

    SQLConnection oConn = new SQLConnection

    // More oConn code here gives a valid connection

    string strPage = GetPages(oConn, 20, 10);

    string strColumn = GetColumns(oConn, 20);

}



public void GetPages(SQLConnection oConn, int intPageId, int intRowCount) {

    // Use oConn as provided connection and remember to check for validity

}



public void GetColumns(SQLConnection oConn, int intNumCols) {

    // Use oConn as provided connection and remember to check for validity

}



It just seems really *weird* to always pass a db-connection as a argument

when you've been used to having global variables available. I know, globals

are bad, but for db-connections they're really nice.



I really hope you'll willing to join the discussion. We must adhere to the

best solution at all times - which is why I'm asking this before starting to

do the work here.



Thanks in advance!



With Regards

Anders Lundholm - lundholm@s...

--

Frederik, there is one simple reason to pass around a SQLConnection or 

ADOConnection.  Performance.  Our data-layer is organized into objects. 

 Those objects are usually persisted to a database table.  So, the 

problem with not passing around the opened Connection object, is that I 

would have to open a new connection in each of my data-layer objects.  

If one of my business layer methods, needs to access two or more of my 

data-layer objects, then I have to open a connection 2 or more times.  

If I create one Connection object and pass it in to the data-layer 

objects, I only have to have one Connection object and I only have to 

open it one time.



I somewhat agree with your assessment that the business layer has a 

data dependency, but not so much as you are making it out.  You 

business layer MUST have a data dependency, otherwise, what good is it? 

 If I use the ADOConnection object in the business layer, I'm still 

sheltered from any changes in the data-layer as to the provider of my 

data.  The business layer won't care where the data comes from.  Our 

business layer access it's data from Collections in the data-layer 

objects.  That part will never change, regardlesss of where the data is 

coming from.  If you change providers or servers or whatever, you 

simply change your Connection String(s) in one central place...real 

simple.



Any downside to creating and opening the connection objects in my 

business layer is far less than the downside and the performance loss 

that I will incur by creating and opening multiple Connection objects.



Scott Davis

MCSD, MCP

International Parts

Decision Consultants Inc.





-----Original Message-----

From: Fredrik Normen [mailto:fredrik.normen@e...]

Sent: Tuesday, November 28, 2000 4:23 AM

To: ASP+

Subject: [aspx] RE: SQLConnection speed vs. ADODB.Connection





> Your conclusion is correct.  We've been working on a rather large 

project

> here using ASP.NET and C# for the past 3 months.  This is exactly 

what we

> are doing.  You ALWAYS want to limit the amount of times that you are 



> opening and closing a connection to a database, as your tests show, 

it is

> rather expensive.  All of our data-layer components either take a

> SQLConnection object as a parameter in the constructor or have 

methods

> that take a SQLConnection as a parameter.





You mean you pass a SQLConnection from the business layer ?

If so you will have a data dependency in the business layer and it's 

not

very nice. The business layer should not know where the data comes 

from.

And even if you do so, the connection must be maid somewhere else and 

what

we have seen is that ADO handles connections much faster then ADO.NET.



A connection in ADO.Net must be faster than a connection in ADO, or

Microsoft will have problems with this, because if you have 100 users 

at

the same time they will each do a connection with ADO.NET, and if we 

look

at the test this is very time consuming. To increase scalability and

performance in ASP.NET the ADO.NET must be much faster, even if we 

think

in terms of OO.



I will never tolerate this bad result, in the final release of ADO.Net 

I

wan't to see big changes, I want to se that ADO.NET is much faster than

ADO or Microsoft have failed with ADO.NET.



/Fredrik Norm=E9n









>

> -----Original Message-----

> From: Anders Lundholm [mailto:lundholm@s...]

> Sent: Monday, November 27, 2000 11:03 AM

> To: ASP+

> Subject: [aspx] SQLConnection speed vs. ADODB.Connection

>

>

> Hello people!

>

> I am eager to see just how fast the SQL Server specific namespace is 

in

> the .NET framework when compared to the older ADODB COM components. 

Thus I

> decided to write a simple performance test where ASPx and ASP was 

supposed

> to compete.

>

> Dual PII-350Mhz, 256Mb, 100Gb UW and Win2K Server/SQL Server 7.0

>

> This is a long letter but I think it covers some questions a lot of

> current and prospective db-programmers ask: Which is faster, when and 

why - and how can I take advantage of each?!

>

>

> I have a question in the bottom of this mail, so please take your 

time to

> read this throroughly and try to answer my question if possible!

>

> -- =3D --

>

> Test #1:

> Pseudocode:

> a. Do a loop with 1000 iterations

> b. Open db-connection to SQL Server

> c. Close db-connection

> d. Loop next

> e. Calculate time

>

> Result:

> ASP+: 8.0 secs.

> ASP: 0.64 secs.

>

> Comments on test #1:

> This is very strange. I know ASP is really good at communicating with 

the

> ODBC layer, but this is scarry. The only answer I can come up with 

is, the

> ASP Connection object isn't opening a 'real' connection to the SQL 

Server -

>  just a 'proxy' of that connection. Once the first data starts to 

flow

> to/from through that proxy, ASP will ask for a 'real' connection.

>

> The other answer is, that the .NET framework haven't incorporated 

proper

> connection-pooling for the SQL Server namespace yet (which I really 

hope -

> look at those numbers!). Thus, the ASPx example opens a 'real' 

database-

> connection to the SQL Server, which naturally is heavier (the below 

test

> kinda supports that theory).

>

> No-one would - under all circumstances - open/close a connection 

without

> doing anything with it first! Only me, I guess! :)

>

> -- =3D --

>

> Test #2:

> Pseudocode:

> a. Do a loop with 1000 iterations

> b. Open db-connection to SQL Server

> c. Create a recordset and perform a simple select.

> d. Loop through all records in each recordset (no display)

> e. Close current recordset

> f. Close db-connection

> g. Loop next

> h. Calculate time

>

> Result:

> ASP+: 18.0 secs.

> ASP: 18 secs.

>

> Comments on test #2:

> It clearly shows that the SQLDataReader is outperforming the ADODB 

Records

> by atleast 50%. Nice, but not as fast as one would want - we're 

talking

> SQL Server optimized code here .. come on! ASP is clearly getting a 

lot

> slower when it's going to do something with that connection, instead 

of

> just using the 'proxy' (or whatever).

>

> -- =3D --

>

> Test #3:

>

> Please notice that I've left the opening/closing of the db-connection 



> outside of the loop - just to see the difference. You'll like the 

outcome:

>

> Pseudocode:

> a. Open db-connection to SQL Server

> b. Do a loop with 1000 iterations

> c. Create a recordset and perform a simple select.

> d. Loop through all records in each recordset (no display)

> e. Close current recordset

> f. Loop next

> g. Close db-connection

> h. Calculate time

>

> Result:

> ASP+: 4.0 secs.

> ASP: 16 secs.

>

> Comments on test #3:

> The SQL Server namespace is obviously gaining a lot of performance 

once it

> got rid of the open/close connection code in each iteration. But, how 

come

> it's really that slow on it? I'd expected the SQL Server namespace to 



> perform just as fast as the ASP, using the proxy method and only take 

a

> performance-hit once data starts to flow.

>

> Question (and discussion):

> When programming datalayer-components using the .NET framework and 

the

> specific SQL Server optimized namespace, it would come with a big

> performance-penalty if you were to open a db-connection in each and 

every

> function of your datalayer.

>

> Unlike regular ASP - you can't (in ASPx) declare a set of global 

variables

> for your page (e.g. oConn =3D valid database-connection) because 

everything

> actually is a self-contained assembly. Thus, variables have assembly 

level

> scope.

>

> Compared to regular ASP, I can't use the global variable holding the

> connection for the entire page (e. g. oConn) and use that very 

connection

> in an include-file or pagelet. I'll have to create my own connection 

in

> each and every part of the assembly.

>

> As the above test shows, then you're actually making your application 

'N'

> times slower for each function you're calling in the code (provided 

those

> functions need database-connections), because each function would 

need a

> database-connection. The same goes for ASP, but you can still rely on 



> the 'global' connection-variable. So, what do we do? We need to 

eliminate

> the overhead and look at a solution.

>

> Theory:

> I've given this a lot of thought and the only solution would be to 

make

> a 'rule', which says that the calling function should always provide 

a

> valid database-connection to the data-layer when it requests data. 

This

> would ensure multi-use of the same db-connection (when it's passed as 

an

> function-argument like the following):

>

> (C#):

> // Master function always providing a 'global' connection as 

function-argument

> public void Master() {

>     SQLConnection oConn =3D new SQLConnection

>     // More oConn code here gives a valid connection

>     string strPage =3D GetPages(oConn, 20, 10);

>     string strColumn =3D GetColumns(oConn, 20);

> }

>

> public void GetPages(SQLConnection oConn, int intPageId, int 

intRowCount) {

>     // Use oConn as provided connection and remember to check for 

validity

> }

>

> public void GetColumns(SQLConnection oConn, int intNumCols) {

>     // Use oConn as provided connection and remember to check for 

validity

> }

>

> It just seems really *weird* to always pass a db-connection as a 

argument

> when you've been used to having global variables available. I know,

> globals are bad, but for db-connections they're really nice.

>

> I really hope you'll willing to join the discussion. We must adhere 

to the

> best solution at all times - which is why I'm asking this before 

starting

> to do the work here.

>

> Thanks in advance!

>

> With Regards

> Anders Lundholm - lundholm@s...

> --

Scott,



Won't you run into some serious scalability issues if you are holding all

those open connections?



Also, you are building dependency between your business and data layers.

Yes, if you change providers you can always update your connection

string....but what if you decided you need to use two (or three) different

providers rather than the single one your business objects assume.



--scott

MCS





> -----Original Message-----

> From: Scott Davis [mailto:Scott.D@e...]

> Sent: Tuesday, November 28, 2000 5:22 AM

> To: ASP+

> Subject: [aspx] RE: SQLConnection speed vs. ADODB.Connection

> 

> 

> Frederik, there is one simple reason to pass around a 

> SQLConnection or ADOConnection.  Performance.  Our data-layer 

> is organized into objects.  Those objects are usually 

> persisted to a database table.  So, the problem with not 

> passing around the opened Connection object, is that I would 

> have to open a new connection in each of my data-layer 

> objects.  If one of my business layer methods, needs to 

> access two or more of my data-layer objects, then I have to 

> open a connection 2 or more times.  If I create one 

> Connection object and pass it in to the data-layer objects, I 

> only have to have one Connection object and I only have to 

> open it one time.

> 

> I somewhat agree with your assessment that the business layer 

> has a data dependency, but not so much as you are making it 

> out.  You business layer MUST have a data dependency, 

> otherwise, what good is it?  If I use the ADOConnection 

> object in the business layer, I'm still sheltered from any 

> changes in the data-layer as to the provider of my data.  The 

> business layer won't care where the data comes from.  Our 

> business layer access it's data from Collections in the 

> data-layer objects.  That part will never change, regardlesss 

> of where the data is coming from.  If you change providers or 

> servers or whatever, you simply change your Connection 

> String(s) in one central place...real simple.

> 

> Any downside to creating and opening the connection objects 

> in my business layer is far less than the downside and the 

> performance loss that I will incur by creating and opening 

> multiple Connection objects.

> 

> Scott Davis

> MCSD, MCP

> International Parts

> Decision Consultants Inc.

> 

> 

> -----Original Message-----

> From: Fredrik Normen [mailto:fredrik.normen@e...]

> Sent: Tuesday, November 28, 2000 4:23 AM

> To: ASP+

> Subject: [aspx] RE: SQLConnection speed vs. ADODB.Connection

> 

> 

> > Your conclusion is correct.  We've been working on a rather 

> large project

> > here using ASP.NET and C# for the past 3 months.  This is 

> exactly what we

> > are doing.  You ALWAYS want to limit the amount of times 

> that you are 

> > opening and closing a connection to a database, as your 

> tests show, it is

> > rather expensive.  All of our data-layer components either take a 

> > SQLConnection object as a parameter in the constructor or 

> have methods 

> > that take a SQLConnection as a parameter.

> 

> 

> You mean you pass a SQLConnection from the business layer ?

> If so you will have a data dependency in the business layer 

> and it's not

> very nice. The business layer should not know where the data 

> comes from.

> And even if you do so, the connection must be maid somewhere 

> else and what

> we have seen is that ADO handles connections much faster then ADO.NET.

> 

> A connection in ADO.Net must be faster than a connection in ADO, or

> Microsoft will have problems with this, because if you have 

> 100 users at

> the same time they will each do a connection with ADO.NET, 

> and if we look

> at the test this is very time consuming. To increase scalability and

> performance in ASP.NET the ADO.NET must be much faster, even 

> if we think

> in terms of OO.

> 

> I will never tolerate this bad result, in the final release 

> of ADO.Net I

> wan't to see big changes, I want to se that ADO.NET is much 

> faster than

> ADO or Microsoft have failed with ADO.NET.

> 

> /Fredrik Normen

> 

> 

> 

> 

> > 

> > -----Original Message-----

> > From: Anders Lundholm [mailto:lundholm@s...]

> > Sent: Monday, November 27, 2000 11:03 AM

> > To: ASP+

> > Subject: [aspx] SQLConnection speed vs. ADODB.Connection

> > 

> > 

> > Hello people!

> > 

> > I am eager to see just how fast the SQL Server specific 

> namespace is in

> > the .NET framework when compared to the older ADODB COM 

> components. Thus I

> > decided to write a simple performance test where ASPx and 

> ASP was supposed

> > to compete.

> > 

> > Dual PII-350Mhz, 256Mb, 100Gb UW and Win2K Server/SQL Server 7.0

> > 

> > This is a long letter but I think it covers some questions a lot of 

> > current and prospective db-programmers ask: Which is 

> faster, when and why - and how can I take advantage of each?!

> > 

> > 

> > I have a question in the bottom of this mail, so please 

> take your time to

> > read this throroughly and try to answer my question if possible!

> > 

> > -- = --

> > 

> > Test #1:

> > Pseudocode:

> > a. Do a loop with 1000 iterations

> > b. Open db-connection to SQL Server

> > c. Close db-connection

> > d. Loop next

> > e. Calculate time

> > 

> > Result:

> > ASP+: 8.0 secs.

> > ASP: 0.64 secs.

> > 

> > Comments on test #1:

> > This is very strange. I know ASP is really good at 

> communicating with the

> > ODBC layer, but this is scarry. The only answer I can come 

> up with is, the

> > ASP Connection object isn't opening a 'real' connection to 

> the SQL Server -

> >  just a 'proxy' of that connection. Once the first data 

> starts to flow 

> > to/from through that proxy, ASP will ask for a 'real' connection.

> > 

> > The other answer is, that the .NET framework haven't 

> incorporated proper

> > connection-pooling for the SQL Server namespace yet (which 

> I really hope -

> > look at those numbers!). Thus, the ASPx example opens a 

> 'real' database-

> > connection to the SQL Server, which naturally is heavier 

> (the below test

> > kinda supports that theory).

> > 

> > No-one would - under all circumstances - open/close a 

> connection without

> > doing anything with it first! Only me, I guess! :)

> > 

> > -- = --

> > 

> > Test #2:

> > Pseudocode:

> > a. Do a loop with 1000 iterations

> > b. Open db-connection to SQL Server

> > c. Create a recordset and perform a simple select.

> > d. Loop through all records in each recordset (no display)

> > e. Close current recordset

> > f. Close db-connection

> > g. Loop next

> > h. Calculate time

> > 

> > Result:

> > ASP+: 18.0 secs.

> > ASP: 18 secs.

> > 

> > Comments on test #2:

> > It clearly shows that the SQLDataReader is outperforming 

> the ADODB Records

> > by atleast 50%. Nice, but not as fast as one would want - 

> we're talking

> > SQL Server optimized code here .. come on! ASP is clearly 

> getting a lot

> > slower when it's going to do something with that 

> connection, instead of

> > just using the 'proxy' (or whatever).

> > 

> > -- = --

> > 

> > Test #3:

> > 

> > Please notice that I've left the opening/closing of the 

> db-connection 

> > outside of the loop - just to see the difference. You'll 

> like the outcome:

> > 

> > Pseudocode:

> > a. Open db-connection to SQL Server

> > b. Do a loop with 1000 iterations

> > c. Create a recordset and perform a simple select.

> > d. Loop through all records in each recordset (no display)

> > e. Close current recordset

> > f. Loop next

> > g. Close db-connection

> > h. Calculate time

> > 

> > Result:

> > ASP+: 4.0 secs.

> > ASP: 16 secs.

> > 

> > Comments on test #3:

> > The SQL Server namespace is obviously gaining a lot of 

> performance once it

> > got rid of the open/close connection code in each 

> iteration. But, how come

> > it's really that slow on it? I'd expected the SQL Server 

> namespace to 

> > perform just as fast as the ASP, using the proxy method and 

> only take a

> > performance-hit once data starts to flow.

> > 

> > Question (and discussion):

> > When programming datalayer-components using the .NET 

> framework and the 

> > specific SQL Server optimized namespace, it would come with a big 

> > performance-penalty if you were to open a db-connection in 

> each and every

> > function of your datalayer.

> > 

> > Unlike regular ASP - you can't (in ASPx) declare a set of 

> global variables

> > for your page (e.g. oConn = valid database-connection) 

> because everything

> > actually is a self-contained assembly. Thus, variables have 

> assembly level

> > scope.

> > 

> > Compared to regular ASP, I can't use the global variable 

> holding the 

> > connection for the entire page (e. g. oConn) and use that 

> very connection

> > in an include-file or pagelet. I'll have to create my own 

> connection in

> > each and every part of the assembly.

> > 

> > As the above test shows, then you're actually making your 

> application 'N'

> > times slower for each function you're calling in the code 

> (provided those

> > functions need database-connections), because each function 

> would need a

> > database-connection. The same goes for ASP, but you can 

> still rely on 

> > the 'global' connection-variable. So, what do we do? We 

> need to eliminate

> > the overhead and look at a solution.

> > 

> > Theory:

> > I've given this a lot of thought and the only solution 

> would be to make

> > a 'rule', which says that the calling function should 

> always provide a 

> > valid database-connection to the data-layer when it 

> requests data. This

> > would ensure multi-use of the same db-connection (when it's 

> passed as an

> > function-argument like the following):

> > 

> > (C#):

> > // Master function always providing a 'global' connection 

> as function-argument

> > public void Master() {

> >     SQLConnection oConn = new SQLConnection

> >     // More oConn code here gives a valid connection

> >     string strPage = GetPages(oConn, 20, 10);

> >     string strColumn = GetColumns(oConn, 20);

> > }

> > 

> > public void GetPages(SQLConnection oConn, int intPageId, 

> int intRowCount) {

> >     // Use oConn as provided connection and remember to 

> check for validity

> > }

> > 

> > public void GetColumns(SQLConnection oConn, int intNumCols) {

> >     // Use oConn as provided connection and remember to 

> check for validity

> > }

> > 

> > It just seems really *weird* to always pass a db-connection 

> as a argument

> > when you've been used to having global variables available. I know, 

> > globals are bad, but for db-connections they're really nice.

> > 

> > I really hope you'll willing to join the discussion. We 

> must adhere to the

> > best solution at all times - which is why I'm asking this 

> before starting

> > to do the work here. 

> > 

> > Thanks in advance!

> > 

> > With Regards

> > Anders Lundholm - lundholm@s...

> > --

> 

> 

Hi,



Comments inline.



>Frederik, there is one simple reason to pass around a SQLConnection or

ADOConnection.

>Performance.  Our data-layer is organized into objects.  Those objects are

usually persisted to a

>database table.  So, the problem with not passing around the opened

Connection object, is that I would have to >open a new connection in each of

my data-layer objects.  If one of my business layer methods, needs to access

>two or more of my data-layer objects, then I have to open a connection 2 or

more times.  If I create one

>Connection object and pass it in to the data-layer objects, I only have to

have one Connection object and I

>only have to open it one time.



Ok, I have sometimes the same problem, but I consider of having a better

design of the system and the ability to manage and change the component

depending on the markets needs, without affecting other layers.

You can solve some of the connection problems with Object pooling.



>I somewhat agree with your assessment that the business layer has a data

dependency, but not so much as you are >making it out.  You business layer

MUST have a data dependency, otherwise, what good is it?



What I ment was that Business object should not know where the data comes

from.

The Business objects should only get data from the data object or the

presentation layer, this will give us a better way to change each layer

without affecting others.

For example maybe we have to change data objects to get information from a

Oracle database.

In order to do is we have to change our SQLCOnnection to ADOConnection and

use OLEDB Providers.

This will affect both the Busniess and data layer in your example.

Maybe we want to get data from a SAP/R3 system, we want to do this without

changing the business objects.



>If I use the ADOConnection object in the business layer, I'm still

sheltered from any changes in the data-layer >as to the provider of my data.

The business layer won't care where the data comes from.  Our business layer

>access it's data from Collections in the data-layer objects.  That part

will never change, regardlesss of where >the data is coming from.  If you

change providers or servers or whatever, you simply change your Connection

>String(s) in one central place...real simple.



Sometimes you need to change more than the connection string.



>Any downside to creating and opening the connection objects in my business

layer is far less than the downside >and the performance loss that I will

incur by creating and opening multiple Connection objects.



I'm aware of the fact that performance and scalability is very important,

but it's very important to change the objects according to the market.. And

it's important for developer to do this in an easy way.



/Fredrik Normén

Frederik, I think I answered most of what you said here in a previous 

post when I explained how we have encapsulated our connection object 

creation and opening in a separate object.  By doing that, the business 

layer, never has to change, only the data-layer.



I think we are saying the same thing here, but our approach is slightly 

different.  In your approach, how do you prevent having to create and 

open many different connections if you are not passing a connection 

object around (which is simply a point that you are passing)??  If you 

are creating, opening and closing several different connections, this 

is not what we want to do here.  It may work fine for your application, 

but it won't work for ours and I don't want to put that kind of load of 

Database server.



Scott Davis

MCSD, MCP

International Parts

Decision Consultants Inc.





-----Original Message-----

From: Fredrik Norm=E9n [mailto:fredrik.normen@e...]

Sent: Tuesday, November 28, 2000 11:02 AM

To: ASP+

Subject: [aspx] RE: SQLConnection speed vs. ADODB.Connection





Hi,



Comments inline.



>Frederik, there is one simple reason to pass around a SQLConnection or

ADOConnection.

>Performance.  Our data-layer is organized into objects.  Those objects 

are

usually persisted to a

>database table.  So, the problem with not passing around the opened

Connection object, is that I would have to >open a new connection in 

each of

my data-layer objects.  If one of my business layer methods, needs to 

access

>two or more of my data-layer objects, then I have to open a connection 

2 or

more times.  If I create one

>Connection object and pass it in to the data-layer objects, I only 

have to

have one Connection object and I

>only have to open it one time.



Ok, I have sometimes the same problem, but I consider of having a 

better

design of the system and the ability to manage and change the component

depending on the markets needs, without affecting other layers.

You can solve some of the connection problems with Object pooling.



>I somewhat agree with your assessment that the business layer has a 

data

dependency, but not so much as you are >making it out.  You business 

layer

MUST have a data dependency, otherwise, what good is it?



What I ment was that Business object should not know where the data 

comes

from.

The Business objects should only get data from the data object or the

presentation layer, this will give us a better way to change each layer

without affecting others.

For example maybe we have to change data objects to get information 

from a

Oracle database.

In order to do is we have to change our SQLCOnnection to ADOConnection 

and

use OLEDB Providers.

This will affect both the Busniess and data layer in your example.

Maybe we want to get data from a SAP/R3 system, we want to do this 

without

changing the business objects.



>If I use the ADOConnection object in the business layer, I'm still

sheltered from any changes in the data-layer >as to the provider of my 

data.

The business layer won't care where the data comes from.  Our business 

layer

>access it's data from Collections in the data-layer objects.  That 

part

will never change, regardlesss of where >the data is coming from.  If 

you

change providers or servers or whatever, you simply change your 

Connection

>String(s) in one central place...real simple.



Sometimes you need to change more than the connection string.



>Any downside to creating and opening the connection objects in my 

business

layer is far less than the downside >and the performance loss that I 

will

incur by creating and opening multiple Connection objects.



I'm aware of the fact that performance and scalability is very 

important,

but it's very important to change the objects according to the market.. 

And

it's important for developer to do this in an easy way.



/Fredrik Norm=E9n

I'm running much less risk of scalability issues with my approach, which 

opens a connection once, uses it and closes it.  Maybe you are mis-

interpreting or missed what we are doing here.  We are working on an 

ASP.NET/C# project.  All data access is done each time a page loads on the 

server.  Therefore, we create ONE connection object, use it, close it and 

then throw it away.  As opposed to creating and opening several connection 

objects, using them, closing them and throwing them all away.



One of earlier posts answered your second question.  All of our 

SQLConnection objects are encapsulated in a separate object.  If we needed 

to use two or three different providers (which isn't realistic on this 

project), we would simply encapsulate those new connections in this 

separate object.  The business layer wouldn't change at all on the data-

layer.



Scott Davis

MCSD, MCP

International Parts

Decision Consultants Inc.





-----Original Message-----

From: Scott Mauvais [mailto:smauvais@m...]

Sent: Tuesday, November 28, 2000 10:29 AM

To: ASP+

Subject: [aspx] RE: SQLConnection speed vs. ADODB.Connection





Scott,



Won't you run into some serious scalability issues if you are holding all

those open connections?



Also, you are building dependency between your business and data layers.

Yes, if you change providers you can always update your connection

string....but what if you decided you need to use two (or three) different

providers rather than the single one your business objects assume.



--scott

MCS





> -----Original Message-----

> From: Scott Davis [mailto:Scott.D@e...]

> Sent: Tuesday, November 28, 2000 5:22 AM

> To: ASP+

> Subject: [aspx] RE: SQLConnection speed vs. ADODB.Connection

> 

> 

> Frederik, there is one simple reason to pass around a 

> SQLConnection or ADOConnection.  Performance.  Our data-layer 

> is organized into objects.  Those objects are usually 

> persisted to a database table.  So, the problem with not 

> passing around the opened Connection object, is that I would 

> have to open a new connection in each of my data-layer 

> objects.  If one of my business layer methods, needs to 

> access two or more of my data-layer objects, then I have to 

> open a connection 2 or more times.  If I create one 

> Connection object and pass it in to the data-layer objects, I 

> only have to have one Connection object and I only have to 

> open it one time.

> 

> I somewhat agree with your assessment that the business layer 

> has a data dependency, but not so much as you are making it 

> out.  You business layer MUST have a data dependency, 

> otherwise, what good is it?  If I use the ADOConnection 

> object in the business layer, I'm still sheltered from any 

> changes in the data-layer as to the provider of my data.  The 

> business layer won't care where the data comes from.  Our 

> business layer access it's data from Collections in the 

> data-layer objects.  That part will never change, regardlesss 

> of where the data is coming from.  If you change providers or 

> servers or whatever, you simply change your Connection 

> String(s) in one central place...real simple.

> 

> Any downside to creating and opening the connection objects 

> in my business layer is far less than the downside and the 

> performance loss that I will incur by creating and opening 

> multiple Connection objects.

> 

> Scott Davis

> MCSD, MCP

> International Parts

> Decision Consultants Inc.

> 

> 

> -----Original Message-----

> From: Fredrik Normen [mailto:fredrik.normen@e...]

> Sent: Tuesday, November 28, 2000 4:23 AM

> To: ASP+

> Subject: [aspx] RE: SQLConnection speed vs. ADODB.Connection

> 

> 

> > Your conclusion is correct.  We've been working on a rather 

> large project

> > here using ASP.NET and C# for the past 3 months.  This is 

> exactly what we

> > are doing.  You ALWAYS want to limit the amount of times 

> that you are 

> > opening and closing a connection to a database, as your 

> tests show, it is

> > rather expensive.  All of our data-layer components either take a 

> > SQLConnection object as a parameter in the constructor or 

> have methods 

> > that take a SQLConnection as a parameter.

> 

> 

> You mean you pass a SQLConnection from the business layer ?

> If so you will have a data dependency in the business layer 

> and it's not

> very nice. The business layer should not know where the data 

> comes from.

> And even if you do so, the connection must be maid somewhere 

> else and what

> we have seen is that ADO handles connections much faster then ADO.NET.

> 

> A connection in ADO.Net must be faster than a connection in ADO, or

> Microsoft will have problems with this, because if you have 

> 100 users at

> the same time they will each do a connection with ADO.NET, 

> and if we look

> at the test this is very time consuming. To increase scalability and

> performance in ASP.NET the ADO.NET must be much faster, even 

> if we think

> in terms of OO.

> 

> I will never tolerate this bad result, in the final release 

> of ADO.Net I

> wan't to see big changes, I want to se that ADO.NET is much 

> faster than

> ADO or Microsoft have failed with ADO.NET.

> 

> /Fredrik Normen

> 

> 

> 

> 

> > 

> > -----Original Message-----

> > From: Anders Lundholm [mailto:lundholm@s...]

> > Sent: Monday, November 27, 2000 11:03 AM

> > To: ASP+

> > Subject: [aspx] SQLConnection speed vs. ADODB.Connection

> > 

> > 

> > Hello people!

> > 

> > I am eager to see just how fast the SQL Server specific 

> namespace is in

> > the .NET framework when compared to the older ADODB COM 

> components. Thus I

> > decided to write a simple performance test where ASPx and 

> ASP was supposed

> > to compete.

> > 

> > Dual PII-350Mhz, 256Mb, 100Gb UW and Win2K Server/SQL Server 7.0

> > 

> > This is a long letter but I think it covers some questions a lot of 

> > current and prospective db-programmers ask: Which is 

> faster, when and why - and how can I take advantage of each?!

> > 

> > 

> > I have a question in the bottom of this mail, so please 

> take your time to

> > read this throroughly and try to answer my question if possible!

> > 

> > -- = --

> > 

> > Test #1:

> > Pseudocode:

> > a. Do a loop with 1000 iterations

> > b. Open db-connection to SQL Server

> > c. Close db-connection

> > d. Loop next

> > e. Calculate time

> > 

> > Result:

> > ASP+: 8.0 secs.

> > ASP: 0.64 secs.

> > 

> > Comments on test #1:

> > This is very strange. I know ASP is really good at 

> communicating with the

> > ODBC layer, but this is scarry. The only answer I can come 

> up with is, the

> > ASP Connection object isn't opening a 'real' connection to 

> the SQL Server -

> >  just a 'proxy' of that connection. Once the first data 

> starts to flow 

> > to/from through that proxy, ASP will ask for a 'real' connection.

> > 

> > The other answer is, that the .NET framework haven't 

> incorporated proper

> > connection-pooling for the SQL Server namespace yet (which 

> I really hope -

> > look at those numbers!). Thus, the ASPx example opens a 

> 'real' database-

> > connection to the SQL Server, which naturally is heavier 

> (the below test

> > kinda supports that theory).

> > 

> > No-one would - under all circumstances - open/close a 

> connection without

> > doing anything with it first! Only me, I guess! :)

> > 

> > -- = --

> > 

> > Test #2:

> > Pseudocode:

> > a. Do a loop with 1000 iterations

> > b. Open db-connection to SQL Server

> > c. Create a recordset and perform a simple select.

> > d. Loop through all records in each recordset (no display)

> > e. Close current recordset

> > f. Close db-connection

> > g. Loop next

> > h. Calculate time

> > 

> > Result:

> > ASP+: 18.0 secs.

> > ASP: 18 secs.

> > 

> > Comments on test #2:

> > It clearly shows that the SQLDataReader is outperforming 

> the ADODB Records

> > by atleast 50%. Nice, but not as fast as one would want - 

> we're talking

> > SQL Server optimized code here .. come on! ASP is clearly 

> getting a lot

> > slower when it's going to do something with that 

> connection, instead of

> > just using the 'proxy' (or whatever).

> > 

> > -- = --

> > 

> > Test #3:

> > 

> > Please notice that I've left the opening/closing of the 

> db-connection 

> > outside of the loop - just to see the difference. You'll 

> like the outcome:

> > 

> > Pseudocode:

> > a. Open db-connection to SQL Server

> > b. Do a loop with 1000 iterations

> > c. Create a recordset and perform a simple select.

> > d. Loop through all records in each recordset (no display)

> > e. Close current recordset

> > f. Loop next

> > g. Close db-connection

> > h. Calculate time

> > 

> > Result:

> > ASP+: 4.0 secs.

> > ASP: 16 secs.

> > 

> > Comments on test #3:

> > The SQL Server namespace is obviously gaining a lot of 

> performance once it

> > got rid of the open/close connection code in each 

> iteration. But, how come

> > it's really that slow on it? I'd expected the SQL Server 

> namespace to 

> > perform just as fast as the ASP, using the proxy method and 

> only take a

> > performance-hit once data starts to flow.

> > 

> > Question (and discussion):

> > When programming datalayer-components using the .NET 

> framework and the 

> > specific SQL Server optimized namespace, it would come with a big 

> > performance-penalty if you were to open a db-connection in 

> each and every

> > function of your datalayer.

> > 

> > Unlike regular ASP - you can't (in ASPx) declare a set of 

> global variables

> > for your page (e.g. oConn = valid database-connection) 

> because everything

> > actually is a self-contained assembly. Thus, variables have 

> assembly level

> > scope.

> > 

> > Compared to regular ASP, I can't use the global variable 

> holding the 

> > connection for the entire page (e. g. oConn) and use that 

> very connection

> > in an include-file or pagelet. I'll have to create my own 

> connection in

> > each and every part of the assembly.

> > 

> > As the above test shows, then you're actually making your 

> application 'N'

> > times slower for each function you're calling in the code 

> (provided those

> > functions need database-connections), because each function 

> would need a

> > database-connection. The same goes for ASP, but you can 

> still rely on 

> > the 'global' connection-variable. So, what do we do? We 

> need to eliminate

> > the overhead and look at a solution.

> > 

> > Theory:

> > I've given this a lot of thought and the only solution 

> would be to make

> > a 'rule', which says that the calling function should 

> always provide a 

> > valid database-connection to the data-layer when it 

> requests data. This

> > would ensure multi-use of the same db-connection (when it's 

> passed as an

> > function-argument like the following):

> > 

> > (C#):

> > // Master function always providing a 'global' connection 

> as function-argument

> > public void Master() {

> >     SQLConnection oConn = new SQLConnection

> >     // More oConn code here gives a valid connection

> >     string strPage = GetPages(oConn, 20, 10);

> >     string strColumn = GetColumns(oConn, 20);

> > }

> > 

> > public void GetPages(SQLConnection oConn, int intPageId, 

> int intRowCount) {

> >     // Use oConn as provided connection and remember to 

> check for validity

> > }

> > 

> > public void GetColumns(SQLConnection oConn, int intNumCols) {

> >     // Use oConn as provided connection and remember to 

> check for validity

> > }

> > 

> > It just seems really *weird* to always pass a db-connection 

> as a argument

> > when you've been used to having global variables available. I know, 

> > globals are bad, but for db-connections they're really nice.

> > 

> > I really hope you'll willing to join the discussion. We 

> must adhere to the

> > best solution at all times - which is why I'm asking this 

> before starting

> > to do the work here. 

> > 

> > Thanks in advance!

> > 

> > With Regards

> > Anders Lundholm - lundholm@s...

> > --

> 

> 



---

Wrox Professional Web Developer Conference II

http://www.wroxconferences.com/WebDevEurope

November 29th - December 1st 2000, Amsterdam, Netherlands

Create powerful distributed web applications serving you now and

in the future. From ASP+ and VB.NET to XML and SQL Server 2000

Hello!



Comments inline!



> You can solve some of the connection problems with Object pooling.



I thought ASP.NET used object pooling?! Isn't all .aspx files compiled and

pooled?! Is it only true components (which have been compiled) that are able

to achieve pooling?! Sounds weird, because .aspx files are assemblies and

compiled too. Thus .aspx should be able to get the same speed as components.



How would you make your datalayer faster then?



> What I ment was that Business object should not know where the data comes

> from.



True! But, how would the datalayer its connection string!?

Would you hardcode the connection-string in the datalayer (not) - or just

hardcode the retrieval of e.g. a DSN from an Application variable or

something else?! I mean, using an argument in the function call would make

it dynamic!



I agree it would affect atlest one more layer if you decided to use e.g. a

db-connection or just a db-DSN string as argument to a constructor or

function-call *because* the argument would become obsolete if you decided to

use the ADO namespace instead of the SQL namespace. This would leave you

with a lot of code which provided SQLConnection arguments to the

constructors/functions instead of the current ADO connection. Is this right?



> The Business objects should only get data from the data object or the

> presentation layer, this will give us a better way to change each layer

> without affecting others.

> For example maybe we have to change data objects to get information from a

> Oracle database.

> In order to do is we have to change our SQLCOnnection to ADOConnection and

> use OLEDB Providers.

> This will affect both the Busniess and data layer in your example.



> Maybe we want to get data from a SAP/R3 system, we want to do this without

> changing the business objects.



When you're talking about different datalayers, how often do you really

change e.g. a SQLConnection to a SAP/R3 connection?



I mean, when I'm building the datalayer for my application, then there will

be parts of that layer which will never change providers, thus allowing me

to use a connection as constructor/argument would give me a faster

datalayer. If I'm doing - lets say - 10 database lookups, why would I want

to sacrifice the speed for more encapsulation?!



You're right Fredrik, but I believe there's a middleway. Some parts of your

datalayer will always want to connect to a DB such as SQL Server (or an ADO

provider). Or?



> Sometimes you need to change more than the connection string.



True, but if you do that, then it's also possible that the consumer (e.g.

the businesslayer) would want to handle the data different. Thus you're

basically changing core components and why not optimize those as well?!



I can follow you all the way, except that I really don't get HOW you'd

specify which DB provider to use in your datalayer if you aren't giving it

as an argument (I bet you're NOT hardcoding the DSN string).



> I'm aware of the fact that performance and scalability is very important,

> but it's very important to change the objects according to the market..

And

> it's important for developer to do this in an easy way.



Naturally, but you really have to sit back and see how frequent you'll want

to change parts of your datalayer. When it comes to datalayers which connect

to variable systems such as CRMs or - heck - even 3rd-party .NET components,

you should really want to encapsulate as much as possible.



But, when it comes to cornerstone parts of your application where DB

connections could potentially drag your site to the dust, I would certainly

optimize as much as possible.



The issue in Scotts and my case is (as I see it), to choose if we want to

specify a SQLConnection or a ADOConnection as an argument. The latter is

more flexible but the former is faster (SQLConnection).



What do you think about this?

Please enlighten me if I'm off here .. I'd want to do it the right way!



with regards

anders lundholm ˇ lundholm@s...

the sphereworx / monoliner experience

--

>I thought ASP.NET used object pooling?! Isn't all .aspx files compiled and

>pooled?! Is it only true components (which have been compiled) that are

able

>to achieve pooling?! Sounds weird, because .aspx files are assemblies and

>compiled too. Thus .aspx should be able to get the same speed as

components.



No, this is not true, the only way to use object pooling is to use Component

Services today with the .Net



Here is a snap from the .Net Framwork Developers Guide:



<SNAP>

Connection Pooling

To deploy high-performance applications, you need to use connection pooling.

When you use the ADO Managed Provider you do not need to enable connection

pooling, because the ADO Managed Provider does so automatically. The SQL

Managed Provider relies on Windows 2000 Component Services to provide

connection pooling, and you need to follow these steps to enable it:



Define the SQLConnection subclass using the code provided below.

Write the code to use the pooled connection.

Include Microsoft.ComServices.dll.

Optionally, configure the pooling characteristics of the component using the

component services user interface.

Defining the SQLConnection Subclass

The following code defines a subclass of the SQLConnection class. Use

Guidgen.exe to obtain a new Guid (FD5ABffd-D026-4684-8515-2BB5184E8991 in

the sample below).



[ConstructionEnabled(default="Server=servername ?"]   // required here or in

gui

[ObjectPooling(true)]                      // required

[Transaction(TransactionOption.Supported)] // optional

public class myConnection : SQLPooledConnection

{

// class is empty unless adding SetComplete and SetAbort methods, e.g.

// public void SetComplete() { ContextUtil.SetComplete(); }

// public void SetAbort() { ContextUtil.SetAbort(); }

}

Writing Code That Uses the Pooled Component

The pooled connection class is used much like an ordinary SQLConnection, as

shown in the following code sample:



IReader iReader;



myConnection conn = new myConnection();

conn.Open

SQLCommand cmd = new SQLCommand("SELECT * FROM Orders", conn);

cmd.Execute(ref iReader);



conn.Close();

The model for pooled connection is the same for pooled and non-pooled

connections. However, it is especially important to call Close when you

finish with a pooled connection to release it back into the pool.



Include Microsoft.ComServices.dll

The COM+ Services DLL must be included to support pooling.



Configuring Pooled Component

Although a pooled connection object can be strictly configured by setting

attributes at compile time, it is often desirable to change the behavior of

the object after the application has been deployed. To enable this,

attributes such as the connection string (also called the construction

string) and transaction requirements can be set with Component Services in

Administrative Tools.



Transaction Enlistment

One of the benefits of the pooling mechanism is that it provides support for

controlling transaction enlistment behavior. The SQL Managed provider

determines if the current context has a distributed transaction associated

with it, and if so, enlists automatically. You can control transaction

enlistment behavior of a pooled connection at compile time with

TransactionAttribute or at runtime with Component Services in Administrative

Tools.

</SNAP>



>True! But, how would the datalayer its connection string!?

>Would you hardcode the connection-string in the datalayer (not) - or just

>hardcode the retrieval of e.g. a DSN from an Application variable or

>something else?! I mean, using an argument in the function call would make

>it dynamic!



You can add the connection string to config.web, and use a shared propety

that read the connection string one time from the config.web file here is an

example:





config.web

-----------

<configuration>

    <!-- store the database connection info here -->

    <appsettings>

        <add key="DSN"

value="server=localhost;uid=sa;pwd=;database=northwind" />

    </appsettings>

</configuration>



Code

------------



using System;

using System.Web;

using System.Collections;



namespace MyApplication

{



    public class MyDBHelper {

static String ConnectionString;



public static String ConnectionString {



      get {

      	if (ConnectionString == null) {

			Hashtable appSettings = (Hashtable)

HttpContext.Current.GetConfig("appsettings");

                  m_ConnectionString = (String) appSettings["DSN"];



                  if (m_ConnectionString == null) {

                       throw new Exception("DSN Value not set in

Config.web");

                  }

             }

             return m_ConnectionString;

      }

}



If you want to get the connection string somewhere in you application you

simply write:



namespace MyApplication



	public class ProductsDB



        public DataSet GetProducts() {



            SQLConnection dbConnection = new

SQLConnection(MyDBHelper.ConnectionString);



.....



I hope this will give you some idea, you will find this in IbuySpy example.



>When you're talking about different datalayers, how often do you really

>change e.g. a SQLConnection to a SAP/R3 connection?



Not often, but if we have too, we must be pepeard to do it and in a simple

way.



>True, but if you do that, then it's also possible that the consumer (e.g.

>the businesslayer) would want to handle the data different. Thus you're

>basically changing core components and why not optimize those as well?!



No, the business layer will never work different because of changing the

data source, and so should it be designed. You should never need to change

the Business layer if you have to move or change the data-access object.

That is why you should seperate business from data.





/Fredrik Normén

Frederik, I agree with pretty much everything that you've said here.  I 

think we're arriving at the same result in different fashions.



>>You can add the connection string to config.web, and use a shared 

propety

>>that read the connection string one time from the config.web file



This works great and we considered doing this.  The problem is if you 

want to use your data-layer components with a fat-client app, then 

you're hosed because you don't have a config.web file.  If don't intend 

to do that, then config.web is the perfect place for it.  We will 

probably be storing our DSNs somewhere that is accessible to all 

applications and easy to make global changes, such as Active Directory.



-----Original Message-----

From: Fredrik Norm=E9n [mailto:fredrik.normen@e...]

Sent: Wednesday, November 29, 2000 3:31 AM

To: ASP+

Subject: [aspx] RE: SQLConnection speed vs. ADODB.Connection





>I thought ASP.NET used object pooling?! Isn't all .aspx files compiled 

and

>pooled?! Is it only true components (which have been compiled) that 

are

able

>to achieve pooling?! Sounds weird, because .aspx files are assemblies 

and

>compiled too. Thus .aspx should be able to get the same speed as

components.



No, this is not true, the only way to use object pooling is to use 

Component

Services today with the .Net



Here is a snap from the .Net Framwork Developers Guide:



<SNAP>

Connection Pooling

To deploy high-performance applications, you need to use connection 

pooling.

When you use the ADO Managed Provider you do not need to enable 

connection

pooling, because the ADO Managed Provider does so automatically. The 

SQL

Managed Provider relies on Windows 2000 Component Services to provide

connection pooling, and you need to follow these steps to enable it:



Define the SQLConnection subclass using the code provided below.

Write the code to use the pooled connection.

Include Microsoft.ComServices.dll.

Optionally, configure the pooling characteristics of the component 

using the

component services user interface.

Defining the SQLConnection Subclass

The following code defines a subclass of the SQLConnection class. Use

Guidgen.exe to obtain a new Guid (FD5ABffd-D026-4684-8515-2BB5184E8991 

in

the sample below).



[ConstructionEnabled(default=3D"Server=3Dservername ..."]   // required 

here or in

gui

[ObjectPooling(true)]                      // required

[Transaction(TransactionOption.Supported)] // optional

public class myConnection : SQLPooledConnection

{

// class is empty unless adding SetComplete and SetAbort methods, e.g.

// public void SetComplete() { ContextUtil.SetComplete(); }

// public void SetAbort() { ContextUtil.SetAbort(); }

}

Writing Code That Uses the Pooled Component

The pooled connection class is used much like an ordinary 

SQLConnection, as

shown in the following code sample:



IReader iReader;



myConnection conn =3D new myConnection();

conn.Open

SQLCommand cmd =3D new SQLCommand("SELECT * FROM Orders", conn);

cmd.Execute(ref iReader);



conn.Close();

The model for pooled connection is the same for pooled and non-pooled

connections. However, it is especially important to call Close when you

finish with a pooled connection to release it back into the pool.



Include Microsoft.ComServices.dll

The COM+ Services DLL must be included to support pooling.



Configuring Pooled Component

Although a pooled connection object can be strictly configured by 

setting

attributes at compile time, it is often desirable to change the 

behavior of

the object after the application has been deployed. To enable this,

attributes such as the connection string (also called the construction

string) and transaction requirements can be set with Component Services 

in

Administrative Tools.



Transaction Enlistment

One of the benefits of the pooling mechanism is that it provides 

support for

controlling transaction enlistment behavior. The SQL Managed provider

determines if the current context has a distributed transaction 

associated

with it, and if so, enlists automatically. You can control transaction

enlistment behavior of a pooled connection at compile time with

TransactionAttribute or at runtime with Component Services in 

Administrative

Tools.

</SNAP>



>True! But, how would the datalayer its connection string!?

>Would you hardcode the connection-string in the datalayer (not) - or 

just

>hardcode the retrieval of e.g. a DSN from an Application variable or

>something else?! I mean, using an argument in the function call would 

make

>it dynamic!



You can add the connection string to config.web, and use a shared 

propety

that read the connection string one time from the config.web file here 

is an

example:





config.web

-----------

<configuration>

    <!-- store the database connection info here -->

    <appsettings>

        <add key=3D"DSN"

value=3D"server=3Dlocalhost;uid=3Dsa;pwd=3D;database=3Dnorthwind" />

    </appsettings>

</configuration>



Code

------------



using System;

using System.Web;

using System.Collections;



namespace MyApplication

{



    public class MyDBHelper {

static String ConnectionString;



public static String ConnectionString {



      get {

      	if (ConnectionString =3D=3D null) {

			Hashtable appSettings =3D (Hashtable)

HttpContext.Current.GetConfig("appsettings");

                  m_ConnectionString =3D (String) appSettings["DSN"];



                  if (m_ConnectionString =3D=3D null) {

                       throw new Exception("DSN Value not set in

Config.web");

                  }

             }

             return m_ConnectionString;

      }

}



If you want to get the connection string somewhere in you application 

you

simply write:



namespace MyApplication



	public class ProductsDB



        public DataSet GetProducts() {



            SQLConnection dbConnection =3D new

SQLConnection(MyDBHelper.ConnectionString);



.....



I hope this will give you some idea, you will find this in IbuySpy 

example.



>When you're talking about different datalayers, how often do you 

really

>change e.g. a SQLConnection to a SAP/R3 connection?



Not often, but if we have too, we must be pepeard to do it and in a 

simple

way.



>True, but if you do that, then it's also possible that the consumer 

(e.g.

>the businesslayer) would want to handle the data different. Thus 

you're

>basically changing core components and why not optimize those as 

well?!



No, the business layer will never work different because of changing 

the

data source, and so should it be designed. You should never need to 

change

the Business layer if you have to move or change the data-access 

object.

That is why you should seperate business from data.





/Fredrik Norm=E9n

>Frederik, I agree with pretty much everything that you've said here.  I

think we're arriving at the

>same result in different fashions.



I think I can agree with this.



>This works great and we considered doing this.  The problem is if you want

to use your data-layer

>components with a fat-client app, then you're hosed because you don't have

a config.web file.  If don't >intend to do that, then config.web is the

perfect place for it.  We will probably be storing our DSNs

>somewhere that is accessible to all applications and easy to make global

changes, such as Active

>Directory.



If you are running a Web application you can get data from config.web or you

can use configmanager and read from you own config file.



example:



You config file:



<?xml version ="1.0"?>

<configuration xmlns="x-schema:catalog.xms">



  <Connection>Server=Localhost......</Connection>

</configuration>





C#

using System.Configuration;



String constring = (String) ConfigManager.GetProperty("Connection",

"c:\myapp\myconfig.cfg");





Maybe this will give you something.



Best Regards,

Fredrik Normén







-----Original Message-----

From: Fredrik Normén [mailto:fredrik.normen@e...]

Sent: Wednesday, November 29, 2000 3:31 AM

To: ASP+

Subject: [aspx] RE: SQLConnection speed vs. ADODB.Connection





>I thought ASP.NET used object pooling?! Isn't all .aspx files compiled and

>pooled?! Is it only true components (which have been compiled) that are

able

>to achieve pooling?! Sounds weird, because .aspx files are assemblies and

>compiled too. Thus .aspx should be able to get the same speed as

components.



No, this is not true, the only way to use object pooling is to use Component

Services today with the .Net



Here is a snap from the .Net Framwork Developers Guide:



<SNAP>

Connection Pooling

To deploy high-performance applications, you need to use connection pooling.

When you use the ADO Managed Provider you do not need to enable connection

pooling, because the ADO Managed Provider does so automatically. The SQL

Managed Provider relies on Windows 2000 Component Services to provide

connection pooling, and you need to follow these steps to enable it:



Define the SQLConnection subclass using the code provided below.

Write the code to use the pooled connection.

Include Microsoft.ComServices.dll.

Optionally, configure the pooling characteristics of the component using the

component services user interface.

Defining the SQLConnection Subclass

The following code defines a subclass of the SQLConnection class. Use

Guidgen.exe to obtain a new Guid (FD5ABffd-D026-4684-8515-2BB5184E8991 in

the sample below).



[ConstructionEnabled(default="Server=servername ..."]   // required here or

in

gui

[ObjectPooling(true)]                      // required

[Transaction(TransactionOption.Supported)] // optional

public class myConnection : SQLPooledConnection

{

// class is empty unless adding SetComplete and SetAbort methods, e.g.

// public void SetComplete() { ContextUtil.SetComplete(); }

// public void SetAbort() { ContextUtil.SetAbort(); }

}

Writing Code That Uses the Pooled Component

The pooled connection class is used much like an ordinary SQLConnection, as

shown in the following code sample:



IReader iReader;



myConnection conn = new myConnection();

conn.Open

SQLCommand cmd = new SQLCommand("SELECT * FROM Orders", conn);

cmd.Execute(ref iReader);



conn.Close();

The model for pooled connection is the same for pooled and non-pooled

connections. However, it is especially important to call Close when you

finish with a pooled connection to release it back into the pool.



Include Microsoft.ComServices.dll

The COM+ Services DLL must be included to support pooling.



Configuring Pooled Component

Although a pooled connection object can be strictly configured by setting

attributes at compile time, it is often desirable to change the behavior of

the object after the application has been deployed. To enable this,

attributes such as the connection string (also called the construction

string) and transaction requirements can be set with Component Services in

Administrative Tools.



Transaction Enlistment

One of the benefits of the pooling mechanism is that it provides support for

controlling transaction enlistment behavior. The SQL Managed provider

determines if the current context has a distributed transaction associated

with it, and if so, enlists automatically. You can control transaction

enlistment behavior of a pooled connection at compile time with

TransactionAttribute or at runtime with Component Services in Administrative

Tools.

</SNAP>



>True! But, how would the datalayer its connection string!?

>Would you hardcode the connection-string in the datalayer (not) - or just

>hardcode the retrieval of e.g. a DSN from an Application variable or

>something else?! I mean, using an argument in the function call would make

>it dynamic!



You can add the connection string to config.web, and use a shared propety

that read the connection string one time from the config.web file here is an

example:





config.web

-----------

<configuration>

    <!-- store the database connection info here -->

    <appsettings>

        <add key="DSN"

value="server=localhost;uid=sa;pwd=;database=northwind" />

    </appsettings>

</configuration>



Code

------------



using System;

using System.Web;

using System.Collections;



namespace MyApplication

{



    public class MyDBHelper {

static String ConnectionString;



public static String ConnectionString {



      get {

      	if (ConnectionString == null) {

			Hashtable appSettings = (Hashtable)

HttpContext.Current.GetConfig("appsettings");

                  m_ConnectionString = (String) appSettings["DSN"];



                  if (m_ConnectionString == null) {

                       throw new Exception("DSN Value not set in

Config.web");

                  }

             }

             return m_ConnectionString;

      }

}



If you want to get the connection string somewhere in you application you

simply write:



namespace MyApplication



	public class ProductsDB



        public DataSet GetProducts() {



            SQLConnection dbConnection = new

SQLConnection(MyDBHelper.ConnectionString);



.....



I hope this will give you some idea, you will find this in IbuySpy example.



>When you're talking about different datalayers, how often do you really

>change e.g. a SQLConnection to a SAP/R3 connection?



Not often, but if we have too, we must be pepeard to do it and in a simple

way.



>True, but if you do that, then it's also possible that the consumer (e.g.

>the businesslayer) would want to handle the data different. Thus you're

>basically changing core components and why not optimize those as well?!



No, the business layer will never work different because of changing the

data source, and so should it be designed. You should never need to change

the Business layer if you have to move or change the data-access object.

That is why you should seperate business from data.





/Fredrik Normén