2017.03.

24

Database Lab
http://home.hit.no/~hansha/?lab=database

Hans-Petter Halvorsen
 Lab Overview
Database Design
& Modelling Create Tables
SQL Server Management Studio

Microsoft
Database Management SQL Server
Database

We will create Applications in LabVIEW

& Visual Studio that Write and Read
data to the Database
 Necessary Software Software

ERwin (Academic Edition, free download from Internet)

SQL Server (Express) Edition (Download for free from Internet or
Microsoft Imagine: SQL Server 2016 Express with Service Pack 1)
LabVIEW (2016)
DAQmx Driver Software
LabVIEW SQL Toolkit ( Hans-Petter Halvorsen)
Visual Studio (2016)

Make sure to install the necessary Software before you go to the laboratory!
 Hardware
Hardware

Your Personal Computer

Note! If you don't have the TC-01

device available, you may use
some simulated values instead.

NI USB-TC01 Thermocouple Measurement Device

 System Overview
Data Logging Data Monitoring
Database
Views and/or
Stored Stored
Procedure(s) Procedure(s)

DAQmx Driver
Trigger(s)
DAQ Convert Temperature to Celsius/Fahrenheit
Calculate Average, Max, Min Temperature Data
 System Overview
Logging Network Monitoring
App App

Temperature
Sensor Triggers

TC-01 Thermocouple Stores Procedures

Views

Tables
Table Design
 Lab Assignment Overview
1. Design Database using ERwin.
2. Implement Tables, Views, Stored
Procedures and Triggers using SQL Server.
3. Create a Datalogging App using LabVIEW.
4. Create a Data Monitoring App using Visual
Studio/C#.
See next slides for details...
 Lab Assignment
Make sure to read the whole assignment before you
start to solve any of the problems.
If you miss assumptions for solving some of the
problems, you may define proper assumptions
yourself.
The Tasks are somewhat loosely defined and more
like guidelines, so feel free to interpret the Tasks in
your own way.
Feel free to Explore! Add Value to your
Applications!
The teacher have not done Very often it works on one computer but
not on another. You may have other
all the Tasks in detail, so he versions of the software, you may have
may not have all the installed it in the wrong order, etc...
answers! That's how it is in In these cases Google is your best friend!
real life also!

The Teacher dont have all the answers (very few actually L)!! Sometimes you just need to Google in
order to solve your problems, Collaborate with other Students, etc. Thats how you Learn!
 Troubleshooting & Debugging
Visual Studio Use available Resources
Use the Debugging Tools in your such as User Guides,
Programming IDE. Datasheets, Text Books,
Visual Studio, LabVIEW, etc. have great Tutorials, Examples,
Debugging Tools! Use them!! Tips & Tricks, etc.
My System
is not
Working??
Google It!
Your hardware device most likely works, so
You probably will find the you don't need a new device! Still not working
answer on the Internet after Troubleshooting & Debugging? Fill out
an Equipment Error Form

Check your electric circuit, electrical cables, DAQ device, etc. Check if
Another person in the world probably the wires from/to the DAQ device is correct. Are you using the same
had a similar problem I/O Channel in your Software as the wiring suggest? etc.
 ERwin
Database Modelling and Design

Task 1 Hans-Petter Halvorsen, M.Sc.

ERwin Academic Edition
Database Modelling Tool for creating ER
(Entity Relationship) diagrams
Its free, but you need to apply for it - so it may
take 2-5 days before you get it!
http://erwin.com/education/erwin-academic-
program/academic-edition/
 Theory
Database Design ER Diagram
ER Diagram (Entity-Relationship Diagram)
Used for Design and Modeling of Databases.
Specify Tables and relationship between them (Primary Keys and Foreign Keys)
Table Name
Example:
Table Name

Column
Names

Primary Key Foreign Key

Primary Key
Relational Database. In a relational database all the tables have one or more relation with each other using Primary Keys
(PK) and Foreign Keys (FK). Note! You can only have one PK in a table, but you may have several FKs.
 Database - Best Practice
Tables: Use upper case and singular form in table names not plural, e.g.,
STUDENT (not students)
Columns: Use Pascal notation, e.g., StudentId
Primary Key:
If the table name is COURSE, name the Primary Key column CourseId, etc.
Always use Integer and Identity(1,1) for Primary Keys. Use UNIQUE
constraint for other columns that needs to be unique, e.g. RoomNumber
Specify Required Columns (NOT NULL) i.e., which columns that need to have
data or not
Standardize on few/these Data Types: int, float, varchar(x), datetime, bit
Use English for table and column names
Avoid abbreviations! (Use RoomNumber not RoomNo, RoomNr, ...)
It is recommended that you follow these guidelines!
 Database System
Create the overall Specifications and Design for your
System
Start by Design the Database Tables using ERwin and
create a SQL Script
Implement the Tables in SQL Server, e.g., using a SQL
Script generated in ERwin
Then Create necessary Views, Stored Procedures and
Triggers within the SQL Server Management Studio. It is
recommended that you save these as SQL Files
Congratulations! - You are finished with the Task
 SQL Server
Database Implementation and Structured Query Language (SQL)

Task 2 Hans-Petter Halvorsen, M.Sc.

 Note these are 2 separate
SQL Server modules you need to install

Management Studio
Database Engine and Repository

The Data Storage

A graphical interface to the Database Engine where you
can create tables and manipulate data, etc.
 SQL Server
Start by Design the Database Tables using ERwin
and create a SQL Script
Implement the Tables in SQL Server, e.g., using a
SQL Script generated in ERwin
Create necessary Views, Stored Procedures and
Triggers within the SQL Server Management
Studio.
It is recommended that you wait to create them until
you need them in the LabVIEW or C# Code
Microsoft SQL Server Management Studio Software
3

Your SQL Server

1 4
Your Database Write your Query here
2

Your
Tables

5 The result from your Query

 Theory
SQL Structured Query Language
Query Examples:
insert into STUDENT (Name , Number, SchoolId)
values ('John Smith', '100005', 1)

select SchoolId, Name from SCHOOL

select * from SCHOOL where SchoolId > 100

update STUDENT set Name='John Wayne' where StudentId=2

delete from STUDENT where SchoolId=3

We have 4 different Query Types: INSERT, SELECT, UPDATE and DELETE
Views, Stored Procedures and Triggers
Views: Views are virtual tables for easier access to
data stored in multiple tables.
Stored Procedures: A Stored Procedure is a
precompiled collection of SQL statements. In a
stored procedure you can use if sentence, declare
variables, etc.
Triggers: A database trigger is code that is
automatically executed in response to certain
events on a particular table in a database.
 Database Views
A Database View is a virtual table that can contain
data from multiple tables
You probably need to Create and Use one or more
Database Views in order to get Data from the Database,
both in the Data Logging App and Data Monitoring App

It is recommended that you wait to create them until you

need them in the LabVIEW or C# Code
 1 Create View: Views
IF EXISTS (SELECT name A View is a virtual table
FROM sysobjects
WHERE name = 'CourseData' that can contain data from
AND type = 'V') multiple tables
DROP VIEW CourseData This part is not necessary but if you make any
GO changes, you need to delete the old version before
you can update it
CREATE VIEW CourseData
AS The Name of the View
SELECT
SCHOOL.SchoolId,
SCHOOL.SchoolName,
COURSE.CourseId,
Inside the View you join the
COURSE.CourseName, different tables together using
COURSE.Description
the JOIN operator
FROM
SCHOOL You can Use the View as an
INNER JOIN COURSE ON SCHOOL.SchoolId = COURSE.SchoolId ordinary table in Queries:
GO
Using the View:
2 select * from CourseData
 Copy to SQL Server Management Studio, save as a SQL
View Template File (.sql) as the same name as the View you are going
to create. Store all your files on your hard drive.
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = '<ViewName>'
AND type = 'V')
DROP VIEW <ViewName>
GO

CREATE VIEW <ViewName>

AS

SELECT
<TableName>.<ColumnName>,
<TableName>.<ColumnName>,
<TableName>.<ColumnName>,
<TableName>.<ColumnName>,
<TableName>.<ColumnName>

FROM
<TableName1>
INNER JOIN <TableName2> ON <TableName1>.<PrimKeyColumnName1> = <TableName2>.<PrimKeyColumnName2>
GO
 Stored Procedures
