Paul Brown

Find All Characters In A Column

noreply@blogger.com (Paul G Brown) — Tue, 14 Mar 2017 16:33:00 +0000

Here's an updated way to find all characters in a column. Basically, I loop thru the ASCII readable characters and see if they are present in the column. I did skip A-Z and a-z. If you want it to include A-Z and a-z, just comment out the If statements at the bottom of the loop.

DECLARE @ColumnName varchar(200) = 'Text'
DECLARE @TableName varchar(200) = 'Rows'
DECLARE @SchemaName varchar(200) = 'SourceData'
DECLARE @Sql varchar(max)
DECLARE @MaxLength int = 126
DECLARE @Iterator int = 32

CREATE TABLE #AllChars
(
ColChar CHAR(1),
Instances int
)

WHILE @Iterator < @MaxLength
BEGIN
SET @Sql = 'INSERT INTO #AllChars (ColChar, Instances) SELECT CHAR(' + CAST(@Iterator as varchar) + '), COUNT(*) FROM ' + @SchemaName + '.' + @TableName + ' WHERE CHARINDEX(CHAR(' + CAST(@Iterator as varchar) + '), Text) > 0'

EXEC (@Sql)

SET @Iterator = @Iterator + 1

-- Skips A-Z
IF @Iterator = 65
SET @Iterator = 91

-- Skips a-z
IF @Iterator = 97
SET @Iterator = 123
END

SELECT *
FROM #AllChars
WHERE Instances > 0
ORDER BY colchar

DROP TABLE #AllChars

Back To Basics - Validating a date

noreply@blogger.com (Paul G Brown) — Sun, 12 Jun 2016 21:27:00 +0000

Recently, a lot of my time has been spent working on systems written by someone else. One of the biggest problems I see in these systems is a lack of basic validation. Limiting the length of strings, requiring values and making sure values fall into the allowed range.

The one that annoys me the most is date validation. Let's be clear, I'm talking about some basic rules to make insure that a date is not just a valid date but is a reasonable value for the data element. January 1, 1900 is a valid date except if we're talking about a birth date for living people. The current oldest living person is 116 years old and there aren't that many people over 100. Ask yourself this question, "Are they going to be in my system?" Probably not, so don't allow it.

This simple rule will keep a lot date typos out of your system. Remember, dates come in different formats MM/dd/yyyy or dd/MM/yyyy or yyyy/MM/dd. Some users will enter zeros such as 08/02/1991 while others might enter 8/2/91. We can't eliminate all typos but at least we can get worst ones.

What other rules can we put in place? Here's the two questions you should be asking about any dates in your system.

What is the minimum and maximum range?
Is the date related to some other date that we can use to validate?

Let's say we're developing a tracking system for a health screening company. These are the people that make sure you're not already dead or dying when you buy life insurance. They need to capture the applicant's date of birth, date the test was ordered and date the screening was performed. The insurance company only insures people between the date of 21 and 65.
We can come define some simple rules very quickly.

Date of birth must be between the current date minus 65 years and the current date minus 21 years.
The date test was ordered must be between the date of birth and the current date.
The date of screening must be between the date the test was ordered and the current date.

That's all it took. I would probably look to limit the range on the date the test was ordered because it seems like these should be entered in a timely manner. Maybe six months or a year.
So who is responsible for the developing the rules? The project manager? Business analyst? User? The real answer is you, the developer. Why? The short answer is because in the end, it's your code that is going to be blamed.

The real answer is you're the professional. When people hire a professional, they are paying to get a someone who is more knowledgeable about the details. They expect someone who is going to think "you cannot have a date of birth in the future in this system." You know the look you're going to get when people discover you can enter a birth date of 02/22/2233.

So if you're working with a date element take the time to make sure you get the answers for two simple questions.

VS Removes Usings and Can't Find Types

noreply@blogger.com (Paul G Brown) — Thu, 21 May 2015 02:10:00 +0000

So I was working tonight and ran into a goofy issue with Visual Studio 2013. I had it Remove and Sort Usings for the entire solution. It did but along the way I kept getting a "Your project doesn't compile...Do you want to continue" message. Figuring it was some small issue with a quick fix, I hit Yes. Doh!! When it got done, I had a bunch of "The type or namespace name...could not be found (are you missing a using directive or assembly reference?)" errors. I also had a bunch of metadata errors but they weren't the problem.

When I'd add the needed Using, VS would remove it when I'd save and close the file. Wait a minute, I needed that reference and VS should have known that!! If I added the Using and did a Rebuild, it would leave it there and I could close the file. If the file is open it would give me the same error, red squiggles and Remove Usings would take it back out.

There were two projects that had issues, Entities and Infrastructure. Didn't matter how many times I'd Rebuild these projects. It was only these two projects.

Solution:

I opened the folder containing the solution (right-click on the solution and the option is towards the bottom of the menu).

I closed the solution in VS but left VS open.

I went into each project and deleted the Bin and Obj folders.

I re-opened the solution in VS and did a Rebuild on the solution.

JavaScript runtime error: '$' is undefined

noreply@blogger.com (Paul G Brown) — Mon, 25 Feb 2013 19:51:00 +0000

If you’re developing an MVC4 app and run into this error, you probably haven’t placed your scripts in the Script section.

Looking at the _Layout.cshtml file, you’ll see one of the last lines is

@RenderSection("scripts", required: false)

which will render a section titled scripts. The following is a sample:

@section Scripts
{
    <script type="text/javascript">
        $(function () {
            alert("JQuery loaded and working!!");
        });
    </script>
}

Here’s a link to the Gu man’s discussion of sections.

Cost me about 3 hours. Hope it saves you a few!!!

First NuGet Public Package: States & Provinces

noreply@blogger.com (Paul G Brown) — Sat, 23 Feb 2013 21:27:00 +0000

I recently had to code a drop down for a state field on an entry form. Didn’t take long and not a big pain but I thought, why is there no NuGet package for this. So,,,,

First, states and provinces are generically termed political subregions or geographic subdivisions. There is an ISO standard so we’ll follow that.

First thing we need is a representation of the subregion.

