public Circle Find(CircleId id)
{
var builder = new SqlCircleBuilder();
LoadMembers(builder, id.Value, provider.Connection);
using (var command = provider.Connection.CreateCommand())
{
command.CommandText = @"
SELECT *
FROM circles
LEFT OUTER JOIN userCircles
ON circles.id = userCircles.circleId
WHERE id = @id
";
command.Parameters.Add(new SqlParameter("@id", id.Value));
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
builder.ReadCircle(reader);
var circle = builder.Build();
return(circle);
}
else
{
return(null);
}
}
}
}
private void LoadMembers(SqlCircleBuilder builder, string circleId, SqlConnection connection)
{
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM userCircles WHERE circleId = @id";
command.Parameters.Add(new SqlParameter("@id", circleId));
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
builder.ReadAppendMember(reader);
}
}
}
}
public List <Circle> FindAll()
{
using (var command = provider.Connection.CreateCommand())
{
command.CommandText = @"
SELECT *
FROM circles
LEFT OUTER JOIN userCircles
ON circles.id = userCircles.circleId
";
using (var reader = command.ExecuteReader())
{
if (!reader.Read())
{
return(new List <Circle>());
}
var builder = new SqlCircleBuilder(reader);
var circles = new List <Circle>();
while (reader.Read())
{
var circleId = (string)reader["id"];
if (circleId != builder.CircleId)
{
var circle = builder.Build();
circles.Add(circle);
builder.Clear();
builder.ReadCircle(reader);
}
builder.ReadAppendMember(reader);
}
var lastCircle = builder.Build();
circles.Add(lastCircle);
return(circles);
}
}
}
public Circle Find(CircleName name)
{
using (var command = provider.Connection.CreateCommand())
{
command.CommandText = @"SELECT * FROM circles WHERE name = @name";
command.Parameters.Add(new SqlParameter("@name", name.Value));
using (var reader = command.ExecuteReader())
{
if (!reader.Read())
{
return(null);
}
var builder = new SqlCircleBuilder(reader);
LoadMembers(builder, builder.CircleId, provider.Connection);
var circle = builder.Build();
return(circle);
}
}
}
