1 Introduction

Make sure to fulfill the Checklist (PDF) when you work with the assignment and the technical report.

In this assignment we will learn about databases, design of database systems, implementation of database systems. We will also learn Structured Query Language (SQL). In this assignment we will create a Datalogging and Monitoring System. We will use erwin Data Modeler, SQL Server, LabVIEW and Visual Studio. More Videos.

User Case Scenario

Here you see a typical User Case scenario for such a Datalogging and Monitoring System:

1.1 Hardware

You will need the following hardware:

TC-01 Thermocouple Device
USB-6008 + TMP36 and Thermistor Temperature Sensors
PT-100 Device - Wiring Diagram
RFID Reader - If you want to create, Login/2FA functionality, etc.

1.2 Software

You will need the following software:

2 Database Systems

A Database is a structured way to store lots of information. The information is stored in different tables. We have many different database systems today, such as SQL Server, MySQL, etc. We will use SQL Server from Microsoft.

Resources:

Introduction to Database Systems (YouTube)

2.1 Structured Query Language

SQL (Structured Query Language) is a database computer language designed for managing data in relational database management systems (RDBMS). We can use SQL to insert data into the database, retrieve data that is stored in the database, we can update the data that is stored in the database, and we can delete it.

Resources:

Structured Query Language (SQL) (PDF)

SQL Tutorial - Basic SQL Training from w3schools.com (Recommended!)

SQL Server and Structured Query Language (SQL) (YouTube) + PowerPoint (PDF)

3 Database Design

3.1 Database Design with erwin Data Modeler

erwin Data Modeler is a Database Design and Modelling Tool.

erwin Data Modeler Home page

Download erwin Data Modeler Academic Edition

The Academic Edition is for academic use only. Note! It is free, but you need to apply for it - so it may take 1-5 days before you get it!

erwin Data Modeler - Academic Edition (YouTube) + PowerPoint (PDF)

Additional Resources:

Database Design and Modeling with erwin Data Modeler (YouTube) + PowerPoint (PDF)

SQL Server and Structured Query Language (SQL) (YouTube) + PowerPoint (PDF)

4 SQL Server

SQL Server is a Database System from Microsoft. SQL Server comes in different editions, for basic, personal use SQL Server Express is recommended because it is simple to use and it is free.

SQL Server consists of a Database Engine and a Management Studio. The Database Engine has no graphical interface - it is just a service running in the background of your computer. The Management Studio is graphical tool for configuring and viewing the information in the database.

Download SQL Server Express Edition + Management Studio (You need to install both)

Note! Select "Custom" installation when installing SQL Server Express and then it is important that you select "Mixed Mode" during the installation wizard, and make sure to remember the "sa" Password (write it down). "sa" is short for System Administrator.

Resources:

SQL Server 2022 Express Installation (YouTube)

Introduction to SQL Server (YouTube) + PowerPoint (PDF)

SQL Server and Structured Query Language (SQL) (YouTube) + PowerPoint (PDF)

Database Views and Stored Procedures (YouTube) + PowerPoint (PDF)

SQL Server Connection Strings (PDF)

5 LabVIEW

LabVIEW is a graphical programming language, and it has powerful features for simulation, control and DAQ applications.

Typically engineers often create simple LabVIEW VIs that eventually grow out of control, because they don't have the proper structure and best practices. The solution to this problem is organizing your code and data in a way that enables modularity, readability, and reuse.

Some examples are:

Intuitive GUI (Front Panels): Add Units, Use Controls and Indicators from the same Palette, Resizing the Window size, Don’t use “strong” Colors, etc.
Structured Code (Block Diagram): Make and use SubVIs, Avoid Spaghetti Code, Resizing the Window size, Use Labels, Always use Project Explorer, use State Machine, etc.

Resources:

LabVIEW Fundamentals (Website)

LabVIEW Programming Guidelines (YouTube) + PowerPoint (PDF)

LabVIEW State Machine (YouTube)

LabVIEW in Automation (Website)

5.1 DAQ in LabVIEW

LabVIEW has good support for Hardware and DAQ.

To use the TC-01 Thermocouple device or the USB-6008 DAQ from National Instruments, you need to install the NI-DAQmx driver.

Here you find information about the TC-01 specifications. Here you find the TC-01 User Guide.

Make sure to read the User Guide and Specifications for USB-6008 and USB-6008 Pinout.

Resources:

DAQmx in LabVIEW (YouTube) + PowerPoint (PDF) - Communicate with NI DAQ Devices in LabVIEW. The tutorial shows different ways to use the DAQmx functionality in LabVIEW with practical LabVIEW examples.

LabVIEW DAQ and I/O Modules (Website)

5.2 Database Communication in LabVIEW

You may also use LabVIEW for storing data from Sensors into an SQL Server Database.

Resources:

Database Communication in LabVIEW using LabVIEW SQL Toolkit (YouTube) + PowerPoint (PDF)

LabVIEW Database Connectivity Toolkit is included with LabVIEW professional, but it is cumbersome to use. It is recommended that you use the LabVIEW SQL Toolkit instead. This Toolkit is very simple to use. Follow the installation instructions in the ReadMe.txt file inside the Zip file.

Below we see a practical example:

In the example above, an ODBC connection has been configured in advance.

ODBC:

ODBC (Open Database Connectivity) is a standardized interface (API) for accessing the database from a client. You can use this standard to communicate with databases from different vendors, such as Oracle, SQL Server, etc. The designers of ODBC aimed to make it independent of programming languages, database systems, and operating systems.

