Back to Summer Batch home page

Reading from and writing to RDBMS

Reading from a database

Support for reading from a database is brought by

Summer.Batch.Infrastructure.Item.Database.DataReaderItemReader<T> class.

This is an all purpose database reader, usable with any rdbms for which a System.Data.Common.DbProviderFactory can be provided.

DataReaderItemReader requires the following properties to be set at initialization time :

In addition, if query contains any parameter, one need to supply a Parameter Source (class that implements

Summer.Batch.Data.Parameter.IQueryParameterSource interface).

Supported syntax for query parameters : the query parameters are identified either with ':' or '@' prefix;


Two implementations of IQueryParameterSource are provided in Summer Batch :
The query used in example is
select CODE,NAME,DESCRIPTION,DATE from BA_SQL_READER_TABLE_1
Records are read from BA_SQL_READER_TABLE_1 table, whose DDL is (target database is MS SQL Server) :
CREATE TABLE [dbo].[BA_SQL_READER_TABLE_1]  (
  [IDENTIFIER]	BIGINT	IDENTITY(1,1) NOT NULL,
  [CODE] INT ,
  [NAME] VARCHAR(30) ,
  [DESCRIPTION] VARCHAR(40) ,
  [DATE] DATE
  )
;
Result from this query execution will be stored in the following DatasourceReaderBO business object:

Example 6.10. DataReaderItemReader target business object

using System;

namespace Com.Netfective.Bluage.Business.Batch.Bos
{
    /// <summary>
    /// Entity DatasourceReaderBO.
    /// </summary>
    [Serializable]
    public class DatasourceReaderBO
    {
        /// <summary>
        /// Property Code.
        /// </summary>
        public int? Code { get; set; }

        /// <summary>
        /// Property Name.
        /// </summary>
        public string Name { get; set; }

        /// <summary>
        /// Property Description.
        /// </summary>
        public string Description { get; set; }

        /// <summary>
        /// Property Date.
        /// </summary>
        public DateTime? Date { get; set; }

    }
}

Mapping between resultset rows and given business object is done by the following RowMapper:

Example 6.11. DataReaderItemReader target business object RowMapper

using Summer.Batch.Data;
using System;

namespace Com.Netfective.Bluage.Business.Batch.Bos.Mappers
{
    /// <summary>
    /// Utility class defining a row mapper for SQL readers.
    /// </summary>
    public static class DatasourceReaderSQLReaderMapper
    {
        /// <summary>
        /// Row mapper for <see cref="DatasourceReaderBO" />.
        /// </summary>
        public static readonly RowMapper<DatasourceReaderBO> RowMapper = 
        (dataRecord, rowNumber) =>
        {
            var wrapper = new DataRecordWrapper(dataRecord);
            return new DatasourceReaderBO
            {
                Code = wrapper.Get<int?>(0),
                Name = wrapper.Get<string>(1),
                Description = wrapper.Get<string>(2),
                Date = wrapper.Get<DateTime?>(3),
            };
        };
    }
}

Now let's review how to configure a database reader. First, the job XML file:

Unity configuration:

[Note]Note
  • In this example, the connection string is read from the default application configuration file, using System.Configuration.ConfigurationManager. Here is the corresponding XML configuration (points at a MS SQL Server database):
    <?xml version="1.0" encoding="utf-8" ?>
    <connectionStrings>
      <add name="ReaderConnection"
       providerName="System.Data.SqlClient"
       connectionString="Data Source=(LocalDB)\v11.0;
       	 AttachDbFilename=|DataDirectory|\data\BA_SQL_Reader.mdf;Integrated Security=True" />
    </connectionStrings>
    				
  • The query is read into a resource file (SqlQueries.resx); SqlQueries class is the corresponding designer artifact created by Visual Studio to wrap the underlying resource.

Summer.Batch.Infrastructure.Item.Database.DatabaseBatchItemWriter<T> is able to write a collection of business objects to the target database, using a INSERT or UPDATE SQL statement.

The following properties must be set at initialization time: Summer Batch provides an implementation for IQueryParameterSourceProvider interface :

Summer.Batch.Data.Parameter.PropertyParameterSourceProvider<in T>

Given an business object of type T, this class will provide a Summer.Batch.Data.Parameter.PropertyParameterSource that maps query parameters to the business object properties, on a naming convention basis.

An extra property can be used to refine the writer behavior :

Our writer will be using the following query
INSERT INTO BA_SQL_WRITER_TABLE (CODE,NAME,DESCRIPTION,DATE) 
			VALUES (:code,:name,:description,:date)
, that will write records into the BA_SQL_WRITER_TABLE whose DDL -- for MS SQL Server -- is
CREATE TABLE [dbo].[BA_SQL_WRITER_TABLE]  (
  [IDENTIFIER]	BIGINT	IDENTITY(1,1) NOT NULL,
  [CODE] INT ,
  [NAME] VARCHAR(30) ,
  [DESCRIPTION] VARCHAR(40) ,
  [DATE] DATE
  )
;
Each row written will correspond to an instance of the following business object

Example 6.14. DatabaseBatchItemWriter target business object

using System;

namespace Com.Netfective.Bluage.Business.Batch.Datasource.Bos
{
    /// <summary>
    /// Entity DatasourceWriterBO.
    /// </summary>
    [Serializable]
    public class DatasourceWriterBO
    {
        /// <summary>
        /// Property Code.
        /// </summary>
        public int? Code { get; set; }

        /// <summary>
        /// Property Name.
        /// </summary>
        public string Name { get; set; }

        /// <summary>
        /// Property Description.
        /// </summary>
        public string Description { get; set; }

        /// <summary>
        /// Property Date.
        /// </summary>
        public DateTime? Date { get; set; }

    }
}

Eventually, mapping between business object and query parameters is achieved by using a
PropertyParameterSourceProvider.

Now, we need to configure the writer; Firstly in job XML file :


Unity configuration:


[Note]Note
As in the database reader example:
  • Connection details are read from an application XML config file;
  • Query is read from a resource file (.resx);

Summer Batch currently supports following three RDBMS:

RDBMSProvider Names
Microsoft® SQL ServerSystem.Data.SqlClient
Oracle® DatabaseSystem.Data.OracleClient, Oracle.ManagedDataAccess.Client, Oracle.DataAccess.Client
IBM® DB2IBM.Data.DB2

Summer.Batch.Data.IDatabaseExtension interface allows extending Summer Batch to support more RDBMS or provider names. Implementations of this interface present in a referenced assembly will automatically be detected and registered. It has three properties that require a getter:

The following example show how to add support for PostgreSQL using Npgsql provider:


In addition to providing a complete support for an additional RDBMS, job repository DDL's should be adapted to the targeted RDBMS.

Some required DDL adjustments are:

  • adapting types names to RDBMS types (BIGINT vs NUMBER, etc.)
  • adapting sequence support mechanisms (which are very RDBMS dependant)

[Caution]Caution
We do NOT officially support PostgreSQL in Summer Batch, but for the additional RDBMS support, examples are provided in corresponding appendix section, DDL scripts for PostgreSQL.
Back to Summer Batch home page