Retrieve Datatable

Posts   
 
    
Posts: 8
Joined: 08-Feb-2010
# Posted on: 08-Feb-2010 15:15:52   

Hello, I am thinking about using this product on my next project, but wanted to know if it had any built in support for sending a random query and having it return a datatable. Sometimes i'd like to do that rather then create a SP, map it, etc just in one off cases. I understand there is a way to do this if i were to learn all the ins and outs of this product but i simply don't have the time in this budget to learn advanced joins etc with this. Also, is there functionality for retrieving single values with queries also? Thanks! Peter

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 08-Feb-2010 15:32:00   

To create a query at runtime, you should use a DynamicList, which returns the resultSet in s dataTable. Please check the previous link for more information.

Posts: 8
Joined: 08-Feb-2010
# Posted on: 08-Feb-2010 17:49:13   

While i wish i had the time to learn all the features included, i need to know if i can provide a query as a string and get a datatable back with any of the methods in this product. Thanks so much for answering 'the question'. Peter

Seth avatar
Seth
User
Posts: 204
Joined: 25-Mar-2006
# Posted on: 08-Feb-2010 20:43:42   

Why not use a linq expression projected onto a custom object?

Posts: 8
Joined: 08-Feb-2010
# Posted on: 08-Feb-2010 20:58:26   

Can someone kindly answer my question? I am well aware of the myriad ways to retrieve data, i want to know if i can provide a query in a string at runtime with LLBLGen and get back a datatable.

Thanks so very much! Peter

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 09-Feb-2010 05:29:13   
David Elizondo | LLBLGen Support Team
Posts: 8
Joined: 08-Feb-2010
# Posted on: 09-Feb-2010 16:51:49   

Thanks, i was able to get the action query to work great. Is there a method with the RetrievalQuery that returns a datatable? Also, i read here and there that 'Running Raw sql is a bad idea.' Why? Isn't the end result of most of these ORM's creating a string and sending it to the db to get something back? I get confused on this stuff and would like to be writing the best/fastest code possible. Getting harder and harder to make a buck these days! Thanks, Peter

Seth avatar
Seth
User
Posts: 204
Joined: 25-Mar-2006
# Posted on: 09-Feb-2010 17:55:30   

The reason why a raw string is always bad is because you miss out on a statically checked query at compile time. This will avoid misspellings or other errors of that type when deploying the app. Instead of having to catch these kinds of bugs later, the compiler will catch them for you.

MTrinder
User
Posts: 1461
Joined: 08-Oct-2008
# Posted on: 09-Feb-2010 22:45:27   

Why? Isn't the end result of most of these ORM's creating a string and sending it to the db to get something back?

Yes it is, but the idea is that the string is generated for you based on the entities/filters etc that you specify, so that you don't need to worry about, can guarantee that it is generated ,parameterized and formatted correctly for your database etc.

What you get back is also meant to be the items that are generated by your ORM, rather than a flat data table.

There is no real reason why you cannot use the two techniques side by side if necessary - If all you are doing is sending generated SQL string to db to get back a datatable, I can't really see why you need an ORM in the first place...?

Matt

Posts: 8
Joined: 08-Feb-2010
# Posted on: 10-Feb-2010 02:25:39   

Hi Matt, I find it odd that one can't see why this could be helpful. I mean, really? You can't understand why having something autogenerate (and be able to regenerate based on your schema) and map your data elements for you not being useful along with sometimes being able to retrieve a datatable with complex joins? I can write a query in minutes, learning everything about this product is something i don't have time for now. Sometimes when i post on these forums i wonder if anyone actually has deadlines with what they work on. Thanks, still searching for a built in way to get a datatable back using LLBLGen.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 10-Feb-2010 05:34:58   

I agree with Matt. Tough we better don't get into this debate for now. There is so much debates already out there about this.

If you want raw sql, I think you just can take advantage of using the connection or connectionString from the LLBLGen generated code:

SqlDataAdapter sqlAdapter = new SqlDataAdapter(
    "SELECT * FROM Customers", new DataAccessAdapter().ConnectionString);

DataTable tableToFill = new DataTable();
sqlAdapter.Fill(tableToFill); 
David Elizondo | LLBLGen Support Team
MTrinder
User
Posts: 1461
Joined: 08-Oct-2008
# Posted on: 10-Feb-2010 10:52:43   

As David as said - if you do need a table back from a SQL string, the two lines of ADO.Net code shown are all that you need - why try to force LLBLGen to do something that it was not designed to do?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 10-Feb-2010 10:56:46   

DotNetPeter wrote:

Hi Matt, I find it odd that one can't see why this could be helpful. I mean, really? You can't understand why having something autogenerate (and be able to regenerate based on your schema) and map your data elements for you not being useful along with sometimes being able to retrieve a datatable with complex joins? I can write a query in minutes, learning everything about this product is something i don't have time for now. Sometimes when i post on these forums i wonder if anyone actually has deadlines with what they work on.

Trust me, I know what deadlines are wink

Thanks, still searching for a built in way to get a datatable back using LLBLGen.

The main issue with string based queries is that it really doesn't make sense to use an abstraction layer above the database (like the O/R mapper layer llblgen pro provides) for it, as a plain string is tied to the db targeted so you can just use plain ADO.NET with dataadapters, commands and datatables. After all it's not going to be shorter in code and it's better to use the abstraction layer to begin with, as it allows you to have compile time checked queries (e.g. when an entity gets removed, renamed etc.) among other things. Ofcourse, as a last resort, it can be a time saver to use the string-approach but it never should be used as a way to meet deadlines, as that string will be there forever.

Frans Bouma | Lead developer LLBLGen Pro
Posts: 8
Joined: 08-Feb-2010
# Posted on: 10-Feb-2010 13:36:02   

Thanks, knowing the answer helps so i can put the method in the right spot. So far its done everything i need. Cheers