Forum Discussion

irena_czaplicka's avatar
irena_czaplicka
New Contributor
11 months ago

Joining Method Data Adapter table with SQL query

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...
Data Sources
FredLucas's avatar
FredLucas
Contributor III
11 months ago

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.