Thursday, 7 December 2017

Stock prediction using Azure Machine Learning

I like dabbling in the stock market, and wondered if artificial intelligence could help predict when I should buy/sell stock.

Using one of the FANG stocks (Facebook, Amazon, Netflix, Google) I'll choose Netflix for the purposes of this blog post.

Here is what Netflix has done in the last 3 months:
The top graph is Fast Stochastics (12,26)
As you can see, predicting the future is not easy.

So my first step was to not use AI at all, but to write a program to help me decide which values of %K and %D I should use for the Fast Stochastics graph, using the last 5 years of stock history for Netflix.

Historical data

This can be obtained from Yahoo finance.
Change the Time period to 5Y and press Apply. Then click the download data link to obtain your CSV file.



Best %K %D Fast Stochastics values for Netflix

The program I wrote takes into account that buying stock and selling stock incur a fee, and that the buy and sell values are not the same (bid/ask difference).
The fee for buying/selling stock is £10 (Motley Fool), and Netflix shares incurr a currency fee of £49. So each Netflix trade would cost me £59 GBP.

I will use a £15000 as an example for my investment. Simply buying stock 5 years ago and selling it 5 years later, the money I would have is £219,342.80

The program I wrote iterates through %K values of 3 to 100, and for each value of %K, iterate %D values of 2 to 100, and for each value of %D, iterate a buy/sell trigger by looking at the difference between %K and %D from 1 to 100. 941094 iterations in all. Each buy/sell incurs fees, and each time we sell, it's below the value we could buy at.
So the target to beat is £219,342.80, and the top results are:
k: 4, d: 4, kdDiff 55, profit: £166,469.40 loss: -£8,350.28, gain: £174,819.70, money £180,997.40
k: 52, d: 40, kdDiff 42, profit: £161,173.20 loss: -£3,937.38, gain: £165,110.60, money £175,583.20

Here is what a fast stochastic (4,4) looks like:
The difference between the %K (black line) and %D (Red line) must be 55 in order to trigger a buy/sell.

The 2nd best graph looks like:
The difference between the %K (black line) and %D (Red line) must be 42 in order to trigger a buy/sell.

Azure Machine Learning and AI


First off, we need the historical data, along with the correct buy/sell flag depending on what tomorrow brings. This is easy to work out as looking back in time is easy.
Using SQL Server, I imported the historical data into a table. I did this by using SSMS and right clicking on the database --> Tasks --> Import Data. Select the NFLX.csv file as the source, and the destination as "sql server native client". Press next a few times and finish. And voila, data is now in a table called NFLX. I left everything as string data, which is fine for my import.

Create a table with:
CREATE TABLE StockPrediction
(
    id INT IDENTITY(1,1) NOT NULL PRIMARY key,
    [name] VARCHAR(50) NOT NULL,
    [date] DATETIME NOT NULL,
    [open] DECIMAL(10,4) NOT NULL,
    [high] DECIMAL(10,4) NOT NULL,
    [low] DECIMAL(10,4) NOT NULL,
    [close] DECIMAL(10,4) NOT NULL,
    adj_close DECIMAL(10,4) NOT NULL,
    volume int NOT NULL,
    buy BIT null
)


Feel free to import any other data you wish into separate tables. Hence the need for the name discriminator.

The insert is as follows:
INSERT INTO StockPrediction (name,date,[open],high,low,[close],adj_close,volume)
    SELECT 'NFLX',[Date],[Open], High, Low, [Close], [Adj Close], Volume FROM nflx
    UNION ALL
    SELECT 'PPH',[Date],[Open], High, Low, [Close], [Adj Close], Volume FROM PPH


Next, we need to correctly set the buy column by looking at the next days close. That should tell us if would should buy or sell. This is done via:
UPDATE  StockPrediction
SET     buy = CASE WHEN (prev.[close] > StockPrediction.[close]) THEN 1
                   ELSE 0
              END
FROM    StockPrediction
        JOIN StockPrediction prev
            ON prev.id = StockPrediction.id + 1;
UPDATE  StockPrediction
SET     buy = 0
WHERE   buy IS NULL;


Ok, so now we historical data, and we have a buy/sell flag that I'd like the AI to try and learn to forecast.

Let's export this data into a CSV file.
Run this:
SELECT * FROM StockPrediction


Right click on the results and select "Save results as". I chose StockPrediction.csv on the desktop.
Edit the file and add in the header row: id,name,date,open,high,low,close,adj_close,volume,buy

Fire up your Azure portal and create yourself a Machine learning studio. Once that has been created, head over to Microsoft Azure Machine Learning Studio.

Import your dataset by clicking on "Datasets", and clicking +New icon.

I won't bore you with all the steps, and jump straight to it. Create an experiment, and drop in your StockPrediction.csv file.

Because I have several sets of stock data in the csv file, I only want netflix data. So in the "Apply SQL Transformation" I have
select * from t1
where name='NFLX'
order by id;


For Split Data I have 0.95, meaning I want it to predict the final 5% of data, which is 2 months worth of data to predict. This is usually set to 0.7 for most models, so the last 30% is predicted. Predicting 2 months in advance is plenty for me, so I'm going with 0.95.

For Train Models, select the column 'buy'. This is what we want to predict.

I am using 2 AI engines, to compete against each other, to see which is best for this particular problem. At the botton, click the Run button. Once complete, right click on the "Evaluate Model", and select Evaluation results --> Visualise.
ROC
The results are better than guessing as they are over the midway line. The best graph to see is one that goes straight up the left hand side to the top, then across the top to the top right.

Precision/Recall
As you can see, it's about 50% accurate.

Trying with the "Two-Class Neural Network", and a few tweaked values for the AI settings, I get the following results:

Summary

Given the 5 year history of Netflix stock data, on a day by day basis is not good enough for the AI to accurately predict with. However, it's not bad. More fine grained data (per minute), and many more columns are required to help train it even better. The extra columns could be how many times Netflix appears in a tweets on that day (and the gauge of the sentiment), did directors buy/sell shares, and if so, how many, etc, etc. The more, the better. It doesn't matter if YOU think it's not relevant as the AI training will work out what data works best. Always remove the human bias, as shown by the Google chess/go playing AI proved as it beat the human biased AI every time
It was a good experiment, and one I will keep playing with. It made me realise that more data, perhaps even extraneous data, is also required to help train the AI's to give better predictions.

Tuesday, 7 March 2017

.NET Renaissance

Long live .Net/core.
The future is bright, the future is .Net core.

However in getting there Microsoft have been left people a little confused.

Like Gáspár Nagy has stated in his blog post https://medium.com/@gasparnagy/net-core-in-the-focus-of-c-renaissance-227ad8f59108#.ybx122a54 Bad communication of goals and status - It would be interesting to make a poll among active .NET developers with regard to the status of the .NET Core, asking questions like:
  • “Has .NET Core been released already?”
  • “Has ASP.NET Core been released already?”
  • “Does ASP.NET Core work exclusively on .NET Core?”
  • “Is .NET Core the next version of .NET?”
The two day ASP.NET Core stuff I did with Damian Edwards at NDC is very different to what it is now. Large company feedback has pulled .net core all over the place. It's moving forward, but definitely taken a few sideways steps to get there. A bit like playing rugby then.

For instance, they've gotten rid of project.json (out with the new, in with the old) in favour of a more backwards compatible MSBuild ready, csproj format.

