Chapter 6. Adding Database Support and Exception Handling to the RealNorthwind WCF Service

In the previous chapter, we created a WCF service with two layers. We didn't add the third layer, that is, the data access layer. Therefore, all of the service operations just returned a fake result from the business logic layer.

In this chapter, we will add the third layer to the WCF service. We will also introduce message contracts for service message exchange and fault contracts for service error handling.

We will accomplish the following tasks in this chapter:

  • Create the data access layer project
  • Modify the business logic layer to call the data access layer
  • Prepare the Northwind database for the service
  • Connect the WCF service to the Northwind database
  • Test the service with the data access layer
  • Add a fault contract to the service
  • Throw a fault contract exception to the client
  • Catch the fault contract in the client program
  • Test the service fault contract

Adding a data access layer

Now, we have two layers in our solution. We need to add one more layer the data access layer. We need to query a real database to get the product information, and update the database for a given product.

Creating the data access layer project

First, we will create the project for the data access layer. As we did for the business logic layer, what we need to do is add a C# class library project, named RealNorthwindDAL, where DAL stands for Data Access Layer, to the solution. Then, rename the Class1.cs to ProductDAL.cs, and modify it as follows:

  1. Change its namespace from RealNorthwindDAL to MyWCFServices. RealNorthwindDAL.
  2. Change the class name from Class1 to ProductDAL, if it hasn't been changed already.
  3. Add a reference to project RealNorthwindEntities.

Now, let's modify ProductDAL.cs for our product service:

  1. Add the following using statement:
    using MyWCFServices.RealNorthwindEntities;
  2. Add two new methods to the ProductDAL class. The first method is GetProduct, which will be as follows:
    public ProductEntity GetProduct(int id)
{
// TODO: connect to DB to retrieve product
ProductEntity p = new ProductEntity();
p.ProductID = id;
p.ProductName = "fake product name from data access layer";
p.UnitPrice = (decimal)30.00;
return p;
}
    • In this method, all the product information is still hard coded, though we have changed the product name to be specific to the data access layer. We will soon modify this method to retrieve the actual product information from a real Northwind database.
  3. The second method is UpdateProduct, which will be as follows:
public bool UpdateProduct(ProductEntity product)
{
// TODO: connect to DB to update product
return true;
}

Again, we didn't update any database in this method. We will also modify this method soon to update to the real Northwind database.

The content of the ProductDAL.cs file should now be as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MyWCFServices.RealNorthwindEntities;
namespace MyWCFServices.RealNorthwindDAL
{
public class ProductDAL
{
public ProductEntity GetProduct(int id)
{
// TODO: connect to DB to retrieve product
ProductEntity p = new ProductEntity();
p.ProductID = id;
p.ProductName = "fake product name from data access layer";
p.UnitPrice = (decimal)30.00;
if (id > 50) p.UnitsOnOrder = 30;
return p;
}
public bool UpdateProduct(ProductEntity product)
{
// TODO: connect to DB to update product
return true;
}
}
}

Calling the data access layer from the business logic layer

Before we modify these two methods to interact with a real database, we will first modify the business logic layer to call them, so that we know that the three-layer framework is working.

  1. Add a reference of this new layer to the business logic layer project. From the Solution Explorer, just right-click on the RealNorthwindLogic project item, select Add Reference from the context menu, select RealNorthwindDAL from the Projects tab, and then click the OK button.
  2. Open the ProductLogic.cs file under the RealNorthwindLogic project, and add a using statement:
    using MyWCFServices.RealNorthwindDAL;
  3. Add a new class member:
    ProductDAL productDAL = new ProductDAL();
  4. Modify the method GetProduct to contain only this line:
    return productDAL.GetProduct(id);

    We will use the data access layer to retrieve the product information. At this point, we will not add any business logic to this method.

  5. Modify the method UpdateProduct to look like this:
public bool UpdateProduct(ProductEntity product)
{
// TODO: call data access layer to update product
// first check to see if it is a valid price
if (product.UnitPrice <= 0)
return false;
// ProductName can't be empty
else if (product.ProductName.Length == 0)
return false;
// QuantityPerUnit can't be empty
else if (product.QuantityPerUnit.Length == 0)
return false;
// then validate other properties
else
{
ProductEntity productInDB = GetProduct(product.ProductID);
// invalid product to update
if (productInDB == null)
return false;
// a product can't be discontinued if there are non- fulfilled orders
if (product.Discontinued == true && productInDB. UnitsOnOrder > 0)
return false;
else
return productDAL.UpdateProduct(product);
}
}
  • In this method, we have replaced the last return statement to call the data access layer method UpdateProduct. This means that all of the business logic is still enclosed in the business logic layer, and the data access layer should be used only to update the product in the database.

