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 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
EmployeeID Title ManagerID VacationHours

1 Production 16 21
Technician

2 Marketing 6 42
Assistant

3 Engineering 12 2
Manager

4 Senior Tool 3 48
Designer

Int32 String Int32 Int16

C# programming lecture 10: Database 3

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 tables,
columns, and rows

ƒ The structure of a table is

referred to as its schema

C# programming lecture 10: Database 5

SQL Database

Employee
EmployeeID Title ManagerID VacationHours
1 Production 16 21
Technician

2 Marketing 6 42
Assistant

3 Engineering 12 2
Manager

4 Senior Tool 3 48
Designer

Primary key

Primary key: no two rows can have the same EmployeeID.

EmployeeID cannot be null.

C# programming lecture 10: Database 6

ƒ Assume that we want to add data about
employees’ salary
¾ Assume an employee’s salary is changeable, we
need to add following two columns.
ƒ RateChangeDate
ƒ Rate
ƒ We can’t add additional columns for the same
employee without violating the primary key
constraint. So we use another table.
Employee
EmployeeID Title ManagerID VacationHours

1 PT 16 21

2 MA 6 42

3 EM 12 2

4 STD 3 48

EmployeeID establishs a
relationship between the
EmployeePayHistory tables.
EmployeeID RateChangeDate Rate

1 31.07.1996 12.4500

2 26.02.1997 13.4615

3 12.12.1997 43.2692

4 05.01.1998 8.6200

4 01.07.2000 23.7200

4 15.01.2002 29.8462
Employee We say that there is a
EmployeeID Title ManagerID VacationHours “foreign key constraints”
between the tables.
1 PT 16 21

2 MA 6 42 The column referenced

3 EM 12 2
in the parent table must
be a primary key
4 STD 3 48
Every value in the
foreign column must
actually appear in the
EmployeePayHistory parent table.
EmployeeID RateChangeDate Rate

1 31.07.1996 12.4500

2 26.02.1997 13.4615

3 12.12.1997 43.2692

Relation 4 05.01.1998 8.6200

one-to-many 4 01.07.2000 23.7200
parent/child
4 15.01.2002 29.8462

Foreign key
Simple SQL Queries
ƒ SELECT

SELECE * what columns to output

FROM Employee what tables are involved
WHERE VacationHours > 20 what rows are of interest

C# programming lecture 10: Database 10

Employee
EmployeeID Title ManagerID VacationHours

1 PT 16 21

2 MA 6 42

3 EM 12 2

4 STD 3 48

SELECT EmployeeID, ManagerID

FROM Employee WHERE
VacationHours > 20 EmployeeID ManagerID

1 16

2 6

4 3
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 OleDB
technology
¾ requires a knowledge of SQL

C# programming lecture 10: Database 12

ADO.NET-centric Namespaces
ƒ Core namespaces:
¾ general:
System.Data
¾ SQL Server:
System.Data.SqlClient
¾ Oracle:
System.Data.OracleClient
¾ OleDB:
System.Data.OleDb

C# programming lecture 10: Database 13

Two manners of accessing database
ƒ Connected manner
¾ explicitly connected to and disconnected from the
underlying data store
ƒ Disconnected manner
¾ using DataSet – a local copy of external data to
interact with data stores

C# programming lecture 10: Database 14

Data Providers

.NET Platform Data Provider

Connection Object DataAdapter Object

Transaction Select Command

Client Insert Command

Assembly Connection Object

Parameter Collection Update Command

Delete Command
DataReader Object

ADO.NET providers provide

access to a given DBMS.
Database

C# programming lecture 10: Database 15

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 connection object
2. Allocate and configure a command object
3. Acquire DataReader object
4. Process each record using DataReader object
5. Close connection

C# programming lecture 10: Database 17

Step 1: open connection
ƒ Connections are opened based on connection string info
¾ here we open a connection to a SQL Server database
¾ “AdvertureWorks" database must be installed on the
local machine.

using System.Data;
using System.Data.SqlClient;
...

SqlConnection cn = new SqlConnection(); connection

cn.ConnectionString =
"server=(local);database=AdventureWorks;integrated security=true";
cn.Open();

MessageBox.Show( cn.State.ToString() );

C# programming lecture 10: Database 18

Building connection strings
ƒ Connection strings are vendor-specific, not well-
documented
ƒ 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 access by database field names

string strSQL = "SELECT * FROM HumanResources.Employee";

SqlCommand myCommand = new SqlCommand(strSQL, cn);
data reader record
record
SqlDataReader myDataReader; record
myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

