Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

sqlexpress connection string

Posted on 2006-05-29
18
Medium Priority
?
1,136 Views
Last Modified: 2010-05-19
Hi all...

1)
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=E:\mydb.MDF;Integrated Security=True;Connect Timeout=60;User Instance=True"

2)
connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=mydb;Integrated Security=True"


what is the difference in these .. which one is best .. and when shall i use each one of them ?
what about deployment .. are they the same ?

thanks.

0
Comment
Question by:Misbah
  • 8
  • 6
  • 3
17 Comments
 
LVL 9

Author Comment

by:Misbah
ID: 16786262
sorry .. assume :
Data Source=.\SQLEXPRESS

in both
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16788398
The second one is the best because it is refering to the database name not the database file name.
It is not good to refer to the database Data file directly.
0
 
LVL 9

Author Comment

by:Misbah
ID: 16788617
ok.. thanks .. but i need more information about this..

what is the difference ?? there must be a situation where one is more appropriate than the other ?? right ??

for example .. in the first one.. will the database file be attached to the server every time the programs run .. and
deattached when the program is finished ??

I need some explanation of how these things work

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 28

Expert Comment

by:imran_fast
ID: 16789119
Check this link.
==========

www.connectionstring.com 
0
 
LVL 9

Author Comment

by:Misbah
ID: 16789287
what is this !!

did u check it ur self ???

I couldn't find any information there ... it has only sponsored links !!! ???

and plz.. I am here to save my time and increase my productivity
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16789918
Wow how come  this looks totally different now.

ok this is what you need to know.

which eliminates the need to have a DBA attach the database files to a server before they can be used. When the AttachDBFilename keyword is included in a connection string, the specified file is attached to the SQL Server instance and the client is connected to the newly attached database
The |DataDirectory| is a shortcut for the directory where the program opening the connection lives. To attach a file in a different directory, you must provide the full path to the file. The log file in this case is named Database1_log.ldf and is located in the same directory as the database file. If the database file is already attached to the SQL Server instance, the connection is opened to the existing database.

This is a great option because if you are an administrator, you can attach and connect to a database file by specifying the file name in the connection string in your application. Many developers run as administrators on their systems so AttachDBFilename works well for them. The problem is that Microsoft strongly recommends NOT running as an administrator because this reduces the amount of damage that can be inflicted by a virus. What we need in this case is a way to use AttachDBFilename without being a member of the Windows Administrators group. The solution is the User Instance feature.



But if you have the database already attached to your sql server than why using this option and this option is extended only in .netframework 2.0
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16900978
Do you need further assitance  ?
0
 
LVL 9

Author Comment

by:Misbah
ID: 16902281
well ..
my friend u gave me links and huge material to read .. I just need a short answer from an expert who really understand this .. in ur first post u said that the second option is the best .. and in a next post u said the frst is great ... so I am confused now !!!!

my question is still not answered.. :

what is the difference in these (performance, security, deployment .....) .. which one is best .. and when shall i use each one of them ?
what about deployment .. are they the same ?
0
 
LVL 4

Expert Comment

by:kenpem
ID: 16977872
imran gave you the answer in his very first comment.

The first form is bad. Do not use it.

The second form is good. Use it.

Is that short enough?
0
 
LVL 9

Author Comment

by:Misbah
ID: 16978384
ok .. thanks Kenpem ..

your answer is too short .. it does not  really cover my question above :)

what is the difference in these (performance, security, deployment .....) .. which one is best .. and when shall i use each one of them ?

u answered which one is the best ... but based on what !!!

I need a good explanation .. no problem how long or short it is .. but remeber I don't like reading 15 pages of MSDN to get an answer that can be in only 5 lines :)
0
 
LVL 4

Accepted Solution

by:
kenpem earned 750 total points
ID: 16978413
Unless you have a specific need to directly access the data via its file name as in

connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=E:\mydb.MDF;Integrated Security=True;Connect Timeout=60;User Instance=True"

it is ALWAYS better to just refer to the database, as in

connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=mydb;Integrated Security=True"

The database could move physical location. The file could have been renamed, etc. Directly referring to the file name could possibly allow you to run a few queries on an otherwise unattached database file, but I don't see how that could be useful - you are bypassing all sorts of controls that SQL puts in place. You would have to be running at an administrative level for this to work, and this is generally not a good idea.

Performance: there will be a time penalty on initial connection while the file is attached and checked, but otherwise there should be no appreciable difference.

Security: since you can only do this as an adminitrative-level user, you've bypassed most of the security features that SQL would otherwise assist with.

Deployment: will the application absolutely KNOW where the physical data file is going to be? And where it ALWAYS will be, even years in the future? If the answer is not an emphatic "YES", then you can't really consider using the direct-filename method - any deployed applications will need updating if the file is ever moved or renamed. If the connection is based simply on db name, there is no such problem.

In summary, I cannot imagine when I would ever prefer to use the direct-filename connection method.

Hope this helps!
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16978498
hi kenpem,
can you post any comment in below link so that i can close that

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21898249.html
0
 
LVL 9

Author Comment

by:Misbah
ID: 16978545
ok thanks :)
0
 
LVL 4

Expert Comment

by:kenpem
ID: 16978962
Dr-H, thanks for the points. However, imran_fast actually earned most of them - can you re-allocate them to him? Or at least split them?
0
 
LVL 9

Author Comment

by:Misbah
ID: 16979022
yes.. actually .. i was planing to split  the points .. but since i am new here .. i don't know how to do this ..
when I clicked 'accept answer' .. I didn't see  the spilt options !!

I will check now again .. to see how to do this .
0
 
LVL 9

Author Comment

by:Misbah
ID: 16979044
I could not find a way .. plz .. tell me how to do it
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question