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!

Tuesday 20 March 2012

C# 5 attributes on optional parameters

With C# 5, you can put a special attribute on an optional parameter and the compiler will fill in the value not with a constant but with information about the calling method. This means we can implement the Logger.Trace to automagically pick up where it’s being called from:
public static void Trace(string message,
                         [CallerFilePath] string sourceFile = "",
                         [CallerMemberName] string memberName = "")
{
    string msg = String.Format("{0}: {1}.{2}: {3}",
       DateTime.Now.ToString("yyyy-mm-dd HH:MM:ss"),
       Path.GetFileNameWithoutExtension(sourceFile), memberName, message);

    LoggingInfrastructure.Log(msg);
}

Now, if the caller calls Log.Trace("some message") the compiler will fill in the missing arguments not with the empty string, but with the file and member where the call happens:

// In file called Fred.cs
public void SomeFunc()
{
  Log.Trace("Hello");
  // Compiles to Log.Trace("Hello", "Fred.cs", "SomeFunc")
}

Another example of how you can use this is in implementing INotifyPropertyChanged without needing either literal strings, expression magic or mystic weavers:
public class ViewModelBase : INotifyPropertyChanged 
{
  protected void Set(ref T field,
                        T value,
                        [CallerMemberName] string propertyName = "") 
  {
    if (!Object.Equals(field, value)) 
    {
      field = value;
      OnPropertyChanged(propertyName);
    }
  }
  // usual INPC boilerplate
}
 
public class Widget : ViewModelBase 
{
  private int _thingy;
  public int Thingy
  {
    get { return _thingy; }
    set { Set(ref _thingy, value); } // Compiler fills in "Thingy" as propertyName
  }
}
I grabbed this info from http://www.mindscapehq.com/blog/index.php/2012/03/18/what-else-is-new-in-c-5/. More can be found there, but I thought this feature important/cool enough to blog about it on its own right. And mainly so I don't forget about it...

Friday 2 March 2012

www.stilettos-sos.com

I've created yet another WordPress CMS system for a friend of my wifes this time.
Home page is a blog, the others are static pages.
www.stilettos-sos.com

Thursday 1 March 2012

The real doomsday date is Tue Jan 19 2038 at 03:14:07

Some said it was the Y2K bug we had to worry about, but it's actually Tue Jan 19 2038 03:14:07 you've really to worry about.

There are many, many systems built with time saved as a 32bit long integer value.
The max value of a signed long integer is 2147483647. This value is the number of seconds elapsed since midnight (00:00:00), January 1, 1970, coordinated universal time (UTC)

Here is an example C program, built with Visual Studio 2010.

__time32_t t;

t = 0;
printf( "The min is %s\n", _ctime32( &t ) ); 

t = 2147483647;
printf( "The max is %s\n", _ctime32( &t) ); 

t = 2147483648;
printf( "The date is %s\n", _ctime32( &t) ); 

Output:
The min is Thu Jan 01 00:00:00 1970
The max is Tue Jan 19 03:14:07 2038
The date is (null)

So after Tue Jan 19 03:14:07 2038, the date goes to null which will cause a crash. Let's just hope all the critical (including embedded) systems get fixed before then.

Wednesday 29 February 2012

PowerShell script to automatically clone/update all repos in Kiln

PowerShell Script files

Download the files here.

Change and replace the following url

Change https://your fogbugz root url to be your root url for fogbugz. For example, if your companies fogbugz url is something like https://secure.bbc.com/FogBugz/default.asp, then your root url will be https://secure.bbc.com

Obtaining a Kiln Token

The first thing you need to do before you can use this script is to obtain a Kiln token. Copy and alter the following link in your browsers URL https://your fogbugz root url/FogBugz/api.asp?cmd=logon&email=[yourEmail]&password=[yourpassword]
Changing the text in square brackets (and also removing the brackets).
You should receive something like this:
ymjt123f8882a6s7td0j8eefa6u2g8
Take the token text: ymjt123f8882a6s7td0j8eefa6u2g8 and paste it into the powerShell script (edit your local copy instead of the SVN one)
token placement

Running the script

Start PowerShell and run the script passing in the root path of your Kiln repos.
example

Will it clone everything?

It could, including branches, but by default I've set it not to clone QA/PROD/UAT repos, or branches.

How to clone branches

Remove this to clone branches

How to clone QA/PROD/UAT repos

Modify the function CanCloneRepo() which is used to filter out repos you may not want.
If you want all repos, simply return 1; from this function.
Cloning QA/UAT/PROD

This script will also remove repository folders from your drive where the repo no longer exists in Kiln!

This prevents orphaned code on your drive. However, if you don't want this functionality, simply remove the following code situated at right the bottom of the script:
remove folders
By leaving the above code in, your repository folder structures will be standardised to how its organised in Kiln. I've tested it and it works. Have faith ;-)

Thanks to

Robin Minto for providing the template of the powershell script.

Monday 27 February 2012

Ecommerce site

I forgot to blog about an ecommerce site I created for an artist:
Shop

I used Zen Cart and make my own custom template to match the main home site: www.lookonthebrightside.co.uk

Website


E-Commerce site

Friday 27 January 2012

www.lizigns.com

Just finished a new website for a friend called lizigns.com


I played with three CMS systems: Joomla, concrete5 and finally settled on WordPress CMS system.

I'm pleased with the result. It looks fresh and has all the meta tags Liz wanted. Plus is quick and snappy to use.

My views on the CMS systems I tried:
  • Jooma has good customisation of templates, and a very good admin side of things, but its actually quite slow in use.
  • Concrete5 is excellent in everyway, apart from one major stumble. You can't modify any of the template colours in the admin panel. I couldn't find the right template I wanted, so I ditched it.
  • WordPress. Good admin panel, so I was confident Liz could use it to edit her pages after I handed it over. And it has lots and lots of free template and plugins (like a gallery) to choose from.