Okay I think I almost have this not sure if its optimal or anything and I need help on one little part of it
Basically I moved the Dataprovider and sqldataprovider methods dealing with the DAL+ into its own provider class seems to work if I set it up in the web.config -
Now the question is - how do I pass some sort of sting to it to tell it what connection string to grab? heres my code so far for anyone who might want to wade through it - I included a few comments here and there
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.ApplicationBlocks.Data
Imports System.IO
Imports System.Web
Imports DotNetNuke
Imports DotNetNuke.Common.Utilities
Imports DotNetNuke.Framework.Providers
Imports DotNetNuke.Entities.Users
Namespace Codegalaxy.DNN.Data
Public Class SqlDataProvider
Inherits DataProvider
#Region "Private Members"
Private Const ProviderType As String = "data2" '- new Provider section in DNN Config in web.config - should I move it seperate?
'how to make this cont dynamic ?
Private _providerConfiguration As ProviderConfiguration = ProviderConfiguration.GetProviderConfiguration(ProviderType)
Private _connectionString As String
Private _providerPath As String
Private _objectQualifier As String
Private _databaseOwner As String
'Private _upgradeConnectionString As String
#End Region
#Region "Constructors"
Public Sub New()
' Read the configuration specific information for this provider
Dim objProvider As Provider = CType(_providerConfiguration.Providers(_providerConfiguration.DefaultProvider), Provider)
' Read the attributes for this provider
'Get Connection string from web.config
_connectionString = Config.GetConnectionString()
If _connectionString = "" Then
' Use connection string specified in provider
_connectionString = objProvider.Attributes("connectionString")
End If
_providerPath = objProvider.Attributes("providerPath")
_objectQualifier = objProvider.Attributes("objectQualifier")
If _objectQualifier <> "" And _objectQualifier.EndsWith("_") = False Then
_objectQualifier += "_"
End If
_databaseOwner = objProvider.Attributes("databaseOwner")
If _databaseOwner <> "" And _databaseOwner.EndsWith(".") = False Then
_databaseOwner += "."
End If
'took out upgrade string - no need in this case?
'If Convert.ToString(objProvider.Attributes("upgradeConnectionString")) <> "" Then
' _upgradeConnectionString = objProvider.Attributes("upgradeConnectionString")
'Else
' _upgradeConnectionString = _connectionString
'End If
End Sub
#End Region
#Region "Public Properties"
Public ReadOnly Property ConnectionString() As String
Get
Return _connectionString
End Get
End Property
Public ReadOnly Property ProviderPath() As String
Get
Return _providerPath
End Get
End Property
Public ReadOnly Property ObjectQualifier() As String
Get
Return _objectQualifier
End Get
End Property
Public ReadOnly Property DatabaseOwner() As String
Get
Return _databaseOwner
End Get
End Property
'Public ReadOnly Property UpgradeConnectionString() As String
' Get
' Return _upgradeConnectionString
' End Get
'End Property
#End Region
'not sure here but dont need tem right now
'#Region "Private Methods"
' Private Sub ExecuteADOScript(ByVal trans As SqlTransaction, ByVal SQL As String)
' 'Get the connection
' Dim connection As SqlConnection = trans.Connection
' 'Create a new command (with no timeout)
' Dim command As New SqlCommand(SQL, trans.Connection)
' command.Transaction = trans
' command.CommandTimeout = 0
' command.ExecuteNonQuery()
' End Sub
' Private Sub ExecuteADOScript(ByVal SQL As String)
' 'Create a new connection
' Dim connection As New SqlConnection(UpgradeConnectionString)
' 'Create a new command (with no timeout)
' Dim command As New SqlCommand(SQL, connection)
' command.CommandTimeout = 0
' connection.Open()
' command.ExecuteNonQuery()
' connection.Close()
' End Sub
'#End Region
#Region "Generic Methods"
'Generic Methods
'===============
'
''' -----------------------------------------------------------------------------
''' <summary>
''' ExecuteReader executes a stored procedure or "dynamic sql" statement, against
''' the database
''' </summary>
''' <remarks>
''' </remarks>
''' <param name="ProcedureName">The name of the Stored Procedure to Execute</param>
''' <param name="commandParameters">An array of parameters to pass to the Database</param>
''' <history>
''' [cnurse] 12/11/2005 created
''' </history>
''' -----------------------------------------------------------------------------
Public Overrides Sub ExecuteNonQuery(ByVal ProcedureName As String, ByVal ParamArray commandParameters() As Object)
SqlHelper.ExecuteNonQuery(ConnectionString, DatabaseOwner & ObjectQualifier & ProcedureName, commandParameters)
End Sub
Public Overrides Function ExecuteReader(ByVal ProcedureName As String, ByVal ParamArray commandParameters() As Object) As IDataReader
Return SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner & ObjectQualifier & ProcedureName, commandParameters)
End Function
Public Overrides Function ExecuteScalar(ByVal ProcedureName As String, ByVal ParamArray commandParameters() As Object) As Object
Return SqlHelper.ExecuteScalar(ConnectionString, DatabaseOwner & ObjectQualifier & ProcedureName, commandParameters)
End Function
Public Overrides Function ExecuteSQL(ByVal SQL As String) As IDataReader
Return ExecuteSQL(SQL, Nothing)
End Function
Public Overrides Function ExecuteSQL(ByVal SQL As String, ByVal ParamArray commandParameters() As IDataParameter) As IDataReader
Dim sqlCommandParameters() As SqlParameter = Nothing
If Not commandParameters Is Nothing Then
sqlCommandParameters = New SqlParameter(commandParameters.Length - 1) {}
For intIndex As Integer = 0 To commandParameters.Length - 1
sqlCommandParameters(intIndex) = CType(commandParameters(intIndex), SqlParameter)
Next
End If
SQL = SQL.Replace("{databaseOwner}", DatabaseOwner)
SQL = SQL.Replace("{objectQualifier}", ObjectQualifier)
Try
Return CType(SqlHelper.ExecuteReader(ConnectionString, CommandType.Text, SQL, sqlCommandParameters), IDataReader)
Catch
' error in SQL query
Return Nothing
End Try
End Function
#End Region
' general
Public Overrides Function GetNull(ByVal Field As Object) As Object
Return Null.GetNull(Field, DBNull.Value)
End Function
End Class
Public MustInherit Class DataProvider
#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("data"), DataProvider)
End Sub
' return the provider
Public Shared Shadows Function Instance() As DataProvider
Return objProvider
End Function
#End Region
#Region "Abstract Methods"
'Generic Methods
'===============
Public MustOverride Sub ExecuteNonQuery(ByVal ProcedureName As String, ByVal ParamArray commandParameters() As Object)
Public MustOverride Function ExecuteReader(ByVal ProcedureName As String, ByVal ParamArray commandParameters() As Object) As IDataReader
Public MustOverride Function ExecuteScalar(ByVal ProcedureName As String, ByVal ParamArray commandParameters() As Object) As Object
Public MustOverride Function ExecuteSQL(ByVal SQL As String) As IDataReader
Public MustOverride Function ExecuteSQL(ByVal SQL As String, ByVal ParamArray commandParameters() As IDataParameter) As IDataReader
' general
Public MustOverride Function GetNull(ByVal Field As Object) As Object
#End Region
End Class
End Namespace