I personally think Microsoft should have stood their ground, been brave, and said no to backwards compatibility. If people want to migrate, give them a tool to convert it, and a report of things that are not compatible, or a list of warnings/errors in VS.

Like all good games of rugby, Microsoft will score a try eventually, and everyone will be like "yeah, C# and .Net Core is the business, golang/pyton/java is so yesterday".

Further reading by:

Thursday, 15 January 2015

Calling SQL Server stored procedures from Entity Framework

Using the methods below, you can obtain the stored procedure return value, along with your data, which I've not seen in other blogs.
A stored proc should return 0 for success, and any other value for a failure.

Here is an example table, filled with data:

CREATE TABLE DemoData
(
    id INT NOT NULL PRIMARY key,
    someValue DECIMAL(4,4) NOT NULL
)
GO
INSERT INTO DemoData(id, someValue)
VALUES (1, 1.23), (2, 2.34), (3, 3.45), (4, 4.56)

Here are our example stored procedures:
CREATE PROCEDURE GetDemoData(@maxId INT)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT id, someValue FROM DemoData WHERE id <= @maxId
END
GO

CREATE PROCEDURE AddTwoValues(@a INT, @b INT)
AS
BEGIN
    SET NOCOUNT ON;
    RETURN @a + @b -- Don't do this. Stored procs should return
                   -- 0 for success, and any other value for failure.
END
GO

CREATE PROCEDURE AddTwoValuesWithResult(@a INT, @b INT, @result INT OUTPUT, @result2 INT OUTPUT)
AS
BEGIN
    SET NOCOUNT ON;
    SET @result = @a + @b
    SET @result2 = @b - @a
END
GO

CREATE PROCEDURE ConvertToString(@someValue INT, @someString VARCHAR(20) OUTPUT)
AS
BEGIN
    SET NOCOUNT ON;
    SET @someString = '*' + CAST(@someValue AS VARCHAR(20)) + '*'
END
GO


Here is the C# code to call the above:
public class DemoData
{
    public Int32 Id { get; set; }
    public Decimal? SomeValue { get; set; }
}

public class MyDbContext : DbContext
{
    static MyDbContext()
    {
        Database.SetInitializer<MyDbContext>(null);
    }

    public MyDbContext()
        : base("Name=MyDbContext")
    {
    }

    public MyDbContext(string connectionString)
        : base(connectionString)
    {
    }

    public MyDbContext(string connectionString, 
        System.Data.Entity.Infrastructure.DbCompiledModel model)
        : base(connectionString, model)
    {
    }

    // Stored Procedures
    public int AddTwoValues(int a, int b)
    {
        var procResult = new SqlParameter
        {
            ParameterName = "@procResult", 
            SqlDbType = SqlDbType.Int, 
            Direction = ParameterDirection.Output
        };

        Database.ExecuteSqlCommand(
            "exec @procResult = AddTwoValues @a, @b", 
            new object[]
        {
            new SqlParameter
            {
                ParameterName = "@a", 
                Value = a, 
                SqlDbType = SqlDbType.Int, 
                Direction = ParameterDirection.Input
            }, 
            new SqlParameter
            {
                ParameterName = "@b", 
                Value = b, 
                SqlDbType = SqlDbType.Int, 
                Direction = ParameterDirection.Input
            }, 
            procResult
        });

        return (int)procResult.Value;
    }

    public int AddTwoValuesWithResult(int a, int b, out int result, out int result2)
    {
        var resultParam = new SqlParameter
        {
            ParameterName = "@result", 
            SqlDbType = SqlDbType.Int, 
            Direction = ParameterDirection.Output
        };
        var result2Param = new SqlParameter 
        { 
            ParameterName = "@result2", 
            SqlDbType = SqlDbType.Int, 
            Direction = ParameterDirection.Output };
        var procResult = new SqlParameter
        {
            ParameterName = "@procResult", 
            SqlDbType = SqlDbType.Int, 
            Direction = ParameterDirection.Output
        };

        Database.ExecuteSqlCommand(
            "exec @procResult = AddTwoValuesWithResult @a, @b, @result OUTPUT, @result2 OUTPUT", 
            new object[]
        {
            new SqlParameter
            {
                ParameterName = "@a", 
                Value = a, 
                SqlDbType = SqlDbType.Int, 
                Direction = ParameterDirection.Input
            }, 
            new SqlParameter
            {
                ParameterName = "@b", 
                Value = b, 
                SqlDbType = SqlDbType.Int, 
                Direction = ParameterDirection.Input
            }, 
            resultParam, 
            result2Param, 
            procResult
        });

        result = (int)resultParam.Value;
        result2 = (int)result2Param.Value;

        return (int)procResult.Value;
    }

    public int ConvertToString(int someValue, out string someString)
    {
        var someStringParam = new SqlParameter
        {
            ParameterName = "@someString", 
            SqlDbType = SqlDbType.VarChar, 
            Size = 20, 
            Direction = ParameterDirection.Output
        };
        var procResult = new SqlParameter 
        { 
            ParameterName = "@procResult", 
            SqlDbType = SqlDbType.Int, 
            Direction = ParameterDirection.Output 
        };

        Database.ExecuteSqlCommand(
            "exec @procResult = ConvertToString @someValue, @someString OUTPUT", 
            new object[]
        {
            new SqlParameter
            {
                ParameterName = "@someValue", 
                Value = someValue, 
                SqlDbType = SqlDbType.Int, 
                Direction = ParameterDirection.Input
            }, 
            someStringParam,
            procResult
        });

        someString = (string)someStringParam.Value;
            
        return (int)procResult.Value;
    }


    public List<DemoData> GetDemoData(int maxId, out int procResult)
    {
        var procResultParam = new SqlParameter
        {
            ParameterName = "@procResult", 
            SqlDbType = SqlDbType.Int, 
            Direction = ParameterDirection.Output
        };
            
        var sqlQuery = Database.SqlQuery<DemoData>(
            "exec @procResult = GetDemoData @maxId", 
            new object[]
        {
            new SqlParameter
            {
                ParameterName = "@maxId", 
                Value = maxId, 
                SqlDbType = SqlDbType.Int, 
                Direction = ParameterDirection.Input
            }, 
            procResultParam
        }).ToList();

        procResult = (int) procResultParam.Value;

        return sqlQuery;
    }
}

To call the above and return the results:
using (var db = new MyDbContext())
{
    Console.WriteLine("AddTwoValuesWithResult");
    int result, result2;
    int procResult = db.AddTwoValuesWithResult(3, 7, out result, out result2);
    Console.WriteLine("result = " + result);
    Console.WriteLine("result2 = " + result2);
    Console.WriteLine("procResult = " + procResult);
    Console.WriteLine("");

    Console.WriteLine("ConvertToString");
    string someString;
    procResult = db.ConvertToString(56, out someString);
    Console.WriteLine("someString = " + someString);
    Console.WriteLine("procResult = " + procResult);
        Console.WriteLine("");

    Console.WriteLine("AddTwoValues");
    procResult = db.AddTwoValues(5, 10);
    Console.WriteLine("procResult = " + procResult);
    Console.WriteLine("");

    Console.WriteLine("GetDemoData");
    var list = db.GetDemoData(3, out procResult);
    Console.WriteLine("procResult = " + procResult);
    foreach (var i in list)
    {
        Console.WriteLine(i.Id + ", " + i.SomeValue);
    }
}

