Database Communica/on in Visual Studio/C# using Web Services. Hans- Pe=er Halvorsen, M.Sc.

(1)

Database Communica/on

in Visual Studio/C#

using Web Services

Hans-‐Pe=er Halvorsen, M.Sc.

(2)

Background

2

•  We will use Web Services because we

assume that the the App should be used

on Internet outside the Firewall).

•  The Database is located on a Server that

has no direct access to the Internet.

(3)

Client-‐

Server

SoKware Architecture

Web

Services

APIs

3-‐Tier

Architecture

API: Applica/on Programming

Interface. Diﬀerent devices or soKware modules can share the same code.

Code once, use it many /mes

Web Services: A standard way to get data over a network/Internet using standard Web protocols (HTTP, etc.)

3-‐Tier: A way to structure your code into logical parts. Diﬀerent devices or soKware modules can share the same code.

Good SoKware!

2-‐Tier

n-‐Tier

(4)

4

The database-‐centric style. Typically, the clients communicate directly with the database.

A three-‐/er style, in which clients do not connect directly to the database.

Web Services, etc.

(5)

3-‐/er/layer Architecture

5

Presenta/on Tier

Business Logic Tier

Data Access Tier

Data Source

PL

BL

DAL

N ote ! T he d iﬀ er en t l ay er s can b e on th e sam e co m pu te r ( Lo gi c Lay er s) o r o n di ﬀe re nt Co m pu te rs in a ne tw or k (P hy si cal L ay er s)

Data Tier -‐ DL

Logic Tier

(6)

Why 3-‐Tier (N-‐Tier Architecture?)

•  Flexible applica/ons

•  Reusable code

–  Code once, use many /mes

•  Modularized

–  You need only to change part of the code

–  You can deploy only one part

–  You can Test only one part

–  Mul/ple Developers

•  Diﬀerent parts (Tiers) can be stored on diﬀerent

computers

•  Diﬀerent Pla\orms and Languages can be used

•  etc.

6

(7)

7

h=p://en.wikipedia.org/wiki/Mul//er_architecture

(8)

3-‐/er/layer Architecture

PresentaAon Tier

•  This is the topmost level of the applica/on.

•  The presenta/on /er displays informa/on related to such services as browsing

merchandise, purchasing and shopping cart contents.

•  It communicates with other /ers by which it puts out the results to the

browser/client /er and all other /ers in the network.

•  In simple terms it is a layer which users can access directly such as a web page,

or an opera/ng systems GUI

ApplicaAon Aer (business logic, logic Aer, data access Aer, or middle Aer)

•  The logical /er is pulled out from the presenta/on /er and, as its own layer.

•  It controls an applica/on’s func/onality by performing detailed processing.

Data Aer

•  This /er consists of database servers. Here informa/on is stored and retrieved.

•  This /er keeps data neutral and independent from applica/on servers or

business logic.

•  Giving data its own /er also improves scalability and performance.

8

h=p://en.wikipedia.org/wiki/Mul//er_architecture

(9)

3-‐/er Architecture

Presenta/on Tier

Business Logic Tier

Data Access Tier

Database

Presenta/on Tier Presenta/on Tier

Stored Procedures

Diﬀerent Devices can share the same Business and Data Access Code

The diﬀerent Tiers can be

physical or logical Data Tier

Logic Tier

(10)

Web Server

3-‐/er + WebService Architecture -‐ Example

10

Presenta/on Tier

Business/

Data Logic Tier

Data Source

Web Services

Te am F ou nd a/ on S er ve r

TFS Cient

Installed on one or more

Windows Servers in your LAN or in the Cloud

Data Tier

Stored Procedures

Team Founda/on Server

(11)

3-‐/er Architecture Scenarios

Presenta/on Layer

Business Logic

Data Access Logic Database

Presenta/on Layer Presenta/on Layer

Stored Procedures

Client

Web Server

Web Service

Presenta/on Layer

Client

Presenta/on Layer

Internet

Local Network (LAN)

Client Firewall

Presenta/on Layer

Server

(12)

Database Presenta/on Tier

e.g., ADO, ADO.NET Logic Tier

Web Service

Business Tier Data Access Tier

Data Tier Stored Procedures

Views Tables

Web Server

Database Server

Presenta/on Tier

Client

ASP.NET Web Forms

Web App Presenta/on Tier

Client

Client Client

WinForms

3-‐/er

Architecture

Scenarios

Firewall

Clients

Desktop App

Mobile App

Internet

Local

Network

Note! The diﬀerent Tiers can be on the same

Computer (Logic Layers) or on diﬀerent Computers in a network (Physical Layers)

Android, iOS, Windows 8/Windows Phone, etc.

PresentaAon Tiers

Devices can share the same Business/Logic Tier and APIs

Seperate Presenta/on Tier for each Device App

API

API API

API

(13)

13

Visual Studio Projects

Solu/on with all Projects (Logic Tier, Web Service, Desktop App, Web App, Mobile App)

