Installation
You know I thought this problem went away with the beta, but it apparently is still there. I had installed Visual Studio 2005 Professional, used it for a while and then tried to install SQL Server 2005 Development Edition. I never get to see the Management Studio. It seems that my computer always wants to use SQL Express.
I can see the Management Studio if I install SQL Server 2005 Development Edition without previously installing Visual Studio 2005 Professional. I haven’t yet tried installing Visual Studio 2005 Professional after installing SQL Server 2005 Development Edition to see if they work together.
Here’s the version of Visual Studio 2005 Professional that I have.
Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727
Installed Edition: Professional
Accessing Data with SQL Express
I have been using Visual Studio 2005 for a while now, but just recently tried accessing a database with SQL Server Express. I had a rude awakening. I couldn’t use the method I had become familiar with. I finally got this to work, but I didn’t find the documentation trail easy. No one ever talks about this; everyone must have figured it out a long time ago, I guess.
Here’s what I tried to do that did not work. I got error messages like cannot login to the database or something cryptic about named pipes. I didn’t pursue the details of the error message. I mean I might have to later, but experience has told me that it is more efficient to figure out how to do something than to analyze why the method you tried did not work. Sometimes it’s frustrating to do that, but the pursuit of knowledge can get in the way of getting the job done. My academic friends are now shaking their heads and wagging their fingers.
I should mention that I can access the data in the database just fine with Server Explorer
Anyway, here’s what I tried to do (that did not work)…
SqlConnection cnn = new SqlConnection();
cnn.ConnectionString = "Data Source=beachparty;" +
"Initial Catalog=Northwind;" +
"Integrated Security=SSPI";
SqlCommand cmd = cnn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT CompanyName FROM Customers " +
"ORDER BY CompanyName";
cnn.Open();
Here’s what I did instead that worked…Is there a better way? I’m sure there is. If you know of one, please don’t keep it a secret!
I chose Data-Add New Data Source… from the Visual Studio menu, I chose Database and clicked Next.
Look at that connection string. Not what I expected. Click Next again.
I click Next and get asked what I want in my Dataset. I don’t really want a Dataset, so I choose nothing and click finish. The result is that I get a NORTHWNDDataSet.xsd, which I then delete. But what I gained that I wanted was an app.config file, which has the appropriate connection string.
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
</configSections>
<connectionStrings>
<add name="StepByStep7_7.Properties.Settings.NORTHWNDConnectionString"
connectionString="Data Source=.\SQLEXPRESS;
AttachDbFilename="C:\SQL Server 2000 Sample Databases\NORTHWND.MDF";
Integrated Security=True;Connect Timeout=30;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
The point now is how to take advantage of that new connection string stored in the app.config. I noticed that under my project (called AppA) in the Solution Explorer, I see a section called Properties. I can expand that and see a file called Settings.Designer.cs.
This file resides in the namespace AppA.Properties, so I put a using AppA.Properties at the top of my winform. I also notice that the file has a property called NORTHWNDConnectionString.
public string NORTHWNDConnectionString {
get {
return ((string)(this["NORTHWNDConnectionString"]));
}
}
So I replace the connection string assignment
cnn.ConnectionString = "Data Source=beachparty;" +
"Initial Catalog=Northwind;" +
"Integrated Security=SSPI";
with
Settings mySettings = new Settings();
cnn.ConnectionString = mySettings.NORTHWNDConnectionString;
Hmmm… now things are working. All the code above is in the event handler for the button labeled Get Customers. Click on the button and fill the listbox.
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
lbCustomers.Items.Add(dr.GetString(0));
}
