.NET Development Examined

Creating a Service Layer Part III

Saturday, October 18, 2014

In Part One of our WCF services series we looked at how to create the contracts and implementation of our WCF/CODE Framework service. In Part Two we will look at how to instantiate our service end points during development using a CODE Framework Development Host.

In this post we will look at how to access an SQL database using Entity Framework so that our service layer can actually do something of interest.

Exercise 3: Create SQL Database

In this exercise you will use Microsoft Management Studio to create a database to persist data.

Task 1 – Create Database, Table, and Columns

  1. Open Microsoft Management Studio and connect to your local server. Note that this can typically be done my connecting to localhost. If you are running SQL Server Express, this is typically localhost\SQLExpress
  2. Expand server listing and Right Click on Databases and select New Database.
  3. Name the database ServicesExercise.
  4. Expand the new database and Right Click on Tables. Select the New Table menu item.
  5. Create the following columns:
  6. Highlight the Id column and click the Set Primary Key icon.
  7. Click on the Manage Indexes and Keys icon and set the Create as Clustered to No.
  8. Click the save icon and give the table a name of ServicesExercise.
  9. Refresh the table listing and expand the columns of the table. It should appear as shown here.
  10. Right click on the Customer table and select Edit Top 200 Rows. Enter information for Name, Address, Phone, and CreditLimit.
  11. Click on the next row.
  12. Click the red exclamation point to refresh the table. You should see that the Id and CustomerSince were populated with default values.

Exercise 4: Create Entity Framework Project

In this exercise you will create a project to expose Object Relational Mapping (ORM) functionality. At this step you are free to utilize your favorite ORM, but for the lab we will utilize Microsoft’s Entity Framework. The ORM will expose the Database objects defined above to our code as class objects. Additionally it will provide a data context to allow our code to interact with the data.

Task 1 – Create an ORM Project

  1. Add a new Class Library Project. Right click on the solution (not a project) and select Add/New Project.
  2. Expand the templates listing and click on Visual C#/Windows
  3. Select the Class Library template and give it a name of <your project>.BusinessObjects and click OK.
  4. Remove the Class1.cs file. This file is generated by the project template and will not be used.
  5. Add the EF Data Model. Start by Right click on the <your project>.BusinessObjects project and select Add/New Item.
  6. Expand the template tree and select Visual C# Items\Data
  7. Click on ADO.NET Entity Data Model and give it a name of <your project>Model and select Add.
  8. Step through the Entity Data Model Wizard. Select Generate from Database then click Next.
  9. Define data connection. Select an existing connection or create a new connection to the SQL Database created in Exercise 3. Note that it’s preferable for this exercise to use localhost (or localhost\SQLExpress) for the server name.
  10. Select objects to include and define Model Namespace. Check all Tables to be included in application and enter ServicesExerciseModel for the Model Namespace. Click on Finish to complete wizard.
  11. This will generate a file called <your project>Model.edmx. The diagram view will show all tables and foreign key relationships defined in the database. At this point your Business Objects project implements EF.

Exercise 5: Update Service Implementation

In this exercise you will update the previously created service implementation project to utilize the Entity Framework context. This final step of the services lab will allow your service methods to interact with the ORM layer.

Task 1 – Update Project .NET Framework Target

When adding projects to a solution it is possible that the .NET Framework versions each project targets are not the same. For projects that are standalone this is not an issue, however when you start referencing project to one another it is necessary to ensure that all project target the same .NET Framework version.

  1. Right click on the <your project>.BusinessObjects project and select Properties.
  2. Click on the Application tab and note the Target Framework. Show here it is targeting .NET Framework 4.5
  3. To be able to reference a project to another the version of the .NET Framework must match. Check the project properties of each project and ensure that the Target Framework matches this setting.

