Category Archives: Database

The World of art database

Code-First Entity Framework’s Fluent API

By Tim Daniels
This is a follow-up to my post on the Code-First Development Workflow. In that post I described how the Code-First workflow automatically generated an object model and a SQL Server database, by implementing the concept of convention over configuration. With this approach, Code-First assumes that the POCO classes defined in the Entities class library, follow the conventions of the schema that Entity Framework uses for a conceptual model. If this is the case, then Entity Framework is able to work out the details of what needs to be done, to form the object model and the relational database. However, if the conventions established in the POCO classes do not produce the desired result, you still have the ability to add configurations to your classes. As we look more closely at this approach, you will see that sometimes the best solution, is a compromise between two opposing techniques and technologies.

When I defined the domain entities for the Action Manager app, I created a one-to-many relationship between the customer object and the project object. In other words, a customer can have one-to-many projects. Here is the code where this is accomplished.

    public class Customer
    {
        public int CustomerId { get; set; }
        public string Name { get; set; }
        public string ContactPerson { get; set; }
        public string Phone { get; set; }
        public string Fax { get; set; }
        public string EmailAddress { get; set; }

        public virtual List<Project> Projects { get; set; }
    }
    public class Project
    {
        public int ProjectId { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public DateTime StartDate {get; set; }
        public DateTime EndDate {get; set; }
        public double BudgetedCost { get; set; }
        public double ActualCost {get; set; }
        public double EstimatedHours { get; set; }
        public double ActualHours { get; set; }
        public double BillableHours { get; set; }
        public double PercentEstHours { get; set; }
        public double PercentBudgetCost { get; set; }

        public virtual Customer Customer { get; set; }
        public virtual List<Task> Tasks { get; set; }
        public virtual List<Rate> Rates { get; set; }
        public virtual List<TimeSheetEntry> TimeSheetEntries { get; set; }
    }

public class Customer

{

public int CustomerId { get; set; }

public string Name { get; set; }

public string ContactPerson { get; set; }

public string Phone { get; set; }

public string Fax { get; set; }

public string EmailAddress { get; set; }

public virtual List<Project> Projects { get; set; }

}

public class Project

{

public int ProjectId { get; set; }

public string Name { get; set; }

public string Description { get; set; }

public DateTime StartDate {get; set; }

public DateTime EndDate {get; set; }

public double BudgetedCost { get; set; }

public double ActualCost {get; set; }

public double EstimatedHours { get; set; }

public double ActualHours { get; set; }

public double BillableHours { get; set; }

public double PercentEstHours { get; set; }

public double PercentBudgetCost { get; set; }

public virtual Customer Customer { get; set; }

public virtual List<Task> Tasks { get; set; }

public virtual List<Rate> Rates { get; set; }

public virtual List<TimeSheetEntry> TimeSheetEntries { get; set; }

}

In the customer class, there is a virtual property called Projects, which is defined as a generic list of Project objects. This is known as a navigation property. The project class also has a navigation property of type customer. In this case, the navigation properties establish the convention that a customer can have one to many projects. With this bit of information, Entity Framework created the Customers table and the Projects table in the SQL Server database as illustrated here;

Fluent API Customers table and Projects table

Fluent API Cutomers table and Projects table

As you can see from the code snippet above, I chose not to clutter up the project class with a property for customer ID. However, this caused Entity Framework to create a foreign key relationship between projects and customers that allows the foreign key to be null. Obviously, this was not my intention.

With the code-first fluent API, I can configure the contextual model to generate the foreign key relationship between project and customer ID as not nullable. Here is the code where this is accomplished.

    public class ProjectManagementContext : DbContext
    {

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<Project>().HasRequired(c => c.Customer);

public class ProjectManagementContext : DbContext

{

protected override void OnModelCreating(DbModelBuilder modelBuilder)

{

base.OnModelCreating(modelBuilder);

modelBuilder.Entity<Project>().HasRequired(c => c.Customer);

The DbContext class has an overridable method called OnModelCreating. This is where I can tweak the conventions of the entity classes, with specific configurations, to produce the desired result. In this case, the HasRequired method of the modelBuilder class, configures the Customer property of the Project entity, to be required for input. This configuration causes the foreign key relationship between project and customer ID to be defined as not nullable, as illustrated here.

Fluent API Configuration of Customers table and Projects table

Here is an outstanding article by Julie Lerman on Code First Relationships Fluent API. Julie covers this subject in more detail with several different examples of configuration through the fluent API.

Managing Database Connections

Leave a reply

By Tim Daniels

Before an application can access information from a database, a proper connection to the server hosting the database, and the database itself, must be established. Managing database connections to optimize application performance, and application maintainability, is relatively easy to accomplish on the Microsoft .NET platform. To demonstrate this, I’ve developed a C#.NET class, which utilizes three key components for managing database connections;

Configuration File

First, lets discuss the configuration file. A configuration file in .NET, is simply an XML file with parent/child nodes. Each node can contain a set of both system defined, and programmer defined, key/value pairs. These key/value pairs contain application settings to control specific application and program level functions. The configuration file can be accessed by program code throughout an application. Many of the project templates in Visual Studio will automatically generate a configuration file for the project. However, a configuration file can be manually added to a project simply by right clicking on the project name in the Solution Explorer, taking the add new item menu option, and then selecting Application Configuration File, as illustrated here;

Adding an application configuration file to a project in Visual Studio 2012

This will add an XML file called App.config to the Visual Studio project. There is one subtle difference when adding a configuration file to a website project. The configuration file is called Web Configuration File, and adds an XML file called Web.config to the project, as illustrated here;

Adding a web configuration file to a website project in Visual Studio 2012

The beauty of the configuration file, is that it’s deployed along with the application. Configuration files can be changed as needed. Developers can use configuration files to change settings without recompiling the application. Administrators can use configuration files to set policies that affect how applications run on their computers.

Here is the application configuration file I created for an application I’m currently developing.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=4.4.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
  </entityFramework>
  <connectionStrings>
    <add name="DbConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Lexicon.ActionManager.ProjectManagementContext;Integrated Security=True"/>
  </connectionStrings>
</configuration>

<?xml version="1.0" encoding="utf-8"?>

</configSections>

</entityFramework>

</connectionStrings>

</configuration>

As you can see there are several XML nodes dealing with Entity Framework, which is another component I’m using in this application. For this discussion we are only concerned with the “configuration” node and its child node “connectionStrings”.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <connectionStrings>
    <add name="DbConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Lexicon.ActionManager.ProjectManagementContext;Integrated Security=True"/>
  </connectionStrings>
</configuration>

<?xml version="1.0" encoding="utf-8"?>

</connectionStrings>

</configuration>

Now lets look at an example of using the configuration file to manage database connections in an application. Below is a C# class I wrote called “ConnectionsStrings”. Here is how the class is implemented in an application that access a SQL Server database.

Database Connection Management Solution
The objectives for developing a database connection management solution are as follows;

Develop a reusable class with flexibility to retrieve a connection string from the App.config file, or the Web.config file, and return the string to the consumer
Implementation of the ConfigurationManager class, to take advantage of having the database connection string coded in one place, the App.config file. Thus, eliminating the need to have it hard-coded throughout the application. This improves overall maintainability
Implementation of the SqlConnectionStringBuilder class, to supply additional values to the connection string, such as user name and password

Configuration Manager
The ConnectionStrings class below, has a method called BuildConnectionString. You will notice that the method is also overloaded with a version that will accept the following parameters;

dataSource
userName
userPassword

The BuildConnectionString method implements two classes from the System.Configuration name space.

The ConfigurationManager class has a property called ConnectionStrings. This property gets the connection strings section data from the current application’s default configuration file. It returns a connection string settings collection object. This collection object holds the contents of the connection strings section of the configuration file. Refer back to the configuration file example above to see the contents of the connection strings section. Remember how I described the contents of the configuration file as key/value pairs. In this case, the key is name=”DbConnection”. The value associated with this key is connectionString=”Data Source=.\SQLEXPRESS;Initial Catalog=Lexicon.ActionManager.ProjectManagementContext;Integrated Security=True”.

The following line of code retrieves the value associated with “DbConnection” and assigns it to variable called settings which is a type of ConnectionStringSettings.

ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["DbConnection"];

1	ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["DbConnection"];

Don’t forget to add a reference to the System.Configuration name space in your project and put the using System.Configuration statement at the top of the class.

Connection String Builder
Now lets take a look a the SqlConnectionStringBuilder class. This class comes in handy when additional information needs to be added to the connection string in the configuration file. In cases where it may not be practical to store the connection string in a configuration file, it can be used to build the entire connection string from a set of parameters. The SQLConnectionStringBuilder class has numerous properties for configuring all kinds of settings related to database connectivity. In my ConnectionStings class, I overloaded the BuildConnectionString method to accept data source, user name and password as parameters, that supply additional information for the connection string retrieved from the configuration file.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;

namespace Lexicon.ActionManager.Connections
{
    /// <summary>
    /// Retrieves a connection string from the app.config file or the web.config file.
    /// </summary>
    public static class ConnectonStrings
    {
         static ConnectonStrings()
        {
        }

        /// <summary>
         ///  Retrieves a connection string from the app.config file or the web.config file.
        /// </summary>
        /// <returns>Connection string property of connection string settings class</returns>
         public static string BuildConnectionString()
         {
             // Retrieve the connection string named "DbConnection" from the app.config file or web.config file
             ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["DbConnection"];
             return settings.ConnectionString;
         }

        /// <summary>
         ///  Retrieves a partial connection string from the app.config file or the web.config file and build a connection string from parameters 
        /// </summary>
        /// <param name="dataSource">Server name hosting data base</param>
        /// <param name="userName">User ID granted access to the database</param>
        /// <param name="userPassword">Password associated with the User ID</param>
        /// <returns>Connection string property of SQL Connection String Builder class</returns>
         public static string BuildConnectionString(string dataSource, string userName, string userPassword)
         {
             // Retrieve the connection string named "DbConnection" from the app.config file or web.config file
             ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["DbConnection"];
             
             SqlConnectionStringBuilder csBuilder;
             if (settings != null)
             {
                 csBuilder = new SqlConnectionStringBuilder(settings.ConnectionString);
                 //supply additional values to the connection string
                 csBuilder.DataSource = dataSource;
                 csBuilder.UserID = userName;
                 csBuilder.Password = userPassword;
                 return csBuilder.ConnectionString;
             }
             else
             {
                 return "Invalid connection string";
             }
         }
    }
}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Configuration;

using System.Data.SqlClient;

namespace Lexicon.ActionManager.Connections

{

/// <summary>

/// Retrieves a connection string from the app.config file or the web.config file.

/// </summary>

public static class ConnectonStrings

{

static ConnectonStrings()

{

}

/// <summary>

/// Retrieves a connection string from the app.config file or the web.config file.

/// </summary>

/// <returns>Connection string property of connection string settings class</returns>

public static string BuildConnectionString()

{

// Retrieve the connection string named "DbConnection" from the app.config file or web.config file

ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["DbConnection"];

return settings.ConnectionString;

}

/// <summary>

/// Retrieves a partial connection string from the app.config file or the web.config file and build a connection string from parameters

/// </summary>

/// <param name="dataSource">Server name hosting data base</param>

/// <param name="userName">User ID granted access to the database</param>

/// <param name="userPassword">Password associated with the User ID</param>

/// <returns>Connection string property of SQL Connection String Builder class</returns>

public static string BuildConnectionString(string dataSource, string userName, string userPassword)

{

// Retrieve the connection string named "DbConnection" from the app.config file or web.config file

ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["DbConnection"];

SqlConnectionStringBuilder csBuilder;

if (settings != null)

{

csBuilder = new SqlConnectionStringBuilder(settings.ConnectionString);

//supply additional values to the connection string

csBuilder.DataSource = dataSource;

csBuilder.UserID = userName;

csBuilder.Password = userPassword;

return csBuilder.ConnectionString;

}

else

{

return "Invalid connection string";

}

Final Implementation
In order to make the ConnectionStrings class as reusable as possible, I’m hosting it within a separate class library called “Connections”. This class library is referenced by a console application project I developed for unit testing purposes. This is illustrated in the solution explorer of Visual Studio 2012.

Solution Explorer with Connections class library

Finally, here is one implementation of the ConnectionStrings class, where it is used to populate the connection string property of a database instance of a database context class.

using Lexicon.ActionManager.Connections;

namespace TestEntityFrameworkModel
{
    public class Program
    {
        static void Main(string[] args)
        {
            using (var pmContext = new ProjectManagementContext())
            {
                pmContext.Database.Connection.ConnectionString = ConnectonStrings.BuildConnectionString();

using Lexicon.ActionManager.Connections;

namespace TestEntityFrameworkModel

{

public class Program

{

static void Main(string[] args)

{

using (var pmContext = new ProjectManagementContext())

{

pmContext.Database.Connection.ConnectionString = ConnectonStrings.BuildConnectionString();

Layered Architecture & Separation of Concerns

2 Replies

Now that I have dealt with my philosophical and project management issues, we can get back to improving the design of the World of Art web app. If you recall my previous post on Improving the Design, I identified several objectives for improving the application design;

Eliminate the tight coupling of the user interface and data access, by extracting database connectivity and data access, into a separate architectural layer of the application.
Design and build an abstracted view of the data access domain.
Develop an interface to the data access domain.

A tremendous amount of progress towards meeting these objectives, can be gained by implementing the Microsoft ADO.NET Entity Framework. The Entity Framework provides a means for achieving our design objects. However, the Entity Framework provides even greater benefits;

Enables me to program against a conceptual application model instead of programming directly against a relational database schema.
Decreases the amount of code and maintenance required for data-oriented applications.
Provides freedom to focus more on the problem/business domain issues, and much less on database access and query issues.

Now with all that said, the ADO.NET Entity Framework is no the complete solution for improving the design of our application. The core purpose of our design objectives is to establish a separation of concerns, by creating distance between dissimilar aspects of our code.

Separation of Concerns

Kyle Baley, Donald Belcham and James Kovacs, wrote an excellent series in MSDN Magazine called Extreme ASP.NET Makeover. Part six of this series deals with the subject of separation of concerns.

Another design improvement objective, is to introduce a layered architecture to our design. The traditional layered architecture of a software application, utilizes three basic layers for separating coding concerns;

User Interface
Business Logic
Data Access

Layered Architecture

Applying a layered architectural design to an app is always good practice, because it simplifies each component of the app, and improves maintainability in a way that lends itself very nicely to evolutionary improvements, as opposed to total re-writes. However, applying a layered design to large, complex, enterprise level applications, can introduce other levels of tight coupling which should be avoided.

Hendry Luk wrote a very good piece on this subject. See Software Development Fundamentals, Part 2: Layered Architecture

Jeffrey Palermo also tackles this issue in his series on The Onion Architecture.

Please keep in mind, the concepts covered by Hendry Luk and Jeffrey Palermo, are intended for large enterprise level apps, with complex behaviors and long running life cycles. However, it is good to study and understand these concepts. As developers, it is our responsibility to determine the best practical application of these concepts, based on a desire to satisfy our customer’s needs and our project requirements.

In my next post, we will introduce a Data Model Layer to our application. In this layer, we will utilize the ADO.NET Entity Framework, to create an object model over the World of Art Relation Database.

Improving the Design

4 Replies

As previously posted, The artist content page has been added to The World of Art web app. The ASP.net Web Form Model was implemented as the foundation for developing this component of the application.

Using the ASP.net Web Form model, provided a stable architectural software development platform, along with off the shelf software components, which facilitated rapid development and deployment, of the artist content page. If you recall, I used the ASP.Net SQL Data Source Control, to query artist information from the database. However, after spending a little time studying the overall design of the artist page, I do have some concerns regarding the use of the SQL Data Source Control;

More consideration should be given to scale, as the size of the artist database is certain to grow much larger.
Using the SQL Data Source control is acceptable for small applications. However, as the application grows in size and complexity, we will find this approach is too tightly coupled with the user interface. The current approach will require each web page, that renders dynamic database content, to have its own unique interface to the data source. This introduces some vulnerability to the impact of change. With each web page developed, with this tight coupling to the data source, the impact of future changes to data access requirements increases . As the impact of change increases, so does the level of effort in code maintenance.

In my next post, I will discuss an architectural approach for extracting data access into a separate design layer of the application, thus eliminating this tight coupling with the user interface layer. We will also design and build an abstracted view of the data access domain, like we did with the World of Art Domain. We will then develop an interface to our data source domain. ASP.Net provides some off the shelf components, which make this more sophisticated design, surprisingly easy to implement.

Date Limitation in SQL Server Database

Leave a reply

When you visit the Artist Page at the World of Art website. Notice how the artist from the Renaissance era, all have the same birthday. While building the database, I discovered a limitation in the Microsoft SQL Server Database. While the SQL Server Database Engine does support data types of date and time, the earliest date it can support is January 1, 1753. Isn’t that peculiar?

Since art work spans the know history of mankind, at some point in the development life cycle of this application, I’m certain we will encounter requirements dealing with the historical aspects of the art world domain. At that time we will need to formulate some kind of workaround to this problem.

Database Deployment

Leave a reply

I selected WinHost as the provider for hosting the World of Art web app and the supporting SQL Server Database. WinHost provides affordable ASP.Net web hosting. Publishing and deploying the database to WinHost was very easy.

Database deployment in two easy steps

The Database

Leave a reply

I started off with a very simple set of interfaces and classes, as a small first step, in defining the domain of the application. Now that the artist object, and works of art object are defined in the World of Art domain, I though it would be a good time to develop the first iteration of the database. This is the data which will populate the world of art objects and their properties. In my opinion, it is the domain that must first be defined, then the requirements of the database can be derived from the domain.

World of Art Database – First Iteration

M	T	W	T	F	S	S
« Jun
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	31

The Lexicon of Software

Devoted to the Art and Science of Software Development

Category Archives: Database

Code-First Entity Framework’s Fluent API

Managing Database Connections

Layered Architecture & Separation of Concerns

Improving the Design

Date Limitation in SQL Server Database

Database Deployment

The Database

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this: