Chair of Software Engineering C Programming in Depth

  • Slides: 34
Download presentation
Chair of Software Engineering C# Programming in Depth Prof. Dr. Bertrand Meyer March 2007

Chair of Software Engineering C# Programming in Depth Prof. Dr. Bertrand Meyer March 2007 – May 2007 Lecture 10: Database Lisa (Ling) Liu

Database and Data Representation § Database Management System (DBMS): Ø provides efficient, convenient, and

Database and Data Representation § Database Management System (DBMS): Ø provides efficient, convenient, and safe multi-user storage of persistent data Ø provides access to massive amounts of persistent data Ø provides a programming interface that allows a user or program to § create new database and specify their struture § query and modify the data § Dominant approach: relational database and SQL C# programming lecture 10: Database 2

Database and Data Representation Employee. ID Title Manager. ID Vacation. Hours 1 Production Technician

Database and Data Representation Employee. ID Title Manager. ID Vacation. Hours 1 Production Technician 16 21 2 Marketing Assistant 6 42 3 Engineering Manager 12 2 4 Senior Tool Designer 3 48 Int 32 String Int 32 Int 16 C# programming lecture 10: Database 3

Database and Data Representation § § § A “relation” is a table of data

Database and Data Representation § § § A “relation” is a table of data The columns are known as “attributes” The row are called “tuples” It is allowable for some values to be missing We can add, remove, or update tuples Each attribute has an underlying domain, or data type C# programming lecture 10: Database 4

SQL Database § We will generally refer to the relations, attributes, and tuples as

SQL Database § We will generally refer to the relations, attributes, and tuples as tables, columns, and rows § The structure of a table is referred to as its schema C# programming lecture 10: Database 5

SQL Database Employee. ID Title Manager. ID Vacation. Hours 1 Production Technician 16 21

SQL Database Employee. ID Title Manager. ID Vacation. Hours 1 Production Technician 16 21 2 Marketing Assistant 6 42 3 Engineering Manager 12 2 4 Senior Tool Designer 3 48 Primary key: no two rows can have the same Employee. ID cannot be null. C# programming lecture 10: Database 6

§ Assume that we want to add data about employees’ salary Ø Assume an

§ Assume that we want to add data about employees’ salary Ø Assume an employee’s salary is changeable, we need to add following two columns. § Rate. Change. Date § Rate § We can’t additional columns for the same employee without violating the primary key constraint. So we use another table. C# programming lecture 10: Database 7

Employee. ID Title Manager. ID Vacation. Hours 1 PT 16 21 2 MA 6

Employee. ID Title Manager. ID Vacation. Hours 1 PT 16 21 2 MA 6 42 3 EM 12 2 4 STD 3 48 Employee. ID establishs a relationship between the tables. Employee. Pay. History Employee. ID Rate. Change. Date Rate 1 31. 07. 1996 12. 4500 2 26. 02. 1997 13. 4615 3 12. 1997 43. 2692 4 05. 01. 1998 8. 6200 4 01. 07. 2000 23. 7200 4 15. 01. 2002 29. 8462 C# programming lecture 10: Database 8

Employee. ID Title Manager. ID Vacation. Hours 1 PT 16 21 2 MA 6

Employee. ID Title Manager. ID Vacation. Hours 1 PT 16 21 2 MA 6 42 3 EM 12 2 4 STD 3 48 The column referenced in the parent table must be a primary key Every value in the foreign column must actually appear in the parent table. Employee. Pay. History Relation one-to-many parent/child We say that there is a “foreign key constraints” between the tables. Employee. ID Rate. Change. Date Rate 1 31. 07. 1996 12. 4500 2 26. 02. 1997 13. 4615 3 12. 1997 43. 2692 4 05. 01. 1998 8. 6200 4 01. 07. 2000 23. 7200 4 15. 01. 2002 29. 8462 Foreign key C# programming lecture 10: Database 9

Simple SQL Queries § SELECT SELECE * FROM Employee WHERE Vacation. Hours > 20

Simple SQL Queries § SELECT SELECE * FROM Employee WHERE Vacation. Hours > 20 what columns to output what tables are involved what rows are of interest C# programming lecture 10: Database 10

Employee. ID Title Manager. ID Vacation. Hours 1 PT 16 21 2 MA 6

Employee. ID Title Manager. ID Vacation. Hours 1 PT 16 21 2 MA 6 42 3 EM 12 2 4 STD 3 48 SELECT Employee. ID, Manager. ID FROM Employee WHERE Vacation. Hours > 20 Employee. ID Manager. ID 1 16 2 6 4 3 C# programming lecture 10: Database 11

How to interact with data stores? § ADO. NET a set of namespaces defined

