Title: Develop Web ERP Software Using VS 2015

web erp book with database programming book

Table of Contents

The objective of this book is to teach the secrets of developing a Web ERP software application using ASP.Net and C#.

Chapter : 1. Introduction to Visual Studio 2015

  1. Understanding .NET 2015 components
    • .Net framework 4.6
    • .Net Core 1.0
    • Core CLR
    • .Net Native
    • Compilers
    • ASP.NET Core 1.0
    • Universal Windows apps
    • ASP.Net 4.6
  2. Introduction to ASP.NET Web Forms
  3. Deciding when to create a Web Forms Application
  4. Advantages of a Web Forms-Based Web Application
  5. Working of ASP.Net Applications
  6. Advantages of an MVC-Based Web Application
  7. ASP.NET 4.5 features
  8. New features in ASP.NET Web Forms in .NET 4.6
  9. Creating ASP.Net 4.6 application in VS 2015
  10. Code behind page model
  11. ASP.NET Page Syntax
  12. Overview of ASP.Net server controls
    • Using Labels and TextBox controls
    • Using Button control, LinkButton and ImageButton
    • How to wire multiple controls in a page to the same event handler
    • Using ListBoxes and DropDown Lists
    • Creating Multiple-Selection List boxes
    • Get the selected Item in the single-selection ListBox control
    • Get the selected Items in the multiple-selection ListBox control
    • SelectedIndexChanged event of ListBox control
    • Creating ListItem objects and adding them to a Control using the Add() method
    • Removing items from a multiple-selection ListBox control
    • Using CheckBoxes and RadioButtons
    • Get the Selection in a RadioButton Control using Radio button click event
      • Check Box control
      • RadioButtonList and CheckBoxList controls
      • Get selected items from the CheckBoxList control
    • Validation Controls
    • RequiredField Validator
    • CompareValidator Control
    • RangeValidator Control
    • Regular Expression Validator Control
    • CustomValidator Control
    • ValidationSummary Control
    • Using Validation Groups

Chapter : 2. Data Access in ASP.Net Application

  1. The SqlDataSource control
  2. How to Connect to a SQL Server Database Using the SqlDataSource Control
  3. Creating a SqlDataSource control declaratively
  4. Storing Connection String in Web.config file
  5. Executing Data Commands with the SqlDataSource Control
  6. Binding a ListBox Control to the SqlDataSource control
  7. Setting DataSouceMode property
  8. SqlDataSource control Events
  9. Using parameters with SqlDataSource control
  10. Properties of ASP.Net parameter objects
  11. SqlDataSource control Collections
  12. How to set SqlDataSource's parameters before the select command is executed
  13. Using the ASP.Net ControlParameter Object
  14. Overview of GridView control
  15. Properties of GridView control
  16. GridView Events
  17. GridView Methods
  18. Display, edit and delete data in the GridView control using SqlDataSource control
  19. Programmatic DataBinding in a GridView Control
  20. Formatting GridView control
  21. Formatting GridView control with CSS
  22. Using fields with the GridView control
  23. Using BoundFields
  24. How to Format date and currency column using DataFormatString property
  25. Using Command Fields
  26. Using Template Fields
  27. How to use Eval() and Bind() method
  28. Display a DropDownList when Editing in the GridView using EditItemTemplate
  29. How to insert new data into the database with GridView control using FooterTemplate
  30. How to restrict an item from being selected using GridView SelectedIndexChanging Event
  31. DetailsView and FormView control
  32. Using Fields in a DetailsView control
  33. Using style elements in a DetailsView control
  34. Creating a DetailsView control
  35. Data Binding with the DetailsView Control
  36. Using DataSourceID property
  37. Using DataSource property
  38. DetailsView Properties
  39. DetailsView Events
  40. Using Command buttons in DetailsView
  41. Using Templates in a DetailsView control
  42. How to create Master - Detail page by using GridView and DetailsView control
  43. FormView control
  44. FormView vs. DetailsView control
  45. How to use FormView control to update and insert data

