none
One to Many database relation in SQL & VB.Net RRS feed

  • Question

  • Dear All, 

    I am working on Project Management System where I would like to create database for people assigned on Project. Now relation is as Below :

    1) There can be multiple projects saved in "Projects" table in SQL.

    2) There are registered employees whose names & designations are saved in "User" table in SQL.

    3) I have created Windows form where combo box selects  Name of Project. Also form contains checklistbox. Checklistbox is provided for assigning team on Project. Checklistbox will be populated with User names from "User" table. 

    Now, I want to select project in combobox & check items in checklistbox. An "Assign" button will save selected multiple resource [user] names against a Project number. So a " Resource" table in database will store Project number in one column & names of people assigned on project, in another column. I wish to retrieve this data in the same form. I mean when project is selected in another Windows form, checklistbox will be populated with the names of people stored in database. 

    I am helpless & unable to understand philosophy to store & retrive multiple selected values against one Project.  

    Sunday, February 3, 2019 3:20 PM

All replies

  • Hello,

    IMPORTANT

    1. The following provides ground work to get your feet wet, there is more to what you want them presented below (which is a good deal to swallow). 
    2. Having primary keys available is very important e.g. presenting table data you need to have the keys for filtering, editing, deleting and adding records else your task complexity increase.

    The core elements are to have relationships defined between all tables first and test them using SELECT, DELETE, UPDATE and INSERT statements in your database before writing any code.

    The above can be done in MS-Access under database tools to set relationships while in SQL-Server this is best done in SSMS (SQL-Server Management Studio) and is free by double clicking on "Database Digraphs"

    Once the above is done I would recommend creating a class that is responsible for all data operations.

    The following is simplified versions of typical data operations using the appropriate manage data provider e.g. OleDb for MS-Access, SqlClient for SQL-Server.

    NOTE: There are easier ways then what's shown below but are not recommended .e.g. using Adapters of various types.

    Add the following code module (used in the next code block) which is used to easily create relations.

    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 

    This is part of a larger example where here we are reading related data, Customers to orders. We can use the same logic to go deeper e.g. Orders would have order details we then would relate the two.

    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=KARENS-PC;" &  
                                             "Initial Catalog=MasterRelationsDemo;" &  
                                             "Integrated Security=True" 
        Public Property CustomerTable As DataTable 
        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 
    
     
                Catch ex As Exception 
                    HasErrors = True 
                    ExceptionMessage = ex.Message 
                End Try 
            End Using 
        End Sub 
    End Class 
    
    
    

    In the form to show data we initialize the class above and load data

    Public Class Form1 
        Private ops As New Operations 
     
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load 
     
            ops.LoadData() 

    Once LoadData has been called we can access the data set in the SELECT statements done above.

    • Use BindingNavigators to provide traversing of data.
    • Data binding data to controls e.g. firstNameTextBox.DataBinding.Add

    Let's say you need to access child tables from the parent table, see the following. For child to parent GetParentRows.

    For the basics of working with SQL-Server and CheckedListBox see my code sample and this one too.

    A comprehensive master-details code sample with DataGridView controls I wrote.

    If the above is too much for you then look at TableAdapters which are easy in the beginning but become difficult down the road. 

    Any ways you approach this time needs to be spent up front as indicated at the beginning of this reply rather than force your way through it.

    If you get stuck open a new question and ask for assistance.



    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, February 3, 2019 4:01 PM
    Moderator