Typically you need some Stored Procedures:
The Datalogging App should use a Stored Procedure in
order to save Measurement Data to the Database.
The Datalogging App should use a Stored Procedure in
order to save Configuration Data to the Database.
Logging Interval
Unit (Celsius or Fahrenheit)
It is recommended that you wait to create them until you
need them in the LabVIEW or C# Code
1
Create Stored Procedure: Stored Procedures
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'StudentGrade'
A Stored Procedure is like a Method in C# - it
AND type = 'P')
DROP PROCEDURE StudentGrade
is a piece of code with SQL commands that
OG do a specific task and you reuse it
CREATE PROCEDURE StudentGrade This part is not necessary but if you make any
@Student varchar(50), changes, you need to delete the old version before
@Course varchar(10), you can update it
@Grade varchar(1)
Procedure Name
AS
Input Arguments
DECLARE
@StudentId int, Internal/Local Variables
@CourseId int Note! Each variable starts with @
select @StudentId = StudentId from STUDENT where StudentName = @Student

select @CourseId = CourseId from COURSE where CourseName = @Course

SQL Code (the body of the
insert into GRADE (StudentId, CourseId, Grade)
values (@StudentId, @CourseId, @Grade) Stored Procedure)
GO
2 Using the Stored Procedure:
execute StudentGrade 'John Wayne', 'SCE2006', 'B'
Stored Procedure Template
Copy to SQL Server
Management Studio, save as a
SQL File (.sql) as the same
IF EXISTS (SELECT name name as the SP you are going
FROM sysobjects
WHERE name = '<StoredProcedureName>'
to create. Store all your files
AND type = 'P') on your hard drive.
DROP PROCEDURE <StoredProcedureName>
GO

CREATE PROCEDURE <StoredProcedureName>

@<InputVariable1> <DataType>,
@<InputVariable2> <DataType>
AS

DECLARE
@<InternalVariable1> <DataType>,
@<InternalVariable2> <DataType>

select @<InternalVariable1> = <ColumnName> from <TableName> where <ColumnName> =

@<InputVariable1>

insert into <TableName> (<ColumnName1>, <ColumnName2>, ...) values (@<InternalVariable1>,

@<Inputvariable1>, ...)
GO
 Database Trigger(s)
You may need one or more Triggers that do e.g. the following:
Convert Temperature to Celsius/Fahrenheit
E.g., If Unit=Celsius, the Trigger should Convert Temperature Data to
Fahrenheit
E.g., If Unit=Fahrenheit, the Trigger should Convert Temperature Data to
Celsius
Both Celsius and Fahrenheit values should probably be stored in the
Database for easy access later in Monitoring App
Calculate Average, Max, Min Temperature Data
The Trigger should calculate and store Average(Mean), Max and Min
Temperature Data into the Database

You may wait to create them until you need them in the LabVIEW or C# Code
A Trigger is executed when you insert, update or delete data in a Table
specified in the Trigger. Trigger Example:
Triggers
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'CalcAvgGrade' This part is not necessary but if you make any
AND type = 'TR') changes, you need to delete the old version before
DROP TRIGGER CalgAvgGrade you can update it
GO
Name of the Trigger Inside the
CREATE TRIGGER CalcAvgGrade ON GRADE Trigger you can
FOR UPDATE, INSERT, DELETE
AS
Specify which Table the use ordinary SQL
Trigger shall work on statements,
DECLARE
@StudentId int, Specify what kind of operations the Trigger create variables,
@AvgGrade float shall act on etc.
Internal/Local Variables
select @StudentId = StudentId from INSERTED
SQL Code
select @AvgGrade = AVG(Grade) from GRADE where StudentId = @StudentId (The body
update STUDENT set TotalGrade = @AvgGrade where StudentId = @StudentId of the Trigger)
GO

Note! INSERTED is a temporarily table containing the latest inserted data, and it is very handy to use inside a trigger
 Trigger Template
Copy to SQL Server Management Studio, save as a SQL
File (.sql) as the same name as the Trigger you are going
to create. Store all your files on your hard drive.
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = '<TriggerName>'
AND type = 'TR')
DROP PROCEDURE <TriggerName>
GO

CREATE TRIGGER <TriggerName>

FOR UPDATE, INSERT, DELETE -Delete the ones not needed
AS

DECLARE
@<InternalVariable1> <DataType>,
@<InternalVariable2> <DataType>

select @Variable1 = Column1 from INSERTED

select @Variable2 = AVG(Column2) from TABLE where Column1 = @Variable1
update TABLE set Column3= @Variabl2e where Column1= @Variable1