while (myDataReader.Read())
{
Console.WriteLine("EmployeeID: {0}, Title: {1}, ManagerID: {2}, VacationHours: {3}",
myDataReader["EmployeeID"].ToString().Trim(),
myDataReader["Title"].ToString().Trim(),
myDataReader["ManagerID"].ToString().Trim(),
myDataReader["VacationHours"].ToString().Trim());
}

C# programming lecture 10: Database 20

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?

IDbConnection dbConn = null;

try {
cn.Open();
.
.
.
}
catch(Exception ex) {
System.Diagnostics.EventLog.WriteEntry("MyApp", ex.Message);
System.Diagnostics.EventLog.WriteEntry("MyApp", ex.StackTrace);
throw ex;
}
finally {
if ((cn != null) && (cn.State != ConnectionState.Closed))
cn.Close();
}

C# programming lecture 10: Database 22

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 EmployeeID = '{0}'",
employeeID);
SqlCommand cmd = new SqlCommand(sql, cn);

try
{
cmd.ExecuteNonQuery();
}
catch
{
Console.WriteLine("Sorry! That employ cannot be deleted.");
}

C# programming lecture 10: Database 23

Example of action queries
Insert, update and delete:

Insert Into Customers(CID, FirstName, LastName,

CreditLimit, Balance) Values(118, 'Jia', 'Zhang',
10000.0, 0.0);

Update Customers Set CreditLimit = 40000000000.0,

Balance = 0.0 Where LastName = 'Gates' and
FirstName = 'Bill';

Delete From Customers Where CID = 666;

C# programming lecture 10: Database 24

Database Access (Disconnect Manner)

SqlDataAdapter
• SqlCommand
− “SELECT * FROM Table1” SQL Server
− Sql Connection
• Fill (myDataSet, “myTable”)

Client Application

DataSet
SQL
Database
Forms

C# programming lecture 10: Database 25

DataSet
ƒ DataSets are an in-memory, read-write data structure
¾ easily filled with data from a database
¾ easily displayed in a GUI app

DataSet

DataTablesCollection

DataRelationCollection

PropertyCollection

C# programming lecture 10: Database 26

DataAdapter
ƒ make use of DataSet objects to move data between
client and data store.
ƒ is used to fill DataSet with DataTable objects
ƒ send modified DataTables 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 command object
2. fill DataSet using the internal command within the data
adapter
3. operate on the DataSet
4. using data adapter to update data store with the
DataSet

C# programming lecture 10: Database 28

Example
Retrieve product info and display in a DataGrid:

string sql = "SELECT * FROM Employee";

SqlCommand myCmd = new SqlCommand(sql, cn);
SqlDataAdapter myAdapter = new SqlDataAdapter(myCmd);

DataSet myDS = new DataSet("HumanResources");

myAdapter.Fill(myDS, "Employee");

PrintDataSet(myDS);

C# programming lecture 10: Database 29

Flushing changes back to database

sql = string.Format("INSERT INTO Employee" +

"(Title, ManagerID, VacationHours) VALUES" +
"('{0}', '{1}', '{2}')", title, managerID, vacationHours);

SqlCommand insertCmd = new SqlCommand(sql, cn);

myAdapter.InsertCommand = insertCmd;

//Update Employee table with new row

DataRow newEmployee = myDS.Tables["Employee"].NewRow();
newEmployee["Title"] = title;
newEmployee["ManagerID"] = managerID;
newEmployee["VacationHours"] = vacationHours;
myDS.Tables["Employee"].Rows.Add(newEmployee);
myAdapter.Update(myDS.Tables["Employee"]);

C# programming lecture 10: Database 30

Untyped DataSets
ƒ Collection of tables
¾ Tables are collections of columns and rows
¾ Rows hold the data
¾ Filling tables does not create relations between them

carName = myDS.Tables[“Inventory"].Rows[0][“PetName”]

ƒ To use relations between tables in memory, we must write code that

builds the relations

C# programming lecture 10: Database 31

Typed DataSets
ƒ A class derived from DataSet
ƒ Incorporates the schemas for the tables it contains
ƒ Has properties and methods that allow access to tables
and columns by name
ƒ Extends DataSet, DataTable, DataRow to provide custom
classes
ƒ Table, column, and method are known by names, reducing
coding time and erros

carName = myDs.Inventory[0].PetName;

C# programming lecture 10: Database 32

Generating a typed DataSet
1. Right-click project
2. Add | Add new item.. | Data Set
3. Select a name
4. 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
ƒ A file with extension .xsd that represents the
tables and schemas
ƒ A class derived from DataSet in a .cs file

C# programming lecture 10: Database 33

Questions

C# programming lecture 10: Database 34