LLBLGen Pro - generated code - Fetching DataReaders and projections, Adapter

Generated code - Fetching DataReaders and projections, Adapter

Preface

LLBLGen Pro v2 introduces two new ways of fetching a resultset: as an open IDataReader object and as a projection. This section discusses both and illustrates both with a couple of examples, either using a stored procedure or a query build using entity fields. Fetching a resultset as an open IDataReader is considered an advanced feature and should be used with care: an open IDataReader object represents an open cursor to data on a connected RDBMS over an open connection. This means that passing the IDataReader around in your application is not recommended. Instead use the IDataReader in the routine you also called the fetch logic to create it and immediately after that make sure the IDataReader gets closed and disposed. This way you're sure you'll free up resources early.

To understand projections better, it's recommended to first read the section about fetching an open IDataReader. Another section describing projections, but then related to an entity view object, is Generated code - using the EntityView2 class.

Fetching a resultset as an open IDataReader

To fetch a resultset as an open IDataReader, you call one of the overloads of FetchDataReader, a method of DataAccessAdapter. There are two ways to use the FetchDataReader method: by supplying a ready to use IRetrievalQuery or by specifying a fields list, and various other elements which are required for creating a new query by the Dynamic Query Engine (DQE). The first option, the IRetrievalQuery option, can be used to fetch a retrieval stored procedure as an open IDataReader, by using the RetrievalProcedures.GetStoredProcedureNameCallAsQuery() method of the particular stored procedure call. This is a generated method, one for every retrieval stored procedure call known in the LLBLGen Pro project.

FetchDataReader accepts also a parameter called CommandBehavior. This parameter is very important as it controls the behavior the datareader should perform when the datareader is closed. It's only required to specify a behavior different than CloseConnection if the fetch is inside a transaction and the connection has to stay open after the datareader has been closed.

It's recommended to familiar yourself with the various overloads of the FetchDataReader method using the LLBLGen Pro reference manual. The method is defined on DataAccessAdapterBase, the base class of every generated DataAccessAdapter class.

It's possible to construct your own IRetrievalQuery object with your own SQL, by instantiating a new RetrievalQuery object. However in general, it's recommended to use the FetchDataReader overloads which accept a fieldslist and other elements and let LLBLGen Pro generate the query for you.

Fetching a Retrieval Stored Procedure as an IDataReader

An example of calling a procedure and receive a datareader from it is enlisted below. It calls the Northwind stored procedure CustOrdersOrders which returns a single resultset with 4 fields. The example simply prints the output on the console.
The VB.NET example uses a try / finally block as VB.NET for .NET 1.x doesn't support the Using statement. Users of VB.NET for .NET 2.0 can replace the try/finally block with the new Using statement as illustrated in the C# example.

C#
VB.NET

 // C#
using( DataAccessAdapter adapter = new DataAccessAdapter() )
{
	IDataReader reader = adapter.FetchDataReader( 
		RetrievalProcedures.GetCustOrdersOrdersCallAsQuery( "CHOPS" ), 
		CommandBehavior.CloseConnection );
	while( reader.Read() )
	{
		Console.WriteLine( "Row: {0} | {1} | {2} | {3} |", reader.GetValue( 0 ), 
			reader.GetValue( 1 ), reader.GetValue( 2 ), reader.GetValue( 3 ) );
	}
	// close reader, will also close connection
	reader.Close();
}

 ' VB.NET
Dim adapter As New DataAccessAdapter()
Try
	Dim reader as IDataReader = adapter.FetchDataReader( _
		RetrievalProcedures.GetCustOrdersOrdersCallAsQuery( "CHOPS" ), _
		CommandBehavior.CloseConnection )
	While reader.Read()
		Console.WriteLine( "Row: {0} | {1} | {2} | {3} |", reader.GetValue( 0 ), _
			reader.GetValue( 1 ), reader.GetValue( 2 ), reader.GetValue( 3 ) )
	End While
	' close reader, will also close connection
	reader.Close()
Finally
	' Connection is closed when the reader was closed
	adapter.Dispose()
End Try

Fetching a Dynamic List as an IDataReader

An example of a dynamic list which is used to receive a datareader from it is enlisted below. The example simply prints the output on the console.

C#
VB.NET

 // C#
