Nice DB code I use for all my pages.

[ZtaCari]

I put this in an include file on the top of all my pages and only use it when I need to connect to the db. All I do is call the OpenDB() at the beginning of the page and CloseDB() at the end of the page. This makes sure I close and destroy all my objects. It also has the option to comment out the server I am not currently using in one file.

<%'This page should get included at the top of all files that use database connections.

dim Conn, ConnString, RS, strSQL

Function OpenDB()
'Open database connection
Set Conn = Server.CreateObject("ADODB.Connection")
'ConnString = "Provider=SQLOLEDB; Data Source=testservername; Initial Catalog=testdbname; User Id=testuser; Password=testpasswrd"
ConnString = "Provider=SQLOLEDB; Data Source=liveservername; Initial Catalog=livedbname; User Id=liveuser; Password=livepasswrd"
Conn.Open ConnString
set RS = server.CreateObject("ADODB.RecordSet")
End Function

Function CloseDB()
'Close database connection and destroy object
set RS = nothing
Conn.Close
set Conn = nothing
End Function
%>


I just thought this may help some of the newbies keep their db code clean and easy to administer. Don't forget to name it .asp just for security reasons.

 

[KCWebMonkey]

That works if you want to use a DSN i guess... I prefer DSN-less connections myself.

 

[ZtaCari]

I used an OLEDB connection which is similar to DSN-Less connections. Some consider it quicker. Check out this article from 4GuysFromRolla.com OLEDB For Me

 

[KCWebMonkey]

Guess I should've looked at the code closer

I will need to try that out and see if i notice any performance changes.

 

[yorri]

Guess I should've looked at the code closer

I will need to try that out and see if i notice any performance changes.

I saw tests on the differences and it is supposed to be faster but I haven't been able to see any difference...I think it is better when you have many connections. If it is for access I think Access will probably crap out before you really get any benefit.

BUT...I have trained myself to not use ODBC now because it is "supposed" to be better. I don't know...guess it could be filed under the best practices document for database access.

 

[fedwebmaster]

A DSN-less connection to a SQL database is faster, quite simply because it bypasses ODBC which must perform translation between the ODBC layer and the provider eg. SQLOLEDB.

I use this method in VB and ASP programming, but I'm getting very interested in what ADO.NET can do too, as it provides even more performance improvements:

Reading through my "Bumper Book of VB.NET" (AKA Wrox Professional VB.NET 2nd Edition), ADO.NET is even faster, because the SQL Server Data Provider communicates with SQL Server using it's native data format (Tabular Data Stream). This means that there's even less translation and your application communicates with the database even faster.

Wherever possible it is also better to use disconnected data/record sets, as this provides scalability benefits. As the number of users increases there will be less of an impact on your database and application servers as "resource-hungry" connections exist only for as long as is required to retrieve the data.

I can thoroughly recommend the Wrox book. Lots of detail and some excellent examples. You can just download the code examples from their website too - no purchase necessary

Rob
FedWebMaster

 

[WineIsGood]

My tests here at work during my R&D phase for ASP.NET/ADO.NET showed a clear 2:1 speed increase. Nearly exactly twice as fast.
-Dave