/// <summary>
/// Represents a political subregion such as a state or province.
/// </summary>
public class SubRegion
{
    /// <summary>
    /// Gets or sets the name of the subregion.
    /// </summary>
    public string Name { get; set; }

    /// <summary>
    /// Gets or sets the ISO-3166 code for the subregion.
    /// </summary>
    public string IsoCode { get; set; }

    /// <summary>
    /// Gets or sets the standard abbreviation for the subregion.
    /// </summary>
    /// <remarks>
    /// For the US, this is the USPS 2 character abbreviation.
    /// </remarks>
    public string Abbreviation { get; set; }

    /// <summary>
    /// Gets or sets an alternative abbreviation that may be used.
    /// </summary>
    /// <remarks>
    /// This is a common local or historical abbreviation.
    /// </remarks>
    public string AlternateAbbreviation { get; set; }
}

Now we need a way to identify which countries we can produce. I’m only going to do the US and Canada since that’s all I’ve had to deal with so far. Since we’ll want to support returning more than one country, we’ll add the FlagsAttribute to our enum.

/// <summary>
/// Represents the countries for whom subregions are available.
/// </summary>
[Flags]
public enum CountrySelection
{
    [Description("Canada")]
    Canada = 1,

    [Description("United States")]
    UnitedStates = 2
}

Now we need a factory to make our lists. The Make method creates an empty list and then adds the desired countries’ subregions. This allows us to return more than just one at a time.

/// <summary>
/// Public member for creating a list of subregions.
/// </summary>
/// <param name="selection">The country or countries that define the desired subregions.</param>
/// <returns>A generic List of Subregions.</returns>
/// <remarks>
/// More that one country may be returned by using the bitwise OR operator.
/// </remarks>
public static List<SubRegion> Make(CountrySelection selection)
{
var results = new List<SubRegion>();

    if (selection.HasFlag(CountrySelection.Canada))
    {
        results.AddRange(MakeCanadianProvinces());
    }

    if (selection.HasFlag(CountrySelection.UnitedStates))
    {
        results.AddRange(MakeUSStates());
    }

    if (results.Count == 0)
    {
        throw new System.NotImplementedException("The country selection has not been implemented.");
    }
    return results;
}

Then we have a method for each country to keep things neat and organized. Here’s the one for Canada.

/// <summary>
/// Creates the list of Canadian provinces.
/// </summary>
/// <returns>A generic List of provinces.</returns>
private static List<SubRegion> MakeCanadianProvinces()
{
    var results = new List<SubRegion>();
    results.Add(new SubRegion() { Abbreviation = "AB", Name = "Alberta", AlternateAbbreviation = "Alta.", IsoCode = "CA-AB" });
    results.Add(new SubRegion() { Abbreviation = "BC", Name = "British Columbia", AlternateAbbreviation = "B.C.", IsoCode = "CA-BC" });
    results.Add(new SubRegion() { Abbreviation = "MB", Name = "Manitoba", AlternateAbbreviation = "Man.", IsoCode = "CA-MB" });
    results.Add(new SubRegion() { Abbreviation = "NB", Name = "New Brunswick", AlternateAbbreviation = "N.B.", IsoCode = "CA-NB" });
    results.Add(new SubRegion() { Abbreviation = "NL", Name = "Newfoundland and Labrador", AlternateAbbreviation = "Nfld.", IsoCode = "CA-NL" });
    results.Add(new SubRegion() { Abbreviation = "NS", Name = "Nova Scotia", AlternateAbbreviation = "N.S.", IsoCode = "CA-NS" });
    results.Add(new SubRegion() { Abbreviation = "NT", Name = "Northwest Territories", AlternateAbbreviation = "N.W.T.", IsoCode = "CA-NT" });
    results.Add(new SubRegion() { Abbreviation = "NU", Name = "Nunavut", AlternateAbbreviation = "Nun.", IsoCode = "CA-NU" });
    results.Add(new SubRegion() { Abbreviation = "ON", Name = "Ontario", AlternateAbbreviation = "Ont.", IsoCode = "CA-ON" });
    results.Add(new SubRegion() { Abbreviation = "PE", Name = "Prince Edward Island", AlternateAbbreviation = "P.E.I.", IsoCode = "CA-PE" });
    results.Add(new SubRegion() { Abbreviation = "QC", Name = "Quebec", AlternateAbbreviation = "Que.", IsoCode = "CA-QC" });
    results.Add(new SubRegion() { Abbreviation = "SK", Name = "Saskatchewan", AlternateAbbreviation = "Sask.", IsoCode = "CA-SK" });
    results.Add(new SubRegion() { Abbreviation = "YT", Name = "Yukon", AlternateAbbreviation = "Yuk.", IsoCode = "CA-YT" });
    return results;
}

So, if we want to make a list of US states, we simply call our factory.

var states = Factory.Make(CountrySelection.UnitedStates);

Let’s get the US and Canada

var usAndCanada = Factory.Make(CountrySelection.UnitedStates | CountrySelection.Canada);

I’m making this a public project on GitHub so others can add the information for their country. Hope you find it useful.

Migrating Legacy Apps to the New SimpleMembership Provider

noreply@blogger.com (Paul G Brown) — Fri, 16 Nov 2012 19:53:00 +0000

Asp.Net MVC4 uses the new SimpleMembership provider, changes the table structure and adds a new hashing algorithm. The reasons for the changes can be found in this article by Jon Galloway. This article shows how to migrate your existing apps to the new provider.

I’m assuming that you stored your passwords in the unrecoverable SHA-1 format. If you didn’t, then you’ll have to change a couple of things. All of my apps are done this way so… I’m also assuming that you have created the basic skeleton of the new app and ran it once so the correct tables will be created.

First, we’ll look at the new tables. Previously, we had all of those aspnet_xxxxxx tables. Here’s the new ones.

UserProfile	Contains all of the elements relevant to the user. This is a combination of the aspnet_Users table and the aspnet_Profiles table.
webpages_Membership	Stores the password info when not using OAuth, Live, Facebook, etc. This table is somewhat of a match to the aspnet_Membership table.
webpages_OAuthMembership	Stores the provider info when using OAuth, Live, Facebook, etc.
webpages_Roles	The roles available in the system. Matches the aspnet_Roles table.
webpages_UsersInRoles	The junction table used to place users in roles. Matches the aspnet_UsersInRoles table.

