Lesson 2 - Creating a Local Database in Visual Studio

In the last lesson, Introduction to databases in C# .NET, we made an introduction to relational databases and installed Microsoft SQL Server.

In today's C# .NET tutorial we're going to create a database with which we'll communicate for the rest of the course.

Creating the Database

There are many ways to create a local database. As a tool we can use either SQL Server Management Studio or Visual Studio directly. The easier way is definitely to create the database directly in Visual Studio, so we'll choose this approach. If you want to use Management Studio, you can use the article Creating a database in SQL Server Management Studio.

In Visual Studio, we'll create a new project, a console application, which we'll name VocabularySQL.

Adding a Local Database

To add a database, we right-click on the project in Solution Explorer and select Add -> New Item. In the newly opened window, we find the item "Service-based Database" and name it VocabularyDB. Our test application will be a simple volcabulary. Confirm the new item with the Add button:

In order to use the database, we must first connect it to our application. In the Data Sources window (menu View -> Other Windows -> Data Sources) click on "Add New Data Source":

A new window will open for selecting the data source. Select "Database" and confirm with "Next":

On the next page, leave "Dataset" selected and confirm with "Next" again. The page for selecting the data connection will be displayed. We'll select our VocabularyDB database:

After confirming with the "Next" button, a page will appear asking if we want to save the ConnectionString, which we'll later use to connect to the database, in the project settings. Check that we want that, leave the name as default and confirm with the "Next" button again:

On the last page, we leave the DataSet name as VocabularyDBDataSet and click on "Finish" to complete the connection:

Creating a Table

The tree structure of the local server on which the database is located can be viewed either via the "Server Explorer" window or via the "SQL Server Object Explorer" window. We'll open the "SQL Server Object Explorer", as it is (as the name suggests) intended for working with SQL databases. We'll expand the item SQL Server -> (localdb)\MSSQLLocalDB > Databases. In this folder we should see our database, expand it along with its "Tables" item, where we only have system tables for now.

We talked about tables last time. We know that this is how data is stored in a relational database. We'll store words in our database. First we have to create a new table where we'll define the columns, i.e. the properties that a word has. Right-click on "Tables" and select "Add New Table":

Visual Studio opens the table designer. This designer has a window divided into three parts. In the upper half we have the columns of our table with their data types and other important properties. Below is the resulting T-SQL code, which will run and create the database when we'll be finished.

As we've already said, each entry in the database (that is, a row in a table) should have a unique identifier (a column which value is unique for each row). We use the primary key for this purpose. It's an ordinary column, most often named simply Id and will be of the int type. We'll write column names with the first letter capitalized.

We'll also add 2 more columns: Spanish and English. This is the word in the Spanish and English languages. The columns will be of the nvarchar(50) type. This is a string where 50 is the maximum number of characters. Do not confuse it with the nchar type, where it wouldn't be the maximum size, but the text would be always 50 characters long. There are also the char and varchar types (without the n), these don't support Unicode encoding and therefore we won't use them. So we'll always use the nvarchar type for short texts. Finally, we'll add the last column, Difficulty, indicating the difficulty of the word. It'll be of the int type.

If the similarity of a database table and a C# class crossed your mind, then you're right. The definition of a table is almost identical to the definition of a class, we simply add properties which the given entity has. The rows written in the table can then be understood as individual class instances. So it's like we created a Word class, which had the Id, Spanish (Spanish version) and English (English version) properties. We'll also work with the database like this, but let's not peek much further. Let's go back to the Id column, right-click on it and select Properties. In the Properties window, we'll set the Is Identity option to True (we need to expand Identity Specification):

This determines that the Id column is always unique. Visual Studio also set the Identity Increment and Identity Seed values to 1 automatically. This means that the first word in the table will have the value Id of 1, the second 2, etc. The database will automatically assign a higher Id to each newly inserted word, so it takes care of the uniqueness itself.

Ids are still growing, even if we delete a word and its Id is released, it will no longer be used. This is because recycling old Ids would cause problems.

If this column has not yet been set as the primary key for our new table, then right-click on the column name and select "Set Primary Key":

Each table should have a primary key, otherwise we won't be able to identify a specific entry and some technologies (e.g. Entity Framework) could have trouble working with such a table.

Finally, we'll rename the table to Word. We'll achieve that by rewriting the current name (Table) in square brackets at the end of the first line in the T-SQL code. We name the tables in capital letters again and in singular, as if it was a class:

After renaming, we click on the "Update" button. In the window that opens, we click on "Update Database" to save the changes.

We have the database ready. Let's create some more test data so that it's not empty. Right-click on the table and select "View Data":

Let's add just a few words now. Id will be assigned automatically, so just enter the Spanish and English variants and difficulties. I added only a few, you can add more with different difficulties, so that you have as much data to test as possible:

Extension

If creating a database wasn't a big problem for you (which it shouldn't ), you can add a second table to it. Thanks to this, we'll be able to show even more advanced queries via multiple tables further. If you prefer to start easily, you can skip this step as well as a few queries below.

In the same way add a Category table to represent the category of words. Again, we'll give it the Id column, set Identity to True and then make it the primary key. As for the other columns, there will be only one, again of the nvarchar(50) type named Title. This will be the category title (eg "Computers", "Animals" or "Other"). We'll save the table as Category:

Again, we'll create test data:

We'll now add a relation of the category to the word, sometimes called a foreign key. Let's go back to our Word table and add another column named CategoryId. It'll be of the int type. In Properties we'll set "Default Value or Binding" to 1. This is the default value. Next, in the right column of the designer, we'll right-click on the item "Foreign Keys", select "Add New Foreign Key" and name it "FK_Word_Category", for example:

This foreign key (relation) was also generated in the T-SQL code:

Here we set the Word table to bind to the primary key of the Category table, which is Id, using the CategoryId column:

Now the database knows that if, for example, a word in the CategoryId column has a value of 1, it's bound to the first category. We save everything with the "Update" button. Edit the records in the Word table again and set the word categories:

The number is, of course, the primary key in the Category table, so 1 corresponds to the entry "Computers", 2 "Animals" and 3 "Other".

In the next lesson, Approaches to working with relational databases in .NET, we'll introduce approaches that we can use to communicate with databases from C# .NET.