Chapter : 3. ERP Domain

  1. Introduction
  2. Technologies for ERP software
  3. Platform suited for creating ERP software
  4. ERP software overview
  5. ERP software implementation issues
  6. Resources required for implementing ERP software
  7. Benefits of ERP software
  8. SAAS and ERP
  9. Role of softwares in an ERP system
  10. ERP software and standardization of business processes
  11. Working of the Finance department
  12. Working of the Sale department
  13. Working of the Purchase department
  14. Working of the Manufacturing department
  15. Working of the Inventory department
erp asp application development server controls, web forms, data accesa controls, binding server controls, master detail form

Chapter : 4. ERP Application Architecture

  1. Benefits of Multi-tier Architecture
  2. Purpose of Presentation Layer
  3. Purpose of Business Layer
  4. Purpose of Data Access Layer
  5. Purpose of Data Layer
  6. Creating Web ERP Application Framework using Visual Studio
    • Creating a Class Library for the Data Access Layer
    • Creating a Class Library for the Business Layer
    • Creating a Web Application for the Presentation Layer
  7. Developing Data Access Layer in C#
  8. Creating DataServer Class
  9. Constructor of the DataServer class
  10. DataServer Class Methods
  11. Introduction to Master pages
  12. Creating a Master page for the ERP Web Application
  13. How to place a Menu control on a Master page

Chapter : 5. Developing Finance Module

  1. Business processes in Finance Module
  2. Presentation Layer of Finance Module
  3. Creation of Accounts
  4. Designing and Programming the AccountsDialog.aspx
  5. Managing ASP.Net Navigation using Server.Transfer method
  6. Send a parameter to another page using Server.Transfer method
  7. Designing and Programming the AccountsForm.aspx
  8. Bind the DropDownList control to a ObjectDataSource control
  9. Using SelectMethod property of ObjectDataSource
  10. TypeName property of ObjectDataSource
  11. OnSelecting property of ObjectDataSource
  12. Import the namespace of Business Layer
  13. Retrieve Query String Values in Page_Load() event using Request.QueryString
  14. Set ObjectDataSource's parameters using Selecting event - ObjectDataSource1_Selecting
  15. Save the Account Details
  16. Creating Business Object - Account.cs
  17. Retrieve Groups
  18. Steps to interact with the DataAccessLayer
  19. Save the Account details
  20. Connecting AccountForm to the menu
  21. Creating Customer, Supplier, Bank and General Account using the AccountsForm.aspx
  22. Chart of Accounts
  23. Designing and Programming Chart of Accounts.aspx
  24. Placing a TreeView control on a ChartofAccounts.aspx
  25. Populate the root level nodes in Page_Load() event
  26. Populate the child nodes of a given node
  27. Program the TreeNodePopulate event
  28. Creating Business Object - Group.cs
  29. Return main groups
  30. Return only sub-groups under a particular group
  31. Designing the JournalForm.aspx for the Journal Voucher Transaction
  32. Steps to design the JournalForm.aspx
  33. Create and set important properties and methods of the GridView control
  34. Create Template columns in the GridView control using asp:TemplateField element
    • ItemTemplate
    • EditItemTemplate
    • Footer Template
    • Create a Template column for the AccountName column in the JV
    • Create a Template column for the Debit column in the JV
    • Create a Template column for the Credit column in the JV
  35. Create a Template column for 'Insert' Button
  36. Create a 'Delete' Command button and 'Edit' Command button
  37. Create EmptyDataTemplate to accept first row of data when datasource is empty
  38. How to place the ObjectDatasource control, Transaction number, Date and totals of Credit and Debit column amounts on the form
  39. How to insert multiple accounts in the GridView control
  40. Steps to program the JournalForm.cs
  41. Building the DataTable Structure for the GridView
  42. Program the Page_Load event to bind the DataTable to the GridView control
  43. Program the Add New Record button to add a new row of data to the GridView using EmptyDataTemplate
  44. Access the controls placed in the EmptyDataTemplate
  45. Program the Insert button to add a new row of data to the GridView using FooterTemplate
  46. Retrieve the values of controls (DropDownList and TextBox controls) which are in the Footer Template
  47. Retrieve existing accounts from the Session object and assign to DataTable
  48. Save the DataTable into a session
  49. To get the value from a particular cell, which is a TemplateField
  50. Program the Event handler for the EditCommand event
  51. protected void grdAccounts_RowEditing(object sender, GridViewEditEventArgs e)
  52. Program the Cancel button
  53. protected void grdAccounts_RowCancelingEdit(Object sender, GridViewCancelEditEventArgs e)
  54. Program the Event handler for the DeleteCommand event
  55. protected void grdAccounts_RowDeleting(object sender, GridViewDeleteEventArgs e)
  56. Delete a row from the DataTable
  57. Commit all the changes made to this DataTable
  58. Assign DataTable as DataSource to the GridView and bind the GridView
  59. To clear the selection of a row, we set the SelectedIndex property programmatically to -1
  60. Program the Update button
  61. protected void grdAccounts_RowUpdating(object sender,GridViewUpdateEventArgs e)
  62. Program the function which keeps a running total of the debit amounts of the accounts
  63. Program the procedure for checking Double Entry rule
  64. Program the Save button
  65. Designing and Programming Voucher/ Receipts Transactions
    • Cash Voucher
    • Cash Receipt
    • Check Voucher
    • Check Receipt
  66. Creating Business Object - Transaction.cs
    • Return Cash and Bank Accounts
    • Return Accounts based on menu selection
    • Save transaction details and return boolean value if transaction updation is successful

