Fetching DataReaders and projections

Besides using Linq or QuerySpec, LLBLGen Pro has two other ways of fetching a resultset of fields: as an open IDataReader object and as a projection. This section discusses both and offers examples for each of them, 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 method where 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.

Although QuerySpec and Linq offer ways to fetch projections into objects, this section primarily discusses projections using the low-level API.  QuerySpec also supports fetching a projection as IDataReader. An example of this is given below in the section about fetching a dynamic list as IDataReader.

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 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.

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();
}

Using adapter As New DataAccessAdapter()
    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()
End Using

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.

using(var adapter = new DataAccessAdapter())
{
    var qf = new QueryFactory();
    var q = qf.Create()
                .Select(CustomerFields.CustomerId, CustomerFields.CompanyName, 
                        OrderFields.OrderId)
                .From(qf.Customer.InnerJoin(qf.Order)
                        .On(CustomerFields.CustomerId == OrderFields.CustomerId))
                .Where(CustomerFields.Country == "Germany");

    var reader = adapter.FetchAsDataReader(q, CommandBehavior.CloseConnection);
    while(reader.Read())
    {
        Console.WriteLine("Row: {0} | {1} | {2} |",
        reader.GetValue(0), reader.GetValue(1), reader.GetValue(2));
    }
    reader.Close();
}

Using adapter As New DataAccessAdapter()
    Dim qf As New QueryFactory()
    Dim q = qf.Create() _
                .Select(CustomerFields.CustomerId, CustomerFields.CompanyName, 
                        OrderFields.OrderId) _
                .From(qf.Customer.InnerJoin(qf.Order)
                        .On(CustomerFields.CustomerId = OrderFields.CustomerId)) _
                .Where(CustomerFields.Country = "Germany")

    Dim reader = adapter.FetchAsDataReader(q, CommandBehavior.CloseConnection)
    While reader.Read()
        Console.WriteLine( "Row: {0} | {1} | {2} |", _
            reader.GetValue( 0 ), reader.GetValue( 1 ), reader.GetValue( 2 ) )
    End While
    reader.Close()
End Using

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.Equal("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();
}

Using adapter As New DataAccessAdapter()
    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(CustomerFields.Country.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()
End Using

Projecting Stored Procedure resultset onto POCO classes

For this stored procedure projection example, the following stored procedure 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 SQL Server 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 a List of Poco classes: the first resultset on a List<CustomerDTO> and the second on a List<OrderDTO>. The stored procedure uses a wildcard select list. This is for simplicity.

List<CustomerDTO> customers;
List<OrderDTO> orders;
using(var adapter = new DataAccessAdapter())
{
    using(var query = RetrievalProcedures.GetCustomersAndOrdersOnCountryCallAsQuery("Germany"))
    {
        using(var reader = adapter.FetchDataReader(query, CommandBehavior.CloseConnection))
        {
            customers = adapter.FetchProjection<CustomerDTO>(reader);
            reader.NextResult();
            orders = adapter.FetchProjection<OrderDTO>(reader);
            reader.Close();
        }
    }
}

Where CustomerDTO is:

public partial class CustomerDTO
{
    public string CompanyName { get; set; }
    public string ContactName { get; set; }
    public string ContactTitle { get; set; }
    public string CustomerId { get; set; }
    public string Fax { get; set; }
    public string Phone { get; set; }
    public string PostalCode { get; set; }
    public string Country { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
}

and OrderDTO is

public partial class OrderDTO
{
    public string CustomerId { get; set; }
    public int? EmployeeId { get; set; }
    public Decimal? Freight { get; set; }
    public DateTime? OrderDate { get; set; }
    public int OrderId { get; set; }
    public DateTime? RequiredDate { get; set; }
    public string ShipAddress { get; set; }
    public string ShipCity { get; set; }
    public string ShipCountry { get; set; }
    public string ShipName { get; set; }
    public DateTime? ShippedDate { get; set; }
    public string ShipPostalCode { get; set; }
    public string ShipRegion { get; set; }
}

If only the first resultset is enough, or the stored procedure returns just one resultset, you can also use the following, which avoids fetching the reader first.

List<CustomerDTO> customers;
using(var adapter = new DataAccessAdapter())
{
    customers = adapter.FetchProjection<CustomerDTO>(
                    RetrievalProcedures.GetCustomersAndOrdersOnCountryCallAsQuery("Germany"));
}

Projecting Dynamic List resultset onto POCO 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). For clarity, the Linq and QuerySpec alternatives are given as well.

List<CustomCustomer> customClasses;
using(var adapter = new DataAccessAdapter())
{
    var qf = new QueryFactory();
    var q = qf.Customer
                .Select<CustomCustomer>(CustomerFields.City, 
                                        CustomerFields.CompanyName, 
                                        CustomerFields.Country, 
                                        CustomerFields.CustomerId);
    customClasses = adapter.FetchQuery(q);
}

List<CustomCustomer> customClasses;
using(var adapter = new DataAccessAdapter())
{
    var metaData = new LinqMetaData(adapter);
    var q = from c in metaData.Customer
        select new CustomCustomer()
        {
            CustomerID = c.CustomerId,
            CompanyName = c.CompanyName,
            Country = c.Country,
            City = c.City
        };
    customClasses = q.ToList();
}

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

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

// Define the projections of the fields.    
var 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 );
}

Where the custom class is:

public class CustomCustomer
{
    public string CustomerID { get; set;}
    public string City { get; set; }
    public string CompanyName { get; set;}
    public string Country { get; set;}
}