Setting Up Transparent Data Encryption in SQL Server

Introduction

This is geared more toward DBA's than developers, but developers need to know how this works. Transparent Data Encryption or TDE is a feature of Microsoft SQL Server in version 2008 and up that allows you to encrypt the data and log files of your SQL server with no special operations in your transactions at all. If you have used column level encryption with symmetric or asymmetric keys then you know what a blessing this is. Everything happens behind the scenes and your files and backups are safe from data theft with very little work at all on your part.

The What

TDE protects your data at rest at the database level. What this means is your backups and data and log files for the encrypted database as well as tempDB are encrypted and protected in most cases with a symmetric key called a DEK (Database Encryption Key) that is stored in the database and protected with a certificate in the Master database secured with the service master key of the server. Sometimes an asymmetric key stored in an EKM module is used instead of a certificate but we're going to look at symmetric key TDE in this BLOG.

The multi-layer encryption scheme SQL Server uses for protecting data is a complex one to say the least.  Here we're just going to look at how to get it rolling quickly. Below is the diagram of the TDE hierarchy.

TDE is set on a per database basis. There are a few gotcha's that need to be considered when enabling TDE on a database.

• Encrypted data is NOT compressible. Read that carefully! Row and Page level data compression is still possible since this happens before TDE does its thing. However, your nice little compressed backups that have been saving your space on the NAS...you can kiss those goodbye. Make sure you take this into consideration when capacity planning so you don't run out of space.
• Once encrypted, the whole database is inaccessible without the certificate and key. BACK THIS UP IMMEDIATELY and put it somewhere safe! The same protection that keeps data thieves from stealing your data and log files and attaching them or restoring your backups will also prevent you from accessing the data. You have been warned.
• You can't encrypt a read-only database or a database with any read-only file groups.
• Encryption happens in the background once you enable TDE on the database. Until it has finished, most maintenance operations on the database are not allowed. Review the link to the MSDN at the bottom of the article for a complete listing of restrictions on this.
• Filestream data is not encrypted.
• Replication data is unencrypted before publishing. You will have to enable TDE on each member of the replication team individually.
• TDE is available on Enterprise and higher level editions of SQL server, Development and Evaluation editions starting with version 2008.  They have recently added TDE to Azure but on your local servers you're going to pay the price of taking the easy road. 
• There is a bit of a CPU increase when using TDE obviously so if you're flirting with the 85% usage mark on your server it's time to add some cores before stepping into this.

The How

Okay! Let's take a look at what it takes to enable TDE on a database. Just a hint here, the scripts I give you are fill in the blank kind of things that you can save for a quick script to get it done in most cases. You can save these to file or refer back to the blog for a quick select and copy. I know I am always stealing code from some of Peter's and Daniel's blogs to save typing time.

-- Step 1 -- Create a Master Key
USE [master]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyR3a11yL0ng&c0mpl3xP@$$20rd!'

-- Step 2 -- Create the server certificate
CREATE CERTIFICATE [CERTIFICATENAMEHERE] 
WITH SUBJECT = 'DATABASENAMEHERE TDE'

-- Step 3 -- Create the database encryption key
USE [DATABASEIMENCRYPTING]
GO 
CREATE DATABASE ENCRYPTION KEY 
 WITH ALGORITHM = AES_256 --<--Your choice here, but anything less is uncivilized!
ENCRYPTION BY SERVER CERTIFICATE [CERTIFICATENAMEHERE]
GO 

-- Step 4 -- Backup the key and certificate NOW!!!!
USE [master] 
GO 
-- NOTE! You must use seperate locations for the keys and certs.
BACKUP CERTIFICATE [CERTIFICATENAMEHERE]  
TO FILE = '\\UNC PATH TO MY BACKUP REPOSITORY\certs\[CERTIFICATENAMEHERE].cer' 
WITH PRIVATE KEY (FILE = '\\UNC PATH TO MY BACKUP REPOSITORY\Keys\[CERTIFICATENAMEHERE].pvk' , 
ENCRYPTION BY PASSWORD = 'An0th3rR3a11yL0ng&c0mpl3xP@$$20rd!' ) 
GO 

-- Step 5 -- Enable TDE on the database
ALTER DATABASE [DATABASEIMENCRYPTING]
SET ENCRYPTION ON

-- Step 6 -- Check the progress
USE [master] 
GO
--This will show you the status of the encryption operation going on in the background.
--The database is fully online while this is happening. Neat huh!?
SELECT 
    db.name,
    db.is_encrypted,
    dm.encryption_state,
    dm.percent_complete,
    dm.key_algorithm,
    dm.key_length
FROM sys.databases db
LEFT OUTER JOIN sys.dm_database_encryption_keys dm
 ON db.database_id = dm.database_id;
GO

That was easy enough huh!? But what if I want to move the database to another server? There is a procedure for this as well, I'll throw them all into a handy script block as well but here is the procedure.

Moving an encrypted database

• Create a database master key on the new server. This doesn't have to use the same password.
• Recreate the certificate using the backup you did when encrypting the database. You did back it up? Right??? The password is the same one that you used when backing up the cert originally! 
• Attach the database files from their location or restore the backup, whichever method you are using.
• Test access to the data.
• Here is the handy script for doing these operations and a few more you might need at some point.

-- Drop the database encryption key. REMOVE ENCRYPTION FIRST!!!!
USE [MYENCRYPTEDDATABASE]
GO
DROP DATABASE ENCRYPTION KEY

