Introduction
- Part 1 (Distributed Application Layers with project details): Learn about what layered design in distributed environment is and how we are going to name it while implementing the actual app.
- Part 2 (Database and Library Design): Learn about database design and implementing library which interacts with Edmx container [this article].
- Part 3 (Engine and Service Managers design): Learn how to implement the engine which has core business logic and implementing the actual WCF service with service contracts. And also how to test the service using test client.
- Part 4 (Client implementation): Learn how to implement the actual client with MVVM pattern which invokes services.
This is the second article in a canonic series about the Enterprise Application Architecture design. In the previous article, we discussed about distributed application layers and how we are going to implement these layers in a sample application (Customer Order Management System (COMS)). So in this article, I am going to describe about database design, entity container and library implementation. Project names will remain the same as mentioned at the right side of architecture diagram explained in part 1.
Background
The very first thing in any application design is creating the underlying database to support it. We should be very careful while designing database since we have to cover most possible business logic in the database itself instead of writing in client side code. To be frank, I am not an expert in database design. Please feel free to comment if you think something is less accurate. Let’s start our database design.
Customer Order Management System Database Design
I’ll take a few sample use cases for implementation in COMS (Customer Order Management System) with these four tables for a demonstration.
- Customer Table - Contains customer data such as name, id, address, shipping address, billing address, etc.
- Product Table – Contains product data such as product name, description, unit price, etc.
- Order Table - Contains data on when an order was placed including Customer ID, order date, shipping date, etc.
- OrderDetail Table - Contains data on each product ordered on one order (because you can purchase multiple items on a single order) including the product ordered, quantity, unit price, discounts, etc.
As you might be aware, for a relational database to work properly, you should have a field in each database that uniquely identifies (Primary Key) that row in your database table. Also, we should have a link in the table based on its relationship (Foreign Key). The table below illustrates relationships between the tables.
Table and Key Details
| Table | Primary Key |
Customer | CustomerID |
Product | ProductID |
Order | OrderID |
OrderDetails | OrderDetailsID |
Table Relationship Details
| Table | Related With | Foreign Key |
Orders | Customers | CustomerID |
OrderDetails | Orders | OrderID |
OrderDetails | Products | ProductID |
Have a look into the below mentioned database design image for more ideas.
(Figure 1 – Customer Order Management System Database Design diagram.)
Create Database and Tables in Microsoft SQL Server
Before creating the tables, we have to create our own database in SQL Server. Please note that I am using Microsoft SQL Server Management Studio Express for creating database and tables.
Open the Microsoft SQL Server Management Studio Express and you could see the window as given below:
(Figure 2 – Microsoft SQL Server Management Studio Express)
Now, for creating the database, please follow the steps as mentioned here by Microsoft tech net guys. I assume that you are giving database name as Customer Database. After creating the database, you could see some predefined objects getting displayed under your database in object explorer tree such as Tables, Database Diagram, etc. Please see the below image.
(Figure 3 – Customer Order Management System Database)
Now we are all set to go ahead with table creation.
To create a new table with Table Designer:
- Right-click the Tables item of your database in Object Explorer and click New Table.
- Type column names, choose data types, and choose whether to allow nulls for each column.
- From the File menu, choose Save table name.
- In the Choose Name dialog box, type a name for the table and click OK.
Create all the four tables that I have mentioned in the database design section with proper primary key. After creating all the tables, we also have to create foreign key for the tables as I have mentioned in database design section.
You can create foreign key using SQLQuery analyzer. Click the New Query button which is available in the SQL Express toolbar, and paste the below queries in query window, select all the queries and press F5. Once you’re done with this, foreign key will be created automatically.
ALTER TABLE Orders WITH CHECK ADD CONSTRAINT [FK_CustomerID] FOREIGN KEY([CustomerID])
REFERENCES [Customers] ([CustomerID])
ALTER TABLE OrderDetails WITH CHECK ADD CONSTRAINT [FK_OrderID] FOREIGN KEY([OrderID])
REFERENCES [Orders] ([OrderID])
ALTER TABLE OrderDetails WITH CHECK ADD CONSTRAINT [FK_ProductID] FOREIGN KEY([ProductID])
REFERENCES [Products] ([ProductID])
I assume that you have done everything successfully upto this stage. Oh great :). Now we’re done with customer order management system database design. See the below image which has four tables and some sample customer’s data.
(Figure 4 – Customer Order Management System Database with Tables)
I hope that you have got an idea about how to design the database.
Entity Container and Library Implementation
As we discussed in the architecture diagram which is explained in part 1, we are going to discuss and implement the second level from the bottom (Data Access Layer). See the image given below:
(Figure 5 – Library details)
You could see that I have mentioned clearly about what language and technology we are going to use for implementing this layer with projects name details. So before starting with implementation, we will discuss a bit about the purpose of this layer.
Typically Data Access Layer is for communicating with database to store/retrieve the data. Earlier, we used to write this layer with the help of pure ADO.NET. But now, .NET Framework provides ADO.NET Entity Data Model utilities by default. So using Entity Data Model Wizard, we can create edmx file which describes the target database schema, and defines the mapping between the EDM and the database.
Why class library for interacting with edmx? Can’t we use edmx file directly in business logic layer? Yes. Better not to use edmx file directly in business logic layer. Business logic should be an independent layer. If you use edmx file directly in business logic, that means you are losing extensibility of your platform. For instance, if you add some additional field in the database; you have to touch your business logic again. To avoid this dependency, we are exposing interfaces and methods in library to interact with the edmx file.
Create EDMX and Library Projects
We are here to learn how to create these two projects and what type of project templates needs to be selected.
Type of Projects
- A Class Library
- An Entity project with an edmx
- A Test Client project
The Solution
Open Visual Studio 2010 and select the Class Library project template from Visual C# -> windows. Look at the solution and project name in the given snapshot.
(Figure 6 – Project Template For Library)
I have given the same name (“ServiceLibaries.CustomerServiceLibrary”) as mentioned in the architecture diagram. I have added two folders and .cs files for the implementation in the solution. One is for CustomerServiceLibrary interface and another one is for the actual method implementation. But don’t worry; there is no code inside the files now. Here is the structure of my VS solution.
(Figure 7 – Library Project)
Let’s start creating an edmx file and then we will get back to the actual library implementation. Here are the steps for generating edmx file from a specific database.
- In solution explorer, right-click on the solution, add one more project for maintaining edmx files. Keep the project name as “
CustomerOrderManagementSystemDEM”. - Right-click
CustomerOrderManagementSystemDEM project, and then click Add-->New Item. - From the Add New Item dialog box, select Data in the left pane and select ADO.NET Data Entity Model.
- In Name, enter edmx file name “
CustomerOrderManagementSystemEntityDataModel” and click ok.
(Figure 8 – Solution with Edmx project)
Now you will be asked to proceed with Entity Data Model Wizard. Select Generate from Database option from the wizard and choose your data connection from the combo box. If you don’t find your database in combo box, click the New Connection button and choose your Server name, Data source, Authentication type (Leave it as Use Windows Authentication), Database name “Customer Database” and click ok.
(Figure 9 – New Connection Dialog Window)
Then you will get the page with connection string details as the given below. Click the next button and choose the tables that you want to generate entities. I selected all the four tables. Change the model namespace at bottom of the dialog window (optional), and click Finish button.
(Figure 10 – New Connection String)
(Figure 11 – Complete Database)
When you hit the Finish button, Visual Studio starts to generate the edmx file with app.config file for you. Once it’s generated, you could see ConnectionStrings tag in the XML file with other attributes such as connection string and provider name details.
<configuration />="1.0"="utf-8"
<configuration>
<connectionStrings>
<add name="Customer_DatabasesEntities"
connectionString="metadata=res://*/CustomerOrderManagementSystemEntityDataModel.
csdl|res://*/CustomerOrderManagementSystemEntityDataModel.ssdl|res:
//*/CustomerOrderManagementSystemEntityDataModel.msl;
provider=System.Data.SqlClient;
provider connection string='Data Source=XXXXX-PC\SQLEXPRESS;
Initial Catalog="Customer Databases";
Integrated Security=True;MultipleActiveResultSets=True'"
providerName="System.Data.EntityClient" />
</connectionStrings>
</configuration>
Apart from the configuration file, .edmx file (with code-behind) is also generated. If you open .cs file, you could see all the tables generated as an entity object class along with default object context class (“Customer_DatabasesEntities”). These are the class that we are going to use in our library to interact with database.
(Figure 12 – Entity Objects and Object Context Class)
That’s it. We’re done with the edmx file generation. Let’s come back with the library implementation, as we discussed already, we have two .cs files in the library project. Now we can directly go ahead with the implementation part. Here is the ICustomerServiceLibrary interface which contains the methods declaration to provide customer details as well as saving data in database.
public interface ICustomerServiceLibrary
{
List<Customer> GetCustomers(Customer_DatabasesEntities context);
Customer GetCustomer(int customerID, Customer_DatabasesEntities context);
Customer_DatabasesEntities GetEntitiesObjectContext();
void Save(Customer_DatabasesEntities context);
}
Interface provides four functionalities:
GetCustomers – Returns all the customers data from database with the help of entity object context. GetCustomer(int CustomerID) – Returns the specific customer details which takes customer id as argument. GetEntitiesObjectContext() – Returns the actual Customer Databases Entities object context. Save (context) – Saves the data in database.
Let’s go ahead with the actual implementation of the methods:
namespace ServiceLibraries
{
public class CustomerServiceLibrary : ICustomerServiceLibrary
{
Customer_DatabasesEntities context = null;
public CustomerServiceLibrary()
{
context = new Customer_DatabasesEntities();
}
#region ICustomerServiceLibrary Members
public List<Customer> GetCustomers(Customer_DatabasesEntities context)
{
return context.Customers.ToList() ;
}
public Customer GetCustomer(int customerID, Customer_DatabasesEntities context)
{
var cust = from customer in context.Customers
where customer.CustomerID == customerID select customer;
return cust.FirstOrDefault();
}
public void Save(Customer_DatabasesEntities context)
{
context.SaveChanges();
context.Dispose();
}
public Customer_DatabasesEntities GetEntitiesObjectContext()
{
return context;
}
#endregion
}
}
Constructor of the CustomerServiceLibrary class creates the entity object context instance and it’s shared across the class. In case, if engine wants the entity context object for saving data into the database, we can get through GetEntitiesObjectContext method via library instance.
Save method is used for saving new data in database. You can see context.SaveChanges() method called inside the Save method. This method saves the changes in database. To call this method, you have to add System.Data.Entity assembly reference in library project. Don’t forget to add this assembly in your library. Otherwise, you will get a compilation error.
That’s all. Now the library is ready for the customer data service. Can we test this library through some test client? Yes. Let’s create a simple console application for testing this library.
Test Client
I have added a test client project in the attached sample. This client project contains the code to displays the customer’s data in console. Also, it will ask you to type new customer details. After entering the new customer details, it will display all the customer data again from the database including new customer data. Remember that you should add both our library and edmx project in client project as reference. Here is the client code:
static void Main(string[] args)
{
ICustomerServiceLibrary service = new CustomerServiceLibrary();
List<customer /> list = service.GetCustomers(service.GetEntitiesObjectContext());
Console.WriteLine("Customer ID" + "First Name".PadLeft(21) +
"Last Name".PadLeft(29));
Console.WriteLine("========================================================");
int lastCustId = 0;
foreach (Customer data in list)
{
Console.WriteLine(data.CustomerID + data.FirstName.PadLeft(48) +
data.LastName.PadLeft(10));
lastCustId = data.CustomerID;
}
Customer newCustomer = new Customer();
Console.WriteLine("Enter New Customer Details");
Console.WriteLine("First Name:");
newCustomer.FirstName = Console.ReadLine();
Console.WriteLine("Last Name");
newCustomer.LastName = Console.ReadLine();
Console.WriteLine("Email Id");
newCustomer.Email = Console.ReadLine();
Console.WriteLine("Address1");
newCustomer.Address1 = Console.ReadLine();
Console.WriteLine("Address2");
newCustomer.Address2 = Console.ReadLine();
newCustomer.CustomerID = ++lastCustId;
service.GetEntitiesObjectContext().AddToCustomers(newCustomer);
service.Save(service.GetEntitiesObjectContext());
Console.WriteLine("Customer Data Stored Successfully");
service = new CustomerServiceLibrary();
List<customer /> newlist = service.GetCustomers(service.GetEntitiesObjectContext());
Console.WriteLine("Customer ID" + "First Name".PadLeft(21) +
"Last Name".PadLeft(29));
Console.WriteLine("=========================================================");
foreach (Customer data in newlist)
{
Console.WriteLine(data.CustomerID + data.FirstName.PadLeft(48) +
data.LastName.PadLeft(10));
}
Console.ReadLine();
}
Test Client Output
(Figure 13 – Test Client)
Note
Make sure that connection string is mentioned in the client config file. Otherwise application never connects with the database.
For running the attached sample application, you will have to create the database and tables in your SQL Server management studio express. But, for your convenience, I have attached the database backup file, so that you can just download and import into your SQL server. If you have any questions about how to import the database in SQL Server, please visit this site.
Oops. At the last moment of completing this article, my colleague Boopalan who is also good in tech side raised this question. Why don’t you integrate your database in Visual Studio itself? Yes. He is absolutely correct. Why do I want to kill your time for running demo sample? So I decided to explain how to manage/integrate the database in Visual Studio itself and posted an article here. If you decided to go ahead with this logic, you don’t want to restore the database backup file that is uploaded along with in the sample source. However, I still leave this traditional database design concept which I have explained in this article since it will be helpful for some folks who don’t have idea about it.
I am really delighted with the logic and sample app, and I do think it's really easy to run from your end. As such, I sure would really appreciate some votes, and some comments if you feel this post will help you out in entity access through library :).
As I stated in the introduction, the remaining parts will be posted one by one.