using( DataAccessAdapter adapter = new DataAccessAdapter() )
{
	ResultsetFields fields = new ResultsetFields( 3 );
	// simply set the fields in the indexes, which will use the field name for the column name
	fields[0] = CustomerFields.CustomerId;
	fields[1] = CustomerFields.CompanyName;
	fields[2] = OrderFields.OrderId;
	RelationPredicateBucket filter = new RelationPredicateBucket(CustomerFields.Country == "Germany");
	filter.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerId);
	IDataReader reader = adapter.FetchDataReader( fields, filter, CommandBehavior.CloseConnection, 0, true );
	while( reader.Read() )
	{
		Console.WriteLine( "Row: {0} | {1} | {2} |", 
			reader.GetValue( 0 ), reader.GetValue( 1 ), reader.GetValue( 2 ) );
	}
	reader.Close();
}

 ' VB.NET 
Dim adapter As New DataAccessAdapter()
Try
	Dim fields As New ResultsetFields( 3 )
	' simply set the fields in the indexes, which will use the field name for the column name
	fields(0) = CustomerFields.CustomerId
	fields(1) = CustomerFields.CompanyName
	fields(2) = OrderFields.OrderId
	Dim filter As New RelationPredicateBucket()
	filter.PredicateExpression.Add( _
		New FieldCompareValuePredicate(CustomerFields.Country, Nothing, ComparisionOperator.Equal,  "Germany"))
	filter.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerId)
	Dim reader As IDataReader = adapter.FetchDataReader( fields, filter, CommandBehavior.CloseConnection, 0, True )
	While reader.Read()
		Console.WriteLine( "Row: {0} | {1} | {2} |", _
			reader.GetValue( 0 ), reader.GetValue( 1 ), reader.GetValue( 2 ) )
	End While
	reader.Close()
Finally
	adapter.Dispose()
End Try

Resultset projections

In the previous section we've seen that a query could be fetched as an open IDataReader, where the query could be an IRetrievalQuery object containing a stored procedure call, or a dynamic formulated query from fields, a filter and other elements you might want to use in the query. It is then up to you what to do with the IDataReader. It's likely you'll project the data available to you through the IDataReader object onto a data-structure. Projecting a resultset is a term from the relational algebra, the Wikipedia has a formal explanation of it: Projection (relational algebra) (opens in a new window). It comes down to the fact that you create a new set of data from an existing set of data. The existing set of data is the resultset you want to project. The new set is the projection result.

LLBLGen Pro offers two different projection mechanisms: projecting an EntityView2 (see: Generated code - using the EntityView2 class) and projecting a fetched resultset, which is discussed here. Both mechanisms are roughly the same, only the source data origin differs and the used interface implemented by the used projection engine. The projections of entity view data are a little more advanced because it's possible to execute in-memory filters on the entity object itself to make a selection which field to project. This means that the projector objects, as discussed in the EntityView2 Projection documentation, are both implementing the IDataValueProjector, but the projector objects used for EntityView2 projections also implement the interface derived from IDataValueProjector, IEntityPropertyProjector. For projections of EntityView2 data, EntityPropertyProjector objects are used, for projections of resultset data, the more simpler DataValueProjector objects are used. Their meaning is roughly the same, so if you're familiar with EntityView2 projections, you'll directly understand the examples below using DataValueProjector objects. As the projection engine interfaces required for both mechanisms are fairly similar, the shipped projection engines thereby can be used for both mechanisms.

Resultset projections are done by an IGeneralDataProjector implementation. IGeneralDataProjector allows an object[] array of values to be projected onto new instances of whatever class is supported by the IGeneralDataProjector implementation, for example new entities or a DataRow in a DataTable. Which values in the object[] array are projected onto which properties of the target element, created by the IGeneralDataProjector implementation, is specified by the specified set of IDataValueProjector implementations passed in. In the following examples you'll see the usage of the projection engines is similar to the usage of the projection engines in the EntityView2 projection examples.

In Adapter, the DataAccessAdapter class has a method called FetchProjection with various overloads. This method produces the projection of the resultset defined by the input parameters (similar to the FetchDataReader method) or the resultset passed in in the form of an open IDataReader object. By which projection engine the projection is performed as well which data is projected is passed in as well. FetchProjection doesn't return a value, the result is in the projection engine object. This method has similar overloads as FetchDataReader, though it doesn't accept a commandbehavior: if a connection is open, it leaves it open, if no connection is open, it creates one and closes one afterwards.

