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.
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:
Double click on properties to see a new screen appear. Click on the Resources tab on the left of the new screen:
Click on the Add Resource dropdown list at the top and select Add Existing File:
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:
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:
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:
Click the button to see this dialogue box:
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:
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:
Click on Open and you'll get back to the Connection Properties dialogue box:
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:
From the Advanced Properties box, select LocalDb from the Data Source drop down box:
Then click AttachDbFilename in the area just above Data Source. Click the button on the right of the text area:
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 2019\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 (the year will be different, depending on which version of Visual Studio you have):
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:
Save your work and close the Properties page. We can now make a start with coding our database project.