adamkdean

software engineering

Sql threesomes.. Two foreign keys, one primary key

By Adam K Dean on

So here I am, coding my own business, being a law abiding coder, when out of the blue comes a rather bothersome little problem which really did one on me. I had laid out an awesome plan for my awesome top secret project, a truly awesomely smart database design with relationships to link a rows in a table to rows in the exact same table, one way relationships from A->B. All I needed was a way to cascade them on delete, in other words, make sure when a row is deleted, every relationship (in the relationship table) is deleted along with it.

Are things ever that simple? MSSQL decided to tell me that because one field had a foreign key (FK) to the primary key (PK), the other field couldn't also have an FK to the PK, and that's not okay (OK). That's !OK ..

Luckily, SQL has things called triggers, and they're actually pretty useful. The below SQL creates a trigger that deletes any rows from the second table, that have the same id as the row being deleted from the first table:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==============================================
-- Author: Adam K Dean
-- Website: http://imdsm.blogspot.com/
-- Create date: 12/09/10
-- Description: Deletes relationships on deletion
-- of row in the main database. Replaces the need
-- for two foreign keys on the same primary key
-- which is unfortunately impossible.
-- ==============================================
ALTER TRIGGER DeleteRelationships ON t_Sites AFTER DELETE
AS
BEGIN

 DELETE FROM t_Relationships 
 WHERE ReferrerId IN (SELECT Id FROM Deleted)
  OR TargetId IN (SELECT Id FROM Deleted)

END
GO

Somehow I managed to make this work, finding snippets and testing them, because lets face it, the SQL examples have so many options it's like a wall of text has just hit you at 150 pages per second, not really as easy to use as looking at some chinese hackers code and picking out the bits you need.

It seems the Deleted object refers to the deleted row, and well the rest is pretty simple.

Hope this helps you, or future me, if you're suffering the same Jeremy SQ-yle problems as I was!

Indexers - make your classes act like arrays

By Adam K Dean on

Indexers are great, I've only really started using them consciously today, but they're a great discovery I'll be sure to incorporate into more of my code from now on.

As you can see here, we have a class, but we access a value as if it was an array, or a Hashtable. Just think of how you could use this!

VarClass vc = new VarClass();
vc["key"] = "value";
Console.WriteLine(vc["key"].ToString());

The code is really simple too, it's simply like a property with an input, here we just put a layer on top of a Hashtable, you can of course add something more useful, but this shows you how it works:

class VarClass
{
    private Hashtable ht = new Hashtable();

    public object this[object key]
    {
        get
        {
            return ht[key];
        }
        set
        {
            ht[key] = value;
        }
    }
}  

As you can see, you take the object they put within the square brackets as a parameter, and you make a property but as the name you put the keyword this, referring to the actual class object.

I just think it's so simply, yet so awesome..

I actually came across this whilst writing a simple Hashtable clone for someone over at Dream.In.Code. For the sake of your dying curiosity I will post the awesome class here:

class HashtableEx
{
    private List<object> keys = new List<object>();
    private List<object> values = new List<object>();

    public object this[object key]
    {
        get
        {
            int index = keys.IndexOf(key);
            if (index == -1) return null;
            else return values[index];
        }
        set
        {
            if (keys.Contains(key))
            {
                int index = keys.IndexOf(key);
                values[index] = value;
            }
            else
            {
                keys.Add(key);
                values.Add(value);
            }
        }
    }

    public object Get(object key)
    {
        int index = keys.IndexOf(key);
        return values[index];
    }

    public void Add(object key, object value)
    {
        keys.Add(key);
        values.Add(value);
    }

    public void Remove(object key)
    {
        int index = keys.IndexOf(key);
        keys.RemoveAt(index);
        values.RemoveAt(index);
    }

    public void Clear()
    {
        keys = new List<object>();
        values = new List<object>();
    }
}

Use it like so:

static void Main()
{
    HashtableEx ht = new HashtableEx();

    ht["hello"] = "world";

    Console.WriteLine("Value of \"hello\": {0}", ht["hello"]);
    Console.WriteLine("Type of \"hello\": {0}", ht["hello"].GetType());

    Console.ReadKey();
}

Which gives you the output:

Value of "hello": world
Type of "hello": System.String

XML to Hashtable

By Adam K Dean on

XML is often used nowadays for many things, mainly due to it being so flexible, lightweight and easy to understand for humans. From configuration files to layouts, they all seem to be using it. With all the methods inside System.Xml you have everything you could ever need, unless of course you are, like me, addicted to generic lists and hashtables...

I don't like going through nodes, and well I don't really like children so seeing ChildNode in my code makes me cry inside. Today I present you with a way to convert those bothersome XmlNodes into something we can all understand, a hashtable. Or well, a hashtable of hashtables, with a few generic lists thrown in for good measure.

Let us look at the following XML, it's nice and simple.

You can find the XML file here: http://www.w3schools.com/XML/note.xml

<note>
    <to>Tove</to>
    <from>Jani</from>
    <heading>Reminder</heading>
    <body>Don't forget me this weekend!</body>
</note>

We can easily access this using my XML to Hashtable parser method ParseNode:

string xmlFilePath = @"http://www.w3schools.com/XML/note.xml";

XmlDocument doc = new XmlDocument();
doc.Load(xmlFilePath);
XmlNode main = doc.DocumentElement;
Hashtable note = ParseNode(main);

string to = note["to"].ToString();
string from = note["from"].ToString();
string heading = note["heading"].ToString();
string body = note["body"].ToString();

But what if there are multiple notes? What if there are a number of tags that are the same? Already ahead of you there, Sherlock, just look at how easy it is.

The XML we will be using is which follows this simple layout: http://www.w3schools.com/XML/simple.xml

<breakfast_menu>
    <food>
        <name>Belgian Waffles</name>
        <price>$5.95</price>
        <description>two of our famous Belgian Waffles with plenty of real maple syrup</description>
        <calories>650</calories>
    </food>
    <food>
        <name>Strawberry Belgian Waffles</name>
        <price>$7.95</price>
        <description>light Belgian waffles covered with strawberries and whipped cream</description>
        <calories>900</calories>
    </food>
</breakfast_menu>

As you can see, we simply choose the node we require (food), cast it to a List and then iterate through each piece of food.

string xmlFilePath = @"http://www.w3schools.com/XML/simple.xml";

XmlDocument doc = new XmlDocument();
doc.Load(xmlFilePath);
XmlNode docNode = doc.DocumentElement;
Hashtable menu = ParseNode(docNode);

foreach (Hashtable food in (List<hashtable>)menu["food"])
{
    Console.WriteLine("Name: {0}", food["name"]);
    Console.WriteLine("Price: {0}", food["price"]);
    Console.WriteLine("Description: {0}", food["description"]);
    Console.WriteLine("Calories: {0}", food["calories"]);
    Console.WriteLine("");
}

Console.ReadKey();

To which we will get the output:

Name: Belgian Waffles
Price: $5.95
Description: two of our famous Belgian Waffles with plenty of real maple syrup
Calories: 650

Name: Strawberry Belgian Waffles
Price: $7.95
Description: light Belgian waffles covered with strawberries and whipped cream
Calories: 900

Name: Berry-Berry Belgian Waffles
Price: $8.95
Description: light Belgian waffles covered with an assortment of fresh berries and whipped cream
Calories: 900

and so on..

So, without further ado, the part you're all dying to copy and paste (I hope!), the method:

public static Hashtable ParseNode(XmlNode node)
{
    Hashtable ht = new Hashtable();
    // loop through all nodes within the node
    foreach (XmlNode n in node.ChildNodes)
    {
        string name = n.Name;
        object value = null;

        // if it has nodes within this node, and more than just one, then parse them
        if (n.HasChildNodes)
        {
            /* "In order to understand recursion, one must first understand recursion." */
            if (n.ChildNodes.Count > 1) value = (object)ParseNode(n);
            else
            {
                // if theres only one, it may be the value, so take the value
                if (n.ChildNodes[0].NodeType == XmlNodeType.Text)
                    value = (object)n.ChildNodes[0].Value;
                else value = (object)ParseNode(n);
            }
        }
        else value = (object)n.Value;

        // as hashtables can't have a key the same, and xml can have two nodes of the same name
        // we have to put the hashtables into a list if there are more than one of the same node
        // example: <test></test> -> ht["test"] = Hashtable
        // but: <test></test><test></test> -> ht["test"] = List<hashtable>
        if (ht.ContainsKey(name))
        {
            // list exists, add to it
            if (ht[name] is List<hashtable>)
            {
                List<hashtable> list = (List<hashtable>)ht[name];
                list.Add((Hashtable)value);
                ht[name] = list;
            }
            // list doesn't exist, so create it
            else if (ht[name] is Hashtable)
            {
                List<hashtable> list = new List<hashtable>();
                Hashtable htTmp = (Hashtable)ht[name];
                list.Add(htTmp);
                list.Add((Hashtable)value);
                ht[name] = list;
            }
        }
        else ht.Add(name, value);
    }
    return ht; // and return it
}

And there you have it, one of my favourite methods. I hope it helps you as much as it helps me, it is my preferred method of reading Xml files. Maybe one day I will write a Hashtable to XML writer. One day.

Remove annoying beep from pressing enter in textbox

By Adam K Dean on

Continuing with useful snippets and methods, today I present you mighty readers with a very useful and simple way to get rid of the annoying sound from pressing enter in a textbox. We've all had it, trying to make enter take us from the username, to the password, and then to the submit button.. but that sound.. must..claw..eyes..out... or ears.. but anyway, it's really easy, read and learn:

private void txtInput_KeyUp(object sender, KeyEventArgs e)
{
    if (e.KeyCode == Keys.Enter)
    {
        e.SuppressKeyPress = true;
    }
}

private void txtInput_KeyPress(object sender, KeyPressEventArgs e)
{
    if (e.KeyChar == (char)Keys.Enter)
    {
        // any logic would go here
        e.Handled = true;
    }
}

And there you have it. Very easy, very simple and very very useful.

Now for me, back to work, a friend of mine Mihn was talking to me about refactoring yesterday, little did he know he'd plant seeds that would grow into enormous fruitition* when I'd overhaul the whole administrator area of the new site at work and change it from web based to a desktop application.. damn you Mihner, damn you!

* I hearby declare fruitition a real word.

Readable File Sizes

By Adam K Dean on

In an effort to fill up this blog with code, I will over the next few weeks be looking back over my snippets, finding useful methods and posting them. Today's feature is a simple text formatter, which converts a long (representing bytes) into a string, giving you a nice readable file size.

So without further ado:

private static string GetReadableBytes(long lSize)
{
    double size = lSize;
    if (size >= 1024 && size < 1048576)
        return string.Format("{0:#,0.00} KB", (size / 1024));

    else if (size >= 1048576 && size < 1073741824)
        return string.Format("{0:#,0.00} MB", (size / 1048576));

    else if (size >= 1073741824 && size < 1099511627776)
        return string.Format("{0:#,0.00} GB", (size / 1073741824));

    else if (size >= 1099511627776)
        return string.Format("{0:#,0.00} TB", (size / 1099511627776));

    else return string.Format("{0} B", size);
}

And here you can see the output.

/* Bytes                    Readable Size
 * =====                    =============
 * 436                  ->  436 B
 * 2159                 ->  2.11 KB
 * 36236                ->  35.39 KB
 * 2362893              ->  2.25 MB
 * 276227272            ->  263.43 MB
 * 62367298764          ->  58.08 GB
 * 3623647473473        ->  3.30 TB
 * 352635208736296      ->  320.72 TB
 * 236363536873629626   ->  214,971.38 TB
 **/

Enjoy!