Creating a Database Project with C# .NET

What we're going to do now is to use the Employees database we created in the previous section. We'll add it as a resource to a new project. We can then create a form with buttons that allow us to scroll back and forward through the records in the database. If you didn't manage to create a database then you can use ours. You'll find the Employees database in the extra files here on our site: extra files.


Adding a Database to a Project

Create a new project. Call it EmployeesDatabase. To add a database to your new project, have a look at the Solution Explorer on the right. Locate the Properties item:

Solution Explorer, Properties item

Double click on properties to see a new screen appear. Click on the Resources tab on the left of the new screen:

Resources tab

Click on the Add Resource dropdown list at the top and select Add Existing File:

Add a resource to a C# NET project

When you click on Add Existing File, you'll see a standard Open File dialogue box appear. Navigate to where you saved your Employees.mdf database. (If you didn't create a database, navigate to the one you downloaded from our extra files, here: extra files.) Click Open on Open File dialogue box. You will then see this:

Database added as a resource to a C# NET project

The database has been added to the project. You can see that it has been added by looking at the Solution Explorer on the right.

Have a look at the Properties screen again. Now click on the Settings tab, just below Resources. If you have version 2012 of Visual Studio Express, you'll see this:

The settings tab

A Name, a Type, a Scope and a Value have been filled in for you. The Value is the connection string needed when we connect to the database. The Name will show up in the IntelliSense list a little later. For VS 2012 users, you can now scroll down this page a bit, until you come to the section All Visual Studio Users.

However, 2013 and 2015 users won't see any entries on the Settings page. You'll have to fill them out for yourself. To do that, click into the Name box and type EmployeesConnectionString. From the Type dropdown list select Connection String. Change the Scope to Application. For the Value, click inside the long text box. You'll see a button to the right:

Addng a connection string setting

Click the button to see this dialogue box:

Connection properties

The default Data Source is for an Access database, so we need to change this. Click the Change button to see a new dialogue box:

Change data source to a SQL Server database

Select the option Microsoft SQL Server Database File, and then click OK to go back to the previous screen.

You now need to browse for your MDF database. So click the Browse button to see an Open File dialogue box. Navigate to your project folder (the EmployeesDatabase project that you have open). Double click your Resources folder to see your database:

Browsing for the Employees database

Click on Open and you'll get back to the Connection Properties dialogue box:

Testing the database connection

Click Test Connection and it should report success. In Visual Studio Community 2015, you may get an error when you click Test Connection. If you do, click the Advanced button at the bottom of the Connection Properties dialogue box:

Connection Properties dialogue box

From the Advanced Properties box, select LocalDb from the Data Source drop down box:

Advanced properties dialogue box

Then click AttachDbFilename in the area just above Data Source. Click the button on the right of the text area:

Attach DB Filename

Navigate to where your database is saved, which is in the Resources folder of the project you have open. Click the Open button when you have located your database. Click OK on your Advanced Properties dialogue box. Your Database file name area will then read something like this:

C:\Users\Ken\Documents\Visual Studio 2015\Projects\employees_database\employees_database\Resources\Employees.mdf

(Instead of Ken you'll have the name of your own computer. And instead of employees_database you'll have the name of your Visual Studio project.)

Click Test Connection and hopefully you'll see a message saying Test Connection Succeeded. Click OK to return to your Settings page. The Value area when look something like this:

Data Source properties

 

For Visual Studio Express 2012 and 2013 users, your Value area should look something like this:

Connection string

This is a connection string we can use to connect to the Employees.mdf database. Because we've added it as setting, we don't need to type out the full string above.

 

All Visual Studio users

To gain access to the table in the database, you need something called a SQL String. This takes the form SELECT * FROM tbl_employees. The * symbol means "All records". There's quite a lot you can do with SQL as a language. For our purposes, though, we just want all the data from the database, so we can use a SELECT ALL statement.

Rather than typing the SQL statement in our code, we can add it as a setting. We can then retrieve this settings quite easily.

On the Settings tab, then, click in the Name text box, the one just below EmployeesConnectionString. Type SQL. For the Type, leave it on string. Change the Scope to Application. For the Value, type the following:

SELECT * FROM tbl_employees

Your Settings page will then look like this:

A SQL statement added as a setting

Save your work and close the Properties page. We can now make a start with coding our database project.