Hello support,

I have web application in asp.net 2.0 with very large data base (both in schema and data). I am using LLBL Gen 2.5 DataAcees adapter template with SQL Server 2005.

Most of the data is fetched as collection of entity with adapter.GetCollection Method.

EntityCollection<T> entityCollectionToReturn = new EntityCollection<T>();
            using (IDataAccessAdapter dataAccessAdapter = DataAccessAdapterFactory.Create())
            {
                dataAccessAdapter.FetchEntityCollection(entityCollectionToReturn, bucket, maxNumberOfItemsToReturn, sorter, pageIndex, pageSize);
            }
            return entityCollectionToReturn;

and DataAccessAdapterFactory is

      public static IDataAccessAdapter Create()
        {
            return Create("CBase3", false);
        }
            
        public static IDataAccessAdapter Create(bool keepOpenConnection)
        {
            return Create("CBase3", keepOpenConnection);
        }

        public static IDataAccessAdapter Create(string connectionName)
        {
            return Create(connectionName, false);
        }

        public static IDataAccessAdapter Create(string connectionName, bool keepOpenConnection)
        {
            // Get configured environment from *.config.
            string currentDataEnvironment = ConfigurationManager.AppSettings["CurrentDataEnvironment"];

            // Get LLBL config section.
            LLBLGenConfigurationSection section = (LLBLGenConfigurationSection)ConfigurationManager.GetSection("LLBLGen");

            // Create connection string from selected connection configuration.
            StringBuilder connectionString = new StringBuilder();

            string server = section.DataEnvironments[currentDataEnvironment].Connections[connectionName].Server;
            string catalog = section.DataEnvironments[currentDataEnvironment].Connections[connectionName].Catalog;
            bool isWindowsAuthentication = section.DataEnvironments[currentDataEnvironment].Connections[connectionName].IsWindowsAuthentication;

            connectionString.AppendFormat("Data Source={0};Initial Catalog={1};" , server, catalog);

            if (!isWindowsAuthentication)
            {
                // SQL Server Authentication
                string user = section.DataEnvironments[currentDataEnvironment].Connections[connectionName].User;
                string password = section.DataEnvironments[currentDataEnvironment].Connections[connectionName].PassWord;
                connectionString.AppendFormat("User ID={0};Password={1};", user, password);
            }
            else
            {
                // Windows Authentication
                connectionString.Append("integrated security=SSPI;persist security info=False;packet size=4096;");
            }
            return  new DataAccessAdapter(connectionString.ToString(), keepOpenConnection, CatalogNameUsage.ForceName, catalog);

        }
    }

Now in a single page load if I fetch 20 different collection (which is requirement at my side) then DataAccessAdapter is created that many times.

**Creating DataAccessAdapter is too costly **(ANTS Profiler 0.965 sec.)

My question is how frequntly I should create a DataAccessAdapter ; and how frequently I should open-close the connection?

Can on each application request start I can create dataAccessadapter and request end I can close it (or it will get close).

How this will work if website is multiuser and on IIS6 in the same app domain different (4+)instances of same application exists.

I am using single entity fetch, collection fetch and stored procedure, entity add update, delete with transaction and without transaction.

Thanks Subodh

Now in a single page load if I fetch 20 different collection (which is requirement at my side) then DataAccessAdapter is created that many times.

That's where you shoud use one Adapter, and in the CTor pass true to keep the connection open for all your fetches.

i.e. Use one Adapter for each page load.