Washington State University



Using Data Structures inside a WebPage: Introduction to using DataTables web page demonstrates how to create a data structure to store data. So you learn about data management using a data structure. We use Visual ’s Active Data Objects () which are very simple to use as compared to other technologies. We create the data structure (datatable) then save rows of transaction data into this in-memory datatable. The program scenario is that the Dean of the College issued a fundraising challenge and students of several of the College of Business majors have agreed to compete to see which department can bring in the most donations for a scholarship fund.This web page teaches you common data management principles and practices using Microsoft’s ’s capable datatables which are strongly typed arrays meaning the memory structure can store different data types such as integer, date, string, and decimal and other column properties). All arrays are essentially tabular grids of values stored in the RAM of the web page. Historically, web pages have leveraged arrays (which are temporary, in-memory data structures) for fast data processing and to interact with a connected relational database. Arrays can be used to condense data. Data can be poured into an array, manipulated and then saved to database tables (which have the same column and rows configuration). In contents of datatables are often shown in a gridview control by setting the gridview control’s datasource to the datatable.Other terms for arrays are data structures, matrices, table variables, temp tables, and data grids. The web page being demonstrated in this module shows how to set up the data structure, and how to manipulate data within the data structure. how to filter, extract and display rows of data from the data structure, and how to perform simple analytics (counts, sums, and averages) to facilitate business process management and improvement. The data structures and processes demonstrated here lay a solid foundation for future data management experiences that will use SQL Server database tables. Database concepts are the same, and the database code is similar to that shown here. Because the array data structure of this project is stored in the memory of the webpage, there is no database infrastructure needed. Usage of only Visual (rather than connecting to a SQL Server database) makes this project an easier introduction to database concepts (rather than issuing database accounts and passwords). Here is a link to the running web page.Web page Features Part One: Donation Entry ScreenThe web page creates then leverages a page level datatable to store donation records. A datatable is a familiar tabular 2-dimensional data structure, that has rows and columns, just like Excel. The datatable is stored at the page level so that the data placed inside it from one procedure can be accessed from separate procedures on the webpage. The datatable has defined columns with column names and data types (see the blue row in the image above). The web developer has full authority to define how many columns are utilized in the web pages. While the web developer does have design control, they also normally work with database administrators so that the data saved into the web page is effortlessly appended to SQL server databases and data warehouses. The transfer of current transactional data and analytic calculations should be mapped to permanent database tables so that the front end interface (the webpage) can work in synergy with the back end database, data warehouse, and analytics programs. So while the web developer has the ability to design the needed data structures they should also realize they are part of a data management team. The web page adds datarows to the datatable then analyzes the data. Each donation record (each row) stores one student’s donation. The first column (field) of the data table (TransactionID) has an auto-number field (pseudo primary key), so the web page user does not have to enter this value for the first column. This program is a simplification for edutainment purposes, corporate web pages and web sites would use multiple data structures and interrelate them to improve data accuracy. Buy hey we are just getting started.The web page uses a button click procedure (see button with label “Record donation”) to accept and verify data entry. The donation record is stored as a new row of data added to the bottom of the data table. Datatables can store thousands of rows of transaction data, which then are the datasource for analytics perhaps to monitor donations by department and to compare actual to goal.As the donation is saved to the datatable, some overall analytics are provided using the pute function. These values are shown in a label. pute is an easy way to total or average an entire column of data.Individual donation records are shown in a gridview control by setting the gridview’s datasource to the datatable. Analytics are run automatically for each major. The running totals, counts and averages are calculated for each department based on all the data. In the future more advanced usage of pute will be demonstrated. Here familiar programming control structures are used SELECT CASE processing inside a FOR EACH loop, and 9 local variables. The output of the calculations are shown in a textbox. This use of a textbox for displaying output has been leveraged in prior programs and therefore is familiar. With many new programming techniques introduced here, usage of local variables and a textbox provide a familiar aspect to the program.Imports System.DataImports System.Data.SqlClient'This program serves as a bridge from the content the student should already have mastered to the next content which are using the active data objects () for data management, data analysis and data visualization. This program assumes you already are comfortable reading and writing programs using a) if-then-else-endif processing including nested if-then processing, b) select case processing, c) variables, d) error-checking, e) FOR EACH loops, f) using web form controls for data entry and g) using textboxes for output.'The new content presented here and in related programs are datatables (data structures), adding columns to the datatables, adding rows to the datatables, and looping the datatables to pull out values for analytics. These are foundational concepts that are built upon.'This program is written in such a way as to leverage the student's current knowledge and to provide new functinality and context, but in a comfortable manner. Very few new items are presented here from the content, and all students should be able to understand the code after a while. The code however is not opimized and future programs will provide similar (and extended) functionality in a more parsimonious manner. For example using 9 variables to perform the departmental analytics will be replaced with more advanced (and more parsimonious) functionality.'This program then is a bridge from the introductory content to the more intermediate content. Enjoy! Run this program, type it out, and then make modifications. Next create your own program in preperation or as part of the assignment.Public Class IntermediateDataTables Inherits System.Web.UI.Page 'The table is used as a data structure to store rows of data. Each row in the data table represents one transaction, which are student donations to a scholarship fund. This program is used to support a college-wide fundraising event. The academic departments are in competition to win the right host the school trophy in their department. Public Shared DonationsTable As New DataTable#Region "set up datatable for data storage" Protected Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click 'here is a simple password system. Notice the textmode of the textbox used for password entry is set to password. A future progam will place similar code in a loop so that the person only gets 3 tries. The webpage has 2 panels on it and they are made visible if the right password is supplied. If txtPassword.Text = "swordfish" Then Panel1.Visible = True Panel2.Visible = True 'We start with some error checking to check whether the datatable already has columns of data inside it. We only need to add the columns once If DonationsTable.Columns.Count > 0 Then Exit Sub 'This procedure adds the columns to the data table that was created at the page level. Each column is given a name and data type. The first column is even defined to be integer and auto-numbering. The result of auto-numbering in this context is that the transaction records are automatically numbered in the first column with the next number in sequence. Very useful! With DonationsTable.Columns .Add("TransactionID", Type.GetType("System.Int32")) .Add("Student", Type.GetType("System.String")) .Add("Major", Type.GetType("System.String")) .Add("Donation", Type.GetType("System.Decimal")) .Add("TADate", Type.GetType("System.String")) End With 'the date is a string (text) datatype so we can format it nicely. In other scenarios we will use the date and datetime datatypes so that we can perform time-based analytics on the data. Such as calculating how many days have elapsed since the customer's last interaction. 'here we specify the first column to be auto-numbering starting at 1 and incrementing 1 each new record. With DonationsTable.Columns("TransactionID") .AutoIncrement = True .AutoIncrementSeed = 1 .AutoIncrementStep = 1 End With End If End Sub#End Region#Region "Save Transaction data as a new row at the bottom of the datatable" Protected Sub Button4_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button4.Click 'Here we take values entered into the webform and place them into the columns of a new row of data. We then add the new row of data to the data strucure (the datatable) Dim decTotal, decAverage As Decimal Dim dr As DataRow = DonationsTable.NewRow 'see here is the new row for the datatable, we call it dr short for datarow 'let's make sure all the user-entered values are good If txtStudent.Text = Nothing OrElse rblMajors.SelectedIndex = -1 OrElse txtDate.Text = Nothing OrElse txtDonation.Text = Nothing OrElse IsNumeric(Convert.ToDecimal(txtDonation.Text)) = False Then Response.Write("Check data entry") Exit Sub End If 'start to add values from the controls on the form to the columns in the new datarow. The syntax is dr("column name"). Please verify that the column names used here match the columns created in the above procedure. FYI if there are typos in the column names, of course the code to fill the columns will not work. dr("Student") = txtStudent.Text dr("Major") = rblMajors.SelectedItem.Text 'in this next column we take the value from the calendar and convert it to a string (text) so that it is formatted nicely in the gridview. The textbox txtdate only looks like a calendar, the selected date inside is is really text. We format the date as a shortdate to make it fit within the gridview control (ie 3/15/2019) dr("TADate") = FormatDateTime(DateTime.Parse(txtDate.Text), DateFormat.ShortDate) dr("Donation") = Convert.ToDecimal(txtDonation.Text) DonationsTable.Rows.Add(dr) 'here we add the new row of data to the data structure (similar to SQL INSERT) 'Some analytics are provided. Here the powerful pute technique is demonstrated, with no filtering of the data (the blue Nothing statement below means no filtering used, so perform the computation on ALL the records in the column specified (here sum the values in the Donation column). This pute can be altered changing the term Sum to Count to receive a count of teh number of rows in teh table. We use tabel.rows.count to perform the same functionality. decTotal = pute("Sum(Donation)", Nothing) decAverage = decTotal / DonationsTable.Rows.Count 'again we canuse the concatenator & to bring together different pieces of data. Label1.Text = "Total of " & DonationsTable.Rows.Count & " transactions totalling " & decTotal.ToString("C0") & " averaging " & decAverage.ToString("C2") Label1.Visible = True 'the With statement places the term Gridview1 at the start of each of the rows before the end with, making the lines of code easier to read and more aesthetically pleasing. With GridView1 .DataSource = DonationsTable .DataBind() .Visible = True End With 'run the analytics procedure. that code was segmented into anther procedure as the current procedure was getting too long. Call Analytics() End Sub#End Region#Region "Analytics" Private Sub Analytics() 'Analytics are performed here to count up, sum up and average the donations by department. The methodology used here is popular, but is a lot of typing. IT is used here as it uses the building blocks taht the student shouls already be familiar with loops, select case and variables. This methodology of performing analytics is a lot of work but should be comprehendable for the new programmer. Future programs will use different coding mechanisms that are fewer more powerful words, but also more complextity. The logic is just repeated 3 times for 3 majors so it looks like a lot of code. This code sets a foundation of understanding that we leverage in later code. Dim intTAAccounting, intTAManagement, intTAMIS As Integer 'used to count the number of donations per major Dim decAccounting, decManagement, decMIS As Decimal ' used to sum up the total donations per major Dim AvgAccounting, AvgManagement, AvgMIS As Decimal 'after the looping used to average the donations per major Dim dr As DataRow 'here we create a datarow the has the schema (the column names and datatypes) built into it for the DonationsTable For Each dr In DonationsTable.Rows 'one row at a time and for each row in the table, examine the major column and depending on the major, count and add up the donations Select Case dr("major") Case "Accounting" ' we have variables to keep trace of these running totals, so increment those variables if the row is an accounting student intTAAccounting += 1 decAccounting += dr("Donation") Case "Management" 'total up the donations for the management students intTAManagement += 1 decManagement += dr("Donation") Case "MIS" 'total up the donations for the MIS students intTAMIS += 1 decMIS += dr("Donation") End Select Next 'After all the calculations are completed now calculate averages. If the # of invoices is 1 or more then calculate the average, otherwise the value for the variable for averages remains 0 which is the default value when the variable is created. If teh number of If intTAAccounting > 0 Then AvgAccounting = decAccounting / intTAAccounting End If If intTAManagement > 0 Then AvgManagement = decManagement / intTAManagement End If If intTAMIS > 0 Then AvgMIS = decMIS / intTAMIS End If txtOutput.Visible = True 'now we build the text that will be displayed to show the departmental totals. The lines that use the &= code are adding to the contents of the textbox. Be sure to make the textmode of the textbox multiline when you are planning on displaying a lot of text. As you can see this code is repetitive with the same code beign run for each major. txtOutput.Text = "Donation analysis for each major:" & vbNewLine & vbNewLine txtOutput.Text &= "Accounting: " & intTAAccounting & " donations totalling " & decAccounting.ToString("N0") _ & " averaging " & AvgAccounting.ToString("C0") & vbNewLine txtOutput.Text &= "Management: " & intTAManagement & " donations totalling " & decManagement.ToString("N0") _ & " averaging " & AvgManagement.ToString("C0") & vbNewLine txtOutput.Text &= "MIS: " & intTAMIS & " donations totalling " & decMIS.ToString("N0") & " averaging " _ & AvgMIS.ToString("C0") & vbNewLine End Sub#End Region#Region "Clear webpage" Protected Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click txtStudent.Text = Nothing txtDate.Text = Nothing txtDonation.Text = Nothing rblMajors.SelectedIndex = -1 End Sub#End RegionEnd Class ................
................

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

Google Online Preview   Download