How to interact with data stores? § ADO. NET a set of namespaces defined on. NET platform that understand how to interact with data stores. native support for SQL Server and Oracle Ø support for other databases via older Ole. DB technology Ø requires a knowledge of SQL Ø C# programming lecture 10: Database 12

ADO. NET-centric Namespaces § Core namespaces: Ø general: System. Data Ø SQL Server: System.

ADO. NET-centric Namespaces § Core namespaces: Ø general: System. Data Ø SQL Server: System. Data. Sql. Client Ø Oracle: System. Data. Oracle. Client Ø Ole. DB: System. Data. Ole. Db C# programming lecture 10: Database 13

Two manners of accessing database § Connected manner Ø explicitly connected to and disconnected

Two manners of accessing database § Connected manner Ø explicitly connected to and disconnected from the underlying data store § Disconnected manner Ø using Data. Set – a local copy of external data to interact with data stores C# programming lecture 10: Database 14

Data Providers . NET Platform Data Provider Data. Adapter Object Connection Object Select Command

Data Providers . NET Platform Data Provider Data. Adapter Object Connection Object Select Command Transaction Client Assembly Insert Command Connection Object Parameter Collection Delete Command Data. Reader Object ADO. NET providers provide access to a given DBMS. Update Command Database C# programming lecture 10: Database 15

Overview of database access § General steps: – open connection to database – execute

Overview of database access § General steps: – open connection to database – execute SQL to retrieve records / update DB – close connection C# programming lecture 10: Database 16

Database Access ( Connect Manner ) Five steps: 1. Allocate, configure, and open your

Database Access ( Connect Manner ) Five steps: 1. Allocate, configure, and open your connection object 2. Allocate and configure a command object 3. Acquire Data. Reader object 4. Process each record using Data. Reader object 5. Close connection C# programming lecture 10: Database 17

Step 1: open connection § Connections are opened based on connection string info here

Step 1: open connection § Connections are opened based on connection string info here we open a connection to a SQL Server database Ø “Adverture. Works" database must be installed on the local machine. Ø using System. Data; using System. Data. Sql. Client; . . . connection Sql. Connection cn = new Sql. Connection(); cn. Connection. String = "server=(local); database=Adventure. Works; integrated security=true"; cn. Open(); Message. Box. Show( cn. State. To. String() ); C# programming lecture 10: Database 18

Building connection strings § Connection strings are vendor-specific, not welldocumented § Where to turn

Building connection strings § Connection strings are vendor-specific, not welldocumented § Where to turn for help? Ø www. connectionstrings. com Ø www. carlprothman. net/Default. aspx? tabid=81 C# programming lecture 10: Database 19

Step 2 -4: retrieve records § Retrieve records via SQL Select query Ø read-only

Step 2 -4: retrieve records § Retrieve records via SQL Select query Ø read-only access by database field names string str. SQL = "SELECT * FROM Human. Resources. Employee"; Sql. Command my. Command = new Sql. Command(str. SQL, cn); data reader record Sql. Data. Reader my. Data. Reader; record my. Data. Reader = my. Command. Execute. Reader(Command. Behavior. Close. Connection); while (my. Data. Reader. Read()) { Console. Write. Line("Employee. ID: {0}, Title: {1}, Manager. ID: {2}, Vacation. Hours: {3}", my. Data. Reader["Employee. ID"]. To. String(). Trim(), my. Data. Reader["Title"]. To. String(). Trim(), my. Data. Reader["Manager. ID"]. To. String(). Trim(), my. Data. Reader["Vacation. Hours"]. To. String(). Trim()); } C# programming lecture 10: Database 20

Step 5: close connection § Be sure to close connection… Ø to flush pending

Step 5: close connection § Be sure to close connection… Ø to flush pending updates Ø so others can access DB (connections are limited resources) cn. Close(); C# programming lecture 10: Database 21

