Unir tablas de datos mediante conjuntos de datos y relaciones definidas

He configurado muchos paneles con vistas de cuadrícula rellenadas con un adaptador de datos. Este adaptador se rellena con datos de una tabla de datos relacionales. Esta es una forma sencilla de mostrar el contenido de los datos relacionales dentro de un panel. Se pueden aplicar filtros, lo que permite a los usuarios filtrar los datos mostrados en la vista de cuadrícula. Normalmente, lo he hecho mediante cuadros combinados u otro componente que ofrece a los usuarios una lista de opciones que luego modifican el valor de un parámetro. Este parámetro se utiliza dentro de la sentencia SQL que controla el adaptador de datos. Este proceso siempre ha sido sencillo.

En segundo plano, el adaptador de datos puede realizar esto de dos maneras: mediante un tipo de comando de método o un tipo de comando SQL. El tipo de comando SQL ejecutará una consulta SQL directamente en la fuente relacional, mientras que la consulta de método invocará una regla de negocio de un conjunto de datos, que generalmente consulta una fuente relacional y luego crea una tabla de datos de VB.NET que se devuelve al adaptador de datos.

Probablemente te estés diciendo: "¡Hasta ahora todo bien, lo he conseguido!". Sin embargo, ¿qué pasa si quieres combinar los resultados de dos fuentes de datos relacionadas y devolverlos mediante una única combinación de adaptador de datos y vista de cuadrícula? Probablemente estés pensando: "¡No hay problema, son dos fuentes relacionales, simplemente usaré una unión en la consulta de datos!". Bien, ¿qué pasa si el caso de negocio cambia y las dos fuentes relacionales están en entornos separados a los que no se puede acceder mediante una unión SQL? ¿Qué pasa si una es una tabla de la base de datos de la aplicación y la otra está en una base de datos externa? En este caso, no hay una forma sencilla de crear una unión SQL. O incluso, me pasó hace poco: una fuente de datos era una tabla relacional y la otra era una fuente de datos JSON devuelta desde una llamada a la API web externa. ¿Cómo las vas a unir?

Conjuntos de datos de VB.Net y relaciones definidas al rescate. Un conjunto de datos puede contener varias tablas y relaciones definidas entre ellas. Con esto, se puede consultar el conjunto de datos, basándose en la relación definida entre esas tablas, y devolver una única tabla de datos que contenga esas tablas de datos combinadas o unidas, o solo algunos campos de datos de esas tablas.

Para este ejemplo, utilizo dos tablas de la base de datos de la aplicación, una de las cuales es una tabla personalizada. Sin embargo, también se podría consultar fácilmente una tabla externa usando brapi.Database.CreateCustomExternalDbConnInfo.

La Tabla 1 devuelve las cuentas y sus descripciones de la tabla de miembros interna.

										
											SELECT MemberId, Name, Description, DimId FROM dbo.Member WHERE DimTypeId = '5' ORDER BY Name;
										
									

La Tabla 2 devuelve datos de una tabla personalizada.

Lo que quiero hacer es unir esas 2 tablas en el campo Nombre en la Tabla 1 y el campo Cuenta en la Tabla 2 y devolver solo Nombre, Descripción y Usuarios a una Vista de Cuadrícula (a través de un Adaptador de Datos).

Resultado deseado:

Entonces, ¿cómo hago esto?

1. Configure un adaptador de datos de tipo de método que llame a una regla de negocio del conjunto de datos del panel.

2. Cree la regla de negocio del conjunto de datos del panel y, en esa regla de negocio, cree sus 2 tablas de datos de origen.

										
'First Data Table
Dim dtMembers As New DataTable
Using dbConn As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
    Dim sql As New Text.StringBuilder
    sql.AppendLine($"SELECT MemberId, Name, Description, DimId FROM dbo.Member WHERE DimTypeId = '5' ORDER BY Name;")
    dtMembers = BRApi.Database.ExecuteSql(dbConn,sql.ToString,False)
Eng Using

'Second Tata Table
Dim dtMemberNames As New DataTable
Using dbConn As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
    Dim sql As New Text.StringBuilder
    sql.AppendLine($"SELECT Account, Users FROM dbo.XFC_Accounts_Users;")
    dtMemberNames = BRApi.Database.ExecuteSql(dbConn,sql.ToString,False)
Eng Using
										
									

3. Cree la tableta de datos que contendrá los resultados unidos y será el retorno.

										
Dim dtResult As New DataTable
dtResult.Columns.Add("Name")
dtResult.Columns.Add("Description")
dtResult.Columns.Add(""Users)
										
									

4. Cree el conjunto de datos y agréguele las tablas de datos de origen.

	'Data Set that holds the data tables
Dim dataSetMerged As New DataSet
dataSetMerged.Tables.Add(dtMembers)
dataSetMerged.Tables.Add(dtMemberNames)
										
									

5. Ahora define la relación entre las dos tablas. Estos son los campos de cada tabla de datos que usarás para unir los datos; piensa en ello como una unión SQL.

										
Dim relation As New DataRelation("AccountNameRelation"),dataSetMerged.Tables(0).Columns("Name"),dataSetMerged.Tables(1).Columns("Account"),False)
dataSetMerged.Relations.Add(relation)
										
									

6. En este punto, tiene un conjunto de datos que contiene dos tablas de datos y una relación definida. El siguiente paso es recorrer las filas de una tabla de datos, obteniendo las filas relacionadas de la segunda tabla de datos (según la relación definida) y agregando los campos deseados como una fila a la tabla de datos de retorno.

										
' Loop through the rows of the on data table and get related "parent" rows based on the relationship
For Each childRow As DataRow In dtMemberNames.Rows
    Dim filterValue As String = childRow("Users").ToString()
    Dim parentRows As DataRow() = childRow.GetParentRows(relation)
    
    'Get the fields from each table that you want and add them to the return data table
    For Each parentRow As DataRow In parentRows
        dtResults.Rows.Add(parentRow("Name"),parentRow("Description"), childRow("Users"))
    Next
Next

Return dtResult
										
									

El código en su totalidad.

										
											
Public Function MergeDataTables(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardDataSetArgs) As DataTable

Try

'First Data Table
Dim dtMembers As New DataTable
Using dbConn As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
    Dim sql As New Text.StringBuilder
    sql.AppendLine($"SELECT MemberId, Name, Description, DimId FROM dbo.Member WHERE DimTypeId = '5' ORDER BY Name;")
    dtMembers = BRApi.Database.ExecuteSql(dbConn,sql.ToString,False)
Eng Using

'Second Tata Table
Dim dtMemberNames As New DataTable
Using dbConn As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
    Dim sql As New Text.StringBuilder
    sql.AppendLine($"SELECT Account, Users FROM dbo.XFC_Accounts_Users;")
    dtMemberNames = BRApi.Database.ExecuteSql(dbConn,sql.ToString,False)
Eng Using

Dim dtResult As New DataTable
dtResult.Columns.Add("Name")
dtResult.Columns.Add("Description")
dtResult.Columns.Add(""Users)

'Data Set that holds the data tables
Dim dataSetMerged As New DataSet
dataSetMerged.Tables.Add(dtMembers)
dataSetMerged.Tables.Add(dtMemberNames)

Dim relation As New DataRelation("AccountNameRelation"),dataSetMerged.Tables(0).Columns("Name"),dataSetMerged.Tables(1).Columns("Account"),False)
dataSetMerged.Relations.Add(relation)


' Loop through the rows of the on data table and get related "parent" rows based on the relationship
For Each childRow As DataRow In dtMemberNames.Rows
    Dim filterValue As String = childRow("Users").ToString()
    Dim parentRows As DataRow() = childRow.GetParentRows(relation)
    
    'Get the fields from each table that you want and add them to the return data table
    For Each parentRow As DataRow In parentRows
        dtResults.Rows.Add(parentRow("Name"),parentRow("Description"), childRow("Users"))
    Next
Next

Return dtResult

Catch ex As dtResult
    Throw ErrorHandler.LogWrite(si, New XFException(si,ex))
End Try
End Function
										
									

El resultado, al ejecutarse a través de un adaptador de datos, muestra 2 campos de la Tabla 1 y 1 campo de la Tabla 2.

Esta potente función se puede utilizar cuando se necesitan unir fuentes de datos relacionadas y no se puede usar una unión SQL.