Lesson 1 - Introduction to databases in C# .NET

Welcome to the first lesson of the course on programming database applications in C# .NET. The course deals with advanced issues as simply as possible and with as many examples and illustrations as possible. We'll discuss both the theory and practice and use different approaches available to work with databases in C# .NET. The course describes working with MS-SQL databases in C# .NET. There's also a more detailed course on the MS-SQL database only (regardless of the language).

Why using a database?

You may be asking yourself, why databases? You could just store the data in text files, binaries, XML or come up with some other alternative. You'd get it to work somehow...right? Or wouldn't you?

RDBMS

The term database is actually inaccurate, and in technical literature we can encounter the term RDBMS (Relation DataBase Management System). The database engine (MS-SQL in our case) isn't just a data storage. It's a very sophisticated and optimized tool, that take care of a lot of issues for us and is very simple to use. We use the SQL language to communicate with databases, whose syntax is more or less human-readable sentences. Many extensions have been created above this language, and sometimes we don't even encounter SQL at all. We'll show different approaches in the course and communicate with the database using both SQL and objects only via the LINQ technology.

Along with data storage, many other things need to be managed. We might come up, for example, with security or performance optimizations. But RDBMS does even more, it solves the problem when the same item is being edited by multiple users at the same time, which could otherwise cause inconsistencies in the database. In this case, the RDBMS locks the data and then unlocks it once the writing is done. It also allows us to combine several queries into a transaction when either all the queries in the series are completed or neither of them is executed. It can't happen that only a part of them will be executed. These features of the database engine are summarized by the ACID acronym, let's explain it.

ACID

ACID is an acronym of the words Atomicity, Consistency, Isolation, and Durability. The individual words have the following meanings:

• Atomicity - The operations within a transaction are performed as a single atomic operation. It means, that if any part of the operation fails, the database returns to its original state and no parts of the transaction are executed. A real example would be, for example, transferring money to a bank account. If the money cannot be subtracted from one account, they won't be added to the other account. Otherwise, the database would be inconsistent. If we handled the data transfer by ourselves, this could easily happen to us.
• Consistency - The database state after completing a transaction is always consistent, which means valid according to all the defined rules and constraints. I'll never happen that the database is in an inconsistent state.
• Isolation - The operations are isolated and don't affect each other. If multiple queries happen to need to write into the same row at the same time, they're executed sequentially as in a queue.
• Durability - All data is written to a permanent data storage immediately (usually on a hard drive), in case of a blackout or any other interruption of the RDBMS operation, everything remains as it was before the failure.

So the database (more precisely the database engine) is a black box, that our application communicates with and stores all the data. It's very simple to use and offers data manipulation features we'd hardly be able to provide by ourselves. We don't have to worry about how the data is stored physically, we communicate with the database using the simple SQL query language, see further. Nowadays, it makes no sense to bother with the issue of storing data, we simply go for a database, there is a wide range of them and are mostly free. The database is sometimes referred as to the 3rd layer of the application (the 1st layer is the user interface, the 2nd is the business application logic, the 3rd one is the data layer).

MS-SQL Server

In C# we work almost exclusively with the MS-SQL database (sometimes the technology is referred to as T-SQL as Transact SQL). Theoretically, it's possible to work with any database in C# (e.g. SQLite, MySQL, etc.) However, practically all tools and technologies are optimized for MS-SQL (since it's from Microsoft ) and tools for other databases are not used by many people, their development has ended or they are in alpha versions and the like. An exception is Oracle databases, which Microsoft counts on, but which are intended for large enterprises. MS-SQL is not a bad database and can be used for free. When we choose it, we avoid many problems and application development will be relatively easy.

LocalDB vs. MS-SQL Server

In this course we'll work with the local database (localDB), which can be easily added to the project via Visual Studio itself and will be stored in a single file. It's portable and we don't need a complex service running on the given machine. In the past, this version of the MS-SQL database was referred to as Express, sometimes these types of databases are also generally referred to as embedded or portable.

Alternatively, we can also use a fully-featured MS-SQL Server, and install the SQL Server Management Studio administration tool for creating the database itself. However, this solution is used more on servers.

Preparing Visual Studio

To work with localDB, it's necessary to have the "Data storage and processing" feature set installed in Visual Studio, which together with the database tools also includes a light version of MS-SQL Server Express 2016 LocalDB.

We verify whether we have the given set installed by clicking on the Tools item in the Visual Studio application menu, then we select the "Get Tools and Features" item. The Visual Studio Installer will open for us, with which we can install not only Visual Studio, but we can also use it to install feature sets. Make sure the "Data storage and processing" set is checked, or check it and click "Change":

Relational Databases

MS-SQL is a relational database. This term refers to a database based on tables. Each table contains items of one type. So we can have a users table, an articles table, and another comments table, for example.

We can imagine such a database table as a table in Excel. The users table might look like this:

We store the items as rows, each representing one user in this case. The columns then specify the attributes (properties, if you'd like) the items have. The MS-SQL database is type specific, meaning that each column is of a fixed data type (we distinguish between numbers, characters, short texts, long texts...) and can contain values of only that type. If we want to use a relational database properly, each row in a table should be provided with a unique identifier. The users could have a birth number, but artificial identifiers are used more often. We'll simply assign unique IDs to the users, starting with 1. We'll get to that later.

The word relational refers to a relationship between tables or between entities in one table. But let's leave it for another time and till then, we'll work only with one table at a time.

ADO.NET

ADO.NET is a library that covers technologies for accessing and working with data. It's part of the .NET framework. It contains data providers designed for a specific data source type. So first we need to choose a suitable data provider for our database. We already know that we're going to work with the MS-SQL database, but we should know that C# can also work in the same way with MS Access or Oracle databases, for example.

Data Providers Overview

You can download and install other providers yourself (e.g. MySQL from http://dev.mysql.com/…nnector/net/)

Let's leave today's lesson as an introductory one and install the above-mentioned set of functions in Visual Studio.

In the next lesson, Creating a Local Database in Visual Studio, we'll create a database to connect to from C#.