-- Drop certificate
USE [MASTER]
GO
DROP CERTIFICATE [CERTIFICATENAMEHERE]

-- Drop Master Key
USE [MASTER]
GO
DROP MASTER KEY 

Conclusion

That's it for our short lesson on TDE. I hope this has at the very least given you some clarity on the subject. Maybe it's motivated you to give TDE a shot! I know I love it. Encryption and security is an art form in itself and you never can learn too much about it or do to much to protect your server and your data.

References

TDE MSDN Documentation: https://msdn.microsoft.com/en-us/library/bb934049.aspx

css3 animations, a beginners lesson

Premise:

Ok Tidwell, your OpenGL article was pretty cool but I want to see something with a smaller footprint that still makes some cool animations yet doesn't take a ton of code and still can be used in most any OS environment (tablet/desktop/phone/etc...).  Time to check out animations using css3.  Remember way back when when Peter discussed/coded about css3 transforms?  Time to use a bit of his code along with some of my own css blog code I wrote last year.

Let's code!

I won't go too advanced this go around, I'm in the mood to make a simple square of color move around in a pattern and change colors as it moves.  If I set my timing right you could turn on Pink Floyd at a certain point and experience the magic :).  But enough about that, lets write some code!

Let's use one simple div tag to do our bidding, for this test purpose I'll embed the css directly into the my html sample (not quite a clean way to do it) versus making a .css file and calling from that route (more functional and easier to use in multiple html files).

One of the easier ways to try animations in css3 is to use the keyframes method.  The basic concept is you define the keyframe waypoint at each step of the animation and the css3 takes over the hard part in the backend to make each transition.  Want to turn an orange square to a green square, then to purple, pink, and back to orange within a moving block?  The sample below covers it!  Due to browser weirdness you'll have to create two sections to do the animations, one set handles Chrome/Safari/Opera, the other handles IE.

<!DOCTYPE html>
<html>
<head>
    <title>ph3@r m3 l33t c0d3z!</title>
</head>
<style type="text/css"> 
div {
    width: 100px;
    height: 100px;
    background-color: orange;
    position: relative;
    -webkit-animation-name: movingSquareChangingColors; /* Chrome, Safari, Opera */
    -webkit-animation-duration: 4s; /* Chrome, Safari, Opera */
    -webkit-animation-delay: 2s; /* Chrome, Safari, Opera */
    animation-name: movingSquareChangingColors;
    animation-duration: 4s;
    animation-delay: 2s;
}

/* Chrome, Safari, Opera */
@-webkit-keyframes movingSquareChangingColors {
    0%   { background-color:orange; left:0px; top:0px; }
    25%  { background-color:green; left:200px; top:0px; }
    50%  { background-color:purple; left:200px; top:200px; }
    75%  { background-color:pink; left:0px; top:200px; }
    100% { background-color:orange; left:0px; top:0px; }
}

/* Standard syntax */
@keyframes movingSquareChangingColors {
    0%   { background-color:orange; left:0px; top:0px; }
    25%  { background-color:green; left:200px; top:0px; }
    50%  { background-color:purple; left:200px; top:200px; }
    75%  { background-color:pink; left:0px; top:200px; }
    100% { background-color:orange; left:0px; top:0px; }
}
</style>
</head>
<body>

<p><b>Note:</b> This example does not work in Internet Explorer 9 and earlier versions.</p>

<div></div>

</body>
</html>

The result:

No screenshots this time around, fire the above code up in any decent modern browser (your call, but IE 10 or higher if you're using that one).

Next time I'll take the same code above and make a few minor tweaks here and there to make the animation do something similar but quite different.

Source(s):

css3 keyframes

Reflection in C#, Part 1

Intro

Reflection is a technology whereby you can view and modify the structure and behavior of a program at runtime. This general definition always seemed a bit vague to me, so picture this: You have a class, and for whatever reason you want the user to be able to select a property of your object from a dropdown, and type in a new value for the property in a textbox. You could manually populate your dropdown with the property names and have a big switch statement to set the value, but reflection would make this much easier by allowing you to, at runtime, discover the names of the properties and set their values. Nifty! I've personally used reflection for custom serialization (If you've heard of and used the TurboTags 2.0 format then you've used my reflection), and I've used it for a custom storage system that works a lot like Entity Framework. Well, now that I've tooted my own reflection horn let's get down to shiny and highly reflective brass tacks.

Note: Samples are VS2013 Community edition, .Net 4.5. Here is the sample project if you want to play along.

 

What can Reflection Do?

• List Classes in Assembly - You can get a listing of the classes within an assembly.
• Create Instance of Classes - You can create an instance of one of them thar classes that you found.
• List Properties - Need a listing of the properties of the class? Reflection has you covered.
• Get and set property value - Ooh, and you can modify those properties too!
• List Methods - Once you've listed the properties of a class, it's easy to list the methods.
• Execute Methods - Executing the methods with reflection is simple once you've found them.
• And More! - Yay more! You can get attributes and other things too.

Let's dig into some specifics. I doubt we'll cover all of them this week, but let's give it the ol' college try.

List Classes in Assembly

I've created a Windows Forms app for demonstration purposes. Let's start by listing all the classes in an assembly. The first thing we need to do, after creating the solution of course, is add a reference to the System.Reflection assembly. This is where we get all the reflection junk. Now we're going to, in the Form_Load event handler, populate a dropdownlist with the classes of our current assembly:

        private void MainForm_Load(object sender, EventArgs e)
        {
            var a = Assembly.GetExecutingAssembly();
            ListClassesEntry.Items.AddRange(a.GetTypes());
        }

Getting a reference to the current assembly is easy with reflection. We accomplish the task above by calling Assembly.GetExecutingAssembly. The next line shows how to get all the classes contained within the assembly. In our case we come up with 4 distinct classes: BlogReflection.MainForm, BlogReflection.Program, BlogReflection.Properties.Resources, and BlogReflection.Properties.Settings. This is a nice, small, manageable list. Yay!

Create An Instance of a Class

Now that we have a list of classes within our assembly, let's create an instance of the selected class when the user selects a Type from the DropDownList:

        private void ListClassesEntry_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (!String.IsNullOrWhiteSpace(ListClassesEntry.SelectedItem.ToString()))
            {
                var assembly = Assembly.GetExecutingAssembly();
                var formType = assembly.GetType(ListClassesEntry.SelectedItem.ToString());
                var constructor = formType.GetConstructor(new Type[] { });
                var instance = constructor.Invoke(new Object[] { });
            }
        }

