Spring.NET Data Access

1. Spring.NET Data Access

2. Spring in a nutshell • Applies OO best practices and integrates best-of-breed technologies to provide a “one-stop-shop” for building high-quality enterprise applications quickly. • Best practices are not platform specific • Spring architecture and design is powerful yet simple to use • Proven benefits in the real world • Developer productivity • Spring Data Access Design used by VOCA (British Clearing House) and performs 100,000,000 transactions in a 4 hour window every weekday – no failures.

3. Spring Core Architecture • Manages component lifecycle and dependencies • Centralizes and externalizes application configuration information • Decouples business logic from infrastructure • Decorates components with cross-cutting behavior

4. Spring Solutions • Solutions address major application areas and their common problems • Client Application Development • Web/WinForm • Database Access • ADO.NET, iBATIS.NET, NHibernate (ORM) • Transaction Management • MS-DTC, ADO.NET, NHibrenate . . . • Remote Access • Web Services, Remoting • Each solution builds on the core architecture • Solutions foster integration and does no reinvent the wheel.

5. Before Spring Proprietary, buggy configuration code Proliferation of singletons, service locators (glue code) Copy-and-paste code, ugly exception handling Testing? What’s that? After Spring One elegant way to configure everything Dependency Injection – components are only handed what they need Cohesive reusable business logic Easy comprehensive testing Before/After comparison

6. IoC/Dependency Injection: What’s the point again? • Testing becomes easy • You can test the component in isolation • Maintenance becomes easy • Loose coupling facilitates local changes • Clear separation of concerns improves modularity • Configuration becomes easy • Decoupled from application logic • Easy to vary based on context • Reuse becomes easy • Loosely coupled components can be reused

7. Spring Core/AOP • Core – IoC/Dependency Injection • AOP – Aspect oriented programming • Compliments OO • Empowers modularization of cross-cutting concerns • IoC+AOP = a match made in heaven • AOP Framework build on Spring IoC configuration • Out-of-the-box aspects address common concerns • Transaction Management

8. Spring AOP/Transactions • Example - TransacationProxyFactoryObject • Wraps a plain .NET object in a transactional proxy • Methods to advise are fully configurable • How does it work? • Interceptor intercepts each method invocation • If the method is marked transactional: • Create or reuse a transaction appropriately • Commit or rollback based on configuration policy • Will look at usage examples shortly

9. Spring Data Access: ADO.NET • Simpler programming model than raw ADO.NET for common scenarios • Does not limit your ability to do ‘cast down’ to vendor specific functionality for ‘uncommon’ scenarios. • Resource management • ‘Template’/callback APIs • Transaction management • Programmatic and declarative • Exception Translation • IoC integration for configuration • Designed to create a robust Data Access Layer

10. Spring Data Access Features - Resource Management • Spring removes the need for you to worry about managing resources explicitly in code. • Need to manage Connection, Command, DataReader … • Traditional API usage has limitations • Using ‘using’ • No ‘catch’ to do exception translation/wrapping within called code. • Resource management is not centralized • You still better code it right everywhere you use it.

11. Spring Data Access Features - Transaction Management • Spring removes the need for you to decide up front what transaction manager you are using, i.e. local or distributed for ADO.NET or ORM transaction managers • Spring removes the need to code against a transaction API • Can use declarative transactions to control transaction manager (both local and distributed) • Spring’s declarative transaction management can be driven either by annotations or externalized XML configuration.

12. MS Transaction Management Options • The sweet spot is Declarative transaction management + local transactions • Spring sits in this spot. • * only applies if provider supports “Promotable Single Phase Enlistment” (PSPE)

13. Spring Transaction Managers • AdoPlatformTransactionManager • Local transactions • ServiceDomainPlatformTransactionManager • Distributed transactions (COM+ Services) • TxScopePlatformTransactionManager • Local or Distributed as needed (if PSPE supported) • Others for ORM solutions • Switching is just a small configuration change. • Callback interfaces • ITransactionCallback

14. Spring Data Access Features - Exception Translation • Spring provides a meaningful Data Access Exception hierarchy • MS DataSet actually provides one but not for more general ADO.NET API usage • Not singly rooted (.NET 1.1) • Base exception + error code (.NET 2.0) • Spring knows what ORA-917 is and translates it to a BadSqlGrammerException • Portable across database vendors

