Archive

Posts Tagged ‘C#’

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.

Table-Valued Parameters: CFQL operators

July 10, 2015 Leave a comment

CodeFluent Query Language (CFQL) allows to quickly create simple methods. CFQL provides support for common operations with Table Value Parameters.

To show result of queries we will use the following data:

IN

 

Using the IN operator you can exclude values that aren’t included in a list. For instance:

load(guid[] ids) WHERE Id IN (@ids)

The generated SQL procedure:

CREATE PROCEDURE [dbo].[Customer_LoadByIdsIn]
(
 @ids [dbo].[cf_type_Customer_LoadByIdsIn_0] READONLY
)
AS
SET NOCOUNT ON
DECLARE @_c_ids int; SELECT @_c_ids= COUNT(*) FROM @ids
SELECT DISTINCT
    [Customer].[Customer_Id],
    [Customer].[Customer_Name],
    [Customer].[Customer_DateOfBirth]
FROM [Customer]
WHERE [Customer].[Customer_Id] IN (((SELECT * FROM @ids)))

RETURN
GO

 

 // John and Jane
CustomerCollection.LoadByIds(new int[] { 1, 2 });
 // Empty result set
CustomerCollection.LoadByIds(new int[0]);

Equals (=)

 

Using the equals (=) operator you can exclude values that aren’t included in a list, but unlike the IN operator, when the list is empty no filter is applied. For instance:

load(guid[] ids) WHERE Id = @ids

The generated SQL procedure:

CREATE PROCEDURE [dbo].[Customer_LoadByIdsEquals]
(
 @ids [dbo].[cf_type_Customer_LoadByIdsEquals_0] READONLY
)
AS
SET NOCOUNT ON
DECLARE @_c_ids int; SELECT @_c_ids= COUNT(*) FROM @ids
SELECT DISTINCT
    [Customer].[Customer_Id],
    [Customer].[Customer_Name],
    [Customer].[Customer_DateOfBirth]
FROM [Customer] LEFT OUTER JOIN @ids AS _t_ids ON ((@_c_ids = 0)
OR (([Customer].[Customer_Id] = _t_ids.Item)))
WHERE ((@_c_ids = 0) OR (([Customer].[Customer_Id] = _t_ids.Item)))

RETURN
GO

 

 // John and Jane
CustomerCollection.LoadByIds(new int[] { 1, 2 });
 // John, Jane and Jimmy
CustomerCollection.LoadByIds(new int[0]);

Comparison operators: Like, StartsWith, greater than, freetext, etc.

 

You can use comparison operators between a single value and a TVP:

load(string[] names) WHERE Name STARTSWITH @names

The generated SQL procedure:

CREATE PROCEDURE [dbo].[Customer_LoadByNamesStartsWith]
(
 @names [dbo].[cf_type_Customer_LoadByNamesStartsWith_0] READONLY
)
AS
SET NOCOUNT ON
DECLARE @_c_names int; SELECT @_c_names= COUNT(*) FROM @names
SELECT DISTINCT
    [Customer].[Customer_Id],
    [Customer].[Customer_Name],
    [Customer].[Customer_DateOfBirth]
FROM [Customer] LEFT OUTER JOIN @names AS _t_names ON ((@_c_names = 0)
OR (([Customer].[Customer_Name] LIKE (_t_names.Item + '%'))))
WHERE ((@_c_names = 0)
OR (([Customer].[Customer_Name] LIKE (_t_names.Item + '%'))))

RETURN
GO

 

 // Jane, John
CustomerCollection.LoadByNamesStartsWith(new string[] { "Ja", "Jo" });
 // Jane, John and Jimmy
CustomerCollection.LoadByNamesStartsWith(new string[0]);


Custom

 

You can use RAW methods with Table Value Parameter. Here’s an example with inline SQL:

LOAD(string[] names)
WHERE [EXISTS (SELECT * FROM @names AS n WHERE $Name$ = n.Item)]

The generated SQL procedure:

CREATE PROCEDURE [dbo].[Customer_LoadCustom]
(
 @names [dbo].[cf_type_Customer_LoadCustom_0] READONLY
)
AS
SET NOCOUNT ON
DECLARE @_c_names int; SELECT @_c_names= COUNT(*) FROM @names
SELECT DISTINCT
    [Customer].[Customer_Id],
    [Customer].[Customer_Name],
    [Customer].[Customer_DateOfBirth]
FROM [Customer]
WHERE EXISTS (
SELECT * FROM @names AS n WHERE [Customer].[Customer_Name] = n.Item
)

RETURN
GO

 

 // Jane, John
CustomerCollection.LoadCustom(new string[] { "Jane", "John" });
 // Empty result set
CustomerCollection.LoadCustom(new string[0]);

Happy Querying,

The R&D Team.

How to parse a command line?

January 16, 2015 1 comment

The CodeFluent Runtime offers to applications a clean and concise class for manipulating command line arguments and related tasks. Add the nugget package and refers the CommandLineUtilities class.

This class parses your command line arguments, find all parameters starting with – or / and all the values linked. I assumed that a value could be separated from a parameter with a : or a =.

During this article we are going to simulate this execution:

/Program.exe –ProductName:Word /SendEmail=true -amount:10.5

GetArgument

Gets a positioned argument from the command line and convert it to the type of the default value. If the argument is not found or if the value cannot be converted to the expected type, it returns the default value (internally it uses ConvertUtilities).

CommandLineUtilities.GetArgument(name:"ProductName", defaultValue:string.Empty); // -> “Word”
CommandLineUtilities.GetArgument(name:"ProductName", defaultValue:0); // -> 0
CommandLineUtilities.GetArgument(name:"SendEmail", defaultValue:false); // -> True
CommandLineUtilities.GetArgument("amount", double.MaxValue, new CultureInfo("en-US")); // -> 10.5D

HasArgument

Determines whether the argument is specified in the command line.

CommandLineUtilities.HasArgument(name:"ProductName"); // -> True
CommandLineUtilities.HasArgument(name:"debug"); // -> false

NamedArguments

List all arguments, the name will be the key.

// -> {{“ ProductName”,”Word”},{“SendEmail”,”true”}}
CommandLineUtilities.NamedArguments;

HelpRequested

Gets a value indicating whether help was requested.

// /Progam.exe /HELP
CommandLineUtilities.HelpRequested; // -> True


Happy CLI-ing !

The R&D Team.

Single Instance Application in .NET

September 10, 2014 Leave a comment

Running a single instance of an application is very easy with CodeFluent Runtime. Add the following nugget package: http://www.nuget.org/packages/CodeFluentRuntimeClient and use the SingleInstance class:

 
SingleInstance _singleInstance = new SingleInstance("Unique Name"); 

public MainForm() 
{ 

  if (!_singleInstance.WaitForMutext(IntPtr.Zero, IntPtr.Zero)) 
  { 
    Close(); 
  } 

  InitializeComponent();
}

If you want to make an action when the second instance is started, you can handle the windows message “”WM_” + uniqueName”:

 
protected override void WndProc(ref Message m) 
{ 
  if (m.Msg == _singleInstance.Message) 
  { 
    // your code
  } 
  base.WndProc(ref m); 
} 

SingleInstance class provides a helper to activate a Form, so when the second instance is started, it actually display the form of the first instance:

 
public partial class MainForm : Form 
{ 
  SingleInstance _singleInstance = new SingleInstance("My App"); 

  protected override void WndProc(ref Message m) 
  { 
  // Display the window of the first instance 
    _singleInstance.OnWndProc(this, m, true); 
    base.WndProc(ref m); 
  } 
} 

You can also force the instance to run, even if one is already running, by using the command line argument “/siForce” or “-siForce”.

 
$> myapp.exe /siForce 

Happy runtime exploration,

The R&D Team

CommonMark for C# using CodeFluent Runtime

September 5, 2014 Leave a comment

Markdown is a plain text language that can be converted to HTML. This language is well adopted: GitHub, StackOverflow, Reddit, some blogging platforms, and many others.

This language had no specification, so there were many different implementations. A few days ago, Standard Markdown, quickly renamed to Common Markdown, was born. This is the first specification of Markdown. At the same time, the C and JavaScript implementation was released:

At SoftFluent we though JavaScript is great but we are mostly using C# so there is no Common Markdown implementation.

We’re not providing a C# implementation as it would take time to develop and to maintain. Here, we want to show you how to call JavaScript code from C# using the CodeFluent Script Engine from the CodeFluent Runtime.

This is just a sample/preview and not aims to be used in production.

The full (but very short) code is available on GitHub: https://github.com/SoftFluent/SoftFluent.CommonMarkdown 

Here’s the main code:

private static readonly string _language = DetermineBestEngine();
private static string DetermineBestEngine()
{
    // use IE9+'s chakra engine?
    bool useChakra = ScriptEngine.GetVersion(ScriptEngine.ChakraClsid) != null;
    return useChakra ? ScriptEngine.ChakraClsid : ScriptEngine.JavaScriptLanguage;
}
string method = @"
    function markdownToHtml(text) {
        var reader = new stmd.DocParser();
        var writer = new stmd.HtmlRenderer();
        var parsed = reader.parse(text);
        return writer.render(parsed);
    };";

using (ScriptEngine engine = new ScriptEngine(_language))
using (ParsedScript parsed = engine.Parse(File.ReadAllText("stdm.js") + method))
    return (string)parsed.CallMethod("markdownToHtml", engine.Eval(EncodeJavaScriptString(text)));

And how to use it:

Markdown.ToHtml(@"# Hello there
This is a paragraph.
- one
- two
- three
- four
1. pirate
2. ninja
3. zombie")

This code gives the following result :

<h1>Hello there</h1>
<p>This is a paragraph.</p>
<ul>
  <li>one</li>
  <li>two</li>
  <li>three</li>
  <li>four</li>
</ul>
<ol>
  <li>pirate</li>
  <li>ninja</li>
  <li>zombie</li>
</ol>

CodeFluent Runtime Client is free and available from NuGet. This package contains lots of useful class such as the ScriptEngine.

Have fun with Markdown,

The R&D Team

[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