Skip to main content
PB3
Member
Apr 21, 2024 6:33 AM UTC
Solved

Execute sql on data table

Can I execute an sql statement against a data table created in a business rule?  I created a table to use in a data set business rule, which returns data to the data adapter.   

e.g. if I created dt1 using : Dim dt1 As DataTable = BRApi.Database.ExecuteSql(dbConnApp, strSQL.ToString, True)

Can I run a query against dt1, doing something like "select * from dt1" or in other words can I get the tablename for dt1 and then execute a query?

AndreaFAnswer
Expert
Apr 21, 2024 8:27 PM UTC

The datatable exists within the data set business rule, so yes you can run a query against it, but within its limit of existence, as for any other variable, i.e. you cannot refer to it from other business rules or outside the function or loop where it is defined. However, even within its limit of existence, I believe you cannot refer to it with the "select * from dt1" string. You need to use the appropriate function of the BRApi.Database (I don't remember which one at the moment), and pass the table and where/order by etc. as parameters.

View in thread

6 replies

AndreaFAnswer
Expert
Apr 21, 2024 8:27 PM UTC

The datatable exists within the data set business rule, so yes you can run a query against it, but within its limit of existence, as for any other variable, i.e. you cannot refer to it from other business rules or outside the function or loop where it is defined. However, even within its limit of existence, I believe you cannot refer to it with the "select * from dt1" string. You need to use the appropriate function of the BRApi.Database (I don't remember which one at the moment), and pass the table and where/order by etc. as parameters.

Employee - Sage
JackLacavaEmployee - Sage
Employee - Sage
Apr 21, 2024 9:37 PM UTC

The DataTable is held in memory. Once you have an instance of it, you can call various methods and properties to look at its data; see https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/dataset-datatable-dataview/viewing-data-in-a-datatable for some examples. Once the rule has run, the DataTable object is automatically destroyed.

If you want to retrieve the dataset produced by an adapter, in some other rule you can use BRApi.Dashboards.Process.GetAdoDataSetForAdapter, which will effectively run the adapter and give you back the result as DataSet object (for an introduction to DataSets, see https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/dataset-datatable-dataview/). The DataSet will contain the result DataTable(s).

 

 

 

.

    PB3Author
    Member
    Apr 22, 2024 5:35 AM UTC

    Great - thanks for the information - I will use what I can from this - I don't necessarily want to go too deep into customization and fancy script, but I'll evaluate the options.

    Krishna
    Expert
    Apr 22, 2024 3:53 PM UTC

    AndreaF - Yes Data table are in memory and you can manipulate by copying to another DTL or you can use the existing DTL as well. See below an example 

    									WFICTable1 = WFICTable.Copy()
											
												Dim distinctValues = WFICTable1.AsEnumerable() _
												.Where(Function(row) row.Field(Of String)("Account") <> "Difference")  _
											    .GroupBy(Function(row) New With {
											        Key.Column1 = row.Field(Of String)("Entity"),
											        Key.Column2 = row.Field(Of String)("IC")
											    }) _
											    .Select(Function(Group) New With {
											        .Column1 = Group.Key.Column1,
											        .Column2 = Group.Key.Column2,
											        .OtherColumn1 = Group.First().Field(Of String)("Account"),
											        .OtherColumn2 = Group.First().Field(Of String)("PartnerCurr"),
													.OtherColumn3 = Group.First().Field(Of Decimal)("RptCurrAmount"),
													.OtherColumn4 = Group.First().Field(Of Decimal)("PrimaryCurrAmount"),
													.OtherColumn5 = Group.First().Field(Of Decimal)("PartnerCurrAmount")
											    })

     

     

    PB3Author
    Member
    Apr 23, 2024 5:16 AM UTC

    Thanks Krishna - I'll check it out - looks like your doing something on IC mismatch reporting - that's what I'm working on with this issue too.

    Krishna
    Expert
    Apr 23, 2024 5:22 AM UTC

    PB - Yes but the concept is the same you can try with your SQL table column. Hope this helps.

    Terms & ConditionsAccessibility statement