Database Programming

1
Database Programming

• Yingcai Xiao

2
Introduction to Database
3
Definitions

• Computer Science the science of data processing
  using a computational device.
• Database (DB) a persistent store of data in a
  compact, secure, easy-and-fast-to-retrieve form.
• Database Engine a software program that creates
  and manages databases. (e.g. MS Jet Engine)
• Database Management System (DBMS) a database
  engine plus user interfaces and other supporting
  software.
• DBMS Examples Oracle, DB2 (IBM), Sybase,
  Informix, Microsoft SQL Server, Microsoft Access,
  MySQL (public domain),
• Database Server a DBMS that provides data to its
  remote clients.

.
4
Definitions Cont.

• Database API application programming interface
  to DBMSs.
• Database Table data are stored in a database as
  tables. Each row of a table is called a record,
  each column of a table is called an attribute.

PhoneBook (Database Table Name)
Name Office Home Cell
Tom 330-972-5809 330-888-8888 330-168-8888
John 330-972-7777 330-777-7777 330-168-7777

• Database schema meta data for databases,
  defining tables and their attributes.

5
SQL

• SQL Structured Query Language, a standardized
  language for accessing and manipulating
  databases.
• The Select-From-Where Clause
• Select Office From PhoneBook Where NameTom
• Select From PhoneBook
• Three Parts of SQL
• Query data retrieval
• DML - Data Manipulation Language inserting,
  deleting, updating,
• DDL - Data Definition Language table creation,
  alteration and drop.

6
SQL Example

• CREATE DATABASE pubs
• USE pubs
• CREATE TABLE authors
• (
• au_id varchar(11) NOT NULL,
• au_lname varchar(40) NOT NULL,
• au_fname varchar(20) NOT NULL,
• phone char(12) NOT NULL DEFAULT
  'UNKNOWN',
• address varchar(40) NULL,
• city varchar(20) NULL,
• state char(2) NULL,
• zip char(5) NULL,
• contract bit NOT NULL,
• PRIMARY KEY(au_id)
• )

7
SQL Example

• insert authors
• values('409-56-7008', 'Bennet', 'Abraham',
  '415 658-9932',
• '6223 Bateman St.', 'Berkeley', 'CA', '94705',
  1)
• insert authors
• values('213-46-8915', 'Green', 'Marjorie',
  '415 986-7020',
• '309 63rd St. 411', 'Oakland', 'CA', '94618',
  1)

8
DBMS Example SQL Server

• By Microsoft
• Needs runtime license
• Best fit for .NET
• Features
• http//www.microsoft.com/sql/prodinfo/features/to
  p30features.mspx
• Free version SQL Server Express
• http//www.microsoft.com/sql/editions/express/def
  ault.mspx
• Available in MSDNAA.
• http//www.cs.uakron.edu/xiao/msdnaa.html

9
DBMS Example MySQL

• By MySQL AB (part of Sun after 1/16/08)
• Free http//dev.mysql.com/
• No need of runtime license
• Not the best fit for .NET
• Installed on db1.cs.uakron.edu
• How to install it at home
• http//www.cs.uakron.edu/xiao/windows/MySQL-Insta
  llation.html

10
Database Programming
11
Architecture of a Three-Tier Application
12
Architecture of a Four-Tier Application
DBMS / Database Server
Application Server
WEB S E R V E R
WEB C L I E N T
Supporting Software
App User Interface
User Interface
Application Logic
Database Engine
Database
Database API
Architecture of a Four-Tier Application
13
Building a Four-Tier Application on the CSNET
db1.cs.uakron.edu//MySQL
paush.cs.uakron.edu//PHP
WEB S E R V E R
WEB C L I E N T
Supporting Software
App User Interface
User Interface
Application Logic
Database Engine
Database
Database API
http//www.cs.uakron.edu/xiao/isp/MySQL-HowTo.htm
l
14
PHP/MySQL Example