Yields the following output:
AddTwoValuesWithResult
result = 10
result2 = 4
procResult = 0

ConvertToString
someString = *56*
procResult = 0

AddTwoValues
procResult = 15

GetDemoData
procResult = 0
1 1.230
2 2.340
3 3.450

This will be available in the EntityFramework Reverse POCO Generator, available at https://visualstudiogallery.msdn.microsoft.com/ee4fcff9-0c4c-4179-afd9-7a2fb90f5838

Wednesday, 10 December 2014

Pregenerated Views for Entity Framework 6

When Entity Framework starts, it first builds a set of internal views that describes the database in an agnostic way. All further processing (queries, updates etc.) EF does, is performed against these views. Generating views however can be costly and will impact start up time of the application.
The startup time for a 1000 table context can be in excess of minutes.


However, this can be worked around by generating views at design time by using EFInteractiveViews Nuget Package. It allows you to pre-generate and save the generated views to a file. Neat!

Install the Nuget package: https://www.nuget.org/packages/EFInteractiveViews
Source code and documentation: https://efinteractiveviews.codeplex.com/

Thursday, 7 August 2014

I'm in MSDN Magazine this month :-)

I am in the August 2014 edition of MSDN Magasine, Data Points section.
The article is written by Julie Lerman on the EntityFramework Reverse POCO Generator I created.
MSDN link: http://msdn.microsoft.com/en-us/magazine/dn759438.aspx

Thursday, 10 July 2014

SQL Datetime comparison gotcha

I discovered a problem when inserting data in our database. My insert statement was checking for the existence of data in the WHERE clause to prevent duplicate data being inserted. None was detected, and the INSERT happend. However, the unique constraint rejected the data as it already existed in the database.

The problem was the data to be inserted was DATETIMEOFFSET(2) and the database field being inserted into was DATETIME.

To show you want I'm talking about, run the following:

DECLARE @dt  DATETIME          = '2014-07-07 09:49:33.000';
DECLARE @dto DATETIMEOFFSET(2) = '2014-07-07 09:49:33.00 +07:00';

PRINT CASE WHEN @dt = @dto THEN 'Equals matches'
           ELSE 'Equals does not match'
      END

PRINT CASE WHEN @dt = CAST(@dto AS DATETIME) THEN 'Cast matches'
           ELSE 'Cast does not match'
      END


Results in the following:
Equals does not match
Cast matches

The comparison (=) operator does not perform the same way the implicit cast does if you insert the data. The cast/convert operator actually throws away the offset! Madness.

Tuesday, 1 April 2014

A new tutorial video

I have released a new and updated tutorial video for Entity Framework Reverse POCO generator.
It is available to stream or download at: www.reversepoco.com

Wednesday, 5 March 2014

Entityframework reverse POCO generator V2.4 released

Download: here

Whats new in v2.4.0
  1. Removed use of System.Data.Entity.DLL from the installation template as it is no longer required for EF 6.
  2. Moved spatial types from System.Data.Spatial to System.Data.Entity.Spatial for EF 6.
  3. Singular names and camel casing configuration were accidentally combined by using the UseCamelCase boolean. Thanks to Rune Gulbrandsen.
  4. Added new flag IncludeComments. This controls the generation of comments in the output.
  5. Fixed bug in constructor where a UNIQUEIDENTIFIER column had default value. Thanks to gonglei.

Wednesday, 13 November 2013

Entityframework reverse POCO generator V2.1 released

Whats new in v2.1.0:
  1. Fixed bug if default constraint was not in expected format. Thanks to Filipe Fujiy.
  2. Now detects name clashes with C# keywords.
  3. Added "System." to DateTime.Now(), etc to prevent clashes with table field names.
  4. "Configuration" class names are now configurable as "Mapping", "Map", etc.
  5. Added support for Spatial.DbGeometry and Spatial.DbGeography. Thanks to Simply Foolish and Jorge Bustos.
  6. Can now have custom collection type for Navigation Properties. "ObservableCollection" for example. Thanks to Simply Foolish.
Watch the video and Download at visualstudiogallery

Tuesday, 12 November 2013

NServiceBus and encrypting strings in messages, plus how to remove NServiceBus from your messages library

Summary

There are a few blog posts about using NServiceBus and encrypting strings using the WireEncryptedString type. However, you can also mark an ordinary string to be encrypted via configuration, which is more desirable as you can remove the dependancy from NServiceBus from your common Messages library.
There is a gotcha though, if you ever mix the two, then WireEncryptedString go back to being unencrypted in MSMQ. This blog post explains how to easily fix this, and also how to remove NServiceBus from your Messages library. Win Win.

Source code is avilable here. Compiling the project will automatically download packages via NuGet.

Initial project setup

Always have a separate Messages project to keep your messages in. In this example, the project is called Messages and contains the following class:
using NServiceBus;
 
namespace Messages
{
    public class MyMessage : IMessage
    {
        public WireEncryptedString Secret1 { getset; }
        public string PlainString { getset; }
    }
}

Note there it inherits from IMessage, and therefore has a dependance on NServiceBus. It also makes use of the WireEncryptedString which again has a dependance on NServiceBus.

Nothing special in the app.config other than to specify the message endpoint as SomeQueueName.
<configuration>
    <configSections>
        <section name="UnicastBusConfig" 
                 type="NServiceBus.Config.UnicastBusConfig, NServiceBus.Core" />
    </configSections>
    
    <UnicastBusConfig>
        <MessageEndpointMappings>
            <add Messages="Messages" Endpoint="SomeQueueName" />
        </MessageEndpointMappings>
    </UnicastBusConfig>
</configuration> 

The endpoint configuration specifies that NServiceBus use Rijndael as the encryption service:
public class EndpointConfig : 
    IConfigureThisEndpoint, 
    AsA_Client, 
    IWantCustomInitialization
{
    public void Init()
    {
        Configure.With()
                 .DefaultBuilder()
                 .RijndaelEncryptionService();
    }
}

We also need to setup an encryption key. This is done by the following class:
public class ConfigOverride : 
    IProvideConfiguration<RijndaelEncryptionServiceConfig>
{
    public RijndaelEncryptionServiceConfig GetConfiguration()
    {
        return new RijndaelEncryptionServiceConfig
            {
                // This key could be fetched from a REST/WS call, 
                // Database, or a common xml/settings file.
                Key = "blah.blah.blah.blah.blah.blah..."
            };
    }
}

The final piece is to create a class that will actually send a message:
public class MessageSender : IWantToRunWhenBusStartsAndStops
{
    public IBus Bus { getset; }

    public void Start()
    {
        var msg = new MyMessage
            {
                Secret1 = "You should not be able to read this in MSMQ",
                PlainString = "This is an ordinary string"
            };
        Bus.Send(msg);
        LogManager.GetLogger("MessageSender").Info("Sent message.");
    }

    public void Stop()
    {
    }
}

The resultant message looks like this:
<MyMessage>
    <Secret1>
        <EncryptedValue>
            <EncryptedBase64Value>QcJJfYP2SlOuDYNgFTQ8t8XI5D7zlwQzQXEFk8hcYT7gKUvqxN2Jg1UT7Q1CO929</EncryptedBase64Value>
            <Base64Iv>Q9Dc+k4V4LLPUc5+lEAtOQ==</Base64Iv>
        </EncryptedValue>
    </Secret1>
    <PlainString>This is an ordinary string</PlainString>