Chapter : 6. Developing Sales and Purchase Module

  1. Business processes in Sales Module
  2. Design and Program the OrderForm.aspx for creating Sales orders and purchase orders
  3. Place the DropDownList control and bind it to the ObjectDataSource control to display customer names
  4. How to insert multiple items in the orderForm.aspx
  5. Perform Validations in order Form
  6. Save Sales order /Purchase orders
  7. Update Order details
  8. Program the function which calculates the order total
  9. Program the function for calculating order net amount
  10. Designing and Programming ordersGrid.aspx
  11. Pick and Pack Sales orders
  12. Pick Shipping orders
  13. Back orders
  14. Pending Purchase orders
  15. Designing and Programming InvForm.aspx
  16. Program the function which calculates the invoice total
  17. Program the procedure for checking Double Entry rule
  18. Program the Save button
  19. Validate the TextBox control placed in the Footer using TextChanged event
  20. Validate the TextBox control placed in the EmptyDataTemplate using TextChanged event
    • Programming Purchase Invoice
    • Programming Sale Invoice
    • Programming the Cash Sales
    • Programming the Cash Purchase
    • Programming Purchase Returns
    • Programming Sale Returns
  21. Business Object - Trade
    • Retrieve Customer/Vendor Accounts
    • Retrieve Trade Accounts from the database
    • Retrieve Discount, Freight and Tax Accounts
    • Saving the Transaction details

Chapter : 7. Developing Inventory Module

  1. Business processes in Inventory Module
  2. Designing and programming the ItemForm.aspx for creating different types of items - Raw Material, Consumable, Sub Assembly or a Finished Product
  3. Designing and programming the MaterialIssueForm.aspx to issue components to manufacture products
  4. Business Layer of Inventory Module
  5. Business Object - Item.cs
    • Retrieve Stock Groups from database
    • Retrieve Item types
    • Save the Item details
    • Business Object - Issue
      • Return information about single Work order
      • Saving Issue details

Chapter : 8. Developing the Manufacturing Module

  1. Business processes in Manufacturing Module
  2. Designing and Programming BOMForm.aspx for Creating Bill of Materials
  3. Designing and Programming WorkOrderForm.aspx for creating work orders
  4. Business Layer of Manufacturing Module
  5. Business Objects - BOM
    • Return information about all Items
    • Return list of Poducts
    • Return Components for a parent Item
    • Save Product Structure
    • Create product structures using BOMForm.aspx
    • Display product structures using BOMDisplay.aspx
  6. Business Objects - WorkOrder
    • Return names of Products
    • Return Information about Components and their Quantities which are required to manufacture one parent item (product)
    • Save Work Order details

Let us discuss a few learning objectives of this book

  1. Hierarchical classification of Accounts and Groups
  2. ERP Domain
  3. Schema
  4. Role of ASP.Net GridView control in the Web forms to record business processes in a Web ERP software
  5. Validations in ERP software
  6. Essential skills to develop the ERP application using this book
  7. ERP modules
  8. Layered Design
  9. Reuse of software components