Here’s a diagram of the tables.

Notice there is no ApplicationId. From reading Jon’s post I’m assuming they decided it was much simpler and probably pretty common to imbed the membership tables in the application database or one specifically for the app.

Preparation

In the old membership all of the custom properties in a blob field. SimpleMembership let’s you add columns to the UserProfile table. So do that now. In your app, you’ll also need to modify the UserProfile class in the AccountModels.cs file to have corresponding properties. I have FirstName, LastName and Phone in my example so you’ll see where those get copied.

We also need to add a class. Paste the following into the AccountModels.cs file.

[Table("webpages_Membership")]
public class webpages_Membership
{
    [Key]
    public int UserId { get; set; }

    public Nullable<DateTime> CreateDate { get; set; }

    public string ConfirmationToken { get; set; }

    public bool IsConfirmed { get; set; }

    public Nullable<DateTime> LastPasswordFailureDate { get; set; }

    public int PasswordFailuresSinceLastSuccess { get; set; }

    public string Password { get; set; }

    public Nullable<DateTime> PasswordChangedDate { get; set; }

    public string PasswordSalt { get; set; }

    public string PasswordVerificationToken { get; set; }

    public Nullable<DateTime> PasswordVerificationTokenExpirationDate { get; set; }
}

Now modify the UsersContext to look like this.

public class UsersContext : DbContext
{
    public UsersContext()
        : base("DefaultConnection")
    {
    }

    public DbSet<UserProfile> UserProfiles { get; set; }

    public DbSet<webpages_Membership> Memberships { get; set; }
}

I’m using “DefaultConnection” as my connection string name. You’ll need to modify that accordingly.

This blog post has a UDF that will extract your custom properties from the legacy system. We need to add that. I personally have a database titled Utilities that contains all my functions. You’ll need to create the UDF and place the correct database name in the migration script. Hint: Jay Hilden modified my original and placed a copy in the comments section - use that one!

While your looking at the old tables, get the ApplicationId of the application users you want to migrate. You’ll need in the copy data step.

Copy Your Data

Now that we have everything ready, we need to copy the values from the legacy database to the new database. Open a query window in the new database. In the following script, please LegacyDB with the name of your legacy database. You also need to set the @ApplicationId to the correct value.

Remember I said I had FirstName, LastName and Phone as custom properties. You’ll need to change those lines in this script to get your custom properties. If you don’t have any custom properties, just cut them out.

If you want to test this script in you can wrap it in BEGIN TRAN and ROLLBACK. You’ll just get higher identity values on the UserProfile table but you can reseed if necessary.

-- TODO: Set @ApplicationId to match the correct value from aspnet_Applications
DECLARE @ApplicationId uniqueidentifier = 'C9F849A0-68AA-47B0-B51B-4D927A9E52F0'

-- These are the values we're
-- just going to default.
DECLARE @ConfirmationToken nvarchar(128) = null,
    @IsConfirmed bit = 1,
    @LastPasswordFailureDate datetime = null,
    @PasswordFailuresSinceLastSuccess int = 0,
    @PasswordChangedDate datetime = getdate(),
    @PasswordVerificationToken nvarchar(128) = null,
    @PasswordVerificationTokenExpirationDate datetime = null

/* **************** NOTE ***************
This insert statement needs to be modified to handle
your custom profile properties.
It creates our UserProfile record.
The UserId is the primary key that is used 
throughout all of the other tables to
identify a user.
*/
INSERT INTO UserProfile (UserName, FirstName, LastName, Phone)
    SELECT 
        u.UserName,
        Utilities.dbo.GetProfilePropertyValue('FirstName', P.PropertyNames, P.PropertyValuesString), 
        Utilities.dbo.GetProfilePropertyValue('LastName', P.PropertyNames, P.PropertyValuesString), 
        Utilities.dbo.GetProfilePropertyValue('Phone', P.PropertyNames, P.PropertyValuesString)
    FROM LegacyDb.dbo.aspnet_Users AS U 
    INNER JOIN LegacyDb.dbo.aspnet_Membership AS M ON U.UserId = M.UserId 
        AND U.ApplicationId = M.ApplicationId 
    INNER JOIN LegacyDb.dbo.aspnet_Profile AS P ON U.UserId = P.UserId
/* **************** END NOTE *************** */

/* 
This insert creates the membership record and stores the old password and salt.
*/
INSERT INTO webpages_Membership (UserId, CreateDate, ConfirmationToken, IsConfirmed, LastPasswordFailureDate, PasswordFailuresSinceLastSuccess, Password, PasswordChangedDate, PasswordSalt, PasswordVerificationToken, PasswordVerificationTokenExpirationDate)
    SELECT
        up.UserId, 
        m.CreateDate, 
        @ConfirmationToken, 
        M.IsApproved as IsConfirmed, 
        @LastPasswordFailureDate, 
        @PasswordFailuresSinceLastSuccess, 
        m.Password, 
        @PasswordChangedDate, 
        m.PasswordSalt, 
        @PasswordVerificationToken, 
        @PasswordVerificationTokenExpirationDate 
    FROM LegacyDb.dbo.aspnet_Users AS U 
    INNER JOIN LegacyDb.dbo.aspnet_Membership AS M ON U.UserId = M.UserId 
        AND U.ApplicationId = M.ApplicationId 
    INNER JOIN LegacyDb.dbo.aspnet_Profile AS P ON U.UserId = P.UserId
    INNER JOIN UserProfile up on up.UserName = u.UserName

-- Now we move the roles
INSERT INTO webpages_Roles (RoleName) 
    SELECT r.RoleName FROM LegacyDb.dbo.aspnet_Roles r WHERE r.ApplicationId = @ApplicationId