15. ADO.NET Framework: AdoTemplate • AdoTemplate is central abstraction to execute ADO.NET operations • Contains generic ‘Execute’ methods that handle resource and transaction management • Callback interfaces and/or delegates provide access to underlying ‘managed’ objects such as DbCommand, DataAdapter • Provides many convenient ‘one-liner’ methods for common scenarios • All delegate to generic Execute methods. • AdoTemplate is stateless and threadsafe – can use a single instance in each DAO.

16. AdoTemplate Execute Methods • Would only use in application code if ‘one-liners’ don’t cover your needs • Can downcast to access vendor specific functionality public Object Execute(ICommandCallback action); public Object Execute(CommandDelegate del); public Object Execute(CommandType commandType, string sql, IDataAdapterCallback dataAdapterCallback);

17. Simple usage generic callback public int GetTestObjectCount() { AdoTemplate adoTemplate = new AdoTemplate(dbProvider); return adoTemplate.Execute(new TestCommandCallback()); } • Implicit transaction used in this example. • Passed in IDbCommand connection/transaction properties are managed by Spring. public class TestCommandCallback : ICommandCallback { public Object DoInCommand(IDbCommand cmd) { cmd.CommandText = "SELECT COUNT(*) FROM TestObjects"; int count = (int)cmd.ExecuteScalar(); // do something with count... return count; } }

18. DbProvider • DbProvider is like the .NET 2.0 provider abstraction but with additional metadata to more easily support portability across providers and for use in .NET 1.1 environments.

19. Parameter Convenience Classes • Help build up common parameter collections • IDbParameters • Reduce verbosity • AddOut, AddInOut, DataSet related methods • Also an alternative Parameter Builder Class • Params.Add().Type(DbType.Int32).Name(“Age”) . . .

20. AdoTemplate ‘one-liners’ • Various overloaded methods for • ExecuteScalar • ExecuteNonQuery • Provide lightweight object mapping • Query methods that use callbacks • IRowCallback, IRowMapper, IResultSetExtractor • DataSet Support – overloaded methods for • DataSetFill • DataSetUpdate

21. Partial Method listing • ExecuteNonQuery int ExecuteNonQuery(CommandType commandType, string sql); int ExecuteNonQuery(CommandType commandType, string sql, string name, Enum dbType, int size, object parameterValue); // output parameters can be retrieved... int ExecuteNonQuery(CommandType commandType, string sql, IDbParameters parameters);

22. Partial method listing • DataSet int DataSetFill(DataSet dataSet, CommandType commandType, string sql); int DataSetFill(DataSet dataSet, CommandType commandType, string sql, IDbParameters parameters); int DataSetFill(DataSet dataSet, CommandType commandType, string sql, string[] tableNames); int DataSetFill(DataSet dataSet, CommandType commandType, string sql, DataTableMappingCollection tableMapping); int DataSetUpdate(DataSet dataSet, string tableName, IDbCommand insertCommand, IDbCommand updateCommand, IDbCommand deleteCommand);

23. Simple Example public class TestObjectDao : AdoDaoSupport, ITestObjectDao { public void Create(string name, int age) { AdoTemplate.ExecuteNonQuery( String.Format(CommandType.Text, "insert into TestObjects(Age, Name) " + "VALUES ({0}, '{1}')", age, name)); } } <object id="testObjectDao" type=“MyApp.DAL.TestObjectDao,MyApp.DAL"> <property name="ConnectionString" value="Data Source=(local);Database=Spring; UserID=${UserId};Password=springqa; Trusted_Connection=False"/> </object>

24. DAO Support/Configuration • AdoDaoSupport class in previous example provides basic boiler-plate properties such as connection string/DbProvider and AdoTemplate properties + some simple convenience mehods • Note use of ${UserID} in connection string • Values are replaced with those from a name-value section in App.config • standard Spring.Net configuration functionality

25. More Complex Example public interface IAccountManager { void DoTransfer(float creditAmount, float debitAmount); } public interface IAccountDebitDao { void DebitAccount(float debitAmount); } public interface IAccountCreditDao { void CreateCredit(float creditAmount); } public interface IAccountManager { void DoTransfer(float creditAmount, float debitAmount); }

26. More Complex Example II public class AccountCreditDao : AdoDaoSupport,IAccountCreditDao { public void CreateCredit(float creditAmount) { AdoTemplate.ExecuteNonQuery(CommandType.Text, String.Format("insert into Credits(creditAmount) VALUES ({0})", creditAmount)); } } public class AccountDebitDao : AdoDaoSupport,IAccountDebitDao { public void DebitAccount(float debitAmount) { AdoTemplate.ExecuteNonQuery(CommandType.Text, String.Format("insert into Debits (DebitAmount) VALUES ({0})", debitAmount)); } }

27. More Complex Example III public class AccountManager : IAccountManager{ private IAccountCreditDao creditDao; private IAccountDebitDao debitDao; // Properties for field members... [Transaction()] public void DoTransfer(float creditAmount, float debitAmount) { creditDao.CreateCredit(creditAmount); debitDao.DebitAccount(debitAmount); } }

28. Transaction Management • In configuration file • Each DAO can point to a different database • Switch from local to distributed transaction manager • No code changes! • If use XML based demarcation can more easily change transaction boundaries • Maybe at first demarcate on DAO objects since there is no business logic – simple ‘CRUD’ app. • Over time need service layer – move demarcation to service layer that groups multiple DAOs.

29. AdoOperations • OO model for DB operations • Preferred approach • Also thread safe and reusable • AdoQuery - Result set mapping to objects • AdoNonQuery- Insert/Update/Delete • AdoScalar – Return single value • StoredProcedure • out parameters and multiple result sets • Can derive parameters from metadata. • DataSetOperations – CRUD for DataSets (ala VS.NET generated adapters) • Use of ICommandCreater implementation for efficient parameter re-creation.

30. AdoNonQuery public class CreateTestObjectNonQuery : AdoNonQuery { private static string sql = "insert into TestObjects(Age,Name) values (@Age,@Name)"; public CreateTestObjectNonQuery(IDbProvider dbProvider) : base(dbProvider, sql) { DeclaredParameters.Add("Age", DbType.Int32); DeclaredParameters.Add("Name", SqlDbType.NVarChar, 16); Compile(); } public void Create(string name, int age) { ExecuteNonQuery(name, age); } } Variable length arguments

31. Map result set to objects public class TestObjectQuery : MappingAdoQuery { private static string sql = "select TestObjectNo, Age, Name from TestObjects"; public TestObjectQuery(IDbProvider dbProvider) : base(dbProvider, sql) { CommandType = CommandType.Text; } protected override object MapRow(IDataReader reader, int num) { TestObject to = new TestObject(); to.ObjectNumber = reader.GetInt32(0); to.Age = reader.GetInt32(1); to.Name = reader.GetString(2); return to; } } NullMappingDataReader

32. NullMappingDataReader • IDataReaderWrapper • NullMappingDataReader implementation • Specified in AdoTemplate • Say goodbye to code like this… TestObjectQuery objectQuery = new TestObjectQuery(dbProvider); IList objectList = objectQuery.Query(); //now have a list of TestObject instances to.ObjectNumber = (!reader.IsDBNull(0)) ? reader.GetInt32(0) : -1; to.Age = (!reader.IsDBNull(1)) ? reader.GetInt32(1) : -1; to.Name = (!reader.IsDBNull(2)) ? reader.GetString(2) : String.Empty;

33. Stored Procedure public class CallCreateTestObject : StoredProcedure { public CallCreateTestObject(IDbProvider dbProvider) : base(dbProvider, "CreateTestObject") { DeriveParameters(); Compile(); } public void Create(string name, int age) { ExecuteNonQuery(name, age); } }

34. Stored Procedure • Easy Access to out parameters public class CallCreateTestObject : StoredProcedure { public CallCreateTestObject(IDbProvider dbProvider) : base(dbProvider, "CreateTestObject") { DeriveParameters(); Compile(); } public void Create(string name, int age) { IDictionary inParams = new Hashtable(); inParams["name"] = name; inParams["age"] = age; IDictionary outParams = ExecuteNonQuery(inParams); } }

35. Stored Procedure Class Value Proposition • Use of DeriveParameters and StoredProcedure base class leads to very terse code.

36. Monitoring • Can apply logging/performance timer aspects to AdoTemplate and OO database objects, AdoNonQuery. • Non invasive approach • Isolates the cross cutting concern of logging and performance monitoring • Very accommodating to change • Today log, tomorrow use custom perf counter – core code doesn’t change.

37. Summary • Spring’s approach to Data Access is very applicable to .NET development • ADO.NET framework will give you real productivity gains