Hierarchical classification of General Ledger Accounts and Groups in the ERP system

Essentially, the Chart of accounts is a list of all the Accounts with their Accounting groups in an ordered form.

In the shown Chart of Accounts template, main groups and their subgroups are listed in a hierarchical order.

chart of accounts animation displaying expanded balance sheet and profit and loss statement

At the top level, groups are classified into two main sections. Groups of Balance Sheet and other is Groups of P/L statement.

The Chart of accounts given in this book is a comprehensive template and can be easily customized to any type of organization including adding ofadditional groups at any level.Note that it is an Accounting requirement that any new Account which is entered into the system should belong to a group or a subgroup under the given groups in the COA.

Software such as SAP, Microsoft Dynamics, Sage and Oracle Financials or any open source ERP havetheir own implementation of Chart of Accounts (COA). However, all of them adhere to a generally accepted principles. The flexibility to design a customized COA ensures that a company and its management can meet the requirements of the stakeholders.

In fact, a flexible, sophisticated Chart of Accounts is a key factor for the success of an ERP software.

What is the need to create hierarchicalclassification of General Ledger Accounts and Groups in an ERP system?

Classification of General Ledger Accounts under proper Group heads is essential for multiple reasons.

The most important reason is to extract detailed and meaningful Financial Statements which are seen by the various stakeholders such as the top management, investors, lending institutions and regulatory authorities.

The groups and subgroups are used to logically group different accounts. Additionally, the group category determines the format in which the accounts and their amounts should appear. It also determines whether a particular account should appear or not in the transactions and reports.

The next important reason for a proper COA is that correctly classified and stored data is vital as it is used by all other modules of the system.

A good classification system should be customizable for individual Country’s Accounting system needs.

In the book, you will learn, how to program creation of Customer accounts and Vendor accounts and other accounts listed in the Table of Contents. When a user creates Vendor account, the software will automatically create and place this account under Accounts Payable group. Similarly, the Customer accounts are created under Accounts Receivable group.

Need for enhancement

In every company’s existence, the need for enhancing the Chart of Accounts arises frequently as stakeholders demand particular information from time to time.

The Finance department may require a modified and enhanced classification of accounts to generate reports for a new project or increased lending.

An example of modification of COA is to group Customers data region-wise, district-wise and city-wise from a groupingwhich only is region-wise.

Another example is changing the way the stock is classified from a simpler classification to a detailed classification which can be raw material stock, finished goods stock and machinery spares stock etc.

Enhancing of Chart of Accounts is done by creating groups under the groups present in the COA template.

Extracting a detailed Financial Statements is possible only with a flexible and sophisticated COA classification.

A well designed Chart of accounts classification lays a good foundation for programmers to build sophisticated ERP systems. A poorly designed COAresults in a Financial module which can become a bottleneck for smooth functioning of the organization.

Most software projects involve a financial module as its core and pricing for such projects multiplies depending on the size of the project.

ERP projects with multiple modules require seamless integration with the Financial module.

Learning to build a Financial systemfor a project from the scratch is time consuming and a laborious task often requiring months of work. With proper guidance given in the book, building the Financial module becomes easy and integration with SCM, CRM, Purchase and Sales modules becomes seamless.

The Chart of Accounts givenin the book can be used for commercial projects.

Programming the COA can be seen from in three perspectives

  1. Database Schema
  2. Web Forms implementation
  3. Display Chart of Accounts

Building the COA begins with Database schema

Two tables, Groups table and Accounts table play a pivotal role in the implementation of Chart of accounts.

Setting up relationships between these two tables using keys is an important step to ensure that integrity of the Accounting transactions.

When a new group or an account is created, it should be automatically and correctly placed in the system.

You will learn this entire process in the Chapter 4 of this book.

Web Forms Implementation

While the COA can be sophisticated, the Web form which is used to record the creation of general ledger account or a group has to be simple and intuitive, so that end users find it easy to use the form.

This is made possible by the use of advanced features given ASP.Net, such as ASP.Net Server controls and Validation controls.