Task 2 – Add Connection String

  1. Open the App.config file in the BusinessObjects project and locate the connection string for your entities. Highlight and copy the section.
  2. Open the App.config file in the DevelopmentHost project. Paste in the section.
    Note that connection strings for Entity Framework contain a regular SQL Server (or whatever your chosen database is) connection string along with other settings. You can edit the server and database names in this part of the connection string if you need to move this to another computer. That’s why, for development localhost is preferable to an explicit computer name.

Task 3 – Update References

  1. Expand the <your project>.Services.Implementation project.
  2. Right click on the References folder and select Add Reference.
  3. Click on the Solution/Projects tree the <your project>.Services.Contracts project should already be checked. Check the <your project>.BusinessObjects project as well.
  4. Click on the Assemblies/Framework tree and check the System.Data.Entity and click OK.
  5. Add reference to Entity Framework. Open the Tools/Library Packet Manager/Manage NuGet Packages for Solution menu item.
  6. Search for Entity Framework. The ADO.NET Entity Data Model should have added the package to the solution, so you should see a green check next to the listing.
  7. Expand the Installed Packages/All tree listing and click on the Manage button on the EntityFramework listing.
  8. Check the box next to <your project>.Services.Implementation to ensure this project includes a reference to the Entity Framework.

Task 4 – Update Implementation

  1. In the <your project>.Services.Implementation project open the CustomerService.cs file
  2. At the top add the using statement for LINQ.
    using System.Linq;
  3. Replace GetCustomerList test code. Expand the GetCustomerList method and find the previous test code (just below the //TODO). Comment out or remove the entire for loop:
    for (var x = 1; x < 10; x++)
  4. Add a new entities using statement.
    using (var context = new ServicesExerciseEntities())
    { }
  5. Add new LINQ query to get customer list from ORM.
    using (var context = new ServicesExerciseEntities())
        var query = from customer in context.Customers
                    orderby customer.Name
                    select new CustomerQuickInformation
                            Id = customer.Id,
                            FullName = customer.Name,
                            Address = customer.Address,
                            Phone = customer.Phone
        response.Customers = query.ToList();
    Note: this code may not be as concise as possible, but it is much easier to debug. Also note that if no data is returned the .ToList() will create an empty list, so null is never returned from the implementation.
  6. Replace GetCustomer test code. Expand the GetCustomer method and find the previous test code (just below the //TODO). Comment out or remove the response.Customer assignment.
  7. Comment out the existing response.Success = true; statement.
  8. Add a new entities using statement and add the following LINQ code inside the using statement.
    using (var context = new ServicesExerciseEntities())
        var query = from customer in context.Customers
                    where customer.Id == request.CustomerId
                    select customer;
        var cust = query.FirstOrDefault();
        if (cust == null)
            response.Success = false;
            response.FailureInformation = "Customer was not found";
            response.Success = true;
            Mapper.Map(cust, response.Customer);
    The Mapper.Map is a CODE Framework utility that will copy the properties from one object to another. It is important to note that both the property names and types must match. Additionally keep in mind that types must be of the same nullable types (i.e. int? will not match int).
    Add code to SaveCustomer method. Just below the //TODO statement add the following LINQ code.
    using (var context = new ServicesExerciseEntities())
        var query = from customer in context.Customers
                    where customer.Id == request.Customer.Id
                    select customer;
        var cust = query.FirstOrDefault();
        if (cust == null)
            cust = new Customer();
        Mapper.Map(request.Customer, cust);
  9. Right click on the <your project>.Services.DevelopmentHost project and select Debug/Start New Instance. As before select the GetCustomerList operation and click on Invoke. You should see a listing of all customer records in your database.

So there you have it. You have learned how to create WCF Service Contract and Implementation projects. You have seen an illustration of how services can be conceived and implemented independently of other application layers. The CODE Framework Development Host project shows how easily a WCF Service can be developed interactively during the design process, even when the UI is not available. Finally you have learned how to create a SQL Database and have its business objects exposed to the service layer using Microsoft’s Entity Framework.


Add Comment