DSN or DSN-less?

[Spookster]

Ok I got stuck with doing a project in ASP. :mad:

I have complete access to the server and decided on using MS SQL server for the database. Now looking through tutorials I've seen people using various different methods for connecting to their database.

I started out without using a DSN and just set all the necessary information (Data Source, Uiser ID, Password, Server, etc.) and connected to the database just fine and then I went ahead and set up a system DSN and connected that way. I've also seen the other way using OLEDB where you specify the driver and set all the other necessary information.

Which way is best? Advantages/Disadvantages to each?

[oracleguy]

One of the main reasons for using DSN-less is if your host charges (most do) for DSNs. For example, my host charges $25 per dsn so thats kinda rediculous when I can code in the connection information for free.

[Spookster]

Good point. The software I am designing may eventually be sold so I suppose I could not expect the people using the software to have to set up a DSN so it would probably be a good idea to just hard code the database connection.

So with that in mind which one of the two DNS-less connections is best?

[raf]

spookster:

two main reasons for going DSN-less:
- the above mentioned
- speed. DSN requires that your systems looks up the databaseadres, which is realy a waste of time.

I just put my connectionstring into a SSI (which makes it easy to switch databases/host.

Which connection? This thread looks in to that.
http://www.codingforums.com/showthread.php?s=&threadid=7097&highlight=Mysql+connection

[dominicall]

If it's a straight choice between DSN and DSN-less connection then I'd def choose DSN-less - for the speed.

The better options still is to go OLEDB - this cuts a chunk out of the processing of the connection which makes it quicker still.

An example for SQL is...

Provider=SQLOLEDB; Data Source=MACHINENAME; " &_
"Initial Catalog=DATABASENAME; User Id=USERNAME; Password=PASSWORD
I agree with raf too - def best way is to put the definition into an include file - I have a general site wide file called global.asp.

Then you can do stuff like this:

Dim location, strConn
location = "live" 'or test
If lcoation = "live" Then
strConn = "Provider=SQLOLEDB; Data Source=LIVEMACHINENAME; Initial Catalog=LIVEDATABASENAME; " &_
"User Id=LIVEUSERNAME; Password=LIVEPASSWORD"
Else
strConn = "Provider=SQLOLEDB; Data Source=TESTMACHINENAME; Initial Catalog=TESTDATABASENAME; "&_
"User Id=TESTUSERNAME; Password=TESTPASSWORD"
End If
Then - when you deploy live all you need to do is to change the location status in the include file for everything to work.

You can use the same If Then Else statement for anything that changes between test and live servers - another good example is the SMTP server to use if you're sending email from the site.

Hope that helps

dominic all :D

[Spookster]

This has been very helpful thanks. Ok now what the difference when using a DNS-less connection where you specify the conneciton string like so:

"Driver={MySQL}; server=localhost; database=db1; username=******; password=******";

where you specify the database driver

or where you specify the string like so:

Provider=SQLOLEDB; Data Source=MACHINENAME; " &_
"Initial Catalog=DATABASENAME; User Id=USERNAME; Password=PASSWORD

Oh and dominicall, couldn't you just specify "localhost" for the location no matter which machine it is on?

[dominicall]

OK - will try and explain...

Your code...
"Driver={MySQL}; server=localhost; database=db1; username=******; password=******";"
uses ODBC to connect to the database - all you're doing is taking out the extra step of the DSN.

The connection string I run...
Provider=SQLOLEDB; Data Source=MACHINENAME; " &_
"Initial Catalog=DATABASENAME; User Id=USERNAME; Password=PASSWORD
is an OLEDB connection which is supposed to be faster - M$ supposedly optimised OLEDB to improve data access speeds.

Interestingly, there's an article on www.adopenstatic.com that has tested the different types of connections - and it seems there's not really that much difference - although OLEDB does show to be slightly quicker.

You can find the article here...http://www.adopenstatic.com/experiments/ConnStringSpeed.asp

I still prefer the OLEDB route as it takes some processing steps out of the way that would be used with ODBC.

You can't use localhost for the machine name as this is just a web access thing for local machines. Mostly, what you'll find with good hosting companies is that the SQL Server is on a separate dedicated server to the web server. I run a similar set-up at home - separate web and sql servers.

Hope that helps.

dominicall

[Spookster]

Ok great. I had originally been using the OLEDB connection but then saw the others and just wondered so I think I will stick with the OLEDB as it seems the easiest to remember and is more readable.

As far as the Data Source though as long as the SQL server is on the same machine I can use "localhost". The thought never occurred to me that the SQL server would be on another machine. I will just set up all those values as variables in the include file anyways and default it to localhost and just put a comment for people if their SQL server is on another machine they need to change that value.

Ok thanks all. This has been most helpful. :thumbsup:

[dominicall]

No problem...

I'm not really sure whether you can use localhost or not with SQL - have always used development servers (even at home) - worth checking it out...

dominicall

[Spookster]

Originally posted by dominicall
No problem...

I'm not really sure whether you can use localhost or not with SQL - have always used development servers (even at home) - worth checking it out...

dominicall

It does work. That's what I had originally used as the Data Source since my SQL server is on the same machine as my IIS.

[dominicall]

OK - cool...

Worth knowing - thanks

dominicall