SQL: Fundamentals for master detail data view with .NET

Article
1/17/2024

Introduction

Writing data-centric applications when designed properly rarely use a single database table to store application data.

For example, you would not place orders in a customer table but instead create a table for customers, another table for orders and so on. This article provides details on creating a simple three table solution from a SQL-Server database using SqlClient managed data provider.

In the screenshot above, each numeric value above a ListBox is primary keys only visible in debug mode.

Steps

1. Design schema

Design the schema which encompasses creating each table with auto-incrementing primary keys so that each table can be related. In this sample the master table is a table named product while the detail table is color. Color becomes a master to a size table which makes size table the detail table for colors.

2. Populate tables

Populate the three tables with data.

3. Create SQL Statements

Create SQL select statements with where conditions to ensure data comes back as expected. Developers tend to skip this step and instead will test the relationships between the tables in code. Testing in code can be done but not in the actual project but instead in unit test methods. Once satisfied the relationships are sound using SQL statements or unit test move on to the next step.

Caveat, the best place to write/test SQL statements is in SQL-Server Management Studio. If you don’t have SSMS installed then in your project add a new text file, the base name does not matter but change the file extension from .txt to .sql. Now you can write SQL statements and execute them. If using Visual Studio 2017 a add-in is installed called SQL-Prompt which will provide Intellisense when writing your SQL. Start off by connecting to your SQL database via the connect button while in the .sql file. Once connected add a using statement for the database. SQL-Prompt will kick in and provide assistance with field names available.

4. Create data class

Create a data class, in this class, a method will be needed to read the three tables. The following is a base to work from.

Imports System.Data.SqlClient
Imports System.Windows.Forms
 
Public Class  Operations
 ''' <summary>
 ''' Make sure to change "Data Source" to your server
 ''' </summary>
 Private ConnectionString As String  = "Data Source=YOUR-PC;"  & 
 "Initial Catalog=MasterRelationsDemo;" & 
 "Integrated Security=True"
   Public Property  HasErrors As  Boolean
 Public Property  ExceptionMessage As  String
 Public Property  bsMasterProduct As  New BindingSource
 Public Property  bsDetailsColor As  New BindingSource
 Public Property  bsDetailsSize As  New BindingSource
 
 Public Sub  LoadData()
 
 Dim ds As New  DataSet
 
 Dim productsSelectStatement As String  = 
 <SQL>
 SELECT 
 id,
 Name, 
 Description, '$' + CONVERT(NVARCHAR, [Retail]) AS RetailFormatted, 
 Retail 
 FROM dbo.Product 
 ORDER BY NAME
 </SQL>.Value
 
 Dim colorsSelectStatement as String =
 <SQL>
 SELECT 
 id,
 ProductId,
 Color 
 FROM dbo.ProductColor
 </SQL>.Value
 
 Dim sizeSelectStatement as String = 
 <SQL>
 SELECT 
 id,
 Size,
 ProductColorId,
 DisplayOrder  
 FROM dbo.ProductSize
 </SQL>.Value
 
 
 Using cn As  New SqlConnection With {.ConnectionString = ConnectionString}
 
 Dim da As New  SqlDataAdapter(productsSelectStatement, cn)
 
 Try
 
 da.Fill(ds, "Product")
 
 da = New  SqlDataAdapter(colorsSelectStatement, cn)
 da.Fill(ds, "ProductColor")
 '
 ' Using a language extension method to create a relationship between Product and Color where
 ' parameter1 and parameter2 are concatenated to form the relationship name.
 '
 ds.SetRelation("Product", "ProductColor", "Id", "ProductId")
 
 da = New  SqlDataAdapter(sizeSelectStatement, cn)
 da.Fill(ds, "ProductSize")
 
 '
 ' Using a language extension method to create a relationship between Color and size where
 ' parameter1 and parameter2 are concatenated to form the relationship name.
 '
 ds.SetRelation("ProductColor", "ProductSize", "Id", "ProductColorId")
 
 '
 ' Configure our BindingSource components to work in the user interface so
 ' there is no need to (like many developers do) to filter data with events
 ' for when displayed in three ListBox or ComboBox controls
 '
 bsMasterProduct.DataSource = ds
 bsMasterProduct.DataMember = ds.Tables(0).TableName
 
 bsDetailsColor.DataSource = bsMasterProduct
 bsDetailsColor.DataMember = ds.Relations(0).RelationName
 
 bsDetailsSize.DataSource = bsDetailsColor
 bsDetailsSize.DataMember = ds.Relations(1).RelationName
 
 '
 ' Here we show the data in a raw format to the IDE console.
 '
 If Debugger.IsAttached Then
 For Each  row As  DataRow In  ds.Tables("ProductColor").Rows
 Console.WriteLine($"{row.Field(Of Integer)("ProductId")}")
 For Each  cRow As  DataRow In  row.GetChildRows("ProductColorProductSize")
 Console.WriteLine("  " & String.Join(",", cRow.ItemArray))
 Next
 Next
 End If
 
 Catch ex As Exception
 HasErrors = True
 ExceptionMessage = ex.Message
 End Try
 End Using
 End Sub
 ''' <summary>
 ''' This is a simple shell, in a real application the data schema 
 ''' would be more robust to include a full shopping cart that 
 ''' when we add an item to the shopping cart there first is a check 
 ''' to see if the item selected is in stock and if so mark it on hold
 ''' for x amount of time so if someone else wants to order it and 
 ''' there was one in stock it would appear as out of stock.
 ''' 
 ''' I go just far enough here for the topic at hand. From having 
 ''' the primary key we can then use SQL to look at the data which 
 ''' as mentioned above would have a robust shopping cart including 
 ''' quanities and more.
 ''' </summary>
 ''' <param name="pProductRow"></param>
 ''' <param name="pColorRow"></param>
 ''' <param name="pSizeRow"></param>
 Public Sub  AddToOrder(ByVal  pProductRow As  DataRow, ByVal  pColorRow As  DataRow, ByVal  pSizeRow As  DataRow)
 Dim productId As Integer  = pProductRow.Field(Of Integer)("id")
 Dim colorId As Integer  = pColorRow.Field(Of Integer)("id")
 Dim sizeId As Integer  = pSizeRow.Field(Of Integer)("id")
 
 Using cn As  New SqlConnection With {.ConnectionString = ConnectionString}
 Using cmd As  New SqlCommand With {.Connection = cn}
 '
 ' setup command text to check if in stock and underly logic to complete
 ' the shopping cart.
 '
 End Using
 End Using
 End Sub
End Class

We start off with several class properties along with the database connection string. HasErrors provides a way to determine if there were any exceptions thrown while reading data. If an exception is thrown ExceptionMessage will contain information for the exception.

The following three properties are BindingSource components used in the form to traverse the data. Usually BindingSource components are created and used in a form but that is not a hard rule. By having the BindingSource components in this class keeps all major data components together.

There are two methods, the first LoadData for reading in the data from the three tables. All three tables will be stored into a DataSet via a single Data Adapter. Developers will tend to create three Data Adapters, one for each table, although this is fine why create three when we can reuse a single Data Adapter for retrieving data from all three tables.

SELECT statements are stored in XML literals rather than string variables. Granted there is no value for these queries yet as your statements grow and become complex this is the way to go.

After the first two tables are loaded into the DataSet their relations are setup by primary keys using a language extension method located in the same project.

Public Module  DataRelationsExtensions
 ''' <summary>
 ''' USed to create a one to many relationship for a master-detail in a DataSet.
 ''' </summary>
 ''' <param name="sender"></param>
 ''' <param name="pMasterTableName">master table</param>
 ''' <param name="pChildTableName">child table of master table</param>
 ''' <param name="pMasterKeyColumn">master table primary key</param>
 ''' <param name="pChildKeyColumn">child table of master's primary key</param>
 <DebuggerStepThrough()>
 <Runtime.CompilerServices.Extension()>
 Public Sub  SetRelation(
 sender As  DataSet, 
 pMasterTableName As  String, 
 pChildTableName As  String, 
 pMasterKeyColumn As  String, 
 pChildKeyColumn As  String)
 
 sender.Relations.Add(New DataRelation(
 String.Concat(pMasterTableName, pChildTableName), 
 sender.Tables(pMasterTableName).Columns(pMasterKeyColumn), 
 sender.Tables(pChildTableName).Columns(pChildKeyColumn)))
 
 End Sub
 
End Module

Once the relationships have been set up each table is assigned to the DataSource of the BindingSource components setup as properties in this class.

Note that the product BindingSource DataSource is the DataSet. By setting the first table (Product table) as the DataMember this will permit Products to display in a ListBox. The detail BindingSource DataSource is setup to the product BindingSource with the first relation as the data member. The master DataTable is Color table so this will be what displays in the colors ListBox. Finally the Size BindingSource follows suit as with the color BindingSource

Note: Setting up the BindingSource components is critical, this is as important as a good database schema. When one or the other is incorrect the master-detail will fail. Another failure point is when there is bad data in the detail tables.

5. Load event in form

In the form, in form load event (or place the code in form shown event) we create an instance of the data class created above. In the load event invoke LoadData method followed by assigning each ListBox to a BindingSource component.

There are two events subscribed too for both product and color tables via the BindingSource components for PostionChanged. This is done to show related data when moving from one item in a ListBox to another item in the ListBox.

There is a button on the form solely to show how to get at current records being displayed in the ListBox controls.

Note: In both the data class and form there are lines of code which only work while in debug mode. When not in debug mode these lines of code will not execute. They are here as a guide to see what is going on under the covers.

Imports BackEnd
Public Class  Form1
 Private ops As New  Operations
 
 Private Sub  Form1_Load(sender As  Object, e As EventArgs) Handles MyBase.Load
 
 ops.LoadData()
 
 If ops.HasErrors Then
 MessageBox.Show($"{ops.ExceptionMessage}")
 Exit Sub
 End If
 
 ProductsListBox.DataSource = ops.bsMasterProduct
 ProductsListBox.DisplayMember = "Name"
 
 ColorsListBox.DataSource =  ops.bsDetailsColor
 ColorsListBox.DisplayMember = "Color"
 
 SizesListBox.DataSource = ops.bsDetailsSize
 
 SizesListBox.DisplayMember = "Size"
 
 SetupDebuggingDataBindings()
 
 DescriptionLabel.DataBindings.Add("Text", ops.bsMasterProduct, "Description")
 RetailLabel.DataBindings.Add("Text", ops.bsMasterProduct, "RetailFormatted")
 
 AddHandler ops.bsMasterProduct.PositionChanged, AddressOf MasterPositionChanged
 AddHandler ops.bsDetailsColor.PositionChanged, AddressOf DetailsPositionChanged
 
 ShowCurrentProductAndColor()
 
 End Sub
 Private Sub  MasterPositionChanged(sender As Object, e As  EventArgs)
 ShowCurrentProductAndColor()
 End Sub
 Private Sub  DetailsPositionChanged(sender As Object, e As  EventArgs)
 ShowCurrentProductAndColor()
 End Sub
 Private Sub  ShowCurrentProductAndColor()
 If ops.bsMasterProduct.Current IsNot Nothing AndAlso  ops.bsDetailsColor.Current IsNot Nothing Then
 TextBox1.Text = $"{ops.bsMasterProduct.CurrentRow.Field(Of String)("Name")}," &
 $"{ops.bsDetailsColor.CurrentRow.Field(Of String)("Color")}"
 End If
 End Sub
 ''' <summary>
 ''' This method shows how to obtain data for the current selection both directly in the form
 ''' and also passing information via DataRow objects to the data class Operations.
 ''' </summary>
 ''' <param name="sender"></param>
 ''' <param name="e"></param>
 Private Sub  SelectedButton_Click(sender As Object, e As  EventArgs) Handles  SelectedButton.Click
 
 Dim currentProductRow As DataRow  = ops.bsMasterProduct.CurrentRow 
 Dim product As String  = currentProductRow.Field(Of String)("Name")
 Dim retail As Integer  = currentProductRow.Field(Of Integer)("Retail")
 
 
 Dim currentColorRow As DataRow = CType(ops.bsDetailsColor.Current, DataRowView).Row
 Dim color As String  = currentColorRow.Field(Of String)("Color")
 
 Dim currentSizeRow As DataRow = CType(ops.bsDetailsSize.Current, DataRowView).Row
 Dim size As String  = ops.bsDetailsSize.CurrentRow.Field(Of String)("Size")
 
 ops.AddToOrder(currentProductRow, currentColorRow, currentSizeRow)
 
 End Sub
 Private Sub  SetupDebuggingDataBindings()
 If Debugger.IsAttached Then
 lblProductIdentifier.Visible = True
 lblColorIdentifier.Visible = True
 lblSizeIdentifier.Visible = True
 lblProductIdentifier.DataBindings.Add("text", ops.bsMasterProduct, "id")
 lblColorIdentifier.DataBindings.Add("text", ops.bsDetailsColor, "id")
 lblSizeIdentifier.DataBindings.Add("text", ops.bsDetailsSize, "id")
 End If
 End Sub
End Class

Source code

YOUR-PC

The full project can be found on MSDN here.

Prior to running the code, you need to create the database, tables and populate the tables using a script in the backend data class. Also, change the connection string server from YOUR-PC to your server e.g. .\SQLEXPRESS.

Share via