Another pretty simple method. The key part here is the call to GetConstructor and then the call to Invoke. GetConstructor, in this form, will find the constructor that accepts parameters as specified by the Type[] type array parameter. In our case we passed in an  empty array of type Type, so we want a parameterless constructor. The next line, Invoke, calls the constructor method passing in another empty array of parameters. So in essence we first find the parameterless constructor of the selected type, and we then invoke the parameterless constructor of the type. Neato! Tip: This method bombs horribly if the type you select, with the DropDownList, doesn't have a parameterless constructor. Oh well, nothing's perfect. Figure out how to get around that on your own :)

We're Done!

Hey Pete, you didn't talk about all the other cool stuff Reflection can do! You're right, what can I say; I'm lazy. We'll cover some more parts of reflection next week.

What's Next?

Figure out how you can call a constructor that has parameters. If you'd like to, you can also skip ahead to next week's topic by figuring out how to find and execute methods as well as find and get/set property values.

Resources

C# Reflection

Web API Global Exception Handling Made Easy

Intro

Exception handling can be a bit of a chore. It always ends up looking the same; the familiar try...catch pattern, maybe you log the exception, maybe you rethrow it, yadda yadda. In a Web API you might even throw back a server error 500 response when you encounter an exception.

I know you're all sitting there chanting impatiently, willing me to tell you I can make it better...easier even! You're probably sitting at your desk or staring at your phone, thinking "Peeticus, please save us from the doldrums of routine! Help us!!". Well OK, just this once.Only because I can see the tears of joy brimming in your eyes.

Example

So I've made a simple Web API. It has a single GET method that throws an exception. Here's the code:

using System;
using System.Web.Http;

namespace BlogWebApiExceptionHandling.Controllers
{
    public class HandlingController : ApiController
    {
        public IHttpActionResult Get()
        {
            throw new ApplicationException("hey, an error!");
        }
    }
}

Run this thing and send a request to it with Fiddler. Here's what you get:
HTTP/1.1 500 Internal Server Error
Cache-Control: no-cache
Pragma: no-cache
Content-Type: application/json; charset=utf-8
Expires: -1
Server: Microsoft-IIS/8.0
X-AspNet-Version: 4.0.30319
X-SourceFiles: =?UTF-8?B?YzpcdXNlcnNccGVldGljdXNcZG9jdW1lbnRzXHZpc3VhbCBzdHVkaW8gMjAxM1xQcm9qZWN0c1xCbG9nV2ViQXBpRXhjZXB0aW9uSGFuZGxpbmdcQmxvZ1dlYkFwaUV4Y2VwdGlvbkhhbmRsaW5nXGFwaVxoYW5kbGluZ1w=?=
X-Powered-By: ASP.NET
Date: Fri, 17 Apr 2015 01:20:24 GMT
Content-Length: 2176

{"Message":"An error has occurred.","ExceptionMessage":"hey, an error!"...}

Server error 500 with a message. That's not too bad I guess, though the exception is unhandled. What if we want to log it, or send a notification email to somebody? you can put a try...catch around it sure. What if you have 5 methods? 50? all that try...catch becomes a pain in the arse. So, let's do this the easy way.

Now I add a new class to my Web API. This new class will be named GlobalExceptionLogger, but you can name yours whatever you want. Because this is only a demonstration of exception handling, I won't do anything complicated in here. I'm just setting a local variable to the Message property of the exception. Here it be:

using System.Web.Http.ExceptionHandling;

namespace BlogWebApiExceptionHandling
{
    public class GlobalExceptionLogger : ExceptionLogger
    {
        public override void Log(ExceptionLoggerContext context)
        {
            var stuff = context.Exception.Message;
        }
    }
}

There's one more line of code necessary to make this work. This new line of code goes in your WebApiConfig.cs file, within the Register method:

config.Services.Add(typeof(IExceptionLogger), new GlobalExceptionLogger());

If you now set a breakpoint back in the Log method of GlobalExceptionLogger and fire up the Web API using Fiddler, you'll see that the Log method is called for the unhandled exception. Hey, now it's globally handled! Any further Web API controllers and methods will use this thing, which is exceptionally cool. Like me! :)

Resources

What's New in ASP.Net Web API 2.1

OpenGL in C#

Preface

Have you ever want to do some graphics programming but think to yourself: Hey, do I have other options than DirectX/Flash/Silverlight?  Fear not, OpenGL (Open Graphics Library) is an option you've had since the 90's.  Based on research I'd done in regards to generating the world famous glutTeapot via Delphi in the late 90s (don't laugh too hard) it seemed to be a great idea to port over some old school code into C#.

