Build an Employee Tracker with AngularJS and Web API – Part 1

This is the first part of Building an Employee Tracker using AngularJS and ASP.NET Web API Series. You will learn how to:

• Part 1 – Create a Web API Application in C# (This Post)
• Part 2 – Create the Web API services
• Part 3 – Create a SPA in AngularJS 1.x
• Part 4 – Create Features in AngularJS 1.x

The source code for this tutorial series is published on GitHub. Demo application is hosted in Microsoft Azure.

Part 1: Create a Web API Application in C#

In this tutorial, we will lay down the foundation for this single page application using Visual Studio 2013 and SQL Server.

Server-Side Technologies:

• ASP.NET Web API 2.2
• SQL Server 2014
• Internet Information Services (IIS)
• Dapper
• Autofac

Task 1. Create the initial solution and projects

1. Right-click on Visual Studio 2013 and select Run as administrator to launch Visual Studio. Then, select New Project from the Start page. Or, from the File menu, select New and then Project… to start a new solution.

2. In the New Project dialog box, select ASP.NET Web Application under the Visual C# tab. Make sure .NET Framework 4.5 is selected, name it EmployeeTracker.Api, set the Solution name to EmployeeTrackerRS, choose a Location and click OK.

   

   The EmployeeTracker.Api contains controllers for the Web API Restful interfaces.

3. In the New ASP.NET Project dialog box, select the Empty template. Select the Web API option under the Add folders and core references for. Make sure that the Authentication option is set to No Authentication. Click OK to continue.

   

4. In Solution Explorer, right-click the EmployeeTrackerRS Solution, and select Add and then New Project…

   

5. In the Add New Project dialog box, select Class Library under the Visual C# tab. Make sure .NET Framework 4.5 is selected, name it EmployeeTracker.Services, and click OK.

   

   The EmployeeTracker.Services encapsulates the data access layer, and contains logic for retrieving data and mapping it to data models.

6. In the EmployeeTracker.Service project, right-click the Class1.cs file and select Delete to remove the file.

7. In Solution Explorer, right-click the EmployeeTrackerRS solution, and select Properties.

8. In Solution ‘EmployeeTrackerRS’ Property Pages dialog box, select Startup Project under the Common Properties tab. Make sure Single startup project is selected for EmployeeTracker.Api.

   

9. In the same Solution ‘EmployeeTrackerRS’ Property Pages dialog box, select Project Dependencies under the Common Properties tab. Set the Projects to EmployeeTracker.Api. Select the checkbox for EmployeeTracker.Services under the Depends on: and click OK.

   

10. In Solution Explorer, right-click the EmployeeTracker.Api project, and select Properties.

11. In the EmployeeTracker.Api pane, select Web tab. Make sure Local IIS is selected and set the Project Url to http://localhost/employee-tracker-apis. Click Create Virtual Directory.

    

12. Let’s rebuild the application. In Solution Explorer, right-click the EmployeeTrackerRS Solution, and select Rebuild Solution.

Task 2. Install the needed NuGet Packages to each project

1. In the Package Manager Console, select EmployeeTracker.Api as the Default project. Select the nugget.org as the Package source.

    

   Install-Package Autofac -Version 3.5.0	
   Install-Package Autofac.WebApi2 –Version 3.4.0
   Install-Package Microsoft.AspNet.Cors –Version 5.2.3
   Install-Package Microsoft.AspNet.WebApi.Cors –Version 5.2.3

    

   Enter the above Install-Package command in line 1 and hit the enter key. Repeat this step till you have finished installing all four packages.

   

2. In the Package Manager Console, select EmployeeTracker.Services as the Default project. Select the nugget.org as the Package source.

    

   Install-Package Dapper -Version 1.42

    

   Enter the above Install-Package command in line 1 and hit the enter key.

   

3. In Solution Explorer, right-click the EmployeeTrackerRS Solution, and select Enable NuGet Package Restore. Click Yes to continue.

   

Task 3. Create data models and services in the EmployeeTracker.Services project

I came across this article written by Joe Sauve on how to use Dapper accessing data from the database asynchronously. Dapper also supports numerous different database vendors. With that in mind, you can refactor the following classes to support different databases other than SQL Server.

