Featherman’s ASP.NET Adventures
This web page provides learning materials that demonstrate how to create cloud-based data-driven websites, and incorporate PowerBI charts. If you read the documentation, watch the videos, and recreate the sample programs provided (pause the training videos and type the code) you can learn programming basics in one semester. After some introduction, you will create a database and save data from your ASP.NET webpage into the database (and also edit, delete, and summarize the data). The WSU online version of this course uses arrays, not SQL Server databases. The web pages you will create provide a reliable user interface, provide database interactivity to store and update data records, calculate a few important business metrics, and display simple Power BI column charts.
Many prior students have said that this class makes you understand the MIS major. You create a web page to provide a user interface for data entry and editing, save data records, and then analyze the business performance. You may think this class is computer science based focusing only on the technology, however the focus belongs on the usage of web/database/dashboard technologies to solve business problems and run business processes. Further, you learn how to create simple metrics to aid understanding of business performance.
More than ever it is important for you to learn data modeling, data management, data visualization, problem-solving skills, and to think like a coder. By the end of the class you should be able to solve business problems by designing your own ASP.NET digital webpage solutions that replace paper-based or Excel-based processes. Transitioning a business process to a more efficient, semi-automated process is called Business Process Reengineering or BPR. Think of the class as an exercise in using technology to redesign work processes thereby improving worker satisfaction, productivity, and organizational competitiveness.
Another purpose of the class is to further develop student zen-state poise. Everyone gets frustrated when developing computer programs, after a few years you get used to it. If you can develop poise and playfulness you will learn faster with more joy. The norm is to struggle, so don’t get excited. Everyone struggles, however the successes are exhiliarating!
This website is used to teach ASP.NET web-based business solutions development using industry standard Microsoft products Visual Studio.NET, and SQL server databases. The focus is on digitizing business processes into an easy to use web and mobile application. The content packaged and presented here is very rare actually in that it ties together different technologies to provide business solutions quickly. You create a website that records business transactions, updates master records such as invnetory records, produces simple analytics, and integrates PowerBI charts into a webpage.
Modules 1-3 are the introductory content, 4-6 present in-memory arrays which are used to store and update data. Modules 7-9 demonstrate database integration, and the final module 10 demonstrates integration of PowerBI charts inside the ASP.NET website. Please be advised this class does not cover the aesthetics of web design, rather the functionality and database integration.
The triple play of ASP.NET webpages, SQL Server databases, and T-SQL programming for data management and analytics is unprecedented. You use ASP.NET (the objects that make the webpages), VB.NET (the programming language used to instruct the webserver how to perform the processing), ADO.NET (the objects that connect to databses and allow data management) and SQL Server (the database) to build an operational database, transaction processing system, and basic analytics.
A new component added in 2018 builds the data structure using strongly-typed in-memory arrays called datatables. You create data structures for transaction data (fact tables) and dimension tables (with some aggregate measures) then insert data into the data structures and update the summary tables. Analytics and charts are added to the webpages. This concludes the first half of the class.
The last third of the class puts all the pieces together to make useful websites. integrates SQL Server databases using Management Studio, then ASP.NET procedures are used to validate data input, and then route that data to perform business processes such as data inserts, updates, and deletes. The transactions are saved in an operational SQL Server database either stored on the participants PC or cloud database.
You learn how to generate web-forms that facilitate data input, validate that data, and use it in business processes. The business processes generate data inputs and updates (changes to database) which trigger other procedures such as inventory reordering, customer promotions, employee payroll, and accounts settlement. In the process of solving business problems you learn how to use data arrays, SQL Server database tables, simple data structures, and T-SQL data programming. Each of these technologies are industry standard.
As of Fall 2020 semester, Sam Kite a WSU network administrator has delivered a virtual cloud-based solution
You are assumed to be an aspirational emerging business leader. You have the time, interest, and focus to learn how to solve business problems with cloud-based web development, data management and data visualization tools. If you download, learn and use the presented technologies, you can publish business applications to the Azure cloud. If you link your apps and dashboards to Linked-In, Indeed and similar career-building websites, then potential recruiters can see your work and examples of your problem solving ability. This author has heard from many Seattle-based recruiters, that having a great college GPA is not sufficient. Graduates now must demonstrate their ability to solve business problems quickly (which means systems development whether its web/database or Power BI dashboards). The challenge then is for you to post up examples of your ability to solve business problems.
No shortcuts. Embrace the struggle, perseverance always wins. Help your fellow Coug.
Watch the training videos, run the programs, read the documentation to learn how the processing magic is made possible. Attend each class, and strive to complete the programs we make in class, then code up a similar program that is your own. Have coding buddies and be helpful to your classmates when you can. Review the course material for each module, attend class, experiment with the content writing your own programs. You are given ample documentation, live URL’s, and training videos. Consume this content before you start any assignment. If you start an assignment before understanding the content (perhaps relying on copy/paste of sample code), you are in for a bad night. More importantly you are robbed of the joy of web development.
Dr. Mauricio Featherman has been teaching ASP.NET/SQL Server programming for many years at the University of Hawaii and Washington State University. He is an expert at guiding both new developers in their first class and more experienced developers as well. Dr. Featherman lives in Pullman, Washington, USA. He is available for corporate training, and consulting in web programming/database/ETL/SQL and analytics. You can reach him at email@example.com.
Let’s get started!
Common misunderstandings/problems for rookie programmers and users of Visual Studio
- Do not go to the next module until you can explain the current module to mom or dad or some other person. Very often students fail on a technical job interview because while they can hack out code, they can’t really explain the functionality in a conceptual manner. The result is that they can’t explain what they have learned.If you can explain a module that you learned by speaking in conceptual sentences (no code), then you probably understand the content. Many students fail technical interviews because they can’t really explain what they have been studying.So perhaps get in the habit of writing summary statements and communicating them, and keep refining your understanding and communication of the concepts, until you can easily make someone else understand the concepts and usefulness of your code. Many a student have failed a technical interview because they kind of knew what they were talking about, but they could not communicate it.
- Count this class as 6 credits. WSU online students – its not a good idea to take another class concurrently. Sorry you are not a super-hero, and programming is not a ‘read the book – take a test class, no cramming possible.You need time to learn coding, it cannot be crammed on Sunday night. You cannot cram code. You learn how to develop by creating lots of programs, not only the assignments. Its best to code everyday you can during the class. Do not expect to make lots of progress in one sitting. Time management is critical in this class, meaning spread the learning out over the week.
- Expect to suffer, and experience frustration before your code works. That is the nature of IT development, frustration is common. Code can be 99% correct and still not work. IT professionals have built up a tolerance for frustration, so if you want to be an IT professional, then this is a great opportunity to learn how to deal with frustration.
- Many students create their own suffering by not learning the content before starting an assignment. If you want to reduce suffering make a plan for the functionality of your project before you start. This is similar to taking a skiing lesson before getting on the chairlift.
- Expect this class to be hard, minimum 10 hours per week if you plan well, double that if you don’t plan well. If you are looking for an easy class; keep looking.
- Coding is not for everyone, many people are not detail oriented enough. MIS as a major is not for everyone, it is best suited for detail oriented people.
- Visual Studio websites use a set of files to provide the functionality. Many of these files are hidden from the developer to reduce clutter. What this means to you is:a) you cannot easily change the webpage’s file name. When you create the webform .aspx page, do not use the filename default.aspx. Rather give thought to the name, because it is too hard to change. The document at the end of this line explains the content inside your .asapx and .aspx.vb pages that need to match. FileNames
b) do not use spaces in file names, folder names, column names, etc.
c) you cannot easily copy a webpage from one project or website to another. You can copy the code, but not the .aspx interface controls.
- Worth repeating: you cannot cram code. It takes a few days to think about a plan, begin a project, etc. You will get stuck and you need time to reflect, learn, and fix your code.
- Some common rookie errors
a) do not copy and paste code. More often then not, you will forget to make some small change and then waste hours wondering what went wrong. Plus you learning will be stunted. You learn by typing. Also don’t expect to start and complete an assignment in the same day or within 48 hours. It takes time.
b) do not code without a plan, for your variables, calculations, etc. There is no GPS so you need a map.
c) the order of your code matters, use the same best practices as demonstrated.
d) don’t make up new syntax, its a good idea to print code and sit it next to you, or split screen.
e) after every line of code check it for errors (especially red squiggly lines)
f) after adding a piece of functionality, test it
g) Open the website and add webpages to it. This class does not use projects. Do not include spaces or periods in aspx file or folder names,
h) do not walk this path alone, get a coding buddy, and work with WSU tutors.
i) do not expect your professor or WSU TA’s and tutors to be available on your schedule. Rather learn their schedule and show up.
j) check email and course schedule daily for info and updates.
k) It is probably a good idea to wake up at least an hour before class so you have time to shower and wake up. Then you are more likely to engage.
l) Try to take all emotion out of your systems development efforts. Expect frustration and delay. This is the norm, ask any professional developer. Expect to suffer, and find an outlet to cleanse yourself of your day’s stress. When you make progress in your program it is exhilarating for a moment, however the norm is frustration. As you develop experience, the frustrations diminish as the work becomes more orderly. Hopefully you get a glimpse into the joy or development, and into the life of a developer. Use exercise to remove stress.
m) never ignore the red squigglies ~~~~~~~~ under any of your lines of code. These must be fixed.
n) Its pretty hard to code when you are tired. Its better to code every day than marathon cram sessions
0) Its pretty easy to destroy your immune system by lack of sleep die to programming, so again you can’t cram code
- Development software
a) Use the virtual desktop software or remote desktop software rather than download visual studio.
b) The virtual development interface is slow when using a MAC. Do not use a MAC, rather borrow a Windows computer, or work someplace where you have access to a WIndows machine.If you must use a MAC then you will have to download Visual Studio for MAC (any version) from Microsoft.com. Do some Youtube research on how to install Visual Studio and when you have to choose options, choose web developer, and webforms projects. You professor does not troubleshoot MAC. The training videos will be close to your MAC version but there will be differences.If you are a rookie IT person, it is probably a mistake to try to use a MAC to get a MIS degree. If you are pretty IT savvy, then you should be able to figure out how to install Visual Studio.
The most important advice of the semester
Getting Started Videos
The interface is a little bit different, you will not have to create a new website or project. Just open the website assigned to you. Use this video to learn about the terms.
Here is a link to an older set of videos that students still find useful in the first 2 months of the course.
Click here to see a suggested Kindle $7 VB.NET reference book
a) Intro Programming Glossary
b) This video explains the glossary
Module 1: Introductory Content – create webpages (apps) by dragging objects onto a webform, and using VB.NET code to generate the processing and interactivity. Learn about the common ASP.NET web controls used for data entry, validation of data entered by the program user (error checking), local and global variables, simple calculations.
This musical recording is recommended for your coding sessions. Especially starting at 9:30
We are building towards creating this program. Please add some sales
When you create your webpage names or folder names do not include spaces or random periods
Training Videos for Module 1
IMPORTANT: You can learn a great deal about coding by watching the videos and reading the .docx documents. While useful, this conceptual understanding does not mean you know how to program. To learn how to program you just have to type and click the mouse until you can create functionality. The way to learn for every module is to split your computer screen (or plug your notebook computer into a TV), or use dual monitors. Start up Visual Studio on one screen and play the video on the second screen. Pause the video every few minutes and re-create the same program. This is a form of guided learning. Next create other sample programs of your own design, and in the .docx files. Together the .docx files and the videos are your textbook.
Only after you gain strong experience with the new tools presented in the module, are you ready to read the assignment and make your plan to complete it.
Video #1 – covering module 1
Videos #2 and 3 review Select Case processing
Video #4 How to ‘publish’ your webpage so that the rest of the Internet can see it (building the URL for assignment submission)
Video #5 Downloading images from the web into your ASP.NET webspace
Video #6 – Can’t create the URL for your webpages – watch this vdo
Module 2: Adding Functionality and building a better HCI – Use select case to kick up the interactivity of your app
When you create your webpage names or folder names do not try to change them
3. More Interactivity – Dynamically filling a list control
a) Documentation – as you start using .selectedindexchanged events, and break webpage functionality into a number of procedures, you will have to think about your usage of global variables. If you need to use a variable in different procedures you will need to use a global variable to enable that functionality. You may assign a value to a global variable in one .selectedindexchanged procedure and then need to update that variable from other procedures. Just be sure to zero out the global variable at the end of your transaction.
b) Live link
5. Taking the next step – this program takes the next step in making a point of sale app – the invoice can be many line items. IN the next module you may get the idea that this functionality is best implemented with a loop, but the approach here is subtly genius.
b) Live link
Video 1: Textboxes
Video #2 – Checkboxes, List controls
Video #3 – Using a list control when a selection is optional vs. required – Live Link
Module 3: Loops and Timespan – Using loops to solve computing problems, performing calculations across time periods (calculating # hours worked, # days rented, # days since invoice etc.). Loops are used to run the same code over and over, for example processing payroll for all 300 employees, one at a time.
For Next Loops are common, use them if you know how many times you want to run the code, such as if you can get a count of how many times you need to run the code (such as listbox.items.count). For Each loops are a little different but the most like natural English. Do While and Do Until loops are the original loops, you will see them in legacy code and still are popular in SQL programming.
While loops are useful and deserve a place in the programmers toolbox, don’t think that their use is critical. More common is to use SQL code to operate on blocks of data rather than one at a time. Your exam requires you to implement them primarily to enable multi-select controls to work. Advanced programming does use loops but much of what novice coders use loops for can be more easily accomplished with SQL commands (learned elsewhere)
Timespan – Time and Date variables and calculations -perform date and time related calculations similar to DateDiff in Excel. For example we can create a systems that keeps track of how long a student studied for each test.
2. Solving multi-select controls using FOR EACH Loops –
When using a multi-select control, the concept of .selectedindex is not relevant. There is no .selectedindex because there are many of them.
Rather we loop through each of the items in a list and see if .selected = True
b) Live link
Primer on Processing Time and Date Data in ASP.NET
To date the usage of a date or the date datatype in our webpages has been minimal. This primer demonstrates the richness and ease of use of the new date controls and the date and datetime variables. Calendar dates that the webpage users select can easily be formatted properly. Time or date based calculations can now be performed easily using simple webpage controls and the timespan variable. A classic accounts receivable processing solution is displayed, by the webpage suggesting different business tactics depending on the number of days since the invoice was issued.
The richness of date variables is demonstrated. Once you capture a date from a textbox or calendar control, or the .now or .today functions into a date or datetime variable, you can parse out many aspects of the timestamp. Each new parcel of data that you can extract out of the date can be used to later analyze the data transactions that are saved. The data variable has many built-in functions that can generate a new formatting of the date that can be used to generate new columns of data. For example the hour of day a transaction occurred can be parceled out and put into a new column. If you connect Excel to the data saved then you can draw column charts and line charts.
Time and date dimensions are of course very critical to reporting and analysis of business performance. Many charts in Excel or PowerBI could not be created if more columns are not added to the dataset. The date variable has built-in functionality such that they can generating a multitude of dimensions each useful to analyze the data that is accumulated. Much business reporting that accountants and financiers perform leverages these and other date dimension.
Module 4: Leveraging Time/Date Calculations and Introducing Data Structures – transitioning from using stacks of variables to tabular data structures with rows and columns. Inserting transactions into data tables and generating summary datatables is covered. Datatables are the technology which makes module three (SQL database tables and SQL queries) of the course much easier to comprehend and leverage.
When you create your column names in your array do not include spaces
NOTE: The ADO.NET glossary is shown below, after the sample programs
1. Recording labor records for a construction project – Two versions of this program are provided. The first summarizes what you already can produce in module 3. The second adds the module 4 data tables. This module introduces a Microsoft invented data structure called a datatable (see view #2) which is used for data management storing summary data with running totals. Later we add the metrics to these summary tables and use PowerBI to analyze the data.
Start with this introductory video which describes arrays
a) Documentation – Construction Labor – This app uses timespan(), Weekday(), select case, and 4 global variables. Adds rows of text to a textbox to record payroll transaction records. Uses a multi-view. This document reviews prior functionality
b) Live Link
Version 2: This program has the code for HW #4 & 5 (Auto-reservation system part 1 & 2)
a) Documentation Construction-Payroll-with-DT-10-10-21
This program introduces the new content of this module. Datatables are used to organize data, and to demonstrate the classic INSERT THEN UPDATE approach. First save transaction data in one array and then update relevant summary data in summary arrays.
This final version of the software uses three data tables to store individual labor records, keep summaries by labor category, and
maintain grand totals. A new % of total metric is also demonstrated.
When adding your datatable be sure to use the syntax – as NEW datatable
b) Live Link
HW Helper Auto Rental Arrays – this version of the program is provided to assist with updating arrays. An easier methodology to load the first column of an array with textual values is provided (rather than use a loop), and an easier data update technique is demonstrated
2. Demonstration Program: Featherman’s Study Aid: Keeping track of exams and progress towards achieving study goals. This program provides another example of the insert then update methodology.
a) Study Aid 10-9-2020
b) Live Link
3. Facilitating fundraising operations – this app stores donation records into a datatable, and introduces table.compute to provide some analytics. Here no summary arrays are used, rather to provide the summary analytics, we use select case processing of each data record within a For Each loop to update a stack of global variables to generate the summary data, Prior to the invention of SQL (1969) and ability to connect Excel (1993) or Power BI (2016) to a SQL Server database table to perform analytics, programmers used loops and variables in a similar way to summarize and report data.
This program shows an older way to calculate analytics that you should know. As a learning exercise please type out this project (but not the analytics section). Change the analytics processing to use arrays and the ‘insert then update approach’ shown in the module 4 programs above.
Training videos – These next three videos demonstrate the final app possible from the module 5 apps. They are placed here so you can understand the code provided and facilitate learning of module 4 and 5. Other code within modules 4 and 5 programs show data management using methodologies derived from the first part of the class. Many different methodologies are needed to fill out your toolbox of programmer/analyst tricks. Your learning of data management and analysis techniques in these modules will facilitate your understanding of the MIS major learning of SQL technologies in the next portion of the class. This video has 3 parts
Part 1 – explaining the app requirements, user interface, and setting up the data structures
Part 2 – transaction processing – saving individual records of data. Scraping the values from the webpage and storing them into columns of the a new row being added to an in-memory data structure.
Part 3 – maintaining summary data and adding a summary metric
4. ADO.NET Glossary
a) ADO.NET Glossary 2019 – Documentation
b) ADODOTNET-Glossary Online -2019
c) Video to explain glossary – for Pullman class only (has material not relevant to MIS 325 Online version)
Module 5: Leveraging data structures to monitor business operations – this module provides additional INSERT THEN UPDATE exampled adding further data management and summary analytics. WU global students should dive into this module, however for Pullman students the new content demonstrated will be discussed according to schedule constraints. The additional features of the dataview and table.compute are presented, similar functionality presented in future modules using SQL connectivity.
1. Corporate payroll system for three states – this app uses datatables to store labor records at both individual transaction level (highly granular data) and summary level (aggregated data). Records are added to datatables, and other records are updated. Recording transactions digitally, and aggregating them based on business dimensions (previously labor grade, here state) is the historical foundation of programming. The result is increased business understanding and process efficiency.
This app requires some arduous learning, but is a better use of your time than this prior version of the software which uses stacks and stacks of variables and labels to provide the functionality. Programs with many objects require increased attention to organization (problematic). Programs that are complex and grow into hundreds of lines of code introduces errors. Here is the documentation of an easier version of the software but which also is too many lines of code. Better to use data tables. You can compare this old code.
2. Facilitating fundraising operations – part two – this app compiles several summary and donation metrics using table.compute calculations (similar to GROUP BY query). To calculate the totals for each major that can be selected from a radio button list, we use a table.compute calculation within a FOR EACH loop, The result is nicely formatted rows of compiled metrics added to the summary data table. Because we hope for many donations we introduce a new object the dataview which is useful to filter the transactions table for example to only show the donations for MIS majors.
Other programs use the insert, then update the summary tables methodology. This app uses a separate procedure to build a table of summary categories then uses table.compute calculations to calculate and populate each column of the table, one row at a time (ie one major at a time). Table.compute is a great tool to total, count, or average a column of numbers. Table.compute is not so great when used with a filter as the code is tricky and pros use a simple SELECT FROM WHERE SQL statement.
Insert then Update Approach to Refreshing and Displaying Summary Data
This program provides another example of how to update one row of data (running totals) in a summary datatable. You are shown two ways to generate summary data a) perform a table.compute operation on a column of numbers in your transaction table and b) update the necessary rows of data in different summary tables as part of recording a transaction the classic insert and update approach. While a fancy filtered table.compute is shown it is not quiz material.
For example, a) after a retail sale is recorded, many numeric and date columns in the customer record need to be updated to reflect the business with that consumer, b) columns of inventory need to be updated in the inventory table (which can kick off purchasing transactions if inventory levels drop below reorder point and c) accounts receivable data also need updating.
3. Collecting orders for a fundraiser
a) Documentation – a trick is used when setting up the columns of the summary datatables. The items collection of list controls are looped to place these values in a column of the new summary data table. BE SURE to put this code in the page_init procedure. A simpler approach is also demonstrated
b) Live link
Using Arrays for Data Shaping and Reporting – demonstration only
4. Using arrays to reformat data – There is considerable new content introduced here that is outside the scope of this module, so this program is for demonstration purposes only, and designed for the interested student. This program was a request from Troy Morrow a 2020 Accounting/MIS student. This webpage creates an aging report by looping each of the sales in a Sales datatable, and building a an array with the columns 0-30, 31-60, 61-90, 91-145, 146-180. The invoice amounts for the unpaid invoices are categorized and summarized into the time buckets to build an aging report. A CASE statement inside a loop is used to pour the unpaid invoice balances from one array into another to compile and condense the data. To take it one step further the aging report is calculated for each customer, with one row in the new array being compiled and presented for each customer.
a) Aging Report Example 5-4-2020
b) Live link
Module 6 made for MIS 325 Online students only:
A Data management, data analytics and data visualization project using arrays. This is the final module for the online course, so it needs to be epic and flush out the arrays-based development possibilities. You will again how to insert transaction records to a fact table, then update measures on summary tables. Analytics are added to improve reporting using gridviews. Also charts are used for data visualization (it is unsure whether using charts will work in the virtual desktop software). The sample project provided here gives the student a model to plan their own final projects.
1. Adding more metrics and charts to the order collection program
b) Zip file: download, UNZIP, then run the project. No live link possible. Be sure to unzip the program fully onto the computers desktop first before running it. Enjoy the movies, they are paced slow enough for you to sit and type the code (if you just copy/paste code you won’t learn much) at the same time, reading from the documentation.
c) Training videos:
1. Charting in a LocalHost ASP.NET Webform – Video #8
2. An intro to charting video in your .aspx page. The last video of this module also shows the process again.
Videos to show the entire webpage creation
3a. This next set of videos start by creating a new project. Projects is an older methodology that has been replaced by using webpages.
Disregard any usage of projects and and continue to use the File | Open Website methodology.
Watch this first video to get insight into setting up the multi-view project, and using image controls
3b. Setting up data structures – adding columns to the datatables: a) for goal setting b) for new transaction records, c) For metrics and maintaining running totals
3c. Adding rows of data to the datatables, populating a radiobutton list in code
3d. Updating data in a summary data table
3e. Adding metrics to your data table using code – numeric and textual metrics (using select case to explain project performance)
4. Complete the webpage by adding Charts using the Microsoft Chart Control
Module 7 – Selecting, retrieving and displaying data from the SQL Server database
Modules 7,8,9 together explain and demonstrate how to connect your ASP.NET webpage to a set of SQL Server database tables. It takes some time to learn this content, use repetition until each process makes sense.
Before digging deeper into the code – read these overview documents that explains what we are doing in modules 7,8,9. Refer to these documents often!!
- ADO.NET-Overview 4-17-20 – This document explains the web-database interaction code
- Connecting ASP websites to Databases – This document attempts to dispel any mystery of how your ASP.NET webpage connects to your SQL Server database.
- ADODOTNET Glossary 2019
In module seven, you are shown how to retrieve filtered data from a database table and display it in a gridview (if the retrieved data has many columns), or in a list control (if you want to display one attribute of a dimension – 1 column, and have access to the corresponding primary key value). Technically the topic is termed parameterized SQL SELECT statements. You will want to pull and look at filtered data:
a) to display some rows of data that are being analyzed (e.g. the products in a product line)
b) to display the row(s) of data that you want to update
c) to display the row(s) of data that were just updated to verify the edits were effected
d) to display some compiled data with metrics added
1. Running SQL SELECT statements to retrieve compiled lists from the database. This is demoware designed to give you ideas. Code outside the scope of the class – so just take a look to spark your imagination, then move to the next sample program to learn how it works
a) live link
2. Now we have 2 servers that are talking to each other, the ASP.NET web server, and the SQL Database Server. Here is a linked image that attempts to explain how the two servers talk to each other.
3. Retrieving filtered lists from the database – here different scenarios for data retrieval are demonstrated. Each semester the WSU server number changes, so the SQLconnection on the .docx files needs to change as well. Currently Featherman_Analytics is on DEVST05.
b) Live Link
4. Start of a better pizza ordering system: Loading database content into the webpage to ease programming – demonstration only
This program is presented here to give you an idea of functionality that we will use in a future module. The program demonstrates the auto-loading of datatable content into web controls and tables allows your program to stay up to date by itself.
1. Running parameterized SQL queries.
2. More training videos: Look at topics 7, 8 on this resource
Module 8 – Saving data to SQL Database tables, and updating data in SQL Database tables
More training videos: Look at topics 7, 8 on this resource
Starter Project – Create these tables in your database – TablesForMIS325 – Now its time for you to create some database tables and then type some data into them. By the end of this module you will be saving new rows of data into these database tables. You will learn the classic ‘INSERT THEN UPDATE’ procedure. First insert a new row of transaction data into the sales table, then update some running totals in the customers table. While it is helpful to put some ‘running totals’ in the Customers dimension table, most reports of sales activity are created by compiling the transaction data.
Follow these steps to create the Customers and Sales tables
- Using SSMS create and save the Customers table, refresh SSMS to see the table in the object explorer. Set the primary key.
- Next use the EDIT TOP 200 option and save three rows of data into the Customers table.
- Using SSMS create and save the Sales table, refresh SSMS to see the table in the object explorer.
Set the primary key and foreign key. Save the schemas and close those views.
- Next use the EDIT TOP 200 option and save three rows of data into the Sales table.
Caution: Two assignments are used to aid your learning, mastery and utilization of the webpage-database interactivity and the abundant functionality provided by a data-driven website. When you work on these assignments you are STRONGLY advised to follow the programming patterns shown in these examples, rather than freelance and design your own patterns. When writing programs that interact with a database it is very important to run code in a certain order. Please follow the best practices provided rather than be innovative in your usage of this webpage-database connectivity content. Add your inattentiveness only after you learn the data interactivity patterns, and you have successfully implemented a few projects. Follow the patterns in the documentation to learn the content 10x more efficiently allowing you to complete assignments on-time. Many previous students did not heed this advice, experienced prolonged sleep deprivation, and damaged their immune system, resulting in sickness, susceptibility to infection, and spreading of disease. Please maintain a healthy programming lifestyle in this last section of the course.
Sample Projects – the first two videos show an interesting data retrieval technique
- Simple SQL Statements for Analytics: Retrieving one value INTO your webpage FROM a database using a command’s executescalar method. If you want to retrieve a set of data records into your webpage use a dataAdapter (as in the last module). When you want to retrieve just one number then use a command and executescalar method to (often calculate and) pull one value from a database into a global variable.
2. Retrieving values from local arrays
Retrieving a value from a global datatable into a local procedure? Often you need one value from the database, such as a price or the VIP status of a customer.
This vdo shows two ways to do this a) retrieve from database, b) retrieve from local array
3. Saving records of business transactions and providing analytics by compiling the database transaction records. This program provides in-depth explanation of the data entry objects and concepts, and continues the construction payroll example. Here new rows of data are added to an underlying database but no updates of data records are performed (such as incrementing running totals), rather totals are calculated from the transaction data using SQL queries.
a) Start here for data entry and editing – Here is the documentation that demonstrates Saving-and-Updating-rows-of-data 4-13-20
This program leverages the connection to SQL Server database tables, by introducing two main SQL programming methodologies to quickly provide analytics inside the web page by compiling and aggregating transaction records (in essence performing table.compute calculations in the database rather than in the web page). The SQL GROUP BY and PIVOT queries that are introduced are outside the scope of this class, but included here for exposure. If you are intrigued you can self-study here. The current class does not formally teach these concepts for examination, and rather provides similar functionality by incorporating PowerBI reports into ASP.NET web pages. A PowerBI chart or table can provide the same results as the demonstrated SQL functionality. There is no need to jump into SQL analytics at this point in your learning, unless you need the content quickly.
b) Live link
Documentation that demonstrates an interactive Chart Control
4. Another example of Inserting Data with SQL Data Adapters and Updating data with SQL Commands – Here is another example closely related to in-class learning on saving and updating data using ADO.NET objects. Now we leverage our understanding of data tables and adding rows to them to quickly save data using the dataAdapter.update methodology. We again review one of the most important computing concepts of the semester, updating running totals using SQL UPDATE SET commands.
a) Documentation – Saving-Rows-of-Transaction-Data-to-SQL-Server-Databases 4-19-20
b) Live link
5. An extended example using a Customers have Sales metaphor. THIS PROGRAM HAS EVERYTHING!
Here you create SQL Database tables, Insert transaction data, and update current values in dimension tables (two ways!). Also introduced for the first time is archiving and deleting data. This content covers the INSERT/UPDATE/REFRESH and ARCHIVE/DELETE/UPDATE/REFRESH paradigms.
Aspects of this program include a) Load DDL and refresh of customers names used in inserts and updates b) Insert new rows of transaction data from a business process, c) use data from a business transaction to SQL UPDATE columns of metrics in a hybrid dimension table, d) deleting records, e) preventing the same customer from being saved two times, and, f) performing analytics and g) retrieving filtered lists
a) Documentation – Mod8Program3
b) Special documentation covering deleting records Deleting records
c) Flowchart explanation of deleting records Powerpoint version – DeleteRows , PDF version – DeleteRows
d) Live Link
Explaining the INSERT/UPDATE/DISPLAY processes
Using a Microsoft Chart Control inside your webpage (disregard comment to create a new project, you can add the chart control directly into your webpage)
Module 9 – Navigating Rows of Data, Updating and Deleting one Row of Data and Password Protection
1. Choosing and editing one record for update – Here three different HCI interfaces are demonstrated each able to pull one row of customer data into row 0 of a data table, and then parsed into the controls on a webform. The data is displayed in web controls, and also displayed in a gridview. Each interface includes an update procedure that takes the values in the webpage controls and drops them into the columns for the row in the data table and saves the data back to the SQL Server database record.
2. Password Protecting your Website – Here you are shown how to add a password protection to your website.
Module 10 – Adding PowerBI reports to your website
In this module you learn how to create and publish a few simple PowerBI charts within an ASP.NET project. The column chart that uses a dimension variable (such as product or customer name) on the X-axis is actually running a SQL GROUP BY() query behind the scenes. Some useful SQL for analytics were demonstrated in prior modules — GROUP BY() and PIVOT() — and these are replicated using POWERBI’s column charts and matrix tables.
Sample end of semester project
Construction Supply Store Shopping Cart POS System – This program uses four SQL Server tables (grouped together by a cohesive data model with primary key and foreign keys) and four views to provide the next evolution of a point of sales system. When sales are registered, summarized, and recorded, SQL UPDATE SET commands are used to reduce inventory, and update metrics tracked for each customer focused on transaction recency, frequency, monetary effect, and supply chain effect.
The breakthrough with this last program is the demonstration that you can design your own database tables and arrays to suit your needs. Here we pull columns of data from different tables into a shopping cart (line items) table that both gives the webpage an array to manipulate (add/remove line items into the shopping cart) and a database table to hold the data. When the array used for the shopping cart is the same schema as a database table, then saving data is easy. So start your projects by planning the data model and table structures, and then create the webpage that can push data into these database tables.
Explaining the Data Model
Running the Program
Explaining the Code