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/After comparison

• Before Spring
• Proprietary, buggy configuration code
• Proliferation of singletons, service locators
  (glue code)
• Copy-and-paste code, ugly exception handling
• Testing? Whats 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

6
IoC/Dependency Injection Whats 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
• IoCAOP 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)
• Springs declarative transaction management can
  be driven either by annotations or externalized
  XML configuration.

12
MS Transaction Management Options
Local Distributed Declarative Programmatic
ADO.NET (1.1) ? ? ? ?
EnterpiseServices (1.1) ? ? ? ?
System.Transactions (2.0) ? ? ? ?
.NET 3.0 ? ? ? ?

• 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 dont 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
())
public class TestCommandCallback
ICommandCallback public Object
DoInCommand(IDbCommand cmd)
cmd.CommandText "SELECT COUNT() FROM
TestObjects" int count (int)cmd.ExecuteSca
lar() // do something with count...
return count

• Implicit transaction used in this example.
• Passed in IDbCommand connection/transaction
  properties are managed by Spring.

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(CommandTyp
e 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))
ltobject id"testObjectDao"
typeMyApp.DAL.TestObjectDao,MyApp.DAL"gt
ltproperty name"ConnectionString"
value"Data Source(local)DatabaseSpring
UserIDUserIdPasswordspringqa
Trusted_ConnectionFalse"/gt lt/objectgt
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, Str
ing.Format("insert into Credits(creditAmount)
VALUES (0)",
creditAmount))
public class AccountDebitDao AdoDaoSupport,IAcco
untDebitDao public void DebitAccount(float
debitAmount) AdoTemplate.ExecuteNonQuery(Com
mandType.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(credit
Amount) 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
(_at_Age,_at_Name)" public CreateTestObjectNonQ
uery(IDbProvider dbProvider)
base(dbProvider, sql) DeclaredParameters.Ad
d("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
TestObjectQuery objectQuery new
TestObjectQuery(dbProvider) IList objectList
objectQuery.Query() //now have a list of
TestObject instances

• IDataReaderWrapper
• NullMappingDataReader implementation
• Specified in AdoTemplate
• Say goodbye to code like this

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(I
DbProvider 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(I
DbProvider 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 doesnt change.

37
Summary

• Springs approach to Data Access is very
  applicable to .NET development
• ADO.NET framework will give you real productivity
  gains