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 :
-
ConnectionString : a
System.Configuration.ConnectionStringSettings
instance; connection string is used to provide all required details needed to connect to a given database. These 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 queries in a database reader. Externalizing these 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 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 implementsSummer.Batch.Data.Parameter.IQueryParameterSource
interface).
Supported syntax for query parameters : the query parameters are identified either with ':' or '@' 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 queries are stored in a dictionary ; matching with query parameters will be done using dictionary entries keys; -
Summer.Batch.Data.Parameter.PropertyParameterSource
: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
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
);
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; }
}
}
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),
};
};
}
}
Example 6.12. DataReaderItemReader
declaration in the job XML file¶
<step id="DatasourceReader">
<chunk item-count="1000">
<reader ref="DatasourceReader/DatasourceReaderRecord" />
...
</chunk>
</step>
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"];
container.RegisterInstance<ConnectionStringSettings>("ReaderConnection", readerConnectionstring);
// Reader - DatasourceReader/DatasourceReaderRecord
container.StepScopeRegistration<IItemReader<DatasourceReaderBO>,
DataReaderItemReader<DatasourceReaderBO>>("DatasourceReader/DatasourceReaderRecord")
.Property("ConnectionString").Reference<ConnectionStringSettings>("ReaderConnection")
.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
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.
Writing to a database¶
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:
-
ConnectionString : a System.Configuration.ConnectionStringSettings instance; connection string is used to provide required details needed to connect to a given database. These details are usually being stored in an application XML configuration file;
-
Query : a string representing SQL query to be executed on the database; To write to a database, only INSERT or UPDATE SQL statements should be used. Externalizing SQL queries in a resource file (.resx) is recommended; As for reader, query parameters will be prefixed either by ':' or '@'.
-
DbParameterSourceProvider : a class that implements
Summer.Batch.Data.Parameter.IQueryParameterSourceProvider<in T>
interface, in charge of filling query parameters with appropriate values, usually from a business object.The method to be implemented is
IQueryParameterSource CreateParameterSource(T item)
: query parameters will be fed by consumingSummer.Batch.Data.Parameter.IQueryParameterSource
.
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 :
- AssertUpdates : a flag to indicate whether to check if database rows have actually been updated; defaults to true (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)
CREATE TABLE [dbo].[BA_SQL_WRITER_TABLE] (
[IDENTIFIER] BIGINT IDENTITY(1,1) NOT NULL,
[CODE] INT ,
[NAME] VARCHAR(30) ,
[DESCRIPTION] VARCHAR(40) ,
[DATE] DATE
);
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; }
}
}
PropertyParameterSourceProvider
.
Now, we need to configure the writer; Firstly in 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>
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"];
container.RegisterInstance<ConnectionStringSettings>("WriterConnection", writerConnectionstring);
//... reader and processor registration not shown here
// Writer - DatasourceWriter/WriteRecodDatasource
container.StepScopeRegistration<IItemWriter<DatasourceWriterBO>,
DatabaseBatchItemWriter<DatasourceWriterBO>>("DatasourceWriter/WriteRecodDatasource")
.Property("ConnectionString").Reference<ConnectionStringSettings>("WriterConnection")
.Property("Query").Value(SqlQueries.WriteRecodDatasource_S01_DatasourceWriter_SQL_QUERY)
.Property("DbParameterSourceProvider")
.Reference<PropertyParameterSourceProvider<DatasourceWriterBO>>()
.Register();
}
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)
Database Support¶
Summer Batch currently supports following three RDBMS:
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 |
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
is used to replace parameters in queries with placeholders. All SQL queries should use either “@” or “:” to prefix parameters, the placeholder getter will be used to transform the query so that it uses placeholders required by the provider.
Incrementer
An instance of IDataFieldMaxValueIncrementer
is used to retrieve unique ids for different batch entities, such as job or step executions. Best way to generate unique ids is dependent on the specified RDBMS, thus the extension is required to provide an incrementer.
The following example show how to add support for PostgreSQL using 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);
}
}
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
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.