sp_msforeachtable "SELECT '?', COUNT(*) FROM ?"

Another nifty little sproc to use is the sp_spaceused which will besides row count return reserved size, data size, index size and unused size of a given table. Combine the two and you get a pretty nice list:

CREATE TABLE #TempTable
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)
GO

EXEC sp_msforeachtable "INSERT #TempTable EXEC sp_spaceused '?'"
GO

SELECT * FROM #TempTable

DROP TABLE #TempTable
GO

What is OAuth?

OAUth makes it possible for providers to communicate in a secure and authenticated manor, without needing to share user secrets (username/password). For example it can allow the users of my web application to post Tweets to their Twitter account without needing to specify their username and password (except if the need to login to the Twitter site).

Who is involved?

Just to set the scene and get into the terms used, the actors involved are:

We want to get access to the Service Provider, e.g. Twitter.

The Consumer would like to access a resource on the Service Provider, e.g. my application.

The User want to access a resource on the Service Provider via the Consumer, e.g. post a Tweet using my application.

How does it work?

Seen from a larger perspective the idea is that the 3 actors (the Service Provider, the Consumer and the User) communicate with each other in such a way that all agree on who is who.

In more technical terms, all 3 actors generates and present various tokens (share secrets) to each other, which when combined establishes a trusted relationship between the Consumer and the Service Provider on behalf of the User.

Step-by-Step

A User want to perform an action on the Consumer which requires access to the/a protected resource on the Service Provider, e.g. create a Tweet.

1. The Consumer contacts the Service Provider, requesting a Request Token and a Request Secret.
2. The User (e.g. his/her browser) is then redirected to the Service Provider presenting the Request Token.
3. The Service Provider validates the user and request approval from the User that the Consumer (found via the Request Token) can access the User’s protected resource.
4. The Service Provider generates an Access Token and an Access Secret.
5. The User (e.g. his/her browser) is then redirected to the Consumer, presenting the Access Token (the Access Secret is kept um… secret).
6. The Consumer now takes the Access Token and the Request Secret and asks the Service Provider for the Access Secret.
7. Once the Consumer has the Access Token and the Access Secret, it can access the protected resource.

The point of presenting the Request Secret to the Service Provider in step 6 is so that replays cannot be performed using the Access Token alone.

The Tokens are used to communicate between the Consumer and the Service Provider via the User (his/her browser). The Secrets are kept between the Consumer and the Service Provider.

Now what?

Once the relationship is in place, the “real” communication can take place. This is where it gets hairy and where we’ll stop for this dummy introduction.

However during my implementation I found a little problem with the way .NET UrlEncode works – it is simply not compatible with OAuth. I found an implementation by Andrew Arnott, who is OAuth/OpenID aficionado and author of DotNetOpenAuth that does this and is RFC 3986 compliant (it basically used the UrlEncode from .NET and then fixes what is broken):

/// <summary>

/// The set of characters that are unreserved in RFC 2396 but are NOT unreserved in RFC 3986.

/// </summary>

private static readonly string[] UriRfc3986CharsToEscape = new[] { "!", "*", "'", "(", ")" };


/// <summary>

/// Escapes a string according to the URI data string rules given in RFC 3986.

/// </summary>

/// <param name="value">The value to escape.</param>

/// <returns>The escaped value.</returns>

/// <remarks>

/// The <see cref="Uri.EscapeDataString"/> method is <i>supposed</i> to take on

/// RFC 3986 behavior if certain elements are present in a .config file.  Even if this

/// actually worked (which in my experiments it <i>doesn't</i>), we can't rely on every

/// host actually having this configuration element present.

/// </remarks>

public static string UrlEncodeRfc3986(this string value)

{

    // Start with RFC 2396 escaping by calling the .NET method to do the work.

    // This MAY sometimes exhibit RFC 3986 behavior (according to the documentation).

    // If it does, the escaping we do that follows it will be a no-op since the

    // characters we search for to replace can't possibly exist in the string.

    var escaped = new StringBuilder(Uri.EscapeDataString(value));


    // Upgrade the escaping to RFC 3986, if necessary.

    for (int i = 0; i < UriRfc3986CharsToEscape.Length; i++)

    {

        escaped.Replace(UriRfc3986CharsToEscape[i], Uri.HexEscape(UriRfc3986CharsToEscape[i][0]));

    }


    // Return the fully-RFC3986-escaped string.

    return escaped.ToString();

}

public static string ToISO8601(this DateTime date)

{

    return date.ToString("yyyy-MM-dd");

}


public static string ToRFC3339(this DateTime date)

{

    return date.ToUniversalTime().ToString("yyyy-MM-ddThh:mm:ssZ");

}

public static string ToSlug(this string message)

{

    // replace space with -

    message = Regex.Replace(message, @"[\s/\\\.,+|_]+", "-");

    // normalize the message 

    message = message.Normalize(NormalizationForm.FormD);

    message = message.Replace("ø", "oe").Replace("Ø", "Oe").Replace("æ", "ae").Replace("Æ", "Ae").Replace("å", "aa").Replace("Å", "Aa");

    StringBuilder result = new StringBuilder();

    for (int i = 0; i < message.Length; i++)

    {

        UnicodeCategory uc = CharUnicodeInfo.GetUnicodeCategory(message[i]);

        if (uc != UnicodeCategory.NonSpacingMark)

        {

            result.Append(message[i]);

        }

    }

    return Regex.Replace(result.ToString().Normalize(NormalizationForm.FormC), @"[^a-zA-Z0-9\-]", "").ToLower();

}

