Setting Null Value in an Optional Non-identifying Relationship Using ComboxBox

Introduction

The most common relationship in any data model is the one-to-many non-identifying relationship. Non-identifying relationship implies weak dependency relationship between parent and child entities. There are two kinds of non-identifying relationships, including optional and mandatory. The necessity of the parent entity is "exactly one" and "zero or one" in the mandatory and optional non-identifying relationship respectively. One problem I've tackled in many of my enterprise application is the presentation of complex data relationship such as optional non-identifying relationship using data binding techniques in Windows Forms applications. In this article, I will illustrate how to face this problem.

Getting started with the solution

Let's supposed that we have the table schema for the dept and emp tables representing the department and employee business entities respectively (see Listing 1). This is an optional one-to-many non-identifying relationship where the dept table is the parent and the emp table is the child.

CREATE TABLE [dbo].[dept](
[deptno] [int] NOT NULL,
[dname] [varchar](60) NULL,
[loc] [varchar](60) NULL,
[rowversion] [timestamp] NOT NULL,
PRIMARY KEY CLUSTERED([deptno] ASC)
)
go
CREATE TABLE [dbo].[emp](
[empno] [int] NOT NULL,
[ename] [varchar](60) NULL,
[salary] [numeric](7, 2) NULL,
[deptno] [int] NULL,
[rowversion] [timestamp] NOT NULL,
PRIMARY KEY CLUSTERED( [empno] ASC )
) ON [PRIMARY]
go
ALTER TABLE [dbo].[emp] WITH CHECK ADD CONSTRAINT [FK_emp_dept] FOREIGN KEY([deptno])
REFERENCES [dbo].[dept] ([deptno])
ON UPDATE CASCADE
ON DELETE CASCADE
go
ALTER TABLE [dbo].[emp] CHECK CONSTRAINT [FK_emp_dept]

go

Listing 1

Now let's represent this complex relationship in a Windows form using data binding techniques. Let's add one DataSet NonIdentifyingDS and one Windows Form NonIdentifyingForm artifact. Add a table representing the employees and another table representing the departments as well as the option of not select any department (set the deptno property of the underlying employee business entity to null) onto the DataSet NonIdentifyingDS (see Figure 1, Figure 2 and Figure 3).

Figure 1

Figure 2

Figure 3

Now open the NonIdentifyingForm Windows Form, change data presentation style of the emp node (representing the employee entity) from DataGridView to Details in the Data Sources as well as change the presentation style of the deptno attribute of the emp node from TextBox to ComboBox (see Figure 4).

Figure 4

Then, drag and drop the emp node in the Data Sources onto the form (see

Figure 5).

Figure 5

Then drag and drop the dept node from the Data Sources windows onto the deptno Textbox control. You can check the data binding properties (see Figure 6).

Figure 6

Now, let's run the application and leave in blank the dname field (see Figure 7).

Figure 7

When you run a query against the data source, you see the null value associated to the column of the underlying row (see Listing 3).

empno ename salary deptno
----------- ------------------------------------------------------------ --------------------------------------- -----------
7369 John C. Olamendy 1000.00 NULL

Listing 3

Conclusion

In this article, I've illustrated how to face the problem of the presentation of complex data relationship such as optional non-identifying relationship using data binding techniques in Windows Forms applications. Now you can apply this solution to your business scenario.