Here is the full content of the ProductLogic.cs file:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MyWCFServices.RealNorthwindEntities;
using MyWCFServices.RealNorthwindDAL;
namespace MyWCFServices.RealNorthwindLogic
{
public class ProductLogic
{
ProductDAL productDAL = new ProductDAL();
public ProductEntity GetProduct(int id)
{
/*
// TODO: call data access layer to retrieve product
ProductEntity p = new ProductEntity();
p.ProductID = id;
p.ProductName = "fake product name from business logic layer";
//p.UnitPrice = (decimal)20.0;
if(id > 50) p.UnitsOnOrder = 30;
return p;
*/
data access layercalling, from business logic layerreturn productDAL.GetProduct(id);
}
public bool UpdateProduct(ProductEntity product)
{
// TODO: call data access layer to update product
// first check to see if it is a valid price
if (product.UnitPrice <= 0)
return false;
// ProductName can't be empty
else if (product.ProductName == null || product. ProductName.Length == 0)
return false;
// QuantityPerUnit can't be empty
else if (product.QuantityPerUnit == null || product. QuantityPerUnit.Length == 0)
return false;
// then validate other properties
else
{
ProductEntity productInDB = GetProduct(product.ProductID);
// invalid product to update
if (productInDB == null)
return false;
// a product can't be discontinued if there are non-fulfilled orders
else if (product.Discontinued == true && productInDB.UnitsOnOrder > 0)
return false;
else
return productDAL.UpdateProduct(product);
}
}
}
}

If you run the program and test it using the WCF Test Client, you will get exactly the same result as before, although now it is a three layer application, and you will see a different, although obviously still fake product name.

Preparing the database

As we have had the three-layer framework ready, we will now implement the data access layer to actually communicate with a real database.

In this book, we will use the Microsoft sample database, Northwind. This database is not installed by default in SQL Server 2005 or SQL Server 2008.

  1. Download the database package. Just search for "Northwind Sample Databases" on the Internet, or go to this page:

    http://www.microsoft.com/downloads/details.aspx?FamilyId=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

    and download file SQL2000SampleDb.msi.

  2. Install (extract) it to: C:SQL Server 2000 Sample Databases.
  3. Open SQL Server 2005/2008 Management Studio.
  4. Connect to your database engine.
  5. Right click on the Databases node, and select Attach from the context menu, as shown in the SQL Server Management Studio diagram below:
    Preparing the database
  6. In the pop-up Attach Databases dialog box, click Add, browse to the file C:SQL Server 2000 Sample DatabasesNORTHWND.MDF, click OK, and you now have the Northwind database attached to your SQL Server 2005 or 2008 engine.
    Preparing the database

Adding the connection string to the configuration file

Now that we have the Northwind database attached, we will modify our data access layer to use this actual database. At this point, we will use a raw SqlClient adapter to do the database work. We will replace this layer with LINQ to SQL in a later chapter.

Before we start coding, we need to finish the following tasks, to add a connection string to the configuration file. We don't want to hard-code the connection string in our project. Instead, we will set it in the App.config file, so that it can be changed on the fly.

  1. Add a reference to System.Configuration to the RealNorthwindDAL project. We will store connection string in the configuration file, and we need this assembly to read it.
  2. Add the following configuration settings to the App.config file under the RealNorthwindService project.
    <appSettings>
<add key="NorthwindConnectionString"
value="server=your_db_serveryour_db_instance;
uid=your_user_name; pwd=your_password;
database=Northwind"/>
</appSettings>
    • There are a couple of things to note for this new key in the configuration file.
      • It should be added to the App.config file in the RealNorthwindService project, not to the RealNorthwindDAL project. Actually, there is no file called App.config in the RealNorthwindDAL project.
      • The node appSettings should be a child node of the root configuration node, that is, the highlighted lines should be placed immediately after the line<configuration>. So, the first few lines of the App.config file should be as follows (highlighted lines are new lines to add):
    <?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
data access layerconnection string, adding to configuration file<add key="NorthwindConnectionString"
value="server=your_db_serveryour_db_instance;
uid=your_user_name; pwd=your_password;
database=Northwind"/>
</appSettings>

<system.web>
<compilation debug="true" />
</system.web>
  3. Replace your_db_server with your actual database server name. If the database is located on your own machine, you can use local as the db server name.
  4. Replace your_db_instance with your database's instance name. If you have installed your SQL server with the default instance, don't put anything here.
  5. Replace your_user_name and your_password with your actual logon and password to the SQL server database. This user must have write access to the Northwind database.
  6. If you use sa to log in to your database, make sure that in your database, the user sa is enabled for login. Some installation may have automatically disabled sa from logging on to the database (use SQL Server Management Studio | Login Properties sa | status | Permission to connect to database engine and login).
  7. If you don't have an SQL Server logon, or you just want to use Windows authentication, you can use trusted connection, or SSPI integrated security connection. The key for the trusted connection will be:
<add key="NorthwindConnectionString" value="server= your_db_ serveryour_db_instance;database=Northwind; Trusted_Connection=yes" />
  • The key for the integrated security connection will be:
<add key="NorthwindConnectionString" value="server= your_ db_serveryour_db_instance;database=Northwind;Integrated Security=SSPI" />

Querying the database (GetProduct)

Because we have added the connection string as a new key to the configuration file, we need to retrieve this key in the DAL class, so that we can use it when we want to connect to the database. Follow these steps to get and use this new key from within the DAL class:

  1. Open the file ProductDAL.cs in the RealNorthwindDAL project, and first add two using statements:
    using System.Data.SqlClient;
using System.Configuration;
  2. Add a new class member to the ProductDAL class:
    string connectionString = ConfigurationManager.AppSettings["Northw indConnectionString"];
    • We will use this connection string to connect to the Northwind database, for both the GetProduct and UpdateProduct methods.
  3. Modify the GetProduct method to get the product from the database, as follows:
public ProductEntity GetProduct(int id)
{
/*
// TODO: connect to DB to retrieve product
ProductEntity p = new ProductEntity();
p.ProductID = id;
p.ProductName = "fake product name from data access layer";
p.UnitPrice = (decimal)30.00;
if (id > 50) p.UnitsOnOrder = 30;
return p;
*/
ProductEntity p = null;
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlCommand comm = new SqlCommand();
comm.CommandText = "select * from Products where ProductID=" + id;
comm.Connection = conn;
conn.Open();
SqlDataReader reader = comm.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
p = new ProductEntity();
p.ProductID = id;
p.ProductName =
(string)reader["ProductName"];
p.QuantityPerUnit =
(string)reader["QuantityPerUnit"];
p.UnitPrice =
(decimal)reader["UnitPrice"];
p.UnitsInStock =
(short)reader["UnitsInStock"];
p.UnitsOnOrder =
(short)reader["UnitsOnOrder"];
p.ReorderLevel =
(short)reader["ReorderLevel"];
p.Discontinued =
(bool)reader["Discontinued"];
}
}
return p;
}

In this method, we first create an SqlConnection to the Northwind database, and then issue an SQL query to get product details for the ID.

The following statement is a new feature of C# 3.0, and equivalent to the traditional try…catch…finally… mechanism to deal with SqlConnection matters: 

using (SqlConnection conn = new SqlConnection(connectionString))

Testing the GetProduct method

If you now set the RealNorthwindService as the startup project and run the application, you can get the actual product information from the database, as seen in the following screenshot:

Testing the GetProduct method

If you get an error screen, it is probably because you have set your connection string incorrectly. Double-check the new appSettings key in your App.config file, and try again until you can connect to your database.

Testing the GetProduct method

Instead of the connection error message, you might see the following error message:

Testing the GetProduct method

This error will happen when you try to get the product information for a product with a product ID of 0. The error message doesn't give much detail about what went wrong here, because we didn't let the server reveal the details of any error. Let's follow the instructions in the error message to change the setting IncludeExceptionDetailInFaults to True in the App.config file, and run it again. Now you will see that the error detail has changed to "Object reference not set to an instance of an object."

Testing the GetProduct method

A little investigation will tell us that this is a bug in our ProductService class. Inside the ProductService GetProduct method, after we call business logic layer to get the product detail for an ID, we will get a null product if the ID is not in the database. When we pass this null object to the next method (TranslateProductEntityToProductContractData), we get the above error message. Actually, this will happen whenever you enter a product ID outside of the range 1-77. This is because, in the sample Northwind database, there are only 77 products, with product IDs ranging from 1 to 77. To fix this problem, we can add the following statement inside the GetProduct method right, immediately after the call to the business logic layer:

