Archive

Archive for the ‘Producers’ Category

Dissecting the ASP.NET Identity Producer – Part 3

June 24, 2014 Leave a comment

If you’ve read our two previous posts (Part 1 and Part 2), you should know how to create a CodeFluent Entities custom producer. Now you may ask yourself how to integrate it into Microsoft Visual Studio and how to debug it.

Visual Studio Integration

Fist, to declare the producer, we have to create or edit the xml file located in “%APPDATA%\CodeFluent.Modeler.Design”.

<codeFluent.Modeler> 
    <producerDescriptors> 
      <producerDescriptor name="AspNetIdentity" displayName="Asp.Net Identity" category="Security" typeName="SoftFluent.AspNetIdentity.AspNetIdentityProducer, SoftFluent.AspNetIdentity" /> 
    </producerDescriptors> 
</codeFluent.Modeler>

Then, open Visual Studio and try to add a new producer:

AspNet Identity Producer Configuration

The property grid displays properties exposed by the producer:

public class AspNetIdentityProducer : BaseProducer
{
    [DefaultValue(false)]
    [Category("Source Production")]
    [DisplayName("Must Implement IQueryableUserStore")]
    [Description("Determines if the IQueryableUserStore interface must be implemented. WARNING: this is not a real IQueryable data source. This can be used to load all users.")]
    [ModelLevel(ModelLevel.Normal)]
    public bool MustImplementQueryableUserStore
    {
        get
        {
            return XmlUtilities.GetAttribute(Element, "implementQueryableUserStore", false);
        }
        set
        {
            XmlUtilities.SetAttribute(Element, "implementQueryableUserStore", value.ToString().ToLowerInvariant());
        }
    }
}

As you can see, parameter values are stored in the XML file. Do not create automatic properties, it won’t work!

We show that we can create custom attributes, but it can be very useful to display them in the property grid:

Custom Producer Property Grid

The BaseProducer implements the IDescribable interface, so we have to override the BuildDescriptors method.

protected override void BuildDescriptors(IList<Descriptor> descriptors)
{
    if (descriptors == null)
        return;

    descriptors.Add(BuildDescriptor(
        name: "entityType",
        typeName: typeof(EntityType).AssemblyQualifiedName,
        defaultValue: "None",
        displayName: "Entity Type",
        description: "ASP.NET Identity Entity Type.",
        targets: NodeType.Entity));

    descriptors.Add(BuildDescriptor(
        name: "propertyType",
        typeName: typeof(PropertyType).AssemblyQualifiedName,
        defaultValue: "None",
        displayName: "Property Type",
        description: "ASP.NET Identity Property Type.",
        targets: NodeType.Property));

    descriptors.Add(BuildDescriptor(
        name: "methodType",
        typeName: typeof(MethodType).AssemblyQualifiedName,
        defaultValue: "None",
        displayName: "Method Type",
        description: "ASP.NET Identity Method Type.",
        targets: NodeType.Method));

    base.BuildDescriptors(descriptors);
}

Thanks to the target, descriptors are shown only when needed. This allow to not pollute the property grid with meaningless descriptors. Note that the same descriptor can have multiple targets. Combine them with OR (“|” in C#). For example :

 NodeType.Property | NodeType.Method.

Because creating identity entities is boring, we add a form to create them automatically:

AspNet Identity Form

The two issues are:

  • How to open this form?
  • How to edit the model?

To answer the first one, CodeFluent Entities uses another interface: IDesignProducer. It allows to add menu items at the producer level.

Create Identity Entities

Once again, the BaseProducer already implements this interface so we have to override two methods: EnumerateMenus and ExecuteMenu.

protected override void BuildMenus(IList<IDesignProducerMenu> menus)
{
    base.BuildMenus(menus);

    if (menus == null)
        return;

    menus.Add(new BaseDesignMenu("Create Identity Entities", true));
}

protected override bool ExecuteMenu(IServiceProvider serviceProvider, IDictionary<string, object> context, int index)
{
    Project project = context["Project"] as Project;
    if (project == null)
        return false;

    switch (index)
    {
        case 0:
            var form = new ConfigurationForm(project);
            form.ShowDialog();
            return true;
    }

    return base.ExecuteMenu(serviceProvider, context, index);
}

Now we open the form, we need to create entities. You can see that we have access to the Project object, so just use it. Here’s the code to create a new entity:

Entity entity = new Entity();
entity.Name = entityName;
entity.Namespace = @namespace;
entity.SetAttributeValue("", "entityType", Constants.NamespaceUri, entityType);
project.Entities.Add(entity);

When you edit the model, CodeFluent Entities automatically update surfaces.

How to debug your custom producer?

One way is to start Visual Studio as administrator, so you can use the post build event to copy the generated DLL to the CodeFluent Entities directory.

xcopy “$(TargetPath)” “C:\Program Files (x86)\SoftFluent\CodeFluent\Modeler” /Y

Then you can configure the debugger to start an external program:

Program: C:\Program Files (x86)\SoftFluent\CodeFluent\Modeler\CodeFluent.Build4.exe
Command line arguments:

Producer Debugger Configuration
Another solution is to add System.Diagnostics.Debugger.Launch and System.Diagnostics.Debugger.Break in your code. This can be useful in templates.

Now you can start the debug (F5) and set breakpoints into your producer.

To conclude, this producer is quite simple, but it shows:

  • How to extends the modeler,
  • How to edit the model at design time,
  • How to generate code with Templates and CodeDom.

This is a great start when you want to write a producer. If you need more information, feel free to ask your question on the forums.

The full source code is available on our GitHub repository.

Happy producing!

The R&D Team.

Dissecting the ASP.NET Identity Producer – Part 2

June 23, 2014 Leave a comment

In our last post, we talked about some generalities regarding writing a custom producer.
Today we’ll see how does the ASP.NET Identity Producer generate C# code. Let’s remind that the full source code is available on our GitHub repository.

First and before producing code, we have to find the Role and the User entities in the model.

Do you remember the NamespaceUri? It allows you to add custom attributes in the xml file which represents the model.

<cf:entity name="User" d2p1:entityType="User" xmlns:d2p1=
"http://www.softfluent.com/codefluent/producers.aspNetIdentityProducer/2014/1"> 

</cf:entity> 

We add the entityType attribute to the entity. Xml Namespace are useful to avoid naming conflict between producers.

To find the User entity we can do the following, create an enumeration named “EntityType”:

public enum EntityType 
{ 
    None, 
    User, 
    Role, 
    UserRole, 
    Claim, 
    Login 
} 

Then, read the attribute value in the following way:

foreach (var entity in project.Entities) 
{ 
    if (entity.GetAttributeValue("entityType", 
               Constants.NamespaceUri, EntityType.None) == EntityType.User) 
    { 
        return entity; 
    } 
} 

The Produce method is a blank method so you can do what you want. When the output file is simple (with no complex logic), the easiest way is to use a template. When the logic is more complex you may want to use other mechanism such as CodeDom.

In the ASP.NET Identity producer we decided to use two templates: one for UserRole and the other one for RoleStore. CodeFluent Runtime already provides everything you need to use template. We’ll use here the SimpleTemplateProducer abstract class to simplify templates mechanisms (loading, parsing, processing). This producer takes directly the template from the resources of the producer, so you’ll have only one DLL to release. Of course the template has access to the producer instance, and so to its properties and methods.

AspNet Identity Producer

Tips: Keep the maximum of the logic in the producer, your templates will be much simpler to read

Here’s an extract from the template:

public System.Threading.Tasks.Task CreateAsync([%=TemplateProducer.IdentityRole.Entity.ClrFullTypeName%] role)
{
	if(role == null)
		throw new System.ArgumentNullException("role");

    return System.Threading.Tasks.Task.FromResult(role.Save());
}

And the code to run the template

public class RoleStoreProducer : SimpleTemplateProducer
{
    public IdentityRole IdentityRole { get; set; }
    
    protected override string DefaultNamespace
    {
        get { return Producer.Project.DefaultNamespace + Producer.WebNamespaceSuffix + ".Security"; }
    }

    protected override string DefaultTypeName
    {
        get { return "RoleStore"; }
    }

    protected override Template CreateTemplate()
    {
        var template = base.CreateTemplate();

        template.AddReferenceDirective(typeof(CodeDomBaseProducer));
        template.AddReferenceDirective(typeof(UserStoreProducer));

        return template;
    }

    public override string TargetPath
    {
        get
        {
            string path = ConvertUtilities.Nullify(XmlUtilities.GetAttribute(Producer.Element, ConvertUtilities.Camel(this.TargetName) + "TargetPath", (string)null), true);
            if (path == null)
                return BaseType.GetFilePath(Producer.TargetBaseNamespace, TypeName, Namespace, Producer.FullTargetDirectory, null);

            return Producer.GetFullRelativeDirectoryPath(path);
        }
    }
}

Note: We omit some code to make this implementation simpler

The template file is found based on the DefaultTypeName, so we don’t need to specify anything else. Instantiating the RoleStoreProducer and calling its Produce method will load the template from resources, and run it.

To implement the IUser and IRole interfaces, we need to edit the code generated by the CodeDom Producer. To do so, the way to go is to write a CodeDomSubProducer. But you can also get the instance of the BOM producer and register to the CodeDomProduction event:

var producer = project.Producers.GetProducerInstance<CodeDomProducer>();
if (producer == null)
    return;
producer.CodeDomProduction += CodeDomProducer_CodeDomProduction;

We can now handle the event and add the interface implementation:

private void CodeDomProducer_CodeDomProduction(object sender, CodeDomProductionEventArgs e)
{
    if (e.EventType == CodeDomProductionEventType.EntityCommitting)
    {
        CodeCompileUnit unit = e.Argument as CodeCompileUnit;
        if (unit == null)
            return;

        foreach (CodeNamespace ns in unit.Namespaces)
        {
            foreach (CodeTypeDeclaration typeDeclaration in ns.Types)
            {
                BaseType type = UserData.GetBaseType(typeDeclaration);
                if (type.GetAttributeValue("entityType", NamespaceUri, EntityType.None) == EntityType.User)
                {
                    // Implements IUser<TKey> & IUser
                    if (_identityUser.MustImplementGenericInterface)
                    {
                        ImplementIUser(typeDeclaration, true);
                    }

                    ImplementIUser(typeDeclaration, false);
                }
            }
        }
    }
}

private void ImplementIUser(CodeTypeDeclaration typeDeclaration, bool generic)
{
    string keyTypeName = generic ? _identityUser.KeyTypeName : typeof(string).FullName;
    var iuserCodeTypeReference = new CodeTypeReference("Microsoft.AspNet.Identity.IUser");
    var iuserGenericCodeTypeReference = new CodeTypeReference("Microsoft.AspNet.Identity.IUser");
    iuserGenericCodeTypeReference.TypeArguments.Add(keyTypeName);
    if (generic)
    {
        iuserCodeTypeReference.TypeArguments.Add(keyTypeName);
    }

    typeDeclaration.BaseTypes.Add(iuserCodeTypeReference);

    CodeMemberProperty idProperty = new CodeMemberProperty();
    idProperty.PrivateImplementationType = iuserGenericCodeTypeReference;
    idProperty.Type = new CodeTypeReference(keyTypeName);
    idProperty.Name = "Id";
    idProperty.HasSet = false;
    idProperty.GetStatements.Add(new CodeMethodReturnStatement(new CodePropertyReferenceExpression(new CodeThisReferenceExpression(), generic ? _identityUser.KeyPropertyName : "EntityKey")));
    typeDeclaration.Members.Add(idProperty);
}

The CodeDom can be traversed to find class declarations. The CodeDom producer annotates CodeDom elements, so we can get model concept from the CodeDom element by using UserData class. In this case we know if a class corresponds to the User entity by using UserData.GetBaseEntity.

In the last part, we’ll explain how to integrate this producer in Visual Studio and how to debug it.

The R&D Team.

Dissecting the ASP.NET Identity Producer – Part 1

June 19, 2014 Leave a comment

A few weeks ago we release a new producer: ASP.NET Identity Producer. Let’s see how we wrote it.

The main idea of the article is not to explain every line of code, but to explain the main concepts of the producer.

As a reminder the full source code of the producer is available on our GitHub repository.

What is a producer?

The main goal of a producer is to translate the model into code of any kind: C#, VB.NET, SQL, plain text. But a producer can generate what it want, or maybe don’t generate anything. It can also interact with database or file system.  You can do what you want with a producer.

First you have to implement the interface IProducer.

public interface IProducer 
{ 
    event Producer.OnProductionEventHandler Production; 
    void Initialize(Project project, Producer producer); 
    void Produce(); 
    void Terminate(); 
}

The build process uses producer this way:

Build Process

In the Initialize method you should make required initializations. Often this method simply copy the Project parameter to a field, so it can be used in the Produce method. The project object contains all information about the model (Entities, Views, Tables, etc.).

In the Produce method, you have to generate the output. This can be a complex output as the BOM or a much simpler as a summary of the project or maybe no output.
The Terminate method allows to clean resources.

This is the theory. In practice producers often inherit from a base class which already implement the IProducer interface:

  • BaseProducer
  • UIProducer
  • CodeDomBaseProducer
  • TemplateProducer
  • any existing producer

The basic code of the ASP.NET Identity producer is:

public class AspNetIdentityProducer : BaseProducer
{
    protected override string NamespaceUri
    {
        get { return Constants.NamespaceUri; }
    }

    public Version TargetFrameworkVersion
    {
        get
        {
            return new Version(4, 5);
        }
    }

    public override void Initialize(Project project, Producer producer)
    {
        base.Initialize(project, producer);
    }

    public override void Produce()
    {
    }
}

public class Constants
{
    public const string NamespaceUri = "http://www.softfluent.com/codefluent/producers.aspNetIdentityProducer/2014/1";
}

We introduce two additional notions: TargetFrameworkVersion and NamespaceUri.

The first one is the Framework version needed by the generated code. The second one is the NamespaceUri used to store custom xml attributes. You should create a unique name.

How to deploy and use the producer?

A producer is composed of one DLL that contains a class which implements IProducer. To deploy the producer, you must copy the DLL to the CodeFluent Entities directory (by default: C:\Program Files (x86)\SoftFluent\CodeFluent\Modeler). To use it, you have to declare it in the model file:

  <cf:producer name="Asp.Net Identity" typeName="CodeFluent.Producers.AspNetIdentity.AspNetIdentityProducer, CodeFluent.Producers.AspNetIdentity">
    <cf:configuration targetDirectory="..\Samples" implementQueryableUserStore="true" implementQueryableRoleStore="true" cfx:targetProjectLayout="Update" />
  </cf:producer>

The next post will cover how does the producer generate the code. In the meantime you can download the full code on our GitHub repository.

Let us know what you think!

The R&D Team.

ASP.NET Identity Producer


CodeFluent Entities can generate out of the box Membership, Role & Profiles Providers. Recently Microsoft release a new Identity system: ASP.NET Identity. As it seems to be more and more use, we decided to write a new producer to support this new Identity system.

First of all, the source code of the producer is available on our GitHub repository.

The producer does two things:

  • It assists you in creating ASP.NET Identity entities (User, Role, Claim, Login)
  • It generates the UserStore and RoleStore and implements IUser and IRole

How to install

  • Download the code from GitHub and compile it. A compiled version is also available here
  • Copy the output “CodeFluent.Producers.AspNetIdentity.dll” to “C:\Program Files (x86)\SoftFluent\CodeFluent\Modeler”
  • (Optional) To integrate the producer to the graphical modeler copy or merge “custom.config” to “%APPDATA%\CodeFluent.Modeler.Design\custom.config”

How to use it

  1. Create a CodeFluent Entities project
  2. Add a Business Object Model (BOM) producer
  3. Add this new producer (Security -> Asp.Net Identity). Note: The target path has no effect. The target path will be “Target Path of the BOM Producer\Web\Security(User|Role)Store”
  4. Add New Producer

  5. Right click on the producer and click the “Create Identity Entities” menu item
  6. Create Identity Entities

  7. Select necessary entities (User, Role, Claim, Login) and the destination namespace
  8. ASP.NET Identity

  9. Customize entities. For example you can remove “Password” property if you don’t need it
  10. ASP.NET Identity Model

  11. Generate and use the code 🙂

Note: you must add the “Microsoft.AspNet.Identity.Core” Nuget package before compiling the generated code (but this is obvious).

CodeFluent Entities is very extensible. As we try to show in this article, one way of extending it is by using producers. Of course the main goal of producers is to generate code. But they can also interact with the model at design time thanks to the CodeFluent Entities API.

Please share your feedbacks about this producer and don’t hesitate to make some suggestion.

Happy coding!

The R&D Team.

Categories: Producers Tags: ,

Fetch performance of CodeFluent Entities compared to others

March 27, 2014 Leave a comment

CodeFluent Entities has a great way to extend or modify generated code. This can be done through Custom Producers, Sub-Producers or Aspects. In fact, it can happen that you will meet very special requirements during your project, the kind of requirements that will involve some customizations to all of your code. Of course you don’t want to do this job manually by modifying each property or method in your project. Instead you can change the generated code to fit your expectations by using aspect. For a first introduction to aspect development in CodeFluent Entities please visit our blog.

In our situation, after reading Frans Bouma’s blog on benchmark of several ORMs of the .Net platform (Entity Framework, NHibernate, LLBLGen Pro, Linq To Sql and more), we wanted to integrate CodeFluent Entities to the benchmarks he made.

Integrating CodeFluent Entities to the benchmark project

First we downloaded the project from its GitHub repository.

After opening the RawBencher solution we created a CodeFluent Entities Model project:

CodeFluent Entities Project

Also we created a class library project to hold the code generated by CodeFluent Entities:

Class Library Project

Then we added one Business Object Model Producer and one SQL Server Producer to this project:

Solution Explorer

Here is the configuration for each producer:

Business Object Model

Business Object Model

Microsoft SQL Server

Microsoft SQL Server

Then we imported the AdventureWorks database to our CodeFluent Entities model:

Import

Then select the Microsoft SQL Server importer:

SQL Server Importer

Finally, set the connection string as pictured below:

SQL Server importer configuration

Once the import from database was done we built the model project to generate C# code and database stored procedures. After this, for the final step we added a bencher class to call CodeFluent Entities generated code. We basically reproduced the same schema as the existing ones for other ORMs already set up in this benchmarking project.

We are now ready to start the benchmarking!

Running the benchmark and analyzing results

We ran the project in release mode and we got the following results:

Non-change tracking fetches, set fetches (10 runs), no caching:

  1. Handcoded materializer using DbDataReader: 214,63ms
  2. PetaPoco Fast v4.0.3: 285,50ms
  3. Dapper: 306,25ms
  4. Linq to SQL v4: 318,50ms
  5. PetaPoco v4.0.3: 355,00ms
  6. Entity Framework v6: 362,13ms
  7. CodeFluent Entities 551,00ms
  8. ServiceStack OrmList v4.0.9.0: 555,75ms
  9. LLBLGen Pro v4.1.0.0, typed view: 585,00ms
  10. Oak.DynamicDb using dynamic Dto class: 902,50ms

Non-change tracking individual fetches (100 elements, 10 runs), no caching:

  1. CodeFluent Entities: 0,18ms
  2. DataTable, using DbDataAdapter: 0,37ms
  3. Oak.DynamicDb using dynamic Dto class: 0,40ms
  4. LLBLGen Pro v4.1.0.0: 0,44ms
  5. Telerik DataAccess/OpenAccess Fluent v4.0.3: 0,50ms
  6. Telerik DataAccess/OpenAccess Domain v4.0.3: 0,50ms
  7. NHibernate v3.3.1.4000: 0,68ms
  8. Entity Framework v6: 1,85ms
  9. Linq to Sql v4: 2,89ms

We focused only on non-change Tracking mode because it is the one that matches CodeFluent Entities features.

We can see that CodeFluent Entities is ranked at the first place for single fetch operations. Also we can see that it is ranked 7th for the multiple fetch operations.

Of course each ORM offers different features and because of that some of them can have a more naïve approach than others which will check data type conversion for instance or check cache during the fetch. This will lead to a speed difference in execution time.

For example if you compare a SQL hand coded query against any ORM among the ones available in .NET, hand coded query will be for sure faster. In our case we can explain why CodeFluent Entities generated code is taking more time in the multiple set fetch operation. Basically the code generated is doing some additional operations that we can get rid of in this particular scenario:

For instance in this LoadAll method we do not need to check if an element is already contained in the inner list so we should remove the check:

LoadAll

Another example is the ReadReacord method, in this case we do not need to test if the reader is null or not, neither the options and since the type are secure and primitive types we do not need to use the CodeFluent Persistence GetReader methods a simple reader.GetIn32 or reader.GetDate can be used depending on the type:

ReadRecord

After making these changes we can run the benchmark again to see what changed!

Running the benchmark with the adapted code

Here are the new result after code adaptation:

Non-change tracking fetches, set fetches (10 runs), no caching:

  1. Handcoded materializer using DbDataReader: 214,63ms
  2. CodeFluent Entities 273,25ms
  3. PetaPoco Fast v4.0.3: 285,50ms
  4. Dapper: 306,25ms
  5. Linq to SQL v4: 318,50ms
  6. PetaPoco v4.0.3: 355,00ms
  7. Entity Framework v6: 362,13ms
  8. ServiceStack OrmList v4.0.9.0: 555,75ms
  9. LLBLGen Pro v4.1.0.0, typed view: 585,00ms
  10. Oak.DynamicDb using dynamic Dto class: 902,50ms

After these changes CodeFluent Entities is now ranked at the 2nd place just after the hand coded query!

I will now show you how to make these custom changes more generic to apply them to the entire project for instance.

Understanding CodeFluent Entities Aspects

In CodeFluent Entities, the code generation process is model-first and continuous: from your declarative model, a meta-model will be inferred which code generators will then translate into code.

AspectAspects introduce a new notion allowing you to plug into this process. Using aspects you’ll be able to work on this in-memory representation of the model, before anything is produced and dynamically add/remove/modify elements in your model: this is what we call dynamic modeling. In a nutshell, in CodeFluent Entities, dynamic modeling is materialized as aspects and it allows developers to inject extra-behaviors in models.

You can easily see what the inferred model contains by selecting the option “View Inferred Model” on your project:

View Inferred Model

Then you can get details about any method or property of your code, for instance in our case the LoadAll method of the SalesOrderHeader entity:

Inferred Model

This inferred model will be used by the Business Object Model Producer we configured before to generate our code.

When you build your project, the enabled producers are instantiated to work on your model. With CodeFluent Entities you can interact at moment of the code production. For example you can change on the fly the behavior of your CodeDom producer (aka Business Object Model Producer) by accessing its instance:

CodeDomProducer codeDomProducer = Project.Producers.GetProducerInstance<CodeDomProducer>();

codeDomProducer.CodeDomProduction += (sender, e) =>
{
    //write your code here ...
}

And then you can manipulate this codeDomProducer object to change the body of your methods or do any other change to the generated code. In our case this will be very helpful to change the body of the LoadAll and ReadRecord methods.

Making code adaptation using aspects

In fact the changes we made can be reproduced automatically among the code by using a custom aspect that will interact with the Business Object Model Producer on the fly.

You can download the FastReader.xml file that contains the aspect we developed to make our customizations generic.

To remind you here is what the LoadAll method looked like before using the aspect:

LoadAll Before

Here is the new version of the same function:

LoadAll After

Another example is the ReadRecord method; here is what it was like before using the aspect:

Read Record Before

After enabling the aspect the method is replaced by a new one with the name FastReadRecord:

Read Record After

CodeFluent Entities offers many ways to customize the code generation and aspects is only one way among the others. In fact code customization can also be done by using sub-producers or patch-producers. Each technic has its pros and cons and in our case aspects was the best way to reach our goal. If you want to read more about sub-producers or patch producers please visit our blog here and here.

I hope this article helped you to figure out the flexibility of CodeFluent Entities.

Feel free to download and use the FastReader.xml aspect if you need.
Moreover, the full source code is available on our GitHub Profile.

Happy Adapting!

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

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.