-- Get everybody in the correct roles.
INSERT INTO webpages_UsersInRoles
    SELECT up.UserId, wp_R.RoleId
    FROM LegacyDb.dbo.aspnet_UsersInRoles a_UIR
    INNER JOIN LegacyDb.dbo.aspnet_Roles a_R ON a_UIR.RoleId = a_R.RoleId
    INNER JOIN webpages_Roles wp_R ON a_R.RoleName = wp_R.RoleName
    INNER JOIN LegacyDb.dbo.aspnet_Users a_U ON a_UIR.UserId = a_U.UserId
    INNER JOIN UserProfile up ON a_U.UserName = up.UserName

Making The App Work

You might think you’re ready to go but not yet. The old membership used a different hash to store passwords. This means when your user goes to login, they’ll get rejected because the hashed password values don’t match.

What we’re going to do is manually validate the password using the old hash and then have SimpleMembership “change” the password so that the correct hash is stored in the tables.

Here’s the code.

public class LegacySecurity
{
    /// <summary>
    /// The user's profile record.
    /// </summary>
    private UserProfile userProfile;

    /// <summary>
    /// The users membership record.
    /// </summary>
    private webpages_Membership membership;

    /// <summary>
    /// The clear text password.
    /// </summary>
    private string clearPassword;

    /// <summary>
    /// The password after it has been hashed using SHA1.
    /// </summary>
    private string sha1HashedPassword;

    /// <summary>
    /// The user's user name.
    /// </summary>
    private string userName;

    /// <summary>
    /// Inidcates if the authentication token in the cookie should be persisted beyond the current session.
    /// </summary>
    private bool persistCookie;

    /// <summary>
    /// Validates the user against legacy values.
    /// </summary>
    /// <param name="userName">The user's UserName.</param>
    /// <param name="password">The user's password.</param>
    /// <param name="persistCookie">Inidcates if the authentication token in the cookie should be persisted beyond the current session.</param>
    /// <returns>true if the user is validated and logged in, otherwise false.</returns>
    public bool Login(string userName, string password, bool persistCookie = false)
    {
        this.userName = userName;
        this.clearPassword = password;
        this.persistCookie = persistCookie;

        if (!GetOriginalValues())
        {
            return false;
        }

        SetHashedPassword();

        if (this.sha1HashedPassword != this.membership.Password)
        {
            return false;
        }

        this.SetPasswordAndLoginUser();

        return true;
    }

    /// <summary>
    /// Gets the original password values
    /// </summary>
    protected bool GetOriginalValues()
    {
        using (var context = new Models.UsersContext())
        {
            this.userProfile = context.UserProfiles.Where(x => x.UserName.ToLower() == userName.ToLower()).SingleOrDefault();

            if (this.userProfile == null)
            {
                return false;
            }

            this.membership = context.Memberships.Where(x => x.UserId == this.userProfile.UserId).SingleOrDefault();

            if (this.membership == null)
            {
                return false;
            }
            
            if (!this.membership.IsConfirmed)
            {
                return false;
            }
        }

        return true;
    }

    /// <summary>
    /// Encrypts the password using the SHA1 algorithm.
    /// </summary>
    /// <remarks>
    /// Many thanks to Malcolm Swaine for the hashing code.
    /// http://www.codeproject.com/Articles/32600/Manually-validating-an-ASP-NET-user-account-with-a
    /// </remarks>
    protected void SetHashedPassword()
    {
        byte[] bIn = Encoding.Unicode.GetBytes(clearPassword);
        byte[] bSalt = Convert.FromBase64String(membership.PasswordSalt);
        byte[] bAll = new byte[bSalt.Length + bIn.Length];
        byte[] bRet = null;
        Buffer.BlockCopy(bSalt, 0, bAll, 0, bSalt.Length);
        Buffer.BlockCopy(bIn, 0, bAll, bSalt.Length, bIn.Length);

        HashAlgorithm s = HashAlgorithm.Create("SHA1");
            bRet = s.ComputeHash(bAll);
        string newHash = Convert.ToBase64String(bRet);
            this.sha1HashedPassword = newHash;
    }

    /// <summary>
    /// Sets the password using the new algorithm and perofrms a login.
    /// </summary>
    protected void SetPasswordAndLoginUser()
    {
        var token = WebMatrix.WebData.WebSecurity.GeneratePasswordResetToken(this.userName, 2);
            WebMatrix.WebData.WebSecurity.ResetPassword(token, clearPassword);
            WebMatrix.WebData.WebSecurity.Login(userName, clearPassword, persistCookie);
    }
}

Here’s how this works. GetOriginalValues retrieves the original hashed password and salt. SetHashedPassword hashes the password entered by the user. The two hashed strings are compared. If they don’t match, false is returned.

If they do, then SetPasswordAndLoginUser gets a reset token and then immediately resets the password using SimpleMembership. Now everything is stored correctly. We have to use GeneratePasswordResetToken instead of ChangePassword because even though we know the password, we can’t use it because the hashes won’t match.

We login the user so that the user doesn’t know anything different happened.

Not Quite Done

We have one thing left to do. We have to get the system to call our Login method. In the AccountController, go to the Login method and replace it with this.

[HttpPost]
[AllowAnonymous]
[ValidateAntiForgeryToken]
public ActionResult Login(LoginModel model, string returnUrl)
{
    if (ModelState.IsValid && WebSecurity.Login(model.UserName, model.Password, persistCookie: model.RememberMe))
    {
        return RedirectToLocal(returnUrl);
    }

    // Here we check to see if the user has a legacy password.
    var legacySecurity = new LegacySecurity();
    if (ModelState.IsValid && legacySecurity.Login(model.UserName, model.Password, persistCookie: model.RememberMe))
    {
        return RedirectToLocal(returnUrl);
    }

    // If we got this far, something failed, redisplay form
    ModelState.AddModelError("", "The user name or password provided is incorrect.");
    return View(model);
}

The nice thing about this code is that when we’re done allowing the old hash, we simply remove the code in the middle and it is exactly as it was.

What about performance? Once a user is converted to the new hash, they won’t hit the legacy check unless they use the wrong password so there should be minimal impact.

The thing I like about this is that it keeps the user from knowing anything changed.

Hope this makes life easier for you.

Windows 8 Keyboard Shortcuts

noreply@blogger.com (Paul G Brown) — Thu, 08 Nov 2012 22:23:00 +0000

