ASP.NET

<br />
<br />
        Private Function fnPopulateDataGrid()<br />
            Try<br />
                ' Define a dataset the is used to store the retrieved data from SQL<br />
                ' This dataset is going to be used to hold multiple result sets.<br />
                ' These result sets are:<br />
                '     * Country<br />
                '     * Region<br />
                '     * Dates - From the diary<br />
                '     * Merchandisers<br />
                '     * Account details and Merchandiser claim details<br />
                Dim oDS As DataSet<br />
                '<br />
                ' This is the data table used to 'massage' the result sets for output.<br />
                ' It is our generic 'container' used for the purpose of our results which<br />
                ' we WANT to have programatic control over.<br />
                Dim oTable As DataTable = New DataTable("RESULTS")<br />
                '<br />
                ' Set up columns in the results table (oTable)<br />
                ' Note how we actually define the column header, and the data type the column will<br />
                ' be utilising. We could have avoided this, but for type save conversions<br />
                ' it is much more safer to have explicite types (as we do in database fields for tables).<br />
                oTable.Columns.Add("Country", GetType(System.String))<br />
                oTable.Columns.Add("Region", GetType(System.String))<br />
                oTable.Columns.Add("Diary Date", GetType(System.String))<br />
                oTable.Columns.Add("Merchandiser", GetType(System.String))<br />
                oTable.Columns.Add("Customer", GetType(System.String))<br />
                oTable.Columns.Add("Claim Time", GetType(System.String))<br />
                oTable.Columns.Add("Claim Kms", GetType(System.Int32))<br />
                oTable.Columns.Add("Approved", GetType(System.Web.UI.WebControls.CheckBox))<br />
                '<br />
                ' New instance of a check box for the approve facilitation.<br />
                Dim oCheckBox As CheckBox = New CheckBox<br />
                oCheckBox.Visible = True<br />
                oCheckBox.Enabled = True<br />
                '<br />
                ' Here is defined each table to be used to map to each result set<br />
                ' obtained from the data set<br />
                Dim oCountryTable As DataTable = New DataTable("Countries")<br />
                Dim oRegionTable As DataTable = New DataTable("Regions")<br />
                Dim oDiaryDateTable As DataTable = New DataTable("DiaryDates")<br />
                Dim oMerchandiserTable As DataTable = New DataTable("Merchandisers")<br />
                Dim oCustomerTable As DataTable = New DataTable("Customers")<br />
                '<br />
                ' And now we define our rows to be added into out data table for output<br />
                ' oRow is a 'generic' row used specifically for the output data table.<br />
                ' We use this row to populate each column (as defined above).<br />
                Dim oRow As DataRow<br />
                '<br />
                ' And now a row defined for each data table in our data set.<br />
                ' We want to have a data row defined so we can access the details, form<br />
                ' relationships and use for processing<br />
                Dim oCountryRow As DataRow<br />
                Dim oRegionRow As DataRow<br />
                Dim oDiaryDateRow As DataRow<br />
                Dim oMerchandiserRow As DataRow<br />
                Dim oCustomerRow As DataRow<br />
                '<br />
                ' The function called returns a DataSet<br />
                ' Lets call our business facade method to get us our mechandisers passing no parameters <br />
                ' at this point.<br />
                ' The parameters are obtained from within the function.<br />
                ' We eventually call sp_getMerchandiserClaimDetails stored procedure.<br />
                ' Note: We are using the Microsoft Data Access Blocks, which is a part of the Microsoft Enterprise Blocks<br />
                '       (Just so that it is a bit more repeatable, and robust - like java - haha).<br />
                oDS = fnGetMerchHierData()<br />
                '<br />
                ' Now we have our data set with multiple result sets, we want to store these results into<br />
                ' a table of their own.<br />
                ' In this case we want to have a country, Region, Date, Merchandiser and Customer table<br />
                ' so we can use more efficiently and cleanly going forward.<br />
                ' We can address each result set in the data set by referencing the index (as we haven't named them<br />
                ' for efficiency purposes. We name 'things' going forward for readability and supportability<br />
                ' purposes.<br />
                ' Once we have our addressed result set from our data set populated from the business facade,<br />
                ' we explicitely set it to our prior defined data tables so we can use them in an<br />
                ' object-relational fasshion (as per below).<br />
                oCountryTable = oDS.Tables(0)<br />
                oRegionTable = oDS.Tables(1)<br />
                oDiaryDateTable = oDS.Tables(2)<br />
                oMerchandiserTable = oDS.Tables(3)<br />
                oCustomerTable = oDS.Tables(4)<br />
                '<br />
                ' Now we define our relationships between our data tables.<br />
                ' Note how we define it to our Data Set, as it is the primary container for all the tables.<br />
                ' Note: a data set is an in memory data base (IMDB).<br />
                ' Note: We hare using 3 parameters in the .Relations.Add method as opposed to 4.<br />
                '       The 4th parameter is used to overide whether or not a constraint is formed or not.<br />
                '       By default (by excluding this parameter) it forces a constraint.<br />
                '       This has been intentionally designed this way to ensure we do not violate any<br />
                '       integrity rules, and also ensure we have correct data as we populate our data table.<br />
                ' Note: Because of the way we are forming these relation ships, we actually creating<br />
                '       Parent -> Child -> Sub-Child -> Sub-Sub-Child -> Sub-Sub-Sub-Child relations<br />
                '       in the following fasion:<br />
                '            Country -> Region -> Dates -> Merchandisers -> Accounts serviced and claim details<br />
                ' First establish a relationship between Country as our parent to child records, Regions.<br />
                oDS.Relations.Add("Regions", oCountryTable.Columns("CountryID"), oRegionTable.Columns("CountryID"))<br />
                '<br />
                ' Next, we define a relationship between Region as the parent to child records, Dates.<br />
                oDS.Relations.Add("DiaryDates", oRegionTable.Columns("RegionID"), oDiaryDateTable.Columns("RegionID"))<br />
                ' Next, we define a relationship between Dates as the parent to chile records, Merchandisers.<br />
                'oDS.Relations.Add("Merchandisers", oDiaryDateTable.Columns("DiaryDate"), oMerchandiserTable.Columns("DiaryDate"))<br />
                oDS.Relations.Add("Merchandisers", oDiaryDateTable.Columns("DiaryRegionID"), oMerchandiserTable.Columns("DiaryRegionID"))<br />
                ' Finaly, we establish a relationship between Merchandisers as the parent to the child records, the accounts serviced.<br />
                oDS.Relations.Add("Customers", oMerchandiserTable.Columns("DiaryID"), oCustomerTable.Columns("DiaryID"))<br />
                '<br />
                '<br />
                ' Now the hard yakka. Lets go through our data tables (related).<br />
                ' Because we now have a relationional IMDB, we can access this in a programatic fashion.<br />
                ' We start off with our 'base' table, being Countries, and access the child, sub-child records<br />
                ' as defined in our relationships above.<br />
                ' Note: For each 'dimension' specified we generically add a row to our data table (oRow within Table)<br />
                '       so we can accordingly add the dimension specific (column) to the row to represent the detail<br />
                '       required in a hierarchical fashion. So, in essence we have grouped data into their logical<br />
                '       relation.<br />
                ' We cascade 'down' the relations in the order of parent -> child definitions.<br />
                ' This is: Country -> Region -> Dates -> Merchandisers -> Accounts serviced and claim details.<br />
                ' Note : To obtain the child details from the parent form, we use the .GetChildRows(SubstituteTableNameHere).<br />
                '        This utilises the data tables defined above, the relations formed above them, and<br />
                '        we can be sure that the integrity is maintained by the rules upon forming the relations.<br />
                For Each oCountryRow In oCountryTable.Rows<br />
                    oRow = oTable.NewRow()<br />
                    oRow("Country") = oCountryRow("Country")<br />
                    oTable.Rows.Add(oRow)<br />
                    For Each oRegionRow In oCountryRow.GetChildRows("Regions")<br />
                        oRow = oTable.NewRow()<br />
                        oRow("Region") = oRegionRow("Region")<br />
                        oTable.Rows.Add(oRow)<br />
                        For Each oDiaryDateRow In oRegionRow.GetChildRows("DiaryDates")<br />
                            oRow = oTable.NewRow()<br />
                            oRow("Diary Date") = oDiaryDateRow("DiaryDate")<br />
                            oTable.Rows.Add(oRow)<br />
                            For Each oMerchandiserRow In oDiaryDateRow.GetChildRows("Merchandisers")<br />
                                oRow = oTable.NewRow()<br />
                                oRow("Merchandiser") = oMerchandiserRow("Merchandiser")<br />
                                oTable.Rows.Add(oRow)<br />
                                For Each oCustomerRow In oMerchandiserRow.GetChildRows("Customers")<br />
                                    oRow = oTable.NewRow()<br />
                                    oRow("Customer") = oCustomerRow("Customer")<br />
                                    oRow("Claim Time") = oCustomerRow("ClaimTime")<br />
                                    oRow("Claim Kms") = oCustomerRow("ClaimKms")<br />
                                    '<br />
                                    ' Add the Approved value as a checkbox control<br />
                                    If oCustomerRow("Approved").GetType.ToString = "System.DBNull" Then<br />
                                        oCheckBox.Checked = False<br />
                                    Else<br />
                                        oCheckBox.Checked = CBool(oCustomerRow("Approved"))<br />
                                    End If<br />
                                    oRow("Approved") = oCheckBox<br />
                                    '<br />
                                    oTable.Rows.Add(oRow)<br />
                                Next<br />
                            Next<br />
                        Next<br />
                    Next<br />
                Next<br />
                '<br />
                ' We now want to add our data table (oTable) to our result set (oDS) just incase we want to<br />
                ' further process any details within our data set (oDS).<br />
                ' so, we have our original result sets from our busines facade, and our 'processed'<br />
                ' data table (oTable) from this function.<br />
                oDS.Tables.Add(oTable)<br />
                '<br />
                ' We now define to our data grid (dgMerchandiserClaimData) the source of where it will be<br />
                ' getting it's data to present.<br />
                ' Because we have gone to the effort of 'processing' and generating our own data table (oTable),<br />
                ' we want to actually use this explicitly.<br />
                ' We have opted to specify .DefaultView which gets any defined customized<br />
                ' view of the table which may include a filtered view, or a cursor position. We haven't defined<br />
                ' any of these customisations for our purposed, but it gives us the ability to do so incase we do.<br />
                dgMerchandiserClaimData.DataSource = oTable.DefaultView<br />
                '<br />
                ' Add our checkbox to the grid :S<br />
                'dgMerchandiserClaimData.Columns.Add(oApprovedColumn)<br />
                '<br />
                ' Now we actually form the bind from the defined data source to the grid.<br />
                dgMerchandiserClaimData.DataBind()<br />
<br />
            Catch ex As Exception<br />
                Throw New Exception(ex.Message, ex)<br />
            End Try<br />
        End Function<br />
<br />