Developer forum

Hi Tom

Dynamicweb cannot read DB connection from web.config.

You can however, create a DatabaseConnectionProvider - implementing Dynamicweb.IDatabaseConnectionProvider - simply return a SqlConnection instance on the CreateConnection method and a fresh instance on the CreateAdapter methode (New SqlDataAdapter()).

Something like this:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Reflection;
using System.Data.OleDb;
using System.Data.SqlClient;

public class DatabaseConnectionProvider : IDatabaseConnectionProvider
{

    public virtual IDbConnection CreateConnection(string database)
    {
        return new SqlClient.SqlConnection(Dynamicweb.Database.ConnectionString);
    }

    public virtual IDbDataAdapter CreateAdapter()
    {
        return new SqlDataAdapter();
    }

}

Hi Nicolai,

Thanks for your answer! Would that automatically override all database connections? In other words, could I leave the values in the GlobalSettings.aspx file for the connectionstring empty?

Cheers,
Tom

Yes, it will override all database connections - and remove all connection data from GlobalSetting.aspx

Nicolai

When leaving the information in the GlobalSettings.aspx file empty I get to see the Setup screen for new applications, so there definetly needs to be some sort of info in the GlobalSettings.aspx. When putting the info in the GlobalSettings file and in the DatabaseConnectionProvider I get the following error: ExecuteScalar requires an open and available Connection. The connection's current state is closed. I guess it could have something to do with a connection containing user/password information although I copied the complete connection string into the SqlConnection object. 

In the end I need an automatically changeable connection string for auto-deployment to different servers / connection strings. Any ideas on a workable solution?

Hi Tom

Yes, I can see that setup takes a look at GlobalSettings. But set Database/ConnectionString = "XXXX" and it should work. Are you sure your implementation returns an open connection? It says that there is a connection, but it is closed.... Check that or post your code so I can see it.

The automatic changeable connection string you can handle in web.config or your IDatabaseConnectionProvider

Hi Nicolai,

I've written the code below, actually almost identical to your code:

using System.Data;
using System.Data.SqlClient;
using Dynamicweb;
 
namespace Jaarbeurs.PoC.Web.Providers
{
    public class DatabaseConnectionProvider : IDatabaseConnectionProvider
    {
        public IDbConnection CreateConnection(string database)
        {
            return new SqlConnection("Data Source=xxx;Initial Catalog=xxx;Persist Security Info=True;User ID=xxx;Password=xxx;");
        }
 
        public IDbDataAdapter CreateAdapter()
        {
            return new SqlDataAdapter();
        }
    }
}

When I add code to first open the connection string I still get an error:

When debugging my application, I don't hit any breakpoint within the DatabaseConnectionProvider. My application crashes in the Application_AuthenticateRequest in the Global.asax:

protected void Application_AuthenticateRequest(object sender, EventArgs e)
        {
            Dynamicweb.Frontend.GlobalAsaxHandler.Application_AuthenticateRequest(sender, e);
        }

Any idea?

Hi Tom

You need to open the connection:

IDbConnection con = new SqlConnection("Data Source=xxx;Initial Catalog=xxx;Persist Security Info=True;User ID=xxx;Password=xxx;");
con.Open()

Please check the last part of my post Nicolai.

I did.

You wrote that you got the exception  ExecuteScalar requires an open and available Connection. The connection's current state is closed. That indicates that the connection is closed... But there is a connection. And yes, Dynamicweb creates the first connection in Authenticate_request - but if you created a provider that returns a closed connection, it will give an exception.

Is you connection open or not? Or do you get another exception?

BR Nicolai

Hi Nicolai,

This is the code which raised the error, but it doesn't get hit by the compiler:

