SQL Express questions many of them...

[Winston]

Hey guys, ok i have so many questions since i'm a total newbie to SQL server.

 

Firstly, i posted this code on another forum we ended up solving it, but i need bit more explanation:

 

String dbpath = Path.Combine(Application.StartupPath, "Database1.mdf");

String conn = @"Server=.\SQLExpress;AttachDbFilename=" + dbpath + ";Database=database1;Trusted_Connection=Yes;";

SqlConnection con = new SqlConnection(conn);

con.Open();



SqlCommand comm = new SqlCommand("SELECT * FROM Student", con);

SqlDataReader reader = comm.ExecuteReader();

while (reader.Read())

{

MessageBox.Show("Retrieved ID:" + reader["ID"] + ", Name: " + reader["Name"]);

}

 

 

So firstly the initial problem was i couldn't even make it past the connection string itself,

 

I kept getting this exception:

 

Directory lookup for the file "C:\Documents and Settings\Winston Pang\Desktop\WindowsAppplication2\WindowsApplication2\bin\Debug\Database1.mdf" failed with the operationg system error 5(Access is denied.).

 

Could not attach file 'C:\Documents and Settings\Winston Pang\Desktop\WindowsAppplication2\WindowsApplication2\bin\Debug\Database1.mdf' as database 'database1'.

 

I didn't do anything majorly complicated.

 

SQL Express was brand new installed.

 

I just whipped up a new C# project, added a button and added a new .MDF file, created a Student table with 3 fields, then made the code above, and got the exception.

 

 

There was a hacky fix, someone told me to use user instance = true in the connection string, that fixed it, but what does this attribute do, i heard it's only in the SQL Express edition? And is this the only way you conenct locally to a .mdf file?

 

I also want to know what the Database and Initial catalog values come from for the connection string, are they arbitrary values you make up?

 

Also, finally, if i wanted to set a user id and password on the .mdf file, how does that work?

 

Thanks for everyones help!

[PlausiblyDamp]

The Database (or Initial Catalog) entries specify the name of the database on the server to connect to; in your example you are conecting to a database called database1.

 

The 'user instance = true' isn't really a hack at all, normally SQL Server runs as a background service, SQLExpress being a stripped down version also allows this behaviour. One of the problems is that in a shared hosting environment (like an ISP who hosts your site) you may not have access to a SQL server running as a service or if you do various permissions need to be configured to make things work - hence the error you were getting originally.

SQLExpress allows you to treat the database (.MDF file) as a stand alone file (similar to how MS Access works) and therefore avoid the configuration problems with SQL server - that is what user instance is for.