We use a web form to record creationof Customer Account.Typically Customer accounts have name, address, credit limit etc. We also need to specify to which group the account belongs to.

By default, all the Customer accounts belongto Accounts Receivable Group.

We also need to facilitate the system to place the Customer accounts to any of the Accounts Receivable sub groups.

The validations incorporated in this web form takes care of displaying proper groups in dropdown control of the Accounts Web Form.

A step by step explanation is given in the book to implement all of the above including creation of Account class, Constructor, Class methods to retrieve correct groups from the database, using methods in the Data access Layer and display them in the dropdown list control of Web form.

Display Chart of Accounts

To understand how accounts are classified under group heads in the ERP system, a tabular display of COA is not useful.

Accounts such as Customers, Vendors, Stock and Asset accounts and other Liability accounts have to be displayed in a hierarchical fashion so that relationship between each one of the above is clearly seen.

Using the TreeView control, you will learn how to display Chart of Accounts with groups and accounts in a hierarchical fashion clearly showing their parent groups.

As shown in the picture, when we expand the BALANCE SHEET node, the main groups under this group are displayed. Similarly, when we expand P/L Statement node, the main groups under this group are displayed.

The steps to program the COA is given in the Financial module of the book.

ERP domain

Hundreds of transactions occur in a company and an ERP system should be able to record all the various types of transactions.

A good ERP system can be created with the thorough knowledge of:

  1. Working of each department and its business processes
  2. Detailed analysis of each of the business process
  3. The data to be recorded in each and every business process
  4. What is the input given to the business process and the output expected
  5. Data entry forms to be designed to record each business process
  6. Sequence of business processes occurring in each department
  7. Reports generated.


business processes in the purchase department of an organization ERP domain covers all of the above and is fully dealt with in the Chapter : 3.

Schema

The ERP schema is the logical organization of ERP data including the tables for the Financial Accounting module, Sales module, Purchase module and Stock module including relationships and Stored Procedures.

An ERP schema scriptis given to you with the books and when you run it, tables with data required to initialize the ERP schema system is created.

The created Schema will include default fixed groups and accounts and their relationships established to maintain a successful ERP system.

erp schema of the accounts and groups table Module wise schema explanation given in the Chapter 4 gives a broad perspective so that you can relate business processes and the table structures in the database schema.

You can also customize the given ERP database Schemato add additional fields in the tables. For example, in the customer and sales invoice table, you may want to add additional fields to record varied data and extract in-depth reports.

Role of ASP.Net GridView control in the Web forms to record business processes in a Web ERP software

We use Web forms with GridView control extensively to implement transactions in the ERP application.

design view of voucher form after placing the gridview objectdatasource control Data is recorded using simple fields such as DropDownList, Textbox controls, Date control and Radiobutton controls etc.

Along with these, GridView is used to record transactions which involve data with one-to-many and many-to-many relationships.

GridView control is used extensively in the application to design the Vouchers, Receipts, Sales invoice, Purchase Invoice and Orders.

A Sales Invoice in the ERP system involves data such as Invoice No, Date on which sales invoice raised, Customer and the multiple Stock Items sold.

In real time usage, multiple items are added using the Sales Invoice. This data can edited or deleted before and after saving the Sales Invoice transaction.

The need for recording multiple items arises as all transactions in an ERP system have to accept multiple rows of data items and their Quantities and Values. This can be managed easily with the GridView control.

The GridView control’sFooterTemplate, ItemTemplate, EditItemTemplate and InsertTemplate enables creation of sophisticated data entry formsand customizing them.All these are covered in chapter2:

Validations in ERP software

Why are validations are critical in software systems?

  1. To maintain the integrity of the system
  2. To ensure that data entry errors are minimized
  3. To speed up the process of the data entry
  4. To reduce the possibility of system failures


Validations can be enforced in the back-end, front-end or both

A good ERP implementation uses a combination of back-end and front-endvalidations.

Implementing validations for maintaining Double entry in the Financial module of the ERP system is a critical task.

An expert ERP programmer would know which validations have to be implemented at the front end and those which have to be implemented at the back end.