GO
Congratulations! - You are finished with the Task
 Datalogging using LabVIEW

Task 3 Hans-Petter Halvorsen, M.Sc.

 Datalogging using LabVIEW
Start by Design and Implement the
Database Tables using ERwin
Database Design Create Stored Procedure(s)
& Modelling and Triggers in SQL Server

TC-01 Thermocouple

Data Microsoft
SQL Server
DAQ Stored Procedure(s)
Database

Triggers
Convert Temperature to Celsius/Fahrenheit
Calculate Average, Max, Min Temperature Data
 LabVIEW HMI Example
The Temperature Data from the TC-01 DAQ device should be stored in the Database.
Datalogging App This is just a Design Sketch!
Feel free to Explore!
Tab Control Chart Configuration

Temperature Data:
30
Show Temperature in
Celsius or Fahrenheit
depending on the
Configuration
Chart
0
Time

Button Exit
 LabVIEW HMI Example
The Temperature Data from the TC-01 DAQ device should be stored in the Database.
Datalogging App This is just a Design Sketch!
Feel free to Explore!
Tab Control Chart Configuration

Logging Interval: Use a Stored Procedure in order

Numeric Control 2 sec. to Save Data to the Database
Combo Box Unit:
Celsius Save
E.g., If Unit=Celsius, the Trigger
should Convert Temperature
Data to Fahrenheit

E.g., If Unit=Fahrenheit, the

Trigger should Convert Buttons Exit
Temperature Data to Celsius
 LabVIEW SQL Toolkit Software
Software

For Easy Database Communication with LabVIEW

Hans-Petter Halvorsen
Download for free here:
http://home.hit.no/~hansha/documents/labview/code/SQLToolkit.zip
 LabVIEW SQL Toolkit Examples
Easy Access to Database Systems from LabVIEW

Example 1: Get Data from Database into LabVIEW: 2D Table with Data

1 2 3

Query
Your ODBC
Connection Try these Examples
Example 2: Write Data to Database from LabVIEW:

1 2 3
Query

38
 Connect to Database
Alt 1: Use ODBC
Setup your Database connection using a Wizard (ODBC
Data Source Administrator)
Alt 2: Use Connection String directly
Alt 2.1: Windows Authentication:
Data Source=<dbserver>;Initial Catalog=<dbname>;Trusted_Connection=True
Alt 2.2: SQL Server Authentication:
Data Source=<dbserver>;Initial Catalog=<dbname>;Persist Security Info=True;User ID=sa;Password=<password>
See Examples on next slides...
 ODBC Theory
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.
Control Panel Administrative Tools Data Sources (ODBC)
We will use this ODBC Connection later
in LabVIEW in order to open the
Database Connection from LabVIEW

Note! Make sure to use the 32

bit version of the ODBC Tool!
 ODBC Step by Step Instructions
The Name of your
ODBC Connection

The Name of your

SQL Server

Select the Database

you are using

Use either Windows

or SQL Server
authentication Test your
(Windows is simplest connection to see
to use!) if its works 41
 LabVIEW SQL Toolkit Example
Easy Access to Database Systems from LabVIEW
Alternative Solution: Type in the Connection String for your Database

Your Password for the sa user Type your Database here

Your SQL Server Instance

Your SQL Query

Note! When using this method, you dont need to create an ODBC Connection first!
LabVIEW SQL Toolkit Example

You should use a Stored Procedure

43
 LabVIEW SQL Toolkit Example
1 GUI/HMI If we want to save input data from the user
we can use the Format Into String function
The %s operator will be replaced by the text
from the TextBox on the Front Panel. For
Numbers we can use %d (Integer) or %f for
Floating-point Number.
2 Code:

3 Resulting SQL Query:

execute CreateBook Lord of the Rings', J.R.R. Tolkien', Wiley', 32-2-333-56', Fantasy'
Congratulations! - You are finished with the Task
 Data Monitoring using
Visual Studio/C#

Task 4 Hans-Petter Halvorsen, M.Sc.

Data Monitoring using Visual Studio/C#

Microsoft
SQL Server
Database View(s)
Stored Procedure(s)

You can create a Desktop Application (WinForm App or

WPF App) or a Web Application (WebForm App)
 Visual Studio HMI Example
Data Monitoring App
Temperature Data: This is just a Design Sketch!
30 Feel free to Explore!

You should get

