Archive

Archive for the ‘SQL Server’ Category

SQL Server In-Memory OLTP

July 10, 2014 Leave a comment

In-Memory OLTP comes with Microsoft SQL Server 2014 and can significantly improve OLTP database application performance. It is a memory-optimized database engine integrated into the standard SQL Server engine. This system provides memory-optimized tables which are fully transactional and are accessed using class Translact-SQL instructions.

In-Memory Tables comes with some limitations. We won’t enumerate all but only those which are related to CodeFluent Entities:

  1. Foreign keys aren’t supported
  2. RowVersion and Timestamp columns aren’t supported: http://msdn.microsoft.com/en-us/library/dn133179.aspx
  3. Default constraints aren’t supported
  4. Some Transact-SQL constructs aren’t supported: http://msdn.microsoft.com/en-us/library/dn246937.aspx

Let’s handle those four points!

Foreign Keys

There are two options:

  • Don’t create relation :(
  • Create relation without foreign key :)

The second solution requires the usage of an Aspect. Fortunately we already wrote it a few time ago: http://www.softfluent.com/forums/codefluent-entities/how-to-disable-creating-foreign-key-by-sql-producer-

Even if foreign keys do not exist anymore, CodeFluent Entities still generates LoadBy_Relation methods so you won’t see any difference in your code. :)

Foreign Keys

RowVersion

RowVersion is not supported by In Memory tables so let’s remove it. We have to set “Concurrency Mode” to “None”:

RowVersion

 

Default Constraints

Default constraints used by tracking columns (creation time & last write time) are not supported. Here we have two options:

  • Remove default constraints :(
  • Move them into the Save stored procedure :)

The first option is available in the Property Grid at project or entity level by removing the tracking time columns:

Properties

The second option can be done with an Aspect as you can see in the full example (see below). The edited INSERT statement looks like:

    INSERT INTO [Customer] (
        [Customer].[Customer_Id],
        [Customer].[Customer_Name],
        [Customer].[Customer_ContactSource_Id],
        [Customer].[_trackCreationUser],
        [Customer].[_trackLastWriteUser],
        [Customer].[_trackLastWriteTime])
    VALUES (
        @Customer_Id,
        @Customer_Name,
        @Customer_ContactSource_Id,
        @_trackLastWriteUser,
        @_trackLastWriteUser,
        (GETDATE())) -- Default Value

Tansact SQL

By default the SQL Server Producer surround the procedure code with a transaction. This transaction isn’t supported when using In Memory Table. The following exception is thrown when calling the stored procedure:

Unhandled Exception: System.Data.SqlClient.SqlException: Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions. Provide a supported isolation level for the memory optimized table using a table hint, such as WITH (SNAPSHOT).

To remove it, we have to configure the SQL Server to not produce it:

SQL Server

 

Migrate the table

After those small changes, we can migrate the table to an In Memory table:

Migration

Migration Result

 

We can now use the In Memory table from the application:

Customer customer = new Customer();
customer.Name = "John Doe";
customer.Save();

All-in-One method

All the previous steps are automated by an aspect. All you have to do is include the aspect and set “enabled” on tables:

SqlServer In Memory Aspect

The full code sample including the aspect is available on our GitHub repository.

The R&D Team

Using LocalDB with CodeFluent Entities

April 8, 2014 Leave a comment

With Microsoft SQL Server 2012, Microsoft has introduced a feature called LocalDB which is a new edition of SQL Express. LocalDB is created specifically for developers and it is much easier to install (no service) and manage than standard editions. Developers initiate a connection by using a special connection string. It supports AttachDbFileName property, which allows you to specify a database file location.

When connecting, the server is automatically created and started, enabling the application to use the database without complex configuration tasks. This edition uses the same sqlservr.exe as the regular SQL Express and other editions of SQL Server.

The installation of Visual Studio 2012 and 2013 includes LocalDB 2012 and you can download the SQL Express 2014 LocalDb edition directly from the MSDN.

The SqlLocalDB Utility help you to manage your LocalDb instances. The following command will list you all LocalDB versions installed on your computer:

SqlLocalDb-versions

And the existing LocalDB instances owned by the current user and all shared LocalDB instances:

SqlLocalDb-instances

To check on the status and other details about an instance, you can run:

SqlLocalDB-info

CodeFluent Entities Build 769 introduced the support of Microsoft SQL Server 2014 and gives you the opportunity to use SQL Server LocalDB (2012 and 2014) as your persistence server of your CodeFluent Entities application.

The SQL Server Producers allows you to generate your database layer on an SQL Server LocalDB instance:

SQL Server Producer LocalDb

Just build your model and connect to your LocalDb instance with SQL Server Management Studio or the Visual Studio Server Explorer. You can see that a new database has been created with the named you specified in the Connection String, and populated with the tables automatically inferred from your model as well as instances:

Server Explorer

Happy LocalDB-ing!

The R&D Team

Multi-database deployment with PowerShell and the Pivot Script Runner – Part 2

March 6, 2014 Leave a comment

In Part 1 of this article, we looked at using the PowerShell strengths to automate the process of updating several databases through the PivotRunner tool.

Now, we want to go further and create a PowerShell command, better known as a Cmdlet.

Build the Cmdlet

A Cmdlet can be built directly in a Powershell script, or through the .NET Framework. We need to inherit from System.Management.Automation.Cmdlet and define its naming attributes therefor.

By agreement, the name of a Cmdlet consists of a verb, followed by a dash and a name (e.g: Get-ChildItem andAdd-PSSnapIn):

using System.Management.Automation;

namespace CodeFluentEntitiesCmdlet
{
    [Cmdlet(VerbsData.Update, "CFEDatabase", SupportsShouldProcess = true, 
            ConfirmImpact = ConfirmImpact.High)]
    public class UpdateCFEDatabase : Cmdlet
    {
    }
}

Here, the Cmdlet’s name will be Update-CFEDatabase.

Use the following PowerShell command: Copy ([PSObject].Assembly.Location) C:\MyDllPath to find the System.Management.Automation library

SupportsShouldProcess and ConfirmImpact attributes allow the Cmdlet to use the PowerShell Requesting Confirmation feature.

The Cmdlet abstract class includes a fairly advanced command parameters engine to define and manage parameters:

[Parameter(Mandatory = true)]
public string ConnectionString { get; set; }

[Parameter(Mandatory = true)]
public string PivotFilePath { get; set; }

The Mandatory term is used to warn the command parameters engine of whether or not a parameter is required.

Cmdlet also exposes some methods which can be overriden. These pipeline methods allow the cmdlet to perform pre-processing operations, input processing operations, and post-processing operations.

Here, we’ll just override the ProcessRecord method:

protected override void ProcessRecord()
{
  // Process logic code
}

Then, we need to use the PivotRunner which is located in the CodeFluent.Runtime.Database assembly.

The tool takes the connection string and the pivot script producer output file as parameters:

using CodeFluent.Runtime;
using CodeFluent.Runtime.Database.Management.SqlServer;

private void UpdateDatabase()
{
    try
    {
        PivotRunner runner = new PivotRunner(PivotFilePath);

        runner.ConnectionString = ConnectionString;

        if (!runner.Database.Exists)
        {
            WriteObject("Error: The ConnectionString parameter does not lead to an existing database!");
            return;
        }
        runner.Run();
    }
    catch (Exception e)
    {
        WriteObject("An exception has been thrown during the update process: " + e.Message);
    }
}

Do not forget to reference CodeFluent.Runtime.dll and CodeFluent.Runtime.Database.dll!

Moreover, we can recover the PivotRunner output (internal logs) by providing an IServiceHost implementation:

public class CmdletLogger : IServiceHost
{
    private Cmdlet _cmdLet;

    public CmdletLogger(Cmdlet cmdlet)
    { 
        _cmdLet = cmdlet;
    }

    public void Log(object value)
    {
        _cmdLet.WriteObject(value);
    }
}

runner.Logger = new CmdletLogger(this);
runner.Run();

Powershell integration

The Cmdlet is now finished! :)