• Create the database using cars.sql
• Read and display the Covette database table
  using access_cars.php

15
PHP/MySQL Example Programming

1. Open Database.
2. Prepare SQL string for query
3. Execute the query.
4. Display a table to show the query results.
5. Read and display the rows of data from the query.

16
PHP/MySQL Example

• lt!-- access_cars.php
• A PHP script to access the cars database
  through MySQL
• --gt
• lthtmlgt
• ltheadgt
• lttitlegt Access the cars database with MySQL
  lt/titlegt
• lt/headgt
• ltbodygt
• lt?php
• // Connect to MySQL
• db mysql_connect("db1.cs.uakron.edu3306",
  "xiaotest", "wp2009")
• if (!db)
• print "Error - Could not connect to MySQL
• exit

17
PHP/MySQL Example

• // Select the cars database
• er mysql_select_db("xiaotest")
• if (!er)
• print "Error - Could not select the cars
  database
• exit
• // Clean up the given query (delete leading and
  trailing whitespace)
• query "SELECT FROM Corvettes"
• trim(query)
• //query stripslashes(query)
• print "Striped query is query ltbr /gt
• query_html htmlspecialchars(query)
• print "ltpgt ltbgt The query is lt/bgt " . query_html
  . "lt/pgt

18
PHP/MySQL Example

• // Execute the query
• result mysql_query(query)
• if (!result)
• print "Error - the query could not be
  executed
• error mysql_error()
• print "ltpgt" . error . "lt/pgt
• exit

19
PHP/MySQL Example

• // Display the results in a table
• print "lttablegtltcaptiongt lth2gt Query Results lt/h2gt
  lt/captiongt"
• print "lttr align 'center'gt"
• // Get the number of rows in the result, as well
  as the first row
• // and the number of fields in the rows
• num_rows mysql_num_rows(result)
• print "Number of rows num_rows ltbr /gt"
• row mysql_fetch_array(result)
• num_fields mysql_num_fields(result)
• // Produce the column labels
• keys array_keys(row)

20
PHP/MySQL Example

• for (index 0 index lt num_fields index)
• print "ltthgt" . keys2 index 1 .
  "lt/thgt
• print "lt/trgt
• // Output the values of the fields in the rows
• for (row_num 0 row_num lt num_rows
  row_num)
• print "lttr align 'center'gt
• values array_values(row)
• for (index 0 index lt num_fields
  index)
• value htmlspecialchars(values2
  index 1)
• print "ltthgt" . value . "lt/thgt
• print "lt/trgt"
• row mysql_fetch_array(result)

21
PHP/MySQL Example

• print "lt/tablegt
• ?gt
• lt/bodygt
• lt/htmlgt

22
ADO .NET
23
ADO .NET

• ADO.NET is the database API for managed
  applications (application servers) to talk to
  database servers (DBMS Database Management
  Systems).
• a database API for managed applications
• a set of classes in .NET FCL System.Data
  namespace
• designed to work over the Web
• integrates effortlessly with XML
• maps very well to stateless, text-based protocol
  HTTP
• accesses databases through modules known as data
  providers ( a set of APIs that make the accesses
  easy to program).

24
Two Data Providers

• The SQL Server .NET provider
• interfaces to Microsoft SQL Server (7.0 or later)
  
• all managed code
• code runs faster
• code not portable to other databases
• The OLE DB .NET provider
• OLE Object Linking and Imbedding
• interfaces to databases through unmanaged OLE DB
  providers SQLOLEDB for SQL Server (6.5 or
  earlier), MSDAORA for Oracle and Microsoft,
  Jet.OLEDB.4.0 for Microsoft Jet database engine.
• code runs slower
• code portable to other databases

25
The System.Data.SqlClient and System.Data.OleDb
Namespaces

• Classes in System.Data.SqlClient are for SQL
  Server .NET
• using System.Data.SqlClient
• SqlConnection conn new SqlConnection
• ("serverlocalhostdatabasepubsuidsapwd")
  