1. In Solution Explorer, right-click the EmployeeTracker.Services project, and select Add and then New Folder. Name the folder Common.

   

2. Right-click the newly created Common folder. From the context menu, select Add and then New Item…

   

3. In the Add New Item dialog box, select Interface under the Visual C# Items tab. Name it IConnection.cs, and click Add.

   

   Replace the generated code with the following:

    

   namespace EmployeeTracker.Services.Common
   {
       public interface IConnection
       {
           string ConnectionString { get; }
       }
   }

    

4. Once again, right-click the Common folder. From the context menu, select Add and then New Item…. In the Add New Item dialog box, select Class under the Visual C# Items tab. Name it Connection.cs, and click Add.

   

   Replace the generated code by adding the Connection class that implements IConnection interface:

    

   using System;
   using System.Configuration;
   
   namespace EmployeeTracker.Services.Common
   {
       public class Connection: IConnection
       {
           public Connection(ConnectionStringSettings settings)
           {
               // must use a guard clause to ensure something is injected
               if (settings == null)
                   throw new ArgumentNullException("settings", "Connection expects constructor injection for connectionStringSettings param.");
   
               // we have a value by now so assign it
               ConnectionString = settings.ConnectionString;
           }
   
           public string ConnectionString { get; set; }
   
       }
   }

    

   The Connection class contains the connection setting to our database in SQL Server.

5. Create a new folder called SqlServer. Add the IRepository interface to this folder. Replace the generated code with the following:

    

   using System;
   using System.Data;
   using System.Data.SqlClient;
   using System.Threading.Tasks;
   
   namespace EmployeeTracker.Services.SqlServer
   {
       public interface IRepository
       {
           Task<T> WithConnection<T>(Func<IDbConnection, Task<T>> getData);
           SqlConnection GetConnection(bool multipleActiveResultSets = false);
       }
   }

    

   Next, add the Repository class that implements IRepository interface:

    

   using System;
   using System.Collections.Generic;
   using System.Data;
   using System.Data.SqlClient;
   using System.Linq;
   using System.Text;
   using System.Threading.Tasks;
   using EmployeeTracker.Services.Common;
   
   namespace EmployeeTracker.Services.SqlServer
   {
       public class Repository: IRepository
       {
           private readonly string _connectionString;
   
           public Repository(IConnection db)
           {
               _connectionString = db.ConnectionString;
           }
   
           // use for buffered queries that return a type
           public async Task<T> WithConnection<T>(Func<IDbConnection, Task<T>> getData)
           {
               try
               {
                   using (var connection = new SqlConnection(_connectionString))
                   {
                       await connection.OpenAsync();
                       return await getData(connection);
                   }
               }
               catch (TimeoutException ex)
               {
                   throw new TimeoutException("Timeout Exception", ex);
               }
               catch (SqlException ex)
               {
                   throw new DBConcurrencyException("Sql Exception", ex);
               }
           }
   
           public SqlConnection GetConnection(bool multipleActiveResultSets = false)
           {
               try
               {
                   var cs = _connectionString;
                   if (multipleActiveResultSets)
                   {
                       var scsb = new SqlConnectionStringBuilder(cs) { MultipleActiveResultSets = true };
                       if (scsb.ConnectionString != null) cs = scsb.ConnectionString;
                   }
                   var connection = new SqlConnection(cs);
                   connection.Open();
                   return connection;
               }
               catch (TimeoutException ex)
               {
                   throw new TimeoutException("Timeout Exception", ex);
               }
               catch (SqlException ex)
               {
                   throw new DBConcurrencyException("Sql Exception", ex);
               }
           }
   
       }
   }

    

   This Repository class implements Joe’s WithConnection() method to manage opening database connection, accessing data, and closing connection asynchronously.

6. In Solution Explorer, right-click the EmployeeTracker.Services project, and select Add and then Reference….

7. In the Reference Manager dialog box, select Framework under the Assemblies tab. Select the checkbox for System.Configuration and click OK.

8. Rebuild the solution.

Task 4. Register all services in the EmployeeTracker.Api project

Next, we are going to use Autofac as the IoC container that will handle all the lifetime management of our dependencies and do the dependency injection for us.

The idea behind inversion of control is that, rather than tie the classes in your application together and let classes “new up” their dependencies, you switch it around so dependencies are instead passed in during class construction.
Autofac

Following the documentation on how to integrate Autofac into our application, we need to create a ContainerBuilder and then register our classes to expose their interfaces with it.

1. In the EmployeeTracker.Api project, open the WebApiConfig.cs file in the App_Start folder. Replace the generated code with the following:

    

   using System.Reflection;
   using System.Web.Http;
   using Autofac;
   using Autofac.Integration.WebApi;
   using EmployeeTracker.Api.Autofac;
   using Newtonsoft.Json.Serialization;
   using System.Web.Http.Cors;
   
   namespace EmployeeTracker.Api
   {
       public static class WebApiConfig
       {
           public static void Register(HttpConfiguration config)
           {
               // Web API configuration and services
   
               // Web API routes
               config.MapHttpAttributeRoutes();
   
               // use camel case for JSON data
               config.Formatters.JsonFormatter.SerializerSettings.ContractResolver =
                 new CamelCasePropertyNamesContractResolver();
   
               config.Routes.MapHttpRoute(
                   name: "DefaultApi",
                   routeTemplate: "api/{controller}/{id}",
                   defaults: new { id = RouteParameter.Optional }
               );
   
               /*** Autofac: Build the container ***/
               var builder = new ContainerBuilder(); 
   
               // register Web API Controllers
               builder.RegisterAssemblyTypes(Assembly.GetExecutingAssembly());
   
               // register your classes and expose their interfaces - shared
               builder.RegisterModule(new StandardModule()); 
   
               var container = builder.Build();
               config.DependencyResolver = new AutofacWebApiDependencyResolver(container);
           }
       }
   }

    

2. Create a new folder called Autofac. Add the StandardModule class to this folder. Replace the generated code with the following:

    

   using System.Configuration;
   using Autofac;
   using EmployeeTracker.Services.Common;
   using EmployeeTracker.Services.SqlServer;
   
   namespace EmployeeTracker.Api.Autofac
   {
       public class StandardModule: Module
       {
           protected override void Load(ContainerBuilder builder)
           {
               base.Load(builder);
   
               // obtain database connection string once and reuse by Connection class
               var conn = ConfigurationManager.ConnectionStrings["DBConnection"];
   
               // Register Connection class and expose IConnection 
               // by passing in the Database connection information
               builder.RegisterType<Connection>() // concrete type
                   .As<IConnection>() // abstraction
                   .WithParameter("settings", conn)
                   .InstancePerLifetimeScope();
   
               // Register Repository class and expose IRepository
               builder.RegisterType<Repository>() 
                   .As<IRepository>() 
                   .InstancePerLifetimeScope();
           }
       }
   }

    

3. In Solution Explorer, right-click the EmployeeTracker.Api project, and select Add and then Reference….

4. In the Reference Manager dialog box, click the Browse… button.

5. In the Select the files to reference… dialog box, locate and select the EmployeeTracker.Services.dll. Click Add and then OK.

6. Rebuild the solution.

Task 5. Run SQL script to create a database, tables and stored procedures

1. Launch SQL Server 2014 Management Studio. Then, connect to the database server.

2. In the File menu, select Open and then File….

   

3. In the Open File dialog box, select EmployeeTracker db.sql and click Open. You can downloaded EmployeeTracker db.sql from Github.

   

4. Make sure Master is selected. Click Execute to create database, tables, and stored procedures for EmployeeTrackerRS.

   

In this tutorial, we have just finished creating the initial structure of the Employee Tracker Web API application. In the next tutorial, we will build the Web API services.

The source code for this tutorial series is published on GitHub. Demo application is hosted in Microsoft Azure.

References

• Joe Sauve’s Async Dapper with async SQL connection management
• ASP.NET Core Integration documentation

The post Build an Employee Tracker with AngularJS and Web API – Part 1 appeared first on CC28 Technology.