Hans-Petter Halvorsen
A Database is a structured way to store lots of information. The information is stored in different tables.
Some of the most popular Database Systems today are:
We will focus on SQL Server from Microsoft.
Video: Introduction to Database Systems:
This video is also available on my Youtube Channel (Industrial IT and Automation).
SQL Server is a Database System from Microsoft. SQL Server comes in different editions, for basic, personal use SLQ Server Express is recommended because it is simple to use and it is free. Latest version is SQL Server 2019.
Download Software:
Download SQL Server Express Edition + Management Studio (You need to install both)
SQL Server Installation - Step by Step (In this guide SQL Server 2016 is used, but the installation process is almost identical in SQL Server 2019)
Video: SQL Server:
This video is also available on my Youtube Channel (Industrial IT and Automation).
During the setup of SQL Server you should select "Mixed Mode" (i.e., both "SQL Server Authentication" and "Windows Authentication") and enter the password for your sa user.
"Windows Authentication" is the default option during installation, so make sure to "Mixed Mode" (i.e., both "SQL Server Authentication" and "Windows Authentication") and enter the password for your sa user.
In WinForm desktop applications you should put the Connection String in the App.config file, while for ASP.NET Core applications the Connection String should be placed in the in the appSettings.json file.
SQL Server Authentication
Using "SQL Server Authentication" the Connection String looks like this:
DATA SOURCE=<SQL Server Name>;DATABASE=<Database Name>;UID=sa;PWD=<Your Password>;
Replace <SQL Server Name> with the name of your SQL Server, typically "<YourComputerName>\SQLEXPRESS" if you are using SQL Server Express.
UID is a SQL Server user, here you can create your own SQL Server user inside SQL Server Management Studio or use the built-in sa user (sa=System Administrator). During the setup of SQL Server you need to select "Mixed Mode" and enter the password for your sa user.
It may look something like this:
DATA SOURCE=DELLPCWORK\\SQLEXPRESS;DATABASE=MEASUREMENTS;UID=sa;PWD=Password123;
Localhost:
If you dont know the name of your PC or if you use multiple PC, it may be a good idea to use "LOCALHOST" instead of your real computer name (assuming the application and the database in located on the same computer).
DATA SOURCE=LOCALHOST\\SQLEXPRESS;DATABASE=MEASUREMENTS;UID=sa;PWD=Password123;
You can also turn on "SQL Server Authentication" in SQL Server Management Studio (SSMS) after installation of SQL Server.
To change security authentication mode, do the following steps
In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.
In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted. Or just restart your computer.
Then to enable the sa login, do the following steps:
In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
On the General page, you might have to create and confirm a password for the login.
On the Status page, in the Login section, click Enabled, and then click OK.
Note! You have to restart your computer afterwards (well, it is enough to restart the “Sql service...”) in order to work.
Windows Authentication
Using "Windows Authentication" the Connection String looks like this:
DATA SOURCE=DELLPCWORK\\SQLEXPRESS;DATABASE=MEASUREMENTS;Integrated Security = True;
Localhost:
If you dont know the name of your PC or if you use multiple PC, it may be a good idea to use "LOCALHOST" instead of your real computer name (assuming the application and the database in located on the same computer).
DATA SOURCE=LOCALHOST\\SQLEXPRESS;DATABASE=MEASUREMENTS;Integrated Security = True;
Here you can read more about Connection Strings.
SQL (Structured Query Language) is a database computer language designed for managing data in relational database management systems (RDBMS).
Tutorials:
Structured Query Language (SQL) (PDF)
Additional Resources:
Structured Query Language - Basic SQL Training from Khan Academy
SQL Tutorial - Basic SQL Training from w3schools.com
Views, Stored Procedures and Triggeres are iportant components for inserting, updating, retrieving and manipulationg data in a database.
Video: Database Views and Stored Procedures:
This video is also available on my Youtube Channel (Industrial IT and Automation).
Video: Database Modelling with ERwin:
This video is also available on my Youtube Channel (Industrial IT and Automation).
ADO.NET is the core data access technology for .NET languages.
System.Data.SqlClient (or the newer Micrsoft.Data.SqlClient) is the provider or namespace you typically use to connect to an SQL Server.
SQL Server with C# Windows Forms App (YouTube) + PowerPoint (PDF)
Below you find some resources for ASP.NET Core.
Video: ASP.NET Core - Database Communication:
This video is also available on my Youtube Channel (Industrial IT and Automation).
Video: ASP.NET Core - CRUD Application:
This video is also available on my Youtube Channel (Industrial IT and Automation).
Additional Resources:
Introducing the new Microsoft.Data.SqlClient
Working with the new Microsoft.Data.SqlClient (Microsoft, Channel 9)
Here you can download the examples in Web Programming | ASP.NET Core (Textbook, PDF):
Entity Framework Core is an Object-Relational Mapper that simplifies working with relational databases using strongly-typed .NET objects.
Entity Framework Core 101 (Microsoft, Channel 9) - 5 Videos - Getting started with EF, building an ASP.NET Core Web Apps with EF Core.