public static string GravatarUrl(this UrlHelper url, string email, int size)

{

    string imageUrl = ConfigurationManager.AppSettings["DefaultGravatar"];

    if (imageUrl.StartsWith("~/"))

    {

        imageUrl = url.Absolute(imageUrl);

    }


    if (string.IsNullOrEmpty(email))

    {

        return imageUrl;

    }


    string md5 = email.ToLowerInvariant().MD5();

    return string.Format(

        "http://www.gravatar.com/avatar/{0}.jpg?d={1}&s={2}&r=g",

        md5.ToLowerInvariant(),

        url.Encode(imageUrl),

        size);

}


public static string GravatarUrl(this UrlHelper url, string email)

{

    return url.GravatarUrl(email, 32);

}

It uses another custom URL Helper extension method Absolute to generate an absolute URL to a default image (based on an application relative url).

The Absolute extension method is similar to the ActionAbsolute method I posted earlier:

public static string Absolute(this UrlHelper url, string contentUrl)

{

    return new Uri(GetBaseUrl(url), url.Content(contentUrl))

        .AbsoluteUri;

}

And it also used the MD5 extension method from yesterdays post.

public static string MD5(this string value)

{

    System.Security.Cryptography.MD5 algorithm =

        System.Security.Cryptography.MD5.Create();

    byte[] data = Encoding.ASCII.GetBytes(value);

    data = algorithm.ComputeHash(data);

    string md5 = "";

    for (int i = 0; i < data.Length; i++)

    {

        md5 += data[i].ToString("x2").ToLower();

    }

    return md5;

}

Just substitute MD5 for SHA1, SHA256, SHA384, SHA512 or RIPEMD160 which ever hash algorithm fits your needs.

For example the date mentioned above might be described as “8 months ago”. Just by giving it a quick glance you get an idea about when this event occurred.

For this here are a couple of extension methods that generate this descriptive text based on a given time:

public static string ToRelativeTime(this DateTime from)

{

    DateTime now = DateTime.Now;

    return from.ToRelativeTime(now);

}


public static string ToRelativeTime(this DateTime from, bool usePreAndSuffix)

{

    DateTime now = DateTime.Now;

    return from.ToRelativeTime(now, usePreAndSuffix);

}


public static string ToRelativeTime(this DateTime from, DateTime to)

{

    return from.ToRelativeTime(to, true);

}


public static string ToRelativeTime(this DateTime from, DateTime to, bool usePreAndSuffix)

{

    string prefix = usePreAndSuffix && (from > to) ? "in " : string.Empty;

    string suffix = !usePreAndSuffix || (from > to) ? string.Empty : " ago";

    // is more than 1 year?


    if (from > to)

    {

        DateTime d = from;

        from = to;

        to = d;

    }


    int years = to.Year - from.Year;

    if ((to.Month < from.Month) || ((to.Month == from.Month) && (to.Day < from.Day)))

    {

        years--;

    }


    if (years > 1)

    {

        return string.Format("{0}{1} years{2}", prefix, years, suffix);

    }

    else if (years == 1)

    {

        return string.Format("{0}1 year{1}", prefix, suffix);

    }


    // less than 1 year, is more than 1 month?


    int months = to.Month - from.Month;

    if (months < 0)

    {

        months += 12;

    }

    if ((to.Day < from.Day) || ((to.Day == from.Day) && (to.TimeOfDay < from.TimeOfDay)))

    {

        months--;

    }


    if (months > 1)

    {

        return string.Format("{0}{1} months{2}", prefix, months, suffix);

    }

    else if (months == 1)

    {

        return string.Format("{0}1 month{1}", prefix, suffix);

    }


    // less than 1 month, is more than 1 day/week?


    TimeSpan diff = to - from;


    if (diff.Days > 7)

    {

        return string.Format("{0}{1} weeks{2}", prefix, diff.Days / 7, suffix);

    }

    else if (diff.Days > 1)

    {

        return string.Format("{0}{1} days{2}", prefix, diff.Days, suffix);

    }

    else if (diff.Days == 1)

    {

        return string.Format("{0}1 day{1}", prefix, suffix);

    }


    // less than 1 day, is more than 1 hour?


    if (diff.Hours > 1)

    {

        return string.Format("{0}{1} hours{2}", prefix, diff.Hours, suffix);

    }

    else if (diff.Hours == 1)

    {

        return string.Format("{0}1 hour{1}", prefix, suffix);

    }


    // less than 1 hour, is more than 1 minute?


    if (diff.Minutes > 1)

    {

        return string.Format("{0}{1} minutes{2}", prefix, diff.Minutes, suffix);

    }

    else if (diff.Minutes == 1)

    {

        return string.Format("{0}1 minute{1}", prefix, suffix);

    }


    // less than 1 minute


    if (diff.Seconds == 1)

    {

        return string.Format("{0}1 second{1}", prefix, suffix);

    }

    else if (diff.Seconds < 1)

    {

        return string.Format("{0}less than 1 second{1}", prefix, suffix);

    }


    return string.Format("{0}{1} seconds{2}", prefix, diff.Seconds, suffix);

}

This can of course be optimized for localization etc.

Especially the quote:

They made a good work teaching us about how evil Datasets were for interoperability with other platforms, and now they came up with a solution like this, no way.

Priceless…

On the topic from Cibrax’ post, I could not agree more, I am a strong believer in the explicitness of service design and the WCF “opt-in” approach. How else would you know what is on the wire!? Similar to how you would choose ASP.NET MVC over classic ASP.NET WebForms if you want complete control of your HTML. Similar even to WCF vs. ASMX (on a high level).