The install

As it's an open source product there are quite a few options to pull from.  For this blog I'm using OpenTK as it was the least hassle install/code conversion over than the other options out there.  This one's a pretty easy install compared to some of the topics I've blogged about in the past.  Click the install below and include the 3 dlls in your project (OpenTK.dll, OpenTK.GLControl, and OpenTK.Compatibility.dll) after you've extracted them from the downloaded file.

Some coding (well, quite a bit)

The glut (GL Users Toolkit) isn't quite available for our purposes, but as luck would have it I found a project out there that used OpenTK and mapped out the necessary vertices to fake the teapot quite well.  The original code within the Teapot class was written by Mark J. Kilgard in 1994.  Below I've created a console application to make use of some gaming capability we have able to us via Visual Studio, plus it looked pretty good in the window :).  Rather than giving a detailed explanation outside of the source code it'll most likely be easier to follow the line by line documentation I did within my project below.

using System;
using System.Drawing;
using OpenTK;
using OpenTK.Graphics.OpenGL;
using OpenTK.Input;

namespace OpenGLConsoleApp
{
  class MyApplication
  {
    public static class Teapot
    {
      // Rim, body, lid, and bottom data must be reflected in x and
      // y; handle and spout data across the y axis only.
      public static int[,] patchdata = new int[,]
    {
      // rim
      {102, 103, 104, 105, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15},
      // body
      {12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27},
      {24, 25, 26, 27, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40},
      // lid
      {96, 96, 96, 96, 97, 98, 99, 100, 101, 101, 101, 101, 0, 1, 2, 3,},
      {0, 1, 2, 3, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117},
      // bottom 
      {118, 118, 118, 118, 124, 122, 119, 121, 123, 126, 125, 120, 40, 39, 38, 37},
      // handle
      {41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56},
      {53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 28, 65, 66, 67},
      // spout
      {68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83},
      {80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95}
    };

      public static float[,] cpdata =
    {
        {0.2f, 0, 2.7f}, {0.2f, -0.112f, 2.7f}, {0.112f, -0.2f, 2.7f}, {0,
        -0.2f, 2.7f}, {1.3375f, 0, 2.53125f}, {1.3375f, -0.749f, 2.53125f},
        {0.749f, -1.3375f, 2.53125f}, {0, -1.3375f, 2.53125f}, {1.4375f,
        0, 2.53125f}, {1.4375f, -0.805f, 2.53125f}, {0.805f, -1.4375f,
        2.53125f}, {0, -1.4375f, 2.53125f}, {1.5f, 0, 2.4f}, {1.5f, -0.84f,
        2.4f}, {0.84f, -1.5f, 2.4f}, {0, -1.5f, 2.4f}, {1.75f, 0, 1.875f},
        {1.75f, -0.98f, 1.875f}, {0.98f, -1.75f, 1.875f}, {0, -1.75f,
        1.875f}, {2, 0, 1.35f}, {2, -1.12f, 1.35f}, {1.12f, -2, 1.35f},
        {0, -2, 1.35f}, {2, 0, 0.9f}, {2, -1.12f, 0.9f}, {1.12f, -2,
        0.9f}, {0, -2, 0.9f}, {-2, 0, 0.9f}, {2, 0, 0.45f}, {2, -1.12f,
        0.45f}, {1.12f, -2, 0.45f}, {0, -2, 0.45f}, {1.5f, 0, 0.225f},
        {1.5f, -0.84f, 0.225f}, {0.84f, -1.5f, 0.225f}, {0, -1.5f, 0.225f},
        {1.5f, 0, 0.15f}, {1.5f, -0.84f, 0.15f}, {0.84f, -1.5f, 0.15f}, {0,
        -1.5f, 0.15f}, {-1.6f, 0, 2.025f}, {-1.6f, -0.3f, 2.025f}, {-1.5f,
        -0.3f, 2.25f}, {-1.5f, 0, 2.25f}, {-2.3f, 0, 2.025f}, {-2.3f, -0.3f,
        2.025f}, {-2.5f, -0.3f, 2.25f}, {-2.5f, 0, 2.25f}, {-2.7f, 0,
        2.025f}, {-2.7f, -0.3f, 2.025f}, {-3, -0.3f, 2.25f}, {-3, 0,
        2.25f}, {-2.7f, 0, 1.8f}, {-2.7f, -0.3f, 1.8f}, {-3, -0.3f, 1.8f},
        {-3, 0, 1.8f}, {-2.7f, 0, 1.575f}, {-2.7f, -0.3f, 1.575f}, {-3,
        -0.3f, 1.35f}, {-3, 0, 1.35f}, {-2.5f, 0, 1.125f}, {-2.5f, -0.3f,
        1.125f}, {-2.65f, -0.3f, 0.9375f}, {-2.65f, 0, 0.9375f}, {-2,
        -0.3f, 0.9f}, {-1.9f, -0.3f, 0.6f}, {-1.9f, 0, 0.6f}, {1.7f, 0,
        1.425f}, {1.7f, -0.66f, 1.425f}, {1.7f, -0.66f, 0.6f}, {1.7f, 0,
        0.6f}, {2.6f, 0, 1.425f}, {2.6f, -0.66f, 1.425f}, {3.1f, -0.66f,
        0.825f}, {3.1f, 0, 0.825f}, {2.3f, 0, 2.1f}, {2.3f, -0.25f, 2.1f},
        {2.4f, -0.25f, 2.025f}, {2.4f, 0, 2.025f}, {2.7f, 0, 2.4f}, {2.7f,
        -0.25f, 2.4f}, {3.3f, -0.25f, 2.4f}, {3.3f, 0, 2.4f}, {2.8f, 0,
        2.475f}, {2.8f, -0.25f, 2.475f}, {3.525f, -0.25f, 2.49375f},
        {3.525f, 0, 2.49375f}, {2.9f, 0, 2.475f}, {2.9f, -0.15f, 2.475f},
        {3.45f, -0.15f, 2.5125f}, {3.45f, 0, 2.5125f}, {2.8f, 0, 2.4f},
        {2.8f, -0.15f, 2.4f}, {3.2f, -0.15f, 2.4f}, {3.2f, 0, 2.4f}, {0, 0,
        3.15f}, {0.8f, 0, 3.15f}, {0.8f, -0.45f, 3.15f}, {0.45f, -0.8f,
        3.15f}, {0, -0.8f, 3.15f}, {0, 0, 2.85f}, {1.4f, 0, 2.4f}, {1.4f,
        -0.784f, 2.4f}, {0.784f, -1.4f, 2.4f}, {0, -1.4f, 2.4f}, {0.4f, 0,
        2.55f}, {0.4f, -0.224f, 2.55f}, {0.224f, -0.4f, 2.55f}, {0, -0.4f,
        2.55f}, {1.3f, 0, 2.55f}, {1.3f, -0.728f, 2.55f}, {0.728f, -1.3f,
        2.55f}, {0, -1.3f, 2.55f}, {1.3f, 0, 2.4f}, {1.3f, -0.728f, 2.4f},
        {0.728f, -1.3f, 2.4f}, {0, -1.3f, 2.4f}, {0, 0, 0}, {1.425f,
        -0.798f, 0}, {1.5f, 0, 0.075f}, {1.425f, 0, 0}, {0.798f, -1.425f,
        0}, {0, -1.5f, 0.075f}, {0, -1.425f, 0}, {1.5f, -0.84f, 0.075f},
        {0.84f, -1.5f, 0.075f}
    };

