How would you refactor this bit of code?

Question

This bit of code runs on Windows Compact Framework and what it does is obvious. It looks as it should be refactored (especially considering that I may want to add cmd.ExecuteResultSet() later), but I can't see an elegant way to do it. Any ideas appreciated.

    internal void RunNonQuery(string query)
    {
        string connString = GetLocalConnectionString();

        using (SqlCeConnection cn = new SqlCeConnection(connString))
        {
            cn.Open();
            SqlCeCommand cmd = cn.CreateCommand();
            cmd.CommandText = query;
            cmd.ExecuteNonQuery();
        }
    }  




    internal int RunScalar(string query)  
    {
        string connString = GetLocalConnectionString();

        using (SqlCeConnection cn = new SqlCeConnection(connString))
        {
            cn.Open();
            SqlCeCommand cmd = cn.CreateCommand();
            cmd.CommandText = query;
            return int.Parse(cmd.ExecuteScalar().ToString());
        }
    }

Answer 1

I'm not sure I would refactor it, but perhaps:

static void PerformQuery(string connectionString, string command,
      Action<SqlCeCommand> action)
{ //TODO: sanity checks...
    using(SqlCeConnection conn = new SqlCeConnection(connectionString))
    using(SqlCeCommand cmd = conn.CreateCommand()) {
        cmd.CommandText = command;
        conn.Open();
        action(cmd);
    }
}

internal void RunNonQuery(string query)
{
    string connString = GetLocalConnectionString();
    PerformQuery(connString, query, cmd => cmd.ExecuteNonQuery());
}  

internal int RunScalar(string query)  
{
    int result = 0;
    string connString = GetLocalConnectionString();
    PerformQuery(connString, query,
        cmd => {result = int.Parse(cmd.ExecuteScalar().ToString()); }
    );
    return result;
}

Otherwise - just maybe a CreateAndOpenConnection(string) method, and a CreateCommand(SqlCeConnection,string) method.

Answer 2

If you are using C# 3.0, you could do something like the following:

private T CreateCommand<T>(string query, Func<SqlCeCommand, T> func)
{
    var connString = GetLocalConnectionString();

    using (var cn = new SqlCeConnection(connString))
    {
        cn.Open();

        using (var cmd = cn.CreateCommand())
        {
            cmd.CommandText = query;
            return func(cmd);
        }
    }
}

private void CreateCommand(string query, Action<SqlCeCommand> action)
{
    CreateCommand<object>(query, cmd => 
    {
        action(cmd);
        return null;
    });
}

internal void RunNonQuery(string query)
{
    CreateCommand(query, cmd => cmd.ExecuteNonQuery());
}

internal int RunScalar(string query)
{
    return CreateCommand(query, cmd => 
        int.Parse(cmd.ExecuteScalar().ToString()));
}

Answer 3

I would create a class out of the code to wrap the connection creation and command execution logic. This will provide you with a single place to implement transactions in the future and will consolidate creation of the connection and command. This consolidation will allow for settings timeouts, joining transactions, etc.

class Connection : IDisposable
{
    readonly SqlConnection _conn;
    public Connection()
    {
        string connString = GetLocalConnectionString();
        _conn = new SqlConnection(connString);
        _conn.Open();
    }

    public void Dispose() { _conn.Dispose(); }

    public SqlCommand CreateCommand(string qry)
    {
        SqlCommand cmd = _conn.CreateCommand();
        cmd.CommandText = qry;
        //cmd.CommandTimeout = TimeSpan.FromMinutes(x);
        return cmd;
    }
    public int ExecuteNonQuery(string qry)
    {
        using (SqlCommand cmd = CreateCommand(qry))
            return cmd.ExecuteNonQuery();
    }
    public int RunScalar(string qry)
    {
        using (SqlCommand cmd = CreateCommand(qry))
            return int.Parse(cmd.ExecuteScalar().ToString());
    }
}

Then if you still want to maintain your original API, you do the following:

class SqlCode
{
    internal void RunNonQuery(string query)
    {
        using (Connection cn = new Connection())
            cn.ExecuteNonQuery(query);
    }

    internal int RunScalar(string query)
    {
        using (Connection cn = new Connection())
            return cn.RunScalar(query);
    }
}

The only thing left is to re-insert the 'Ce' in the SqlXxxx stuff ;)