Chapter 12 ADO NET Yingcai Xiao Introduction to

Chapter 12 ADO. NET Yingcai Xiao

Introduction to Database

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, DB 2 (IBM), Sybase, Informix, Microsoft SQL Server, Microsoft Access, My. SQL (public domain), … § Database Server: a DBMS that provides data to its remote clients.

Architecture of a Four-Tier Application Supporting Software App User Interface Application Logic Database Engine Database DBMS / Database Server Database API Application Server Architecture of a Four-Tier Application WEB S E R V E R C L I E N T

DBMS Example: SQL Server • • By Microsoft Needs runtime license Best fit for. NET Features http: //www. microsoft. com/sql/prodinfo/features/top 30 features. mspx • Available in MSDNAA. http: //www. cs. uakron. edu/~xiao/msdnaa. html • Free-for-all version: SQL Server Express http: //www. microsoft. com/sql/editions/express/default. mspx

DBMS Example: My. SQL • By My. SQL AB (part of Sun after 1/16/08. Sun has been merged into Oracle. ) • Free: http: //dev. mysql. com/ • No need of runtime license • Not the best fit for. NET

My. SQL on db 1. cs. uakron. edu • My. SQL is used for the following classes: Windows Programming, Internet System Programming, Introduction to Database, Data Mining, and Data Integration. • It has been installed on db 1. cs. uakron. edu • To use it, follow the instructions at http: //www. cs. uakron. edu/~xiao/windows/4 T-App. Deployment. html • To install it at home, follow the instructions at http: //www. cs. uakron. edu/~xiao/windows/My. SQLInstallation. html

Data Format in the Database § 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. Each needs to have a “key” attribute(s). Each record needs to have a unique key value. Phone. Book (Database Table Name) Name Office Home Cell Tom 330 -972 -5809 330 -8888 330 -168 -8888 John 330 -972 -7777 330 -7777 330 -168 -7777 … … § Database schema: meta data for databases, defining tables and their attributes.

Database Schema, Language, API § Database schema: meta data for databases, defining tables and their attributes. (UDT, like classes) § SQL (Structured Query Language): the de facto standard language for database. § Database API: application programming interface to DBMSs.

SQL § SQL: Structured Query Language, a standardized language for accessing and manipulating databases. § The Select-From-Where Clause: Select Office From Phone. Book Where Name=’Tom’; Select * From Phone. Book; § Three Parts of SQL: § Query: data retrieval § DML - Data Manipulation Language: inserting, deleting, updating, … § DDL - Data Definition Language: table creation, alteration and drop.

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) );

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 63 rd St. #411', 'Oakland', 'CA', '94618', 1);

Database Programming

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).

Two Data Providers 1. The SQL Server. NET provider § § interfaces to Microsoft SQL Server (7. 0 or later) all managed code runs faster code not portable to other databases 2. 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

The System. Data. Sql. Client and System. Data. Ole. Db Namespaces Classes in System. Data. Sql. Client are for SQL Server. NET using System. Data. Sql. Client; Sql. Connection conn = new Sql. Connection ("server=localhost; database=pubs; uid=sa; pwd="); try { conn. Open (); Sql. Command cmd = new Sql. Command ("select * from titles", conn); Sql. Data. Reader reader = cmd. Execute. Reader (); while (reader. Read ()) Console. Write. Line (reader["title"]); } catch (Sql. Exception ex) { Console. Write. Line (ex. Message); } finally { conn. Close (); }

The System. Data. Sql. Client and System. Data. Ole. Db Namespaces Classes in System. Data. Ole. Db are for OLE DB. NET using System. Data. Ole. Db; Ole. Db. Connection conn = new Ole. Db. Connection("provider=sqloledb; server=localhost; dat abase=pubs; uid=sa; pwd="); try { conn. Open (); Ole. Db. Command cmd = new Ole. Db. Command ("select * from titles", conn); Ole. Db. Data. Reader reader = cmd. Execute. Reader (); while (reader. Read ()) Console. Write. Line (reader["title"]); } catch (Ole. Db. Exception ex) { Console. Write. Line (ex. Message); } finally { conn. Close (); }

Pattern of database programming § § § n Create a connection object. Open the connection. Create a command object. Execute the command. Access the data. Close the connection.

Pattern of database programming § Create a connection object. § Open the connection.

Connection Objects The Sql. Connection Class The Connection. String Sql. Connection conn = new Sql. Connection (); conn. Connection. String = "server=localhost; database=pubs; uid=sa; pwd="; or Sql. Connection conn = new Sql. Connection ("server=localhost; database=pubs; uid=sa; pwd="); Errors in the connection string only throws exceptions at runtime.

Server=localhost or Server=(local) or Data Source=(local) SQL Server permits different instances of servers to be installed on a given machine. server=db 1 (an database server computer named “db 1” at the CS department of UA) server=hawkeyewintellect (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

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 (default=15). Sql. Connection conn = new Sql. Connection ("server=hawkeyewintellect; database=pubs; uid=sa; pwd=; " + "min pool size=10; max pool size=50; connect timeout=10");

Exceptions and Closing Open Connections § Exceptions should never go uncaught, and open connections should always be closed before terminating. (Calling Close on a connection that’s not open isn’t harmful. ) Sql. Connection conn = new Sql. Connection ("server=localhost; database=pubs; uid=sa; pwd="); //before try block try {conn. Open (); // TODO: Use the connection } catch (Sql. Exception e) { Console. Write. Line (e. Message); // TODO: Handle the exception } finally { conn. Close (); }

Pattern of database programming § Create a connection object. § Open the connection. § Do not hardcode the connection string in your code. Use web. config and session cache to allow customization.

A mini enterprise application Congo (C 9) Congo: A virtual storefront for an online bookseller. Related to: database => data grid => shopping cart Forms: Database supported, web based security. ExamplesC 9Congo-My. SQL http: /winserv 1. cs. uakron. edu/xiaotest/Congo. aspx Deployment: http: //www. cs. uakron. edu/~xiao/windows/4 T-App-Deployment. html Output format in C# http: //blog. stevex. net/string-formatting-in-csharp/

Structure of an ASP. NET Web Application An ASP. NET application. § The Web. config File To support XCOPY installs— to install applications by copying them to a directory and uninstall them by deleting the files and directories.

Web. config Strings defined in the. config file can be retrieved in the program at run time: string conn = Configuration. Settings. App. Settings ["My. Connection. String"]; <!-- Web. Configuration File --> <configuration> <app. Settings> <add key="My. Connection. String" value="server=db 1; database=pubs; uid=sa; pwd=" /> <add key="connect. String" value="Integrated Security=true; Initial Catalog=pubs; Data Source=XIAO-T 23 -01" /> </app. Settings> </configuration>

Pattern of database programming § § Create a connection object. Open the connection. Create a command object. Execute the command.

Connections, Commands, and Data. Readers n n n Connection objects represent physical connections to a database. Sql. Connection or Ole. Db. Connection Command objects represent the commands performed on a database. Sql. Command or Ole. Db. Command Data. Reader objects represent the data obtained by the commands. Sql. Data. Reader or Ole. Db. Data. Reader

Command Classes: Sql. Command Ole. Db. Command. – 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 database’s “Titles” table using an SQL DELETE command: Sql. Command cmd = new Sql. Command ("delete from titles where title_id = 'BU 1032'", conn); cmd. Command. Timeout = 10; // Allow 10 seconds, default 30. cmd. Execute. Non. Query (); // Execute the command

The Execute. Non. Query Method n n n For executing DML and DDL commands: CREATE, INSERT, UPDATE, DELETE, … Not getting any data back. Examples: Sql. Command cmd = new Sql. Command ("create database My. Database", conn); cmd. Execute. Non. Query (); Sql. Command cmd = new Sql. Command ("create table titles …", conn); cmd. Execute. Non. Query (); Sql. Command cmd = new Sql. Command ("insert into titles (title_id, title, type, pubdate) " + "values ('JP 1001', 'Programming Microsoft. NET', " + "'business', 'May 2002')", conn); cmd. Execute. Non. Query ();

The Execute. Non. Query Method Sql. Command cmd = new Sql. Command ("update titles set title_id = 'JP 2002' " + "where title_id = 'JP 1001'", conn); cmd. Execute. Non. Query (); Sql. Command cmd = new Sql. Command ("delete from titles where title_id = 'JP 2002'", conn); cmd. Execute. Non. Query ();

The Execute. Scalar Method Executes a query command returns a single value in the result set, such as COUNT, AVG, MIN, MAX, and SUM. Sql. Command cmd = new Sql. Command ("select min (price) from titles", conn); decimal amount = (decimal) cmd. Execute. Scalar (); Console. Write. Line ("Execute. Scalar returned {0: c}", amount);

The Execute. Scalar Method Another common use for Execute. Scalar is to retrieve BLOBs (binary large objects) from databases. u For example, retrieving an image from the “Logo” field of the Pubs database’s “Pub_info” table and encapsulates it in a bitmap: u use System. IO; use System. Drawing; use System. Data. Sql. Client; Sql. Command cmd = new Sql. Command ("select logo from pub_info where pub_id='0736'", conn); byte[] blob = (byte[]) cmd. Execute. Scalar (); stream. Write (blob, 0, blob. Length); Bitmap bitmap = new Bitmap (stream); stream. Close ();

Write a BLOB to a database. File. Stream stream = new File. Stream("Logo. jpg", File. Mode. Open); byte[] blob = new byte[stream. Length]; stream. Read (blob, 0, (int) stream. Length); stream. Close (); Sql. Command cmd = new Sql. Command ("insert into pub_info (pub_id, logo) values ('9937', @logo)", conn); cmd. Parameters. Add ("@logo", blob); cmd. Execute. Non. Query ();

The Execute. Reader Method § For performing database queries and obtain the results as quickly and efficiently as possible. § Returns a Data. Reader object. § Pulls back only the data to be “Read” by the Data. Reader not all records satisfying the query condition. Sql. Command cmd = new Sql. Command ("select * from titles", conn); Sql. Data. Reader reader = cmd. Execute. Reader (); while (reader. Read ()) Console. Write. Line (reader["title"]); § Each call to “Read” returns one row from the result set. § It uses a property indexer to extract the value of the record’s “title” field. § Fields can be referenced by name or by numeric index (0 based).

Data. Reader § § § § 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 Data. Reader: 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.

Data. Sets p § § § 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.

Data. Set, Data. Table and Data. Adapter p . NET supports set-based database accesses through three classes: § Data. Set: equivalent of an in-memory database. It consists of a collection of Data. Tables. § Data. Tables are created by a Data. Adapter (Sql. Data. Adapter and Ole. Db. Data. Adapter). § Data. Set doesn’t interact with databases directly. Data. Adapter reads the physical data sources and fills Data. Tables and Data. Sets

Data. Sets vs. Data. Readers § To simply query a database and read through the records one at a time until you find the one you’re looking for, then Data. Reader is the right tool. Data. Readers (1) retrieve only the data that you actually use, and (2) they don’t consume memory by not storing every record that you read, but (3) they can’t 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 Data. Set. § Many controls that support Data. Sets are perfectly capable of binding to Data. Readers.

Data. Grid (GUI) • Data. Grid is an ASP control for displaying datasets. • Database displaying procedure: – Use Data. Adapter to get data from the database. – Fill the data into a Data. Set – Bind the Data. Set to a Data. Grid – Select the fields (columns) to be displayed and their header texts.

Example: Data. Adapter, Data. Set and Data. Grid (GUI) <asp: Data. Grid ID="My. Data. Grid" On. Item. Command="On. Item. Command" Run. At="server"> <Columns> <asp: Bound. Column Header. Text="Title" Data. Field="title" /> <asp: Bound. Column Header. Text="Price" Data. Field="price" Data. Format. String="{0: c}"/> <asp: Button. Column Header. Text="Action" Text="Add to Cart" Command. Name="Add. To. Cart" /> </Columns> </asp: Data. Grid> Examples/C 9/Congo-My. SQL/View. Cart. aspx

Example: Data. Adapter, Data. Set and Data. Grid (GUI) void Page_Load (Object sender, Event. Args e) { if (!Is. Post. Back) { string Connect. String = Configuration. Settings. App. Settings["connect. String"]; My. Sql. Data. Adapter adapter = new My. Sql. Data. Adapter ("select * from titles where price != 0", Connect. String); Data. Set ds = new Data. Set (); adapter. Fill (ds); My. Data. Grid. Data. Source = ds; My. Data. Grid. Data. Bind (); //Bind data to GUI } }

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):

Transaction Commands // Start a local transaction trans = conn. Begin. Transaction (Isolation. Level. Serializable); // Create and initialize a Sql. Command object Sql. Command cmd = new Sql. Command (); cmd. Connection = conn; cmd. Transaction = trans; // Debit $1, 000 from account 1111 cmd. Command. Text = "update accounts set balance = " + "balance - 1000 where account_id = '1111'"; cmd. Execute. Non. Query (); // Credit $1, 000 to account 2222 cmd. Command. Text = "update accounts set balance = " + "balance + 1000 where account_id = '2222'"; cmd. Execute. Non. Query (); // Commit the transaction (commit changes) trans. Commit ();

Transaction Commands § Isolation. Level. Serializable locks down the records while they’re updated so that they can’t be read or written. § Committing the transaction writes the changes to the database.

Uses Data. Grid to represent a Data. Set in XML Data. Set ds = new Data. Set (); ds. Read. Xml (Server. Map. Path ("Bonuses. xml")); My. Data. Grid. Data. Source = ds;

Summary Database Programming: part of the architecture of a multitier application. DB Programming API ADO. NET Data Providers (SQLServer Provider, OLEDB Provider) Connection Commands (Non. Query, Scaler) Data. Reader Data. Set Data. Adapter Data. Grid Transaction