/SqlWebApi-HtmlDataManager

Simple HTML app on top of ASP .NET WebApi and SQL Server, developed with Visual Studio and as little code as possible

Primary LanguageC#MIT LicenseMIT

SqlWebApi-HtmlDataManager

Simple data manager application leveraging HTML on top of ASP .NET WebApi and SQL Server database, developed with Visual Studio and as little code as possible.

Development steps

Get started

  1. Initiate creation of a new Visual Studio project of type ASP .NET Web Application 1

  2. Start with Empty template and Web API folders and core references 2

  3. Open Solution Explorer in Visual Studio to see and manage the source code files 3

Create an organization database including employee and department data

  1. Add new SQL Server Database in App_Data folder from Solution Explorer 4

  2. Set MyOrganization as the database name 5

  3. Add a new table to the new database from Server Explorer 6

  4. Set Departments table name in the T-SQL script, add columns, and set Id column (primary key) as identity column 7

  5. Add another Employees table 8

  6. Initiate adding of a new foreign key to Employees table to create a relation between employees and departments 9

  7. Set Employees.DepartmentId column as foreign key to Departments.Id column 10

  8. Show table data for Departments and Employees tables from Server Explorer 11

  9. Enter some test data for Departments table 12

  10. Enter some test data for Employees table, using DepartmentId values from actually created department rows; DepartmentId is nullable indicating that department relationship is optional for employees 13

Create data model for the organization database (mapping database tables to middle code data entity objects)

  1. Add new ADO .NET Entity Data Model in Models folder from Solution Explorer 14

  2. Set MyOrganizationModel as the Entity Framework data model name 15

  3. Use EF Designer from database as the database already exists 16

  4. Save connection string to Web.config 17

  5. Select Entity Framework version 18

  6. Select database tables that you want to create data model objects for 19

  7. View the data model and relations between entities 20

Create WebApi controllers using the existing data model classes to expose get and update actions for your data entities (and eventually database) on HTTP as /api/* URLs

  1. Build the Visual Studio project to ensure data model is compiled

  2. Initiate adding of a new controller in Controllers folder from Server Explorer 22

  3. Select Web API 2 Controller with actions, using Entity Framework 23

  4. Select MyOrganizationEntities as data context class and then Employee as model class to generate controller for 24

  5. View EmployeesController code 25

Prepare for client side development

  1. Open Nuget package manager to add frameworks to help with client side Development 26

  2. Use Browse to search for, then select and install AngularJS.Core package (or another client side framework package that you would like to use) 27

Create client side app to display employee data from the database

  1. Initiate adding of a new HTML Page in the project folder from Server Explorer 28

  2. Set index as the page name 29

  3. Write HTML code with a list item to display employee information as a template definition 30

  4. Run the app to see the template itself without actual employee values 31

  5. Initiate adding of a new JavaScript code file in Scripts folder from Server Explorer 32

  6. Set app as the JavaScript file name 33

  7. Write an Angular module with a controller that loads actual employee values using HTTP from WebApi using /api/ControllerName URL 34

  8. Update HTML code to load the Angular script, app.js script, and initialize the Angular app module and controller, then repeat generating list items for each employee 35

  9. Run the app again and use browser developer tools (F12) to notice a circular reference serialization error on the server side

  10. To resolve the issue caused by employee data linked to department that is linked back to employee data go to Entity Framework data model and select False for its Lazy Loading Enabled property 37

  11. Run the app again and see base employee data is loaded successfully; note, though, that department data is not available (since related department data for employees hasn't been loaded anymore) 38

  12. To resolve the issue you may use a separate data transfer object (DTO) as model; initiate adding a new class in Models folder from Server Explorer 39

  13. Write very simple code to define EmployeeDto class and its properties, mapping to fields to be displayed on the client side, including DepartmentName 40

  14. Update EmployeesController code to return EmployeeDto objects instead of Employee entities for the GET verb 41

  15. Update HTML code to use DepartmentName field of the employee DTO 42

  16. Run the app again and see full employee data is loaded and displayed correctly, including department names; some employees may not have a department 43

Update client side app to be able to add new employee records to the database

  1. Update HTML code with input elements to support new employee field entering, and an Add button linked to a new addNewEmployee function to be defined in JavaScript code 44

  2. Run the app to see the New employee form visuals 45

  3. Update JavaScript code to support editing fields for and adding a new employee, and reinitializing the employee list afterwards 46

  4. Run the app again and click Add button 47

  5. See the new employee created and his or her information displayed in the list of employees 48