      public static float[] tex =
    {
      0, 0,
      1, 0,
      0, 1,
      1, 1
    };

      private static void DrawTeapot(int grid, float scale, MeshMode2 type)
      {
        float[] p = new float[48], q = new float[48], r = new float[48], s = new float[48];
        int i, j, k, l;

        GL.PushAttrib(AttribMask.EnableBit | AttribMask.EvalBit);
        GL.Enable(EnableCap.AutoNormal);
        GL.Enable(EnableCap.Normalize);
        GL.Enable(EnableCap.Map2Vertex3);
        GL.Enable(EnableCap.Map2TextureCoord2);

        // time for the math portion: remember augmented matrices?  here's where you use them!
        // prep the matrix for the data to be loaded
        GL.PushMatrix();
        // rotate the view
        GL.Rotate(270.0f, 1.0f, 0.0f, 0.0f);
        // set the size of the data
        GL.Scale(0.5f * scale, 0.5f * scale, 0.5f * scale);
        // move the data via X/Y/Z coordinates
        GL.Translate(0.0f, 0.0f, -1.5f);
        for (i = 0; i < 10; i++)
        {
          for (j = 0; j < 4; j++)
          {
            for (k = 0; k < 4; k++)
            {
              for (l = 0; l < 3; l++)
              {
                p[j * 12 + k * 3 + l] = cpdata[patchdata[i, j * 4 + k], l];
                q[j * 12 + k * 3 + l] = cpdata[patchdata[i, j * 4 + (3 - k)], l];
                if (l == 1)
                  q[j * 12 + k * 3 + l] *= -1.0f;
                if (i < 6)
                {
                  r[j * 12 + k * 3 + l] = cpdata[patchdata[i, j * 4 + (3 - k)], l];
                  if (l == 0)
                    r[j * 12 + k * 3 + l] *= -1.0f;
                  s[j * 12 + k * 3 + l] = cpdata[patchdata[i, j * 4 + k], l];
                  if (l == 0)
                    s[j * 12 + k * 3 + l] *= -1.0f;
                  if (l == 1)
                    s[j * 12 + k * 3 + l] *= -1.0f;
                }
              }
            }
          }

          // high level math for the texture coordinates
          GL.Map2(MapTarget.Map2TextureCoord2, 0f, 1f, 2, 2, 0f, 1f, 4, 2, tex);
          // high level math for the vertices
          GL.Map2(MapTarget.Map2Vertex3, 0f, 1f, 3, 4, 0f, 1f, 12, 4, p);
          // high level math for a 2 dimensional map
          GL.MapGrid2(grid, 0.0, 1.0, grid, 0.0, 1.0);
          // high level math to do the evaluation of the grids
          GL.EvalMesh2(type, 0, grid, 0, grid);
          // high level math for the vertices
          GL.Map2(MapTarget.Map2Vertex3, 0, 1, 3, 4, 0, 1, 12, 4, q);
          // high level math to do the evaluation of the grids
          GL.EvalMesh2(type, 0, grid, 0, grid);
          if (i < 6)
          {
            // high level math for the vertices
            GL.Map2(MapTarget.Map2Vertex3, 0, 1, 3, 4, 0, 1, 12, 4, r);
            // high level math to do the evaluation of the grids
            GL.EvalMesh2(type, 0, grid, 0, grid);
            // high level math for the vertices
            GL.Map2(MapTarget.Map2Vertex3, 0, 1, 3, 4, 0, 1, 12, 4, s);
            // high level math to do the evaluation of the grids
            GL.EvalMesh2(type, 0, grid, 0, grid);
          }
        }

        // release the manipulated data from the matrix
        GL.PopMatrix();
        // release the manipulated data from the matrix attributes
        GL.PopAttrib();
      }

