Hi I am using LLBLGenPro v2.0, C#, SelfServicing.

I note that when using LLBLGenPro support for calling SQL Server Retrieval stored procs, all resultsets come back as generic DataTable objects. You then (ie. in your code) have to manually match up what value of what type is returned in what column e.g. you have to know that column 3 is a String containing the Customer Address etc.

I am wondering if there is any support in LLBLGen to be able to determine the returned column names or even datatypes when its generating the Stored Proc Caller class, and then ALSO generate a strongly-type Return Object e.g if my stored proc is

CREATE  procedure pr_CustomersByCountry
    @country VARCHAR(50)
AS
SELECT CustomerID, CustomerName, CustomerCreditLimit
FROM Customers 
WHERE Country = @country

Then, instead of generating:

    public static DataTable pr_CustomersByCountry(String country)

it would generate

public class pr_CustomersByCountryResultRow
{
    public int CustomerID;
    public String CustomerName;
    public Decimal CustomerCreditLimit
}

public static List<pr_CustomersByCountryResultRow> pr_CustomersByCountry(String country)

Is this a current or planned feature at all, or is there a better way to do this?

Cheers Brett

I know you've asked a similar question yesterday and are aware of projections. http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=9153 You may take a look at the documentation. There is an example of this. I believe you would have to retrieve all fields in the Customers table, but it is somewhat streamlined to work with Collections already. Here's a quick example from the docs.

CREATE  procedure pr_CustomersOnCountry
    @country VARCHAR(50)
AS
SELECT * FROM Customers WHERE Country = @country

CustomerCollection customers = new CustomerCollection();
using( IRetrievalQuery query = RetrievalProcedures.GetCustomersOnCountryCallAsQuery( "Germany" ) )
{
    TypedListDAO dao = new TypedListDAO();
    using( IDataReader reader = dao.GetAsDataReader(null, 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.
        DataProjectorToIEntityCollection projector = new DataProjectorToIEntityCollection( customers );
        dao.GetAsProjection( valueProjectors, projector, reader );
    }
}

I know that you may have seen this and I just may not be picking up on your difference. If you could explain a bit on how you would rather do with these as examples that will help.

Hi Thanks for the quick reply!

Yes, you're right - i can do largely what I want using projections, writing pretty much the code you had in your example.

However, there are a couple of problems with this: 1. It requires me to write a reasonable chunk of repetitive code myself i.e. lots of very similar lines, and I have to manually write this code for every single stored proc in my database (we have hundreds). 2. It still requires me to explicitly know what order the columns are returned in by the stored proc (e.g. first column is customer ID, second column is name etc), and I also have to know all the data types. It would be very easy to make a mistake and not know e.g accidentally get a datatype, or column position or column name wrong. 3. If somebody changes the stored proc without me knowing and inserts a new column or changes an existing one, my code will only fail at runtime or worse, might continue to work but give the wrong results.

My thinking is that given LLBLGenPro can determine the names and types of input params, could it not ALSO derive the output column names and data types, and generate a corresponding class as well, meaning that all stored proc calls return a collection of strongly typed row objects instead of a DataTable? I believe there would be big benefits: 1. All my client code would then be working entirely with collections of objects, rather than rows in data tables ie. much more OO. 2. Having strongly-typed collections returned by stored procs makes the client code much clearer e.g I can go

resultList.CustomerName

instead of

resultTable.rows[0].columns[2].ToString()

1. Having strongly-typed collections returned by stored procs would also allow the compiler to find datatype etc bugs at design time e.g. if my client code is trying to treat CustomerName as an Integer, the compiler would flag that as a compile error, rather than me having to track down a Type Conversion error at runtime. Every bug I can get the compiler to eliminate for me is one less thing to go wrong at run time..!
2. If somebody changes a stored proc to insert or change column, I would know all about it as soon as I regenerated the DAL code and tried to compile my project, because the strongly typed collection would have changed.

I realise that to do this, LLBLGEN would probably have to parse the T-SQL in some fashion, which is probably tricky, but I think CodeSmith already does this in some fashion? Anyway, I am keen to hear your thoughts on this whole plan!

Cheers Brett

If you need to perform a repetitive task for all the objects you have in a certain type (eg. entities, sproc...etc).

The you might use the code generation feature that comes with the LLBLGen Pro, to add a template that creates the projection for each SProc.

Hi Yes, possibly I could create my own template(s) to generate the projections as you suggest. However, it would rely on my Template and/or LLBLGen being able to determine the names and datatypes of all columns returned by Retrieval Stored Procs. I am relatively new to SQL Server (from an Oracle background) so is it possible to determine the return columns/types of a stored proc from metadata stored in the System Tables, or would you have to actually parse the T-SQL code - does anybody know? Cheers Brett

it would rely on my Template and/or LLBLGen being able to determine the names and datatypes of all columns returned by Retrieval Stored Procs. I am relatively new to SQL Server (from an Oracle background) so is it possible to determine the return columns/types of a stored proc from metadata stored in the System Tables, or would you have to actually parse the T-SQL code - does anybody know?

You got the point why LLBLGen Pro don't automatically detect returned columns and don't map them to an entity/entityCollection.

That's why Projection was added.

That meta-data isn't retrievable on a reliable basis. The OleDb ado.net client has a method called GetOleDbSchemaTable on the OleDbConnection which tries to retrieve the resultset columns from a proc but it's not reliable (mistakes are made, no results are returned with procs with temptables etc.), so we don't use it.

A ha! Well that explains THAT then..! I thought it all seemed too easy... ;-)

Thanks guys - much appreciated.

Cheers Brett