Skip to content

Instantly share code, notes, and snippets.

@einarwh
Created June 20, 2011 16:48
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save einarwh/1035972 to your computer and use it in GitHub Desktop.
Save einarwh/1035972 to your computer and use it in GitHub Desktop.
Dry data: Refactored naive client with generic methods
public class Client2
{
private readonly string _connStr;
private readonly DbProviderFactory _dpf;
public Client2(string connStr) : this(connStr,
DbProviderFactories.GetFactory("System.Data.SqlClient"))
{}
public Client2(string connStr, DbProviderFactory dpf)
{
_connStr = connStr;
_dpf = dpf;
}
private DbParameter CreateParameter(string name, object val)
{
var p = _dpf.CreateParameter();
p.ParameterName = name;
p.Value = val;
return p;
}
public IEnumerable<User> GetCompanyUsers(string company)
{
return ExecuteReader("spGetCompanyUsers",
new[] {CreateParameter("@companyName", company)},
r => new User
{
Id = (string) r["id"],
UserName = (string) r["user"],
Name = (string) r["name"],
Email = (string) r["emailAddress"],
Phone = (string) r["cellPhone"],
ZipCode = (string) r["zip"]
});
}
public IEnumerable<T> ExecuteReader<T>(string spName,
DbParameter[] sqlParams, Func<IDataRecord, T> map)
{
var result = new List<T>();
using (var conn = _dpf.CreateConnection())
using (var cmd = _dpf.CreateCommand())
{
conn.ConnectionString = _connStr;
conn.Open();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = spName;
cmd.Parameters.AddRange(sqlParams);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
result.Add(map(reader));
}
}
return result;
}
public string GetUserEmail(string userId)
{
return ExecuteScalar("spGetEmailForUser",
new[] {CreateParameter("@userId", userId)},
o => o as string);
}
public T ExecuteScalar<T>(string spName,
DbParameter[] sqlParams, Func<object, T> map)
{
using (var conn = _dpf.CreateConnection())
using (var cmd = _dpf.CreateCommand())
{
conn.ConnectionString = _connStr;
conn.Open();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = spName;
cmd.Parameters.AddRange(sqlParams);
return map(cmd.ExecuteScalar());
}
}
public void StoreUser(User u)
{
ExecuteNonQuery("spInsertOrUpdateUser",
new[]
{
CreateParameter("@userId", u.Id),
CreateParameter("@user", u.UserName),
CreateParameter("@name", u.Name),
CreateParameter("@emailAddress", u.Email),
CreateParameter("@cellPhone", u.Phone),
CreateParameter("@zip", u.ZipCode)
});
}
public void ExecuteNonQuery(string spName,
DbParameter[] sqlParams)
{
using (var conn = _dpf.CreateConnection())
using (var cmd = _dpf.CreateCommand())
{
conn.ConnectionString = _connStr;
conn.Open();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = spName;
cmd.Parameters.AddRange(sqlParams);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment