Microsoft 'NET Programming: Building Applications with C

1
Generic Data Access in Microsoft .NET
aCompelling Example of Inheritance,
Interfaces,and the Factory Method Design
PatternOOPSLA 2004 Design Patterns and
Objects First WorkshopJoe Hummel, PhDDept of
Math/CSLake Forest Collegehummel_at_lakeforest.edu
2
Introductions

• Joe Hummel, PhD
• Chicago, USA
• email hummel_at_lakeforest.edu
• 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

3
Part 1

• The problem

4
Database access in .NET is vendor-specific

• A different set of classes for each technology
• ODBC
• OLEDB
• SQL Server
• Oracle
• DB2
• 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 object for reading records
• etc.

DB
5
Naïve result

• Developers create a distinct class for each DB
  type

SQLServerDB
MSAccessDB
OracleDB
MySQLDB
6
Goal

• Apply good OOD to obtain reusable, maintainable
  result

GenericDataAccess
MSAccessDB
SQLServerDB
OracleDB
MySQLDB
7
Killer example?

• Real
• Important
• Inheritance
• Interfaces
• Factory Method design pattern

8
Part 2

• From naïve to gOOd solution

9
Outline

• 4-step process
• identify generic vs. vendor-specific data access
  code
• apply Factory Method design pattern
• use interfaces to define common design across
  Microsofts classes
• 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.Upda
te()
10
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.SqlConne
ction(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()
11
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 . .
. conn.Close()
private ??? CreateConnection(string connectInfo)
return new System.Data.SqlClient.SqlConnectio
n(connectInfo) . . .
12
Step 3

• Use interfaces to define common design across DB
  classes

System.Data.IDbConnection conn System.Data.IDbCo
mmand 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 . . . conn.Close()
private IDbConnection CreateConnection(string
connectInfo) return new System.Data.SqlClien
t.SqlConnection(connectInfo) . . .
13
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) . . .
14
End result?

• 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() db
3.Delete()
15
Part 3

• Extensions

16
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

17
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

18
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

19
Other ideas for CS1 / CS2

• Ive 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
• I had students build a recursive-descent parser
• I provided lexer
• they designed AST built parser

20
That's it!

• Questions?
• Discussion?
• Thank you!