Projecting Stored Procedure resultset onto entity collection

For this stored procedure projection example, the following stored proecdure is used:

CREATE  procedure pr_CustomersAndOrdersOnCountry
	@country VARCHAR(50)
AS
SELECT * FROM Customers WHERE Country = @country
SELECT * FROM Orders WHERE CustomerID IN
(
	SELECT CustomerID FROM Customers WHERE Country = @country
)

which is a SqlServer stored procedure and which returns 2 resultsets: the first is all customers filtered on a given Country, and the second is all orders of those filtered customers.

The stored procedure is fetched as an open IDataReader and both resultsets are projected onto entity collections: the first resultset on an EntityCollection object with CustomerEntity instances and the second on an EntityCollection of OrderEntity instances. The stored procedure uses a wildcard select list. This is for simplicity. The code below is written using .NET 1.x for clarity. .NET 2.0 users are encouraged to use the generic variants of the discussed classes instead, as discussed also in Generated code - using the EntityView2 class.

C#
VB.NET

 // C#
EntityCollection customers = new EntityCollection( new CustomerEntityFactory() );
EntityCollection orders = new EntityCollection( new OrderEntityFactory() );
using(IRetrievalQuery query = RetrievalProcedures.GetCustomersAndOrdersOnCountryCallAsQuery( "Germany" ))
{
	using(DataAccessAdapter adapter = new DataAccessAdapter())
	{
		using(IDataReader reader = adapter.FetchDataReader(query, CommandBehavior.CloseConnection))
		{
			// first resultset: Customers.
			List<IDataValueProjector> valueProjectors = new List<IDataValueProjector>();
			// project value on index 0 in resultset row onto CustomerId
			valueProjectors.Add( new DataValueProjector( CustomerFieldIndex.CustomerId.ToString(), 0, typeof( string ) ) );
			// project value on index 1 in resultset row onto CompanyName
			valueProjectors.Add( new DataValueProjector( CustomerFieldIndex.CompanyName.ToString(), 1, typeof( string ) ) );
			// resultset contains more rows, we just project those 2. The rest is trivial.
			DataProjectorToIEntityCollection2 projector = new DataProjectorToIEntityCollection2( customers );
			adapter.FetchProjection( valueProjectors, projector, reader );

			// second resultset: Orders. 
			valueProjectors = new ArrayList();
			valueProjectors.Add( new DataValueProjector( OrderFieldIndex.OrderId.ToString(), 0, typeof( int ) ) );
			valueProjectors.Add( new DataValueProjector( OrderFieldIndex.CustomerId.ToString(), 1, typeof( string ) ) );
			valueProjectors.Add( new DataValueProjector( OrderFieldIndex.OrderDate.ToString(), 3, typeof( DateTime ) ) );
			// switch to the next resultset in the datareader
			reader.NextResult();
			projector = new DataProjectorToIEntityCollection2( orders );
			adapter.FetchProjection( valueProjectors, projector, reader );
			reader.Close();
		}
	}
}

 ' VB.NET 
Dim customers As New EntityCollection( New CustomerEntityFactory() )
Dim orders As New EntityCollection( New OrderEntityFactory() )
Dim query As IRetrievalQuery = RetrievalProcedures.GetCustomersAndOrdersOnCountryCallAsQuery( "Germany" )
Try
	Dim adapter As New DataAccessAdapter()
	Try
		Dim reader As IDataReader = adapter.FetchDataReader(query, CommandBehavior.CloseConnection))
		Try
			' first resultset: Customers.
			Dim valueProjectors As New List(Of IDataValueProjector)()
			' project value on index 0 in resultset row onto CustomerId
			valueProjectors.Add( New DataValueProjector( CustomerFieldIndex.CustomerId.ToString(), 0, GetType( Sring ) ) )
			' project value on index 1 in resultset row onto CompanyName
			valueProjectors.Add( New DataValueProjector( CustomerFieldIndex.CompanyName.ToString(), 1, GetType( String ) ) )
			' resultset contains more rows, we just project those 2. The rest is trivial.
			Dim projector As New DataProjectorToIEntityCollection2( customers )
			adapter.FetchProjection( valueProjectors, projector, reader )

			' second resultset: Orders. 
			valueProjectors = New ArrayList()
			valueProjectors.Add( New DataValueProjector( OrderFieldIndex.OrderId.ToString(), 0, GetType( Integer ) ) )
			valueProjectors.Add( New DataValueProjector( OrderFieldIndex.CustomerId.ToString(), 1, GetType( String ) ) )
			valueProjectors.Add( New DataValueProjector( OrderFieldIndex.OrderDate.ToString(), 3, GetType( DateTime ) ) )
			' switch to the next resultset in the datareader
			reader.NextResult()
			projector = New DataProjectorToIEntityCollection2( orders )
			adapter.FetchProjection( valueProjectors, projector, reader )
			reader.Close()
		Finally
			reader.Dispose()
		End Try
	Finally
		adapter.Dispose()
	End Try
