Actually, I don't recommend storing connection strings somewhere other than with all the other connection strings - in web.config. I take the view that for maintenance purposes, it is much easier to manage similar resources in one common location, rather than search all over web.config and then search through modulesettings table, and then tabmodulesettings table and then maybe a custom module table.
So my approach would be to create a subclass of the DotNetNuke SqlDataProvider class and then to create a simple factory class that knew how to create my custom SqlDataProvider.
The factory class would essentially be a clone of the standard Shared methods in the regular DotNetNuke DataProvider class without all of the abstract methods. This would give me the caching and performance benefits of using statics/shared method along with the caching afforded by the createobject method:
Public Class MyDataProviderFactory
#Region "Shared/Static Methods"
' singleton reference to the instantiated object
Private Shared objProvider As DataProvider = Nothing
' constructor
Shared Sub New()
CreateProvider()
End Sub
' dynamically create provider
Private Shared Sub CreateProvider()
objProvider = CType(Framework.Reflection.CreateObject("MyNamespace.MySqldataprovider, MySqlDataProvider", "MySqlDataProviderCacheKey"), DataProvider)
End Sub
' return the provider
Public Shared Shadows Function Instance() As DataProvider
Return objProvider
End Function
#End Region
End Class
At this point I could now create my own custom SqlDataProvider class that inherited from the standard DNN SqlDataProvider class and overrode the connection string property.
ImportsDotNetNuke.Common.Utilities
Imports DotNetNuke.Data
MyNamespace
Public Class MySqlDataProvider
Inherits SqlDataProvider
Private _connectionString As String
Public Sub New()
MyBase.New()
_connectionString = Config.GetConnectionString("NameOfMyConnectionString")
End Sub
Public Overloads ReadOnly Property ConnectionString() As String
Get
Return _connectionString
End Get
End Property
End Class
End Namespace
So the reason for doing this is now in my code I can use a similar method for all of the my data access:
To execute SQL against the default DNN database:
DataProvider.Instance().ExecuteSQL("Select * from MyTableInDNN")
To execute SQL against the alternate database defined by my custom connection string:
MyDataProviderFactory.Instance().ExecuteSQL("Select * from MyTableInAccountingSystem")
Since most systems will have several developers working on them over their lifetime, it is always best to stick with known development patterns whenever possible. In this case a new developer looking at your code would not be surprised by some new mechanism for accessing the secondary database. It would be using the same structure as the standard DNN methods and thus should greatly ease maintenance burdens.