connection to db best practices

[Mike D]

What are the best practices when using a db and include files?

I typically store my connection string in an include file. I then open my
db do what I need to and close the connection. I haven't put these in
functions. How does everyone else handle the connections are the in
functions? in includes?

Mike

[Curt_C [MVP]]

Re: connection to db best practices

I may have the string in the INC but I do the open and close on every page.
You could easily do it in an INC as well though. Either way as long as you
ensure you close the connection out you are doing better then many.

--
Curt Christianson
Owner/Lead Developer, DF-Software
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com


"Mike D" <MikeD@discussi ons.microsoft.c om> wrote in message
news:5EB10352-E1CD-4B22-89B4-69FE08F2B105@mi crosoft.com...[color=blue]
> What are the best practices when using a db and include files?
>
> I typically store my connection string in an include file. I then open my
> db do what I need to and close the connection. I haven't put these in
> functions. How does everyone else handle the connections are the in
> functions? in includes?
>
> Mike[/color]

[Ray Costanzo [MVP]]

Re: connection to db best practices

I also use an include file, like so:

<%
Dim oADO, bDataOpen
Sub OpenData()
Set oADO = CreateObject("A DODB.Connection ")
oADO.Open "Provider=sqlol edb;Data Source=serverna me;Initial
Catalog=dbName; User Id=username;Pas sword=password; "
bDataOpen = True
End Sub

Sub CloseData()
oADO.Close
Set oADO = Nothing
bDataOpen = False
End Sub

Function TextIn(TheText, MaxLength)
Dim sResult
sResult = TheText
If MaxLength > 0 Then
If Len(sResult) > MaxLength Then sResult = Left(sResult, MaxLength)
End If
sResult = Replace(sResult , "'", "''")
sResult = "'" & sResult & "'"
TextIn = sResult
End Function
%>

I'll also sometimes create functions like GetData (returns array from
..GetRows), ExecNonQuery (sub), GetSingleValue, or some thing along those
lines.

Ray at work


"Mike D" <MikeD@discussi ons.microsoft.c om> wrote in message
news:5EB10352-E1CD-4B22-89B4-69FE08F2B105@mi crosoft.com...[color=blue]
> What are the best practices when using a db and include files?
>
> I typically store my connection string in an include file. I then open my
> db do what I need to and close the connection. I haven't put these in
> functions. How does everyone else handle the connections are the in
> functions? in includes?
>
> Mike[/color]

[Luis]

Re: connection to db best practices

Is it ok/safe to put the OpenData() and CloseData() subs that you
illustrated in this example in the Global.asa file (instead of an
includes file)?


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows [MVP]]

Re: connection to db best practices

Luis wrote:[color=blue]
> Is it ok/safe to put the OpenData() and CloseData() subs that you
> illustrated in this example in the Global.asa file (instead of an
> includes file)?
>
>[/color]
If you mean: is it OK to store the connection object in session or
application, then the answer is No, not if you want your website to handle
more than one user at a time.

http://www.aspfaq.com/2053

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Luis]

Re: connection to db best practices

Bob wrote:[color=blue]
> If you mean: is it OK to store the connection object in
> session or application, then the answer is No, <snip>[/color]

Luis replied:

So, would it be bad to include the following in my Global.asa?

SUB CloseRs
rs.Close
Set rs = nothing
END SUB

SUB CloseConn
conn.Close
Set conn = nothing
END SUB

Then I just add:

CloseRs()
CloseConn()

to each of my asp pages when I want to close a db connection that I've
opened on those pages.


Example:

'*** START MyStuff.asp page ***

Dim conn
Set conn = Server.CreateOb ject("ADODB.Con nection")
conn.Open Application("co nnstring")

Dim strGetSql, rs
'SomeStoredProc edure does a SELECT on the db...
strGetSql = "EXEC SomeStoredProce dure @Blah = '" & SomeBlah & "'"
Set rs = conn.Execute(Ge tSql)

strTra = rs("Field1")
strLala = rs("Field2")

CloseRs()
CloseConn()

Response.Write( "Tra is: " & strTra & "<br>" & "Lala is: " & strLala)

'*** END MyStuff.asp page. ***


The only ther thing I would have in my Global.asa is:

SUB Application_OnS tart
Application("Co nnString") = "PROVIDER=SQLOL EDB; Server=MyServer ;
DATABASE=MyDB; USER ID=Foo; PASSWORD=Bar;"
Application("Se rver_Name") = "http://localhost/blah"
END SUB


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Ray Costanzo [MVP]]

Re: connection to db best practices

You'd have to use an include file for this, not global.asa. But putting it
all in an include would be fine.

One thing about your CloseRS sub though - you may want to pass the RS as an
argument to the subroutine, i.e.

'''your code...
Set rs = conn.Execute(so mething...)
Set rs2 = conn.Execute(so methingElse...)
'''some code
CloseRS rs
CloseRS rs2



Sub CloseRS(ByRef rs)
rs.Close
Set rs = Nothing
End Sub

Ray at work

"Luis" <andyzaNOSPAM@w ebmail.co.za> wrote in message
news:u14y3C3tEH A.1008@tk2msftn gp13.phx.gbl...[color=blue]
> Bob wrote:[color=green]
>> If you mean: is it OK to store the connection object in
>> session or application, then the answer is No, <snip>[/color]
>
> Luis replied:
>
> So, would it be bad to include the following in my Global.asa?
>
> SUB CloseRs
> rs.Close
> Set rs = nothing
> END SUB
>
> SUB CloseConn
> conn.Close
> Set conn = nothing
> END SUB
>
> Then I just add:
>
> CloseRs()
> CloseConn()
>
> to each of my asp pages when I want to close a db connection that I've
> opened on those pages.
>
>
> Example:
>
> '*** START MyStuff.asp page ***
>
> Dim conn
> Set conn = Server.CreateOb ject("ADODB.Con nection")
> conn.Open Application("co nnstring")
>
> Dim strGetSql, rs
> 'SomeStoredProc edure does a SELECT on the db...
> strGetSql = "EXEC SomeStoredProce dure @Blah = '" & SomeBlah & "'"
> Set rs = conn.Execute(Ge tSql)
>
> strTra = rs("Field1")
> strLala = rs("Field2")
>
> CloseRs()
> CloseConn()
>[/color]

[Bob Barrows [MVP]]

Re: connection to db best practices

Luis wrote:[color=blue]
> Bob wrote:[color=green]
>> If you mean: is it OK to store the connection object in
>> session or application, then the answer is No, <snip>[/color]
>
> Luis replied:
>
> So, would it be bad to include the following in my Global.asa?[/color]

Yes, given that this code is not going to do anything. You can't call a sub
in Global.asa from an asp page ...
[color=blue]
>
> SUB CloseRs
> rs.Close
> Set rs = nothing
> END SUB
>
> SUB CloseConn
> conn.Close
> Set conn = nothing
> END SUB
>
> Then I just add:
>
> CloseRs()
> CloseConn()
>
> to each of my asp pages when I want to close a db connection that I've
> opened on those pages.[/color]

??? These subs are not in scope. These lines should raise an error.
[color=blue]
>
>
> Example:
>
> '*** START MyStuff.asp page ***
>
> Dim conn
> Set conn = Server.CreateOb ject("ADODB.Con nection")
> conn.Open Application("co nnstring")[/color]

This is good. Nothing wrong with this.
[color=blue]
>
> Dim strGetSql, rs
> 'SomeStoredProc edure does a SELECT on the db...
> strGetSql = "EXEC SomeStoredProce dure @Blah = '" & SomeBlah & "'"
> Set rs = conn.Execute(Ge tSql)
>
> strTra = rs("Field1")
> strLala = rs("Field2")
>
> CloseRs()
> CloseConn()[/color]

These two lines of code aren't going to do anything. Don't you get an error
message when they're attempted to be executed? Is there an "on error resume
next" line that you aren't showing us?
[color=blue]
>
> The only ther thing I would have in my Global.asa is:
>
> SUB Application_OnS tart
> Application("Co nnString") = "PROVIDER=SQLOL EDB; Server=MyServer ;
> DATABASE=MyDB; USER ID=Foo; PASSWORD=Bar;"
> Application("Se rver_Name") = "http://localhost/blah"
> END SUB
>[/color]
This is good: no objects, just strings.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.