Reading from and writing to RDBMS

Reading from and writing to RDBMS
Prev	Chapter 6. Using Basic Features	Next

Reading from a database

Support for reading from a database is brought by the

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.

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

ConnectionString : a System.Configuration.ConnectionStringSettings instance; the connection string is used to provide all required details needed to connect to a given database; Those details are usually being stored in an application xml configuration file;
Query : a string representing the sql query to be executed against the database; Obviously, only SELECT sql statements should be used as query in a database reader;Externalizing the sql queries in a resource file (.resx) is recommended;
RowMapper: instance of a Summer.Batch.Data.RowMapper<out T>, in charge of converting a row from the resultset returned by the query execution to a business object of type T.

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

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

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

Example 6.9. Query parameters supported syntax examples

(both query are valid and equivalent):

select CODE,NAME,DESCRIPTION,DATE from BA_SQL_READER_TABLE_1 
					where CODE = :Code

select CODE,NAME,DESCRIPTION,DATE from BA_SQL_READER_TABLE_1 
					where CODE = @Code

Two implementations of IQueryParameterSource are provided in Summer Batch :

Summer.Batch.Data.Parameter.DictionaryParameterSource: parameters for the query are stored in a dictionary ; matching with the query parameters will be done using the dictionary entries keys;
Summer.Batch.Data.Parameter.PropertyParameterSource: The PropertyParameterSource constructor requires a business object as argument; the query parameters will be filled by the business object properties, matching will be done using the properties names.

The query used in the example is

select CODE,NAME,DESCRIPTION,DATE from BA_SQL_READER_TABLE_1

Records are read from the 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
  )
;

The results from the 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 the resultset rows and the 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 the database reader. First, the job xml file :

Example 6.12. DataReaderItemReader declaration in the job xml file

<step id="DatasourceReader">
	<chunk item-count="1000">
		<reader ref="DatasourceReader/DatasourceReaderRecord" />
		...
	</chunk>
</step>

Then, the unity configuration part:

Example 6.13. Formatted flat file writer - sample unity configuration

/// <summary>
/// Registers the artifacts required to execute the steps (tasklets, readers, writers, etc.)
/// </summary>
/// <param name="container">the unity container to use for registrations</param>
public override void LoadArtifacts(IUnityContainer container)
{
    ConnectionStringRegistration.Register(container);
    RegisterDatasourceReader(container);
}

/// <summary>
/// Registers the artifacts required for step DatasourceReader.
/// </summary>
/// <param name="container">the unity container to use for registrations</param>
private void RegisterDatasourceReader(IUnityContainer container)
{
	//Connection string
	var readerConnectionstring = 
		System.Configuration.ConfigurationManager.ConnectionStrings["ReaderConnection"];

    // Reader - DatasourceReader/DatasourceReaderRecord
    container.StepScopeRegistration<IItemReader<DatasourceReaderBO>,
    	DataReaderItemReader<DatasourceReaderBO>>("DatasourceReader/DatasourceReaderRecord")
        .Property("ConnectionString").Instance(readerConnectionstring)
        .Property("Query").Value(SqlQueries.DatasourceReader_SQL_QUERY)
        .Property("RowMapper").Instance(DatasourceReaderSQLReaderMapper.RowMapper)
        .Register();

		// ... Processor and writer registration not being shown here

Note

In this example, the connection string is read from the default application configuration file, using the 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); the SqlQueries class is the corresponding designer artifact created by Visual Studio to wrap the underlying resource.

Writing to a database

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

The following properties must be set at initialization time:

ConnectionString : a System.Configuration.ConnectionStringSettings instance; the connection string is used to provide all required details needed to connect to a given database; Those details are usually being stored in an application xml configuration file;
Query : a string representing the sql query to be executed against the database; To write to a database, only INSERT or UPDATE sql statements should be used;Externalizing the sql queries in a resource file (.resx) is recommended; As for the reader, the query parameters will be prefixed either by ':' or '@'.
DbParameterSourceProvider : a class that implements the Summer.Batch.Data.Parameter.IQueryParameterSourceProvider<in T> interface, in charge of filling the query parameters with the appropriate values, usually from a business object.
The method to be implemented is
IQueryParameterSource CreateParameterSource(T item) : the query parameters will be fed by consuming the provided Summer.Batch.Data.Parameter.IQueryParameterSource.

