database’communicaon’’ in’visual’studio/c#’...
TRANSCRIPT
Database Communica/on in Visual Studio/C#
using ASP.NET Web Forms
Hans-‐PeBer Halvorsen, M.Sc.
Web Programming
Hans-‐PeBer Halvorsen, M.Sc.
Web is the Present and the Future
3
History of the Web
• Internet (1960s) • World Wide Web -‐ WWW (1991) • First Web Browser -‐ Netscape, 1994 • Google, 1998 • Facebook, 2004 • Smartphones, 2007 • Tablets, 2010
4
5
The Web Browser O. W
idde
r. (201
3). geek&
poke. A
vailable: hBp://geek-‐and
-‐poke.com
Internet Explorer Chrome
Firefox
Opera
Safari
6
Web Pages Examples
HTML
JavaScript CSS
Web Programming
Use HTML to define the content of web pages
Use CSS to specify the layout of web pages
The Web Programming Triangle
Use JavaScript to program the behavior of web pages
8
CSS
JavaScript
Web Server
Web Architecture
9
Web Server
Web Browser
HTML JavaScript
Client
CSS
Server-‐side
Internet Explorer Chrome Firefox Opera Safari
<!DOCTYPE html> <html> <body> <h1>My First Heading</h1> <p>My first paragraph.</p> </body> </html>
Web Plaaorm
10
Client-‐side
Server-‐side
Web Browser
Web Server
Web Page (HTML)
HTML, CSS, JavaScript
ASP.NET, PHP, ...
Internet Informa/on Services (IIS), Apache, etc.
The code runs on the server and converted to HTML
before sending to client (Web Browser)
The Web Browser creates the visual web page you see in the browser based on the HTML code
HTML • HyperText Markup Language (HTML) • The Visual appearnce of a Web Site • “Web Browser Language”: All Web Browser understand HTML
• HTML 5 is the latest • Maintened by W3C -‐ World Wide Web Consor/um
11
<!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Title of the document</title> </head> <body> Content of the document...... </body> </html>
CSS • CSS – Cascading Style Sheets • Styles define how to display HTML elements • CSS is used to control the style and layout of mul/ple Web pages all at once
12
body { background-color: #d0e4fe; } h1 { color: orange; text-align: center; } p { font-family: "Times New Roman"; font-size: 20px; }
JavaScript • JavaScript is the programming language of the Web. • All modern HTML pages are using JavaScript. • JavaScript is the default scrip/ng language in all modern browsers, and in HTML5.
• JavaScript is probably the most popular programming language in the world.
• It is the language for HTML, for the Web, for computers, servers, laptops, tablets, smart phones, and more.
• JavaScript can Change HTML Elements! – which makes it very powerful!
13
Why JavaScript?
JavaScript is one of 3 languages all web developers must learn: • 1. HTML to define the content of web pages • 2. CSS to specify the layout of web pages • 3. JavaScript to program the behavior of web pages
This tutorial is about JavaScript, and how JavaScript works with HTML and CSS.
14
Web Server The term web server can refer to either the hardware (the computer) or the sohware (the computer applica/on) that helps to deliver web content that can be accessed through the Internet. The most common use of web servers is to host websites, but there are other uses such as gaming, data storage or running enterprise applica/ons.
• IIS -‐ Internet Informa/on Services – Microsoh Windows
• Apache Web Server – Open Source – Cross-‐plaaorm: UNIX, Linux, OS X, Windows, ...
• Nginx (pronounced "engine x") -‐ Has become very popular latly
• GWS (Google Web Server) 15
Web Server
16 hBp://www.digi.no/921119/under-‐halvparten-‐bruker-‐apache
Web Programming
• HTML/HTML5 • ASP.NET • PHP • AJAX • JavaScript • CSS • ...
17 HTTP Error 500 Internal Server Error
O. W
idde
r. (201
3). geek&
poke. A
vailable: hBp://geek-‐and
-‐poke.com
18
HTML
CSS JavaScript
IIS
ASP.NET
AJAX PHP
SQL Web Services
JQuery
XML
Web API
Web
Program
ming
Basic Web Programming • HTML • CSS • JavaScript
For more Dynamic Web Programming we use e.g., • ASP.NET • SQL • AJAX • PHP • etc. (But these are not part of this Tutorial)
19
Client-‐Server Example
20
Database
Client
Web Server
Request
Response
Web Browser
Internet Informa/on Services (IIS), Apache, etc.
ASP.NET
Hans-‐PeBer Halvorsen, M.Sc.
22
Web Server
ASP.NET
C#/VB.NET
.NET Framework
Web Browser
HTML JavaScript
Server-‐side
Client
Web & ASP.NET
CSS
23
Web Sites
ASP.NET
C#/VB.NET
.NET Framework
Web Pages
Web Forms MVC
ASP.NET
ASP.NET Example
Hans-‐PeBer Halvorsen, M.Sc.
ASP.NET WebForm App
25
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 crea/ng Web Pages using C#
Database
26
This is our Example Database (Designed with ERwin)
Students: Create the Tables in ERwin and implement the Tables in SQL Server
27
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
28
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
29
-‐-‐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
SQL Script – Insert some Data into the Tables
ASP.NET Web Form
30
Create a New Project in Visual Studio
31
Add a New Web Form (“Books.aspx”)
Books.aspx
Right-‐click in the Solu/ons Explorer and select “Add New Item”
32
Create the following GUI (“Books.aspx”)
GridView (Drag and Drop from the Toolbox)
Header
33
Create the following Code (“Books.aspx.cs”) using System.Web.Configuration; using DatabaseWebApp.Data; ... public partial class BookList : System.Web.UI.Page { 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(); } }
We shall create the Connec/on String to the Database in the “Web.config page”
Reference to our Class that communicates with the Database
See next slides for implementa/on of the Book Class
34
Create Database Code – Create a new Class (“Books.cs”)
Books.cs
35
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)
36
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
37
Create Database Connec/on String in “Web.config”
<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 applica/on:
Congratula/ons! It works!
Your Database
UserName and Password for your SQL Server
38 You are finished with the Exercise
Recommended LiBerature
• Tutorial: Introduc/on to Database Systems hBp://home.hit.no/~hansha/?tutorial=database
• Tutorial: Structured Query Language (SQL) hBp://home.hit.no/~hansha/?tutorial=sql
• Tutorial: Using SQL Server in C# • Tutorial: Introduc/on to Visual Studio and C# hBp://home.hit.no/~hansha/?tutorial=csharp
39
Hans-‐PeSer Halvorsen, M.Sc. Telemark University College Faculty of Technology Department of Electrical Engineering, InformaYon Technology and CyberneYcs
E-‐mail: [email protected] Blog: hSp://home.hit.no/~hansha/
40