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.

If you created a SQL Server Express database in the previous section, copy and paste it somewhere handy, like the root of C:\. This is so that you're not working with very long file paths. Once you have copied it over to your C drive, you'd 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!

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. If you were using an Access database, you'd click this button and select Microsoft Access Database File. The default is for a SQL Server Database. That's what we want to connect to, so leave this as it is.

Click the Browse button and browse to the location where you saved your database. The Add Connection box will then look 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:

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 lost 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):

Notice that we still have error underlines in the connection string. There are two of them in the image above, both after the backslash character. It is the backslash character that is the problem. This is considered a special character in database 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. Change your code to match.

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