Solu/on with Projects used by Web App (Logic Tier, Web App)

(14)

Data Tier

We are going to create the Database / Data Layer/Tier,

including:

1.  Tables

2.  Views

3.  Stored Procedures

4.  Triggers

5.  Script for some “Dummy” Data

Download Zip Files with Tables, Views, Stored Procedures

and Triggerse in order to create the Data Tier in SQL Server

(The ZIP File is located on the same place as this File)

14

Note! Install them in this order

(15)

Data Tier

15

Tables Views

Stored Procedures

SQL Server

Triggers

Data Tier

(16)

16

Database Tables

(17)

17

Execute the diﬀerent

Scripts inside SQL Server

Management Studio

(18)

18

You are ﬁnished with the Exercise

(19)

Logic Tier

19

Database Presenta/on Tier

Logic Tier

ASP.NET Web Forms

Data Tier

Presenta/on Tier Presenta/on Tier

WinForms Windows Store App

Purpose:

•  All the Apps should/could share the same Logic Tier

•  To make your Apps easier to maintain and extend

•  etc.

(20)

20

Create an Empty (Blank) SoluAon in Visual Studio

(21)

21

Add Project for Logic Tier (Data Access)

Select a “Class Library” Project

“LogicTier”

(22)

22

Add a New Class to the

Project (“StudentData.cs”)

“StudentData.cs”

(23)

23

Create the Code, e.g., like this (“StudentData.cs”):

Create your own Namespace

Improvements: Use Try... Catch ...

A View that collects data from several tables

(24)

24

You should test the SQL Query in the SQL Server

Management Studio ﬁrst

(25)

25

Code (“StudentData.cs”):

using System.Data.SqlClient;

using System.Data.SqlTypes;

using System.Data;

namespace Tuc.School.LogicTier {

public class StudentData {

public DataSet GetStudentDB(string connectionString) {

string selectSQL = "select StudentName, StudentNumber, SchoolName, ClassName, Grade from StudentData order by StudentName";

// Define the ADO.NET objects.

SqlConnection con = new SqlConnection(connectionString);

SqlDataAdapter da = new SqlDataAdapter(selectSQL, con);

DataSet ds = new DataSet();

da.Fill(ds);

return ds;

} } }

(26)

26

Create a proper name for the Assembly (.dll File)

This will be the Assembly for your Logic Tier, that can be imported and used in other projects.

Create once – use it many /mes!!

Then Build your Project (hopefully with no errors) Right-‐click on the Project in the Solu/on Explorer and select Proper/es

(27)

27

You are ﬁnished with the Exercise

(28)

28

Presenta/on Layer

Desktop App: WinForms

Using Web Services (we assume the The App should be used on Internet outside the Firewall)

Label

DataGridView

(29)

29

Step 1: Create Web Service

Add Web Service:

Create an ASP.NET Project:

“SchoolWS”

“SchoolWS.asmx”

(30)

30

Web Service Code

Database Connec/onString is located in Web.conﬁg

Web Service Method

(31)

31

Database Connec/onString is located in Web.conﬁg

(32)

32

Test Web Service

It Works!!

Click to Test the Web Service Method we created

(33)

33

Deploy/Publish Web Service to IIS

Copy Web Service Files (Project) to default IIS Directory: C:\inetpub\wwwroot

(34)

34

(35)

35

Test if WS working:

h=p://localhost/SchoolWS

(36)

36

Step 2: Use Web Service in WinForm

Create New WinForm Project:

“WinFormAppWSClient”

(37)

37

Add Web Service Reference

Our Web Service Methods

(38)

Create GUI

Label

DataGridView

(39)

39

Create Code

(40)

40

using System.Windows.Forms;

namespace WinFormAppWSClient {

public partial class FormWSClient : Form {

public FormWSClient() {

InitializeComponent();

}

private void FormWSClient_Load(object sender, EventArgs e) {

FillStudentGrid();

}

private void FillStudentGrid() {

DataSet ds = new DataSet();

SchoolWSReference.SchoolWSSoapClient schoolWs = new SchoolWSReference.SchoolWSSoapClient();

ds = schoolWs.GetStudent();

dataGridViewStudentInformation.DataSource = ds.Tables[0];

} }

WinForm Code

Call the Web Service method

Fill GridView

(41)

41

Test it:

It works!!!

(42)

42

You are ﬁnished with the Exercise

(43)

Recommended Li=erature

•  Tutorial: Introduc/on 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: Using SQL Server in C#

•  Tutorial: Introduc/on to Visual Studio and C#

h=p://home.hit.no/~hansha/?tutorial=csharp

43

(44)

Hans-‐PeUer Halvorsen, M.Sc.

Telemark University College

Faculty of Technology

Department of Electrical Engineering, InformaAon Technology and CyberneAcs

E-‐mail: [email protected]

Blog: hUp://home.hit.no/~hansha/

44

Database Communica/on in Visual Studio/C# using Web Services. Hans- Pe=er Halvorsen, M.Sc.