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:
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.
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:
RealNorthwindDAL
to MyWCFServices. RealNorthwindDAL
. Class1
to ProductDAL
, if it hasn't been changed already. RealNorthwindEntities
.Now, let's modify ProductDAL.cs
for our product service:
using
statement:using MyWCFServices.RealNorthwindEntities;
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; }
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; } } }
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.
ProductLogic.cs
file under the RealNorthwindLogic
project, and add a using
statement:using MyWCFServices.RealNorthwindDAL;
ProductDAL productDAL = new ProductDAL();
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.
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); } }
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.
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.
and download file SQL2000SampleDb.msi
.
C:SQL Server 2000 Sample Databases
.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.
System.Configuration
to the RealNorthwindDAL
project. We will store connection string in the configuration file, and we need this assembly to read it. 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>
App.config
file in the RealNorthwindService
project, not to the RealNorthwindDAL
project. Actually, there is no file called App.config
in the RealNorthwindDAL
project. 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>
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. 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. 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. 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).<add key="NorthwindConnectionString" value="server= your_db_ serveryour_db_instance;database=Northwind; Trusted_Connection=yes" />
<add key="NorthwindConnectionString" value="server= your_ db_serveryour_db_instance;database=Northwind;Integrated Security=SSPI" />
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:
ProductDAL.cs
in the RealNorthwindDAL
project, and first add two using
statements:using System.Data.SqlClient; using System.Configuration;
ProductDAL
class:string connectionString = ConfigurationManager.AppSettings["Northw indConnectionString"];
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))
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:
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.
Instead of the connection error message, you might see the following error message:
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."
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.
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:
UpdateProduct()
operationYou 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; } } }