CodeBetter.Com
CodeBetter.Com
RSS 2.0 via Feedburner
           Do you Twitter? Follow us @CodeBetter

Peter's Gekko

public Blog MyNotepad : Imho { }

October 2007 - Posts

  • Sloppy SQL and database schema

    This is another story from the app with the sprocs, an app where a lot of the business logic (BL) is inside a large number of stored procedures (sprocs). These are written by the domain expert himself. As the BL changes the app changes as well, which leads to change of the table structures (the database schema) and the sql code in the sprocs. Whether sprocs are a good or bad place for BL is a discussion I do not want to repeat. Right now the BL is just over there; moving it is too big a step. But recently we bumped into something which I will name "sloppy SQL" and is imho another reason for getting it out of the DB and into an environment which forces stricter code and a way to get away from the sql itself.

    What happened ? Some of the tables got a different structure, we dropped a column here and added a column there. Table columns have a determined order, it is the order in which they appear in the designer. When you issue a sloppy SQL statement like

    SELECT * FROM Customers

    this column order determines in which order the values in the result rows are. When you issue a neater statement, like

    SELECT Name, Address, ZIP, Town FROM Customers

    the statement determines the order.

    Things get worse when it comes to inserting rows. Take this statement to insert a new customer

    INSERT INTO Customer (Name, Address, Zip, Town)

    VALUES ('MyCustomer', 'MyStreet', 'MyZip', 'MyTown')

    Here the mapping between columns and values to insert is clear. But this is also a valid sql statement:

    INSERT INTO Customer

    VALUES ('MyCustomer', 'MyStreet', 'MyZip', 'MyTown')

    Here the values are mapped onto the columns according to the column order. This statement will execute provided you pass a value for every column.

    Now what if the order of the columns is different from the assumed order in the values ? In the worst case you will write the wrong data in the wrong columns, in the best case you will hit a sql exception because of failing typecast or the like.

    The sprocs in our app do contain a lot of these sloppy SQL statements. The guy who writes them has an excellent knowledge of the domain, is not a bad coder at all, just a lazy typer. What can you do ? We did not notice this problem until the app was rolled out to a testing server. To synchronize the database we are using Red Gate's SQL compare, which is a marvel of a tool I introduced before. It generates a script to alter the database schema. Modifying the structure of a table is on itself a matter of dropping some columns and appending the new ones. As a result all the new columns always come last in the column order. Which is different from the order assumed in the sprocs. So running the sprocs led, thank goodness, to exceptions.

    Red Gate wouldn't be Red Gate if it didn't have an option Force Column order.

    This is not set by default. The option generates a different script which

    • Copies the data to a temporary table
    • Drops the table
    • Recreates the table with the columns exactly in the desired order
    • Repopulates the table from the temporary one

    And now the sprocs will work as intended.

    There are a lot of other options to set in Sql Compare, the list is worth a study. Notice the two sets of settings, My Defaults and Red Gate Defaults. You can trust Red Gate to be the experts but not all of their defaults are best for your (or my) scenarios.

    For our app it would be a better alternative to have all BL inside C# code where we have things like strong typing and unit tests to guard it. But that would take a lot of work, including making me a true domain expert. I am learning on there but still have a long way to go. For now it's Red Gate to the rescue.

  • ReSharper in the context menu

    Last week Jean-Paul demonstrated how to get faster access to ReSharper with the Alt key. He did so by removing the competing inbuilt VS 2005 refactor menu. Also in the context menu ReSharper is masked by the VS refactoring. Here's the recipe to promote our favorite tool over there.

    • In VS pick Tools | Customize
    • Select the Toolbars tab
    • In the list check the context menus checkbox
    • A toolbar with named dropdowns will appear
    • Expand Editor Context Menus
    • Select Code Window
    • The context menu will appear
    • Tear of (Drag and drop it anywhere) the VS refactor submenu
    • Right click the Resharper submenu
    • Change the name to &Refactor
    • Close the dialog

    Et voilà, hitting the context menu key, followed by an R now pops up the available ReSharper refactorings.

    This is just as fast as <Alt>R|R, but the nice thing is that the context menu pops up closer to the code to be refactored.

    Posted Oct 18 2007, 12:48 PM by pvanooijen with 5 comment(s)
    Filed under:
  • A program manager should be an architect

    What is an architect ? There are many definitions around, varying from "an elitist in an ivory tower throwing down incomprehensible documents" to "a developer more equal than others". In a previous post I quite boldly stated that an architect always should be a coder and nearly everybody seemed to agree on that. But an architect is more than just a coder. Constructing a building is not throwing an amount of bricks and timber on a heap, you need an architect who will split and organize the job in building walls, constructing the roof, laying the plumbing, etc. To construct software you also have to split the project into manageable parts. To get a separation and lineup of user interactions, concerns and dependencies you need a person who knows how to do that. And that's imho a good workable definition of an architect: somebody who gives a technical structure to the project. For this structure to be constructible the architect has to speak the same language as the constructors, he has to code.

    In comes the program manager (PM). What are usually his roles ?

    1. Coach and motivate his team members
    2. Organize the work to be done
    3. Report to management

    The first role is a matter of personality. The PM might be a total jerk but just one guy on the team with radiating passion and humor can boost the morale and productivity to unknown heights. He (she) does not have to be a technical wonder, just making the workplace a pleasant place is the wonder itself. No PM required.

    The second role is the hardest. To organize the work you need to be able to split the work to give each developer a well defined part to work on. This is important for a "junior" or somebody new on the project to prevent them from drowning. But this is even more important for somebody I call the "over experienced developer". They have been everywhere and done everything. But on their way they forgot the main mantra of software development "keep learning" and still write software in the old DOS style. Their lines of code own the machine. In making their lines work they will change and adapt code all over the project. This may sound somewhat exaggerated but I have seen people like this singlehanded completely ruining a project. Several times. The role of the PM here is to restrict these people, give them a well isolated piece of work.

    My main point is that to clearly separate the work the PM needs knowledge on how to do that in a technical way. In other words he has to be an architect.

    So what to do with a PM who can't get along with his team-mates and doesn't understand architecture ? This leaves the third role. All that needs is a couple of Office macro's and a cell phone. Everybody can do that. So a good PM should have a social personality (having an Xbox might be enough) and be an architect (and thus be a coder). Which is a rare combination. Real good PM's are rare.

  • Parameterized searching in a List<T>

    The generic List is a pretty powerful class. But finding one or more items is not that easy to grasp at first sight. The Find and the FindAll method take a so called Predicate as a parameter to test the individual items in a List. The documentation on this on MSDN is not very obvious. In a rather long example you will not get any further than finding items on hard coded criteria. In the real world you usually want to find items on dynamic criteria. There is some literature on the web on this but the solutions vary. Hereby a roundup.

    In a small example I will also work, just like MSDN, with a list of dinosaurus names

    private static List<string> buildAnimals()

    {

       List<string> animals = new List<string>();

       animals.Add("Diplodocus");

       animals.Add("Tyranosaurus");

       animals.Add("Protoceratops");

       animals.Add("Deinonichus");

       animals.Add("Stegosaurus");

       return animals;

    }

    In the example a selection of these names will populate a listbox. Pure presentation code.

    The FindAll method takes a Predicate as a parameter. A predicate is a delegate, a definition of a method signature. The predicate method has one parameter, of the same type as the members of the list and returns a Boolean. Returning true signifies the list member does satisfy the selection criteria, returning false is a no match. When searching the list the Find(All) method will pass each list member to the method.

    This is the predicate used all over the MSDN example

    private static Boolean findAnimalMSDN(string name)

    {

        return name.EndsWith("saurus");

    }

    Now to parameterize this search we have to get the search criteria into this method. It cannot be passed as a method parameter, an extra member is needed.

    private static string findAnimal;

       

    private static Boolean findAnimal1(string name)

    {

        return name.EndsWith(findAnimal);

    }

       

    private void button1_Click(object sender, EventArgs e)

    {

        List<string> animals = buildAnimals();

        listBox1.Items.Clear();

        findAnimal = textBox1.Text;

        List<string> matchinganimals = animals.FindAll(findAnimal1);

        foreach (string s in matchinganimals)

            listBox1.Items.Add(s);

    }

       

    Just like all MSDN example code the predicate is here a static member. To get the non static textbox value into the predicate requires a static helper string.

    But the predicate does not have to be static. An instance method can read the contents of the textbox itself.

    private Boolean findAnimal2(string name)

    {

        return name.EndsWith(textBox1.Text);

    }

       

    private void button2_Click(object sender, EventArgs e)

    {

        List<string> animals = buildAnimals();

        listBox1.Items.Clear();

        List<string> matchinganimals = animals.FindAll(findAnimal2);

        foreach (string s in matchinganimals)

            listBox1.Items.Add(s);

    }

    But still this code is pretty smelly, I don't like it at all that there are two methods needed to get just one thing done. There is an article on CodeProject by Alex Perepletov where he discusses some of these issues and creates a class to wrap everything up. But I still think that is an overkill. After all I don't want to have to write a complete class to get something very simple done.

    Using anonymous delegates in C# you can combine both methods in one. Instead of writing the predicate and using it as a parameter to the FindAll method you just pass the complete method itself as a parameter.

    private void button4_Click(object sender, EventArgs e)

    {

        List<string> animals = buildAnimals();

        listBox1.Items.Clear();

        List<string> matchinganimals = animals.FindAll(

            delegate(string name) { return name.EndsWith(textBox1.Text); }

            );

        foreach (string s in matchinganimals)

            listBox1.Items.Add(s);

    }

    The parameter is the complete method, the method signature is a delegate.

    The method does not need a name, it is anonymous. In a short but clear reference on thinksharp it is named an inline delegate. The official name is anonymous delegate. The code is inline, note how it can read (and set) it's surrounding scope. Here it can read the contents of the textbox. An anonymous delegate can also read from the scope it is defined in. A variation:

    private void button3_Click(object sender, EventArgs e)

    {

        List<string> animals = buildAnimals();

        listBox1.Items.Clear();

        string privateAnimal = textBox1.Text;

        List<string> matchinganimals = animals.FindAll(

            delegate(string name) { return name.EndsWith(privateAnimal); }

            );

        foreach (string s in matchinganimals)

            listBox1.Items.Add(s);

    }

    Here it works with the local privateAnimal string.

    In the example I have worked with a list of strings. But working with complete methods to test list members there is no limit on the complexity of the comparison.

    Well, that's about it. Then there is the obvious question: Is this code available in VB ? It's pretty funny what the translator comes up with. But no there are no anonymous delegates in VB.

    Posted Oct 02 2007, 10:23 PM by pvanooijen with 3 comment(s)
    Filed under:
More Posts

Our Sponsors

Free Tech Publications

This Blog

Syndication

News