Using a Sybase with S#arp Architecture and nHibernate

This article will discuss how I achieved using a Sybase database alongside SQL Server with S#arp Architecture / nHibernate. In this article, I would assume that you use S#arp and already have a running project where you want to include Sybase in your scenario. So let's start.

1. Create your Sybase Connection String

Go to your web config and add a connection string like such:

<connectionStrings>
  <add name="SybaseConnectionString"
  connectionString="Data Source='{YOURSYBASEDATABASE}';
  Port=5000;UID='{xxxxxx}';Password='{xxxxxx}';Database='{SomeDatabase}'"/>
</connectionStrings>

2. Create a Domain for your Sybase Tables

For this demo, we will reference a table from a Sybase database called Activity.

First, we create a class library project to separate your existing Domain to your Sybase Domain. We will call it Sybase.Domain.

In that project, we will create a class to represent the “Activity” table.

namespace Sybase.Domain
{
    public class Activity
    {
        public virtual string Code { get; set; }

        public virtual string Name { get; set; }

        public virtual string Type { get; set; }

        public virtual string SubType { get; set; }
    }
}

3. Create a Class to Grab that Connection String

For a cleaner implementation, we create an Infrastructure Class Library Project, under the Infrastructure Solution Folder and call it Sybase.Infrastructure. This project will be a container for infrastructure related items such as repositories. We will also create an Interface for it so we don’t reference Infrastructure in our main Web project and use the already exposed Domain. Having said that, we will be creating that Interface in the Sybase.Domain project.

At this stage, import the highlighted references on the image below.

Let's create your RepositoryConfiguration Class which will expose your connection string:

using System.Configuration;
using Sybase.Domain.Contracts.Configuration;

namespace Sybase.Infrastructure
{
    public class RepositoryConfiguration : IRepositoryConfiguration
    {
        public string ConnectionString
        {
            get
            {
                string connectionString = ConfigurationManager.ConnectionStrings
                ["SybaseConnectionString"].ConnectionString;

                return connectionString;
            }
        }
    }
}

Then the interface in your Sybase.Domain project:

namespace Sybase.Domain.Contracts.Configuration
{
    public interface IRepositoryConfiguration
    {
        string ConnectionString { get; }
    }
}

4. Create a Mapping Extension

Now you have your domain, we need to create a reusable Mapping mechanism so Sybase column names map properly to the Sybase Domain Classes. At this stage, we will be creating it in another class library project which we call Framework, this will contain Framework Related Items like Referenced Assemblies, Extensions as well as Helpers. Let’s call it Demo.Framework.

Inside the project, we create an Extensions folder and create our AutoMappingExtension class:

using System;
using System.Collections.Generic;
using System.Data;

namespace Demo.Framework.Extensions
{
    public static class AutoMappingExtension
    {
        public static IList<T> Map<T>(this IDataReader dataReader) where T : new()
        {
            IList<T> list = new List<T>();

            if (dataReader != null)
            {
                while (dataReader.Read())
                {
                    var destination = new T();
                    Type destinationType = destination.GetType();

                    for (int i = 0; i < dataReader.FieldCount; i++)
                    {
                        string sourceFieldName = dataReader.GetName(i);
                        string destinationPropertyName = sourceFieldName.InflectTo().Pascalized;

                        var destinationProperty = destinationType.GetProperty(destinationPropertyName);

                        if (destinationProperty != null && destinationProperty.CanWrite)
                        {
                            object destinationValue = FormatDestinationValue(dataReader[i], 
                            destinationProperty.PropertyType.GetUnderlyingType());
                            destinationProperty.SetValue(destination, destinationValue, null);
                        }
                    }

                    list.Add(destination);
                }

                dataReader.Close();
            }

            return list;
        }

        public static Type GetUnderlyingType(this Type source)
        {
            if (source.IsGenericType && 
            (source.GetGenericTypeDefinition() == typeof(Nullable<>)))
            {
                // source is a Nullable type 
                                           // so return its underlying type
                return Nullable.GetUnderlyingType(source);
            }

            // source isn't a Nullable type so just return the original type
            return source;
        }