• try conn.Open ()
• SqlCommand cmd new SqlCommand ("select from
  titles", conn)
• SqlDataReader reader cmd.ExecuteReader ()
• while (reader.Read ()) Console.WriteLine
  (reader"title")
• catch (SqlException ex)
• Console.WriteLine (ex.Message)
• finally conn.Close ()

26
The System.Data.SqlClient and System.Data.OleDb
Namespaces

• Classes in System.Data.OleDb are for OLE DB .NET
• using System.Data.OleDb
• OleDbConnection conn new OleDbConnection("provid
  ersqloledbserverlocalhostdatabasepubsuidsa
  pwd")
• try conn.Open ()
• OleDbCommand cmd
• new OleDbCommand ("select from titles",
  conn)
• OleDbDataReader reader cmd.ExecuteReader
  ()
• while (reader.Read ()) Console.WriteLine
  (reader"title")
• catch (OleDbException ex)
• Console.WriteLine (ex.Message)
• finally conn.Close ()

27
Pattern of database programming

• Create a connection object.
• Open the connection.
• Create a command object.
• Execute the command.
• Access the data.
• Close the connection.

28
Connections, Commands, and DataReaders

• Connection objects represent physical connections
  to a database.
• SqlConnection or OleDbConnection
• Command objects represent the commands performed
  on a database.
• SqlCommand or OleDbCommand
• DataReader objects represent the data obtained by
  the commands.
• SqlDataReader or OleDbDataReader

29
Connection Objects

• The SqlConnection Class
• The ConnectionString
• SqlConnection conn new SqlConnection ()
• conn.ConnectionString
• "serverlocalhostdatabasepubsuidsapwd"
• or
• SqlConnection conn new SqlConnection
• ("serverlocalhostdatabasepubsuidsapwd")
  
• Errors in the connection string only throws
  exceptions at runtime.

30
Server

• Server
• Serverlocalhost or Server(local) or Data
  Source(local)
• SQL Server permits different instances of servers
  to be installed on a given machine.
• serverdb1 (an database server computer named
  db1 at the CS department of UA)
• serverhawkeye\wintellect (an instance of SQL
  Server named Wintellect on a remote machine named
  Hawkeye)
• Database or Initial Catalog database name (e.g.
  Pubs)
• UID or User ID, Pwd tempdb, tempdb

31
Server

• Min Pool Size and Max Pool Size, the size of the
  connection pool (the defaults are 0 and 100)
• Integrated Security default to false, otherwise
  uses Windows access tokens for authentication.
• Connect Timeout how many seconds to wait for a
  connection to open (default15).
• SqlConnection conn  new SqlConnection    
• ("serverhawkeye\wintellectdatabasepubsuidsap
  wd"    
• "min pool size10max pool size50connect timeout
  10")

32
Exceptions and Closing Open Connections

• Exceptions should never go uncaught, and open
  connections should always be closed before
  terminating. (Calling Close on a connection
  thats not open isnt harmful.)
• SqlConnection conn new SqlConnection
• ("serverlocalhostdatabasepubsuidsapwd")
  //before try block
• try conn.Open ()
• // TODO Use the connection
• catch (SqlException e)
• Console.WriteLine (e.Message)
• // TODO Handle the exception
• finally conn.Close ()

33
Command Classes SqlCommand and OleDbCommand.

• Encapsulate SQL commands performed on a database.
• Rely on connections established.
• Include methods to execute the commands
  encapsulated inside.
• Example, delete a record from the Pubs databases
  Titles table using an SQL DELETE command
• SqlCommand  cmd  new SqlCommand       
•  ("delete from titles where title_id  'BU1032'", 
  conn)    
• cmd.CommandTimeout  10 // Allow 10 seconds,
  default 30.
• cmd.ExecuteNonQuery () // Execute the command

34
The ExecuteNonQuery Method

• For executing DML and DDL commands CREATE,
  INSERT, UPDATE, DELETE,
• Not getting any data back.
• Examples
• SqlCommand cmd new SqlCommand
• ("create database MyDatabase", conn)
• cmd.ExecuteNonQuery ()
• SqlCommand cmd new SqlCommand
• ("create table titles ", conn)
• cmd.ExecuteNonQuery ()
• SqlCommand cmd new SqlCommand
• ("insert into titles (title_id, title,
  type, pubdate) "
• "values ('JP1001', 'Programming Microsoft
  .NET', "
• "'business', 'May 2002')", conn)
• cmd.ExecuteNonQuery ()

35
The ExecuteNonQuery Method

• SqlCommand cmd new SqlCommand
• ("update titles set title_id 'JP2002' "
  
• "where title_id 'JP1001'", conn)
• cmd.ExecuteNonQuery ()
• SqlCommand cmd new SqlCommand
• ("delete from titles where title_id
  'JP2002'", conn)
• cmd.ExecuteNonQuery ()

36
The ExecuteScalar Method

• Executes a query command and returns a single
  value in the result set, such as COUNT, AVG, MIN,
  MAX, and SUM.
• SqlCommand cmd new SqlCommand
• ("select min (price) from titles", conn)
• decimal amount (decimal) cmd.ExecuteScalar
  ()
• Console.WriteLine ("ExecuteScalar returned
  0c", amount)

37
The ExecuteScalar Method

• Another common use for ExecuteScalar is to
  retrieve BLOBs (binary large objects) from
  databases.
• For example, retrieving an image from the Logo
  field of the Pubs databases Pub_info table and
  encapsulates it in a bitmap
• use System.IO
• use System.Drawing
• use System.Data.SqlClient
• SqlCommand cmd new SqlCommand
• ("select logo from pub_info where
  pub_id'0736'", conn)
• byte blob (byte) cmd.ExecuteScalar ()
• stream.Write (blob, 0, blob.Length)
• Bitmap bitmap new Bitmap (stream)
• stream.Close ()

38
Write a BLOB to a database.

• FileStream stream new FileStream("Logo.jpg",
  FileMode.Open)
• byte blob new bytestream.Length
• stream.Read (blob, 0, (int) stream.Length)
• stream.Close ()
• SqlCommand cmd new SqlCommand
• ("insert into pub_info (pub_id, logo) values
  ('9937', _at_logo)", conn)
• cmd.Parameters.Add ("_at_logo", blob)
• cmd.ExecuteNonQuery ()

39
The ExecuteReader Method

• For performing database queries and obtain the
  results as quickly and efficiently as possible.
• Returns a DataReader object.
• Pulls back only the data to be Read by the
  DataReader not all records satisfying the query
  condition.
• SqlCommand cmd new SqlCommand ("select from
  titles", conn)
• SqlDataReader reader cmd.ExecuteReader ()
• while (reader.Read ()) Console.WriteLine
  (reader"title")
• Each call to Read returns one row from the
  result set.
• It uses a property indexer to extract the value
  of the records title field.
• Fields can be referenced by name or by numeric
  index (0-based).

40
DataReader

• Reads data.
• Reads schema (meta data) .
• Stream-based access to the results of database
  queries.
• Fast and efficient.
• Read-only and forward-only.
• Closing a DataReader reader.Close( )
  does NOT close the connection, only frees it for
  others to use.
• D-E-F-E-N-S-I-V-E P-R-O-G-R-A-M-M-I-N-G.

41
DataSets

• Set-based Database Accesses
• capture an entire query in memory
• support backward and forward traversal
• edit data and propagate the changes back to the
  database.

42
DataSet, DataTable and DataAdapter

• .NET supports set-based database accesses through
  three classes
• DataSet equivalent of an in-memory database.
• It consists of a collection of DataTables.
• DataTables are created by a DataAdapter
  (SqlDataAdapter and OleDbDataAdapter).
• DataSet doesnt interact with databases directly.
  DataAdapter reads the physical data sources and
  fills DataTables and DataSets

43
DataSets vs. DataReaders

• To simply query a database and read through the
  records one at a time until you find the one
  youre looking for, then DataReader is the right
  tool. DataReaders (1) retrieve only the data that
  you actually use, and (2) they dont consume
  memory by not storing every record that you read,
  but (3) they cant iterate backward.
• To use all the query results and to iterate
  backward and forward through a result set, or to
  cache the result set in memory, use a DataSet.
• Many controls that support DataSets are perfectly
  capable of binding to DataReaders.

44
DataGrid (GUI)

• DataGrid is an ASP control for displaying
  datasets.
• Database displaying procedure
• Use DataAdapter to get data from the database.
• Fill the data into a DataSet
• Bind the DataSet to a DataGrid
• Select the fields (columns) to be displayed and
  their header texts.

45
Example DataAdapter, DataSet and DataGrid (GUI)

• ltaspDataGrid ID"MyDataGrid"
• OnItemCommand"OnItemCommand"
  RunAt"server"gt
• ltColumnsgt
• ltaspBoundColumn HeaderText"Title"
• DataField"title" /gt
• ltaspBoundColumn HeaderText"Price"
• DataField"price"
  DataFormatString"0c"/gt
• ltaspButtonColumn HeaderText"Action"
  
• Text"Add to Cart" CommandName"AddToCart"
  /gt
• lt/Columnsgt
• lt/aspDataGridgt
• Examples/C9/Congo-MySQL/ViewCart.aspx

46
Example DataAdapter, DataSet and DataGrid (GUI)

• void Page_Load (Object sender, EventArgs e)
• if (!IsPostBack)
• string ConnectString
• ConfigurationSettings.AppSettings"c
  onnectString"
• MySqlDataAdapter adapter new
  MySqlDataAdapter
• ("select from titles where price
  ! 0", ConnectString)
• DataSet ds new DataSet ()
• adapter.Fill (ds)
• MyDataGrid.DataSource ds
• MyDataGrid.DataBind ()//Bind data to
  GUI

47
Transaction Commands

• A transaction is a logical unit of operations
  grouped together.
• If one of the operations fails, the others will
  fail (or be rolled back).
• Distributed transactions transactions that span
  two or more databases.
• The .NET Framework supports distributed
  transactions.
• The .NET supports local transactions (one
  database)

48
Transacted Commands

• // Start a local transaction
• trans conn.BeginTransaction
  (IsolationLevel.Serializable)
• // Create and initialize a SqlCommand object
• SqlCommand cmd new SqlCommand ()
• cmd.Connection conn
• cmd.Transaction trans
• // Debit 1,000 from account 1111
• cmd.CommandText "update accounts set
  balance "
• "balance - 1000 where account_id
  '1111'"
• cmd.ExecuteNonQuery ()
• // Credit 1,000 to account 2222
• cmd.CommandText "update accounts set
  balance "
• "balance 1000 where account_id
  '2222'"
• cmd.ExecuteNonQuery ()
• // Commit the transaction (commit changes)
• trans.Commit ()

49
Transacted Commands

• IsolationLevel.Serializable locks down the
  records while theyre updated so that they cant
  be read or written.
• Committing the transaction writes the changes to
  the database.

50
Uses DataGrid to represent a DataSet in XML

• DataSet ds new DataSet ()
• ds.ReadXml (Server.MapPath ("Bonuses.xml"))
• MyDataGrid.DataSource ds

51
Summary

• Database Programming part of the architecture of
  a multi-tier application.
• DB Programming API
• PHP DB Code
• ADO.NET
• Data Providers (SQLServer Provider, OLEDB
  Provider)
• Connection
• Commands (NonQuery, Scaler)
• DataReader
• DataSet
• DataAdapter
• DataGrid
• Transaction