public class DatabaseConnectionProvider : IDatabaseConnectionProvider
    {
        public IDbConnection CreateConnection(string database)
        {
            var connection = new SqlConnection("Data Source=xxx;Initial Catalog=xxx;Persist Security Info=True;User ID=xxx;Password=xxx;");
 
            connection.Open();
 
            return connection;
        }
 
        public IDbDataAdapter CreateAdapter()
        {
            return new SqlDataAdapter();
        }
    }

The error which is raised is the following: Format of the initialization string does not conform to specification starting at index 0.

Which is raised in the Application_AuthenticateRequest method.

Hi Tom,

Perhaps a special character in the connection string?

Try a literal by preceding a "@" in front of the connection string like @"Data Source=..."

Hi guys,

Well, the debugger doesn't hit my breakpoints in the DatabaseConnectionProvider. The application crashes before the provider gets called.

Cheers,
Tom

Is your assembly with this code in the Application's Bin folder? E.g. in /Bin of the web site?

Imar

Yes it is... 

The problem remains. If I put a real connectionstring in the GlobalSettings file and then build my DatabaseConnectionProvider everything works fine. When I put fake content in the GlobalSettings file and leave the DatabaseConnectionProvider in place my application crashes in the Global.asax. My Global.asax looks as follows:

using System;
using System.Web;
using Dynamicweb.Frontend;
 
namespace Jaarbeurs.Shop.Web
{
    public class Global : HttpApplication
    {
        protected void Application_Start(object sender, EventArgs e)
        {
            GlobalAsaxHandler.Application_Start(sender, e);
        }
 
        protected void Session_Start(object sender, EventArgs e)
        {
            GlobalAsaxHandler.Session_Start(sender, e);
        }
 
        protected void Application_BeginRequest(object sender, EventArgs e)
        {
            GlobalAsaxHandler.Application_BeginRequest(sender, e);
        }
 
        protected void Application_AuthenticateRequest(object sender, EventArgs e)
        {
            GlobalAsaxHandler.Application_AuthenticateRequest(sender, e);
        }
 
        protected void Application_Error(object sender, EventArgs e)
        {
            GlobalAsaxHandler.Application_Error(sender, e);
        }
 
        protected void Session_End(object sender, EventArgs e)
        {
            GlobalAsaxHandler.Session_End(sender, e);
        }
 
        protected void Application_End(object sender, EventArgs e)
        {
            GlobalAsaxHandler.Application_End(sender, e);
        }
    }
}

Anybody any info on this? Maybe something I've overlooked in the asax file?

Hm... Try adding Dynamicweb.Extensibility.AddInOrder(-1) attribute to your connection provider...

Hi all,

I've determined that there is an issue using the IDatabaseConnectionProvider interface. It is not respected in the Setup guide checks. I've created an item in our backlog to get this fixed.

- Jeppe

Hi Tom

The problem TFS#17440 IDatabaseConnectionProvider is not respected during application start has now been fixed in version 8.6.1.12

You are able to find this build in the download section:

http://developer.dynamicweb-cms.com/downloads/dynamicweb-8.aspx

Please contact Dynamicweb Support if you need any additional help regarding this.

Kind Regards
Dynamicweb Support
Kristian Kirkholt

Hi Kristian

I have been using the database connection provider interface and it works great. Now I don't have to have environment specific connection string information in GlobalSettings.aspx. BUT still we have this node: "Settings/System/MailServer/Server" in GlobalSettings.aspx which also is environment specific. Please implement an interface for that setting also. If this was done it would be possible to control the settings through transformations in Visual Studio. This would be a great help to ensure correct values in both settings (DB connection string and SMTP server) when publishing to different environments from Visual Studio.

Hi Lars

Duely noted. In a future release you will be able to split the globalsettings file to any number of files - so you can have the SMTP settings in a seperate file and take that out of the publishing process.

BR Nicolai

Hi Nicolai

Sounds great. Could you be a little bit more specific about the release time?

Before going on summer holiday...

Perfect, thanks 

Hi Nicolai

Is it possible to split Globalsettings now in DW9.1?

HI Nicolai

That's great, thanks :-)