Now we’ll see how to call it from Powershell! Here, we have several options, but we shall see the PSSnapIn one.

The “Writing a Windows PowerShell Snap-in” article shows that a PSSnapIn is mostly a descriptive object which inherits from System.Configuration.Install.Installer and is used to register all the cmdlets and providers in an assembly.

So, let’s implement our Powershell snap-in:

using System.ComponentModel;
using System.Management.Automation;

namespace CodeFluentEntitiesCmdlet
{
    [RunInstaller(true)]
    public class CodeFluentEntitiesCmdletSnapin01 : PSSnapIn
    {
        public CodeFluentEntitiesCmdletSnapin01()
            : base() { }

        public override string Name
        {
            get { return ((object)this).GetType().Name; }
        }

        public override string Vendor
        {
            get { return "SoftFluent"; }
        }

        public override string VendorResource
        {
            get { return string.Format("{0},{1}", Name, Vendor); }
        }

        public override string Description
        {
            get { return "This is a PowerShell snap-in that includes the Update-CFEDatabase cmdlet."; }
        }

        public override string DescriptionResource
        {
            get { return string.Format("{0},{1}", Name, Description); }
        }
    }
}

Then, we build our solution which contains our Cmdlet and the PSSnapIn and finally register the built library thinks to the InstallUtil.exe (located in the installation folder of the .NET Framework):

Administrator rights are required.

Administrator rights are required.

By using the “Get-PSSnapIn –Registered” Powershell command, we can observe that our PSSnapIn is well registered. This component can now be used into your Powershell environment:

Get-PSSnapIn–Registered

The “Add-PSSnapIn” command enables us to use our Cmdlet into the current session of Powershell.

As result, we can update our previously built Powershell script:

param([string[]]$Hosts, [string]$PivotFilePath, [switch]$Confirm = $true)

Add-PSSnapin CodeFluentEntitiesCmdletSnapin01

if ($Hosts -eq $null -or [string]::IsNullOrWhiteSpace($PivotFilePath))
{
    Write-Error "Syntax: .\UpdateDatabase.ps1 -Hosts Host1[, Host2, ...] -PivotFilePath PivotFilePath"
    break
}

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

Write-Host "-========- Script started -========-"

$Hosts | foreach {
    $srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $_

    $online_databases = $srv.Databases | where { $_.Status -eq 1 -and $_.Name.StartsWith("PivotTest_") }
    
    if ($online_databases.Count -eq 0)
    {
        Write-Error "No database found"
        break
    }

    Write-Host "Database list:"
    $online_databases | foreach { Write-Host $_.Name }

    [string]$baseConnectionString = "$($srv.ConnectionContext.ConnectionString);database="
    $online_databases | foreach {
        Update-CFDatabase -ConnectionString "$($baseConnectionString)$($_.Name)" -PivotFilePath $PivotFilePath -Confirm:$Confirm
    }
}

Write-Host "-========-  Script ended  -========-"

We can now simply deploy all changes we’ve recently made on our databases thanks to the Cmdlet and PivotRunner components.

The source code is available for download.

Happy PowerShelling !

The R&D team

Multi-database deployment with PowerShell and the Pivot Script Runner – Part 1

February 13, 2014 2 comments

We are working on a solution designed with CodeFluent Entities, which stores and manages data within a SQL Server database created by the SQL Server Producer.
Sometime in the past, we deployed this solution on many servers and, now, we want to keep them all up to date.
Recently, we have made some important changes and we want to deploy them on our many databases.

How to set up and automate the process of updating a range of databases while preserving their content?

Pivot Script Producer

To answer this question, we have developed a new producer called “The Pivot Script Producer”. It provides the opportunity for generating one or more XML files which are a database snapshot of the current CodeFluent Entities project model.

Pivot Runner

These files, generated by the pivot script producer, are intended to be consumed by the PivotRunner tool of the CodeFluent.Runtime.Database library.
Using a connection string, it updates the targeted database from the files we have previously sent to him.

The New SQL Server Pivot Script producer article shows that we can directly use the PivotRunner API from the library. But, even easier, we can just call one of the provided programs: CodeFluent.Runtime.Database.Client.exe or CodeFluent.Runtime.Database.Client4.exe, located in the CodeFluent Entities installation folder.

At this stage, we can very easily and quickly update one database. But we still want to apply this process on several databases!

PowerShell Script

Let’s use the PowerShell strengths ! :)

Powershell is a scripting language developed by Microsoft and default running on any Windows system since Windows Seven. With a fully object-oriented logic and a very close relationship with the .NET Framework, it has become an essential and very simple and useful tool. And that’s why Powershell is so cool!

Thus, we could easily imagine a script that takes a list of servers as first parameter, and the generated files path as the second one to select and update the targeted databases (here, only the online databases whose name starts with “PivotTest_”).

param([string[]]$Hosts, [string]$PivotFilePath)

if ($Hosts -eq $null -or [string]::IsNullOrWhiteSpace($PivotFilePath))
{
    Write-Error "Syntax: .\UpdateDatabases.ps1 -Hosts Host1[, Host2, ...] -PivotFilePath PivotFilePath"
    break
}

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

Write-Host "-========- Script started -========-"

$Hosts | foreach {
    $srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $_

    $online_databases = $srv.Databases | where { $_.Status -eq 1 -and $_.Name.StartsWith("PivotTest_") }
    
    if ($online_databases.Count -eq 0)
    {
        Write-Error "No database found"
        break
    }

    Write-Host "Database list:"
    $online_databases | foreach { Write-Host $_.Name }

    [string]$baseConnectionString = "$($srv.ConnectionContext.ConnectionString);database="
    $online_databases | foreach {
        & “CodeFluent.Runtime.Database.Client.exe” “runpivot” $PivotFilePath "$($baseConnectionString)$($_.Name)"
}
}

Write-Host "-========-  Script ended  -========-"

The script above shows us how easily some .NET Framework features can be used. In particular the Microsoft.SqlServer.Management.Smo namespace that provides an intuitive way of SQL Server instances manipulation.

We could just call one of the CodeFluent Runtime Database programs described above, but the idea of using directly the PivotRunner through a custom PowerShell command is much more attractive!

Indeed, Powershell gives us that opportunity. These customs commands are called “Cmdlets” and can be built under C#.NET, as we will see later in the second part of this article :)

Happy deploying

The R&D team.

The new SQL Server Pivot Script producer

October 10, 2013 1 comment

A new producer is available since the latest builds!

Enter the “SQL Server Pivot Script” producer.

This producer purpose is to allow you to deploy CodeFluent Entities-generated SQL Server databases on any machines (production servers, etc.) much easier.

Before that, during development phases, the CodeFluent Entities SQL Server producer was already able to automatically upgrade live SQL Server databases using an integrated component called the Diff Engine. We all love this cool feature that allows us to develop and generate continuously without losing the data already existing in the target database (unlike most other environments…).

Now, this new producer provides the same kind of feature, but at deployment time.

It generates a bunch of files that can be embedded in your setup package or deployed somewhere on the target server. These files can then be provided as input to a tool named the PivotRunner. This tool will do everything needed to upgrade the database to the required state. It can create the database if it does not exist, add tables, columns, view, procedures, and keys where needed, etc. It will also add instances if possible.

Here is some diagram that recaps all this:

SQL Server Pivot Script Producer

SQL Server Pivot Script Producer


To use it at development/modeling time:

  • Add the SQL Server Pivot Script producer to your project and set the Target Directory to a directory fully reserved for the outputs this tool will create. Don’t use an existing directory, create a new one for this.
  • Once you have built the project, this directory will contain at least one .XML file, but there may be more (if you have instances and blob instances for example). If you set ‘Build Package’ to ‘true’ in the producer’s configuration, the output will always be one unique file with a .parc (pivot archive) extension.
  • Copy these files where you want, or add them to your setup projects.

Now, at deployment time you have two choices:

1) Use the provided tool (don’t develop anything).

Use the CodeFluent.Runtime.Database.Client.exe (CLR2) or CodeFluent.Runtime.Database.Client4.exe (CLR 4) binaries. Just copy them to your target machine. You will also need CodeFluent.Runtime.dll and CodeFluent.Runtime.Database.dll. The tool is a simple command line tool that takes the pivot directory or package file as input.

2) Use the PivotRunner API.

The tool in 1) also uses this API. It’s a class provided in CodeFluent.Runtime.Database.dll (you will also need to reference the CodeFluent.Runtime.dll). The PivotRunner class is located in the CodeFluent.Runtime.Database.Management.SqlServer namespace.
This is very easy:

            PivotRunner runner = new PivotRunner(pivotPath);
            runner.ConnectionString = "This is my SQL Server connection string";
            runner.Run();

If you need to log what happens, just gives it an instance of a logger, a class that implements IServiceHost (in CodeFluent.Runtime), for example:

        public class PivotRunnerLogger : IServiceHost
        {
            public void Log(object value)
            {
                Console.WriteLine(value);
            }
        }

What happens in the database during diff processing can also be logged, like this:

            PivotRunner runner = new PivotRunner(pivotPath);
            runner.Logger = new PivotRunnerLogger();
            runner.ConnectionString = "This is my SQL Server connection string";
            runner.Run();
            runner.Database.StatementRan += (sender, e) =>
                {
                    Console.WriteLine(e.Statement.Command);
                };

Note: This producer is still in testing phase, the forums are here if you need help!

Happy diffin’

The R&D team.

Writing a custom CodeFluent Entities aspect to encrypt/decrypt columns values at runtime

September 25, 2013 Leave a comment

Today, we will demonstrate how to automatically change the SQL code generated during the build process in order to encrypt and decrypt the values stored in the database columns. This is the answer to a very interesting question that was posted to stackoverflow recently: How to manage Encrypt* and Decrypt* TSQL functions on an entity property?

Let’s consider this model:

model

A card number is a sensible piece of information, so you should encrypt it before saving it in the database. Obviously, You should also be able to read it back and decrypt it.

Of course, with CodeFluent Entities, you can do it in the Business Object Model Layer generated BOM (C# or VB.NET) using OnAddSaveParameters and OnAfterReadRecord rules, but this post will demonstrate how it can be done directly in the database layer!

Microsoft SQL Server 2005 and higher provides two new useful functions: ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE. These functions allow you to encrypt or decrypt data with a pass phrase. For example:

ENCRYPTBYPASSPHRASE(‘my super secret key’, ‘1234-5678-9012-3456-7890’) -- will write 0x01000000FFB251B13ADE1344597535490BDD7ABB4A5094CF24C211A63FFDD465052795A9 in the database

So all we need to do is to call ENCRYPTBYPASSPHRASE during saving (INSERT or UPDATE statements), and DECRYPTBYPASSPHRASE during loading (SELECT statements). A code such as this one for instance:

INSERT INTO [Test] ([Test].[Test_CardNumber])
   VALUES (ENCRYPTBYPASSPHRASE(@PassPhrase, @Test_CardNumber))

SELECT [Test].[Test_Id], CONVERT(nvarchar, DECRYPTBYPASSPHRASE(@PassPhrase, Test_CardNumber)) AS [Test_CardNumber]
   FROM   [Test]
   WHERE  [Test].[Test_Id] = @Id

Moreover, you’ll have to change the column type from string to varbinary to match the ENCRYPTBYPASSPHRASE return type.

In the CodeFluent Entities context, you’ll have to add the PassPhrase parameter to the stored procedure parameters, and to the BOM generated code.

Some theory

Before anything is actually generated, CodeFluent Entities parses the model and transforms it into a complete memory representation which contains Entities, Properties, Methods, Tables, Columns, Procedures, etc. The inference engine that does this transformation is using a pipeline that’s divided into steps. CodeFluent Entities Aspects can be introduced at any step, and are able to modify the model currently in memory, therefore influencing the next steps.

Here are the main steps of the inference pipeline:

pipeline

The most important thing to note here is the fact that each step processing uses what has been created in memory during the previous steps. So for example, if you add a property to an entity early enough during inference, this property will be used to create a column automatically, all standard methods will use this property, procedures – based on methods – will use this column automatically, and so on.

At the final stage, generators (a.k.a. ‘producers’ in CodeFluent Entities terminology) will transform this in-memory model into real code, files, etc.

You can read more about the inference pipeline at http://www.softfluent.com/documentation/Aspects_Overview.html

Enough theory… Let’s do it!

To make it short, an aspect is simply a .NET class that implements the CodeFluent.Model.IProjectTemplate interface (located in CodeFluent.Model.dll).

public interface IProjectTemplate
{
    XmlDocument Run(IDictionary context);
}

You’ll find some information about this interface in previous posts http://blog.codefluententities.com/2012/07/27/codefluent-entities-writing-a-custom-aspect/

An aspect usually declares a specific XML namespace it will use for its specific XML attributes that will be store alongside CodeFluent Entities ones. These attributes should also be declared. It’s not mandatory, but it’s cool if you want to use them directly in the graphical editor. To each descriptor will correspond a property grid line in the Visual Studio standard property grid.

public class EncryptAspect : IProjectTemplate
{
    public static readonly XmlDocument Descriptor;
    public const string Namespace = "http://www.softfluent.com/aspects/samples/crypt"; // this is my custom XML namespace
    private const string PassPhraseToken = "PassPhrase";
    public Project Project { get; set; }

    static EncryptAspect()
    {
        Descriptor = new XmlDocument();
        Descriptor.LoadXml(
        @"<cf:project xmlns:cf='http://www.softfluent.com/codefluent/2005/1' defaultNamespace='MyAspect'>
            <cf:pattern name='Encrypt Aspect' namespaceUri='" + Namespace + @"' preferredPrefix='ca' step='Start'>
                <cf:message class='_doc'> CodeFluent Sample Encrypt Aspect Version 1.0.0.1 - 2013/09/20 This aspect modifies Save and Load* procedures in order to call Sql Server         ENCRYPTBYPASSPHRASE / DECRYPTBYPASSPHRASE functions.</cf:message>
                <cf:descriptor name='encrypt'
                    typeName='boolean'
                    category='Encrypt Aspect'
                    targets='Property'
                    defaultValue='false'
                    displayName='Encrypt the property'
                    description='Determines if the property must be encrypted when saving to the database.' />
            </cf:pattern>
        </cf:project>");
    }
}

When the aspect runs, it needs to be notified whenever a property is added to an entity, anywhere in the model, in order to check whether it should be encrypted. If it should be encrypted, the entity should be modified accordingly.

The aspect should also be able to modify stored procedures code, once they are are generated. The step after stored procedures inference is ‘Categories’, so we need to handle this inference pipeline step as well:

public XmlDocument Run(IDictionary context)
{
    if (context == null || !context.Contains("Project"))
    {
        // we are probably called for meta data inspection, so we send back the descriptor xml<br />
        return Descriptor;
    }

    // the dictionary contains at least these two entries
    Project = (Project)context["Project"];

    // hook on new base entities, and hook on new properties
    Project.Entities.ListChanged += (sender, e) =>
    {
        if (e.ListChangedType != ListChangedType.ItemAdded)
            return;

        var entity = Project.Entities[e.NewIndex];
        if (!entity.IsPersistent)
            return;

        if (!entity.IsProjectDerived)
        {
            entity.Properties.ListChanged += OnPropertiesListChanged;
        }
    };

    Project.StepChanging += (sender, e) =>
    {
        if (e.Step != ImportStep.Categories)
            return;

        foreach (var procedure in Project.Database.Procedures.Where(procedure => procedure.Parameters[PassPhraseToken] != null))
        {
            UpdateProcedure(procedure);
        }
    };

    // we have no specific Xml to send back, but aspect description
    return Descriptor;
}

We have designed our aspect so it considers a property should be encrypted if the XML attribute “encrypt” (in the aspect XML namespace) is set to ‘true’ and if the property is persistent (e.g. available in the persistence layer). CodeFluent Entities provides methods to read XML file attributes easily. In this example, if the attribute “encrypt” is not defined or if its value is not convertible to a boolean value, the function will return false.

private static bool MustEncrypt(Property property)
{
    return property != null && property.IsPersistent && property.GetAttributeValue("encrypt", Namespace, false);
}

Now the OnPropertiesListChanged method applies the necessary changes whenever a new property is added:

  1. Check whether it must be encrypted
    private void OnPropertiesListChanged(object sender, ListChangedEventArgs e)
    {
        if (e.ListChangedType != ListChangedType.ItemAdded)
            return;
    
        var property = ((PropertyCollection)sender)[e.NewIndex];
        if (!MustEncrypt(property))
            return;
        ...
    }
  2. Change its persistence type to Binary
    property.DbType = DbType.Binary;
    property.MaxLength = 8000;
    
  3. Add an ambient parameter “PassPhrase” to the entity. This parameter will be used for all methods without explicitly declaring it on each one. The ambient parameter will automatically be inferred as a standard parameter for stored procedures, but it will get its value from a static property or method in the BOM. In this example it will get its value from a static parameterless arbitrarily named “GetPassPhrase” method, described further down the document. Its ambient expression (the expression to use in the WHERE part of the stored procedures) must be also set. Since this parameter is not really used as a filter clause in this example, let’s simply set it to “(1=1)” which is equivalent to a “NOP” in a WHERE SQL clause (i.e: WHERE (([Test].[Test_Id] = @Id) AND (1 = 1)))
    var passPhraseParameter = new MethodParameter
        {
            Name = PassPhraseToken,
            ClrFullTypeName = "string",
            Nullable = Nullable.False,
            Options = MethodParameterOptions.Ambient |
                        MethodParameterOptions.Inherits |
                        MethodParameterOptions.UsedForLoad |
                        MethodParameterOptions.UsedForSearch |
                        MethodParameterOptions.UsedForCount |
                        MethodParameterOptions.UsedForRaw |
                        MethodParameterOptions.UsedForSave,
            ModelName = "[" + Project.DefaultNamespace + ".PassPhrase.GetPassPhrase()]", // Note the brackets here. It means that code should not be verified by CodeFluent Entities; otherwise an existing property of the current entity is expected.
            AmbientExpression = "(1=1)"
        };
    
    property.Entity.AmbientParameters.Add(passPhraseParameter);
    

Ok, we applied the required changes to the future BOM, and now we need to update stored procedures before they get generated.

CodeFluent Entities creates an in-memory Abstract Syntax Tree (AST) to represent stored procedures. This AST is independent from the target database type, and can be modified during inference as well.

To update the in-memory stored procedures AST, you can visit (using a visitor pattern) this tree and modify it when needed. We will use literal expressions (ProcedureExpressionStatement.CreateLiteral(“Sql code”)) to create our ENCRYPT/DECRYPT Sql function calls. In this case, the generated code won’t be of course platform independent anymore. This aspect should be adapted if we wanted to use it on an Oracle, MySQL or PostgreSql database.

private static void UpdateProcedure(Procedure procedure)
{
    procedure.Parameters[PassPhraseToken].DefaultValue = null; // This means the passphrase must be provided, and cannot be null
    if (procedure.ProcedureType == ProcedureType.SaveEntity)
    {
        procedure.Body.Visit(s =>
        {
            var statement = s as ProcedureSetStatement;
            if (statement == null || statement.LeftExpression == null || statement.RightExpression == null || !MustEncrypt(statement.LeftExpression.RefColumn))
                return;

            string parameterName = statement.RightExpression.Parameter.Name;
            statement.RightExpression.Literal = ProcedureExpressionStatement.CreateLiteral(string.Format("ENCRYPTBYPASSPHRASE(@{0}, @{1})", PassPhraseToken, parameterName));
            statement.RightExpression.Parameter = null;

            // Column is of type varbinary but parameter must be of type string
            var parameter = procedure.Parameters[parameterName];
            if (parameter != null)
            {
                parameter.DbType = DbType.String;
            }
        });
        return;
    }
  
    procedure.Body.Visit(s =>
    {
        var statement = s as ProcedureSetStatement;
        if (statement == null || statement.LeftExpression == null || !MustEncrypt(statement.LeftExpression.RefColumn))
            return;

        statement.As = new ProcedureExpressionStatement(statement, ProcedureExpressionStatement.CreateLiteral(statement.LeftExpression.RefColumn.Column.Name));
        statement.LeftExpression.Literal = ProcedureExpressionStatement.CreateLiteral(string.Format("CONVERT(nvarchar, DECRYPTBYPASSPHRASE(@{0}, {1}))", PassPhraseToken, statement.LeftExpression.RefColumn.Column.Name));
        statement.LeftExpression.RefColumn = null;
    });
}

That’s it, the aspect is finished! But we want to use it in Visual Studio now…

Integrate the aspect in the visual modeler

To integrate your aspect, add a reference to the class library project that contains the aspect (it can be in the same solution):

integrate_aspect_in_modeler1

integrate_aspect_in_modeler2

Use the reference context menu to add an aspect (compiled) from this reference.

integrate_aspect_in_modeler3

The following dialog box will display what aspects are available in the compiled project, and what are the descriptors for the selected aspect:

integrate_aspect_in_modeler4

To use the aspect, a developer has to select the concept targeted by a given descriptor (Entity, Property, Method, etc.) and use the “Aspects and Producers Properties” tab in the Visual Studio standard property grid:

integrate_aspect_in_modeler5

Now you can build your model, add the logic to get the pass phrase, and enjoy :)

public static class PassPhrase
{
    public static string GetPassPhrase()
    {
        return "hello world";
    }
}

class Program
{
    static void Main(string[] args)
    {
        DemoEncrypt entity = new DemoEncrypt();
        entity.CardNumber = "0123-4567-8901-2346-5678";
        entity.Save();
        entity.Reload(CodeFluentReloadOptions.Default);
        Console.WriteLine(entity.Trace());
    }
}

The full source code is available here: DemoEncrypt.zip

Conclusion

With the power of CodeFluent Entities and approximately 180 lines of C# code, we have added the possibility to add database encryption to the columns of our choice and the tables of our choice. This aspect is 100% reusable across all our projects. Can you do this without CodeFluent Entities?

Cheers,
Gerald Barré

Defining type mappers when importing a database using the Importer Wizard


You can use the Importer Wizard to import an existing data base as a CodeFluent Entities model. You can access the Importer from the Modeler or the solution explorer on your CodeFluent Entities project (right click).
 

The Importer Wizard

The Importer Wizard


 
The importer makes a mapping between the data source types and the CodeFluent Entities types. But, you may want to define your own mapping configuration for a given type.
 
To do that, go to the “advanced properties” of the Importer Wizard and select “Type Mappers” under the “Input Mapping” group and add your type mapper.
 
Type Mappers Configuration

Type Mappers Configuration


 
Add an Importer Type Mapper

Add an Importer Type Mapper


 
Let’s say I am importing a SQL Server 2012 database and I have a field of type “time” (more information about SQL Server 2012 type mapping here), well I would like to have a CLR type “TimeSpan” mapped to it. When I take a look at the imported model I can see that the mapped type is “time”, but when I generate the Business Object Model (BOM) layer I have a property of type “System.DateTime (nullable)”. This is because the SQL Server type “time” did not exist before the SQL Server 2008 version.
 
SQL Server time type

SQL Server time type


 
time to DateTime mapping

time to DateTime mapping


 
So we can define a “type mapper” for the SQL Server type “time”.
 
elapsedtime mapper

elapsedtime mapper


 

We could also have chosen “System.TimeSpan”, “timespan” or “duration” as the “Type Name”.

 
And now we have a CLR type “System.TimeSpan (nullable)” in our BOM for a SQL Server 2012 type “time”.
 

time to TimeSpan mapping

time to TimeSpan mapping


 

Remember that you can save your Importer Wizard configuration.

 
You can find more resources about the Importer Wizard here.
 
 
Regards,
 
Pablo Fernandez Duran
 
 

Follow

Get every new post delivered to your Inbox.

Join 51 other followers