Developer forum

Hi Mikkel,

In DW9, your configuration settings will be automatically read from any .config file in your files directory.

For instance. you can create a dbConnection.config (or whatever) with the contents:

<?xml version="1.0"?>
<Globalsettings>

  <System>

    <Database>
      <Password>TestUser</Password>
      <Type>ms_sqlserver</Type>
      <UserName>TestUser</UserName>
      <Database>DW9</Database>
      <SQLServer>localhost</SQLServer>
     
    </Database>
  </System>
  
</Globalsettings>

And remove those nodes from your globalsettings.aspx file, and it will work exactly the same - as long as the Xpath to pull the data is the same, it doesn't matter which config file the settings are located in.

Hope this helps.

/Jonas

Hi Jonas,

If I understand it correctly, we will no be able to reuse the web.confg for this, as the syntax in said file is different from the one you mentioned.

Its new to me we can create a databaseConnection.config file as you mentioned, however we would obviously like to keep these settings within the webconfig which is being transformed on publish. As far as I understand transforms are only applied to the web.config - if it's possible to do transforms on any given .config file it would obviously be do-able to target specific environments.

Regarding the databaseConnection.config file (or whatever name you give it) - is there any documentation on this? - I haven't found anything on the DW docs so far regarding this.

Thanks for your input, much appreciated :)

BR

Mikkel

Hi Mikkel,

I haven't worked with the publish functionality that you refer to. However, it sounds like it should be possible to simply have multiple files for the needed environments, and then set up the publisher to include the right one for a given environment?

That was the thought, when we implemented the functionality :-)

I don't think this is documented yet - I've sent it to our technical writer - hopefully it will be added to our documentation soon.

/Jonas

Hi Jonas,

Yeah, I just talked to a colleague of mine and he mentioned he used a package like: https://marketplace.visualstudio.com/items?itemName=WillBuikMSFT.SlowCheetah-XMLTransforms in order to transform .config files based on your publish profile settings - so I guess its a way to go. However, personally, I'm more a fan of keeping environmental specific configurations inside the web.config itself.

Anyways, thanks! - i'll look into it and post my results :-)

BR

Mikkel

Hi Nicolai,

Well thats perfect! - Just what I needed. I'm pretty sure I've heard of the provider, I must have, but never thought og utilizing it in that way!

Regarding the code in Global.asax.cs we're creating a SqlConnectionStringBuilder object in which we parses the connectionstring read from the webconfig.

Thereaefter we load the GlobalSettings file and overrides the DB properties with the ones set in the webconfig.. See attached image.

I'd definitly implement the provider for our startup kit - that would make it possible for us, if needed, to have multiple configurations in the web.config file depending on our target environment.

EDIT: If you have an example of the provider that reads the db connection from the web.config, you're more than welcome to share it :-)

BR

Mikkel

Hi Mikkel

Here it is - you have the reading from web.config in your screen dump

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Data.SqlClient;
using Dynamicweb.Data;

public class DatabaseConnectionProvider : IDatabaseConnectionProvider
{

    public virtual IDbConnection CreateConnection()
    {
        string databaseType = SystemConfiguration.Instance.GetValue("/Globalsettings/System/Database/Type");
        IDbConnection tmpDbConnection = new SqlConnection(Database.ConnectionString);

        try {
            tmpDbConnection.Open();
        } catch {
            tmpDbConnection.Dispose();

            throw;
        }

        return tmpDbConnection;
    }

    [CodeAnalysis.SuppressMessage("Microsoft.Reliability", "CA2000:Dispose objects before losing scope", Justification = "Because IDbDataAdapter is not disposable.")]
    public virtual IDbDataAdapter CreateAdapter()
    {
        return new SqlDataAdapter();
    }

}

Hi Nicolai,

Hmm it seems to work okay on most pages, also in the backend. However, on some pages I get the error below. Also, in the backend, under Repositories, I can't see any indices. Seems that indexing still relies on global settings for accessing the database? We're running 9.2.10 in this case.

System.Data.SqlClient.SqlException (0x80131904): Login failed for user ''. 
ved System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling) 
ved System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) 
ved System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) 
ved System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) 
ved System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) 
ved System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) 
ved System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) 
ved System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) 
ved System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) 
ved System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) 
ved System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) 
ved System.Data.SqlClient.SqlConnection.Open() 
ved Dynamicweb.Ecommerce.Indexing.ProductIndexSchemaExtender.GetFields() 
ved Dynamicweb.Indexing.Schemas.ExtensionFieldDefinition.GetFields() 
ved Dynamicweb.Indexing.IndexHelper.ConvertToIndex(XElement obj) 
ved Dynamicweb.Indexing.IndexService.LoadIndex(String name) 
ved Dynamicweb.Indexing.Queries.IndexQueryProvider.Query(IQuery query, QuerySettings settings) 
ved Dynamicweb.Ecommerce.Frontend.Frontend.GetProductsFromIndexQuery() ClientConnectionId:f05dd8fb-86b4-48d4-af61-d4ffbd55a2fa Error Number:18456,State:1,Class:14

I have removed database information from globalsettings file, as it should not be needed there if we are to trust a web transform using the class you provided. With small changes, we ended up with:

    public class DatabaseConnectionProvider : IDatabaseConnectionProvider
    {

        public virtual IDbConnection CreateConnection()
        {
            var connectionString = ConfigurationManager.ConnectionStrings["ConnectionStringName"].ConnectionString;
            
            IDbConnection tmpDbConnection = new SqlConnection(connectionString);

            try
            {
                tmpDbConnection.Open();
            }
            catch
            {
                tmpDbConnection.Dispose();

                throw;
            }

            return tmpDbConnection;
        }

        [SuppressMessage("Microsoft.Reliability", "CA2000:Dispose objects before losing scope", Justification = "Because IDbDataAdapter is not disposable.")]
        public virtual IDbDataAdapter CreateAdapter()
        {
            return new SqlDataAdapter();
        }
    }

Hi Mikkel

The exception you posted, is for something running in a seperate thread without http context. It can be our task manager that does not take your connection provider into account. It can also be that since this is a thread, it cannot read from web.config because of missing httpcontext - maybe.

I've added a task for us to look into if it is issue #1. You can test if it is 2. Until then, you can add the connection string to a Dynamicweb config file...

BR Nicolai

Hi Nicolai,

Any updates on this one?

Best Regards
Rasmus Westfall Andersen