We are evaluating LLBLGen for a new product. We currently have another product using another ORM. Each client has its own database so we set the connection string when they login. I see this can be done using the apdater class, what about the self-servicing?

A single page request can use multiple connections to different DBs. Currently when a connection is needed we create it and place the connections in the request until it finishes. It appears to me that LLBLGen closes the connection when the adapter is disposed. Is there a way to pass the connection around until I am done with it.

tsmith wrote:

We are evaluating LLBLGen for a new product. We currently have another product using another ORM. Each client has its own database so we set the connection string when they login. I see this can be done using the apdater class, what about the self-servicing?

that's not possible with selfservicing, as selfservicing uses a global connection string as there's no per-call session / adapter you can use. With adapter it is possible, as you can use per-call a different adapter and therefore a different database backend type if you want (read entity A from oracle, read entity B from sqlserver, write entity A and B into access etc. )

A single page request can use multiple connections to different DBs. Currently when a connection is needed we create it and place the connections in the request until it finishes. It appears to me that LLBLGen closes the connection when the adapter is disposed. Is there a way to pass the connection around until I am done with it.

You want to connect to the same database instance (like a sqlserver installation) and multiple catalogs?

You can either use all catalogs (or schemas if you're on oracle) in your project and you can then use 1 adapter to access them all You can also derive a class from DataAccessAdapter and override CreateNewPhysicalConnection() and in your override you pass to the base method a member value you set via a property, which allows you to pass around a connection object.

Using the Northwind Adapter Example I created a decendant of DataAccessAdapter with an override of CreateNewPhysicalConnection. I changed CalculateStatistics to use the new DataAccessAdapter. I receive the following error when apdater.FetchTypedList is called ("The ConnectionString property has not been initialized.")

    public class MyDAA : DataAccessAdapter
    {
        protected override IDbConnection CreateNewPhysicalConnection(string connectionString)
        {
            return MyConnection.GetConnection(connectionString);
        }
    }

    public static class MyConnection
    {
        private static Dictionary<int, SqlConnection> conns = new Dictionary<int, SqlConnection>();

        public static SqlConnection GetConnection(string connectionString)
        {
            int id = connectionString.GetHashCode();

            if (conns.ContainsKey(id))
            {
                return conns[id];
            }
            else
            {
                SqlConnection conn = new SqlConnection(connectionString);
                conns.Add(id, conn);
                return conn;
            }
        }
    }

Strange, it should work ok. COuld you post the stacktrace as well? That's always more helpful that the exception alone

System.InvalidOperationException was unhandled Message="The ConnectionString property has not been initialized." Source="System.Data" StackTrace: at System.Data.SqlClient.SqlConnection.PermissionDemand() at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.OpenConnection() at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowDataTableRetrievalQuery(IRetrievalQuery queryToExecute, DbDataAdapter dataAdapterToUse, DataTable tableToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IGroupByCollection groupByClause) at Northwind.GUI.MainForm.CalculateStatistics() in c:\Projects_Net\Examples\Northwind Example Adapter\GUI\MainForm.cs:line 96 at Northwind.GUI.MainForm..ctor() in c:\Projects_Net\Examples\Northwind Example Adapter\GUI\MainForm.cs:line 69 at Northwind.GUI.Startup.Main(String[] args) in c:\Projects_Net\Examples\Northwind Example Adapter\GUI\Startup.cs:line 23 at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart()

It seems that the connection was closed some where before the last call (FetchTypedList).

Would you please post the code corresponding to the following quote:

I changed CalculateStatistics to use the new DataAccessAdapter. I receive the following error when apdater.FetchTypedList is called ("The ConnectionString property has not been initialized.")

private void CalculateStatistics()
        {
            // get amount customers
            //using (DataAccessAdapter adapter = new DataAccessAdapter())
            using (MyDAA adapter = new MyDAA())  <-- Only Change
            {
                int amountCustomers = (int)adapter.GetScalar(CustomerFields.CustomerId, AggregateFunction.CountRow);
                _amountCustomersTextBox.Text = amountCustomers.ToString();

                // get all order prices
                ResultsetFields orderPricesFields = new ResultsetFields(2);
                orderPricesFields.DefineField(OrderDetailsFieldIndex.OrderId, 0, "OrderId");
                orderPricesFields.DefineField(OrderDetailsFieldIndex.ProductId, 1, "OrderPrice", "", AggregateFunction.Sum);
                orderPricesFields[1].ExpressionToApply = (OrderDetailsFields.Quantity * OrderDetailsFields.UnitPrice);
                IGroupByCollection groupBy = new GroupByCollection();
                groupBy.Add(orderPricesFields[0]);

                // fetch in a datatable, orderid + the order price.
                DataTable orderPrices = new DataTable();
                adapter.FetchTypedList(orderPricesFields, orderPrices, null, 0, null, true, groupBy);
                ...
                
            }
        }

adapter.GetScalar() . . adapter.FetchTypedList()

If you want to use the adapter more than once you should keep the connection open.

So either pass true in the constructor of the adapter object or set DataAccessAdapter.KeepConnectionOpen to true.

It's still strange that the connectionstring error pops up. When OpenConnection() is called, it checks if there's a connection object. If not, it creates one (and thus ends up in your routine. you can check that with a breakpoint). If there is, it opens it. However as this connection object is already created and thus has a connection string, it should work, always....

Walaa wrote:

If you want to use the adapter more than once you should keep the connection open.

So either pass true in the constructor of the adapter object or set DataAccessAdapter.KeepConnectionOpen to true.

That corrected the exception. Why is it that the KeepConnectionOpen flag does not have to be set on the DataAccessAdapter, but does on my inherited class?

tsmith wrote:

Walaa wrote:

If you want to use the adapter more than once you should keep the connection open.

So either pass true in the constructor of the adapter object or set DataAccessAdapter.KeepConnectionOpen to true.

That corrected the exception. Why is it that the KeepConnectionOpen flag does not have to be set on the DataAccessAdapter, but does on my inherited class?

If you don't set it it re-opens the connection every time and closes it every time. However, as I described, that still should work OK. It is as if the connection you create is cleared after the usage of the connection.

The getscalar goes OK, but after that the connection object doesn't contain a connection string. Could you check in the debugger if that's the case?

Otis wrote:

tsmith wrote:

Walaa wrote:

If you want to use the adapter more than once you should keep the connection open.

So either pass true in the constructor of the adapter object or set DataAccessAdapter.KeepConnectionOpen to true.

That corrected the exception. Why is it that the KeepConnectionOpen flag does not have to be set on the DataAccessAdapter, but does on my inherited class?

If you don't set it it re-opens the connection every time and closes it every time. However, as I described, that still should work OK. It is as if the connection you create is cleared after the usage of the connection.

The getscalar goes OK, but after that the connection object doesn't contain a connection string. Could you check in the debugger if that's the case?

Correct, the connection does not contain the connection string.

Hmm. then it gets cleared somewhere... Not in my code though... do you share the connection object anywhere?

Otis wrote:

Hmm. then it gets cleared somewhere... Not in my code though... do you share the connection object anywhere?

All the changes that I have made are posted above. I created the 2 classes above and change the create. I have made no other changes to the example.

I am using VS2005 if that makes a difference.

If you step through the code, could you check if the connection string is cleared right after the getscalar?

Otis wrote:

If you step through the code, could you check if the connection string is cleared right after the getscalar?

adapter.ConnectionString is still filled but the ConnectionString in the list is blank.

I have been looking at it, it appears that I could store the DataAccessAdapter in the Page Request instead of storing the connection. If I do this there is no need to override the adapter. Do you see a problem with this?

The first thing that comes to my head is the weight of the object. A connection string is much lighter to store in a page than a DataAccessAdapter object.

Here is an example of what we are trying accomplish. There may be a better way.

I use adapter as that is how it is setup now, but this could be a SqlConnection or some other object.

Each client has their own Database on SQLServer. WE used LLBL Adapter generator for the ORM. The DataAccess is not accessed directly from the WebPage, but rather thru a middle-tier object. When a PageRequest is made the needed middle-tier objects are created. These objects create/retireve an adapter from the PageContext. This is done so they all will use the same adapter. If a second Database connection (ex. Application Level Items) we create the middle-tier object using another ConnectionString. This places a new adapter in the Context. Once the Request is done all the adapters are Disposed. We do not want each middle-tier to have its own adapter.

Your description sounds good. The only way I can think of is what page context means: is that a shared resource ?

Otis wrote:

Your description sounds good. The only way I can think of is what page context means: is that a shared resource ?

This is not a shared resource. It is defined for each Post of a WebPage (HttpContext.Current).