SQL Script to Get Row Count of All Tables in Database
Posted: May 28, 2010 Filed under: SQL | Tags: script, SQL 1 Comment »There is an undocumented built-in stored procedure than iterates over all tables in a database and executes a SQL script "sp_msforeachtable" which makes the job pretty straightforward:
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
OAuth for Dummies
Posted: October 6, 2009 Filed under: Code, OAuth | Tags: OAuth Leave a comment »Having developed a little feature where I had to publish a Tweet to uh… Twitter, I did a checkup on how OAuth actually works. If you have the time you can go through Beginner’s Guide to OAuth, which is really excellent and was my source or introduction to OAuth. I’ll just condense it a little further, mostly because it is a good way to actually “get it”.
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.
- The Consumer contacts the Service Provider, requesting a Request Token and a Request Secret.
- The User (e.g. his/her browser) is then redirected to the Service Provider presenting the Request Token.
- 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.
- The Service Provider generates an Access Token and an Access Secret.
- The User (e.g. his/her browser) is then redirected to the Consumer, presenting the Access Token (the Access Secret is kept um… secret).
- The Consumer now takes the Access Token and the Request Secret and asks the Service Provider for the Access Secret.
- 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();
}
ISO 8601/RFC 3339 Compatible Dates
Posted: October 4, 2009 Filed under: .NET General, Code | Tags: Dates, Extension Methods, ISO, RFC Leave a comment »There are not standard format specifiers for date that takes a date and converts it to an ISO 8601 or RFC 3339 compatible date. Here are two extension methods that does just that.
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");
}
Generating a Slug From a String
Posted: October 2, 2009 Filed under: .NET General, Code | Tags: Extension Methods, Slug, Unicode, url Leave a comment »I can’t (and won’t) take full credit for this extension method. The hardcore Unicode stuff is from Michael Kaplan’s blog (jeez, he is hardcore). There is a little danish “stuff” included, for special characters æ, ø and å, which can also be written “ae”, “oe” an “aa”.
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();
}
ASP.NET MVC Gravatar HtmlHelper
Posted: September 30, 2009 Filed under: ASP.NET MVC, Code | Tags: ASP.NET MVC, Extension Methods, Gravatar 1 Comment »Here is a quick little URL Helper extension method to add Gravatar images/photos/avatars to your page:
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.
MD5 Extension Method
Posted: September 29, 2009 Filed under: .NET General, Code | Tags: Extension Methods, hash, md5, Secure Hash 1 Comment »A nifty little extension method that generated the MD5/SHA-1 hash of a string. These methods are a continuation of my previous 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.
Relative Time Description
Posted: September 28, 2009 Filed under: .NET General, Code | Tags: Dates, Extension Methods Leave a comment »Showing an absolute date on a webpage, e.g. January 22, 2009 13:01, is of course a very normal way to do it, apart from various different date formats, it is easily read and consumed. However if the date is shown in a context where the absolute date is of little importance, but it is more important to know if it was a long time ago, just now or maybe just in a couple of minutes, a relative and more descriptive method is better.
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.