Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > connection to db best practices

Reply
Thread Tools

connection to db best practices

 
 
Mike D
Guest
Posts: n/a
 
      10-14-2004
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
 
Reply With Quote
 
 
 
 
Ray Costanzo [MVP]
Guest
Posts: n/a
 
      10-14-2004
I also use an include file, like so:

<%
Dim oADO, bDataOpen
Sub OpenData()
Set oADO = CreateObject("ADODB.Connection")
oADO.Open "Provider=sqloledb;Data Source=servername;Initial
Catalog=dbName;User Id=username;Password=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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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



 
Reply With Quote
 
 
 
 
Luis
Guest
Posts: n/a
 
      10-21-2004
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!
 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      10-21-2004
Luis wrote:
> 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)?
>
>

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"


 
Reply With Quote
 
Luis
Guest
Posts: n/a
 
      10-21-2004
Bob wrote:
> If you mean: is it OK to store the connection object in
> session or application, then the answer is No, <snip>


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.CreateObject("ADODB.Connection")
conn.Open Application("connstring")

Dim strGetSql, rs
'SomeStoredProcedure does a SELECT on the db...
strGetSql = "EXEC SomeStoredProcedure @Blah = '" & SomeBlah & "'"
Set rs = conn.Execute(GetSql)

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_OnStart
Application("ConnString") = "PROVIDER=SQLOLEDB; Server=MyServer;
DATABASE=MyDB; USER ID=Foo; PASSWORD=Bar;"
Application("Server_Name") = "http://localhost/blah"
END SUB


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
Reply With Quote
 
Ray Costanzo [MVP]
Guest
Posts: n/a
 
      10-21-2004
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(something...)
Set rs2 = conn.Execute(somethingElse...)
'''some code
CloseRS rs
CloseRS rs2



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

Ray at work

"Luis" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Bob wrote:
>> If you mean: is it OK to store the connection object in
>> session or application, then the answer is No, <snip>

>
> 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.CreateObject("ADODB.Connection")
> conn.Open Application("connstring")
>
> Dim strGetSql, rs
> 'SomeStoredProcedure does a SELECT on the db...
> strGetSql = "EXEC SomeStoredProcedure @Blah = '" & SomeBlah & "'"
> Set rs = conn.Execute(GetSql)
>
> strTra = rs("Field1")
> strLala = rs("Field2")
>
> CloseRs()
> CloseConn()
>


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      10-21-2004
Luis wrote:
> Bob wrote:
>> If you mean: is it OK to store the connection object in
>> session or application, then the answer is No, <snip>

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


Yes, given that this code is not going to do anything. You can't call a sub
in Global.asa from an asp page ...

>
> 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.


??? These subs are not in scope. These lines should raise an error.

>
>
> Example:
>
> '*** START MyStuff.asp page ***
>
> Dim conn
> Set conn = Server.CreateObject("ADODB.Connection")
> conn.Open Application("connstring")


This is good. Nothing wrong with this.

>
> Dim strGetSql, rs
> 'SomeStoredProcedure does a SELECT on the db...
> strGetSql = "EXEC SomeStoredProcedure @Blah = '" & SomeBlah & "'"
> Set rs = conn.Execute(GetSql)
>
> strTra = rs("Field1")
> strLala = rs("Field2")
>
> CloseRs()
> CloseConn()


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?

>
> The only ther thing I would have in my Global.asa is:
>
> SUB Application_OnStart
> Application("ConnString") = "PROVIDER=SQLOLEDB; Server=MyServer;
> DATABASE=MyDB; USER ID=Foo; PASSWORD=Bar;"
> Application("Server_Name") = "http://localhost/blah"
> END SUB
>

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.


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Best Codeplex sample for showing best coding practices? John Dalberg ASP .Net 3 11-16-2006 12:07 PM
Re: Best Practices - VSS integration with VS.NET, migrating our Classic ASP projects news.microsoft.com ASP .Net 0 05-03-2004 08:29 PM
SECURITY: Best Practices for Handling Connection Strings Ryan N. ASP General 2 02-11-2004 07:03 PM
best practices using procedure attributes Izvra ASP .Net 0 12-23-2003 09:43 PM
Best sample app for learning best practices, OO & asp.net? karim ASP .Net 0 07-13-2003 04:26 AM



Advertisments