Facade to disparate databases

Introduction 

The aim of this article is to illustrate how Object Relational Mapper (ORM) models can be utilised to provide a common façade to disparate databases, thereby enabling you to write one system that can service multiple, albeit similar, databases. I will show a sample of a single WCF service, using Entity Framework, which interfaces with two distinct databases schemas (MSSQL and Oracle). I wanted to avoid compiler directives forcing the use of a certain ORM model; as well as conditional code directing the flow into duplicated sections.

The sample databases both implement a license consumption system. A single WCF web service will interface with an object context, the conceptual model of which is common to both the object contexts for the MSSQL and the Oracle databases. The store schema and mapping schema will be unique to each distinct database. The service code can therefore be written once and it will implement its functionality in both databases.

This article is meant to illustrate the combining of a single service for two (or more) databases, it won't delve into the technical intricacies beyond the sample. You know your own systems better than anybody else and are best placed to work out details in your own implementation.

Background

Reasons 

There are two distinct products, one written using MSSQL server and another using Oracle, recently brought under the same management team. Each of these products has their own market share and distinct identity. However there are points of commonality, chiefly of interest is the area of license consumption viz issuing and consumption of the right to utilize a product/service as desired.

The Oracle product has a distinct database bias and is the more flexible setup. Neither of the schemas presented here are from the actual systems.

Limitations

Neither of the systems can be modified in any significant way as they support existing (almost legacy) systems, however objects can be added to either system (table columns) with impunity.

Written using .Net 3.5 for the Devart (Oracle) and a matching EDMX model for the MSSQL instance, as the Devart I have access to is rather old and only works in 3.5. The web service is written in .Net 4.0.

Existing Systems

The MSSQL Schema, note the single USES table and the separate FREE PERIODS table.

The Oracle Schema, note the divided USES tables and the integrated FREE PERIODS (in the PERMITS table).

CREATE TABLE PERMITS
  (
   PERMIT_UID                       NUMBER(10) NOT NULL,
   NAME                             VARCHAR2(400),
   LICENSED_PERMITS                 NUMBER(10),
   FREE_PERMITS                     NUMBER(10),
   FREE_WINDOW                      NUMBER(10),
   LICENSEE_UID                     NUMBER(10),
   DATE_CREATED                     DATE DEFAULT SYSDATE NOT NULL, 
   PRIMARY KEY (PERMIT_UID)
  );

COMMENT ON COLUMN PERMITS.FREE_WINDOW IS 'Window of time a free permit is allowed, in days';

CREATE TABLE LICENSED_PERMITS_CONSUMED
  (
   LICENSED_PERMITS_CONSUMED_UID    NUMBER(10) NOT NULL,
   PERMIT_UID                       NUMBER(10) NOT NULL,
   DATE_CONSUMED                    DATE NOT NULL,
   PRIMARY KEY (LICENSED_PERMITS_CONSUMED_UID),
   CONSTRAINT FK_PERMITS
    FOREIGN KEY (PERMIT_UID)
    REFERENCES PERMITS(PERMIT_UID)
  );

CREATE TABLE FREE_PERMITS_CONSUMED
  (
   FREE_PERMITS_CONSUMED_UID        NUMBER(10) NOT NULL,
   LICENSED_PERMITS_CONSUMED_UID    NUMBER(10) NOT NULL,
   DATE_CONSUMED                    DATE NOT NULL,
   PRIMARY KEY (FREE_PERMITS_CONSUMED_UID),
   CONSTRAINT FK_LICENSED_PERMITS
    FOREIGN KEY (LICENSED_PERMITS_CONSUMED_UID)
    REFERENCES LICENSED_PERMITS_CONSUMED(LICENSED_PERMITS_CONSUMED_UID)
  );

Implementation 

Façade 

The initial plan called for a conceptual schema, common to both database schemas, but unique mapping and store schemas for each of the distinct systems.

This could be achieved in many ways, from modifying the mapping in the ORM model directly; to using views and stored procedures to present a new shape to one of the databases thereby bringing it closer to the other.

This article focuses on the latter option - making use of views and stored procedures via the models.

Once they are of a similar shape the ORM conceptual layer can be use to provide a façade, common to both systems. Please see the component diagram.

First I investigated both schemas to determine which would be the best to transform to the final conceptual model. In this case the Oracle database was chosen, mainly due to the ease of transforming the schema using views and the fact that the existing system uses a large number of stored procedures, views and other database objects.  

Database work

The plan calls for views to transform the existing Oracle schema into one that matches the MSSQL one, the ORM can then provide a common façade to both systems from there. However a straight transformation isn't going to work in all the cases as there would be issues, mainly around the primary keys.

The Entity Framework will create a primary key from all the non-null columns in the table if it can't find a primary key and ordinarily a database view lacks a primary key, but a disabled primary key can be added to the Oracle views to provide the definition of the primary key that suffices for the Entity Framework. Oracle doesn't allow enabled primary key on views as they are for performance, not data integrity; please refer to http://docs.oracle.com/cd/B10500_01/appdev.920/a96590/adg05itg.htm.

PERMITS => Licenses

This is the most straight forward transformation, just the join to the new FreePeriods (see below) view to complicate it a bit.

create or replace view LICENSES as
SELECT p.LICENSED_PERMITS as NumberOfLicenses, p.FREE_PERMITS as NumberOfFrees, p.NAME as Name, 
fp.Id  as FreePeriodId, p.PERMIT_UID as Id, p.LICENSEE_UID as LicenseeId
FROM PERMITS p
left join FREEPERIODS fp on p.FREE_WINDOW * (24*60*60) = fp.PERIODLENGTH;

alter view LICENSES
add constraint LICENSES_PK primary key (Id) disable;

PERMITS => FreePeriods

The Free Periods table is extracted from the Permits table, with a data transformation to change the type from days to seconds.

create or replace view FREEPERIODS as 
select rownum as Id, PeriodLength, Name from
(
 select distinct FREE_WINDOW * 24 * 60 * 60 as PeriodLength, TO_CHAR(FREE_WINDOW) || ' Days' as Name
 from PERMITS
);

alter view FREEPERIODS 
add constraint FREEPERIODS_PK primary key (Id) disable;

This however is reliant on Oracle's rownum pseudocolumn to provide a sort of primary key on the views, this isn't a true primary key and shouldn't be regarded as such. Please see http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm. Some thought must be applied when using these fields, so for example do not rely on this field to order data when displaying a list of Free Periods as it can change with each query; or when inserting Uses the rownum (via the view) will change as soon as the new record is inserted.

A more elegant solution is to add a column to the PERMITS table which can act as a primary key for the FreePeriods view. It will be populated by a trigger (so the existing legacy system won't have to change) and the number will come from a sequence (MSSQL's auto number). Below is the cursor which is used to populate the field on existing databases and the trigger to populate the value.

ALTER TABLE PERMITS
ADD FREEPERIOD_ID INTEGER;
/

DECLARE
  CURSOR C_FREEPERIOD_ID IS
    SELECT PERMIT_UID
    FROM PERMITS
    WHERE FREEPERIOD_ID IS NULL;
    
  FP_ID INTEGER;  
BEGIN
  FOR PERMIT_REC IN C_FREEPERIOD_ID
  LOOP
    SELECT FREE_PERIOD_ID_SEQ.NextVal
    INTO FP_ID
    FROM dual;
    
    UPDATE PERMITS p
    SET FREEPERIOD_ID = FP_ID
    WHERE p.PERMIT_UID = PERMIT_REC.PERMIT_UID;
  END LOOP;    
  COMMIT;
END;
/

ALTER TABLE PERMITS
MODIFY FREEPERIOD_ID NOT NULL;
/

CREATE OR REPLACE TRIGGER TRIG_PERMITS_FREEPERIOD_ID
  BEFORE INSERT ON PERMITS
  REFERENCING OLD AS OLD NEW AS NEW
  FOR EACH ROW
BEGIN
  --disregard any value inputted.
  SELECT FREE_PERIOD_ID_SEQ.NextVal
    INTO :new.FREEPERIOD_ID
  FROM dual;
END;
/

So now the FreePeriod view looks like this:

create or replace view FREEPERIODS as 
select Id, PERIODLENGTH as PeriodLength, NAME from
(
 select distinct FREE_WINDOW * 24 * 60 * 60 as PERIODLENGTH, TO_CHAR(FREE_WINDOW) || ' Days' as NAME,
 FREEPERIOD_ID as Id
 from PERMITS
);
/

alter view FREEPERIODS 
add constraint FREEPERIODS_PK primary key (Id) disable;
/

LICENSED_PERMITS_CONSUMED & FREE_PERMITS_CONSUMED => Uses

The two separate tables can be integrated using an Union statement, and with a join to provide the PERMIT UID column in the FREE table. To provide the primary key, the existing tables in the Oracle schema were modified, as with the FreePeriods table.

As a separate sequence is used to provide the number for the primary key, a continuous, non duplicating value can be produced on either of the Oracle tables.

ALTER TABLE LICENSED_PERMITS_CONSUMED
ADD USES_ID INTEGER;
/

DECLARE
  CURSOR C_USES_ID IS
    SELECT LICENSED_PERMITS_CONSUMED_UID
    FROM LICENSED_PERMITS_CONSUMED
    WHERE USES_ID IS NULL;
    
  US_ID INTEGER;  
BEGIN
  FOR LPC IN C_USES_ID
  LOOP
    SELECT USES_ID_SEQ.NextVal
    INTO US_ID
    FROM dual;
    
    UPDATE LICENSED_PERMITS_CONSUMED L
    SET USES_ID = US_ID
    WHERE L.LICENSED_PERMITS_CONSUMED_UID = LPC.LICENSED_PERMITS_CONSUMED_UID;
  END LOOP;    
  COMMIT;
END;
/

ALTER TABLE LICENSED_PERMITS_CONSUMED
MODIFY USES_ID NOT NULL;
/

CREATE OR REPLACE TRIGGER TRIG_LPC_USES_ID
  BEFORE INSERT ON LICENSED_PERMITS_CONSUMED
  REFERENCING OLD AS OLD NEW AS NEW
  FOR EACH ROW
BEGIN
  --disregard any value inputted.
  SELECT USES_ID_SEQ.NextVal
    INTO :new.USES_ID
  FROM dual;
END;
/

ALTER TABLE FREE_PERMITS_CONSUMED
ADD USES_ID INTEGER;
/

DECLARE
  CURSOR C_USES_ID IS
    SELECT FREE_PERMITS_CONSUMED_UID
    FROM FREE_PERMITS_CONSUMED
    WHERE USES_ID IS NULL;
    
  US_ID INTEGER;  
BEGIN
  FOR FPC IN C_USES_ID
  LOOP
    SELECT USES_ID_SEQ.NextVal
    INTO US_ID
    FROM dual;
    
    UPDATE FREE_PERMITS_CONSUMED f
    SET USES_ID = US_ID
    WHERE f.FREE_PERMITS_CONSUMED_UID = FPC.FREE_PERMITS_CONSUMED_UID;
  END LOOP;    
  COMMIT;
END;
/

ALTER TABLE FREE_PERMITS_CONSUMED
MODIFY USES_ID NOT NULL;
/

CREATE OR REPLACE TRIGGER TRIG_FPC_USES_ID
  BEFORE INSERT ON FREE_PERMITS_CONSUMED
  REFERENCING OLD AS OLD NEW AS NEW
  FOR EACH ROW
BEGIN
  --disregard any value inputted.
  SELECT USES_ID_SEQ.NextVal
    INTO :new.USES_ID
  FROM dual;
END;
/

Now that all the columns are in place, the final view can be done.

create or replace view USES as
select Id, LicenseId, DateTimeOfUse, Licensed from
(
  select PERMIT_UID as LicenseId, DATE_CONSUMED as DateTimeOfUse, 'Y' as LICENSED,
  USES_ID as Id
  from LICENSED_PERMITS_CONSUMED
  union
  select PERMIT_UID as LicenseId, frc.DATE_CONSUMED as DateTimeOfUse, 'N' as LICENSED,
  frc.USES_ID as Id
  from FREE_PERMITS_CONSUMED frc
  join LICENSED_PERMITS_CONSUMED lpc on lpc.licensed_permits_consumed_uid = frc.licensed_permits_consumed_uid
);
/
alter view USES 
add constraint USES_PK primary key (Id) disable;
/

The conceptual model, drawn from either of the source databases, in the final form:

With the conceptual model matched, the CrUD needs to be handled. The following illustrates the stored procedures needed to persist, delete and update the USES entity.

Inserting into Uses table

Reasonably straight forward, interrogate the licensed parameter to determine which table the data needs to be inserted into. Remember that the USES_ID column will be filled in by the trigger in both the LICENSED_CONSUMED and FREE_CONSUMED tables.

PROCEDURE ADD_TO_USE(v_license_id IN INTEGER, 
                     v_licensed IN CHAR, 
                     v_datetime_of_use IN DATE)
IS    
  v_dummy INTEGER;                
begin
  if (v_licensed = 'Y') then
     v_dummy := ADD_TO_LICENSED_CONSUMED(v_license_id, v_datetime_of_use);  
  else
     v_dummy := ADD_TO_FREE_CONSUMED(v_license_id, v_datetime_of_use);
  end if;
end ADD_TO_USE;

FUNCTION ADD_TO_LICENSED_CONSUMED(v_license_id IN integer, 
                                  v_datetime_of_use IN DATE) 
  return integer is
  v_uid integer;
begin
  select LICENSED_PERMITS_CONSUMED_SEQ.NEXTVAL
   into v_uid
  from dual;
  
  INSERT INTO LICENSED_PERMITS_CONSUMED(LICENSED_PERMITS_CONSUMED_UID, PERMIT_UID, DATE_CONSUMED)
  VALUES (v_uid, v_license_id, v_datetime_of_use); 
  
  return v_uid;
end ADD_TO_LICENSED_CONSUMED;

FUNCTION ADD_TO_FREE_CONSUMED(v_license_id IN integer, v_datetime_of_use IN DATE)
  return integer is
  v_uid integer;
  v_license_consumed_uid integer;
  v_period_length NUMBER;
begin  
   select FREE_PERMITS_CONSUMED_SEQ.NEXTVAL
   into v_uid
   from dual;
  
  SELECT p.free_window
  INTO v_period_length 
  FROM PERMITS p
  WHERE p.Permit_Uid = v_license_id;
  
  SELECT MAX(LICENSED_PERMITS_CONSUMED_UID)
  INTO v_license_consumed_uid
  FROM LICENSED_PERMITS_CONSUMED
  WHERE PERMIT_UID = v_license_id
  AND DATE_CONSUMED >= (v_datetime_of_use - v_period_length)
  ORDER BY DATE_CONSUMED;
  
  INSERT INTO FREE_PERMITS_CONSUMED (FREE_PERMITS_CONSUMED_UID, LICENSED_PERMITS_CONSUMED_UID, DATE_CONSUMED)
  VALUES (v_uid, v_license_consumed_uid, v_datetime_of_use); 
  
  return v_uid;
end ADD_TO_FREE_CONSUMED;

Updating Uses table

Again, interrogate the Licensed parameter and then update the table concerned. As the USES_ID value is passed through it is a simple matter to update either of the constituent tables.

PROCEDURE UPDATE_USE(v_use_id IN INTEGER, v_license_id IN INTEGER, v_datetime_of_use IN DATE, v_licensed IN CHAR)
  IS
BEGIN
  IF (v_licensed = 'Y') THEN
    UPDATE_LICENSED_CONSUMED(v_use_id, v_license_id, v_datetime_of_use);
  ELSE
    UPDATE_FREE_CONSUMED(v_use_id, v_datetime_of_use);
  END IF;
END UPDATE_USE;

PROCEDURE UPDATE_LICENSED_CONSUMED(v_use_id IN NUMBER, v_license_id IN INTEGER, v_datetime_of_use IN DATE)
  IS
BEGIN
  UPDATE LICENSED_PERMITS_CONSUMED l
  SET l.permit_uid = v_license_id,
      l.date_consumed = v_datetime_of_use
  WHERE l.uses_id = v_use_id;
END UPDATE_LICENSED_CONSUMED;

PROCEDURE UPDATE_FREE_CONSUMED(v_use_id IN NUMBER, v_datetime_of_use IN DATE)
  IS
BEGIN
  UPDATE FREE_PERMITS_CONSUMED f
  SET f.date_consumed = v_datetime_of_use
  WHERE f.uses_id = v_use_id; 
END UPDATE_FREE_CONSUMED;

Deleting from Uses table.

PROCEDURE DELETE_USE(v_use_id IN INTEGER, v_license_id IN INTEGER, v_licensed IN CHAR)
  IS
BEGIN
  IF (UPPER(v_licensed) = 'Y') THEN
    DELETE_LICENSED_CONSUMED(v_use_id, v_license_id);
  ELSE
    DELETE_FREE_CONSUMED(v_use_id);
  END IF;
END DELETE_USE;

PROCEDURE DELETE_LICENSED_CONSUMED(v_use_id IN NUMBER, v_license_id IN INTEGER)
  IS
BEGIN
  DELETE FROM LICENSED_PERMITS_CONSUMED l
  WHERE l.uses_id = v_use_id
  AND l.permit_uid = v_license_id;
END DELETE_LICENSED_CONSUMED;

PROCEDURE DELETE_FREE_CONSUMED(v_use_id IN NUMBER)
  IS
BEGIN
  DELETE FROM FREE_PERMITS_CONSUMED fr
  WHERE fr.uses_id = v_use_id;
END DELETE_FREE_CONSUMED;

The code above is the nuts and bolts of the integration process. Each table that needs to be transformed will need to be evaluated and the best approach determined. Most of the data types can be mapped across without data loss. Please see http://docs.oracle.com/cd/E10405_01/doc/appdev.120/e10379/ss_oracle_compared.htm for a good reference.

Provided changes can be made to the more constrained database, i.e. the columns widened, there shouldn't be any real obstacles preventing the fusion of the two systems to an acceptable extent. However total data fidelity is unlikely if the databases aren't reasonably close to start with.

The views shown earlier in the article show how it is possible to join tables together or separate out a section of a table to help align the two schemas together. Functions and stored procedures can be used to help with the truncating of, or storage of truncated, data in other tables to help prevent data loss. The stored procedures illustrate that procedures can be used to transform data from the service (façade matching) to the final form needed in the database.

Both database engines also provide 'instead of' triggers which could be used instead of mapping the stored procedures above to the ORM model. These triggers allow you to do an insert into the component tables of a view (Thanks Mark).

Performance

There is a performance penalty to be paid with adding extra layers between the users and the data, however in this case it hasn't been too severe and the benefits far outweigh the negatives.

Entity Framework Models

Both the edmx model (from the ADO .Net Entity Data Model for the MSSQL database) and the edml model (from Devart's dotConnect for the Oracle database) are named "LicenseDataModel" to ensure that all the model files have the same name and are in effect interchangeable.

Part of the Oracle Object context's mapping schema (msl XML file) is shown below, this illustrates the use of the stored procedures to help enable the façade to function.

    <EntitySetMapping Name="UsesSet">
      <EntityTypeMapping TypeName="LicenseModel.Uses">
        <MappingFragment StoreEntitySet="USES">
          <ScalarProperty Name="Id" ColumnName="ID" />
          <ScalarProperty Name="LicenseId" ColumnName="LICENSEID" />
          <ScalarProperty Name="DateTimeOfUse" ColumnName="DATETIMEOFUSE" />
          <ScalarProperty Name="Licensed" ColumnName="LICENSED" />
        </MappingFragment>
      </EntityTypeMapping>
      <EntityTypeMapping TypeName="LicenseModel.Uses">
        <ModificationFunctionMapping>
          <InsertFunction FunctionName="LicenseModel.Store.ADD_TO_USE">
            <ScalarProperty Name="LicenseId" ParameterName="V_LICENSE_ID" />
            <ScalarProperty Name="Licensed" ParameterName="V_LICENSED" />
            <ScalarProperty Name="DateTimeOfUse" ParameterName="V_DATETIME_OF_USE" />
          </InsertFunction>
          <UpdateFunction FunctionName="LicenseModel.Store.UPDATE_USE">
            <ScalarProperty Name="Id" ParameterName="V_USE_ID" Version="Current" />
            <ScalarProperty Name="LicenseId" ParameterName="V_LICENSE_ID" Version="Current" />
            <ScalarProperty Name="DateTimeOfUse" ParameterName="V_DATETIME_OF_USE" Version="Current" />
            <ScalarProperty Name="Licensed" ParameterName="V_LICENSED" Version="Current" />
          </UpdateFunction>
          <DeleteFunction FunctionName="LicenseModel.Store.DELETE_USE">
            <ScalarProperty Name="Id" ParameterName="V_USE_ID" />
            <ScalarProperty Name="LicenseId" ParameterName="V_LICENSE_ID" />
            <ScalarProperty Name="Licensed" ParameterName="V_LICENSED" />
          </DeleteFunction>
        </ModificationFunctionMapping>
      </EntityTypeMapping>
    </EntitySetMapping>

This can be contrasted with the MSL file for the MSSQL model shown below. Notice the lack of the ModificationFunctionMapping tag.

    <EntitySetMapping Name="UsesSet">
      <EntityTypeMapping TypeName="IsTypeOf(MSSQLDataModel.Uses)">
        <MappingFragment StoreEntitySet="Uses">
          <ScalarProperty Name="Id" ColumnName="Id" />
          <ScalarProperty Name="DateTimeOfUse" ColumnName="DateTimeOfUse" />
          <ScalarProperty Name="Licensed" ColumnName="Licensed" />
        </MappingFragment>
      </EntityTypeMapping>
    </EntitySetMapping>

The above two code samples display the real advantage of using the ORM models to provide the final, abstracted façade to the systems. You could always do the same using the views and stored procedures without the ORM - but the ORM provides a very solid abstraction that neatly ties it all together in an effective manner.

Now I have a conceptual model, which interfaces with either the MSSQL database using the tables and columns directly, or indirectly via views and stored procedures in the Oracle DB.

WCF Web Service

The actual implementation of licenses, and recording the use of those licenses, is done in a simple WCF service. This service works with the common conceptual model. Please refer to the following class diagram.

References

If the façades presented by the ORM models are identical, the same classes can be defined in either of the ORM's assemblies and then only one of the assemblies needs to be Referenced in the WCF service. Once the model has been referenced the service can make use of the Entities to implement the required business logic. To keep it as simple as possible I named the assemblies after the database that they link to. Please see solution explorer below. 

However if the ORM model is embedded inside the assembly there won't be an opportunity to point the service at the other database without a recompile.  

Metadata Artefact Processing

To facilitate the easy changing of the target database, without recompiling the service, the data model projects should be built with the Metadata Artefact Processing flag set to split out the component files. These files can then be added to the WCF service to allow them to be used, and have the same name.

The files can be changed when the object context needs to be pointed to a different database by replacing them in the bin folder (or wherever they are located), with the files that make up the other ORM model. Once the correct files are in place and the config file has been changed to reflect the new database connection string (or however you chose to do it) the service will look to the new database without any qualms, provided the required setups have been done (Oracle client etc.).

A consequence of this is the loss of partial classes on the Entities and Object Context, from inside the main WCF service assembly. You could write them in each of the data model assemblies or similar, I however, have chosen to write code in the main WCF service assembly once and only once using extension methods (and extension properties C# 5+) or the Utility design (anti) pattern.

Extension Methods

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LicenseModel;
using UnifiedService.Extensions;

namespace UnifiedService.EntityExtensions
{
    public static class LicenseExtensions
    {
        public static bool IsFreePeriodUse(this License license, LicenseEntities context, DateTime proposedDateTime)
        {
            var freePeriod = context.FreePeriodSet.Where(fp => fp.Id == license.FreePeriodId).First();

            if (freePeriod != null)
            {
                var dateTimeOfLastLicensedUse = context.UsesSet.Where(u => (u.Licensed == "Y")
                                                                        && (u.LicenseId == license.Id))
                                                                        .Max(u => u.DateTimeOfUse).GetValueOrDefault();
                double periodLength = freePeriod.PeriodLength.HasValue ? freePeriod.PeriodLength.Value : 0;
                var freeWindow = dateTimeOfLastLicensedUse.AddSeconds(periodLength);

                if (proposedDateTime.IsBetween(dateTimeOfLastLicensedUse, freeWindow))
                {
                    var numberOfFreesAfter = context.UsesSet.Where(u => (u.Licensed == "N")
                                                                    && (u.LicenseId == license.Id)
                                                                    && (u.DateTimeOfUse > dateTimeOfLastLicensedUse)
                                                                    && (u.DateTimeOfUse <= freeWindow)).Count();
                    if (numberOfFreesAfter < license.NumberOfFrees)
                    {
                        return true;
                    }

                }
            }
            return false;
        }
    }
}

Utilities 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LicenseModel;

namespace UnifiedService.EntityUtilities
{
    public static class UsesUtility
    {
        public static Uses CreateAndPopulateNewFreeUse(DateTime dateTime, LicenseEntities context, License license)
        {
            Uses uses = new Uses();

            uses.DateTimeOfUse = dateTime;
            uses.Licensed = "N";
            uses.LicenseId = license.Id;
            context.AddToUsesSet(uses);
            context.SaveChanges();

            return uses;
        }

        public static Uses CreateAndPopulateNewLicensedUse(DateTime dateTime, LicenseEntities context, License license)
        {
            Uses uses = new Uses();

            uses.DateTimeOfUse = dateTime;
            uses.Licensed = "Y";
            uses.LicenseId = license.Id;
            context.AddToUsesSet(uses);
            context.SaveChanges();

            return uses;
        }
    }
}

Unified Service

Take a License

Very simple example of license consumption - if this Use could be a free period use then make it so, else determine if it is a valid licensed use.

        public AddAUseResult AddAUse(DateTime dateTime, int licenseId)
        {
            AddAUseResult result = null;
            try
            {
                var context = new LicenseEntities(ConnectionsUtility.ReturnMetadataAndConnectionString());

                var license = context.LicenseSet.Where(l => l.Id == licenseId).First();

                var numberOfLicensesUsed = context.UsesSet.Where(u => (u.Licensed == "Y") && (u.LicenseId == license.Id)).Count();

                if ((numberOfLicensesUsed > 0) && (license.NumberOfFrees > 0))
                {
                    var freePeriod = license.IsFreePeriodUse(context, dateTime);

                    if (freePeriod)
                    {
                        var uses = UsesUtility.CreateAndPopulateNewFreeUse(dateTime, context, license);

                        result = AddAUseResult.CreateSuccessfulResult(uses.Id);
                    }
                }

                if ((result == null) && (numberOfLicensesUsed < license.NumberOfLicenses))
                {
                    var uses = UsesUtility.CreateAndPopulateNewLicensedUse(dateTime, context, license);

                    result = AddAUseResult.CreateSuccessfulResult(uses.Id);
                }

                if (result == null)
                {
                    result = AddAUseResult.CreateFailedResult();
                }

                return result;
            }
            catch (Exception exception)
            {
                IntegratedFaultContracts fault = new IntegratedFaultContracts
                {
                    MethodName = "AddAUse",
                    Problem = "Exception",
                    Exception = exception.ToString()
                };
                throw new FaultException<integratedfaultcontracts>(fault, new FaultReason(fault.ToString()));
            }
        }

Update a Use

As we are given the Id of the Use directly it is a simple procedure to change the time as required.

        public bool UpdateAUse(DateTime dateTime, int useId)
        {
            try
            {
                var context = new LicenseEntities(ConnectionsUtility.ReturnMetadataAndConnectionString());

                var use = context.UsesSet.Where(u => u.Id == useId).FirstOrDefault();

                use.DateTimeOfUse = dateTime;

                var saves = context.SaveChanges();

                return (saves > 0);
            }
            catch (Exception exception)
            {
                IntegratedFaultContracts fault = new IntegratedFaultContracts
                {
                    MethodName = "UpdateAUse",
                    Problem = "Exception",
                    Exception = exception.ToString()
                };
                throw new FaultException%lt;integratedfaultcontracts>(fault, new FaultReason(fault.ToString()));
            }
        }

Delete a Use

Again, the use of the primary key (Id) makes the delete a simple procedure.

        public bool DeleteAUse(int useId)
        {
            try
            {
                var context = new LicenseEntities(ConnectionsUtility.ReturnMetadataAndConnectionString());

                var use = context.UsesSet.Where(u => u.Id == useId).FirstOrDefault();

                context.DeleteObject(use);

                var saves = context.SaveChanges();

                return (saves > 0);
            }
            catch (Exception exception)
            {
                IntegratedFaultContracts fault = new IntegratedFaultContracts
                {
                    MethodName = "DeleteAUse",
                    Problem = "Exception",
                    Exception = exception.ToString()
                };
                throw new FaultException<integratedfaultcontracts>(fault, new FaultReason(fault.ToString()));
            }
        }

Conclusion

By manipulating the store and mapping schemas of an object context, a system can be implemented that can be used to interface with multiple databases without any changes to the business logic that makes up that system. All you really need is two existing systems that need the same functionality and have reasonably similar database schemas (that can be brought closer together by using views other database objects).

As this is my first article on CodeProject, I would be grateful for any feedback.