How to execute arbitrary SQL?

Posts   
 
    
trevorg
User
Posts: 104
Joined: 15-Nov-2007
# Posted on: 18-Sep-2008 00:16:31   

I tried to find an answer to this on the forums but suprisingly couldn't......

LLBLGen 2.6 SelfServicing <----------------------------!!! VS2008

I would like to execute some SQL against the database, bypassing LLB entity objects (but using the current LLB connection to the database)

So a simple example is:

Dim x as Dataset = xxxxxxx.ExecuteDataset("select * from some_table")

where xxxxxxx is some LLBLGen object.

This is to handle the ability to handle new columns added to tables (the columns metadata is stored elsewhere and read at runtime in order to build the sql to be executed.)

peerke
User
Posts: 23
Joined: 19-Aug-2008
# Posted on: 18-Sep-2008 08:16:58   

One way to do it (for Oracle):


            DataSet ds = new DataSet();
            
            using (OracleConnection connection = DbUtils.CreateConnection())
            {
                OracleDataAdapter da = DbUtils.CreateDataAdapter();
                da.SelectCommand = new OracleCommand("SELECT * FROM tablename", connection);

                da.Fill(ds);
            }

HTH, peerke

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
trevorg
User
Posts: 104
Joined: 15-Nov-2007
# Posted on: 19-Sep-2008 19:18:06   

I feel like a bit of an idiot, but I have read all of the forums posts on this subject, but I still haven't figured out how one does this:

Dim x as Dataset = xxxxxxx.ExecuteDataset("select * from some_table") or xxxxxxxxx.ExecuteNonQuery("delete from some_table")

I'm hoping to avoid creating my own SQLConnection object (to avoid database specific code), and obviously would like to use the same connection object and support transactions andparameters properly.

SD.LLBLGen.Pro.ORMSupportClasses.DaoBase looks to be the place that contains very close to the functionality I am looking for (except it tends to use datareaders whereas I want to retrieve a dataset), but how to extend this I can't seem to figure out (if this is in fact the correct place).

There seems to be a fair amount of people wanting to do this so it would be really nice if there was a simple way to execute raw SQL via LLBLGen without having to extend the framework.....or at the very least if an extension has to be made that some sort of an all inclusive example could be posted in once place (in my case for self-servicing).

Help!!??

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39589
Joined: 17-Aug-2003
# Posted on: 20-Sep-2008 11:39:31   

Create a RetrievalQuery object, which is basicly a command object with the query, parameters etc. You can then feed that RetrievalObject to a projection retrieval routine or to an Execute* routine on a Dao class. SelfServicing creates its own connections, so you don't have to worry about that.

Frans Bouma | Lead developer LLBLGen Pro