Finally
	' Not really necessary for SqlServer, but is required on Oracle, so it's mentioned here
	' for completeness.
	query.Dispose()
End Try

Projecting Dynamic List resultset onto custom classes

We can go one step further and create a fetch of a dynamic list and fill a list of custom class instances, for example for transportation by a Webservice and you want lightweight Data Transfer Objects (DTO). The projecting a resultset onto custom classes is .NET 2.0 only, as the projection engine uses generics. Of course, you can write your own implementation of IGeneralDataProjector which performs class instantiation and property setting using reflection on .NET 1.x

C# .NET 2.0
VB.NET .NET 2.0

 // C#  .NET 2.0
List<CustomCustomer> customClasses = new List<CustomCustomer>();
ResultsetFields fields = new ResultsetFields( 4 );
fields[0] = CustomerFields.City;
fields[1] = CustomerFields.CompanyName;
fields[2] = CustomerFields.CustomerId;
fields[3] = CustomerFields.Country;

DataProjectorToCustomClass<CustomCustomer> projector = 
	new DataProjectorToCustomClass<CustomCustomer>( customClasses );

// Define the projections of the fields.	
List<IDataValueProjector> valueProjectors = new List<IDataValueProjector>();
valueProjectors.Add( new DataValueProjector( "City", 0, typeof( string ) ) );
valueProjectors.Add( new DataValueProjector( "CompanyName", 1, typeof( string ) ) );
valueProjectors.Add( new DataValueProjector( "CustomerID", 2, typeof( string ) ) );
valueProjectors.Add( new DataValueProjector( "Country", 3, typeof( string ) ) );

// perform the fetch combined with the projection.
using( DataAccessAdapter adapter = new DataAccessAdapter() )
{
	adapter.FetchProjection( valueProjectors, projector, fields, null, 0, true );
}

 ' VB.NET .NET 2.0
Dim customClasses As New List(Of CustomCustomer)()
Dim fields As New ResultsetFields( 4 )
fields(0) = CustomerFields.City
fields(1) = CustomerFields.CompanyName
fields(2) = CustomerFields.CustomerId
fields(3) = CustomerFields.Country

Dim projector As New DataProjectorToCustomClass(Of CustomCustomer)( customClasses )

' Define the projections of the fields.	
Dim valueProjectors As New List(Of IDataValueProjector)()
valueProjectors.Add( New DataValueProjector( "City", 0, GetType( String ) ) )
valueProjectors.Add( New DataValueProjector( "CompanyName", 1, GetType( String ) ) )
valueProjectors.Add( New DataValueProjector( "CustomerID", 2, GetType( String ) ) )
valueProjectors.Add( New DataValueProjector( "Country", 3, GetType( String ) ) )

' perform the fetch combined with the projection.
Using adapter As New DataAccessAdapter()
	adapter.FetchProjection( valueProjectors, projector, fields, Nothing, 0, True )
End Using

Where the custom class is:

public class CustomCustomer
{
	#region Class Member Declarations
	private string _customerID, _companyName, _city, _country;
	#endregion

	public CustomCustomer()
	{
		_city = string.Empty;
		_companyName = string.Empty;
		_customerID = string.Empty;
		_country = string.Empty;
	}

	#region Class Property Declarations
	public string CustomerID
	{
		get { return _customerID; }
		set { _customerID = value; }
	}

	public string City
	{
		get { return _city; }
		set { _city = value; }
	}

	public string CompanyName
	{
		get { return _companyName; }
		set { _companyName = value; }
	}

	public string Country
	{
		get { return _country; }
		set { _country = value; }
	}
	#endregion
}