</MyMessage>

So far so good.

Now let's mark the PlainString to be encrypted using configuration instead of WireEncryptedString.
The first thing we do is to create a custom attribute we can use to mark strings to be encrypted:
namespace Messages
{
    public class MyEncryptionAttribute : Attribute
    {
    }
}

Then mark the strings to be encrypted:
public class MyMessage : IMessage
{
    public WireEncryptedString Secret1 { getset; }
    
    [MyEncryption]
    public string PlainString { getset; }
}

Next, we need to tell NServiceBus to treat all fields that use MyEncryption attributes to be encrypted:

namespace Sender
{
    public class ConventionsConfiguration : 
        IWantToRunBeforeConfiguration
    {
        public void Init()
        {
            Configure
                .Instance
                .DefiningEncryptedPropertiesAs(type => 
                    type.GetCustomAttributes(true)
                    .Any(t => t.GetType().Name == "MyEncryptionAttribute"));
        }
    }
}

So, run it and lets see what we get:
<MyMessage>
    <Secret1>
        <Value>You should not be able to read this in MSMQ</Value>
    </Secret1>
    <PlainString>k9FJRIfCMmyOW4ev8wytLIg56wEKsSbg1stmSo8Wu44=@XEx7Gkaks5nfhrQAuS2kZw==</PlainString>
</MyMessage>

Whoops!

The PlainString is now encrypted as we want, but in doing so the Secret1 string has become plain text. The Secret1 string is still a WireEncryptedString type and yet it still insists on being plain text, just because we've used the DefiningEncryptedPropertiesAs property on NServiceBus.


Let's fix it

We do this by changing ConventionsConfiguration class to also include the WireEncryptedString type as follows:
public class ConventionsConfiguration : 
    IWantToRunBeforeConfiguration
{
    public void Init()
    {
        Configure
            .Instance
            .DefiningEncryptedPropertiesAs(type =>
                type.PropertyType.Name == "WireEncryptedString" ||
                type.GetCustomAttributes(true)
                .Any(t => t.GetType().Name == "MyEncryptionAttribute"));
    }
}

Running this, the message now appears as:
<MyMessage>
 <Secret1>
  <EncryptedValue>
   <EncryptedBase64Value>4PHC9vcbOID08b8wbCl9tHdv8VH0aW8lFRFYGyHodwFkaV+YzF07aPPeRS3PVSCY</EncryptedBase64Value>
   <Base64Iv>/oFdQX9eTv7WWPJZIRwYsw==</Base64Iv>
  </EncryptedValue>
 </Secret1>
 <PlainString>P5vhzqd3NibLovHYMk8cVI/9k60jVDPERMHRPMMuCmg=@qbwJ2hrdeXegHbkisO4J8w==</PlainString>
</MyMessage>

Voila!

Both strings are now encrypted. You can see they are slightly different in the message.
To access Secret1, you do it via the .Value() method on Secret1. To access the plain unencrypted text on PlainString, you simply access the string as normal. Both strings are encrypted and unencrypted for you by NServiceBus.

How to remove NServiceBus from our Messages library

Now we have a way to tag strings to be encrypted, we can alter our message class to be:
public class MyMessage : IMessage
{
    [MyEncryption]
    public string Secret1 { getset; }
    
    [MyEncryption]
    public string PlainString { getset; }
}

Next we need to remove the IMessage interface, and remove all dependancies from NServiceBus. In a similar fashion to marking strings with attributes, we can mark message classes with an attribute.
public class MessageAttribute : Attribute
{
}

The we tag the message as follows:
[Message]
public class MyMessage
{
    [MyEncryption]
    public string Secret1 { getset; }
    
    [MyEncryption]
    public string PlainString { getset; }
}

NServiceBus now needs to be told which messages it can use. This is done in our ConventionsConfiguration class by adding a call to .DefinishMessageAs() as follows:
public class ConventionsConfiguration : 
    IWantToRunBeforeConfiguration
{
    public void Init()
    {
        Configure
            .Instance
            .DefiningEncryptedPropertiesAs(type =>
                type.PropertyType.Name == "WireEncryptedString" ||
                type.GetCustomAttributes(true)
                    .Any(t => t.GetType().Name == "MyEncryptionAttribute"))
            .DefiningMessagesAs(type =>
                type.GetCustomAttributes(true)
                    .Any(t => t.GetType().Name == "MessageAttribute"));
    }
}

Let's run this to see what the message now looks like:
<MyMessage>
    <Secret1>Rv7L0pDwDlY9KOZQqXuoRPGnD8MxXUJ7dPCOfXnBpP337Egz59aGMY/z6yhW03op@CAeXrzjYBwXYiN2R1iGKrw==</Secret1>
    <PlainString>obwa5exdHIt7AFoQKypzSZIRQR/Q2srJgP3Valej54o=@T09tOdiPjMxFntdI1FJouw==</PlainString>
</MyMessage>

That's it

  1. I've removed all references to NServiceBus from my messages library. So no more messy versioning problems for other projects.
  2. Strings to be encrypted are tagged with an attribute, which we tell NServiceBus about.
  3. Message classes are tagged with an attribute, which we tell NServiceBus about.

Friday, 8 November 2013

What's new roundup for EntityFramework Reverse POCO Code First Generator

Whats in the next version:
  1. Fixed bug if default constraint was not in expected format. Thanks to Filipe Fujiy.
Whats new in v2.0:
  1. Fixed issue when running 'Transform All T4 Templates' from Visual Studio Build Menu. Thanks to JRoselle.
  2. Changing mappings during runtime is not possible/expensive. A specific DbModelBuilder can be used for each needed database schema. Thanks to meixger.
  3. Added ability to detect and use .IsRowVersion().
  4. Added many-to-many mappings. The generated code now includes calls to .Map(). Therefore the generated code will be different to what you had previously in v1 if you have many-to-many table mappings in your database. Hence the revision change to v2.0.0 as I'm using semantic versioning.
Whats new in v1.12:
  1. VARBINARY(MAX) is now correctly assigned to byte[]. Thanks to Luke91577.
  2. Extending partial class support. Allow specification of file extension for partial classes (i.e. ".generated.cs"). Thanks to AB_dreeve.
  3. Fixes issues when targeting .NET 4. .NET 4.0 doesn't include the System.ComponentModel.DataAnnotations.Schema namespace, allow specification of TargetFrameworkVersion. Thanks to AB_dreeve.
Whats new in v1.11:
  1. Including views is now working. For a view to be included, at least one column must not be nullable. Thanks to Delmo Carruzzo.
  2. Added selective generation of components (Poco, Context, UnitOfWork, PocoConfiguration). You can now build entities in your Model project, and context, configuration, unit of work in your Data project.  Thanks to kscelfo.
  3. Added PrependSchemaName flag. You can now control if the schema name is prepended to the table name.  Thanks to kscelfo.
Whats new in v1.10:
  1. Added UseCamelCase flag to the tt file. You can now control if you want your table and column names CamelCase or left alone: i.e. FieldPersonOrder or field_person_order
