Chapter 46. ADO.NET Data Access QuickStart

46.1. Introduction

The data access quick start demonstrates the API usage of AdoTemplate (both generic and non-generic versions) as well as the use of the object based data access classes contained in Spring.Data.Objects. It uses the Northwind database and is located under the directory examples/DataAccessQuickStart.

The quick start contains pseudo DAO objects and a collection of NUnit tests to exercise them rather than a full blown application. To run the tests from within VS.NET install TestDriven.NET, ReSharper, or an equivalent . The listing of DAO classes and the parts of Spring.Data that they demonstrate is shown below.

  • CommandCallbackDao - Use of the ICommandCallback and CommandCallbackDelegate

  • ResultSetExtractorDao - Use of IResultSetExtractor and ResultSetExtractorDelegate

  • RowCallbackDao - Use of IRowCallback and RowCallbackDelegate

  • RowMapperDao - Use of IRowMapper and RowMapperDelegate

  • QueryForObject - Use of QueryForObject method.

  • StoredProcDao - Use of Spring.Data.Objects.StoredProcedure

The are simple domain objects in the Spring.DataQuickStart.Domain namespace, collections of which are generally returned from the DAO methods.

[Note]Note

To follow this Data Access QuickStart load the solution file found in the directory <spring-install-dir>\examples\Spring\Spring.DataQuickStart

46.1.1. Database configuration

To get started running the 'unit test' you should configure the database connection string. The listing in DataQuickStart.GenericTemplate.ExampleTests.xml is shown below

<objects xmlns="http://www.springframework.net"
         xmlns:db="http://www.springframework.net/database">

  <db:provider id="dbProvider" 
                provider="SqlServer-1.1" 
                connectionString="Data Source=(local);Database=Northwind;User ID=springqa;Password=springqa;Trusted_Connection=False"/>


  <! -- other definitions not shown 


</objects>

You should change the value of the provider element to correspond to you database and the connection string as appropriate. Please refer to the documentation on the DbProvider abstraction for details particular to your database configuration. You should also install the Northwind database, which is available for SqlServer 2005 from this download location. The minimal schema to support other database providers may be supported in the future.

46.1.1.1. AdoTemplate Configuration

The various DAO objects refer to an instance of AdoTemplate which is responsible for performing data access operations. This is declared in ExampleTest.xml as shown below

  <object id="adoTemplate" type="Spring.Data.Generic.AdoTemplate, Spring.Data">
    <property name="DbProvider" ref="dbProvider"/>
    <property name="DataReaderWrapperType" value="Spring.Data.Support.NullMappingDataReader, Spring.Data"/>
  </object>

The property DbProvider refers to the database configuration you previously defined. Also the property DataReaderWrapper is set to the NullMappingDataReader that ships with Spring. This provides convenient default values for null values returned from the database. To read more about AdoTemplate, refer to the chapter, Data access using ADO.NET.

46.1.2. CommandCallback

The code that exercises the use of a CommandCallback is shown below

        [Test]
        public void CallbackDaoTest()
        {
            CommandCallbackDao commandCallbackDao = ctx["commandCallbackDao"] as CommandCallbackDao;
            int count = commandCallbackDao.FindCountWithPostalCode("1010");
            Assert.AreEqual(3, count);
        }

The configuration of the CommandCallbackDao is shown below

  <object id="commandCallbackDao" type="Spring.DataQuickStart.Dao.GenericTemplate.CommandCallbackDao, Spring.DataQuickStart">
    <property name="AdoTemplate" ref="adoTemplate"/>
  </object>

This the minimal configuration required for a DAO object, typically DAO objects in your application will include other configuraiton information, for example properties to specify the maximum size of the result set returned etc. The implementation of the FindCountWithPostalCode is shown below

        public virtual int FindCountWithPostalCodeWithDelegate(string postalCode)
        {
            // Using anonymous delegates allows you to easily reference the
            // surrounding parameters for use with the DbCommand processing.

            return AdoTemplate.Execute<int>(delegate(DbCommand command)
                   {
                       // Do whatever you like with the DbCommand... downcast to get 
                       // provider specific funtionality if necesary.
                                                    
                       command.CommandText = cmdText;
                         
                       DbParameter p = command.CreateParameter();
                       p.ParameterName = "@PostalCode";
                       p.Value = postalCode;
                       command.Parameters.Add(p);

                       return (int)command.ExecuteScalar();

                   });

        }

Anonymous delegates are used to specify the implementation of the callback function that passes in a DbCommand object. You can then use the DbCommand object as you see fit to access the database. If you are using Spring's delcarative transaction management features then this DbCommand would have its transaction and connection properties based on the context of the surrounding transaction. All resource management for the DbCommand are handled for you by the framework, as well as error reporting on error etc. If you execute the test, it will pass, assuming you haven't modified any data in the Northwind database from its raw installation.