Summer Batch provides an implementation for the 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 the query parameters to the business object properties, on a naming convention basis.

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

AssertUpdates : a flag to indicate whether to check if database rows have actually been updated; defaults to true (which is the recommended mode);

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 the business object and the query parameters is achieved by using a PropertyParameterSourceProvider.

Now, we need to configure the writer; first, in the job xml file :

Example 6.15. DatabaseBatchItemWriter declaration in the job xml file

<step id="DatasourceWriter">
	<chunk item-count="1000">
		...
		<writer ref="DatasourceWriter/WriteRecodDatasource" />
	</chunk>
</step>

Then the unity configuration snippet:

Example 6.16. Database batch writer - sample unity configuration

/// <summary>
/// Registers the artifacts required for step DatasourceWriter.
/// </summary>
/// <param name="container">the unity container to use for registrations</param>
private void RegisterDatasourceWriter(IUnityContainer container)
{
	//Connection string
	var writerConnectionstring = 
		System.Configuration.ConfigurationManager.ConnectionStrings["WriterConnection"];

    //... reader and processor registration not shown here

    // Writer - DatasourceWriter/WriteRecodDatasource
    container.StepScopeRegistration<IItemWriter<DatasourceWriterBO>,
    		DatabaseBatchItemWriter<DatasourceWriterBO>>("DatasourceWriter/WriteRecodDatasource")
        .Property("ConnectionString").Instance(writerConnectionstring)
        .Property("Query").Value(SqlQueries.WriteRecodDatasource_S01_DatasourceWriter_SQL_QUERY)
        .Property("DbParameterSourceProvider")
        	.Reference<PropertyParameterSourceProvider<DatasourceWriterBO>>()
        .Register();
}

	Note
	As in the database reader example: The connection details are read from an application xml config file; The query is read from a resource file (.resx);

Database Support

Summer Batch currently supports three RDBMS, with the following invariant provider names:

RDBMS	Provider Names
Microsoft® Sql Server	`System.Data.SqlClient`
Oracle® Database	`System.Data.OracleClient`, `Oracle.ManagedDataAccess.Client`, `Oracle.DataAccess.Client`
IBM® DB2	`IBM.Data.DB2`

The 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:

ProviderNames: An enumeration of the invariant provider names supported by this extension.
PlaceHolderGetter: An instance of IPlaceHolderGetter that is used to replace parameter in queries with the correct placeholder. All SQL queries should use either “@” or “:” to prefix parameters, the placeholder getter will be used to transform the query so that it uses the placeholder required by the provider.
Incrementer: An instance of IDataFieldMaxValueIncrementer that is used to retrieve unique ids for different batch entities, such as job or step executions. The best way to generate unique ids is very dependent on the actual RDBMS, so the extension is required to provide an incrementer.

The following example show how support for PostgreSQL can be added, using the Npgsql provider:

Example 6.17. Adding support for other RDBMS : the PostgreSQL example

public class PostgreSqlExtension : IDatabaseExtension
{
    public IEnumerable<string> ProviderNames
    {
        get { return new[] { "Npgsql" }; }
    }

    public IPlaceholderGetter PlaceholderGetter
    {
        get { return new PlaceholderGetter(name => ":" + name, true); }
    }

    public IDataFieldMaxValueIncrementer Incrementer
    {
        get { return new PostgreSqlIncrementer(); }
    }
}

public class PostgreSqlIncrementer : AbstractSequenceMaxValueIncrementer
{
    protected override string GetSequenceQuery()
    {
        return string.Format("select nextval('{0}')", IncrementerName);
    }
}

In addition, to provide a complete support for an additional RDBMS, the job repository ddl scripts must be adapted to the target RDBMS;

Typical required ddl adjustements are :

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

	Caution
	We do NOT officially support PostgreSQL in Summer Batch, but for the additional rdbms support example to be exhaustive, we provide in the corresponding appendix section, the ddl scripts for PostgreSQL. Those scripts are provided 'AS IS', just for the sake of completion.