      public static void DrawSolidTeapot(float scale)
      {
        DrawTeapot(14, scale, MeshMode2.Fill);
      }

      public static void DrawWireTeapot(float scale)
      {
        DrawTeapot(10, scale, MeshMode2.Line);
      }

      public static void DrawPointTeapot(float scale)
      {
        DrawTeapot(10, scale, MeshMode2.Point);
      }

    }

    private static int teapotList;

    [STAThread]
    public static void Main()
    {
      using (var game = new GameWindow())
      {
        game.Load += (sender, e) =>
        {
          // setup settings, load textures, sounds
          game.VSync = VSyncMode.On;

          // easier to create float arrays in advance
          float[] ambient = { 0.0f, 0.0f, 0.0f, 1.0f };
          float[] diffuse = { 1.0f, 1.0f, 1.0f, 1.0f };
          float[] specular = { 1.0f, 1.0f, 1.0f, 1.0f };
          float[] position = { 0.0f, 3.0f, 3.0f, 0.0f };
          float[] lmodel_ambient = { 0.2f, 0.2f, 0.2f, 1.0f };
          float[] local_view = { 0.0f };

          // setup your light source(s)
          GL.Light(LightName.Light0, LightParameter.Ambient, ambient);
          GL.Light(LightName.Light0, LightParameter.Diffuse, diffuse);
          GL.Light(LightName.Light0, LightParameter.Position, position);
          GL.LightModel(LightModelParameter.LightModelAmbient, lmodel_ambient);
          GL.LightModel(LightModelParameter.LightModelLocalViewer, local_view);

          GL.FrontFace(FrontFaceDirection.Cw);
          GL.Enable(EnableCap.Lighting);
          GL.Enable(EnableCap.Light0);
          GL.Enable(EnableCap.AutoNormal);
          GL.Enable(EnableCap.Normalize);
          GL.Enable(EnableCap.DepthTest);

          GL.NewList(GL.GenLists(1), ListMode.Compile);

          // teapot time
          Teapot.DrawSolidTeapot(1.0f);
          GL.EndList();
        };

        game.Resize += (sender, e) =>
        {
          // setup the viewer for your image(s)
          GL.Viewport(0, 0, game.Width, game.Height);
        };

        game.UpdateFrame += (sender, e) =>
        {
          // add game logic, input handling
          if (game.Keyboard[Key.Escape])
          {
            game.Exit();
          }
        };

        game.RenderFrame += (sender, e) =>
        {
          // step 1: clear the buffer
          GL.Clear(ClearBufferMask.ColorBufferBit | ClearBufferMask.DepthBufferBit);

          // step 2: render the teapot as you see fit
          // press S for a solid teapot
          if (game.Keyboard[Key.S])
            Teapot.DrawSolidTeapot(0.5f);
          // press W for a wire frame teapot
          else if (game.Keyboard[Key.W])
            Teapot.DrawWireTeapot(0.5f);
          // press P for a point frame teapot
          else if (game.Keyboard[Key.P])
            Teapot.DrawPointTeapot(0.5f);

          // step 3: force the execution of your GL code
          GL.Flush();

          // step 4: swap the buffers to display your code
          game.SwapBuffers();
        };

        // Run the game at 60 updates per second
        game.Run(60.0);
      }
    }
  }
}

There's decent documentation within the above source code, the output below:

Solid:

Wire:

Pixel:

Now for some real fun: code some buttons so when you hit certain keys you change the viewing angle so you can rotate the teapot!  Heck, figure out how to change the colors of the teapot on the fly so it looks like a magical teapot.  Enjoy!

Source(s):

OpenTK
glut utility teapot

Using the MERGE Statement

Introduction

We are born naked, wet, hungry and dying. Then things get worse....

Did you ever need to take a list or table of rows and insert or update a target table depending on the prior existence of the row? We have all run into this at one time or another in our careers. If you have not had the pleasure of doing this you haven't lived yet! We call it an "upsert" for short and it stands for UPDATE or INSERT. Ever write a huge block of code like IF EXISTS(...)...ELSE...? There is an easier way. I now introduce to you the MERGE statement.


The What

So what is this magic that you speak of wizard? The MERGE statement was introduced into SQL in Server 2008. It will perform INSERT, UPDATE and even DELETE from a source to a destination. The definition of the structure of the merge statement can be scary to look at but you will find after using it a few times it's really quite easy to get the hang of using.

[ WITH <common_table_expression> [,...n] ]
MERGE 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]
    USING <table_source> 
    ON <merge_search_condition>
    [ WHEN MATCHED [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
        THEN <merge_not_matched> ]
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ <output_clause> ]
    [ OPTION ( <query_hint> [ ,...n ] ) ]    
;

<target_table> ::=
{ 
    [ database_name . schema_name . | schema_name . ]
  target_table
}

<merge_hint>::=
{
    { [ <table_hint_limited> [ ,...n ] ]
    [ [ , ] INDEX ( index_val [ ,...n ] ) ] }
}

<table_source> ::= 
{
    table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ] 
        [ WITH ( table_hint [ [ , ]...n ] ) ] 
  | rowset_function [ [ AS ] table_alias ] 
        [ ( bulk_column_alias [ ,...n ] ) ] 
  | user_defined_function [ [ AS ] table_alias ]
  | OPENXML <openxml_clause> 
  | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] 
  | <joined_table> 
  | <pivoted_table> 
  | <unpivoted_table> 
}

<merge_search_condition> ::=
    <search_condition>

<merge_matched>::=
    { UPDATE SET <set_clause> | DELETE }

<set_clause>::=
SET
  { column_name = { expression | DEFAULT | NULL }
  | { udt_column_name.{ { property_name = expression
                        | field_name = expression }
                        | method_name ( argument [ ,...n ] ) }
    }
  | column_name { .WRITE ( expression , @Offset , @Length ) }
  | @variable = expression
  | @variable = column = expression
  | column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression
  | @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression
  | @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression
  } [ ,...n ] 

<merge_not_matched>::=
{
    INSERT [ ( column_list ) ] 
        { VALUES ( values_list )
        | DEFAULT VALUES }
}

<clause_search_condition> ::=
    <search_condition>

<search condition> ::=
    { [ NOT ] <predicate> | ( <search_condition> ) } 
    [ { AND | OR } [ NOT ] { <predicate> | ( <search_condition> ) } ] 
[ ,...n ] 

<predicate> ::= 
    { expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } expression 
    | string_expression [ NOT ] LIKE string_expression 
  [ ESCAPE 'escape_character' ] 
    | expression [ NOT ] BETWEEN expression AND expression 
    | expression IS [ NOT ] NULL 
    | CONTAINS 
  ( { column | * } , '< contains_search_condition >' ) 
    | FREETEXT ( { column | * } , 'freetext_string' ) 
    | expression [ NOT ] IN ( subquery | expression [ ,...n ] ) 
    | expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } 
  { ALL | SOME | ANY} ( subquery ) 
    | EXISTS ( subquery ) } 

<output_clause>::=
{
    [ OUTPUT <dml_select_list> INTO { @table_variable | output_table }
        [ (column_list) ] ]
    [ OUTPUT <dml_select_list> ]
}

<dml_select_list>::=
    { <column_name> | scalar_expression } 
        [ [AS] column_alias_identifier ] [ ,...n ]

<column_name> ::=
    { DELETED | INSERTED | from_table_name } . { * | column_name }
    | $action

I told you it was scary! There are all kinds of possible ways to use this. If you ask me though, the very definition is what intimidates people and scares them out of learning to use it. Let's look at some simple examples of how to use this next so it's not quite so scary.


The How

We're going to need two tables to test this out with and some data inside them. Start up SSMS and your handy TestDB and open a query window. Cut and paste this into it, run it and you will have a source and a target table. The source is the table of products that came in on the shipping truck and need to be added to inventory. The target is the table that holds the current inventory of your store.

USE [TestDB]
GO

-- Create some test tables
CREATE TABLE [dbo].[tblProduct] 
( [ID] int IDENTITY
 ,[ProductName] varchar(50)
 ,[Quantity] int
)  

CREATE TABLE [dbo].[NewShipment]
(
 [ID] int IDENTITY
 ,[ProductName] varchar(50)
 ,[Quantity] int
)

-- Add some inventory
INSERT INTO [dbo].[tblProduct]
( [ProductName]
 ,[Quantity]
)
VALUES
('Left Handed Monkey Wrench',1),
('Right Handed Hammer',2),
('Invisible Paint',5),
('Drop Cloth',2),
('Wratchet Wrench',1),
('1/2 Inch Drive Socket Set',4)

-- Add some product to the shipment that just came in.
INSERT INTO [dbo].[NewShipment]
( [ProductName]
 ,[Quantity]
)
VALUES
('Left Handed Monkey Wrench',3),
('Wratchet Wrench',2),
('1/2 Inch Drive Socket Set',1),
('Coffee Cup',10)

Now that we have a products table and a shipment table, let's update our product inventory with the latest shipment that came in. We have some additions to the existing inventory and we added a line of Coffee Cups with a quantity of 10. Here is the statement heavily commented to show what is going on.

USE [TestDB]
GO

--Show initial table content
SELECT * FROM [dbo].[tblProduct];

--Merge the shipment
MERGE [dbo].[tblProduct] AS [Products] --Target
USING [dbo].[NewShipment] AS [Shipment] --Source.
 ON [Products].[ProductName] = [Shipment].[ProductName] --Join condition
--Match Condition. Add to existing inventory.
WHEN MATCHED THEN  
  UPDATE SET [Products].[Quantity] = [Products].[Quantity] + [Shipment].[Quantity] -- 
--No Match Condition. Create new inventory record.
WHEN NOT MATCHED THEN  
  INSERT ([ProductName],[Quantity]) VALUES ([Shipment].[ProductName], [Shipment].[Quantity]);

--Show outcome
SELECT * FROM [dbo].[tblProduct];

You'll notice when the statement finishes there are two result sets. The first was the content of the inventory table before the merge and the second is the content of the table after the merge completed. You'll notice our counts increased as they should have and we have added the new coffee Cup inventory to our table as well.

What about DELETE? It will also delete items in the merge. I left it out for clarity. To remove items during a merge we use the condition WHEN NOT MATCHED BY SOURCE THEN DELETE. This causes any values not in the source table to be deleted from the target table. The statement WHEN NOT MATCHED BY TARGET THEN DELETE is also valid. If we have the statement below and we execute it against the tables, what do you think the outcome will be?

MERGE [dbo].[tblProduct] AS [Products] --Table to hold final output 
USING [dbo].[NewShipment] AS [Shipment] --Table values to merge into above.
 ON [Products].[ProductName] = [Shipment].[ProductName] --Join condition
--Match Condition. Add to existing inventory.
WHEN MATCHED THEN  
  UPDATE SET [Products].[Quantity] = [Products].[Quantity] + [Shipment].[Quantity] -- 
--No Match Condition. Create new inventory record.
WHEN NOT MATCHED THEN  
  INSERT ([ProductName],[Quantity]) VALUES ([Shipment].[ProductName], [Shipment].[Quantity])
--If not matched in SOURCE, delete it.
WHEN NOT MATCHED BY SOURCE THEN DELETE;

Conclusion

I hope this has helped to demonstrate the basic use of the MERGE statement for you and given you some ideas to improve future code you have to write or refine existing code from long ago. Just remember these few little gotcha's and you will be fine:

• Duplicate rows in the target table will give you a duplicate row error. Use the GROUP BY clause or eliminate duplicates rows in the target before attempting a merge.If you need the duplicate rows in the target and use a GROUP BY clause it will apply the action to ALL duplicate rows in the target.
• Avoid filtering rows with the ON clause of the join. Use only the necessary values to provide the needed join or you could end up with some strange outcomes. 
• You can use additional conditional clauses on the WHEN operations of the merge. An example would be like: WHEN MATCHED AND ([Products].[Quantity] > 5) THEN... This would only operate on matches where the Quantity > 5.

Happy coding!

Web Api Request Validation Made Easy

Intro

Validating inputs into your system can be quite a chore. Field X might be a required field, Field Y might need to be email format, Field Z might need to be a number between 1-100. Coding such requirements isn't very difficult, just tedious. Wouldn't it be great if there was something that would lessen the monotony? Yeah, you know where I'm headed...there is such a beast, and we can bask in its magnificence!

Note: I'll be using Visual Studio 2013 Community Edition and Fiddler to do my work.

Sample

For starters, create a new Web Api application. I've created such a solution and named it BlogWebApiValidation. Add a new controller to it named ValidationController.

 

 

 

 Now add a new class to your Models folder named ValidationSampleRequest. It'll have just three properties, so it'll be pretty simple. Here it is:

using System.ComponentModel.DataAnnotations;

namespace BlogWebApiValidation.Models
{
    public class ValidationSampleRequest
    {
        [Required]
        public string RequiredString { get; set; }

        [EmailAddress]
        public string SomeEmail { get; set; }

        [Range(1, 100)]
        public byte SomeNum1To100 { get; set; }
    }
}

Now go back to your ValidationController class. It doesn't need to do much, it just needs a single method named Post that accepts an object of type ValidationSampleRequest. Here's what yours might look like:

using System.Web.Http;
using BlogWebApiValidation.Models;

namespace BlogWebApiValidation.Controllers
{
    public class ValidationController : ApiController
    {
        public IHttpActionResult Post(ValidationSampleRequest request)
        {
            if (ModelState.IsValid)
                return Ok();
            else
                return BadRequest(ModelState);
        }
    }
}

This Post method is pretty bare-bones, as we're just demonstrating model validation. All it does is check the validity of the ModelState (the request), and returns a 200 OK HTTP result if good, and a 400 Bad Request HTTP result if bad. Simple! Go ahead and run the project. You'll get a 403 Forbidden error in your browser but that's OK. Our testing involves something a little more complex than just firing up a browser window. But hey, leave the browser window open :)

Fire up Fiddler and let's play with this sucker. I'll skip some of the explanations and just show you real quick how to use Fiddler to test your Web APIs. There's a lot more to it than what I'll cover, I just want to demonstrate the API for now. Anyways, launch Fiddler. You can install it from the link below if you don't already have it.

First, click on the "File" menu, and click on the "Capture Traffic" menu item. This will tell Fiddler not to capture everything you're doing on the internet. That's too much noise for us to sift through.

Now click on the Composer tab. This brings you to a window where you can create your own requests. Select "POST" as the verb, and type in the URL of your web api controller. You can see mine below, though yours (especially the port) may be slightly different. In the headers field right below the URL you can just copy what I have. In the Request Body field, you can also copy what I have below.

Headers:
User-Agent: Fiddler
Accept: application/json
Content-Type: application/json

Request Body:
{
"RequiredString": null,
"SomeEmail": "not an email",
"SomeNum1To100": 101
}

Now hit the big Execute button. You'll see you now have a single request shown in the left-hand side of the window, and it should have a 400 result. Double-click this response record, then on the right-hand side of the window click on the "Raw" button. If you look down there in the response section it now shows you the raw HTTP response.

2 key points here:

1. You received 400 Bad Request response. Poifect!
2. The body of the response contains a JSON object which has details on what precisely went wrong. What good is model validation if the client doesn't know what they did wrong? If you keep scrolling to the right you can see that all 3 fields failed validation.

What's Next?

Validation has a lot more options like regular expressions, you can use nullable types like bool? to make a normally non-nullable field required, it will validate sub-objects in complex requests, credit cards, comparison of 2 properties, you can even create your own custom class validation methods, custom error messages, and much much more.

Fiddler also has a lot more options than what I breezed on through. Play around with it, it's great for testing Web API's.

Resources

Fiddler 
DataAnnotations (more validation options)