Joining Method Data Adapter table with SQL query

[irena_czaplicka]

Hi team!

We are trying to connect Result Table obtained from the Method query (WorkflowAndEntityStatus) in Data Adapter with another table, preferably SQL table. We expect that it is possible to be done via Dashboard Data Set business rules however we are not sure if the join is possible.

Has anybody tried to join this two dataset types? 

Thanks!

[aformenti]

Hi @irena_czaplicka ,

That is definitely possible.

Indeed, you need to use a Data Set Business Rule, and then you can leverage this api to retrieve the data from the method query data adapter:

'Gets data from the Cube View MD Data Adapter:
				Dim objDataSet As DataSet = BRApi.Dashboards.Process.GetAdoDataSetForAdapter(si, False, DataAdapterName, "ResultTableName", args.CustomSubstVars)
				Dim dt As DataTable = objDataSet.Tables(0).Copy()

[Henning]

Hi, you can use Method Queries in a dashboard data set rule with this function:

Dim objDataSet As DataSet = BRApi.Database.ExecuteMethodCommand(dbConn, methodTypeId, methodQuery, resultDataTableName, customSubVars)

Also available in Snippets:

In your case e.g. like this:

'Open DB connection and execute the method command
Dim ds As DataSet = Nothing
Using dbConnApp As DbConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si)				
	'Execute the Method Command
	ds = BRAPi.Database.ExecuteMethodCommand(dbConnApp, XFCommandMethodTypeId.WorkflowAndEntityStatus,"","WF", Nothing)	
End Using	

Then you pull your other source table and do with the two data tables what you need in the dashboard data set rule.

You could also execute a SQL command, joining this all together before creating the data table, though the above is probably quicker.

(Or pull the data from a adapter data like @aformenti suggested, who beat me with his response time 🙂)

[FredLucas]

Hi @irena_czaplicka,

You can either work with SQL tables and use SQL syntax or work with in memory DataTables and use vb.net syntax (usually not as performant for join type queries)..

The WorkflowAndEntityStatus method returns a DataSet (You can easily get the DataTable you are looking for from this DataSet object), so if you want to use this method you'd have to run a SQL query to also "convert" your other SQL query into a DataTable and do the required logic in VB.net (you can find examples in microsoft / VB.net related forums). See below for some code samples:

OS function to execute a method to return the DataSet object:

Dim objDataSet As DataSet = BRApi.Database.ExecuteMethodCommand(dbConn, methodTypeId, methodQuery, resultDataTableName, customSubVars)

How to run a sql query and get a DataTable object:

Dim strSQL As New Text.StringBuilder
strSQL.AppendLine("Select * From tableName Where 1=1")

Dim dt As New DataTable()
Using dbConn As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
	dt = BRApi.Database.ExecuteSqlUsingReader(dbConn, strSQL.ToString, False)
End Using

This is one of many options you have.