Hi All,



I'm working on a form that has 3 dropdown boxes selecting data from 3 

separate lookup tables.  Trying to use the command.executereader method 

to be the most efficient. I ran into something interesting and wanted 

others opinions. I've seen different examples declaring 3 separate 

connection strings which I'd mean that is 3 separate threads to the 

database.  The system.data.commandbehavior.closeconnection which I was 

understanding it would close the connection to the datastore, this using 

the system.data.sqlclient class.  When I look at active processes in the 

database(sql 2k), three threads where opened up and loading the webpage 

once. 



Digging a bit deaper into the sdk documentation, it states for a 

"connection pool" to be created, the same connection string has to be 

used, if the connection string is different, another pool is created.   

Now thinking this is wrong, I tried to re-use the connection string 

created on the 1st connection calling on the 2nd connection to the 

database and it bombed.   Also the doc's mention all connections should 

be explictly use the Close() or Dispose() method to free up the 

connection that is released back to the pool.  I was able to figure out 

how to only show one active process with the code below, to test I 

opened multiple browsers and hit it with both sets of code(Example 2 and 

Example 3-- Note Example 1 will bomb!).  The Example 3 code shows active 

connections continued to grow the more times i refreshed the page or had 

more browsers hit the page.  When I used Example 2 code below the same 

connection stayed active(I even changed the data on the fly to see it 

wasn't cached and the new data showed up without using a new 

connection).  .  My test shows to use Example #2 -- Has anyone else ran 

into this or did any testing? 



My tests show the system.data.commandbehavior.closeconnection doesn't 

work and shouldn't be used.  Any thoughts or comments?



Example 1 -- code bombs when it try's to open the 2nd instance to the 

db.

**************************************

This code didn't work without creating 3 connection's objects to the db. 

 The code only shows one below though.

**************************************

<%@ Page Language=3D"VB" EnableSessionState=3D"False" 

EnableViewState=3D"False" Trace=3D"False" Debug=3D"True" Strict=3D"True" 

%>

<%@ Import Namespace=3D"System.Data.sqlclient" %>

<script language=3D"VB" runat=3D"server">

Sub Page_Load(Src As Object, E As EventArgs)

    Dim strconn as string =3D ConfigurationSettings.AppSettings("DSN")

    Dim Conn AS SQLConnection =3D new sqlconnection(strconn)



    Dim strSQL as string =3D "select catId, fpType from 

catType_frontpage"

    Dim Cmd as New SQLCommand(strSQL,Conn)

    Conn.Open()

    catId.DataSource =3D 

Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)

    catId.DataBind()



    Dim strSQL2 as string =3D "select catId, demoType from 

catType_Demos"

    Dim Cmd2 as New SQLCommand(strSQL2,Conn2)

    Conn.Open()

    demoId.DataSource =3D 

Cmd2.ExecuteReader(system.data.CommandBehavior.CloseConnection)

    demoId.DataBind()



    Dim strSQL3 as string =3D"select authorId, authorName from 

authorInfo order by authorName"

    Dim Cmd3 as New SQLCommand(strSQL3,Conn3)

    Conn.Open()

    authorId.DataSource =3D 

Cmd3.ExecuteReader(system.data.CommandBehavior.CloseConnection)

    authorId.DataBind()



End Sub



Example 2 -- This the right way, only one active connection stated 

active

**************************************

This code worked

**************************************

Worked fine if I used the same connection



<%@ Page Language=3D"VB" EnableSessionState=3D"False" 

EnableViewState=3D"False" Trace=3D"False" Debug=3D"True" Strict=3D"True" 

%>

<%@ Import Namespace=3D"System.Data.sqlclient" %>

<script language=3D"VB" runat=3D"server">

Sub Page_Load(Src As Object, E As EventArgs)



    Dim strconn as string =3D ConfigurationSettings.AppSettings("DSN")

    Dim Conn AS SQLConnection =3D new sqlconnection(strconn)



    Dim strSQL as string =3D "select catId, fpType from 

catType_frontpage"

    Dim Cmd as New SQLCommand(strSQL,Conn)

    Conn.Open()

    catId.DataSource =3D Cmd.ExecuteReader()

    catId.DataBind()

    Conn.Close()



    Dim strSQL2 as string =3D "select catId, demoType from 

catType_Demos"

    Dim Cmd2 as New SQLCommand(strSQL2,Conn)

    Conn.Open()

    demoId.DataSource =3D Cmd2.ExecuteReader()

    demoId.DataBind()

    Conn.Close()

 

    Dim strSQL3 as string =3D"select authorId, authorName from 

authorInfo order by authorName"

    Dim Cmd3 as New SQLCommand(strSQL3,Conn)

    Conn.Open()

    authorId.DataSource =3D Cmd3.ExecuteReader()

    authorId.DataBind()

    Conn.Close()

End Sub





Example 3 -- BAD BAD, the more times I refreshed the browser or had 

multiple browsers hit the page, the more connections showed up in the 

db.

***********

This code creates ton's of connections the more browsers I hit the test 

page the more connections show up in the active processes

**********

<%@ Page Language=3D"VB" EnableSessionState=3D"False" 

EnableViewState=3D"False" Trace=3D"False" Debug=3D"True" Strict=3D"True" 

%>

<%@ Import Namespace=3D"System.Data.sqlclient" %>

<script language=3D"VB" runat=3D"server">

Sub Page_Load(Src As Object, E As EventArgs)

    Dim strconn as string =3D ConfigurationSettings.AppSettings("DSN")

    Dim Conn AS SQLConnection =3D new sqlconnection(strconn)



    Dim strSQL as string =3D "select catId, fpType from 

catType_frontpage"

    Dim Cmd as New SQLCommand(strSQL,Conn)

    Conn.Open()

    catId.DataSource =3D 

Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)

    catId.DataBind()



    Dim Conn2 AS SQLConnection =3D new sqlconnection(strconn)

    Dim strSQL2 as string =3D "select catId, demoType from 

catType_Demos"

    Dim Cmd2 as New SQLCommand(strSQL2,Conn2)

    Conn2.Open()

    demoId.DataSource =3D 

Cmd2.ExecuteReader(system.data.CommandBehavior.CloseConnection)

    demoId.DataBind()



    Dim Conn3 AS SQLConnection =3D new sqlconnection(strconn)

    Dim strSQL3 as string =3D"select authorId, authorName from 

authorInfo order by authorName"

    Dim Cmd3 as New SQLCommand(strSQL3,Conn3)

    Conn3.Open()

    authorId.DataSource =3D 

Cmd3.ExecuteReader(system.data.CommandBehavior.CloseConnection)

    authorId.DataBind()





End Sub

</script>



Steve Schofield

steve@a...



Webmaster

http://aspfree.com