if (productEntity == null)
data access layerdatabase, queryingthrow new Exception("No product found with id " + id);

So in the ProductService.cs file, the GetProduct method will now be:

public Product GetProduct(int id)
{
ProductLogic productLogic = new ProductLogic();
ProductEntity productEntity = productLogic.GetProduct(id);
if (productEntity == null)
throw new Exception("No product found with id " + id);
Product product = new Product();
TranslateProductEntityToProductContractData(productEntity, product);
return product;
}

For now, we will raise an exception if an invalid product ID is entered. Later, we will convert this exception to a FaultContract, so that the caller will know in advance that an error has occurred.

Now run the application again, and if you enter an invalid product ID, say 0, you will get an error message, "No product found with id 0". This is a much clearer than the previous "Object reference not set to an instance of an object" error message.

Testing the GetProduct method

Updating the database (UpdateProduct)

Next, we will modify the UpdateProduct method to update the product record in the database. The UpdateProduct in the RealNorthwindDAL project should be modified as follows: 

public bool UpdateProduct(ProductEntity product)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand("UPDATE products SET ProductName=@name,QuantityPerUnit=@unit,UnitPrice=@ price,Discontinued=@discontinued WHERE ProductID=@id",conn);
cmd.Parameters.AddWithValue("@name", product.ProductName);
cmd.Parameters.AddWithValue("@unit", product.QuantityPerUnit);
cmd.Parameters.AddWithValue("@price", product.UnitPrice);
cmd.Parameters.AddWithValue("@discontinued", product. Discontinued);
cmd.Parameters.AddWithValue("@id", product.ProductID);
conn.Open();
int numRows = comm.ExecuteNonQuery();
if (numRows != 1)
return false;
}
return true;
}

Inside this method, we have used parameters to specify arguments to the update command. This is a good practice because it will prevent SQL Injection attacks as the SQL statement is precompiled instead of being dynamically built.

We can follow these steps to test it:

  1. Start the WCF Test Client
  2. Double-click on the UpdateProduct() operation
  3. Enter a valid product id, name, price and quantity per unit
  4. Click on Invoke

You should get a True response. To prove it, just go to the GetProduct() page, enter the same product ID, click on Invoke, and you will see that all of your updates have been saved to the database.

The content of the ProductDAL.cs file is now:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MyWCFServices.RealNorthwindEntities;
using System.Data.SqlClient;
using System.Configuration;
namespace MyWCFServices.RealNorthwindDAL
{
public class ProductDAL
{
string connectionString = ConfigurationManager.AppSettings["NorthwindConnectionString"];
public ProductEntity GetProduct(int id)
{
/*
// TODO: connect to DB to retrieve product
ProductEntity p = new ProductEntity();
p.ProductID = id;
p.ProductName = "fake product name from data access layer";
p.UnitPrice = (decimal)30.00;
if (id > 50) p.UnitsOnOrder = 30;
return p;
*/
ProductEntity p = null;
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlCommand comm = new SqlCommand();
comm.CommandText = "select * from Products where ProductID=" + id;
comm.Connection = conn;
conn.Open();
SqlDataReader reader = comm.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
p = new ProductEntity();
p.ProductID = id;
p.ProductName =
(string)reader["ProductName"];
p.QuantityPerUnit =
(string)reader["QuantityPerUnit"];
p.UnitPrice =
(decimal)reader["UnitPrice"];
p.UnitsInStock =
(short)reader["UnitsInStock"];
p.UnitsOnOrder =
(short)reader["UnitsOnOrder"];
p.ReorderLevel =
(short)reader["ReorderLevel"];
p.Discontinued =
(bool)reader["Discontinued"];
}
}
return p;
}
public bool UpdateProduct(ProductEntity product)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand("UPDATE products SET ProductName=@name,QuantityPerUnit=@ unit,UnitPrice=@price,Discontinued=@discontinued WHERE ProductID=@id", conn);
cmd.Parameters.AddWithValue("@name", product. ProductName);
cmd.Parameters.AddWithValue("@unit", product. QuantityPerUnit);
cmd.Parameters.AddWithValue( "@price", product.UnitPrice);
cmd.Parameters.AddWithValue("@discontinued", product.Discontinued);
cmd.Parameters.AddWithValue("@id", product.ProductID);
conn.Open();
int numRows = cmd.ExecuteNonQuery();
if (numRows != 1)
return false;
}
return true;
}
}
}
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset