Washington State University



More with Data tablesImports System.Data'This program shows how to use data structures, how to store data inside them, how to analyze the data inside them, how to compile and present the data from inside the data structure - either all the data or some of the data (ie different labor grades). This program teaches you about data structures and data management which will lay the learning foundation for future programs that interract with SQL server database tables.Partial Class ConstructionPayroll Inherits System.Web.UI.Page 'both of these objects need to be accessed from more than 1 procedure so they are created at the page level Public Shared strLG As String Public Shared LaborRecordsTable As New DataTable#Region "Create dataTable" Protected Sub AddTable() 'This procedure creates the data structure to store the data. The data table is created at the page level. Here the columns are created with their column names and their data types. The first column is even defined to be integer and auto-numbering. The result of auto-numbering in this context is that the labor records recorded are automatically numbered with the next number in sequence. Very useful! 'We start with some error checking to make sure the data table is not created more than once. If the data table already has columns of data inside it, then you do not need to create the data table again. This next code makes sure that the data table Is only created once. This code is called from another procedure, another methodology is to add this code to the Page_init event. If LaborRecordsTable.Columns.Count > 0 Then Exit Sub End If 'Here we add the columns to the table. Notice the different data types (integer for whole numbers, string for words, decimal for bigger numbers). We have to add each column one at a time and specify the data type. the term after the WITH statement is added to the beginning of each line of code before the End With statement. With LaborRecordsTable.Columns .Add("LaborRecord", Type.GetType("System.Int32")) .Add("EmployeeName", Type.GetType("System.String")) .Add("LaborGrade", Type.GetType("System.String")) .Add("Hours", Type.GetType("System.Decimal")) .Add("GrossPay", Type.GetType("System.Decimal")) .Add("Date", Type.GetType("System.String")) End With 'here we specify the first column to be auto-numbering starting at 1 and incrementing 1 each new record. With LaborRecordsTable.Columns("LaborRecord") .AutoIncrement = True .AutoIncrementSeed = 1 .AutoIncrementStep = 1 End With 'this next line of code is not really needed but included to teach the .count property of tables (and list controls). You can leverage the table.columns.count or table.rows.count property in different problem solving scenarios. txtOutput.Text = ("A table with " & LaborRecordsTable.Columns.Count & " columns was created.") End Sub#End Region#Region "Save Labor Record" Protected Sub DropDownList1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList1.SelectedIndexChanged 'let's show the pay rate for the selected labor category to the program user. When saving the data record with the calculated gross pay, DropDownList1.SelectedValue is where the payrate comes from. Payrate is stored when the program is designed, in the value portion of the drop down list. Label1.Text = "Hourly pay rate used in payroll calculations for that " & DropDownList1.SelectedItem.Text & " is " & FormatCurrency(DropDownList1.SelectedValue) End Sub Protected Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click 'this procedure adds a row of labor data. First we make sure the datatable has columns in it. Call AddTable() 'now make sure the data entry is complete so that the data record is complete and calculations can be performed If txtEmployee.Text = Nothing OrElse DropDownList1.SelectedIndex < 1 OrElse DateTime.Parse(txtStartTime.Text) = Nothing OrElse DateTime.Parse(txtEndTime.Text) = Nothing Then txtOutput.Text = "Check data entry, thanks" Exit Sub End If 'calculate the length of time the employee worked, next add the new blank row of data to the data table Dim ts As TimeSpan = Date.Parse(txtEndTime.Text) - Date.Parse(txtStartTime.Text) Dim dr As DataRow = LaborRecordsTable.NewRow 'in case the time entered is wrong. If ts.TotalHours > 16 Then txtOutput.Text = "Check hours worked, should be one shift on one day" Exit Sub End If 'here we populate each column of the new row added to the datatable. Remember the first column is auto-incrementing so that you do not have to insert the value, the value is added automagically. The hours and grosspay are formatted to 2 decimal places because some of the hours calculations will have repeating decimals. This program pulls the hourly payrate from the dropdownlist .value property. This means each employee in the labor grade is paid the same hourly rate. This is a simplification. dr("EmployeeName") = txtEmployee.Text dr("LaborGrade") = DropDownList1.SelectedItem.Text dr("Hours") = FormatNumber(ts.TotalHours, 2) dr("GrossPay") = FormatNumber(ts.TotalHours * DropDownList1.SelectedValue, 2) dr("Date") = Now.ToShortDateString 'now that the data row is populated with values in each column, now add the row to the table and display the data in the gridview. Set the grodview's datasource to the datatable again now that it it has a new row. LaborRecordsTable.Rows.Add(dr) GridView1.DataSource = LaborRecordsTable GridView1.DataBind() End Sub#End Region#Region "Basic looping" Protected Sub LinkButton3_Click(sender As Object, e As EventArgs) Handles LinkButton3.Click 'using loops for analytics is powerful technique and a standard procedure in many different scenarios. Here is a simple example that demonstrates the basics of looping and the basics of pulling data out of columns of a datatable. Note that the SQL group by query provides the same functionality (covered later). 'First we clear out anything from the textbox used for output. txtOutput.Text = Nothing Dim decNumberHours As Decimal ' this variable used to total up the hours inside the loop 'here is a looping mechanism that is used to examine each record in the datatable one at a time. 'If there were 10 rows we could write the next line for intx as integer (which creates the variable) 1 to 10. That numbering does not line up with the numbering of the datarows which start at 0. Items in a collection of datarows of a datatable start at 0. Hence the loop starts at 0 and stops at the count of the rows minus 1. So for example running the loop from 0 to 9 is running the loop 10 times. This loop brings each row of data into the memory ONE AT A TIME so that values in the columns can be accessed, examined, and used in calculations (here added to running totals to sum the # of hours worked). 'this procedure is shown first, before the next procedure to set the foundation. Here there is no FILTERING of the data rows, rather ALL the rows are examined. 'the loop reads the current row of data into computer RAM memory so that the code can examine the values for any column of that row. We add to a running total to count uo the number of hours worked and pull the employee name into the textbox for display. The code on the right hand side of the = sign reads tablename.rows(row #).column("column name") For intx As Integer = 0 To LaborRecordsTable.Rows.Count - 1 decNumberHours += LaborRecordsTable.Rows(intx).Item("Hours") txtOutput.Text &= LaborRecordsTable.Rows(intx).Item("EmployeeName") & vbNewLine Next 'display the list of employee names and the running total txtOutput.Text &= vbNewLine & LaborRecordsTable.Rows.Count & " labor records for " & decNumberHours & " hours" 'FYI the code above can be written with the WITH statement, to ake the code less wordy. This isn't a problem with this simple example. check the next looping demonstration. Using the with statement is critical to keep the code looking clean and legible. The terms after the With statement get added to every obkject that starts with a . (in both cases the .item). 'For intx As Integer = 0 To LaborRecordsTable.Rows.Count - 1 ' ' With LaborRecordsTable.Rows(intx) ' decNumberHours += .Item("Hours") ' txtOutput.Text &= .Item("EmployeeName") & vbNewLine ' End With 'Next End Sub Protected Sub LinkButton4_Click(sender As Object, e As EventArgs) Handles LinkButton4.Click 'Here is another way to perform about the same functionality. We use a FOR each loop rather than a FOR NEXT loop. 'The prior code was presented to focus attention on the row number inside the parentheses to teach how the loop works. 'First we clear out anything from the textbox used for output. txtOutput.Text = Nothing Dim decNumberHours, decGrossPay As Decimal ' these variables used to total up the hours and grosspay inside the loop Dim dr As DataRow 'we create a datarow object that is used to examine a few columns for each row in the datatable. The FOR EACH code is very clean! 'you can use a FOR EACH loop as follows. If you create a datarow object (here named dr), then you can use the code for each row in the rows collection (list) of the datatable. Similar code could use for each item in the items collection of a list control, or for each column in the columns collection of the datatable. For Each dr In LaborRecordsTable.Rows decNumberHours += dr("Hours") decGrossPay += dr("GrossPay") Next 'display the list of employee names and the running total txtOutput.Text = "Total hours: " & decNumberHours & vbNewLine & "Total pay: " & decGrossPay.ToString("C0") End Sub#End Region#Region "Performing analytics with loops" Protected Sub DropDownList2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList2.SelectedIndexChanged 'This procedure does two things a) performs analytics (counts and toals) for one selected labor grade rather than the entire dataset and b)pulls rows of data into a textbox for display. Another program will show similar functionality of how to filter a gridview. 'Here we loop the array and get the transactions for one labor grade and also calculate a total number of labor records and total gross pay for ONE selected labor grade (ie electricians). The business managers can then see how much payroll was spent on any one labor grade. 'clear the output if any exists txtOutput.Text = Nothing 'create the local variables used in the looping code that counts and totals labor records for the one selected labor grade. You can add code to perform averages if you like. Dim decTotalPaid As Decimal 'used for the running totals Dim intCount As Integer 'used to count up the number of employees within one selected laborgrade 'if no labor grade was selected in the dropdownlist then do nto run the analytics below, rather stop the program. If DropDownList2.SelectedIndex < 1 Then Exit Sub End If 'This loop examines each row of the datatable structure. 'Here is an important line of code to examine: 'LaborRecordsTable.Rows(intx).Item("Laborgrade") - this is a bit of magic because the row number is a variable. 'So we can read the value in the laborgrade column for the current row. If you put this code in a loop then you can read the value in the laborgrade colunm for all of the rows, or for those rows that meet a criteria. Notice the (intx) it is an integer variable that specifies the row number of the datatable that is currently in memory and being examined. For intx As Integer = 0 To LaborRecordsTable.Rows.Count - 1 With LaborRecordsTable.Rows(intx) 'with the current row of data in memory we can examine the values for any COLUMN of that row. 'When the datarow is in memory, the value in the labor grade column (of the datarow) is checked against the labor grade selected by the program user (the .selecteditem labor grade such as Electrician) from the dropdownlist. 'When there is a match then a) display the data in a textbox (here we build a string pulling values from each column of the row) and b) perform the analytics of counting the number of employees and totalling their payroll - providing important information for analysis of business performance. If .Item("Laborgrade") = DropDownList2.SelectedItem.Text Then 'examine the labor records for ONLY the selected labor grade. Here we pull many columns of data (column names in red below - you can verify yourself) into the textbox.Another approach shown is to display the values in a gridview. So here we loop through the rows of the data structure, and build a string to display, and also total up some values. Both are extremely common. This methodology is extremely common with web data. Much data is not in a SQL database so you need to know this type of coding. txtOutput.Text &= "Labor Record #" & .Item("LaborRecord") & "- " & .Item("Date") & " " & .Item("Laborgrade") & " " & .Item("EmployeeName") & " worked " & .Item("Hours") & " hours and earned " & vbTab & FormatCurrency(.Item("GrossPay")) & vbNewLine 'here we increment the grosspay running total and the # of labor records decTotalPaid += .Item("GrossPay") intCount += 1 End If End With Next 'The program user may select a labor grade for which there no payroll records. The if statement below shows different text depending on whether or not there are labor records for the selected labor grade. If there are no labor records then display "No payroll for ..." If there are labor records then add a line of totals to the display. If intCount = 0 Then txtOutput.Text = "No payroll for " & DropDownList2.SelectedItem.Text & "s" Else txtOutput.Text &= vbNewLine & "Payroll for " & intCount & " " & DropDownList2.SelectedItem.Text & "s: " & FormatCurrency(decTotalPaid) End If End Sub#End Region#Region "Table compute way to get analytics for all labor grades, then ONE labor grade" Protected Sub Button10_Click(sender As Object, e As EventArgs) Handles Button10.Click 'Looping data structures remains a very popular way (across many different coding languages and contexts) to access data for display, for analytics and other calculations. Here the powerful pute technique is demonstrated, with no filtering of the data. A count of the total number of labor records is calculated and displayed. Also the total grosspsy is calculated. 'Take a look at the pute syntax, the blue term "nothing" means there is no filtering, so perform the aggregating calculations on the ENTIRE data structure. So before we reveal how to provide calculations on a column of data that has had its rows filtered (such as Electricians). The pute(aggregating functio(column name), filter) code is conceptually similar to the SQL GROUP BY WHERE code. 'First we make sure there is data to analyze, if there is no data the button was pressed in error so best to shut down the processing. If LaborRecordsTable.Rows.Count = 0 Then Exit Sub End If 'here we perform the aggregating function and set the results of the calculation to local variables. The code is far more parsimonius. 'BTW this next line of code could be replaced with Dim intNumberPayrollrecords As Integer = LaborRecordsTable.rows.count() 'JUST BE CAREFULL *** if your column name has any spaces in it, then put the column name in brackets, ie., [Labor Record] Dim intNumberPayrollrecords As Integer = pute("Count(LaborRecord)", Nothing) Dim decTotalPayroll As Decimal = pute("Sum(GrossPay)", Nothing) Dim decAverageGrossPay As Decimal = decTotalPayroll / intNumberPayrollrecords 'notice the average grosspay is formatted to zero decimal places txtOutput2.Text = "Total payroll for " & intNumberPayrollrecords & " employees was " & FormatCurrency(decTotalPayroll) & vbNewLine & vbNewLine & "Average grosspay per employee: " & FormatCurrency(decAverageGrossPay, 0) End Sub Protected Sub Button11_Click(sender As Object, e As EventArgs) Handles Button11.Click 'This procedure adds up the payroll for only the electricians. This code similar to the prior procedure except the filtering statement is added. In the next procedure we pass in the filter using a select case statement. Because the next procedure is complex we had to break it down to learn it more easily.. Dim intNumberPayrollrecords As Integer Dim decTotalPayroll, decAverageGrossPay As Decimal 'One way to provide the analytics would be to provide a button for each labor grade with the laborgrade hard coded into the pute such as ... intNumberPayrollrecords = pute("Count(LaborRecord)", "Laborgrade = 'Electrician' ") 'The code below calcualtes an average and therefore could have a divide by zero error which would crash the program, we remove this potential error here. If intNumberPayrollrecords = 0 Then txtOutput2.Text = "no data for electricians" Exit Sub End If 'Now we total up the grosspay for one hard coded labor grade - Electricians. Pay close attention to the filtering statement after the comma. The filter has to be a string so it is in quotes " " also the criteria if a word has to be in songle quotes, numbers not in single quotes. decTotalPayroll = pute("Sum(GrossPay)", "Laborgrade = 'Electrician' ") decAverageGrossPay = decTotalPayroll / intNumberPayrollrecords txtOutput2.Text = "Total payroll for " & intNumberPayrollrecords & " electricians was " & FormatCurrency(decTotalPayroll, 2) & vbNewLine & vbNewLine & "Average payroll for the electricians: " & FormatCurrency(decAverageGrossPay, 0) End Sub#End Region#Region "Analytics for 1 labor grade" Protected Sub DropDownList3_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList3.SelectedIndexChanged 'Now we pass in the filtering condition depending on what labor grade is selected in a dropdownlist. 'There is no easy way to work with the filtering portion of the pute. You cannot pass in a value or a variable such as using pute("Sum(GrossPay)", "Laborgrade = dropdownlist1.selcteditem.text") or 'pute("Sum(GrossPay)", "Laborgrade = strLaborgrade"). Neither of these work. The filter has to be static. 'the filter has to be a string so it is in double quotes. Because you cannot pass in values (at least this professor could not make variables in the filter statment of a pute work) we deploy a work-around solution (there is always a work-around in coding, remember that). Here we can use a select case statement to writes the different filtering statements needed that gets passed into the pute. This is genious, and it should give the emerging analyst a lot of idea of analytics solutions that can be provided. If LaborRecordsTable.Rows.Count = 0 Then 'if there is no data then stop the procedure. Exit Sub End If If DropDownList3.SelectedIndex < 1 Then 'if no selection was made from the list stop the procedure. txtOutput2.Text = "select a labor grade from the pick list" Exit Sub End If 'here we specify the custom string needed for the filtering statement of the pute, depending the selection in the dropdownlist. Select Case DropDownList3.SelectedItem.Text Case "Electrician" strLG = "Laborgrade = 'Electrician' " Case "Framer" strLG = "Laborgrade = 'Framer' " Case "Plumber" strLG = "Laborgrade = 'Plumber' " Case "General Helper" strLG = "Laborgrade = 'General Helper' " Case Else Exit Sub End Select 'there might not be any labor records for one or more of the labor grades. We need to check if there are any records for the selected labor grade. If there are no labor records for the selected labor grade then display the term - no data... and stop the program. Dim intNumberecords As Integer = pute("Count(LaborRecord)", strLG) If intNumberecords = 0 Then txtOutput2.Text = "No data for " & DropDownList3.SelectedItem.Text & "s" Exit Sub End If 'this code is essentially the same as shown in the prior procedure, except that a custom string is passed into the filtering condition of the pute code. Dim strLaborGrade As String = DropDownList3.SelectedItem.Text Dim intNumberPayrollrecords As Integer = pute("Count(LaborRecord)", strLG) Dim decTotalPayroll As Decimal = pute("Sum(GrossPay)", strLG) Dim decAverageGrossPay As Decimal = decTotalPayroll / intNumberPayrollrecords txtOutput2.Text = "Total for " & intNumberPayrollrecords & " " & strLaborGrade & "s was " & FormatCurrency(decTotalPayroll, 2) & vbNewLine & vbNewLine & "Average payroll for the " & strLaborGrade & "'s: " & FormatCurrency(decAverageGrossPay, 0) End Sub#End Region#Region "Analytics for more than one labor grade" Protected Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click 'what if you want to use a multi-select control? What if you want to run analytics for several laborgrades at the same time. There are many ways to perform this analysis - here showing the total number of labor records, or hours worked or gross pay for more than one laborgrade. For example how much was payroll for electricians and plumbers. This code uses a checkboxlist which is one of the multi-select control. This analysis technique fits with the other uses of loops. The code teaches how to use a aggregating function within a loop. Dim strLG, strLaborTypes As String Dim DecGrossPay, decHours As Decimal Dim intNumberPayrollrecords As Integer 'if nothing was selected in the list then stop the procedure from running If CheckBoxList1.SelectedIndex = -1 Then txtOutput2.Text = Nothing Exit Sub End If 'When using a multi-select control it is easiest to use a FOR EACH look. Previously we used a FOR NEXT loop because we used variable in the rows portion of the code (table.rows(intx).items("EmployeeName") to iterate the rows of the datatable. We could use a FOR NEXT look here also but a FOR EACH loop is conceptually easier. 'We start by creating a listitem variable (li as Listitem) named li. Then we set up the loop to read each of the items in the checkbox list items. There are 4 items in the items collection (items list) of this checkboxlist control, so this loop will run 4 times examining each item (each checkbox option) to see if it is selected. 'If the option was selected by the program user (has the check in the box) then we see which item was selected (ie electrician, framer, etc.). When we know which option was selected then we run tht pute code to calculate the number of hours and gross pay for the labor grade (ie electrician, framer, etc.). Because the checkbox control is mult-selecte we need to run the pute code for any combination of the options selected and add them together. To run the same code over and over (in this case for each of the options that are checked in teh control) we use a loop. So running puter code within the FOR EACH loop is pretty ingenious! 'If you are lucky your professor will show you how to run a SQL stored procedure within a loop to run a SQL query over and over against a SQL database table (its the same functionality). 'How to read the IF statement. If the item in the checkboxlist control was selected then next check which item was selected, and based on the item selected set the custom filter statement for the pute and run the analytics. Depending on which item was selected (which laborgrade) we set a different filter criteria for the pute. So for example if the option for Plumber was selected, we set the filter criteria to "Laborgrade = 'Plumber' " and then run the pute analytics. For Each li As ListItem In CheckBoxList1.Items If li.Selected Then Select Case li.Text Case "Electrician" strLG = "Laborgrade = 'Electrician' " Case "Framer" strLG = "Laborgrade = 'Framer' " Case "Plumber" strLG = "Laborgrade = 'Plumber' " Case "General Helper" strLG = "Laborgrade = 'General Helper' " End Select intNumberPayrollrecords = pute("Count(LaborRecord)", strLG) 'Since the pute is run within the loop, and the control is multi-select, we need to be careful to increment the running totals used in the analytics. If there is data for the chosen laborgrade (ie # payroll records >0) then add it to the running totals. Here we increment a local variable for number of hours and grosspay. If intNumberPayrollrecords > 0 Then strLaborTypes &= li.Text & "s, " decHours += pute("Sum(Hours)", strLG) DecGrossPay += pute("Sum(GrossPay)", strLG) Else Continue For End If End If Next 'maybe the options selected in the list control will not have any payroll records. Therefore we need to check for that and show labor analytics ONLY if there are labor hours. If there are no labor hours then just display "no data" If decHours > 0 Then txtOutput2.Text = decHours & " total labor hours for " & strLaborTypes & " Grosspay " & DecGrossPay.ToString("C2") Else txtOutput2.Text = "No data" End If End Sub#End Region#Region "Navigation" Private Sub ConstructionPayroll_Init(sender As Object, e As EventArgs) Handles Me.Init 'show the first view when the program is started MultiView1.ActiveViewIndex = 0 End Sub Protected Sub LinkButton1_Click(sender As Object, e As EventArgs) Handles LinkButton1.Click 'show the first view when the button is clicked MultiView1.ActiveViewIndex = 0 End Sub Protected Sub LinkButton2_Click(sender As Object, e As EventArgs) Handles LinkButton2.Click 'show the second view when the button is clicked MultiView1.ActiveViewIndex = 1 End Sub#End Region#Region "Utility code" Protected Sub Button8_Click(sender As Object, e As EventArgs) Handles Button8.Click 'clear the form txtEmployee.Text = Nothing txtStartTime.Text = Nothing txtEndTime.Text = Nothing txtOutput.Text = Nothing DropDownList1.SelectedIndex = -1 DropDownList2.SelectedIndex = -1 Label1.Text = Nothing End Sub Protected Sub Button9_Click(sender As Object, e As EventArgs) Handles Button9.Click 'this is how you can clear the data records from the data table, then clear the display of the data records in the gridview LaborRecordsTable.Rows.Clear() GridView1.DataSource = Nothing GridView1.DataBind() End Sub#End RegionEnd Class ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download