I came across a problem whereby I wanted to retrieve the table and column names of all the foreign keys in SQL Server. The script I ended up using was fairly straight forward, but I thought it might be useful to someone else so I thought I should share it:

SELECT 
	OBJECT_SCHEMA_NAME(fk.parent_object_id) ParentTableSchema, 
	OBJECT_NAME(fk.parent_object_id) ParentTableName,
	COL_NAME(fk.parent_object_id, fkc.parent_column_id) ParentTableColumn, 
	OBJECT_SCHEMA_NAME(fk.referenced_object_id) ReferencedTableSchema, 
	OBJECT_NAME(fk.referenced_object_id) ReferencedTableName,
	COL_NAME(fk.referenced_object_id, fkc.referenced_column_id) ReferencedTableColumn
FROM SYS.foreign_keys FK
JOIN SYS.foreign_key_columns FKC ON FK.object_id = FKC.constraint_object_id

Happy scripting!

I bought a Motorola Moto G phone and coming from a Samsung SIII, I was used to a little widget that easily and quickly turned on the flash of the phone so I could use it as a torch / flash light.

Unfortunately the Motorola didn't have such a widget built in, and the widgets / apps in the app store either didn't work or opened a screen when you tried to turn on the flash light, so I decided to write my own one.

The source code for this project can be found on Github here: 

https://github.com/heathesh/AndroidFlashLightWidget

The app can be installed from the Google Play Store here: 

https://play.google.com/store/apps/details?id=co.za.MobileDev.FlashLightWidget

The requirement was simple, count the number of lines of code in an ASP.Net Web Application including the number of HTML lines. To fulfil this requirement I thought I would whip up a quick console app.

How it works is pretty straight forward:

1. Get the list of files it needs to do the counts for
2. Make sure the list of files only includes the relevant file types
3. Make sure the list skips the ASP.Net generated file types
4. Skip folders if and when required
5. Make sure it only counted lines of code and not comments and regions
6. Generate a CSV file called "output.csv" with the relevant details

All the settings should be self explanatory and found in the app.config file in the project. I've included the source for the app in a zip file and saved it here:

http://heathesh.com/ftp/LinesOfCodeCounter.zip

Happy Counting!

Here's the proc I created and below is the explanation of how I went about doing this: http://heathesh.com/code/sql/GenerateEnumClass.txt

I'm sure every c# developer has come across a situation where they have a lookup table that they'd like to convert into an enum in code. I've actually had to do it a few times myself and each time I use some sort of cursor and t-sql to create the enum class code. So I figured why not create a procedure to do this that I could re-use?

To begin with, there's a few parameters I needed to pass into the procedure. These were:

1. The table name
2. The namespace I wanted of the code
3. The DataContract namespace. Because most of the stuff I do now is WCF this was a necessity but you can obviously remove this if you want.
4. The id field on the lookup table.
5. The value field on the lookup table.

I've attached the proc for you to copy (http://heathesh.com/code/sql/GenerateEnumClass.txt). The basics of how it works is that I insert the id and value fields into a temporary table using sp_executesql, then loop through the values using a cursor and simply use print statements to output the relevant code.

To use the proc I created a simple table called Title with an Id and a Name column like so:

CREATE TABLE [dbo].[Title](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Title] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

I then populated the table with values like so:

INSERT INTO [Title] ([Name]) VALUES ('Mr')
INSERT INTO [Title] ([Name]) VALUES ('Mrs')
INSERT INTO [Title] ([Name]) VALUES ('Miss')
INSERT INTO [Title] ([Name]) VALUES ('Dr')
INSERT INTO [Title] ([Name]) VALUES ('Prof')
INSERT INTO [Title] ([Name]) VALUES ('Sir')
INSERT INTO [Title] ([Name]) VALUES ('Madam')
INSERT INTO [Title] ([Name]) VALUES ('Mr And Mrs')

You'll notice one of the values has spaces in it. Since enum names cannot have spaces in it when you run the proc you'll notice in the output that it adds the Description attribute with the full name of the row, this is so you can still get the value of the string. If you need help with this check out a previous post of mine on reading the description attribute: Reading the DescriptionAttribute of enumerators ...

Then I simply ran my proc like so:

 [GenerateEnumClass] 
  'Title', -- this is the name of the lookup table
  'Heathesh.Com.Entity', -- this is the namespace I wanted to appear in the code
  'http://heathesh.com/entities', -- this is the data contract namespace I wanted to appear in the code
  'Id', -- this is the id field of the table
  'Name' -- this is the name field of the table

And this produced my required output like so:

using System;
using System.Runtime.Serialization;
using System.ComponentModel;
 
namespace Heathesh.Com.Entity
{
 /// <summary>
 /// Title enum
 /// </summary>
 [Serializable]
 [DataContract(Name = "Title", Namespace = "http://heathesh.com/entities")]
 public enum Title
 {
 [EnumMember]
 Mr = 1,
 [EnumMember]
 Mrs = 2,
 [EnumMember]
 Miss = 3,
 [EnumMember]
 Dr = 4,
 [EnumMember]
 Prof = 5,
 [EnumMember]
 Sir = 6,
 [EnumMember]
 Madam = 7,
 [Description("Mr And Mrs")]
 [EnumMember]
 MrAndMrs = 8,
 }
}

I simply then added a "Title.cs" class to my project, replaced all the code in the class with what was generated and that was it. An enum class generated using sql from a lookup table.

Please note: Don't forget to add a reference to "System.Runtime.Serialization" in your project for the WCF stuff (DataContract, DataMember, EnumMember etc.)

Happy generating!