Windows forms Master-detail view CRUD on SQL-Server (VB.NET/C#)
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.