Generic Data Access in Microsoft .NET: a Compelling Example of Inheritance, Interfaces, and the Factory Method Design Pattern

OOPSLA 2004 : Design Patterns and Objects First Workshop Joe Hummel, PhD Dept of Math/CS Lake Forest College [email protected]

Introductions…

•

Joe Hummel, PhD

– Chicago, USA – email: [email protected]

– web: www.lakeforest.edu/~hummel •

I wear 2 hats:

– Academic: • Associate Prof. of CS at

Lake Forest College ( www.lakeforest.edu

)

• PhD from UC-Irvine (Optimizing Compilers, 1998) – Industry: • professional trainer (1-5 day workshops, webcasts, conferences) • specializing in Microsoft Windows development

OOPSLA 2004 2

Part 1

•

The problem… OOPSLA 2004 3

Database access in .NET is vendor-specific

•

A different set of classes for each technology:

– ODBC – OLEDB – SQL Server – Oracle – DB2 – … DB •

Saving grace:

– Each set of classes is based on a common design: • •

Connection

object to open connection with DB

Command

object for executing SQL •

DataReader

• etc.

object for reading records

OOPSLA 2004 4

Naïve result

•

Developers create a distinct class for each DB type: SQLServerDB MSAccessDB OracleDB MySQLDB OOPSLA 2004 5

Goal

•

Apply good OOD to obtain reusable, maintainable result: GenericDataAccess

MSAccessDB SQLServerDB OracleDB MySQLDB

OOPSLA 2004 6

Killer example?

• • • • •

Real Important Inheritance Interfaces Factory Method design pattern OOPSLA 2004 7

Part 2

•

From naïve to gOOd solution… OOPSLA 2004 8

Outline

•

4-step process:

1. identify generic vs. vendor-specific data access code 2. apply Factory Method design pattern 3.

use interfaces to define common design across Microsoft’s classes 4. use inheritance to: – ensure our generic & concrete data access classes integrate properly – enable single, polymorphic access to different types of databases

GenericDataAccess db; db = new

ConcreteDataAccessClass

(…); db.Insert(…); db.Update(…); OOPSLA 2004 9

Step 1

SQL Server •

Identify generic vs. vendor specific… System.Data.SqlClient.SqlConnection conn; System.Data.SqlClient.SqlCommand cmd; System.Data.SqlClient.SqlDataReader reader; conn = new System.Data.SqlClient.SqlConnection(“connection info…”); cmd = new System.Data.SqlClient.SqlCommand(“Select * …”, conn); conn.Open();

// open connection to DB

reader = cmd.ExecuteReader();

// execute SQL and return object for accessing DB…

while ( reader.Read() )

// for each record…

{ .

.

.

} conn.Close(); OOPSLA 2004 10

Step 2

•

Apply Factory Method design pattern…

??? conn; ??? cmd; ??? reader;

conn = CreateConnection(“connection info…”); cmd = CreateCommand(“Select * …”, conn);

conn.Open();

// open connection to DB

reader = cmd.ExecuteReader();

// execute SQL and return object for accessing DB…

while ( reader.Read() )

// for each record…

{ .

.

.

private ??? CreateConnection (string connectInfo)

}

{ return new System.Data.SqlClient.SqlConnection(connectInfo);

conn.Close();

.

.

.

OOPSLA 2004 11

Step 3

•

Use interfaces to define common design across DB classes… System.Data.IDbConnection conn; System.Data.IDbCommand cmd; System.Data.IDataReader reader; conn = CreateConnection(“connection info…”); cmd = CreateCommand(“Select * …”, conn);

conn.Open();

// open connection to DB

reader = cmd.ExecuteReader();

// execute SQL and return object for accessing DB…

while ( reader.Read() )

// for each record…

{ .

.

.

private {

}

}

conn.Close();

.

IDbConnection CreateConnection (string connectInfo) return new System.Data.SqlClient.SqlConnection(connectInfo); OOPSLA 2004 12

Step 4

•

Use inheritance & abstract base class to enforce design…

public abstract class

GenericDataAccess

{ public void

Insert

(…) { … } public void

Update

(…) { … } } protected abstract IDbConnection

CreateConnection

(string info); protected abstract IDbCommand

CreateCommand

(string SQL, IDbConnection conn);

public class SQLServerDB : GenericDataAccess { protected IDbConnection CreateConnection (string info) { return new System.Data.SqlClient.SqlConnection(info); } .

.

.

OOPSLA 2004 13

End result?

MSAccessDB

GenericDataAccess

SQLServerDB OracleDB •

Reusable, maintainable data access hierarchy!

•

Single, polymorphic database access!

GenericDataAccess db1, db2, db3; db1 = new

SQLServerDB

(…); db2 = new

MSAccessDB

(…); db3 = new

OracleDB

(…); db1.Insert(…); db2.Update(…); db3.Delete(…); OOPSLA 2004 14

Part 3

•

Extensions… OOPSLA 2004 15

Possible extensions

•

Specialized exception handling:

– different databases respond differently to error conditions – define generic exception handler in base class – derived classes can override & specialize if desired –

Template Method

design pattern •

Data access code in .NET really consists of 2 levels:

– low-level code that performs database access: • execute

Action

query (insert, update, delete) • server-side

Select

query • client-side

Select

query – high-level code for domain-specific stuff: validation, build SQL, etc.

– redesign data access hierarchy to take this into account…

OOPSLA 2004 16

Other possible extensions

•

(These are from the workshop discussion…)

• •

Abstract Factory pattern:

– derived classes are really producing objects from a family of classes – should recognize this via Abstract Factory pattern

Configuration pattern:

– make system more dynamic by reading class names from a file – apply Configuration pattern

OOPSLA 2004 17

Application in CS1 / CS2?

•

Perhaps a similar problem involving files instead of DBs…

– we give students a set of classes for reading / writing text files – they apply 4 steps outlined earlier

TxtReader HtmlReader XmlReader .txt

.html

.xml

OOPSLA 2004 18

Other ideas for CS1 / CS2

•

I’ve used the following successfully in CS1 / CS2 sequence:

– compilers use AST as an internal representation – AST (abstract syntax tree) involves: • non-trivial inheritance hierarchy • Visitor design pattern

ASTNode Stmt Expr While Assign If Binary Literal

– I had students build a recursive-descent parser • I provided lexer • they designed AST & built parser

OOPSLA 2004 19

That's it!

• •

Questions?

Discussion?

•

Thank you!

OOPSLA 2004 20