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

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!