Thanks a lot for tis code. I pretty much used as is, apart from one issue - This was the fact that the the database was hit too many times. for example for a matrix of substantial cells the database will be hit everytime a cell needs to be formatted.
To overcome that I used a a shared list of reportStyles that could be used between instances of the datadrivenstyle library. See below (following 2 lines are key - Private Shared reportStyles As List(Of ReportStyle) & If reportStyles Is Nothing Then):
Imports System.Data
Imports System.Data.SqlClient
Imports System.Security
Public Class DataDrivenStyleLibrary
'Declare shared item that can be shared between instances
Private Shared reportStyles As List(Of ReportStyle)
Public Shared Function dbStyle(ByVal ReportSection As String, ByVal StyleType As String) As String
Dim sStyle As String
sStyle = ""
'Check to see if reportstyles list exists already created and if it does not then create it
If reportStyles Is Nothing Then
reportStyles = New List(Of ReportStyle)
' Set up the command and connection objects
Dim MyConnection As SqlConnection
MyConnection = New SqlConnection("Data Source=IMGSERVER17; Initial Catalog=ReportServer; Integrated Security=SSPI;")
Dim cmd As New SqlCommand
cmd.Connection = MyConnection
cmd.Connection.Open()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "pr_GetStyle"
' Read the data, and populate report style list
Dim ddlValues As SqlDataReader
ddlValues = cmd.ExecuteReader()
While ddlValues.Read
Dim rStyle = New ReportStyle()
rStyle.ReportSection = ddlValues.GetString(0)
rStyle.StyleType = ddlValues.GetString(1)
rStyle.StyleDefinition = ddlValues.GetString(2)
reportStyles.Add(rStyle)
End While
'Close any open connections
cmd.Connection.Close()
' return the selected value to the calling proceure
End If
'Loop up particular style from object
For Each rs In reportStyles
If rs.ReportSection = ReportSection And rs.StyleType = StyleType Then
sStyle = rs.StyleDefinition
Exit For
End If
Next
Return sStyle
End Function
End Class
Friend Class ReportStyle
Private sType As String
Private rSection As String
Private sDefinition As String
Public Property StyleType() As String
Get
Return sType
End Get
Set(ByVal value As String)
sType = value
End Set
End Property
Public Property ReportSection() As String
Get
Return rSection
End Get
Set(ByVal value As String)
rSection = value
End Set
End Property
Public Property StyleDefinition() As String
Get
Return sDefinition
End Get
Set(ByVal value As String)
sDefinition = value
End Set
End Property
End Class