Retrieve App Names from Development Environment via Business Rule

Hi Avatar-Roku​ 

To check which Database Table you need, I would recommend checking System->Database in OneStream, first. 

In your example dbo.App is not a real table that actually exists in the Framework (FW) Database (be careful with using CoPilot for OneStream specific code).

For reference, this is the Framework database and we can reference tables and their contents from here.

If we take your OSD example, as you have this installed, there is a table here called [dbo].[XFW_OSD_DatabaseSizes], but if we take a look at this table, we have multiple instances of the same Database Name e.g. AppBuildStudent (in my example below)

Therefore it is safer to write a DISTINCT SELECT statement to this table to retrieve the unique DatabaseName(s) for your environment (the FW database is environment-wide)

There are snippets available on how to create database connections to App, Framework and external databases, correctly

So if we take your original example and refactor based on the information above, we get something like this; which gives you the correct result (and does not error)

' Declare StringBuilder
Dim sb As New Text.StringBuilder()
' Declare SQL
Dim sql As String = "SELECT DISTINCT [DatabaseName] FROM [dbo].[XFW_OSD_DatabaseSizes] ORDER BY [DatabaseName]"				
'Open connection to Framework database
Using dbConnFW As DBConnInfo = BRApi.Database.CreateFrameworkDbConnInfo(si)
	Using dt As DataTable = BRAPi.Database.ExecuteSql(dbConnFW, sql, True)	
		If Not dt Is Nothing Then
            For Each dr As DataRow In dt.Rows
                'Process rows
                sb.AppendLine(dr("DatabaseName"))
            Next
		Else
			sb.AppendLine("No applications found.")
        End If	
	    sb.AppendLine(String.Empty)
	    sb.AppendLine($"Total Applications {dt.Rows.Count}")
		Throw New XFUserMsgException(si, String.Empty, String.Empty, sb.ToString())
	End Using
End Using	

Hope this helps

Sam