Introduction

Business application is created with one database table that is simple to write yet the majority of business applications are written on top of a relational database. For example, a table for customers, a related table for addresses another table for phone numbers, a table for products, orders and order details, vendors to purchase from and shippers to ship orders. 

High-level the code presented here focuses on reading data from SQL-Server using SqlClient managed data provider along with add, edit and remove master records, remove detail records when deleting a customer. 



The following is database schema for this article which has been kept simple for learning.



Base read operation

In the main form for viewing related data, there are three BindingSource components, one for the main table Customer, another for orders and the last one for order details using the above schema.

In the main form load event, a class is made to load all three tables.

Dim ops As New Operations
ops.LoadData(bsMaster, bsDetails, bsOrderDetails)

In Operations.LoadData a SqlDataAdapter is used to first load data from the Customer table using the SqlDataAdapter Fill method to a new DataSet where the second parameter for the Fill method is the table name. This is followed by performing the same for the orders table.

At this point, there are two DataTable containers populated from tables in the selected database but know nothing of each other. The following code will link the two tables together by primary keys.

ds.SetRelation("Customer", "Orders", "Id", "CustomerId")

SetRelation is a simple wrapper for adding a relationship to the DataSet.
<DebuggerStepThrough()>
<Runtime.CompilerServices.Extension()>
Public Sub SetRelation(sender As DataSet, MasterTableName As String, ChildTableName As String, MasterKeyColumn As String, ChildKeyColumn As String)
 
    sender.Relations.Add(
     New DataRelation(String.Concat(MasterTableName, ChildTableName),
        sender.Tables(MasterTableName).Columns(MasterKeyColumn),
        sender.Tables(ChildTableName).Columns(ChildKeyColumn)
     )
  )
 
End Sub

Here are the results by setting a breakpoint after the SetRelation is finished,


Important
For cascading deletes work there must be rules set in the database, in this case, done through SSMS (SQL-Server Management Studio) through the data diagram. If rules are not set a runtime exception will be thrown, same if a DELETE statement were executed in SSMS as this is a constraint violation.




At this point Customer and Order tables are populated, next order details are loaded the same as Customer and Order table. Once all tables are loaded and relations set they are set to BindingSource components passed from the calling form to the class which the above code results.

Method signature:
Public Sub LoadData(
    pCustomer As BindingSource,
    pOrders As BindingSource,
    pOrderDetails As BindingSource)

Code responsible for assigning the DataSet to the BindingSource components.

pCustomer.DataSource = ds
pCustomer.DataMember = ds.Tables(0).TableName
 
pOrders.DataSource = pCustomer
pOrders.DataMember = ds.Relations(0).RelationName
 
pOrderDetails.DataSource = pOrders
pOrderDetails.DataMember = ds.Relations(1).RelationName

What may appear odd is setting order BindingSource DataSource to the customer table, the next line indicates which table to use, Orders. The same applies to orders to ordering details. This all works because of the relationships setup prior.

Full source for reading the three tables.
Public Sub LoadData(
    pCustomer As BindingSource,
    pOrders As BindingSource,
    pOrderDetails As BindingSource)
 
    Dim ds As New DataSet
 
    Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
        Dim da As New SqlDataAdapter(
            "SELECT id,AccountNumber, FirstName,LastName," &
            "Address,City,State,ZipCode FROM Customer ORDER BY AccountNumber", cn)
        Try
            da.Fill(ds, "Customer")
 
            da = New SqlDataAdapter("SELECT id,CustomerId,OrderDate,Invoice FROM Orders", cn)
            da.Fill(ds, "Orders")
            ds.SetRelation("Customer", "Orders", "Id", "CustomerId")
 
            da = New SqlDataAdapter("SELECT id,OrderId ,ProductName,UnitPrice,Quantity FROM OrderDetails", cn)
            da.Fill(ds, "OrderDetails")
            ds.SetRelation("Orders", "OrderDetails", "Id", "OrderId")
 
            pCustomer.DataSource = ds
            pCustomer.DataMember = ds.Tables(0).TableName
 
            pOrders.DataSource = pCustomer
            pOrders.DataMember = ds.Relations(0).RelationName
 
            pOrderDetails.DataSource = pOrders
            pOrderDetails.DataMember = ds.Relations(1).RelationName
 
        Catch ex As Exception
 
            HasErrors = True
            ExceptionMessage = ex.Message
 
        End Try
    End Using
End Sub

Back in the main form load event, each DataGridView has it's DataSource set to the appropriate underlying DataTable within the DataSet which becomes a data source for each of the BindingSource components.



Accessing data in the DataGridView controls

The current row may be accessed through the Current property of the desired BindingSource associated with a specific DataGridView control. For example, to get the current customer.

Dim CustomerRow As DataRow = CType(bsMaster.Current, DataRowView).Row

If there is a chance there are no rows an assertion is needed checking to see if Current is null/nothing.

Private Sub EditCurrentCustomer()
    If bsMaster.Current Is Nothing Then
        Exit Sub
    End If
    Dim CustomerRow As DataRow = CType(bsMaster.Current, DataRowView).Row

If there is a need to interrogate customer orders without using the orders BindingSource use the following code to get a DataRow array or an empty array if no orders.
Dim CustomerRow As DataRow = CType(bsMaster.Current, DataRowView).Row
Dim childrenRows As DataRow() = CustomerRow.GetChildRows("CustomerOrders")

BindingNavigator

A BindingNavigator provides methods to traverse data, remove, add and edit data where forward and backward movement along with goto via the record text box. Add additional buttons for the CRUD operations and take special care with a delete operation.

When deleting from the navigator prompt the user to verify the want to remove a record (full source is in the repository for this article)



In this case, if the yes button is clicked method is called to remove the customer and child records. Since there may be a failure because there are three tables the operation is wrapped in a transaction.

Public Function RemoveCustomerAndOrders(id As Integer) As Boolean
 
    Dim deleteStatement = "DELETE FROM Orders WHERE CustomerId =  @CustomerId"
    Dim success As Boolean = False
 
    Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
 
        cn.Open()
 
        Dim trans As SqlTransaction = cn.BeginTransaction("DeleteOps")
 
        Using cmd As New SqlCommand With {.Connection = cn, .CommandText = deleteStatement, .Transaction = trans}
 
            cmd.Parameters.AddWithValue("@CustomerId", id)
 
            Try
                cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@Id", id)
                cmd.CommandText = "DELETE FROM Customer WHERE  id = @id"
 
                cmd.ExecuteNonQuery()
                trans.Commit()
 
                success = True
 
            Catch ex As Exception
 
                HasErrors = True
                ExceptionMessage = ex.Message
 
                Try
 
                    trans.Rollback()
 
                Catch transEx As Exception
 
                    HasErrors = True
                    ExceptionMessage = transEx.Message
 
                End Try
 
            End Try
        End Using
    End Using
 
    Return success
 
End Function

If the delete operation is successful the current record is removed from the DataGridView by way of 

bsMaster.RemoveCurrent()

Summary

This article provided the basics for setting up a master-detail relationship in a Windows forms solution. Source code is in both C# and VB.NET. There is plenty of operations not covered in this article text but within the source code, you will learn how to add, edit and remove records against the underlying DataSet.

See also

Master Detail DataGridView C# Winform 

Source code


The following repository has both C# and VB.NET projects. In the solution root folder datascript needs to run to setup the database, tables and populate tables.