Creating a Database Project with C# .NET

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.
When you click Open, you'll see a Data Source Configuration screen appear, if you have Visual Studio Express 2010 or 2010. (2013 users won't see this screen.) Select Dataset and then click the Next button at the bottom. You should then see a Choose Your Database Objects screen. Select Tables, and type a new DataSet name at the bottom. Type ds_employees:
Data source configuration wizard
Click on Finish to return to the Resources screen. All VS users 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 2010 or 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 2010 and 2012 users, you can now scroll down this page a bit, until you come to the section All Visual Studio Users.
However, 2013 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 OK (or click Test Connection first, if you want), and you'll get back to the Settings screen. Take a look at your Value text box:
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 belowEmployeesConnectionString. Type SQL. For the Type, leave it on string. Change the Scope toApplication. 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.