views:

387

answers:

8

I came accross the following code today and I didn't like it. It's fairly obvious what it's doing but I'll add a little explanation here anyway:

Basically it reads all the settings for an app from the DB and the iterates through all of them looking for the DB Version and the APP Version then sets some variables to the values in the DB (to be used later).

I looked at it and thought it was a bit ugly - I don't like switch statements and I hate things that carry on iterating through a list once they're finished. So I decided to refactor it.

My question to all of you is how would you refactor it? Or do you think it even needs refactoring at all?

Here's the code:

        using (var sqlConnection = new SqlConnection(Lfepa.Itrs.Framework.Configuration.ConnectionString))
        {
            sqlConnection.Open();

            var dataTable = new DataTable("Settings");

            var selectCommand = new SqlCommand(Lfepa.Itrs.Data.Database.Commands.dbo.SettingsSelAll, sqlConnection);
            var reader = selectCommand.ExecuteReader();
            while (reader.Read())
            {
                switch (reader[SettingKeyColumnName].ToString().ToUpper())
                {
                    case DatabaseVersionKey:
                        DatabaseVersion = new Version(reader[SettingValueColumneName].ToString());
                        break;
                    case ApplicationVersionKey: 
                        ApplicationVersion = new Version(reader[SettingValueColumneName].ToString());
                        break;
                    default:
                        break;
                }
            }

            if (DatabaseVersion == null)
                throw new ApplicationException("Colud not load Database Version Setting from the database.");
            if (ApplicationVersion == null)
                throw new ApplicationException("Colud not load Application Version Setting from the database.");
        }
+15  A: 

My two cents...

  1. As Bobby comments, use using on every disposable object
  2. I would avoid opening a table and iterate through all the records, use a filter if possible to obtain the values
  3. If not possible at all, avoid using switch on strings, as there are only two options you could do an if else with a string.Compare with the case insensitive option, so you don't have to make an upper each time.
  4. Check for null values before reading them to avoid unhandled exceptions
  5. If you have to open that kind of connections many times in your code you may use a factory method to give you the connection.
  6. I would avoid using "var" keyword when you already know what kind of object you are creating. You usually refactor to enhance code legibility.
jmservera
Brilliant answer.
james lewis
I agree on all points except not to use `var`. Something like `Dictionary<Foo, Bar> dictionary = new Dictionary<Foo, Bar>();` is in my opinion much harder to read than `var dictionary = new Dictionary<Foo, Bar>();` - repeating the type twice yields no additional information for a reader and only adds noise to the code.
Daniel Brückner
connections in .net are already pooled. You do not need to manually manage them.Var seems to be slowly becoming a very accepted way to declare variables. and you always have to know the type when using it. (well it could be an anonymous type)
Andrey
+1 for Daniel's refutation of the var suggestion. It is used clearly and appropriately in the given code.
Andrew Anderson
Another vote against the var suggestion. Using var appropriately reduces clutter and repetition in your code and can greatly help the readability. Otherwise good answer.
womp
+1 Great answer and I agree with you on the var. I get the point about not repeating the type twice, but I scan the left side code looking for my variable types. If I see var, var, var, it has no meaning. I then have to look in and find the name and/or then locate the type.
Chuck Conway
@Chuck Conway, @jmservera I'm not sure I agree with this... I think you should use var as Daniel described. The only time I won't use it (to increase transparency) is when I'm setting a variable from the return value of a method and it isn't instantly clear what that variable type will be (i.e. var myVariable = something.GetAValue() - this is not cool).
james lewis
A: 

use an if instead of a switch for less than 5 cases, it's faster.

skimania
+2  A: 

There are minor inefficiencies in the code (a lot of string allocations and unnecessary lookups).

Here's the code with some changes in it:

  • No ToUpper() call. (ToUpper and ToLower can be evil )
  • caching DataReader value
  • No ToString calls
  • removed DataTable instance creation (not used)

The resulting code looks like this:

using (var sqlConnection = new SqlConnection(Lfepa.Itrs.Framework.Configuration.ConnectionString))
{
    sqlConnection.Open();

    using(var selectCommand = new SqlCommand(Lfepa.Itrs.Data.Database.Commands.dbo.SettingsSelAll, sqlConnection))
    {
        using (var reader = selectCommand.ExecuteReader())
        {
            while (reader.Read())
            {
                string val = reader.GetString(reader.GetOrdinal(SettingKeyColumnName));
                if ( string.IsNullOrEmpty(val) )
                    continue;
                if ( val.Equals(DatabaseVersionKey, StringComparison.OrdinalIgnoreCase))
                    DatabaseVersion = new Version(val);
                else if (val.Equals(ApplicationVersionKey, StringComparison.OrdinalIgnoreCase))
                    ApplicationVersion = new Version(val);
            }
        }
    }
}

if (DatabaseVersion == null)
    throw new ApplicationException("Could not load Database Version Setting from the database.");
if (ApplicationVersion == null)
    throw new ApplicationException("Could not load Application Version Setting from the database.");
Vadmyst
How about putting in a `if (DatabaseVersion != null `?
Gabe
Maybe in the future there will be some other settings data in there. However, if speed is of great concern and select statment can return a lot of rows, then ending the while loop is a good option.
Vadmyst
-1: no `using` around the `SqlCommand`. Fix that and I'll remove the downvote.
John Saunders
I agree with John: most people don't even realise that the Sql/Oracle/WhateverCommand objects are IDisposable, which is a shame.
Ed Woodcock
A: 

I would rewrite the query so that it returns a single record with two columns. That would get rid of the conditionals inside the using() statement. As Gabe said, I'd add

if (DatabaseVersion != null && ApplicationVersion != null) break;

inside the using block.

Chris McKenzie
A: 

One suggestion I would add is to perform a check to make sure the connection to the database was established before performing any more commands.

sqlConn.Open();

if (sqlConn.State == ConnectionState.Open)
{

   // perform read settings logic here

}
dretzlaff17
Why would you do that? If `Open` fails, it'll throw an exception.
SLaks
Because in my experience .Open will not throw the exception. I have .Open line executes without error, but then if you check the connection state it is still closed and the next line that attempts to use the connection fails.
dretzlaff17
+1  A: 

This code actually does two different things:

1) Get the database version

2) Get the application version

The only commonality is the data storage method

I would suggest refactoring to have three methods:

// Get the entire dataset from the database using the SettingsSelAll command.
private DataTable GetVersionData() {...}
// Parse a version from the dataset.
private Version GetVersion(string versionName, DataTable dataSet) { ... }

public Version GetVersion(string versionName)
{
    DataTable table = GetVersionData();
    return GetVersion(versionName, table);
}

This enforces seperation between getting the data and actually doing stuff with it, which is always something to aim for. A form of caching would be recommended to avoid doing the query twice, and I would suggest maybe having method that did both the database and application version calls in one step.

Ed Woodcock
A: 

If you want to aim for sustainability and expansion as more settings come through, set up a strategy pattern storing each of the strategies for dealing with the particular setting in a dictionary with an associated Key value to pull the correct strategy out to replace the switch statement.

Gnostus
+1  A: 

Presumably, there are other useful values in the settings table. I would suggest reading all of the values into a dictionary that is held by the application. Then look up the values as needed. The added expense of grabbing all of the records instead of just these two is trivial compared to making another connection later and re-executing the same query.

cdkMoose