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:UsersKenDocumentsVisual Studio 2019Projectsemployees_databaseemployees_databaseResourcesEmployees.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.
back to the c# net contents page