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.Comma开发者_如何学GondText = 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());
}
}
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.
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()));
}
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 ;)
精彩评论