Db-2

ADO.NET (part 2)

Objectives “What if I want to use client-side CPU cycles for DB processing? Stored Procedures? Transactions? Design issues? There's lots more to discuss…”

• • •

Proper construction of SQL queries Additional ADO.NET data access techniques Common design issues

Microsoft

2

Part 1 •

Proper query string construction…

Microsoft

3

Example •

Inserting a new customer – recall that text-based data values must be surrounded by '

string sql;

int result;

sql = "Insert Into Customers(CID, FirstName, LastName, CreditLimit) Values(12345,'Joe','Hummel',1000000.0);" ; OleDbCommand dbCmd; dbCmd = new OleDbCommand(); dbCmd.CommandText = sql; dbCmd.Connection = dbConn;

result = dbCmd.ExecuteNonQuery(); if (result != 1) throw new Exception("Unable to insert customer?!");

Microsoft

4

Dynamic SQL •

Most of the time you'll need to build SQL dynamically – e.g. based on input values from the user

•

Example: – insert new customer based on data in Form's text boxes… string decimal

fn, ln; credit;

fn = this.txtFirstName.Text; ln = this.txtLastName.Text; credit = decimal.Parse(this.txtCreditLimit.Text); . . . Microsoft

5

Dynamic query string building •

Dynamic string building is best done using String.Format:

string

sql, sFormat;

sFormat = "Insert Into Customers(CID, FirstName, LastName, CreditLimit) Values({0}, '{1}', '{2}', {3});" ; sql = String.Format(sFormat, DataAccess.MaxCID()+1, fn, ln, credit); . . .

Microsoft

6

Beware! • •

Beware of the O'Shea problem… What if text-based data contains a ' of its own?

string

sql;

sql = "Insert Into Customers(CID, FirstName, LastName, CreditLimit) Values(45678,'Jimmy','O'Shea',1000.0);" ;

Microsoft

7

Solution: remember to escape ' •

As you build SQL, ' in text-based data must be escaped – replace a single ' with two ' in a row – do this on ALL queries — action & select

string

sql, sFormat;

sFormat = "Insert Into Customers(CID, FirstName, LastName, CreditLimit) Values({0}, '{1}', '{2}', {3});" ;

sql = String.Format(sFormat, DataAccess.MaxCID()+1, fn.Replace("'", "''"), ln.Replace("'", "''"), credit);

Microsoft

8

Part 2 •

Additional data access techniques…

Microsoft

9

Other data access techniques • So far we've seen just two techniques: 1. DataReader: read-only access to multiple rows & values 2. Action queries: for updating DB

• Additional data access techniques: 3. scalar access: optimized for read-only access to single value 4. DataSets: for local, client-side DB access

Microsoft

10

(3) Scalar access •

When you need to retrieve a single value…

• •

Execute a select query via ExecuteScalar method Method returns an object with single value inside

Microsoft

11

Example •

What is the maximum existing customer id? – notice that select query must compute a single value public static long MaxCID() { ... // open DB if necessary… string sql; long cid; sql = "Select Max(CID) From Customers;" ; OleDbCommand dbCmd; dbCmd = new OleDbCommand(); dbCmd.CommandText = sql; dbCmd.Connection = dbConn;

666

cid = System.Convert.ToInt64( dbCmd.ExecuteScalar() ); ... // close DB if necessary… return cid; }

Microsoft

12

(4) DataSets •

DataSets are a local, in-memory data structure – easily filled with data from a database – easily passed around by methods – easily displayed in a GUI app

DataAdapter Product

Price

Ants

$ 0.49

5000

Birds

$ 4.49

500

Cats

$29.95

100

Dogs

$79.95

20

Connection

DB

Quantity

DataSet

Microsoft

Command

•

DataSet mirrors the database – data forms a temporary table within DataSet

13

Why use a DataSet? • •

DataSet is filled with a local copy of requested data This allows you to: – reduce load on DB server since data is now available locally – close connection to DB & work off-line

•

These are important advantages in a networked environment…

Microsoft

14

Filling a DataSet • DataAdapter object is used to fill a DataSet… • Example: – fill DataSet with all product data sql = "Select * From Products;" ; . . . DataSet ds; OleDbDataAdapter adapter; ds = new DataSet(); adapter = new OleDbDataAdapter(dbCmd);

Name

Price

Quantity

Ants

$ 0.49

5000

Birds

$ 4.49

500

Cats

$29.95

100

Dogs

$79.95

20

"Table"

dbConn.Open(); adapter.Fill(ds); dbConn.Close();

Microsoft

15

Accessing data within Dataset • •

Access data by iterating row-by-row through table Example: – display product names in list box

. . . string

name;

foreach (DataRow row in ds.Tables["Table"].Rows) { name = row["Name"].ToString(); this.lstProducts.Items.Add(name); } Microsoft

16

DataGrid display •

DataSet can be bound to DataGrid control for easy display – one line of code!

. . . this.dataGrid1.SetDataBinding(ds, "Table");

Microsoft

17

DataGrid is a powerful control •

By default, DataGrid is read/write – user can modify data – user can add rows – user can delete rows

•

However, all changes are local to DataSet – to flush changes back to DB, reconnect and update…

Microsoft

18

Flushing changes back to database •

Reconnect, and apply adapter's Update() method – use CommandBuilder object to general update SQL for you sql = "Select * From Products;" . . . DataSet OleDbDataAdapter

ds; adapter;

ds = (DataSet) this.dataGrid1.DataSource; adapter = new OleDbDataAdapter(dbCmd); OleDbCommandBuilder cmdBuilder; cmdBuilder = new OleDbCommandBuilder(adapter); dbConn.Open(); adapter.Update(ds); dbConn.Close();

Microsoft

// this fails if updates conflict with values in DB!

19

DataSets are really in-memory databases •

A DataSet: – holds as much data as you want – searchable – sortable – updateable

•

Example: – use a batch query to fill DataSet with entire Sales DB! sql = "Select * From Products;" + "Select * From Customers;" + "Select * From Orders;" ; . . . adapter.Fill(ds);

Microsoft

20

Part 3 •

Common design issues…

Microsoft

21

Common design issues 1. Where should data access code live? 2. Do you open & close connection on each access? 3. Stored procedures 4. Dealing with NULL data 5. Transactions

Microsoft

22

(1) Where should data access code live? •

Typical design is n-tier – good separation of concerns – enables reuse of back-end tiers across varying FEs

object

object

Front-end object

Presentation

Microsoft

Busines s

Data Access

Data

23

Implication •

Data access code should live in a separate class – below is a procedural design based on static methods – alternative is OO design based on instance methods using System.Data; using System.Data.OleDb; public class DataAccess { private static readonly string sConnection = "…" ; public static long MaxCID() { … } . . . }

Microsoft

24

(2) Open & close connection on each access? •

Connections are expensive resources – costly to open (time & space, especially if DB server) – limited in number (connection ≈ license)

•

Design choices: – open during startup & leave open for duration of app – open & close on each access

Microsoft

25

Which design choice? •

Correct design is not always clear…

•

Rough rule of thumb: – single-user app (1 user accessing DB):

leave open

– multi-user app (N users accessing DB):

open & close

• i.e. each DB access should create its own set of DB objects, open conn, etc. • this way, each call into Data Access tier is independent, which you want in a multi-user situation

Microsoft

26

(3) Stored procedures •

Most databases support stored procedures – i.e. SQL-based subroutines / functions within database itself – typically 10x faster (if not more)

sql = "AddCustomer 'Joe', 'Hummel', 1000.0" ; . . .

rows = dbCmd.ExecuteNonQuery(); CREATE PROCEDURE AddCustomer( @FirstName varchar(30), @Lastname varchar(30), @CreditLimit money) AS INSERT INTO Customers(FirstName, LastName, CreditLimit) VALUES(@FirstName, @LastName, @CreditLimit) RETURN(1)

Microsoft

27

(4) Dealing with NULL data • •

•

Some values in a database may be NULL – i.e. the field contains NO value If you try to access a NULL value, an exception is thrown – because hey, the data doesn't exist!

You only need to worry about this if field can be NULL – which depends on database design…

Microsoft

28

Dealing with NULL scalar data • •

Recall that ExecuteScalar returns an object with data inside Object is of type "System.DBNull" if it contains NULL…

object obj; . . . obj = dbCmd.ExecuteScalar();

if (obj.GetType().ToString() == "System.DBNull") << data is NULL >> else << data is safe to access >>

Microsoft

29

Dealing with NULL data via DataReader • •

Recall that ExecuteReader returns a DataReader object You ask DataReader if data is NULL before accessing it… OleDbDataReader dbReader; . . . dbReader = dbCmd.ExecuteReader(); while (dbReader.Read()) { if ( dbReader.IsDBNull(dbReader.GetOrdinal("FirstName")) ) << first name is NULL >> else << first name is safe to access >> . . . }

Microsoft

30

Dealing with NULL data in a DataSet • •

Recall that DataSet holds a local copy of data You ask DataRow object if data is NULL before accessing it…

. . . adapter.Fill(ds); foreach (DataRow row in ds.Tables["Table"].Rows) { if ( row.IsNull("Name") ) << product name is NULL >> else << product name is safe to access >> . . . } Microsoft

31

(5) Transactions • DB operations often need to execute as a single "transaction" • Transaction (Tx) is either: – commited in its entirety, or – rolled back as if nothing happened • Without transactions, DB may appear inconsistent – if one of the operations was to fail along the way…

• Examples: – transferring money:

(1) withdrawl, then (2) deposit

– ordering a product: (1) insert order record (2) update customer's balance (3) update product inventory

Microsoft

32

When do you need transactions? • •

Performing multiple, related updates to database Multi-user database access

Microsoft

33

Working with transactions • •

Transactions are objects like most things in ADO.NET Usage: – you create Tx object from connection – you associate Tx object with each command involved in Tx – now execute commands as before... • all changes to DB are pending – when you're done, either Commit or Rollback • changes are then written or discarded

Microsoft

34

Tx example • Use of Try-Catch is critical here… System.Data.SqlClient.SqlTransaction dbTx = dbConn.BeginTransaction(); try { dbCmd = new SqlClient.SqlCommand(); dbCmd.Connection = dbConn; dbCmd.Transaction = dbTx ;

dbTx; '** create Tx object from connection

'** associate with command object

dbCmd.CommandText = "Insert ..." ; dbCmd.ExecuteNonQuery(); dbCmd.CommandText = "Update ..." ; dbCmd.ExecuteNonQuery(); . . . dbTx.Commit(); '** if we get this far, success! So commit all operations… } catch(Exception ex) { dbTx.Rollback(); . . . } Microsoft

'** if we get here something failed, so rollback DB…

35

Summary •

Just the tip of the iceberg… – really just scratched the surface of databases & ADO.NET

•

Lots of other, important items: – advanced SQL (computed fields, batch processing, …) – database design (normalization, views, …) – stored procedures – triggers – multi-user application design – transactional support via CLR

Microsoft

36

Lab? •

Work on lab #7, "Sales Application"…

Microsoft

37