Code: Impossible

I’ve wrapped up work on v0.4 of the Jsoq Console, and the insanely strenuous release cycle for v0.1 of WhatDidIJustEat.com so I’m starting another side project tonight.

Ever been working with a programming language you’re not 100% familiar with and find yourself wondering:

Is this function a built-in function or in some included library?
What assembly is this class in again?
Can I name my class XXXX without conflicting with another class?

I’ve had these questions recently and found myself annoyed and frustrated to no end (PHP, I’m looking at you) so I’ve decided to build a system to keep track of this stuff for me :D.

The first thing on my list (because it was the easiest) was to do a data dump of all the public classes in the .Net Framework, including the ones in my GAC, and store some metadata for each one in a database table. I’ve just finished this step and thought that this data might be useful, so I’m posting it here.

Currently the fields included in the data dump are:


[namespace] = The namespace that the class exists in (ex: System.Collections.Generic)
[class_name] = The name of the class (ex: StringBuilder)
[assembly_fullname] = The display name of the assembly (ex: mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089)
[assembly_file] = The full file path to the assembly (ex: C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll)
[framework_name] = The framework that uses this class, for this dump they will all be 'Microsoft .Net'
[framework_version] = The framework version that uses this class (ex: v.2.0.50727)


The .sql file takes about 18 seconds to run to completion on my AMD Athlon X2 2.53ghz machine with 4gb of RAM.

If you’re curious about how I generated the .sql file, below is the code I used to find all the classes. It’s not pretty but then again it was just meant to get the data into the database. Just paste the code into a new console app and run it in a command window like so:

[application_name].exe > c:\classes.sql

Or, you can download the .sql file I’ve generated (it will also create the table you need to store the data).

static void Main(string[] args)
{
	var dictionary = new Dictionary(){
		{ "v2.0.50727", @"C:\Windows\Microsoft.NET\Framework\v2.0.50727" },
		{ "v3.0", @"C:\Windows\Microsoft.NET\Framework\v3.0" },
		{ "v3.5", @"C:\Windows\Microsoft.NET\Framework\v3.5" },
		{ "v???", @"c:\windows\assembly\gac" }
	};

	var types = new List();

	foreach (var pair in dictionary)
	{
		var path = pair.Value;

		var assemblies = Directory.GetFiles(path, "*.dll", SearchOption.AllDirectories);

		foreach (string file in assemblies)
		{
			try
			{
				Assembly asm = Assembly.LoadFile(file);

				foreach (Type t in asm.GetTypes())
				{
					if (t.IsPublic)
					{
						Console.WriteLine(String.Format(
							"INSERT INTO Classes (" +
								"[namespace], " +
								"class_name, " +
								"assembly_fullname, " +
								"assembly_file, " +
								"framework_name, " +
								"framework_version" +
							") " +
							"VALUES (" +
								"'{0}', " +
								"'{1}', " +
								"'{2}', " +
								"'{3}', " +
								"'Microsoft .Net', " +
								"'{4}'" +
							")",
							String.IsNullOrEmpty(t.Namespace) ?
								"GLOBAL" :
								t.Namespace,
							t.Name
								.Replace("`1", "")
								.Replace("`2", "")
								.Replace("`3", ""),
							t.Assembly.FullName,
							t.Assembly.Location,
							pair.Key == "v???" ?
								t.Assembly.GetName().Version.ToString() :
								pair.Key
						));
					}
				}
			}
			catch { }
		}
	}
}

If you found this useful, let me know in the comments!

If you’re like me, you recently upgraded to Sql Server 2008 (or just installed the Management Studio). If you’re really like me you found the “Saving changes is not permitted” dialog that pops up every time you try to save a modified table annoying.

You may not have found it annoying enough to investigate but I sure did. I don’t remember Management Studio 2005 complaining when I changed a table and saved it. Why the change in 2008?

Well, anyway, here’s your chance to get rid of that dialog once and for all.

In Sql Management Studio, go to Tools -> Options, expand down the Designers node, and select Table and Database Designers. In the Table Options section uncheck Prevent saving changes that require table re-creation and you’re done!

You can now change your database tables hassle free!

I really like it when an application presents dates in a friendly “n Hours Ago” format. Mostly because I’m lazy and hate to do extra brain-work. John Resig created “pretty date” and it’s insanely useful. It also plugs into JQuery very easily.

But on the project I am working on it made more sense to provide this functionality within the database. So I created a pretty date function for SQL.

[sourcecode language="SQL"]
CREATE FUNCTION dbo.GetFriendlyDateTimeValue
(
@CompareDate DateTime
)
RETURNS nvarchar( 48 )
AS
BEGIN
DECLARE @Now DateTime
DECLARE @Hours int
DECLARE @Suff nvarchar(256)
DECLARE @Found bit

SET @Found = 0
SET @Now = getDate()
SET @Hours = DATEDIFF(MI, @CompareDate, @Now)/60

IF @Hours <= 1
BEGIN
SET @Suff = 'Just Now'
SET @Found = 1
RETURN @Suff
END

IF @Hours < 24
BEGIN
SET @Suff = ' Hours Ago'
SET @Found = 1
END

IF @Hours >= 8760 AND @Found = 0
BEGIN
SET @Hours = @Hours / 8760
SET @Suff = ‘ Years Ago’
SET @Found = 1
END

IF @Hours >= 720 AND @Found = 0
BEGIN
SET @Hours = @Hours / 720
SET @Suff = ‘ Months Ago’
SET @Found = 1
END

IF @Hours >= 168 AND @Found = 0
BEGIN
SET @Hours = @Hours / 168
SET @Suff = ‘ Weeks Ago’
SET @Found = 1
END

IF @Hours >= 24 AND @Found = 0
BEGIN
SET @Hours = @Hours / 24
SET @Suff = ‘ Days Ago’
SET @Found = 1
END

RETURN Convert(nvarchar, @Hours) + @Suff

END
[/sourcecode]

Hey this is just a reminder to everyone, don’t forget that the SQL 2008 Virtual Event is open for registration. You can do so over at http://events.unisfair.com/rt/sql~jan08/?code=solidqweb .

Topics include:

• Developing Data-Centric Applications with SQL Server 2008
• Getting a Handle on Data Cleansing in SSIS
• Performance Tuning
• Reporting Services with SQL Server 2008
• SQL Server Virtualization

The even starts on Jan 24th and runs from 11am to 4:15pm EST. Register now!