In Windows 10 you find the "ODBC Data Source Administrator" tool here: Control Panel → Administrative Tools → Data Sources (ODBC). Then click the "Add..." button in order to create an ODBC connection to your database.

Note! Make sure to use the 32 bit version of the ODBC Tool!

Connection String:

An alternative to ODBC is to type directly type your Connection String for your Database. The Connection String looks like this (just replace the text MyODBC with the text below):

PROVIDER=SQLOLEDB;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:

PROVIDER=SQLOLEDB;DATA SOURCE=DELLPCWORK\SQLEXPRESS;DATABASE=MEASUREMENTS;UID=sa;PWD=Password123;

5.3 LabVIEW Database Logging Example

Here we will create a simple Database used for Logging in SQL Server and a basic LabVIEW Application for Logging Data from LabVIEW into SQL Server.

In this example the SQL Server function getdate() is used. This function just makes sure that the current timestamp is inserted into the "TagTimeStamp" database column.

Decimal Numbers in SQL:

Using Decimal Numbers in SQL can cause problems. Assume we want to insert the Value "22,5":

insert into TAGDATA (TagValue, TagTimestamp) values (22,5, getdate())

Here, SQL assumes you try to insert 3 values into 2 columns since SQL uses "," as a separation symbol, and you will get an error running this statement.

If we instead wite it like this:

insert into TAGDATA (TagValue, TagTimestamp) values (22.5, getdate())

Then it will work without problems.

Format Into String:

Using %.; in front of the string means that "." will be used as Decimal Point. Then we will avoid the decimal problem illustrated above.

%2.1f means that this is replaced with the value that comes from the Temperature control with one decimal value. “f“ means it is a floating point value

Resources:

LabVIEW Database Logging Tutorial

LabVIEW Database Logging Code Example

Here you see an improved version:

Datalogging in LabVIEW with SQL Server (YouTube) + PowerPoint (PDF) - This Tutorial takes the basic LabVIEW Datalogging Example shown and Adding Value by using a State Machine, retrieves available Sensors from the Database, stores the Database Connection in a File, etc. Take a look to get some good ideas. You can also download the LabVIEW Source Code.

6 Visual Studio and C#

Microsoft Visual Studio is an integrated development environment (IDE) from Microsoft. It can be used to develop console and graphical user interface applications along with Windows Forms applications, web sites, web applications, and web services in both native code together with managed code for all platforms supported by Microsoft Windows.

6.1 Database Communication in C#

To communicate with SQL Server from C# you typically use ADO.NET.

SQL Server with C# Windows Forms App (YouTube) + PowerPoint (PDF) - This Tutorial goes through how to create a basic Windows Forms Database Application in Visual Studio/C# that communicates with an SQL Server.

6.2 Plotting Data in Windows Forms Applications

Plotting Data in Windows Forms is something you often want to do. The built-in Chart Control for Windows Forms is no longer supported by Microsoft in the latest .NET versions. So, we may need to find and use alternative solutions. If you still want to use the built-in Chart Control for Windows Forms, you need to select the "Windows Forms App (.NET Framework)" Template. If you choose the newer "Windows Forms App" Template, the built-in Chart Control is no longer supported. In that case, you may use the ScottPlot Plotting Library which is explained in the Tutorial below.

Plotting Data in Windows Forms using ScottPlot - ScottPlot is a free and open-source Plotting Library for .NET

6.3 ASP.NET Core Web Applications

ASP.NET is an open source web framework, created by Microsoft, for building web apps and services using the .NET Framework or the .NET Core. We have both ASP.NET and ASP.NET Core. ASP.NET Core is the new approach built on .NET Core.

Introduction:

ASP.NET Core - Hello World Application (YouTube)

ASP.NET Core - Introduction (YouTube) + PowerPoint (PDF)

Database Communication in ASP.NET Core:

ASP.NET Core - Get Data from Database (YouTube) + PowerPoint (PDF) - Here you learn the basics when it comes to how to get/retrieve data from a SQL Server database.

ASP.NET Core - Database Communication (YouTube) + PowerPoint (PDF) - Here you learn how to get/retrieve data from a SQL Server and present the data in a table in a webpage. You also learn how we can put the Connection String into the cinfiguration file appSettings.json.

ASP.NET Core - CRUD Application (YouTube) + PowerPoint (PDF) - Here you will learn to create a "Data Management" Application that Creates/inserts, Reads, Updates and Deletes (CRUD) data from the database.

Monitor and View your Data with Charts:

ASP.NET Core - Charts (YouTube) + PowerPoint (PDF). In this tutorial Google Charts will be used. Google Charts is an API (or framework) for creating Charts in your web pages. It is free to use and it is easy to use (when you first know how to use it). Note! If the data contains decimals, you may get some trouble if your computer has “,” as a decimal symbol. An easy solution is to change the Decimal symbol to “.” in Windows. In Windows, goto the "Control Panel", then "Clock and Region". Select "Additional settings..." and then set the Decimal symbol to ".". A better solution may be to do some formatting in your code.

See here for more ASP.NET Core resources.

7 Datalogging and Monitoring

Datalogging and Monitoring is important within Industrial IT and Automation Systems, IoT Applications, etc.

Resources:

LabVIEW Database Logging Tutorial + LabVIEW Database Logging Code Example

Datalogging and Monitoring Examples (YouTube) + PowerPoint (PDF) - LabVIEW and Visual Studio/C# Examples