        private static object FormatDestinationValue(object value, Type destinationType)
        {
            if (value == null || value == DBNull.Value)
            {
                return null;
            }

            TypeCode destinationTypeCode = Type.GetTypeCode(destinationType);

            switch (destinationTypeCode)
            {
                case TypeCode.String:
                    value = value.ToString().TrimEnd();
                    break;

                case TypeCode.DateTime:
                    value = Convert.ToDateTime(value);
                    break;
            }

            return value;
        }
    }
}

Please note that we will use Inflector Extension (http://brendanjerwin.com/blog/2010/02/25/inflector-extension/) to provides a convenient Inflect() extension method on string and int data types:

5. Create Your First Sybase Repository

Now you have everything you need, let's create your first Repository which will be in your Sybase.Infrastructure project. Let's call it ActivitiesRepository. You will also need to create your Interface in Sybase.Domain project.

Let's do a simple GetBy, so here is your method:

using System;
using Sybase.Domain.Contracts.Configuration;
using System.Collections.Generic;
using Sybase.Domain;
using Sybase.Data.AseClient;
using Sybase.Domain.Repositories;
using System.Data;
using Demo.Framework.Extensions;
namespace Sybase.Infrastructure
{
    public class ActivitiesRepository : IActivitiesRepository
    {
        private readonly IRepositoryConfiguration repositoryConfiguration;
        public ActivitiesRepository(IRepositoryConfiguration repositoryConfiguration)
        {
            this.repositoryConfiguration = repositoryConfiguration;
        }
        public IList<Activity> GetBy(string activityCode)
        {
            try
            {
                IList<Activity> activities;

                using (var connection = new AseConnection(repositoryConfiguration.ConnectionString))
                {
                    var command = connection.CreateCommand();
                    command.CommandType = CommandType.Text;

                    command.CommandText = @"SELECT
                                            activity_code as Code,
                                            activity_name as Name,
                                            activity_type as Type,
                                            activiy_subtype as SubType
                                            FROM dbo.activity 
                                            WHERE activity_code = '" + activityCode + "'";

                    connection.Open();

                    using (var dataReader = command.ExecuteReader())
                    {
                        activities = dataReader.Map<Activity>();
                    }
                }

                return activities;
            }
            catch (Exception ex)
            {
                // TODO: Log exception
                return null;
            }
        }
    }
}

Then your Interface in your Sybase.Domain project:

using System.Collections.Generic;

namespace Sybase.Domain.Repositories
{
    public interface IActivitiesRepository
    {
        IList<Activity> GetBy(string activityCode);
    }
}

6. Register the Projects You Just Created in CastleWindsor’s Component Registrar

Go to your Presentation Layer, in this case it's Demo.Web.Mvc. In the CastleWindsor Folder, there is a class called ComponentRegistrar. Add the following codes to under the AddQueryObjectsTo object.

container.Register(
    AllTypes.FromAssemblyNamed("Sybase.Infrastructure")
        .Pick()
        .WithService.FirstNonGenericCoreInterface("Sybase.Domain"));

You also need to reference your Sybase Related Projects here:

7. Create your Query Object and Test Your Code

In this instance, we create a method in your controllers queries and call it GetActivities.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using SharpArch.NHibernate;

namespace Demo.Web.Mvc.Controllers.Queries
{
    public class HomeQuery : NHibernateQuery
    {
        private readonly Sybase.Domain.Contracts.
        Repositories.IActivitiesRepository activitiesRepository;
        public HomeQuery(
              Sybase.Domain.Contracts.Repositories.IActivitiesRepository activitiesRepository
            )
        {
            this.activitiesRepository = activitiesRepository;
        }

        public void GetActivities(string activityCode)
        {
            activitiesRepository.GetBy(activityCode);
        }

    }
}

Now run and test your code.

Filed under: Architecture, CodeProject, Programming Tagged: C#, NHibernate, S#arp Architecture