There are numerous posts on the keyboard shortcuts in Windows 8. I’m not going to regurgitate those. Here are the common things you’ll want to do.

Shut Your Computer Off, Network / Wi-Fi, Volume, Keyboard

Press Windows+I which brings up the Settings bar (below left).

Search, Change Application Settings, Manage Devices

Windows+C which brings up the Charms bar (above right). Want to find a song or artist in Music, use

The rest of the story

If you want to read about other short-cuts and print a handy-dandy cheat sheet, visit the Windows Experience Blog.

IE 10

Want to get the most out of IE10, then How-To Geek’s The Best Tips and Tricks for Getting the Most out of Internet Explorer 10 is exactly what you need. There’s also Internet Explorer 10 Shortcut Keys In Windows 8 which has some Windows 8 stuff also.

What A Week!!!!

noreply@blogger.com (Paul G Brown) — Wed, 07 Nov 2012 22:58:00 +0000

I just returned from BUILD 2012 and I can’t say enough about it. If you’ve never been to a Build or PDC, you haven’t been to a conference. Microsoft pulls out all the stops!!! It is amazing. Of course, the goodies are great but they pale in comparison to the people you meet and what you learn.

The People

I met some amazing folks but my Hackathon team is at the top of the list: Glenn Henriksen, Geir-Tore Lindsve, Laura Thompson and Joseph Tu. Five folks who had never met came together win the Windows Azure category! These folks rock!

I also met some ‘softies who just plain kick tail! Sreekanth Kannepalli was our mentor for the Hackathon and was invaluable in our win. We also got help from Denis Delimarschi. Of course, I can’t forget the Dan Fernandez, the host and moderator. It rocked, Dan!!! Arif Shafique and Theo Yaung are two other folks I met through the days. I’m telling you, Microsoft has some smart cookies!!

The Goodies

Each attendee got a Microsoft Surface RT 32GB, a Nokia Lumia 920 and 100GB of SkyDrive storage.

Let’s skip the storage and go straight to the Lumia 920. The pictures are stunning even in low light (see below) and the large screen makes them come to life. It’s fast and Windows Phone 8 just flows.

The Surface RT is not an iPad competitior, it’s an extension of your computing space on a tablet. Working on my Surface is not much different than my desktop aside from form factor. Will I do all my work on it? No, but when I need to be mobile and agile, it’s the tool. Using SkyDrive everything is connected and available. I don’t need to think about where things are stored and what I need to do to make sure they are available. Open a Word or Excel doc, use OneNote, just about anything I need to do, I can do.

So far the only glitch seems to be that I sometimes have to restart to get the touchpad working. So far I haven’t figured out the pattern but I sense there is one. Since I use the screen keyboard most of the time, this is not an issue.

The Technologies

Windows 8 is different but I really like it. If you are not on a touch device, it takes some getting used to (see post on short-cuts coming soon). If you are on a touch device it takes about five minutes to get going and about two days to get the true hang of it. The Metro UI makes sense in a touch world. All of the gestures feel natural and unforced. The Live Tiles feature is visual candy but nice to have. So far, I’ve been running about a month and it is faster than Win7.

Windows 8 Phone flows. Best way to describe it. Everything makes sense and I don’t spend time looking for things that should be there. Only thing I wish it had is the ability to close an app. I know the OS is supposed to take care of that but it’s not perfect yet.

Windows Azure was my “Doh!” moment. I had really passed on it as a miss by Microsoft because I felt the learning curve and cost were going to be high. Wrong! I attended 1 session by Josh Twist and was up and running in nothing flat. More on this in a later post. Looking at the pricing structure, it seems very affordable.

JavaScript was described by Scott Hanselmann as an operating system. Five minutes into his talk and I agreed although it stills seems wrong somehow. JavaScript was everywhere. It’s used to script in Azure which reduces the learning curve tremendously.

That’s it for the overall. I’ll start posting on using the above soon.

Diffference Between RegEx.IsMatch and the RegularExpressionAttribute

noreply@blogger.com (Paul G Brown) — Mon, 21 Nov 2011 19:10:00 +0000

I was working on some code that stores a month as CCYYMM string. Of course I wanted to validate the string with Regex so I had the pattern

^20[0-2][0-9](0[1-9])|(1[0-2])$

Everything was fine as long as I was using Regex.IsMatch. The problems started when I used the RegularExpressionAttribute. Suddenly, I was getting errors everywhere. I constructed a little test to verify that there was a difference. What I found was that using the same string and pattern, yielded different results.

Here’s my unit test.

[TestMethod]
public void DifferenceBetweenIsMatchAndRegExAttribute()
{
  var pattern = "^20[0-2][0-9](0[1-9])|(1[0-2])$";
  int cnt = 0;
  var months = new string[]
  {
  "201001",
  "201002",
  "201003",
  "201004",
  "201005",
  "201006",
  "201007",
  "201008",
  "201009",
  "201010",
  "201011",
  "201012" };

  var attribute = new RegularExpressionAttribute(pattern);
  bool isMatchOk = false;
  bool isAttrOk = false;
 
  foreach (var month in months)
  {
    isMatchOk = System.Text.RegularExpressions.Regex.IsMatch(month, pattern);
    isAttrOk = attribute.IsValid(month);
 
    if (isMatchOk & isAttrOk)
    { cnt += 1; }
  }
 
  Assert.AreEqual(12, cnt);
}

You would expect the number of matches to be 12 but it’s actually 9. Why? Because the attribute is matching only the first half of our pattern. It stops at the pipe (|). To prove this, I flipped the sides of the pipe and got 3. If I add parens around both sides of the logical or I will get the answer I’m looking for.

^20[0-2][0-9]((0[1-9])|(1[0-2]))$

While it seems logical that the RegularExpressionAttribute would use Regex.IsMatch underneath, it apparently is not the case. I’m betting the reason has to do with JavaScript compatibility. Remember, the attribute must also validate client side.

Cheers!

Updated Code To Get Class Definition From Table Structure

noreply@blogger.com (Paul G Brown) — Tue, 26 Apr 2011 19:05:00 +0000

