Download Report

Database Lab with Step by Step Exercises that you can do in your own Pace h-p://home.hit.no/~hansha/?lab=database Hans-‐Pe-er Halvorsen, M.Sc. PracOcal InformaOon •  Room: C-‐222 •  Individual Work! This means everybody shall do all the Tasks (but collaboraOon and discussions with others are allowed) •  Delivery: DemonstraOon when ﬁnished + Deliver Code in Fronter with FileName: database_studentno.zip. Note! Use ZIP File – Not .RAR or other weird formats! Deadline 19. April •  Grade: Approved/Not Approved Week Monday (13:15-‐16:00) Wednesday (09:15-‐12:00) Thursday (08:15-‐10:00) HIL Lab 15 8. April Schedule: 16 13. April 16. April Symbols used in the Lab Work InformaOon Sobware Necessary Sobware Recommended Li-erature Student Task Theory Theory/Knowledge Hardware Necessary Hardware Examples/Guidelines Challenge (Voluntary) Lab Assignment Overview A.  Database Modelling/Design using ERwin B.  Generate SQL Table Script using ERwin C.  Generate Tables in SQL Server using the SQL Script generated by ERwin D.  Use Structured Query Language (SQL) E.  Create Stored Procedures, View, Triggers F.  Database CommunicaOon in LabVIEW G.  (Database CommunicaOon in C#) H.  (Web Shop Database Design) See next slides for details... Necessary Sobware Sobware •  ERwin (CA ERwin Data Modeler Community EdiOon, free download from Internet) •  SQL Server 2014 Express EdiOon (Download for free from Internet or DreamSpark: “SQL Server 2014 Express with Tools”) •  LabVIEW 2014 •  DAQmx Driver Sobware •  LabVIEW SQL Toolkit (© Hans-‐Pe-er Halvorsen) •  Visual Studio Make sure to install the necessary Sobware before you go to the laboratory! 5 Hardware Hardware Your Personal Computer NI USB-‐TC01 Thermocouple Measurement Device 6 Lab Overview Database Design & Modelling Create Tables SQL Server Management Studio Database Management Microsob SQL Server Database We will create ApplicaOons in LabVIEW (& Visual Studio) that Write and Read data to the Database 7 Recommended Li-erature •  Tutorial: IntroducOon to LabVIEW h-p://home.hit.no/~hansha/?page=labview •  Tutorial: IntroducOon to Database Systems h-p://home.hit.no/~hansha/?tutorial=database •  Tutorial: Structured Query Language (SQL) h-p://home.hit.no/~hansha/?tutorial=sql •  Tutorial: Database CommunicaOon in LabVIEW h-p://home.hit.no/~hansha/?tutorial=database_labview •  Tutorial: Using SQL Server in C# •  Tutorial: IntroducOon to Visual Studio and C# h-p://home.hit.no/~hansha/?tutorial=csharp •  Tutorial: Data AcquisiOon in LabVIEW h-p://home.hit.no/~hansha/?tutorial=daq 8 The Teacher dont have all the answers (very few actually L)!! SomeOmes you just need to “Google” in order to solve your problems, Collaborate with other Students, etc. Thats how you Learn! Laboratory Work Hans-‐Pe-er Halvorsen, M.Sc. Mandatory Lab Work! •  Registered a-endance! -‐ Don’t be late! •  You use lab equipment that are only available in the laboratory (meaning that you may not are able to borrow it home) at the scheduled hours. •  Some of the Lab Equipment are used in other courses and some equipment can only be used under the supervision of trained lab personnel (meaning you cannot use it alone without supervison) •  Some of the equipment are also very expensive and fragile and therefore cannot be used outside the laboratory •  This means you have to use the Ome in the laboratory well – because you may only have one chance to do it! 11 Throw Garbage! Laboratory Rules! Noise Food and Drink not allowed during Laboratory Work, because of risk of electric shock, damage of expensive lab equipment, etc. ! Always be prepared! Bring your PC, a printed copy of the Lab Assignment and make a Sketch of the Lab Work in advance! Don’t be late! Laboratory Rules 1. 
No Food & Drinks while working with Laboratory Equipment (for your own safety)! 2.  Lab Assignment: Read and preferably Print out the Lab Assignment before you go to the Laboratory. Be prepared!! 3.  Planning: Plan your work: Create a Sketch of what you shall do (System sketch), etc. Install all necessary SoVware in advance! 4.  Lab Equipment: You need to show the printed Lab Assignment and a self-‐
made System Sketch to the Lab personnel before you get any Equipment!! 5.  Start Working. Be careful when working with Electricity! Make sure everything is wired correctly and safe before you connect the Equipment to a power source 6.  Take Notes (incl. screen shots, save your code, save log ﬁles, etc.) during the Laboratory Work (you will need it when wriOng Reports, Exam preparaOon, etc.) HSE -‐ Health, Safety and Environment Be careful! – Follow instrucOons and signs in the laboratory! Please read the HSE InstrucOons at HiT: h-p://www.hit.no/eng/HiT/Student/HSE Structure and Order in the Lab needed!! Make sure to work in a calm and controlled pace! There are lots of dangerous elements in the laboratory – such as electricity, chemicals, etc. Please put back Inventory (chairs, etc.), Lab Equipment and Tools where you found it and in the same condiOon! TroubleshooOng & Debugging Visual Studio Use the Debugging Tools in your Programming IDE. Visual Studio, LabVIEW, etc. have great Debugging Tools! Use them!! “Google It”! You probably will ﬁnd the answer on the Internet My System is not Working?? Use available Resources such as User Guides, Datasheets, Text Books, Tutorials, Examples, Tips & Tricks, etc. Your hardware device most likely works, so you don't need a new device! SOll not working aber TroubleshooOng & Debugging? Fill out an “Equipment Error Form” Another person in the world probably had a similar problem Check your electric circuit, electrical cables, DAQ device, etc. Check if the wires from/to the DAQ device is correct. Are you using the same I/O Channel in your Sobware as the wiring suggest? etc. Equipment Error Form: Your hardware device most likely works, so you don't need a new device! SOll not working aber TroubleshooOng & Debugging? Fill out an “Equipment Error Form” h-p://home.hit.no/~hansha/documents/lab/Equipment%20Error%20Form.pdf Lab Inventory System (LIS) h-p://128.39.35.248/LabSystem If you are allowed to borrow Lab Equipment at home, you need to register the equipment loan into the LIS system before taking the Equipment out of the room! Register User InformaOon Equipment Loan Access Card Lab Equipment Do not take equipment out of the classroom without Scan/Enter the Barcode on the Equipment you want permission from the teacher!!! to borrow and then the Barcode on your Access Card Equipment Loan •  Remember you are responsible for the Equipment you loan! •  Never give your Equipment to other Students! •  If equipment is destroyed, you will get the blame if the equipment has not been properly handled •  There is an unique link between the Equipment Barcode and your Student Card Id –  so others cannot deliver the equipment for you –  nor can you pass it on to others •  If you for some reason need a new device, remember to unregister the old one ﬁrst Purpose with Laboratory Work •  Get experience with pracOcal problem solving using theory, sobware, etc. you have learned in this course and other courses •  The learning will be through pracOcal lab assignments using diﬀerent hardware components and sobware commonly used in the industry •  The teacher dont have the answers/soluOons to all the problems •  You need to use exisOng Theory and PracOcal Knowledge, exisOng Tutorials, Internet, etc. in order to solve the challenges •  The lab assignments are real life scenarios that you can expect to work with when gexng a job 20 Laboratory Work vs. Ordinary Lectures •  Now you shall do the job -‐ not the teacher! •  You always need to meet prepared! –  Required theory must be read in advance –  Read the Assignment in advance –  Make a sketch of what do do and how to do it • 
• 
• 
• 
• 
• 
Necessary SoVware must be installed in advance! You will use equipment that is only available in the lab You must work with and collaborate with other students You need to plan what to do Laboratory Work requires acYve parYcipaYon from you Lab Work is mandatory! 21 How to study -‐ Guidelines • 
• 
• 
• 
• 
• 
• 
• 
• 
Going to school is a full-‐Ome job! (unless you take it part Ome, but then you must take it over more years than what is normal) A 10 credits course are approximately 13-‐17 hours/week with work. 13 hours/week must be considered as absolute minimum! MoOvaOon! Have you the moOvaOon needed? Be engaged, acOvely parOcipate in your educaOon, ask quesOons, discuss with the teacher and fellow students, read the syllabus, do assignments, etc. Job structured and eﬀecOvely, and plan what to do. Write a “Diary”. Make sure to have a good structure on your PC too! Always be prepared! Read this week's syllabus in advance before class, take notes during the lectures and lab work, repeat this week's syllabus aber class is ﬁnished, Make new structured and improved notes. Read through and prepare for lab assignments, etc. In advance! Study the same amount day by day and week by week. Every week you must read the syllabus, doing assignments, etc. according to the schedule -‐ Hard work is the only thing that ma-ers. Repeat each week the syllabus from previous weeks, i.e., read your notes, books, etc. Collaborate with other students. Learn from other students. You should reach a common goal together. Challenging each other and set requirements for each other! Learning something new should be a li-le “painful”. It requires eﬀort! Challenge yourself! Credit System and Grading System Absolute Minimum! These are successful key factors for engineers and scienOsts! Finally... Think outside the box! Add value! You are allowed to think by yourself and see connecOons that others have not seen before. You don't necessary need to take the same paths that others have gone before you. Let's start the Laboratory Work! Database Systems Hans-‐Pe-er Halvorsen, M.Sc. 26 Old fashion Database (Data-‐storage) Systems Not too long ago, this was the only data-‐storage device most companies needed. Those days are over. Database Systems Theory A Database is a structured way to store lots of informaOon. The informaOon is stored in diﬀerent tables. -‐ “Everything” today is stored in databases! Examples: •  Bank/Account systems •  InformaOon in Web pages such as Facebook, Wikipedia, YouTube, etc. •  Fronter, TimeEdit, etc. •  … lots of other examples! 28 Database Management Systems (DBMS) •  MicrosoV SQL Server Theory –  Enterprise, Developer versions, etc. (Professional use) –  Express version is free of charge •  Oracle •  MySQL (owned by Oracle, but previously owned by Sun Microsystems) -‐ MySQL can be used free of charge (open source license), Web sites that use MySQL: YouTube, Wikipedia, Facebook •  Microsob Access •  IBM DB2 •  Sybase, etc. (we have hundreds diﬀerent DBMS) We w
ill use SQL server because it is very popular in the industry today, and we can use it for free via the MicrosoV DreamSpark Premium SubscripYon – which is available for the students and staﬀ at Telemark University College, or use the Express version which is available for free for everybody. 29 Microsob SQL Server 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 (preferable on the server). The Management Studio is graphical tool for conﬁguring and viewing the informaOon in the database. It can be installed on the server or on the client (or both). Sobware Sobware The newest version of Microsob SQL Server is “SQL Server 2014” 30 ERwin Database Design & Modelling Hans-‐Pe-er Halvorsen, M.Sc. 31 Sobware CA ERwin Data Modeler Community EdiOon •  Free! •  Max 25 Tables (good enough for our purpose) •  Download here: h-p://erwin.com/products/data-‐modeler/community-‐ediOon 32 Database Design – ER Diagram Theory ER Diagram (EnOty-‐RelaOonship Diagram) •  Used for Design and Modeling of Databases. •  Specify Tables and relaYonship between them (Primary Keys and Foreign Keys) Table Name Example: Table Name Column Names Primary Key Primary Key Foreign Key RelaOonal Database. In a relaOonal database all the tables have one or more relaOon 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 FK’s. Database -‐ “Best PracOce” •  Tables: Use upper case and singular form in table names – not plural, e.g., “STUDENT” (not “students”) •  Columns: Use Pascal notaOon, e.g., “StudentId” •  Primary Key: •  If the table name is “COURSE”, name the Primary Key column “CourseId”, etc. •  “Always” use Integer and IdenOty(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, ﬂoat, varchar(x), date2me, bit •  Use English for table and column names •  Avoid abbreviaOons! (Use “RoomNumber” – not “RoomNo”, “RoomNr”, ...) It is recommended that you follow these guidelines! IntroducOon to ERwin Sobware Open ERwin and select File-‐>New... The following window appears (New Model): Make sure to select “Logical/Physical” Model Select the Database Type and Version you shall use 35 IntroducOon to ERwin “Landscape” orientaOon is recommended 36 IntroducOon to ERwin Students: We start very simple: Lets try to create the following Tables, Columns, Primary Keys and Foreign Keys using ERwin: 37 IntroducOon to ERwin How-‐To: Create Tables and Columns Use the “EnOty” Tool in order to Create New Tables Use <Tab> and <Enter> in order to give the Tables a Name and to create Columns. Use the <Arrows> to switch between the Columns inside a Table 38 IntroducOon to ERwin How-‐To: Create Primary Key (PK) – Foreign Key (FK) RelaOonships: Use the “RelaOonship” Tool in order to Create a Primary Key (PK) – Foreign Key (FK) RelaOonship Click ﬁrst on the PK table and then on the FK table using the “RelaOonship” Tool. The RelaOonship ConnecOon and the FK column itself is then Created AutomaOcally 39 Sexng Data Types (Physical Model) Make sure to set proper Data Types You may also Double-‐click (or Right-‐click and select Table/Column ProperOes) on Tables and Columns in order to change diﬀerent A-ributes, eg. Data Types, etc. 40 Final Results: 41 CreaOng TABLE Script How-‐To: Create a SQL Script 1 Make sure you are using the Physical Model 2 Select “Forward Engineering” and “Schema...” 3 4 Select/Deselect diﬀerent OpOons in order to make your script the way you want. Click “Preview” in order to see the results. Click “Save” when you are saOsﬁed with your Script 42 Database Design Exercise Students: Create this Example using ERwin. Find proper Data Types in the Physical Model 43 CongratulaOons! -‐ You are ﬁnished with the Task 44 SQL Server Database ImplementaOon Hans-‐Pe-er Halvorsen, M.Sc. 45 SQL Server 2012/2014 InstallaOon Sobware SQL Server has diﬀerent EdiOons and InstallaOon Packages. Here we will go through the installaOon of SQL Server 2014 Express with Tools. PreparaYon 1: Download InstallaOon Package (SQL Server 2014 Express with Tools) from Internet or DreamSparks. PreparaYon 2: Are you using Windows 8? – You may need to install the .NET Framework 3.5 in advance: • 
• 
• 
Go to Sexngs. Choose Control Panel then choose Programs. Click Turn Windows features on or oﬀ, and the user will see Windows feature window. You can enable this feature by click on .NET Framework 3.5 (include .NET 2.0 and 3.0) select it and click OK. Aber this step, it will download the enOre package from internet and install the .NET Framework 3.5 feature. Note! It takes some Ome to download, so make sure to do it before the Lab Work in Class!! Start Installing SQL Server 2014 Express with Tools Step 1 Step 2 (Just Click Next) Note! These screen shots are from SQL Server 2012 – but SQL Server 2014 is similiar 47 Step 3 (Just Click Next) Step 4 (Just Click Next) 48 Step 5(Use Default or change the Name if you want to) Step 6 (Just Click Next) 49 Step 7 (Select “Mixed Mode”) Select “Mixed Mode” Step 7b (use Default locaOon or change folder for the Database Files) Enter Password for “sa” user and make sure to remember it!! 50 Step 8 (Just Click Next) Step 9 – Finished! Hopefully are all Green (Succeeded) You are now ready to use SQL Server 51 Microsob SQL Server Management Studio Sobware 3 Your SQL Server 1 2 Your Database 4 Write your Query here Your Tables 5 The result from your Query 52 Microsob SQL Server – Create a New Database 2 1 Name you database, e.g., “SCHOOL” 53 Database Design Exercise Students: Create these Tables in SQL Server (either manually or from the Script from ERwin) 54 1 3 Open SQL Server and create a “New Database...” You are Finished. You are ready to start using the database, inserOng data, etc. SQL Server Open the SQL Script in order to insert the Tables in SQL Server 2 55 Microsob SQL Server Do you get an error when trying to change your tables? Make sure to uncheck this opOon! 56 Create Tables using the Designer Tools i
n S
QL S
erver Even if you can do “everything” using the SQL language, it is someOmes easier to do something in the designer tools in the Management Studio in SQL Server. Instead of creaOng a script you may as well easily use the designer for creaOng tables, constraints, inserOng data, etc. 2 Next, the table designer pops up where you can add columns, data types, etc. 1 Select “New Table …”: In this designer we may also specify constraints, such as primary keys, unique, foreign keys, etc. 57 Create Tables with the “Database Diagram” 2 1 You may select exisOng tables or create new Tables Create New Table 3 5 4 Enter Columns, select Data Types, Primary Keys, etc. 58 CongratulaOons! -‐ You are ﬁnished with the Task 59 SQL Structured Query Language Hans-‐Pe-er Halvorsen, M.Sc. 60 What is SQL? Theory •  SQL – Structured Query Language •  SQL is a standard language for accessing databases – and manipulate data •  SQL is not case sensiOve Example: select SchoolId, Name from SCHOOL
We use the “SELECT” command in order to get data from the Database Columns Table SQL – Structured Query Language Theory 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 diﬀerent Query Types: INSERT, SELECT, UPDATE and DELETE 62 Important SQL Commands • 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
Theory SELECT -‐ extracts data from a database UPDATE -‐ updates data in a database DELETE -‐ deletes data from a database INSERT INTO -‐ inserts new data into a database These are most used in your daily work. These commands are used to insert or modify data CREATE DATABASE -‐ creates a new database ALTER DATABASE -‐ modiﬁes a database CREATE TABLE -‐ creates a new table ALTER TABLE -‐ modiﬁes a table DROP TABLE -‐ deletes a table CREATE INDEX -‐ creates an index (search key) DROP INDEX -‐ deletes an index These are used when creaOng or modifying exisOng Tables DDL 2 main groups of SQL Commands Data DeﬁniOon Language (DDL) Create CREATE Tables Rename RENAME Tables Drop Theory SQL Structured Query Language (SQL) DML Data ManipulaOon Language (DML) CRUD Create INSERT INTO Read SELECT Alter ALTER Tables Update UPDATE Delete DELETE Most used in daily work DELETE Tables Example: Create Tables using SQL CREATE TABLE SCHOOL
(
SchoolId int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
SchoolName varchar(50) NOT NULL UNIQUE,
Description varchar(1000) NULL,
Address varchar50) NULL,
Phone varchar(50) NULL,
PostCode varchar(50) NULL,
PostAddress varchar(50) NULL,
)
GO
...
...
65 SQL Queries Table Name: CUSTOMER Students: Create the following Table and Data using SQL 66 INSERT INSERT INTO CUSTOMER (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway'); 67 SELECT Students: Write and Execute the following Queries. SELECT * FROM CUSTOMER
Note! SQL is NOT case sensiOve: “select” is the same as “SELECT” SELECT CustomerName, City FROM CUSTOMER
SELECT DISTINCT City FROM CUSTOMER
SELECT * FROM CUSTOMER WHERE Country='Mexico'
SELECT * FROM CUSTOMER WHERE CustomerID=1
SELECT * FROM CUSTOMER WHERE Country='Germany’ AND City='Berlin'
SELECT * FROM CUSTOMER WHERE City='Berlin’ OR City=’Luleå'
SELECT * FROM CUSTOMER ORDER BY Country
SELECT * FROM CUSTOMER ORDER BY Country DESC
68 UPDATE Students: Write and Execute the following Query UPDATE CUSTOMER SET ContactName='Alfred Schmidt', City='Hamburg' WHERE CustomerName='Alfreds Fucerkiste'
Update Warning! Be careful when updaOng records. What happens if we had omi-ed the WHERE clause, in the example above, like this: UPDATE CUSTOMER SET ContactName='Alfred Schmidt', City='Hamburg';
69 DELETE Students: Write and Execute the following Query DELETE FROM CUSTOMER WHERE CustomerName='Alfreds Fucerkiste' AND ContactName='Maria Anders'
Note! It is possible to delete all rows in a table without deleOng the table DELETE * FROM CUSTOMER Note! Be careful! All Data will be lost!! 70 CongratulaOons! -‐ You are ﬁnished with the Task 71 Advanced SQL Features Hans-‐Pe-er Halvorsen, M.Sc. Advanced SQL Features Theory •  Views: Views are virtual tables for easier access to data stored in mulOple tables. •  Stored Procedures: A Stored Procedure is a precompiled collecOon of SQL statements. In a stored procedure you can use if sentence, declare variables, etc. •  Triggers: A database trigger is code that is automaOcally executed in response to certain events on a parOcular table in a database. •  FuncYons: With SQL and SQL Server you can use lots of built-‐
in funcOons or you may create your own funcOons 73 It is assumed that you already have created these Tables in a previous task 74 Get Data from mulOple tables in a single Query using Joins Example: Students: Try this Example select SchoolName, CourseName from You link Primary Keys and Foreign Keys together SCHOOL inner join COURSE on SCHOOL.SchoolId = COURSE.SchoolId 75 1 Create View: CreaOng Views using SQL code IF EXISTS (SELECT name
FROM
sysobjects
WHERE name = 'CourseData'
AND
type = 'V')
DROP VIEW CourseData
GO
CREATE VIEW CourseData
AS
SELECT
SCHOOL.SchoolId,
SCHOOL.SchoolName,
COURSE.CourseId,
COURSE.CourseName,
COURSE.Description
A View is a “virtual” table that can contain data from mulOple tables This part is not necessary – but if you make any changes, you need to delete the old version before you can update it The Name of the View Students: Create this View and make sure it works FROM
SCHOOL
INNER JOIN COURSE ON SCHOOL.SchoolId = COURSE.SchoolId
GO
Using the View: 2 select * from CourseData
Inside the View you join the diﬀerent tables together using the JOIN operator You can Use the View as an ordinary table in Queries: CreaOng Views using the Editor 3 1 Graphical Interface where you can select columns you need 2 4 Save the View Add necessary tables Students: Try this 77 1 Create Stored Procedure: IF EXISTS (SELECT name FROM sysobjects WHERE name = 'StudentGrade' AND type = 'P') DROP PROCEDURE StudentGrade OG Stored Procedure A Stored Procedure is like a Method in C# -‐ it is a piece of code with SQL commands that do a speciﬁc task – and you reuse it This part is not necessary – but if you make any CREATE PROCEDURE StudentGrade changes, you need to delete the old version before @Student varchar(50), you can update it @Course varchar(10), @Grade varchar(1) AS DECLARE @StudentId int, @CourseId int select @StudentId = StudentId from STUDENT where StudentName = @Student select @CourseId = CourseId from COURSE where CourseName = @Course insert into GRADE (StudentId, CourseId, Grade) values (@StudentId, @CourseId, @Grade) GO Students: Create this Stored Procedure and make sure it works Procedure Name Input Arguments Internal/Local Variables Note! Each variable starts with @ SQL Code (the “body” of the Stored Procedure) 2 Using the Stored Procedure: execute StudentGrade 'John Wayne', 'SCE2006', 'B'
78 A Trigger is executed when you insert, update or delete data in a Table speciﬁed in the Trigger. Create the Trigger: IF EXISTS (SELECT name
FROM
sysobjects
WHERE name = 'CalcAvgGrade'
AND
type = 'TR')
DROP TRIGGER CalgAvgGrade
GO
CREATE TRIGGER CalcAvgGrade ON GRADE
FOR UPDATE, INSERT, DELETE
AS
DECLARE
@StudentId int,
@AvgGrade float
Trigger This part is not necessary – but if you make any changes, you need to delete the old version before you can update it Name of the Trigger Specify which Table the Trigger shall work on Specify what kind of operaOons the Trigger shall act on Internal/Local Variables select @StudentId = StudentId from INSERTED
select @AvgGrade = AVG(Grade) from GRADE where StudentId = @StudentId
update STUDENT set TotalGrade = @AvgGrade where StudentId = @StudentId
GO
Inside the Trigger you can use ordinary SQL statements, create variables, etc. SQL Code (The “body” of the Trigger) Students: Create this Trigger and make sure it works Note! “INSERTED” is a temporarily table containing the latest inserted data, and it is very handy to use inside a trigger 79 Quiz Test your skills with this MulOple Choice Test h-p://www.w3schools.com/quiztest/quiztest.asp?qtest=SQL A-ach your result as part of the submi-ed zip ﬁle 80 Want to learn more SQL? 81 CongratulaOons! -‐ You are ﬁnished with the Task 82 LabVIEW Database CommunicaOon in LabVIEW Hans-‐Pe-er Halvorsen, M.Sc. 83 ODBC ODBC (Open Database ConnecOvity) is a standardized interface (API) for accessing Theory the database from a client. You can use this standard to communicate with databases from diﬀerent vendors, such as Oracle, SQL Server, etc. The designers of ODBC aimed to make it independent of programming languages, database systems, and operaOng systems. Control Panel → AdministraOve Tools → Data Sources (ODBC) We will use this ODBC ConnecOon later in LabVIEW in order to open the Database ConnecOon from LabVIEW Note! Make sure to use the 32 bit version of the ODBC Tool! 84 ODBC – Step by Step InstrucOons The Name of your ODBC ConnecOon The Name of your SQL Server Select the Database you are using for the Library Use either Windows or SQL Server authenOcaOon (Windows is simplest to use!) Test your connecOon to see if its works 85 SQL Server ConﬁguraOon Manager Make sure to Enable these Protocols! If not the Database CommunicaOon from LabVIEW may not work properly! 86 LabVIEW SQL Toolkit Sobware Sobware For Easy Database CommunicaOon with LabVIEW © Hans-‐Pe-er Halvorsen Download for free here: h-p://home.hit.no/~hansha/documents/labview/code/SQLToolkit.zip LabVIEW SQL Toolkit Easy Access to Database Systems from LabVIEW 2D Table with Data Example 1: Get Data from Database into LabVIEW: 1 3 2 Query Your ODBC ConnecOon Example 2: Write Data to Database from LabVIEW: Query 1 Students: Try these Examples 2 3 88 LabVIEW SQL Toolkit Easy Access to Database Systems from LabVIEW AlternaOve SoluOon: Type in the ConnecYon 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 ConnecOon ﬁrst! Database CommunicaOon in LabVIEW Students: •  You should use the NI TC-‐01 Thermocouple device in order to log Temperature data to the SQL Server from LabVIEW •  Design and Create the necessary Table(s), etc. in SQL Server •  Create another applicaOon that plots the logged data from the Database This is just a simple Example: 90 Students: •  Create a GUI/HMI in LabVIEW where the User can see a list of Customers and Add New Customers •  You should Create and use a Stored Procedure for Adding Customers to the Database This is just an Example (you should create your own GUI): You may want to add Code for Edit Customer and Delete Customer as well LabVIEW SQL Toolkit Example 1 GUI/HMI If we want to save input data from the user we can use the “Format Into String” funcOon 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 FloaOng-‐point Number. 2 Code: 3 ResulOng SQL Query: execute CreateBook ‘Lord of the Rings', ‘J.R.R. Tolkien', Wiley', ‘32-‐2-‐333-‐56', Fantasy' 92 LabVIEW Example Main Program Pop-‐up Dialog Box Main Program Update GUI SubVI for Retrieving Data from the Database SubVI for Saving Data to the Database VI ProperOes Sexngs VI ProperOes Sexngs CongratulaOons! -‐ You are ﬁnished with the Task 97 Visual Studio Database CommunicaOon in Visual Studio/C# Hans-‐Pe-er Halvorsen, M.Sc. 98 ASP.NET WebForm App This is the WebForm App we are going to create: ASP.NET is a Web Framework available from Visual Studio. Easily explained, it is just a “Template” for creaOng Web Pages using C# 99 Database This is our Example Database (Designed with ERwin) Students: Create the Tables in ERwin and implement the Tables in SQL Server 100 SQL Script -‐ Tables if not exists (select * from dbo.sysobjects where id = object_id(N'[AUTHOR]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) CREATE TABLE [AUTHOR] ( [AuthorId] [int] IDENTITY(1, 1) NOT NULL PRIMARY KEY, [AuthorName] [varchar](50) NOT NULL UNIQUE, [Address] [varchar](50) NULL, [Phone] [varchar](50) NULL, [PostCode] [varchar](50) NULL, [PostAddress] [varchar](50) NULL, ) GO if not exists (select * from dbo.sysobjects where id = object_id(N'[PUBLISHER]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) CREATE TABLE [PUBLISHER] ( [PublisherId] [int] IDENTITY(1, 1) NOT NULL PRIMARY KEY, [PublisherName] [varchar](50) NOT NULL UNIQUE, [Description] [varchar](1000) NULL, [Address] [varchar](50) NULL, [Phone] [varchar](50) NULL, [PostCode] [varchar](50) NULL, [PostAddress] [varchar](50) NULL, [EMail] [varchar](50) NULL,
) GO if not exists (select * from dbo.sysobjects where id = object_id(N'[CATEGORY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) CREATE TABLE [CATEGORY] ( [CategoryId] [int] IDENTITY(1, 1) NOT NULL PRIMARY KEY, [CategoryName] [varchar](50) NOT NULL UNIQUE, [Description] [varchar](1000) NULL, ) GO if not exists (select * from dbo.sysobjects where id = object_id(N'[BOOK]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) CREATE TABLE [BOOK] ( [BookId] [int] IDENTITY(1, 1) NOT NULL PRIMARY KEY, [Title] [varchar](50) NOT NULL UNIQUE, [ISBN] [varchar](20) NOT NULL, [PublisherId] [int] NOT NULL FOREIGN KEY REFERENCES [PUBLISHER] ([PublisherId]), [AuthorId] [int] NOT NULL FOREIGN KEY REFERENCES [AUTHOR] ([AuthorId]), [CategoryId] [int] NOT NULL FOREIGN KEY REFERENCES [CATEGORY] ([CategoryId]), [Description] [varchar](1000) NULL, [Year] [date] NULL, [Edition] [int] NULL, [AverageRating] [float] NULL, ) GO
101 SQL Script – Insert some Data into the Tables -‐-‐CATEGORY -‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐ INSERT INTO CATEGORY (CategoryName) VALUES ('Science') GO INSERT INTO CATEGORY (CategoryName) VALUES ('Programming') GO INSERT INTO CATEGORY (CategoryName) VALUES ('Novel') GO -‐-‐AUTHOR -‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐ INSERT INTO AUTHOR (AuthorName) VALUES ('Knut Hamsun') GO INSERT INTO AUTHOR (AuthorName) VALUES ('Gilbert Strang') GO INSERT INTO AUTHOR (AuthorName) VALUES ('J.R.R Tolkien') GO INSERT INTO AUTHOR (AuthorName) VALUES ('Dorf Bishop') GO -‐-‐PUBLISHER -‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐ INSERT INTO PUBLISHER (PublisherName) VALUES ('Prentice Hall') GO INSERT INTO PUBLISHER (PublisherName) VALUES ('Wiley') GO INSERT INTO PUBLISHER (PublisherName) VALUES ('McGraw-‐Hill') GO
102 SQL Script – Insert some Data into the Tables -‐-‐BOOK -‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐ INSERT INTO BOOK (Title, ISBN, PublisherId, AuthorId, CategoryId) VALUES ( 'Introduction to Linear Algebra', '0-‐07-‐066781-‐0', (select PublisherId from PUBLISHER where PublisherName='Prentice Hall'), (select AuthorId from AUTHOR where AuthorName='Gilbert Strang'), (select CategoryId from CATEGORY where CategoryName='Science') ) GO INSERT INTO BOOK (Title, ISBN, PublisherId, AuthorId, CategoryId) VALUES ( 'Modern Control System', '1-‐08-‐890781-‐0', (select PublisherId from PUBLISHER where PublisherName='Wiley'), (select AuthorId from AUTHOR where AuthorName='Dorf Bishop'), (select CategoryId from CATEGORY where CategoryName='Programming') ) GO INSERT INTO BOOK (Title, ISBN, PublisherId, AuthorId, CategoryId) VALUES ( 'The Lord of the Rings', '2-‐09-‐066556-‐2', (select PublisherId from PUBLISHER where PublisherName='McGraw-‐Hill'), (select AuthorId from AUTHOR where AuthorName='J.R.R Tolkien'), (select CategoryId from CATEGORY where CategoryName='Novel') ) GO
103 ASP.NET Web Form Create a New Project in Visual Studio 104 Add a New Web Form (“Books.aspx”) Right-‐click in the SoluOons Explorer and select “Add New Item” Books.aspx 105 Create the following GUI (“Books.aspx”) Header GridView (Drag and Drop from the Toolbox) 106 Create the following Code (“Books.aspx.cs”) using System.Web.Configuration; using DatabaseWebApp.Data; ... public partial class BookList : System.Web.UI.Page { Reference to our Class that communicates with the Database private string connectionString = WebConfigurationManager.ConnectionStrings["LibraryDBConnectionString"].ConnectionString; void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { FillBookGrid(); } } private void FillBookGrid() { List<Book> bookList = new List<Book>(); Book book = new Book(); bookList = book.GetBooks(connectionString); gridBookList.DataSource = bookList; gridBookList.DataBind(); } }
Note!! We shall create the ConnecOon String to the Database in the “Web.conﬁg page” See next slides for implementaOon of the Book Class 107 Create Database Code – Create a new Class (“Books.cs”) Books.cs 108 Create the Following Class in “Books.cs” 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>(); 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; } }
GetBookData is a View (see next slide) 109 SQL Script – Views – “GetBookData” IF EXISTS (SELECT name FROM sysobjects WHERE name = 'GetBookData' AND type = 'V') DROP VIEW GetBookData GO CREATE VIEW GetBookData AS SELECT BOOK.BookId, BOOK.Title, BOOK.ISBN, PUBLISHER.PublisherName, AUTHOR.AuthorName, CATEGORY.CategoryName FROM BOOK INNER JOIN AUTHOR ON BOOK.AuthorId = AUTHOR.AuthorId INNER JOIN PUBLISHER ON BOOK.PublisherId = PUBLISHER.PublisherId INNER JOIN CATEGORY ON BOOK.CategoryId = CATEGORY.CategoryId GO
110 Create Database ConnecOon String in “Web.conﬁg” Your Database <connectionStrings> <add name="LibraryDBConnectionString" connectionString="Data Source=macwin8;Initial Catalog=BOOKS;Persist Security Info=True;User ID=sa;Password=xxx" providerName="System.Data.SqlClient" /> </connectionStrings>
Where “xxx” is your SQL Server Database Password Finally, Run your applicaOon: CongratulaOons! It works! UserName and Password for your SQL Server CongratulaOons! -‐ You are ﬁnished with the Task 112 Challenge: Create Add, Edit and Delete as well Step by Step Guide: h-p://home.hit.no/~hansha/documents/sobware/sobware_development/topics/resources/programming/
exercises/Create%20Database%20WebForm%20App/Create%20Database%20WebForm%20App.pdf CongratulaOons! -‐ You are ﬁnished with the Task 114 WebShop Database Design Hans-‐Pe-er Halvorsen, M.Sc. 115 Students: Design the Database Tables (ER diagram) for a typical Web Shop CongratulaOons! -‐ You are ﬁnished with the Task 117 CongratulaOons! -‐ You are ﬁnished with all the Tasks in the Assignment! Hans-‐Pecer Halvorsen, M.Sc. Telemark University College Faculty of Technology Department of Electrical Engineering, InformaYon Technology and CyberneYcs E-‐mail: hans.p.halvorsen@hit.no Blog: hcp://home.hit.no/~hansha/ 119