Executing adhoc query

Posts   
 
    
smurrell
User
Posts: 59
Joined: 22-Feb-2007
# Posted on: 28-Feb-2007 12:20:33   

Hello

I have used LLBLGen to generate the Entities, TypedLists, etc for my base database. However I need to be able to create new databases based on tables/fields which users define in the base database.

Is it possible using LLBLGen to execute a dynamic query to firstly create a database, then a table and then the fields or should I be using the EntLib or good on .NET to do this?

I would prefer to use LLBLGen so I don't have to use different libraries for database access.

Regards, Simon

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 28-Feb-2007 15:26:07   

For DDL at runtime and LLBLGen Pro, please check the following thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=8232

For how to execute raw SQL at runtime check the following threads: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7165 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=3809

smurrell
User
Posts: 59
Joined: 22-Feb-2007
# Posted on: 28-Feb-2007 16:40:49   

Hello Walaa

Do you have some sample code to demonstrate? I tried this but my brain is toast by the end of today. The custom sql statement is also in between a transaction.

Regards, Simon

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 28-Feb-2007 17:40:33   

I'm not sure what exactly that you are trying to do, but maybe the following thread, can help you: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=6245

smurrell
User
Posts: 59
Joined: 22-Feb-2007
# Posted on: 01-Mar-2007 12:03:10   

Hello Walaa

The PhysicalConnection property is not exposed in v2.0 from what I can see. Therefore I can't use the Thread you sent me to.

Regards, Simon

smurrell
User
Posts: 59
Joined: 22-Feb-2007
# Posted on: 01-Mar-2007 12:23:29   
// __LLBLGENPRO_USER_CODE_REGION_START CustomDataAccessAdapterCode

        public IDbConnection GetActiveConnection()
        {

            // Get Action Connection
            return base.GetActiveConnection();

        }

// __LLBLGENPRO_USER_CODE_REGION_END

Hello Walaa

I create a new method called GetActiveConnection in the DataAccessAdapter and called the GetActiveConnection from the DataAccessAdapterBase. I seems to be firing now.

Regards, Simon

smurrell
User
Posts: 59
Joined: 22-Feb-2007
# Posted on: 01-Mar-2007 13:55:01   

Hello Walaa

Below is some code which I finally created once I had create the GetActiveConnection method. I posted it in case someone else has the same question.

Regards, Simon



                        // Initialize Class
                        using (DataAccessAdapter masterAdapter = new DataAccessAdapter(masterConnection))
                        {

                            // Check Database Exists
                            IDataReader reader = masterAdapter.FetchDataReader(new RetrievalQuery(masterAdapter.GetActiveConnection(),
                                new SqlCommand("SELECT Count(*) FROM [MASTER].[dbo].[sysdatabases] WHERE [name] = '" + library.DatabaseName.Replace("'", "") + "'")),
                                CommandBehavior.SingleRow);
                            // Search through Reader
                            while (reader.Read())
                            {

                                // Check Value
                                if (!reader.GetValue(0).ToString().Equals("0"))
                                {

                                    // Throw Exception
                                    throw new ApplicationException("Database already exists!");

                                }

                            }
                            // Close Reader
                            reader.Close();

                        }
Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 01-Mar-2007 16:06:16   

Thank you very much for the feedback and for sharing the code simple_smile