My previous post on Script to Create MetadataType classes was to work with LINQ-To-SQL and partial classes. EF 4.1 introduces Code First which uses POCOs and here is the code to create a class definition from a table’s structure.

Enjoy.

SET NOCOUNT ON
declare @TableName varchar(256) = 'BusinessTypes'
declare @EntityName varchar(256) = 'BusinessType'
declare @TableSchema varchar(256) = 'dbo'

declare @ColumnName varchar(256)
    , @DataType varchar(256)
    , @NewDataType varchar(256)
    , @MaxLength int
    , @Nullable varchar(5)
    
declare @Lines table (Line varchar(1000))

insert into @Lines select 'public class ' + @EntityName
insert into @Lines select '{'

declare @DataTypes table (SqlDataType varchar(1000), DataType varchar(1000))

insert into @DataTypes (SqlDataType, DataType) values ('bit', 'bool')
insert into @DataTypes (SqlDataType, DataType) values ('char', 'string')
insert into @DataTypes (SqlDataType, DataType) values ('datetime', 'DateTime')
insert into @DataTypes (SqlDataType, DataType) values ('decimal', 'decimal')
insert into @DataTypes (SqlDataType, DataType) values ('int', 'int')
insert into @DataTypes (SqlDataType, DataType) values ('money', 'decimal')
insert into @DataTypes (SqlDataType, DataType) values ('ntext', 'string')
insert into @DataTypes (SqlDataType, DataType) values ('nvarchar', 'string')
insert into @DataTypes (SqlDataType, DataType) values ('smalldatetime', 'DateTime')
insert into @DataTypes (SqlDataType, DataType) values ('timestamp', 'byte[]')
insert into @DataTypes (SqlDataType, DataType) values ('uniqueidentifier', 'Guid')
insert into @DataTypes (SqlDataType, DataType) values ('varchar', 'string')

declare cols cursor for 
    select COLUMN_NAME, Data_type, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
    from INFORMATION_SCHEMA.COLUMNS
    where table_Name = @TableName and table_schema = @Tableschema
    order by ORDINAL_POSITION

open cols

fetch next from cols into  @ColumnName, @DataType, @MaxLength, @Nullable
while @@FETCH_STATUS = 0
begin
    select @NewDataType = DataType from @DataTypes where SqlDataType = @DataType
    
    if @DataType in ('varchar', 'char') and @Nullable = 'NO'
        insert into @Lines select char(9) + '[Required]'
        
    if @DataType in ('varchar', 'char')
        insert into @Lines select char(9) + '[StringLength(' + CAST(@MaxLength as varchar) + ')]'
    
    insert into @Lines select char(9) + 'public ' + @NewDataType + ' ' + @ColumnName + ' { get; set; }'
    
    insert into @Lines select char(9) + ''
    
    fetch next from cols into  @ColumnName, @DataType, @MaxLength, @Nullable
end

close cols
deallocate cols
insert into @Lines select '}'

select * FROM @Lines

SET NOCOUNT OFF

Replacement for ExpectedException in MS Test

noreply@blogger.com (Paul G Brown) — Tue, 01 Mar 2011 15:02:00 +0000

This StackOverflow thread has a great solution for the issue that ExpectedException does not allow you to validate the error message. Look at the response from winSharp93 and the ExceptionAssert class. This is a much cleaner method and also shows some great ways to use generics.

Script To Grab Column Names As UML Properties

noreply@blogger.com (Paul G Brown) — Fri, 18 Feb 2011 16:50:00 +0000

I was cooking up some UML diagrams for an existing DB and wanted to get all of the properties. It lists the table, column name in UML markup format and then the data type. The data type column is there so that all I have to do is order it by the second column and I’ll get all of the nulls so I can see if I’m getting a data type that I haven’t handled. The data type conversion is by no means complete but it’s a good start.

Enjoy!

select table_name, '+ ' + column_name + ' : ' +
    CASE WHEN DATA_TYPE in ('ntext', 'nvarchar', 'varchar', 'char') then 'string'
        WHEN DATA_TYPE = 'bit' then 'bool'
        WHEN DATA_TYPE = 'int' then 'int'
        WHEN DATA_TYPE = 'timestamp' then 'timestamp'
        WHEN DATA_TYPE = 'uniqueidentifier' then 'Guid'
        WHEN DATA_TYPE = 'money' then 'decimal'
        WHEN DATA_TYPE in ('smalldatetime', 'datetime') then 'datetime'
        END,
    DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME not like '%aspnet%'
    and TABLE_NAME <> 'sysdiagrams'
order by 1, 2

SQL To Find String In Procedure/Function

noreply@blogger.com (Paul G Brown) — Mon, 10 Jan 2011 15:14:00 +0000

I do a lot of work updating legacy systems. Almost every system needs the column names updated since the names are usually short and not very descriptive. Here’s a command for finding all store procedures/functions that use a specified column.

select s.name, a.name FROM sys.sql_modules m
inner join sys.all_objects a on m.object_id = a.object_id
inner join sys.schemas s on a.schema_id = s.schema_id
where m.definition like '%MyColumnName%'

INFORMATION_SCHEMA.ROUTINES is unreliable because it is limited to the first 4000 characters.

Enjoy!!

Read XML File Into SQL Server

noreply@blogger.com (Paul G Brown) — Mon, 03 Jan 2011 15:49:00 +0000

Wayne Sheffield has a nice article on reading XML files into sql. You can find it here.

User Story Screen

noreply@blogger.com (Paul G Brown) — Thu, 28 Oct 2010 15:08:00 +0000

The user story screen is the starting point for all work. It tells a story from the user perspective that we can use to determine what work needs to be done. This story should be short so that the work can be done in a couple of days. More complicated stories should be broken up into smaller stories so the work can be broken into pieces. My rule of thumb is if the story is takes more than a paragraph or so, you need to break it up.

Here’s the User Story screen from VS.

Title – A one sentence description of the story.

Assigned To – The single individual responsible for the story.

State – Indicates the current state of the story.

Active – The story is currently being worked on or waiting to be worked on.
Closed – The story was closed without being resolved.
Resolved – The story was completed and implemented.