This topic is dealt with in detail in the book with clear programming examples.

Back-end Validations

One common method developers use to implement validations is by using Primary Key and Foreign Key constraints.

Foreign Key constraints ensure Referential integrity by checking any change in the Primary Key value which corresponds to the value in the Foreign Key constraint in another table.

Another way of implementing validations is by using Stored Procedures. Validations are essentially ERP business rules which are executed before insert, delete and update operations.

You will learn how to program and implement many ERP Stored procedures in the application.

You will also learn how to call SPs from the Data Access Layer.

Front-end validations

One common method programmers use to perform validations in the front end is by using Web Forms. Validations in the web form are executed before data reaches the backend.

ASP.Net provides server control and validation controls to implement validations.

For example, DropDownList control is used to restrict data to be chosen and also restrict unwanted data display. This is a simple but very useful technique when designing the ERP user interface.

In a Sale Invoice form, Customer accounts existing in the Accounts Receivable group should only be displayed in the dropdown list. This ensures proper selection of Customer account in the Sale Invoice transaction by the user.

Implementing Double Entry requires extra validations as finances are involved. validations front end and back end validations In Financial Accounting transactions such as Vouchers, Receipts, many validations are implemented, one such validation is a minimum of two accounts should exists in the transaction, and another is both debit side totals and credit side totals should be equal and amounts should not be negative.

You will learn how to manage these business rules under the hood so that the user will not enter incorrect transactions.

Implementation of the above is clearly explained in the book.

Essential skills to develop the ERP application using this book are:

  1. Basics of C# and OOPs
  2. Relational database SQL server
  3. ASP.Net programming
  4. ERP domain
Before you start developing an ERP application, basics of C# and OOPs are essential.

Understanding of Relational databases like SQL server, creating database and Tables and writing stored procedures is also essential.

You will get thorough understanding all of the above in the book. Every ERP system has a solid backend like SQL server or Oracle.

Familiarity of using ASP.Net Data Access controls to access the data from the SQL server will be useful when developing the application.

ASP.Net Programming knowledge is also essential and can be learnt from this book itself in the initial chapters such as working with ASP.Net Server Controls and Validation Controls and Data Access controls.

All these are explained with examples to make it easier for you to develop the ERP application.

ERP Modules

ERP modules cover implementationof User Interface, Validations, Business Functionality, Data Retrieval and Data Saving and Integration with other modules.

The five ERP modules are Finance, Purchase, Sales, Inventory and Manufacturing module.

While implementation of each module is important, integration of one module with the other is also equally important and is discussed in the respective chapters.

modules in an erp system and organization, all five modules

Of all these modules, implementing Financial module is crucial. The reason being all other modules interact with Financial module in real time.

Software Developers often take some timeto understand how to implementaFinancial system. This is because of lack of a background of Accounting.

Companies emphasize perfect Financial systemsto ensure monies are safe. Tracking expenses in all departments and income from sales department is vital.

A combination of accounting knowledge and technical skills to implement accounting rules is the foundation for creating a Financial module. Taking note of this, the books cover Financial module implementation in extensive detail.

Layered Design

Web based ERP software consists of three layers –browser based user interface implemented with ASP.Net 4.6, Business Layer and Data access layers are implemented in C#.

Implementing ERP system based on Layered architecture makes the system more scalable and flexible to change with business.

web erp application architecture These layers are available as reusable components and implementing with other components makes easier to implement.

Business Layer implements the business processesthat occur in the ERP business. This includes many classes responsible for automating the execution of business processes.

Reuse of software components

The reuse benefits of components can be realized when you read the chapter on Web ERP Application Architecture.

This chapter gives the complete code and design of the Data access layer. The other two layers Business Layer and Presentation Layer are explained in the subsequent chapters.

Just as the other two layers, the Data access layeris builtfrom the scratch and covers Data access functionality.

The Data Access layer essentially containsC# classes and methods and handles communication with the underlying database for accessing and saving the data. The interface of this layer is designed so that it can be reused in other projects and is also easy to use.

Components communicates with each other seamlessly as the interface methods are well defined.

resue of data access layer

As shown in the picture, this component (layer) can be reused by other projects for implementing data access functionality.