Guaranteed close? IDb. Connection db. Conn = null; try { cn. Open(); . .

Guaranteed close? IDb. Connection db. Conn = null; try { cn. Open(); . . . } catch(Exception ex) { System. Diagnostics. Event. Log. Write. Entry("My. App", ex. Message); System. Diagnostics. Event. Log. Write. Entry("My. App", ex. Stack. Trace); throw ex; } finally { if ((cn != null) && (cn. State != Connection. State. Closed)) cn. Close(); } C# programming lecture 10: Database 22

Updating a database To update database, execute an SQL Action query Example: Ø delete

Updating a database To update database, execute an SQL Action query Example: Ø delete employee by their id number string sql = string. Format("DELETE FROM Employee WHERE Employee. ID = '{0}'", employee. ID); Sql. Command cmd = new Sql. Command(sql, cn); try { cmd. Execute. Non. Query(); } catch { Console. Write. Line("Sorry! That employ cannot be deleted. "); } C# programming lecture 10: Database 23

Example of action queries Insert, update and delete: Insert Into Customers(CID, First. Name, Last.

Example of action queries Insert, update and delete: Insert Into Customers(CID, First. Name, Last. Name, Credit. Limit, Balance) Values(118, 'Jia', 'Zhang', 10000. 0, 0. 0); Update Customers Set Credit. Limit = 400000. 0, Balance = 0. 0 Where Last. Name = 'Gates' and First. Name = 'Bill'; Delete From Customers Where CID = 666; C# programming lecture 10: Database 24

Database Access (Disconnect Manner) Sql. Data. Adapter • Sql. Command − “SELECT * FROM

Database Access (Disconnect Manner) Sql. Data. Adapter • Sql. Command − “SELECT * FROM Table 1” − Sql Connection • Fill (my. Data. Set, “my. Table”) SQL Server Client Application Data. Set SQL Database Forms C# programming lecture 10: Database 25

Data. Set § Data. Sets are an in-memory, read-write data structure easily filled with

Data. Set § Data. Sets are an in-memory, read-write data structure easily filled with data from a database Ø easily displayed in a GUI app Ø Data. Set Data. Tables. Collection Data. Relation. Collection Property. Collection C# programming lecture 10: Database 26

Data. Adapter § make use of Data. Set objects to move data between client

Data. Adapter § make use of Data. Set objects to move data between client and data store. § is used to fill Data. Set with Data. Table objects § send modified Data. Tables back to the database for processing § take care of connection, hence client don’t need to explicitly open and close the connection with DBMS C# programming lecture 10: Database 27

Steps 1. construct data adapter with a valid connection or connection string and a

Steps 1. construct data adapter with a valid connection or connection string and a command object 2. fill Data. Set using the internal command within the data adapter 3. operate on the Data. Set 4. using data adapter to update data store with the Data. Set C# programming lecture 10: Database 28

Example Retrieve product info and display in a Data. Grid: string sql = "SELECT

Example Retrieve product info and display in a Data. Grid: string sql = "SELECT * FROM Employee"; Sql. Command my. Cmd = new Sql. Command(sql, cn); Sql. Data. Adapter my. Adapter = new Sql. Data. Adapter(my. Cmd); Data. Set my. DS = new Data. Set("Human. Resources"); my. Adapter. Fill(my. DS, "Employee"); Print. Data. Set(my. DS); C# programming lecture 10: Database 29

Flushing changes back to database sql = string. Format("INSERT INTO Employee" + "(Title, Manager.

Flushing changes back to database sql = string. Format("INSERT INTO Employee" + "(Title, Manager. ID, Vacation. Hours) VALUES" + "('{0}', '{1}', '{2}')", title, manager. ID, vacation. Hours); Sql. Command insert. Cmd = new Sql. Command(sql, cn); my. Adapter. Insert. Command = insert. Cmd; //Update Employee table with new row Data. Row new. Employee = my. DS. Tables["Employee"]. New. Row(); new. Employee["Title"] = title; new. Employee["Manager. ID"] = manager. ID; new. Employee["Vacation. Hours"] = vacation. Hours; my. DS. Tables["Employee"]. Rows. Add(new. Employee); my. Adapter. Update(my. DS. Tables["Employee"]); C# programming lecture 10: Database 30

Untyped Data. Sets § Collection of tables Ø Tables are collections of columns and

Untyped Data. Sets § Collection of tables Ø Tables are collections of columns and rows Ø Rows hold the data Ø Filling tables does not create relations between them car. Name = my. DS. Tables[“Inventory"]. Rows[0][“Pet. Name”] § To use relations between tables in memory, we must write code that builds the relations C# programming lecture 10: Database 31

Typed Data. Sets § A class derived from Data. Set § Incorporates the schemas

Typed Data. Sets § A class derived from Data. Set § Incorporates the schemas for the tables it contains § Has properties and methods that allow access to tables and columns by name § Extends Data. Set, Data. Table, Data. Row to provide custom classes § Table, column, and method are known by names, reducing coding time and erros car. Name = my. Ds. Inventory[0]. Pet. Name; C# programming lecture 10: Database 32

Generating a typed Data. Set 1. 2. 3. 4. Right-click project Add | Add

Generating a typed Data. Set 1. 2. 3. 4. Right-click project Add | Add new item. . | Data Set Select a name Find the tables of interest in the Server Explorer and dray them onto the design surface 5. Drag relations onto the child tables and verify the settings 6. Save 1. A file with extension. xsd that represents the tables and schemas 2. A class derived from Data. Set in a. cs file C# programming lecture 10: Database 33

Questions C# programming lecture 10: Database 34

Questions C# programming lecture 10: Database 34