Archive

Archive for the ‘.NET’ Category

Table-Valued Parameters: Multi-Columns

July 24, 2015 Leave a comment

In previous blog posts we’ve seen how to use Table-Valued Parameters (TVP) that contains one column. Today we’ll see how to use TVP that contains two columns or more.

First we create our model:

CodeFluent Entities cannot create multi-columns TVP, so we simply use “object[]” to declare the parameter and we will create the table type by hand in an SQL file. For much convenience we’ll include the hand-written script in the generation process, so each time you generate the model, the custom script is executed J Persistence producers provide a way to execute a hand-made script before or after a standard generated script. Generated scripts are ran in a specific order, and you can plug-in your own hand-made ones by using the script name prefixed with the desired adjective (before or after). You’ll find a list of possibilities on the documentation page.

Let’s create a new SQL file named “before_<Project default namespace>_procedures.sql”

Then copy these lines into the before script file:

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = object_id(N'[dbo].[Customer_TVPInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Customer_TVPInsert]
GO

IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'CustomerTableType' AND ss.name = N'dbo')
DROP TYPE [dbo].[CustomerTableType]
GO

CREATE TYPE dbo.CustomerTableType AS TABLE
(
	FirstName	nvarchar(256),
	LastName	nvarchar(256)
)

We can now map the method parameter to this Table Type:

Note: If the parameter is not in the list, you can create it by using the Add button.

That’s it, we can use the generated code:

Customer.TVPInsert(new Tuple<string, string>[]
{
    Tuple.Create("John", "Doe"),
    Tuple.Create("Jane", "Doe")
});

Or maybe you prefer a typed version:

public class CustomerTvp
{
    public string FirstName { get; set; }
    public string LastName { get; set; }

    public CustomerTvp(string firstName, string lastName)
    {
        FirstName = firstName;
        LastName = lastName;
    }
}
Customer.TVPInsert(new CustomerTvp[]
{
    new CustomerTvp("John", "Doe"),
    new CustomerTvp("Jane", "Doe")
});

The TVPInsert method has a parameter of type “object[]”. You can change it to a typed one as we’ve seen in a previous blog post.

 

And voilà !

Happy Querying,

The R&D Team.

Categories: .NET Tags: , , , ,

Table-Valued Parameters: Use IEnumerable instead of an array

July 17, 2015 Leave a comment

We’ve seen that CodeFluent Entities supports Table-Valued Parameters. The generated .NET method has a parameter of type array:

public static CustomerCollection LoadByIds(System.Guid[] ids)

What you may not know is that you can IEnumerable, IEnumerable<T>, DataTable or IDataReader instead of the array!

Select your parameter and set Type name to the desired type name:

After generating the code, the signature of the method is

public static SampleAdvancedTvp.CustomerCollection LoadByIds(
    System.Collections.IEnumerable ids
)

Happy Querying,
THe R&D Team.

Exploring the CodeFluent Runtime: The Template Engine

December 26, 2013 Leave a comment

As you probably know, we released a few year ago the CodeFluent Runtime as a free Nuget Package named CodeFluentRuntimeClient. This is a set of utilities which is usable across all types of .NET applications (WPF, WinForms, ASP.NET, console, Windows service, etc.) and aims to ease the developer’s life.

Today, I’d like to introduce you, as part of the Exploring the CodeFluent Runtime series, an easy-to-use class to generate documents from a template source file.
You should ask yourself what is a template in term of software development. It’s simply a mixture of text blocks and control logic that can generate an output file. With our template engine, the control logic is written in JavaScript as fragments of program code. By default, the engine is based on the fast IE9+ “chakra” JavaScript engine developed by Microsoft for its Internet Explorer web browser.

Before we go any further it’s important to briefly describe the two following classes from the CodeFluent.Runtime.TemplateEngine namespace:

Template: Defines properties and characteristics of an ActiveX Scripting template and provides a set of methods to load and process a template file.

ParsedTemplate: In-memory and compiled representation of your template file (JavaScript).

The following diagram shows how the template engine internally works:

CodeFluentRuntimeClient Template

If you want to understand how to parse and execute JavaScript by C#, just have a look to this StackOverflow discussion. You’ll find how to interop with the JavaScript “IE9+ Chakra” JavaScript engine.

The easiest way to understand how to use the template engine is by way of an example. The first thing we have to do is create our template file.

Consider the following Rtf input file:

RTF Input file

Rtf Input file

You can download the template file here.

The “<% %>” tag represents the JavaScript code blocks. Let’s use the Template engine in order to process the template described above:

// The ComVisible indicates that the managed type is visible to COM.
[ComVisible(true)]
public class OrderLine
{
    public string ProductName { get; set; }
    public decimal UnitPrice { get; set; }
    public int Quantity { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        // initialize the argument dictionary.
        IDictionary<string, object> arguments = new Dictionary<string, object>();
        arguments.Add("orderLine1", new OrderLine() { ProductName = "Product A", Quantity = 5, UnitPrice = 12 });
        arguments.Add("orderLine2", new OrderLine() { ProductName = "Product B", Quantity = 10, UnitPrice = 30 });
        arguments.Add("orderLine3", new OrderLine() { ProductName = "Product C", Quantity = 4, UnitPrice = 40 });

        CodeFluent.Runtime.TemplateEngine.Template template = new CodeFluent.Runtime.TemplateEngine.Template();

        // Load the source template with the argument initialized above.
        template.Load("PurchaseOrder_Template.rtf", arguments.Keys.ToArray());

        using (StreamWriter writer = new StreamWriter(@"PurchaseOrder.rtf"))
        {
            // Run the template using the CodeFluent Runtime Template engine.
            template.Run(writer, arguments);
        }
    }
}

For running this code you have just to reference the CodeFluent Runtime Client Library and when executing it you’ll get the generated output file :

Rtf Output file

Rtf Output file

It’s good to know that the template engine is fully extendable. It would be helpful if you want to add your own keywords or business rules. For this purpose, you should inherit from the Template and/or ParsedTemplate objects.

On the same topic and through the CodeFluent Entities product, we are shipping a Template producer which is an engine that allow developers to generate text files from template containing C# code blocks.

Happy templating,

Antoine Diekmann

[Pet Shop Reloaded] The End

November 24, 2013 Leave a comment

Across this series of posts we have seen how is to design and build a business application using CodeFluent Entities and Entity Framework 5 and as you have seen, we can say that CodeFluent Entities can be located as a superset of Entity Framework (taking into account that CodeFluent Entities does not rely on Entity Framework).

Richer Modeler

As you could see, the CodeFluent Entities modeler is a very advanced one. The number of notions supported by CodeFluent Entities is larger than those of Entity Framework:

  • namespaces
  • model search engine
  • aspects and dynamic modeling
  • code snippets
  • methods
  • attributes
  • rules
  • rules editor
  • instances
  • forms
  • forms editor
  • configuration properties
  • model design customization
  • menu/action ribbon
  • inferred model
  • multi surface/file model
  • naming conventions
  • user defined types

Performance and readability

Entity Framework generate SQL code dynamically which can be ok when you have a small application and when you don’t need to debug/understand your SQL code. Indeed, dynamically generated code is not easy to read and can present performance issues when dealing with complex queries. All SQL code generated by CodeFluent Entities is generated in design time so you know in advance exactly the code that will be called.

linq-to-entities

linq-to-entities

Using CFQL

Using CFQL

The first image shows a query using Entity Framework and Linq-to-Entities, we can also see the dynamically SQL generated code translated from the C# code. The second image shows the same query using CodeFluent Entities and its language agnostic query system (CFQL), all this in design time.

Multipart model

When working with a large model or when several members of a team modify the same model it is really handy to split the model in different parts (files), this is not possible with Entity Framework without losing information and you may have experienced the consequences: merge conflicts, Visual Studio slows down…

No Mapping

When modeling your business domain with CodeFluent Entities you don’t need to make any specific mapping, you can easily add, remove and update elements in your model and CodeFluent Entities does the rest.

Continuous generation

CodeFluent Entities embraces the notion of Continuous Generation, it means that you can “tune” your model (update, go back) until it suits your needs and then generate it over and over again without losing any data, this is possible thanks to the CodeFluent Entities diff engine. This way you can more easily support functional changes.

Write less code

CodeFluent Entities will save you thousands of lines of code, code that the development team would have to write, code that would need to be tested and code that would always have a risk to have errors.  As all we know:

the less code we write…

  • the less that can go wrong
  • the sooner we’ll be done
  • the fewer bugs we write
  • the less we have to maintain
  • the better…

We haven’t seen everything

We have only covered a piece of the potential of CodeFluent, it provides a huge set of producers:

  • WPF
  • WindowsForms
  • Azure
  • Windows 8
  • ASP.NET MVC
  • WCF
  • REST/JSON web services
  • SharePoint
  • Silverlight proxy
  • advanced web services proxies
  • automatic traces
  • cache features
  • documentation files
  • Linq-to-Sql
  • Access and Excel lists
  • MS Build tasks
  • templates
  • web controls
  • and more…

That is explained by saying that CodeFluent Entities is not an ORM, it is a full model-driven software factory.

Code metrics

Let’s see a comparison of code metrics between the application built with CodeFluent Entities and Entity Framework.

We used the Visual Studio 2012 tool for code metrics analysis (Analyze -> Calculate Code Metrics for Solution).

We focalize on 3 indexes: Lines of Code, Maintainability Index and Cyclomatic Complexity. For each index the less is better.

Entity Framework – Code Metrics:

Entity Framework Code Metrics

Entity Framework Code Metrics

CodeFluent Entities – Code Metrics:

CodeFluent Entities Code Metrics

CodeFluent Entities Code Metrics

The code metrics analysis has not been applied to generated code, that’s why the “PetShopReloaded” project has a value of 0 Lines of Code for the CodeFluent Entities solution.

We needed to modify the t4 templates (Model.tt and Model.Context.tt) for Entity Framework in order to generate the System.CodeDom.Compiler.GeneratedCodeAttribute attribute so the generated code has not been taken in account by the code metrics tool. CodeFluent Entities generated that attribute by default.

As we can see we will need to maintain more code (and more complex) for the solution built with Entity Framework.

Finally

If you are convinced by all the advantages that CodeFluent Entities can provide to your software engineering process but you have already started with Entity Framework, don’t worry, CodeFluent Entities provides a “Entity Framework Importer” (http://www.softfluent.com/documentation/Importer_EntityFrameworkImporter.html) 🙂

Regards,

The SoftFluent 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 https://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é

[Pet Shop Reloaded] Using the generated code – Part 2

August 13, 2013 Leave a comment

In the previous article, we talked about using the code generated by CodeFluent Entities. On this one, we will first focus on the code generated by Entity Framework then we will summarize in a conclusion what is provided by the two solutions.
 

I – Use of Entity Framework generated code

In order to use the code generated by Entity Framework we will need to have some assemblies referenced in our project like “System.Data.Entity”, “EntityFramework”. Nevertheless, in our case we choose an ASP.NET MVC project and those are included out-of-the-box in the solution.

First of all, we have seen in the previous article that CodeFluent Entities generates out-of-the-box a BLOB handler for our images stored in the database. Obviously, Entity Framework doesn’t generate it for us, therefore we have to create our own BLOB handler.

To do so, we will add a new .ashx file at the root of our solution named “ImageHandler.ashx”. This file will contain the following code.

public class ImageHandler : IHttpHandler
{
  public void ProcessRequest(HttpContext context)
  {
    ModelContainer _db = new ModelContainer();
    string idStr, entityType;
    int id;

    if ((idStr = context.Request.QueryString.Get("ID")) != null && (entityType = context.Request.QueryString.Get("type")) != null && int.TryParse(idStr, out id))
    {
      if (entityType == "product")
      {
        Product product = _db.Product.Single(p => p.Id == id);
        context.Response.Clear();
        context.Response.ContentType = "image/jpg";
        context.Response.BinaryWrite(product.Image);
        context.Response.End();
      }
      else if (entityType == "item")
      {
        Item item = _db.Item.Single(i => i.Id == id);
        context.Response.Clear();
        context.Response.ContentType = "image/jpg";
        context.Response.BinaryWrite(item.Image);
        context.Response.End();
      }
    }
  }

  public bool IsReusable
  {
    get
    {
      return true;
    }
  }
}

As you can see, our class inherits from “IHttpHandler”, and based on the type it will either return the product image or the item image.

Once the handler created we will need to modify the “Web.config” file to reference our “ImageHandler” according to the following code.

<configuration>
  . . .
  <system.webServer>
    . . .
    <handlers>
      <add name="ImageHandler" verb="*" path="*.ashx" type="EF.PetShopReloaded.WebApp.ImageHandler,EF.PetShopReloaded.WebApp" />
      . . .
    </handlers>
  </system.webServer>
  . . .
</configuration>

In order for our blob handler to work we need to add a new route to be ignored in our “RouteConfig.cs” file as it is showed in the code below.

routes.IgnoreRoute("{handler}.ashx");

Our “Web.config” file is set up, our web application is able to reach our database, display images stored in, and use generated providers.

To be able to request data stored we also needed to instantiate our “ModelContainer” thanks to the following code.

public static ModelContainer _db = new ModelContainer();

As we did in the previous article with the code generated thanks to CodeFluent Entities, we will create a “CategoryController” containing an “Index” ActionResult method which will be used to retrieve products based on their category name.

public class CategoryController : Controller
{
  [HttpGet]
  public ActionResult Index(string name)
  {
    var products = EF.PetShopReloaded.WebApp.MvcApplication._db.Product.Where(c => c.Category.Name == name).ToList();

    if (products.Count != 0)
    {
      ViewBag.CategoryName = name;

      return View(products);
    }
    else if (products.Count == 0)
    {
      ViewBag.CategoryName = "No such category";

      return View(products);
    }
    else
      return RedirectToAction("Index", "Home");
  }
}

Let’s focus on the line

var products = EF.PetShopReloaded.WebApp.MvcApplication._db.Product.Where(c => c.Category.Name == name).ToList();

We used LINQ-to-Entities to retrieve all the products by name, in this example this is a very simple query and maybe we won’t have any problem when trying to debug it. However when we have more complicated queries it will be harder to debug them if there is a problem, this because Entity Framework generate SQL code on the fly, so we will need to use a profiler tool to see the actual query that is sent to the SQL server.

Obviously we can also manipulate data thanks to the code generated by Entity Framework. As we have seen in the article treating about the creation of the model of the application with CodeFluent Entities we are able to create instances to add data at generation but such a feature isn’t provided by Entity Framework and we had to create data manually from code. Therefore, we will use these data creation scripts to illustrate how to manipulate data with Entity Framework.

Regarding data creation we will take as example the “CreateCategories” class which can be find under the “Data” folder. As you can see in the code below, we are adding new categories and persisting them by calling the “SaveChanges” method.

public class CreateCategories
{
  public CreateCategories(ModelContainer _db)
  {
    _db.Category.Add(new Category() { Name = "Birds", Description = "Birds" });
    _db.Category.Add(new Category() { Name = "Backyard", Description = "Backyard" });
    _db.Category.Add(new Category() { Name = "Bugs", Description = "Bugs" });
    _db.Category.Add(new Category() { Name = "Endangered", Description = "Endangered" });
    _db.Category.Add(new Category() { Name = "Fish", Description = "Fish" });

    _db.SaveChanges();
  }
}

In order to update data, we just need to get the category we want to modify, update its properties and finally call the “SaveChanges” method to persist changes.

//Update sample
Category category = _db.Category.Single(c =&gt; c.Name == "Birds");
category.Name = "New name";

_db.SaveChanges();

For deletion, a “Delete” method is provided by CodeFluent Entities, the sample below shows how to delete an entity with Entity Framework.

//Delete sample
_db.Category.Remove(selectedCategory);

_db.SaveChanges();

Taking for example the view where we show our items, we just want to display the content of the product list we passed from our controller. Therefore, we are creating a view which have a model type of “IEnumerable<EF.PetShopReloaded.WebApp.Models.Product>”. This view also uses the layout of the web application. Since we have passed a list we need an enumerator, in this case we are using a “foreach”, to display its content. As we did in the previous article we will once again add some style to make it look nicer.

@model IEnumerable<EF.PetShopReloaded.WebApp.Models.Product>

@{
    Layout = "~/Views/Shared/_Layout.cshtml";
}

<h2 style="color: #444444;">@ViewBag.CategoryName</h2>

@foreach (var item in Model) {
    <a class="category" href="@Url.Action("Index", "Product", new { item.Category.Description, item.Name })">
        <p class="categoryTitle">@item.Name</p>
        <img id="@item.Id" src="~/ImageHandler.ashx?ID=@item.Id&type=product" style="width: 100%; height: 200px; margin:0; padding:0;"/>
        <p style="text-align:justify; width: 278px; padding: 6px; height:35px; margin: 0; font-size: 9pt;">@item.Description</p>
    </a>
}

As you can see, the “src” property of the “img” tag isn’t a simple variable. Since our images are stored in the database as BLOBs, as we said earlier we need an http handler. In this case, the url is simpler than the one showed in the previous article but the handler is also less complex and flexible.

To conclude, we can say that CodeFluent Entities provides ready-to-use code for you developers allowing you to focus on the application you are working on.

 

II – Conclusion

In this two part article we have seen how to use code generated by both CodeFluent Entities and Entity Framework. We have seen that the code generated by CodeFluent Entities is more efficient, providing us ready-to-use and platform independent code whilst letting us focus on using it inside our application.

In the next article, we will talk more about statistics (e.g. number of lines of code, performances) to conclude this blog post series about “Pet Shop Reloaded”.

 

Cheers,

The SoftFluent Team.

[Pet Shop Reloaded] Using the generated code – Part 1

August 5, 2013 Leave a comment

In this article, we will talk about using the code generated by CodeFluent Entities and Entity Framework. On the first part of this article we will focus on using the code generated by CodeFluent Entities then on an upcoming article we will see how to use the code generated by Entity Framework and the differences between the two approaches.

Use of CodeFluent Entities generated code

In order to use the code generated by CodeFluent Entities do not forget to add a reference to our class library project which contains the generated files as well as a reference to the “CodeFluent.Runtime” and the “CodeFluent.Runtime.Web”. Those DLL can be found in the installation directory of CodeFluent Entities, by default in “C:Program Files (x86)SoftFluentCodeFluentx64”.

First of all, we need to modify the “Web.config” file according to the screenshot below to add our connection string, add the generated blob handler in the “HttpHandler” section and register membership and role providers generated by CodeFluent Entities which are based on the AspNet membership and role providers.

9

In order for our blob handler to work we need to add a new route to be ignored in our “RouteConfig.cs” file as it is showed in the code below.

 routes.IgnoreRoute("{handler}.ashx");

Once our “Web.config” file is set up, our web application is able to reach our database, display images stored in, and use generated providers.

We are now able to create a controller and a view associated to this controller to show how it works.

Therefore, I will create a “CategoryController” containing an “Index” ActionResult method which will use our custom method to load products by their category name.

Once our “Web.config” file is set up, our web application is able to reach our database, display images stored in, and use generated providers.

We are now able to create a controller and a view associated to this controller to show how it works.

Therefore, I will create a “CategoryController” containing an “Index” ActionResult method which will use our custom method to load products by their category name.

[HttpGet]
public ActionResult Index(string name)
{

ProductCollection products = ProductCollection.LoadByCategoryName(name);

if (products != null && products.Count != 0){

ViewBag.CategoryName = name;

return View(products);

}

else if (products != null && products.Count == 0){

ViewBag.CategoryName = "No such category";

return View(products);

}

else

return RedirectToAction("Index", "Home");

}

As you can see in the code above, we are getting all the matching products from our database and we returning this ProductCollection to our view.

Before going further, we will take a look to what was generated inside this “ProductCollection” class focusing on the “LoadByCategoryName” method.

[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]

public static PetShopReloaded.ProductCollection LoadByCategoryName(string category)

{

PetShopReloaded.ProductCollection ret = PetShopReloaded.ProductCollection.PageLoadByCategoryName(int.MinValue, int.MaxValue, null, category);

return ret;

}

public static System.Data.IDataReader PageDataLoadByCategoryName(CodeFluent.Runtime.PageOptions pageOptions, string category)

{

if ((category == default(string)))

{

throw new System.ArgumentNullException("category");

}

CodeFluent.Runtime.CodeFluentPersistence persistence = CodeFluentContext.Get(PetShopReloaded.Constants.PetShopReloadedStoreName).Persistence;

persistence.CreateStoredProcedureCommand(null, "Product", "LoadByCategoryName");

persistence.AddParameter("@category", category);

if ((pageOptions != null))

{

System.Collections.IEnumerator enumerator = pageOptions.OrderByArguments.GetEnumerator();

bool b;

int index = 0;

for (b = enumerator.MoveNext(); b; b = enumerator.MoveNext())

{

CodeFluent.Runtime.OrderByArgument argument = ((CodeFluent.Runtime.OrderByArgument)(enumerator.Current));

persistence.AddParameter(string.Format("@_orderBy{0}", index), argument.Name);

persistence.AddParameter(string.Format("@_orderByDirection{0}", index), ((int)(argument.Direction)));

index = (index + 1);

}

}

System.Data.IDataReader reader = CodeFluentContext.Get(PetShopReloaded.Constants.PetShopReloadedStoreName).Persistence.ExecuteReader();

return reader;

}

CodeFluent Entities automatically generates “LoadBy” methods in collection classes for each entity relationship. For example, in the “ProductCollection” class we have a “LoadByCategory” method, as a Product has a relation with Category. We have also defined a “LoadByCategoryName” method to meet our specific needs. This method was generated using CFQL which is a platform independent language (https://blog.codefluententities.com/?s=cfql).

image

You can see that the code generated by CodeFluent Entities is clear and intelligible. Plus, if your business needs requires something really specific you can still extend what was generated by CodeFluent Entities since all the classes generated are partial classes. Therefore, you can focus on what matter for you and your application and not on the plumbing code.

CodeFluent Entities also generates methods to create, modify and delete data. Regarding creation or modification, a “Save” method is provided. This method will either save modifications made to an object or create this one if it doesn’t exist and it can be used as shown in the code sample below.

//Creation sample
Product product = new Product() { Name = "Demo" };
product.Save();

//Update sample
Product product = Product.Load(MyProductId);
product.Save();

For deletion, a “Delete” method is provided by CodeFluent Entities, a sample is shown below.

//Delete sample
Product product = Product.Load(MyProductId);
product.Delete();

CRUD methods are generated by default by CodeFluent Entities for all entities.

Taking for example the view where we show our items, we just want to display the content of the “ProductCollection” we passed from our controller. Therefore, we are creating a view which have a model type of “ProductCollection”. This view also uses the layout of the web application. Since we have passed a collection we need an enumerator, in this case we are using a “foreach”, to display its content. We are also adding some style to make it look nicer.

@model PetShopReloaded.ProductCollection

@{

Layout = "~/Views/Shared/_Layout.cshtml";

}

<h2 style="color: #444444;">@ViewBag.CategoryName</h2>

@foreach (var item in Model) {

<a href="@Url.Action("Index", "Product", new { item.Category.EntityDisplayName, item.Name })">

<p>@item.Name</p>

<img id="@item.Id" src='@(Request.ApplicationPath + CodeFluent.Runtime.Web.UI.BinaryLargeObjectHttpHandler.BuildUrl(null, CodeFluent.Runtime.Web.UI.WebControls.BinaryLargeObjectUrlType.Image, "PetShopReloaded.Product", "Product_Image", null, null, null, null, null, -1, -1, 0, new object[] { item.Id }))' style="width: 100%; height: 200px; margin:0; padding:0;"/>

<p style="text-align:justify; width: 278px; padding: 6px; height:35px; margin: 0; font-size: 9pt;">@item.Description</p>

</a>

}

As you can see, the “src” property of the “img” tag isn’t a simple variable. Since our images are stored in the database as BLOB’s as we said earlier we need an http handler. This BLOB handler was also generated by CodeFluent Entities.

public partial class HttpHandler : CodeFluent.Runtime.Web.UI.BinaryLargeObjectHttpHandler

{

private CodeFluent.Runtime.CodeFluentContext _context;

public override CodeFluent.Runtime.CodeFluentContext CodeFluentContext

{

get

{

if ((this._context == null))

{

if ((this.EntityClrFullTypeName == "PetShopReloaded.Item"))

{

this._context = CodeFluentContext.Get(PetShopReloaded.Constants.PetShopReloadedStoreName);

return this._context;

}

if ((this.EntityClrFullTypeName == "PetShopReloaded.Product"))

{

this._context = CodeFluentContext.Get(PetShopReloaded.Constants.PetShopReloadedStoreName);

return this._context;

}

this._context = CodeFluentContext.Get(PetShopReloaded.Constants.PetShopReloadedStoreName);

}

return this._context;

}

}

public override CodeFluent.Runtime.BinaryServices.BinaryLargeObject LoadBinaryLargeObject(System.Web.HttpContext context, string propertyName, object[] identifiersValues)

{

if ((this.EntityClrFullTypeName == "PetShopReloaded.Item"))

{

PetShopReloaded.Item Item = PetShopReloaded.Item.Load(((int)(ConvertUtilities.ChangeType(identifiersValues[0], typeof(int), -1))));

if ((Item == null))

{

return null;

}

if ((propertyName == "Item_Image"))

{

return Item.Image;

}

}

if ((this.EntityClrFullTypeName == "PetShopReloaded.Product"))

{

PetShopReloaded.Product Product = PetShopReloaded.Product.Load(((int)(ConvertUtilities.ChangeType(identifiersValues[0], typeof(int), -1))));

if ((Product == null))

{

return null;

}

if ((propertyName == "Product_Image"))

{

return Product.Image;

}

}

return null;

}

}

Remembering the model of our application you will notice that this generated BLOB handler will be able to handle image from “Product” and “Item” tables, which also are the only entities where we defined properties of type “image”. Also you can check-out the Blob http Handler article for more information.

To conclude, we can say that CodeFluent Entities provides ready-to-use code for you developers allowing you to focus on the application you are working on.

NB. For our application Pet Shop Reloaded we have used the generated BOM classes in a Web context (ASP .NET MVC), but know that you can also use it in other contexts like a WPF or a Windows Forms application.

Cheers,

The SoftFluent Team.