Tuesday, March 14, 2017

Find All Characters In A Column

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

    ColChar CHAR(1),
    Instances int

WHILE @Iterator < @MaxLength
    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

FROM #AllChars
WHERE Instances > 0
ORDER BY colchar


Sunday, June 12, 2016

Back To Basics - Validating a date

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.

  1. Date of birth must be between the current date minus 65 years and the current date minus 21 years.
  2. The date test was ordered must be between the date of birth and the current date.
  3. 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.

Wednesday, May 20, 2015

VS Removes Usings and Can't Find Types

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.


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.

Monday, February 25, 2013

JavaScript runtime error: '$' is undefined

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!!");

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

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

Saturday, February 23, 2013

First NuGet Public Package: States & Provinces

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>
public enum CountrySelection
    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))

    if (selection.HasFlag(CountrySelection.UnitedStates))

    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.

Friday, November 16, 2012

Migrating Legacy Apps to the New SimpleMembership Provider

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.


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.

public class webpages_Membership
    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)
        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)
        M.IsApproved as IsConfirmed, 
    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;


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


        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.

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.

Thursday, November 8, 2012

Windows 8 Keyboard Shortcuts

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).

Windows Settings Bar Windows Charms Bar

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.