Whats new in v1.9.2:
  1. Removed the Linq de-duplication code as it is now being done in SQL. The Linq version did not include the schema name when de-duplicating FK's.
Whats new in v1.9.1:
  1. Added DISTICT to the foreign key SQL. This prevents duplicates from appearing.
    They can appear with SQL such as:
    ALTER TABLE [Mars].[Table2] WITH CHECK ADD CONSTRAINT [FK_Table2_Table1]
        FOREIGN KEY([Table1Id]) REFERENCES [Mars].[Table1] ([Id])
    ALTER TABLE [Mars].[Table2] CHECK CONSTRAINT [FK_Table2_Table1] 
    
Whats new in v1.9.0:
  1. It now has the ability to read connection strings from other config files/projects.
    It starts by looking at the local project for the named connection string in the following files:
    • app.config
    • web.config
    • app.config.transform
    • web.config.transform
    • or edit the above list in the .tt and specify which file(s) to use
    If not found, it then looks for those files in all the projects within the solution.
Whats new in v1.8.0:
  1. Can now optionally generate separate files. See the new boolean flag GenerateSeparateFiles.
  2. Removed the hard coded 300 second timeout. The timeout can be specified in the connection string.
Whats new in v1.7.1:
  1. Add support for DateTime2
  2. Add support for UnitOfWork and the repository pattern. This means you can now unit test your repositories. See the source code for examples.
  3. Add IDisposable to db context.
Whats new in v1.7.0:
  1. Add .HasPrecision(precision, scale) for fields that have scale. Thanks to @choudeshell
Whats new in v1.6.0:
  1. Enhance ReadSchema performance with early table exclusion.
  2. Now supports columns with symbols and punctuation.
  3. Handles decimal default values.
Whats new in v1.5.1:
  1. A bugfix for WCF. See issue https://efreversepoco.codeplex.com/workitem/4
Whats new in v1.5.0:
Ability to add WCF(DataMember, DataContract attributes) support on Entity. Requested by spatemp

Friday, 15 March 2013

EntityFramework Reverse POCO Code First Generator v1.4.1 released

Reverse engineers an existing database and generates EntityFramework Code First POCO classes, Configuration mappings and DbContext.

v1.4.1 includes these new features:

  • If a field has a default contrainst of GetDate(), add DateTime.Now() in the ctor for the field.
  • Add support for the datetimeoffset type, including support for the default constraint sysdatetimeoffset().
  • Add Resharper naming comments.
  • Mapping classes are marked internal.
  • Make sure Foreign keys map only to Primary keys.
To see a video, head over to http://visualstudiogallery.msdn.microsoft.com/ee4fcff9-0c4c-4179-afd9-7a2fb90f5838

Monday, 10 December 2012

“Entity Framework Reverse POCO” OSS project made top link today (Dec 7)

I saw a spike on the Entity Framework open-source project I created, and had a look around to see what it could be…
 
Found it listed as the top link on http://www.alvinashcraft.com
As developers we take so much (understatement) from the open-source community ourselves, it’s nice to give back from time to time.

I've also noticed that it is now listed in the "most popular" category in the VisualStudio add new item.

Thursday, 22 November 2012

EntityFramework Reverse POCO Code First Generator

Reverse engineers an existing database and generates EntityFramework Code First POCO classes, Configuration mappings and DbContext.

To install and use this project:

  • Use Nuget and install EntityFramework.
  • Add a connect string to your app.config. Somethine like:
  • 
       
    
  • In Visual Studio, right click project and select "add - new item".
  • Select Online, and search for "reverse poco". Or you can download it from this page.
  • Select "EntityFramework Reverse POCO Code First Generator".
  • Give the file a name, such as Database.tt and click Add.
  • Edit the Database.tt file and specify the connection string as MyDbContext which matches your name in app.config.
  • Save the Database.tt file, which will now generate the Database.cs file.

To see a video, head over to http://visualstudiogallery.msdn.microsoft.com/ee4fcff9-0c4c-4179-afd9-7a2fb90f5838

Friday, 26 October 2012

Obtaining the database schema, tables, columns, and primary keys in a single SQL call

I am currently writing an EntityFramework reverse engineer code-first generator. It will generate POCO classes, DbContext and Code First mapping for an existing database.

There is one already in Entity Framework Power Tools Beta 2 However this one is going to do the job right, and include table filtering!

Watch this space...
SELECT  [Extent1].[SchemaName],
        [Extent1].[Name] AS TableName,
        [Extent1].[TABLE_TYPE] AS TableType,
        [UnionAll1].[Ordinal],
        [UnionAll1].[Name] AS ColumnName,
        [UnionAll1].[IsNullable],
        [UnionAll1].[TypeName],
        ISNULL([UnionAll1].[MaxLength],0) AS MaxLength,
        ISNULL([UnionAll1].[Precision], 0) AS Precision,
        ISNULL([UnionAll1].[Default], '') AS [Default],
        ISNULL([UnionAll1].[DateTimePrecision], '') AS [DateTimePrecision],
        ISNULL([UnionAll1].[Scale], 0) AS Scale,
        [UnionAll1].[IsIdentity],
        [UnionAll1].[IsStoreGenerated],
        CASE WHEN ([Project5].[C2] IS NULL) THEN CAST(0 AS BIT)
             ELSE [Project5].[C2]
        END AS PrimaryKey
FROM    (
         SELECT QUOTENAME(TABLE_SCHEMA) + QUOTENAME(TABLE_NAME) [Id],
                TABLE_SCHEMA [SchemaName],
                TABLE_NAME [Name],
                TABLE_TYPE
         FROM   INFORMATION_SCHEMA.TABLES
         WHERE  TABLE_TYPE IN ('BASE TABLE', 'VIEW')
        ) AS [Extent1]
        INNER JOIN (
                    SELECT  [Extent2].[Id] AS [Id],
                            [Extent2].[Name] AS [Name],
                            [Extent2].[Ordinal] AS [Ordinal],
                            [Extent2].[IsNullable] AS [IsNullable],
                            [Extent2].[TypeName] AS [TypeName],
                            [Extent2].[MaxLength] AS [MaxLength],
                            [Extent2].[Precision] AS [Precision],
                            [Extent2].[Default],
                            [Extent2].[DateTimePrecision] AS [DateTimePrecision],
                            [Extent2].[Scale] AS [Scale],
                            [Extent2].[IsIdentity] AS [IsIdentity],
                            [Extent2].[IsStoreGenerated] AS [IsStoreGenerated],
                            0 AS [C1],
                            [Extent2].[ParentId] AS [ParentId]
                    FROM    (
                             SELECT QUOTENAME(c.TABLE_SCHEMA) + QUOTENAME(c.TABLE_NAME) + QUOTENAME(c.COLUMN_NAME) [Id],
                                    QUOTENAME(c.TABLE_SCHEMA) + QUOTENAME(c.TABLE_NAME) [ParentId],
                                    c.COLUMN_NAME [Name],
                                    c.ORDINAL_POSITION [Ordinal],
                                    CAST(CASE c.IS_NULLABLE
                                           WHEN 'YES' THEN 1
                                           WHEN 'NO' THEN 0
                                           ELSE 0
                                         END AS BIT) [IsNullable],
                                    CASE WHEN c.DATA_TYPE IN ('varchar', 'nvarchar', 'varbinary')
                                              AND c.CHARACTER_MAXIMUM_LENGTH = -1 THEN c.DATA_TYPE + '(max)'
                                         ELSE c.DATA_TYPE
                                    END AS [TypeName],
                                    c.CHARACTER_MAXIMUM_LENGTH [MaxLength],
                                    CAST(c.NUMERIC_PRECISION AS INTEGER) [Precision],
                                    CAST(c.DATETIME_PRECISION AS INTEGER) [DateTimePrecision],
                                    CAST(c.NUMERIC_SCALE AS INTEGER) [Scale],
                                    c.COLLATION_CATALOG [CollationCatalog],
                                    c.COLLATION_SCHEMA [CollationSchema],
                                    c.COLLATION_NAME [CollationName],
                                    c.CHARACTER_SET_CATALOG [CharacterSetCatalog],
                                    c.CHARACTER_SET_SCHEMA [CharacterSetSchema],
                                    c.CHARACTER_SET_NAME [CharacterSetName],
                                    CAST(0 AS BIT) AS [IsMultiSet],
                                    CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') AS BIT) AS [IsIdentity],
                                    CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed')
                                    | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1
                                           ELSE 0
                                      END AS BIT) AS [IsStoreGenerated],
                                    c.COLUMN_DEFAULT AS [Default]
                             FROM   INFORMATION_SCHEMA.COLUMNS c
                                    INNER JOIN INFORMATION_SCHEMA.TABLES t
                                        ON c.TABLE_CATALOG = t.TABLE_CATALOG
                                           AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
                                           AND c.TABLE_NAME = t.TABLE_NAME
                                           AND t.TABLE_TYPE IN ('BASE TABLE', 'VIEW')
                            ) AS [Extent2]
                    UNION ALL
                    SELECT  [Extent3].[Id] AS [Id],
                            [Extent3].[Name] AS [Name],
                            [Extent3].[Ordinal] AS [Ordinal],
                            [Extent3].[IsNullable] AS [IsNullable],
                            [Extent3].[TypeName] AS [TypeName],
                            [Extent3].[MaxLength] AS [MaxLength],
                            [Extent3].[Precision] AS [Precision],
                            [Extent3].[Default],
                            [Extent3].[DateTimePrecision] AS [DateTimePrecision],
                            [Extent3].[Scale] AS [Scale],
                            [Extent3].[IsIdentity] AS [IsIdentity],
                            [Extent3].[IsStoreGenerated] AS [IsStoreGenerated],
                            6 AS [C1],
                            [Extent3].[ParentId] AS [ParentId]
                    FROM    (
                             SELECT QUOTENAME(c.TABLE_SCHEMA) + QUOTENAME(c.TABLE_NAME) + QUOTENAME(c.COLUMN_NAME) [Id],
                                    QUOTENAME(c.TABLE_SCHEMA) + QUOTENAME(c.TABLE_NAME) [ParentId],
                                    c.COLUMN_NAME [Name],
                                    c.ORDINAL_POSITION [Ordinal],
                                    CAST(CASE c.IS_NULLABLE
                                           WHEN 'YES' THEN 1
                                           WHEN 'NO' THEN 0
                                           ELSE 0
                                         END AS BIT) [IsNullable],
                                    CASE WHEN c.DATA_TYPE IN ('varchar', 'nvarchar', 'varbinary')
                                              AND c.CHARACTER_MAXIMUM_LENGTH = -1 THEN c.DATA_TYPE + '(max)'
                                         ELSE c.DATA_TYPE
                                    END AS [TypeName],
                                    c.CHARACTER_MAXIMUM_LENGTH [MaxLength],
                                    CAST(c.NUMERIC_PRECISION AS INTEGER) [Precision],
                                    CAST(c.DATETIME_PRECISION AS INTEGER) AS [DateTimePrecision],
                                    CAST(c.NUMERIC_SCALE AS INTEGER) [Scale],
                                    c.COLLATION_CATALOG [CollationCatalog],
                                    c.COLLATION_SCHEMA [CollationSchema],
                                    c.COLLATION_NAME [CollationName],
                                    c.CHARACTER_SET_CATALOG [CharacterSetCatalog],
                                    c.CHARACTER_SET_SCHEMA [CharacterSetSchema],
                                    c.CHARACTER_SET_NAME [CharacterSetName],
                                    CAST(0 AS BIT) AS [IsMultiSet],
                                    CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') AS BIT) AS [IsIdentity],
                                    CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed')
                                    | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1
                                           ELSE 0
                                      END AS BIT) AS [IsStoreGenerated],
                                    c.COLUMN_DEFAULT [Default]
                             FROM   INFORMATION_SCHEMA.COLUMNS c
                                    INNER JOIN INFORMATION_SCHEMA.VIEWS v
                                        ON c.TABLE_CATALOG = v.TABLE_CATALOG
                                           AND c.TABLE_SCHEMA = v.TABLE_SCHEMA
                                           AND c.TABLE_NAME = v.TABLE_NAME
                             WHERE  NOT (
                                         v.TABLE_SCHEMA = 'dbo'
                                         AND v.TABLE_NAME IN ('syssegments', 'sysconstraints')
                                         AND SUBSTRING(CAST(SERVERPROPERTY('productversion') AS VARCHAR(20)), 1, 1) = 8
                                        )
                            ) AS [Extent3]
                   ) AS [UnionAll1]
            ON (0 = [UnionAll1].[C1])
               AND ([Extent1].[Id] = [UnionAll1].[ParentId])
        LEFT OUTER JOIN (
                         SELECT [UnionAll2].[Id] AS [C1],
                                CAST(1 AS BIT) AS [C2]
                         FROM   (
                                 SELECT QUOTENAME(tc.CONSTRAINT_SCHEMA) + QUOTENAME(tc.CONSTRAINT_NAME) [Id],
                                        QUOTENAME(tc.TABLE_SCHEMA) + QUOTENAME(tc.TABLE_NAME) [ParentId],
                                        tc.CONSTRAINT_NAME [Name],
                                        tc.CONSTRAINT_TYPE [ConstraintType],
                                        CAST(CASE tc.IS_DEFERRABLE
                                               WHEN 'NO' THEN 0
                                               ELSE 1
                                             END AS BIT) [IsDeferrable],
                                        CAST(CASE tc.INITIALLY_DEFERRED
                                               WHEN 'NO' THEN 0
                                               ELSE 1
                                             END AS BIT) [IsInitiallyDeferred]
                                 FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
                                 WHERE  tc.TABLE_NAME IS NOT NULL
                                ) AS [Extent4]
                                INNER JOIN (
                                            SELECT  7 AS [C1],
                                                    [Extent5].[ConstraintId] AS [ConstraintId],
                                                    [Extent6].[Id] AS [Id]
                                            FROM    (
                                                     SELECT QUOTENAME(CONSTRAINT_SCHEMA) + QUOTENAME(CONSTRAINT_NAME) [ConstraintId],
                                                            QUOTENAME(TABLE_SCHEMA) + QUOTENAME(TABLE_NAME) + QUOTENAME(COLUMN_NAME) [ColumnId]
                                                     FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                                                    ) AS [Extent5]
                                                    INNER JOIN (
                                                                SELECT  QUOTENAME(c.TABLE_SCHEMA) + QUOTENAME(c.TABLE_NAME) + QUOTENAME(c.COLUMN_NAME) [Id],
                                                                        QUOTENAME(c.TABLE_SCHEMA) + QUOTENAME(c.TABLE_NAME) [ParentId],
                                                                        c.COLUMN_NAME [Name],
                                                                        c.ORDINAL_POSITION [Ordinal],
                                                                        CAST(CASE c.IS_NULLABLE
                                                                               WHEN 'YES' THEN 1
                                                                               WHEN 'NO' THEN 0
                                                                               ELSE 0
                                                                             END AS BIT) [IsNullable],
                                                                        CASE WHEN c.DATA_TYPE IN ('varchar', 'nvarchar', 'varbinary')
                                                                                  AND c.CHARACTER_MAXIMUM_LENGTH = -1 THEN c.DATA_TYPE + '(max)'
                                                                             ELSE c.DATA_TYPE
                                                                        END AS [TypeName],
                                                                        c.CHARACTER_MAXIMUM_LENGTH [MaxLength],
                                                                        CAST(c.NUMERIC_PRECISION AS INTEGER) [Precision],
                                                                        CAST(c.DATETIME_PRECISION AS INTEGER) [DateTimePrecision],
                                                                        CAST(c.NUMERIC_SCALE AS INTEGER) [Scale],
                                                                        c.COLLATION_CATALOG [CollationCatalog],
                                                                        c.COLLATION_SCHEMA [CollationSchema],
                                                                        c.COLLATION_NAME [CollationName],
                                                                        c.CHARACTER_SET_CATALOG [CharacterSetCatalog],
                                                                        c.CHARACTER_SET_SCHEMA [CharacterSetSchema],
                                                                        c.CHARACTER_SET_NAME [CharacterSetName],
                                                                        CAST(0 AS BIT) AS [IsMultiSet],
                                                                        CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)),
                                                                                            c.COLUMN_NAME, 'IsIdentity') AS BIT) AS [IsIdentity],
                                                                        CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)),
                                                                                            c.COLUMN_NAME, 'IsComputed')
                                                                        | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1
                                                                               ELSE 0
                                                                          END AS BIT) AS [IsStoreGenerated],
                                                                        c.COLUMN_DEFAULT AS [Default]
                                                                FROM    INFORMATION_SCHEMA.COLUMNS c
                                                                        INNER JOIN INFORMATION_SCHEMA.TABLES t
                                                                            ON c.TABLE_CATALOG = t.TABLE_CATALOG
                                                                               AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
                                                                               AND c.TABLE_NAME = t.TABLE_NAME
                                                                               AND t.TABLE_TYPE IN ('BASE TABLE', 'VIEW')
                                                               ) AS [Extent6]
                                                        ON [Extent6].[Id] = [Extent5].[ColumnId]
                                            UNION ALL
                                            SELECT  11 AS [C1],
                                                    [Extent7].[ConstraintId] AS [ConstraintId],
                                                    [Extent8].[Id] AS [Id]
                                            FROM    (
                                                     SELECT CAST( NULL AS NVARCHAR (1)) [ConstraintId], CAST( NULL AS NVARCHAR (MAX)) [ColumnId] WHERE 1= 2
                                                    ) AS [Extent7]
                                                    INNER JOIN (
                                                                SELECT  QUOTENAME(c.TABLE_SCHEMA) + QUOTENAME(c.TABLE_NAME) + QUOTENAME(c.COLUMN_NAME) [Id],
                                                                        QUOTENAME(c.TABLE_SCHEMA) + QUOTENAME(c.TABLE_NAME) [ParentId],
                                                                        c.COLUMN_NAME [Name],
                                                                        c.ORDINAL_POSITION [Ordinal],
                                                                        CAST(CASE c.IS_NULLABLE
                                                                               WHEN 'YES' THEN 1
                                                                               WHEN 'NO' THEN 0
                                                                               ELSE 0
                                                                             END AS BIT) [IsNullable],
                                                                        CASE WHEN c.DATA_TYPE IN ('varchar', 'nvarchar', 'varbinary')
                                                                                  AND c.CHARACTER_MAXIMUM_LENGTH = -1 THEN c.DATA_TYPE + '(max)'
                                                                             ELSE c.DATA_TYPE
                                                                        END AS [TypeName],
                                                                        c.CHARACTER_MAXIMUM_LENGTH [MaxLength],
                                                                        CAST(c.NUMERIC_PRECISION AS INTEGER) [Precision],
                                                                        CAST(c.DATETIME_PRECISION AS INTEGER) AS [DateTimePrecision],
                                                                        CAST(c.NUMERIC_SCALE AS INTEGER) [Scale],
                                                                        c.COLLATION_CATALOG [CollationCatalog],
                                                                        c.COLLATION_SCHEMA [CollationSchema],
                                                                        c.COLLATION_NAME [CollationName],
                                                                        c.CHARACTER_SET_CATALOG [CharacterSetCatalog],
                                                                        c.CHARACTER_SET_SCHEMA [CharacterSetSchema],
                                                                        c.CHARACTER_SET_NAME [CharacterSetName],
                                                                        CAST(0 AS BIT) AS [IsMultiSet],
                                                                        CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)),
                                                                                            c.COLUMN_NAME, 'IsIdentity') AS BIT) AS [IsIdentity],
                                                                        CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)),
                                                                                            c.COLUMN_NAME, 'IsComputed')
                                                                        | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1
                                                                               ELSE 0
                                                                          END AS BIT) AS [IsStoreGenerated],
                                                                        c.COLUMN_DEFAULT [Default]
                                                                FROM    INFORMATION_SCHEMA.COLUMNS c
                                                                        INNER JOIN INFORMATION_SCHEMA.VIEWS v
                                                                            ON c.TABLE_CATALOG = v.TABLE_CATALOG
                                                                               AND c.TABLE_SCHEMA = v.TABLE_SCHEMA
                                                                               AND c.TABLE_NAME = v.TABLE_NAME
                                                                WHERE   NOT (
                                                                             v.TABLE_SCHEMA = 'dbo'
                                                                             AND v.TABLE_NAME IN ('syssegments', 'sysconstraints')
                                                                             AND SUBSTRING(CAST(SERVERPROPERTY('productversion') AS VARCHAR(20)), 1, 1) = 8
                                                                            )
                                                               ) AS [Extent8]
                                                        ON [Extent8].[Id] = [Extent7].[ColumnId]
                                           ) AS [UnionAll2]
                                    ON (7 = [UnionAll2].[C1])
                                       AND ([Extent4].[Id] = [UnionAll2].[ConstraintId])
                         WHERE  [Extent4].[ConstraintType] = N'PRIMARY KEY'
                        ) AS [Project5]
            ON [UnionAll1].[Id] = [Project5].[C1]
WHERE   NOT ([Extent1].[Name] IN ('EdmMetadata', '__MigrationHistory'))
Do not add the following as it slows down the query by 97%. Do the sorting within your code instead.
ORDER BY [Extent1].[CatalogName],
         [Extent1].[SchemaName],
         [Extent1].[Name],
         [UnionAll1].[Ordinal]
Foreign Keys for all tables in database:
SELECT  FK_Table = FK.TABLE_NAME,
        FK_Column = CU.COLUMN_NAME,
        PK_Table = PK.TABLE_NAME,
        PK_Column = PT.COLUMN_NAME,
        Constraint_Name = C.CONSTRAINT_NAME,
        FK.TABLE_SCHEMA AS fkSchema,
        PK.TABLE_SCHEMA AS pkSchema
FROM    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
        INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
            ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
        INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
            ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
            ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
        INNER JOIN (
                    SELECT  i1.TABLE_NAME,
                            i2.COLUMN_NAME
                    FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
                            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
                                ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
                    WHERE   i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
                   ) PT
            ON PT.TABLE_NAME = PK.TABLE_NAME
ORDER BY FK.TABLE_NAME, CU.COLUMN_NAME

Tuesday, 23 October 2012

FizzBuzz generators

SQL

WITH mil AS (
 SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY c.column_id ) [n]
 FROM master.sys.all_columns as c
 CROSS JOIN master.sys.all_columns as c2
)                
 SELECT CASE WHEN n  % 3 = 0 THEN
             CASE WHEN n  % 5 = 0 THEN 'FizzBuzz' ELSE 'Fizz' END
        WHEN n % 5 = 0 THEN 'Buzz'
        ELSE CAST(n AS char(6))
     END + CHAR(13)
 FROM mil

