Dear all,
this is my small contribute to the community.
The latest version of DNN comes with a Reports module in which you can
create a custom query in SQL against the DNN Database. For my company
needs I had to connect to Oracle and to an extra SQL Server. Following the
solution I've found.
How to Create ReportGrid module
STEP 1:
Create a new module using the Item template installed by the StarterKit. Rename
the folders as suggested by the instructions and modify the Namespace name all
around the new files... (take care there are a lot of references to
YourCompany_)
STEP 2:
Delete the followings files:
App_Code/ModuleName/DataProvider.vb
App_Code/ModuleName/SqlDataProvider.vb
DesktopModules/ModuleName/EditModuleName.ascx
DesktopModules/ModuleName/EditModuleName.ascx.vb
DesktopModules/ModuleName/App_LocalResources/EditModuleName.ascx.resx
DesktopModules/ModuleName/01.00.00.SqlDataProvider
DesktopModules/ModuleName/Uninstall.SqlDataProvider
STEP 3:
Edit ModuleName.dnn and remove the list of deleted files.....
STEP 4:
At this point we have an empty module with the same number of files of the
Reports module. Copy all the source code from the Reports module, file by file.
Fix the references to ReportsInfo and ReportsController with your class name
(eg. in my ReportgridInfo and ReportgridController)
Before proceding be sure to compile the module correctly. (It takes about 15
minutes to fix all the errors....)
STEP 5:
Add a property to the Info Class (ModuleNameInfo.vb) called ConnectionString of
type String.
STEP 6:
Install the module using the Host > Module definitions interface
STEP 7:
Apply the followings:
Settings.ascx
ADD, BEFORE THE QUERY ROW, THIS ROW TO THE TABLE
|
<tr>
<td class="SubHead" style="width: 125px; vertical-align:
top">
<dnn:Label ID="lblDatabase" runat="server"
ControlName="txtDatabase" Suffix=":" />
</td>
<td style="width: 291px; vertical-align: top">
<asp:DropDownList ID="lstDatabase" runat="server"
Width="100%">
</asp:DropDownList>
</td>
</tr>
|
Settings.ascx.vb
ADD TO Sub LoadSettings() AFTER CheckPermissions()
|
Dim cs As ConnectionStringSettingsCollection =
ConfigurationManager.ConnectionStrings
lstDatabase.DataSource = cs
lstDatabase.DataTextField = "Name"
lstDatabase.DataBind()
|
ADD TO Sub LoadSettings() AFTER txtQuery.Text = objReport.Query
|
lstDatabase.SelectedValue = objReport.ConnectionString
|
ADD TO UpdateSettings() AFTERobjReport.CreatedOn = DateTime.Now
|
objReport.ConnectionString = lstDatabase.SelectedValue.ToString
|
If you like to test the settings, a dropdown list loads now the
connectionstrings from the web.config file.
STEP 8
ModuleNameController.vb
REPLACE Function ExecuteReport() with this (in red you can see the modified part)
|
Public Shared Function ExecuteReport(ByVal objReport As
ReportGridInfo) As DataTable
If objReport Is Nothing Then Throw New
ArgumentNullException("objReport")
If String.IsNullOrEmpty(objReport.Query) Then
Return Nothing ' If there's no query, there's no report
Else
Dim strCacheKey As String = String.Concat(CACHEKEY_Reports,
objReport.ModuleID)
Dim objCache As Object = DataCache.GetCache(strCacheKey)
Dim dataTable As DataTable = Nothing
If objCache IsNot Nothing AndAlso TypeOf objCache Is DataTable Then
dataTable = DirectCast(objCache, DataTable)
Else
If objReport.ConnectionString = "SiteSqlServer" Then
Dim dr As IDataReader =
DotNetNuke.Data.DataProvider.Instance().ExecuteSQL(objReport.Query)
If dr Is Nothing Then Return Nothing
dataTable = New DataTable("QueryResults")
dataTable.Load(dr)
Else
Dim dr As IDataReader = ExecuteSQL(objReport)
If dr Is Nothing Then Return Nothing
dataTable = New DataTable("QueryResults")
dataTable.Load(dr)
End If
DataCache.SetCache(strCacheKey, dataTable)
End If
Return dataTable
End If
End Function
|
Add this NEW function to the class
|
Public Shared Function ExecuteSQL(ByVal objReport As ReportGridInfo)
As System.Data.IDataReader
Try
Dim settings As ConnectionStringSettings =
ConfigurationManager.ConnectionStrings(objReport.ConnectionString)
Dim provider As DbProviderFactory =
DbProviderFactories.GetFactory(settings.ProviderName)
Dim connection As DbConnection = provider.CreateConnection()
Dim command As DbCommand = provider.CreateCommand()
connection.ConnectionString = settings.ConnectionString
command.Connection = connection
command.CommandText = objReport.Query
connection.Open()
Return command.ExecuteReader(CommandBehavior.CloseConnection)
Catch ex As Exception
Return Nothing
End Try
End Function
|
READY TO GO!
Compile everything and it should be finished. Now the reports module accepts any
connection string takin' them from web.config file.
Please post suggestions and improvements. I'm new to DNN (not to .NET) and
probably this will look horrible to some of you. Send me your critics and I'll
try do my best to fix my work!!!
KNOW PROBLEMS
I found that the Paging and the Sorting features of the GridView have
problems with the Cache time setting (section Page). Set it to 0 to
avoid problems....
Best Regards
Danilo