Using a Sybase with S#arp Architecture and nHibernate

This article will discuss on 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 have already a running project where you want to include Sybase in your scenario.  So lets 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 you 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

Lets 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 and 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 lets create your first Repository which will be in your Sybase.Infrastructure project, Lets call it ActivitiesRepository.  You will also need to create your Interface in Sybase.Domain project.

Lets do a simple Get By, 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 its 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.