C#

foreach (int number in Enumerable.Range(1, 100))
{
    bool isDivisibleBy3 = (number % 3) == 0;
    bool isDivisibleBy5 = (number % 5) == 0;

    if (isDivisibleBy3)
         Console.Write("Fizz");

    if (isDivisibleBy5)
         Console.Write("Buzz");

    if (!isDivisibleBy3 && !isDivisibleBy5)
         Console.Write(number);

    Console.WriteLine();
}

C# linq

 Enumerable
  .Range(1, 100)
  .Select(i =>
    i % 15 == 0 ? "FizzBuzz" :
    i % 5 == 0 ? "Buzz" :
    i % 3 == 0 ? "Fizz" :
    i.ToString())
  .ToList()
  .ForEach(s => Console.WriteLine(s));

SQL Lotter number selector

Just a bit of fun with SQL

WITH
    L0 AS (SELECT 0 AS C UNION ALL SELECT 0),
    L1 AS (SELECT 0 AS C FROM L0 AS A CROSS JOIN L0 AS B),
    L2 AS (SELECT 0 AS C FROM L1 AS A CROSS JOIN L1 AS B),
    L3 AS (SELECT 0 AS C FROM L2 AS A CROSS JOIN L2 AS B),
    Nums AS (SELECT TOP(49) ROW_NUMBER()
       OVER(ORDER BY (SELECT 0)) AS n FROM L3 ORDER BY n),
    Choice AS (SELECT TOP(6) n FROM Nums ORDER BY CHECKSUM(NEWID()))
SELECT STUFF( (SELECT ',' + CAST(n AS VARCHAR(10)) AS [text()]
FROM Choice ORDER BY n
FOR XML PATH('')), 1, 1, '')

Friday, 17 August 2012

Effortless .Net Encryption

I've just released a new open source project called Effortless .Net Encryption. It can be found here: https://github.com/sjh37/Effortless-.Net-Encryption

Effortless .Net Encryption is a library that is written in C# 4.0, contains 68 unit tests and 190 Pex unit tests, and provides:
  • Rijndael encryption/decryption.
  • Hashing and Digest creation/validation.
  • Password and salt creation.

Available on Nuget

https://nuget.org/packages/Effortless.Net.Encryption/
To install Effortless.Net.Encryption, run the following command in the Package Manager Console

Install-Package Effortless.Net.Encryption

Wednesday, 25 April 2012

A generic singleton using Lazy< T >

Carring on from the generic singleton post here.

Since we have .NET 4, we can now make use of Lazy< T >

public class Singleton< T > where T : class, new()
{
    private Singleton() {}

    private static readonly Lazy< T > instance = new Lazy< T >(() => new T());

    public static T Instance { get { return instance.Value; } } 
}

Wednesday, 28 March 2012

Please turn off hyperlinks in PowerPoint

When giving presentations, you should really turn off PowerPoints AutoCorrect feature of it changing a hyperlink into an underlined link.

Unless you actually are going to click on the link during the presentation, then turn it off. It's much easer to read without it being in a different font and underlined.

To turn it off in PowerPoint, goto Tools -> Options -> Proofing -> AutoCorrect options. Untick the box highlighted below.

Friday, 23 March 2012

Recursive CTE (Common Table Expression)

There is sometimes a problem of wanting to remove data (email addresses in this example) from within a string which are delimited.

For example, if you want to remove all non bybox email addresses from "some.name@bybox.com; simon@hicrest.net; fred.bloggs@bybox.com"
and do this for every table, without having to create functions to break apart the string first, how are you going to do it?

Here's how:

Our example table looks like this
CREATE TABLE data_export
(
 data_export_id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
 email_address VARCHAR(255) NOT NULL
 -- ... Other fields left out for brevity
)

Insert some test data
INSERT INTO data_export (email_address)
VALUES  ('some.name@bybox.com; simon@hicrest.net; fred.bloggs@bybox.com'),
        ('neo@matrix.com; me@bybox.com'),
        ('fred@b.com; xxx@bybox.com'),
        ('fred@bbc.com'),
        ('an.other@bybox.com')

Next is the recursive CTE SQL is in several sections:
  • split_by_delimeter - Breaking apart the string by delimeters.
  • just_bybox - Keep the @bybox emaill addresses.
  • distrinct_set - The ID's we want to update.
  • STUFF - The rebuild section is the final select/update statement which containst the STUFF keyword.

;WITH   split_by_delimeter
          AS (
              SELECT    data_export_id,
                        email_address,
                        CHARINDEX(';', email_address + ';') AS n,
                        CAST('' AS VARCHAR(255)) AS result
              FROM      data_export
              UNION ALL
              SELECT    data_export_id,
                        SUBSTRING(email_address,CHARINDEX(';',email_address)+1, 255),
                        CHARINDEX(';', email_address),
                        LTRIM(RTRIM(SUBSTRING(email_address, 0,
                            CASE WHEN CHARINDEX(';', email_address) = 0
                            THEN 255
                            ELSE CHARINDEX(';', email_address)
                            END)))
              FROM      split_by_delimeter
              WHERE     n > 0
             ),
        just_bybox
          AS (
              SELECT    data_export_id,
                        result
              FROM      split_by_delimeter
              WHERE     result <> ''
                        AND result LIKE '%@bybox%'
             ),
        distinct_set
          AS (
              SELECT DISTINCT data_export_id
              FROM just_bybox
             )
    -- For checking
    SELECT  data_export_id,
            STUFF((
                   SELECT ';' + result FROM just_bybox y
                   WHERE y.data_export_id= x.data_export_id
                  FOR XML PATH('')
                  ), 1, 1, '') AS email_address
    FROM    distinct_set x
    /*
    -- For actual update
    UPDATE  data_export
    SET     email_address = STUFF((
                                   SELECT ';' + result FROM just_bybox y
                                   WHERE y.data_export_id= x.data_export_id
                                   FOR XML PATH('')
                                  ), 1, 1, '')
    FROM    distinct_set x
            JOIN data_export
                ON data_export.data_export_id = x.data_export_id
 */

This returns the following results:
data_export_idemail_address
1some.name@bybox.com;fred.bloggs@bybox.com
2me@bybox.com
3xxx@bybox.com
5an.other@bybox.com

Perfect!