Reason – The reason the item is in the current state. Matching states are in parenthesis.

New – A new story (Active).
Abandoned – The story was no longer relevant (Closed).
Out Of Scope – The story does not fall within the scope of the current project (Closed).
Rejected – The story was not desired by management, too complex or violates system integrity (Closed).
Code complete and unit tests pass – Work has been completed and all unit tests pass (Resolved).

Area – The functional area of the application such as clients, services, etc.

Iteration – The iteration of the system where the task will be worked on. This may correspond to sprints but it could be version number based.

Stack Rank – The relative importance of the item to the product owner(s).

System/process must support this user story / functionality
System/process should support this user story / functionality
System/process could support this user story / functionality

Story Points – An indicator of the complexity of the user story. This value allows for comparison between stories based on an abstract value rather than time (which is not the same between developers).

Risk – The level of risk to the project if the item is not completed.

A Simple Man’s Guide To Using VS & TFS 2010

noreply@blogger.com (Paul G Brown) — Thu, 28 Oct 2010 14:34:00 +0000

One of the areas I personally struggle with is planning and organizing. I’m a doer. Git-r-dun! I can see a task and wiz through coding it. As long as I’m the only developer, this works great. When you’re the team lead, it’s not a good thing. What ends up happening is that everything is in your head. The team doesn’t know what to work on and the users don’t know the status of the project.

Want to know if you’re in this category? Take a day off and see what happens! If you come back and find that things have gone haywire, welcome to the club.

So what’s the solution? For me, learning Agile and Scrum basics and using VS/TFS 2010 to keep track of stuff. For the next several posts, I ‘m going to document how we’re going to use VS/TFS 2010. If you’ve done it differently, please let me know. I’ll update this post as the master to keep track of everything together.

The first step will be to create a user story for each discrete activity. Then we’ll write the test cases that insure things are working correctly. Finally, we’ll create the tasks for the actual coding. The following links cover each of the items in VS/TFS 2010 and how we’ll be using them.

User Story

Test Case

Task

Bug

Quick Script to Create Constants from All Columns Names

noreply@blogger.com (Paul G Brown) — Mon, 28 Jun 2010 20:13:00 +0000

One thing I like to do in all my apps is create a static class that holds constants for all Column/Property names. This just avoids messing up names in places where I need to use strings. This is particularly useful when checking that attributes have been applied.

select 
     'public const string ' + COLUMN_NAME + ' = "' + COLUMN_NAME + '";'
from INFORMATION_SCHEMA.COLUMNS
where table_Name in ('Agencies', 'Customers')
group by COLUMN_NAME
order by COLUMN_NAME

The code produced looks like this:

public static class ColumnNames
{
    public const string AgencyName = "AgencyName";
    public const string CustomerName = "CustomerName";
}

I can then refer to the name using something like

VerifyPropertyHasAttribute(ColumnName.AgencyName, typeof(RequiredAttribute));

Script to Create MetadataType classes

noreply@blogger.com (Paul G Brown) — Mon, 28 Jun 2010 20:03:00 +0000

UPDATE: Here's the same thing for EF 4.1 and Code First.

I’ve been using the DataAnnotations for a validation with MVC2 and Silverlight 4. They both rely on metadata (buddy) classes. The Entity Framework now will create metaclasses for you but LinqToSql didn’t get this feature.

This short script will create a partial class and a metaclass for the specified table.

declare @TableName varchar(256) = 'Agencies'
declare @EntityName varchar(256) = 'Agency'
declare @TableSchema varchar(256) = 'dbo'

declare @ColumnName varchar(256)
    , @DataType varchar(256)
    , @MaxLength int
    , @Nullable varchar(5)
    
declare @Lines table (Line varchar(1000))

insert into @Lines select 'partial class ' + @EntityName
insert into @Lines select '{'
insert into @Lines select '}'
insert into @Lines select 'public class ' + @EntityName + 'MetaClass'
insert into @Lines select '{'

declare cols cursor for 
    select COLUMN_NAME, Data_type, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
    from INFORMATION_SCHEMA.COLUMNS
    where table_Name = @TableName and table_schema = @Tableschema
    order by ORDINAL_POSITION

open cols

fetch next from cols into  @ColumnName, @DataType, @MaxLength, @Nullable
while @@FETCH_STATUS = 0
begin
    if @DataType in ('varchar', 'char') and @Nullable = 'NO'
        insert into @Lines select char(9) + '[Required]'
        
    if @DataType in ('varchar', 'char')
        insert into @Lines select char(9) + '[StringLength(' + CAST(@MaxLength as varchar) + ')]'
    
    insert into @Lines select char(9) + 'public object ' + @ColumnName + ' { get; set; }'
    
    insert into @Lines select char(9) + ''
    
    fetch next from cols into  @ColumnName, @DataType, @MaxLength, @Nullable
end

close cols
deallocate cols
insert into @Lines select '}'

select * FROM @Lines

Breaking? Change In Asp.Net MVC2

noreply@blogger.com (Paul G Brown) — Sun, 04 Apr 2010 03:19:00 +0000

In MVC1, by default an empty textbox was sent as string.Empty to the controller on POST. In MVC2, it now sends the value as NULL. See ModelMetadata.ConvertEmptyStringToNull. I think there are some valid reasons for why they made the change – see Brad Wilson’s comment for more. If your system relies on string.Empty, you’ve got a problem.

There are several options for handling this. The first is using the DisplayFormatAttribute on every property that needs it. Painful. Here’s an example:

[DisplayFormat(ConvertEmptyStringToNull=false)]
public string FirstName { get; set; }

You can also write a custom binder that sets this value to false. Like so:

public class CustomStringModelBinder : DefaultModelBinder 
{
    public override object BindModel(ControllerContext controllerContext, ModelBindingContext bindingContext)
    {

        var value = bindingContext.ValueProvider.GetValue(bindingContext.ModelName);   
        if (bindingContext.ModelType == typeof(string))
        {   
            bindingContext.ModelMetadata.ConvertEmptyStringToNull = false;
            if (value == null || string.IsNullOrEmpty(value.AttemptedValue))   
                return "";
        }
        return value.AttemptedValue;
    }
}

