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 ;)

友情链接