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
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
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
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
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
We'll also add 2 more columns:
This is the word in the Spanish and English languages. The columns will be of
nvarchar(50) type. This is a string where
the maximum number of characters. Do not confuse it with the
type, where it wouldn't be the maximum size, but the text would be
50 characters long. There are also the
varchar types (without the
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
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
Spanish (Spanish version) and
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
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
1, the second
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
Id is released, it will no longer be used. This is because
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:
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
save the table as
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
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
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
Word table again and set the word categories:
The number is, of course, the primary key in the
1 corresponds to the entry
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.
No one has commented yet - be the first!