Introductions…

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@lakeforest.edu

2. Introductions… • Joe Hummel, PhD • Chicago, USA • email: hummel@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 Microsoft’s 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.Update(…);

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

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.SqlConnection(connectInfo); } . . .

12. 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… { . . . } conn.Close(); private IDbConnection CreateConnection(string connectInfo) { return new System.Data.SqlClient.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. GenericDataAccess MSAccessDB SQLServerDB OracleDB 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(…); db3.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. TxtReader HtmlReader XmlReader .txt .html .xml 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. ASTNode Stmt Expr While Assign If Binary Literal 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 • I had students build a recursive-descent parser • I provided lexer • they designed AST & built parser

20. That's it! • Questions? • Discussion? • Thank you!