Chart the Data from
the Database
0
Time Typically you get
Date & Time Value [C] Value [F]
Average: 22 Data from the
2016.03.22 14:45 22 71.6
Database using
... ... ... Min: 10 Views and/or Stored
DataGridView Procedures
... ... ...
Max: 26
... ... ...
Labels TextBoxes
 Visual Studio/C# Database Example
using System.Configuration;

...

public partial class BookList : System.Web.UI.Page Note! You need to add a

{
private string connectionString = ConfigurationManager.ConnectionStrings["LibraryDBConnectionString"].ConnectionString; Reference in order to use
void Page_Load(object sender, EventArgs e)
the Configuration Manager
{
if (!IsPostBack)
{
FillBookGrid();

}
}
In this example the Connection
String to the Database is stored in
private void FillBookGrid()
{ the App.config file
List<Book> bookList = new List<Book>();
Book book = new Book(); <connectionStrings>
<add name="LibraryDBConnectionString"
bookList = book.GetBooks(connectionString); connectionString="Data Source=macwin8;Initial
Catalog=BOOKS;Persist Security Info=True;User
ID=sa;Password=xxx"
gridBookList.DataSource = bookList;
providerName="System.Data.SqlClient" />
} </connectionStrings>
}

49
 Configuration Manager
Note! You need to add a Reference in
order to use the Configuration Manager
Right-click
 Visual Studio/C# Database Example
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data;

public class Book

{
public int BookId { get; set; }
public string Title { get; set; }
public string Isbn { get; set; }
public string PublisherName { get; set; }
public string AuthorName { get; set; }
public string CategoryName { get; set; }

public List<Book> GetBooks(string connectionString)

{

List<Book> bookList = new List<Book>(); This is a Database View

SqlConnection con = new SqlConnection(connectionString);

string selectSQL = "select BookId, Title, Isbn, PublisherName, AuthorName, CategoryName from GetBookData";

con.Open();

SqlCommand cmd = new SqlCommand(selectSQL, con);

SqlDataReader dr = cmd.ExecuteReader();

if (dr != null)
{
while (dr.Read())
{
Book book = new Book();

book.BookId = Convert.ToInt32(dr["BookId"]);
book.Title = dr["Title"].ToString();
book.Isbn = dr["ISBN"].ToString();
book.PublisherName = dr["PublisherName"].ToString();
book.AuthorName = dr["AuthorName"].ToString();
book.CategoryName = dr["CategoryName"].ToString();

bookList.Add(book);
}
}

return bookList;

51
}
}
1
Timer In Visual Studio you may want to use a Timer instead of a While Loop in
order to read values at specific intervals.
Select the Timer component in the Properties: 3
2 Initialization: Toolbox
public Form1()
{
InitializeComponent();
You may specify the Timer Interval
timer1.Start(); in the Properties Window
} Double-click on the Timer object in
Timer Event: order to create the Event
4
private void timer1_Tick(object sender, EventArgs e) Structure your Code
properly!!
{
Define Classes and Methods
//Read from DB which you can use here
//Formatting
//Plot Data
}
 Charting in Visual Studio
Visual Studio has a Chart control that you can use in Windows Forms or Web application (ASP.NET)
https://msdn.microsoft.com/en-us/library/dd489237.aspx
http://www.i-programmer.info/programming/uiux/2756-getting-started-with-net-charts.html
using System.Windows.Forms.DataVisualization.Charting;
...
Creating a Web App? Use the following Namespace instead:
chart1.Series.Clear(); System.Web.UI.DataVisualization.Charting
chart1.Series.Add("My Data");
chart1.Series["My Data"].ChartType=SeriesChartType.Line;
...
int[] x = {1, 2, 3, 4, 5, 6, 7, 8};
int[] y = {20, 22, 25, 24, 28, 27, 24, 26};
for (int i = 0; i < x.Length; i++)
{
chart1.Series["My Data"].Points.AddXY(x[i],y[i]);
}
Congratulations! - You are finished with the Task
 Finished Solution
Logging Network Monitoring
App App

Temperature
Sensor Triggers

TC-01 Thermocouple Stores Procedures

Views

Tables
Table Design
Congratulations! - You are finished with all the Tasks in the Assignment!
Hans-Petter Halvorsen, M.Sc.

University College of Southeast Norway

www.usn.no

E-mail: [email protected]
Blog: http://home.hit.no/~hansha/