You’ll need to add the following to the Global.asax Application_Start method to use your custom model binder.

ModelBinders.Binders.Add(typeof(string), new CustomStringModelBinder());

One thing to note, if you use the RequiredAttribute, you don’t need to worry about the null because it will invalidate the model based on the NULL.

Which option is better? Neither. Simply pick the default for ConvertEmptyStringToNull and then use DisplayFormat to handle the exceptions. Not perfect but not bad.

I do wish that this was configurable in some way instead of creating a ModelBinder.

Navigation Using Areas in Asp.Net MVC

noreply@blogger.com (Paul G Brown) — Thu, 01 Apr 2010 12:54:00 +0000

Asp.Net MVC2 introduced Areas as a feature part of the framework. Areas allow you to divide your application into logical units. It also makes your Url’s look more accurate.

Thinking of a generic store they might have areas such as Inventory, Payroll, Employees and Sales. Here are example ActionLinks for each area.

<%= Html.ActionLink("Home", "Index", "Home", new { area = "" }, new { })%>
<%= Html.ActionLink("Payroll", "Index", "Main", new { area = "Payroll" }, new { })%>
<%= Html.ActionLink("Inventory", "Index", "Main", new { area = "Inventory" }, new { })%>
<%= Html.ActionLink("Employees", "Index", "Main", new { area = "Employees" }, new { })%>
<%= Html.ActionLink("Sales", "Index", "Main", new { area = "Sales" }, new { })%>

Notice that the area is passed as a route value. I wrote my own overload for the ActionLink that takes the Area as an argument.

public static MvcHtmlString ActionLink(this HtmlHelper helper, string linkText, string actionName, string controllerName, string areaName, object routeValues, object htmlAttributes)
{
    RouteValueDictionary routes = new RouteValueDictionary(routeValues);
    RouteValueDictionary attributes = new RouteValueDictionary(htmlAttributes);
    routes.Add("area", areaName);
    return helper.ActionLink(linkText, actionName, controllerName, routes, attributes);
}

This makes our links look as follows:

<%= Html.ActionLink("Home", "Index", "Home", "", new { }, new { })%>
<%= Html.ActionLink("Payroll", "Index", "Main", "Payroll", new { }, new { })%>
<%= Html.ActionLink("Inventory", "Index", "Main", "Inventory", new { }, new { })%>
<%= Html.ActionLink("Employees", "Index", "Main", "Employees", new { }, new { })%>
<%= Html.ActionLink("Sales", "Index", "Main", "Sales", new { }, new { })%>

If you haven’t worked with Areas yet, your probably looking at the routes and noticing that each area link has a controller called Main with an Index method. You might think we’re calling the same controller and passing the area name as parameter. That is not what we are doing, each area has a controller named Main. Maybe the generated links will help.

http://somedomain.com/Payroll/Main

http://somedomain.com/Inventory/Main

http://somedomain.com/Employees/Main

http://somedomain.com/Sales/Main

Next thing you should notice is that the link for returning to the Home page has an empty area. We need this so that when we’re in an area we can move back up to the root. Otherwise, we’ll get a Resource Not Found error indicating showing that it’s looking for Home in that Area. This can also work in your favor. If you have a Home controller (or Main) in all of your areas, MVC will, by default, navigate within the Area and you don’t need to change your Site.Master.

Overall, areas are a great feature for keeping your applications organized.

Lose your Find / Replace window?

noreply@blogger.com (Paul G Brown) — Fri, 19 Mar 2010 17:53:00 +0000

Working in VS2010, I suddenly lost my Find /Replace window. I found this post from Rick Strahl but unfortunately that doesn’t work for me since ALT-M is mapped as a menu shortcut.

Solution: Window –> Dock.

This will dock the window and then you can move it to your desired location.

Cheers!

Visual Studio/SQL Server Management Studio Find and Replace with Regular Expressions

noreply@blogger.com (Paul G Brown) — Fri, 12 Mar 2010 17:09:00 +0000

I needed to format a bunch of strings in SQL Management Studio (this works for VS also) and finally took the time to figure out Find/Replace with Regular Expressions.

The first thing to note is that VS/SSMS use different regex codes (don’t know why but I’m sure they have their reasons). You can find the list of codes here.

I have list of 3 strings containing 10 digits followed by a space and then 3 letters. I wanted to replace the digits with the word Descriptor.

Here’s my data:

1234567870 ABC
1234567880 DEF
1234567890 GHI

Here’s my find expression:

[0-9]^10 {[A-Z]^3}

Here’s my replace expression:

Descriptor: \1

Here’s my result:

Descriptor: ABC
Descriptor: DEF
Descriptor: GHI

The key is the {} brackets and \1. The {} brackets tell the engine I want to keep whatever matches the expression. This is called a Tagged Expression. The \1 says place the first tagged expression here. I can create multiple tagged expressions and place by using \1, \2, etc. I can even change the order in the result because they are numbered based on their order in the original expression. You can use newlines (\n) so that you can break lines apart).

This is great for creating some testing stuff or moving non-standard data.

Sweet!

Asp.Net MVC, Forms and Missing Values

noreply@blogger.com (Paul G Brown) — Tue, 22 Dec 2009 16:00:00 +0000

Remember, Asp.Net requires that you use the Name and not just ID on your form controls. If you don’t, they don’t come along for the ride to your controller.

RedBox “Verify Your Subscription” Emails

noreply@blogger.com (Paul G Brown) — Fri, 04 Dec 2009 16:01:00 +0000

Anytime I get an unsolicited email saying “verify” something, I’m suspicious. I’ve gotten three of these emails lately. Checking the links, they seem to be genuine. I also found this on Facebook, that seems to indicate they are real. Did notice that lady hadn’t been able to use her code.

Think I’ll give it a try and see what happens.

Cache and Compress in Asp.Net MVC

noreply@blogger.com (Paul G Brown) — Mon, 23 Nov 2009 00:56:00 +0000

Found a good entry on caching at the user browser and compressing the response. Note: It’s from some early beta stuff so be sure to change FilterExecutingConext to ActionExecutingContext. More on that here.

Enjoy!