Connecting to a SQL Server Express Database with C# .NET

<< Part of an ongoing lesson - first part is here >>

 

Close down the project you have open, and click File > New Project to create a new one.

To find the MDF database you created in the previous section, look in the Projects folder of Visual Studio 2010 (or whatever version you are using). Double click the name you gave to your project and you should see the MyWorkers.mdf file. (If you still can't find it, do a search using that file name.)

Copy it somewhere like the root of C:\ if you're a XP user. This is so that you're not working with very long file paths. Once you have copied it over to your C drive, XP users would then only have a path like this:

C:\\MyWorkers.mdf

If you leave it where it is, the file path would be this:

C:\\Documents and Settings\\pc_name\\My Documents\\Visual Studio 2005\\Projects\\cSharp\\dbtests\\MyWorkers.mdf

Which is a bit long and unwieldy!

For Vista and Windows 7 users, copy the database file to your Documents folder. The file path would then be:

"C:\\Users\\Owner\\Documents\\MyWorkers.mdf"

If you didn't create a SQL Server Express database then you can use ours. It is amongst the files you downloaded at the start of the course, in the databases folder. You will also find an Access version of the same database, just in case the SQL Server Express one doesn't work for you.

 

How to Connect to a SQL Server Express Database

To connect to a database using SQL Server Express, you first need to set up a SQL Connection object. You then need something called a connection string to tell C# where the database is.

To set up a connection object, double click the blank form. Just outside of the Form Load event add the following:

System.Data.SqlClient.SqlConnection con;

Your coding window should look like this:

Inside of the Form Load event, add the following:

con = new System.Data.SqlClient.SqlConnection();

When the form loads, a new SQL Connection object will be created with the name of con. Here's what your code should look like:

Now that we have a connection object, we can access the ConnectionString property. To see what the string should be, click the Data menu item at the top of the C# .NET software. Then select Show Data Sources. This will display a new tab where the Solution Explore is:

Click Add New Data Source and you'll see a Wizard appear. On the first screen, make sure Database is selected and then click the Next button get to the Choose your Data Connection step. Click the New Connection button, and you'll see the following:

The Data Source area has a Change button. Click this to see the following:

Select Microsoft SQL Server Database File (SqlClient). Then click OK.

Click the Browse button and browse to the location where you saved your database. The Add Connection box will then look something like this:

Click the Test Connection button to see if everything is working. Then click OK to get back to the Choose your Data Connection step. Expand the Connection String area, and the dialogue box should look like this:

Highlight the entire string (XP users):

And this for Vista or Windows7:

Now hold down the CTRL key on your keyboard. Press the letter C to copy the string. Go back to the Form Load event in your coding window and press CTRL then the letter V to paste the connection string. You'll have lots of red underlines, but don't worry about that. Cancel the wizard, because we're done with it - we only wanted the connection string!

Just after the SqlConnection( ) line, type the following:

con.ConnectionString

Type an equals sign then a double quote:

con.ConnectionString = "

Now move your connection string up to just after the quote mark:

con.ConnectionString ="DataSource=.\SQLEXPRESS; AttachDbFilename =C:\MyWorkers.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

At the end of that long connection string, type another double quote mark. End the line with the usual semicolon. Your coding window will then look something like ours below (we've got word wrap switched on):

Vista/ Windows 7:

Notice that we still have error underlines in the connection string. There are two of them in the XP code, and five for Vista/Windows 7, all after the backslash character. It is the backslash character that is the problem. This is considered a special character in C# programming, so it needs to be escaped. To escape a backslash character (or any other character) just type another backslash before it:

There are now four backslash characters in the code above, two before SQLEXPRESS, and two before MyWorkers.mdf. Add more backslash characters to your own code to get rid of the errors.

All the code does, though, is to tell C# where the database is, and sets a few properties. You can add more database properties here, as well. For example, if the database required a user name, you'd add this:

User ID=your_user_name;

After your connection string, you can then try to open up a connection to the database. Again we use our con object:

con.Open();

When the connection is open, we'll be writing code to get all the records. Once we've done that, we can close the connection:

con.Close();

Add two message boxes to your code, and your coding window should look like ours:

Run your programme and test it out. You should see the "Database Open" message appear first, followed by the "Database closed" message. Then the form should load.

Congratulations! All that hard work and you have now made a connection to your SQL Server Express database!

In the next lesson, you'll learn how to connect to an Access Database.

<-- Create a Database | Connect to an Access Database -->

<--Back to the C# .NET Contents Page

View all our Home Study Computer Courses