Article: ZeroMQ via C#: Multi-part messages, JSON and Synchronized PUB-SUB pattern

In this article we will talk about the two types of messages that ZeroMQ can send or receive and how to format these messages using JSON. We will also learn the polling mechanism and how to use it. Finally, we will explore the synchronized PUB-SUB pattern with an example.

See the full article at CodeProject

Posted in Articles, C# | Leave a comment

Article: ZeroMQ via C#: Introduction

An Introduction to ZeroMQ, a very lightweight message queuing open source software.Then exploring and testing in a very easy way its main communication patterns using C#.

See the full article at CodeProject

Posted in Articles, C# | Leave a comment

Find gaps in a time data table (C#, LINQ to SQL): Optimized

In the last post I showed a LINQ to SQL query for finding gaps in a time data table. This query can be optimized to be very fast by applying a very little modification on it. First, let us fill the table with more than 1000 rows and adding an index to the dt column. This can be done by executing the following script:

USE [MeasuresDb]

-- Create table and index
CREATE TABLE [dbo].[dtTable](
	[id] [smallint] IDENTITY(1,1) NOT NULL,
	[dt] [datetimeoffset](7) NOT NULL,
 CONSTRAINT [PK_dtTable] PRIMARY KEY CLUSTERED
	(
		[id] ASC
	)
) ON [PRIMARY]

GO

CREATE UNIQUE NONCLUSTERED INDEX [Idx_Dt] ON [dbo].[dtTable]
(
	[dt] ASC
)
Go

-- Fill table with data
declare @dtStart datetimeoffset , @dtEnd datetimeOffset
set @dtStart = '2012-09-01 00:00:00'
set @dtEnd = dateadd(minute, 1440, @dtStart)

insert into [dbo].[dtTable]
    select dateadd(minute, number, @dtStart)
    from
        (select distinct number from master.dbo.spt_values
         where name is null
        ) n
    where dateadd (minute, number, @dtStart) < @dtEnd

-- Create gaps (delete rows)
delete from [dbo].[dtTable]
where id = 2 or id = 3 or id = 7

And the query that we have used in the previous post is:

from a in DtTables
where DtTables.Count(date => date.Dt == a.Dt.AddMinutes(1)) == 0 &&
				   a.Dt < DtTables.Max(date => date.Dt)
select new
{
		startGap = a.Dt,
		endGap = (from b in DtTables
			   where b.Dt > a.Dt
			   select b).Min(c => c.Dt)
}

Before optimizing the actual query, let us analyze it first. Each LINQtoSQL query is translated to T-SQL language before sending it to SQl Server. To see the translated T-SQL from this query, we will use LINQPad tool (free tool).  it’s an ergonomic C#/VB/F# scratchpad that instantly executes any expression, statement block or program with rich output formatting.  So, copy the linq query to LINQPad and execute it:

The translated  T-SQL query is :

-- Region Parameters
DECLARE @p0 Float = 1
DECLARE @p1 Int = 0
-- EndRegion
SELECT [t0].[dt] AS [startGap], (
    SELECT MIN([t4].[dt])
    FROM [dtTable] AS [t4]
    WHERE [t4].[dt] > [t0].[dt]
    ) AS [endGap]
FROM [dtTable] AS [t0]
WHERE (((
    SELECT COUNT(*)
    FROM (
        SELECT
            (CASE
                WHEN [t1].[dt] = DATEADD(ms, (CONVERT(BigInt,@p0 * 60000)) % 86400000, DATEADD(day, (CONVERT(BigInt,@p0 * 60000)) / 86400000, [t0].[dt])) THEN 1
                WHEN NOT ([t1].[dt] = DATEADD(ms, (CONVERT(BigInt,@p0 * 60000)) % 86400000, DATEADD(day, (CONVERT(BigInt,@p0 * 60000)) / 86400000, [t0].[dt]))) THEN 0
                ELSE NULL
             END) AS [value]
        FROM [dtTable] AS [t1]
        ) AS [t2]
    WHERE [t2].[value] = 1
    )) = @p1) AND ([t0].[dt] < ((
    SELECT MAX([t3].[dt])
    FROM [dtTable] AS [t3]
    )))

To know if this query has a problem, we must see its  execution plan.  SQL Sentry Explorer is a good free tool to accomplish this job.  Copy the T-SQL query to the commandText tab of the SQL Sentry and click on Actual Plan, we get the following execution plan:

We notic that the filter operator which is marked with red has 80.1% CPU and I/O cost. The job of this  operator is to restrict a set of rows based on a predicate. And the predicate in our T-SQL query is from line 12 to 21 in the above list. The overall statistics of the query execution is:

Now, let us optimize the Linq query. What we will do is a very simple modification. Change the Count operator of the second statement to Where and add a Count operator to the end of the satement as shown here:

from a in DtTables
where DtTables.Where(date => date.Dt == a.Dt.AddMinutes(1)).Count() == 0 &&
					a.Dt < DtTables.Max(date => date.Dt)
select new
{
		startGap = a.Dt,
		endGap = (from b in DtTables
			   where b.Dt > a.Dt
			   select b).Min(c => c.Dt)
}

The translated T-SQL query is:

-- Region Parameters
DECLARE @p0 Float = 1
DECLARE @p1 Int = 0
-- EndRegion
SELECT [t0].[dt] AS [startGap], (
    SELECT MIN([t3].[dt])
    FROM [dtTable] AS [t3]
    WHERE [t3].[dt] > [t0].[dt]
    ) AS [endGap]
FROM [dtTable] AS [t0]
WHERE (((
    SELECT COUNT(*)
    FROM [dtTable] AS [t1]
    WHERE [t1].[dt] = DATEADD(ms, (CONVERT(BigInt,@p0 * 60000)) % 86400000, DATEADD(day, (CONVERT(BigInt,@p0 * 60000)) / 86400000, [t0].[dt]))
    )) = @p1) AND ([t0].[dt] < ((
    SELECT MAX([t2].[dt])
    FROM [dtTable] AS [t2]
    )))

We notice that the translation is not tha same as in the first query. The fruit of the optimization is noticed in the value of the query execution duration which is very small now as shown here:

This T-SQL query does not contain the predicate that casues a poor performance as in the first version.

So, we must be careful with linq query, a little modification can change the execution performance from poor to good or vice-versa.

Posted in C#, SQL Server | Leave a comment

Find gaps in a time data table (C#, LINQ to SQL)

In the previous post I showed a T-SQL solution for finding gaps in a time data table, and in this post I will show the same solution but this time it will be with C# and LINQ to SQL. Here is the code that produces the same result as in the previous post:

var context = new DataClassesDataContext();
var query = from a in context.dtTables
            where context.dtTables.Count(date => date.dt == a.dt.AddMinutes(1)) == 0 &&
                    a.dt < context.dtTables.Max(date => date.dt)
            select new
                {
                    startGap = a.dt,
                    endGap = (from b in context.dtTables
                            where b.dt > a.dt
                            select b).Min(c => c.dt)
                };

Console.WriteLine("StartGap                     EndGap");
Console.WriteLine("--------------------------   -------------------------");
foreach (var item in query)
    Console.WriteLine(item.startGap + "   " + item.endGap);

and here is the result after executing the above code:

Update: This post shows an optimized version of this Linq query.

Posted in C#, SQL Server | Leave a comment

Find gaps in a time data table (T-SQL)

Recently, one of the solutions that I had to implement is the detection of gaps in times in measure tables. What I wanted was to have a list of start and end time gaps. To illustrate the solution, first of all we can execute the following script to create a simple time table and fill it with datetimeOffset values (the interval between times is one minute):

declare @dtStart datetimeoffset , @dtEnd datetimeOffset
set @dtStart = '2012-09-01 00:00:00'
set @dtEnd = dateadd(minute, 10, @dtStart)

CREATE TABLE [dbo].[dtTable](
	[id] [smallint] IDENTITY(1,1) NOT NULL,
	[dt] [datetimeoffset](7) NOT NULL)

insert into [dbo].[dtTable]
	select dateadd(minute, number, @dtStart)
	from
		(select distinct number from master.dbo.spt_values
		 where name is null
		) n
	where dateadd (minute, number, @dtStart) < @dtEnd

the resulted table is:

Now let us create some gaps in this table by executing the following script:

delete from [dbo].[dtTable]
where id = 2 or id = 3 or id = 7

our table has gaps now:

Let us find these gaps by executing the following script:

SELECT a. dt AS StartGap
       ,(SELECT MIN(dt)
             FROM [dbo].[dtTable] c
             WHERE c. dt > a .dt) AS EndGap
FROM [dbo].[dtTable] a
WHERE NOT EXISTS (SELECT *
                  FROM [dbo].[dtTable] b
                  WHERE b. dt = DATEADD(MINUTE ,1, a.dt)
                  ) and
          a .dt < (SELECT MAX(dt)
                   FROM [dbo].[dtTable])

And here is the result:

The startGap column indicates the column before the gap and the EndGap column indicates the column after the gap.

Posted in SQL Server | Leave a comment

Article: Generating SQL Server CLR Assemblies report

In real situations we have so many databases in our SQL server and some of them may have one or more registered assemblies. Some of these assemblies may reference other assemblies. And we may have different versions of the same assembly in different databases. It will be nice if we have a script that reports all necessary information about all assemblies in all databases. So, the purpose of this article is to create this script.

See the full article at CodeProject

Posted in Articles, SQL Server | Leave a comment

Article: Create CLR Stored Procedure with Visual Studio Express edition

Visual studio Express edition does not provide a project type (template) for creating CLR Stored Procedures as the professional and higher editions. But the principle of creating a CLR Stored Procedure is very simple and we can accomplish it with the express edition.

See the full article at CodeProject

Posted in Articles, SQL Server | Leave a comment

Using Commands in WPF applications

In this post I will explain an easy way to use commands in WPF applications. I use the Prism 4.1 library  which contains some useful command classes and other stuff. So, let us add a reference to Microsoft.Practices.Prism.dll. This dll can be found in the folder you have chosen during  the installation of Prism:

Of course I use also MVVM pattern, so in main windows view model we add a property that create a command instance as follow:

using System.Windows.Input;
using Microsoft.Practices.Prism.Commands;

namespace wpfApp
{
    public class MainWindowsViewModel
    {
        private ICommand _saveCommand;
        public ICommand saveCommand
        {
            get
            {
                return (_saveCommand ??
                    (_saveCommand =
                        new DelegateCommand(Save, CanSave)));
            }
        }

        private void Save()
        {
            // Put save action
        }

        private bool CanSave()
        {
            return true;
        }
    }
}

In the above code we create an instance (if not already created) of DelegateCommand class. This class belongs to Prism classes, it allows us  to supply delegates for the Execute and CanExecute methods. This means that when the Execute or CanExecute methods are invoked on the command,  our delegates will be invoked. Knowing that Execute and CanExecute methods are defined in ICommand interface and our corresponding delegates are Save and CanSave respectively.

Supposing that our main window has a Save button. Now we can bind this button’s command to saveCommand property as follows:

<Window x:Class="wpfApp.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="MainWindow" Height="95" Width="248">
    <Grid>
        <TextBlock Text="Some elements here" Margin="10"
                   HorizontalAlignment="Center"/>
        <Button Content="Save" Height="23"
                HorizontalAlignment="center"
                VerticalAlignment="Bottom"
                Margin="5" Width="40"
                Command="{Binding saveCommand}"
                />
    </Grid>
</Window>

That’s all, if we click on the button , the Save method will be invocked.

Posted in WPF | Leave a comment

Using SQL Server Management Objects in your projects

When I want to test methods that build databases at runtime, I use the SQL Server Management Objects (SMO). SMO is a collection of .Net objects that allow us to do things that SQl Server Management studio application can do, for example:
  1. create/delete database
  2. list databases on the sever
  3. create/delete tables
  4. list tables in a database
  5. create/delete columns
  6. list columns in a table
  7. get sql server version
Of course, you can use SMO in your .Net projects or in PowerShell scripts.
To get this functionality available in your projects, you must:
  1. Download and install SQL Server System CLR Types wich is required by SQL Server Management Objects: you can download it from  http://www.microsoft.com/download/en/details.aspx?id=29065 (search in the same page “Microsoft® System CLR Types for Microsoft® SQL Server® 2012″ and choose either x86 or x64 package).
  2. Download and install SQL Server Management Objects from  http://www.microsoft.com/download/en/details.aspx?id=29065 (search in the same page “Microsoft® SQL Server® 2012 Shared Management Objects” and choose either x86 or x64 package).
The installations will put the dlls under C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies. 
SMO 2012 is compatible with SQL Server 2005 and later versions, so these packages work fin for sql server 2005, SQl Server 2008, SQL Server 2008 R2, and SQL Server 2012.
Note that Visual studio 2010 will not show these dlls in “Add Reference” dialog, I don’t know why!, so you can add reference to these dlls in your projects by selecting them directly from its location.

You need to reference these dlls:
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SqlEnum.dll

Here are some examples of how using SMO objects:

Connect to server:

// Create server instance representing the
// SQL sever instance
Server server = new Server(@"WINXP\SQLEXPRESS2008R2");
// Connect to server using windows authentication
server.ConnectionContext.LoginSecure = true;
server.ConnectionContext.Connect();
You can get the name of SQL Server from the properties of the server node in SQL Server Manaemenet Studio:


Create database

// Create a database named 'MyDatabase'
Database db = new Database(server, "MyDatabase");
db.Create();

The above code creates a database named ‘MyDatabase’:


Delete database

To delete a database from the server, you have to ways to do it,
either:
// Delete 'MyDatabase'
server.Databases["MyDatabase"].Drop();

or

// Kill 'MyDatabase'
server.KillDatabase("MyDatabase");
The last way forces database deletion by closing all open connection to it. This can be useful when you do some work with the database and directly after that you want to delete it (in unit test for example). If you use the Drop method in this situation it will fails saying that the database is in use.

Create Table with columns

The following code shows how to create a table with three columns. The first one is the identity column which is the primary key index:

// Get the database instance
Database myDatabase = server.Databases["MyDatabase"];
// Create a table instance named 'MyTable'
Table myTable = new Table(myDatabase,"MyTable");
// Create [id] column
Column id = new Column(myTable, "id", DataType.BigInt);
id.Identity = true;
// Add [id] column to the table
myTable.Columns.Add(id);
// Create another column [name]
Column name = new Column(myTable, "name", DataType.VarChar(125));
name.Nullable = true;
// Add [name] column to the table
myTable.Columns.Add(name);

// Create an index
Index pkIndex = new Index(myTable, "PK_MyTable");
// Set it as primary
pkIndex.IndexKeyType = IndexKeyType.DriPrimaryKey;
// Create an indexed column for the column [id]
// and add it to the primary indexd columns
pkIndex.IndexedColumns.Add(new IndexedColumn(pkIndex, "id"));
// Add the primary key index to the table's indexes
myTable.Indexes.Add(pkIndex);

// Create the table on the instance SQL server
myTable.Create();

Posted in SQL Server | Leave a comment

Ignoring some entity framework model fields at run time

Recently I ought to implement a module that must do CRUD operations on either sql server 2005 or 2008R2. The specifications precise that we must use a DateTimeOffset field for sqlserver 2008R2 and Datetime filed for Sql Server 2005 (since it has no definition for DateTimeOffset). So, the problem was how I can use one entity to do this works?. The solution that I found is to use entity framework code-first approach (if you are not familiar with this techn
ology, you can read this introduction). This technology gives use some facilities in doing custom configurations at runtime. Among these facilities is the ability to ignore one or more fields.
To download the entity framework you have two choices:
  1. using NuGet package management system. Read this page for installation instructions.
  2. right-click on this http://packages.nuget.org/v1/package/download/entityframework/4.3.1  and save it. Then rename the “EntityFramework.4.3.1.nupkg” file to “EntityFramework.4.3.1.nupkg.rar”. Now extract its content to a local directory. Add a reference to EntityFramework.dll in your project.

Let us define a simple entity:
public class MyEntity
{
     public long id { get; set; }
     public DateTimeOffset dateTimeOffset { get; set; }
     public DateTime dateTime  { get; set; }
     public string c1 { get; set; }
     public int c2 { get; set; }
}

In this entity I used the two datetime types. The datetTimeOffset property will be ignored when attacking sql server 2005 and the datetime  property will be ignored when 2008R2 is attacked.
This can be done in the context derived class as follow:

public class MyDbContext : DbContext
{
    public DbSet <MyEntity> MyEntities { get; set; }
    private bool _is2005;

    public MyDbContext( string connectionString, bool is2005)
     : base(connectionString)
    {
        _is2005 = is2005;
    }

    protected override void OnModelCreating( DbModelBuilder modelBuilder)
    {
       if (_is2005)
          modelBuilder.Entity<MyEntity >().Ignore(p => p.dateTimeOffset);
       else
          modelBuilder.Entity<MyEntity >().Ignore(p => p.dateTime);
            base.OnModelCreating(modelBuilder);
    }
}

 The overridden method “OnModelCreating”; [ is called only once when the first instance of a derived context is created.  The model for that context is then cached and is for all further instances of the context in the app domain. This caching can be disabled by setting the ModelCaching property on the given ModelBuidler, but this can seriously degrade performance.] as indicated in the msdn page. Keeping in mind that the default implementation of this method does nothing.

But, since my solution is aimed to use only one entity for the two databases and the switch from one database to another occurs at application start-up only, so I keep the default caching configuration.
Posted in SQL Server | Leave a comment