public bool AddMember(CodeProjectMember member)
{
var connection = new SqliteConnection("Data Source=" + dbPath);
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT [Key] FROM [Member] WHERE [Key]=" + member.Id;
var r = command.ExecuteReader();
if (r.HasRows)
{
using (var uc = connection.CreateCommand())
{
uc.CommandText = "UPDATE [Member] SET " +
" [Name] = '" + member.Name + "'," +
" [ArticleCnt] = '" + member.ArticleCount + "'," +
" [BlogCnt] = '" + member.BlogCount + "'," +
" [Reputation] = '" + member.Reputation + "'," +
" WHERE [Key]=" + member.Id;
uc.ExecuteNonQuery();
}
}
else
{
using (var ic = connection.CreateCommand())
{
ic.CommandText = "INSERT INTO [Member] ([Key], [Name], [ArticleCnt], [BlogCnt], [Reputation])"
+ " VALUES(" + member.Id + ", '" + member.Name + "', '" + member.ArticleCount + "', '" + member.BlogCount + "', '" + member.Reputation + "')";
ic.ExecuteNonQuery();
}
}
}
connection.Close();
FileStorageService storage = new FileStorageService();
if (member.Avatar != null)
{
storage.WriteBytes(member.Avatar, member.Id.ToString());
}
return true;
}
public List<CodeProjectMember> GetMembers()
{
List<CodeProjectMember> memberList = new List<CodeProjectMember>();
var connection = new SqliteConnection("Data Source=" + dbPath);
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT [Key], [Name], [ArticleCnt], [BlogCnt], [Reputation], [IsMe] FROM [Member]";
var r = command.ExecuteReader();
while (r.Read())
{
CodeProjectMember member = new CodeProjectMember();
FillMemberFromDataReader(member, r);
memberList.Add(member);
}
}
connection.Close();
return memberList;
}
private void FillMemberFromDataReader(CodeProjectMember member, DbDataReader r)
{
object idAsObject = r["Key"];
member.Id = int.Parse(idAsObject.ToString());
member.Name = r["Name"].ToString();
member.ArticleCount = int.Parse(r["ArticleCnt"].ToString());
member.BlogCount = int.Parse(r["BlogCnt"].ToString());
member.Reputation = r["Reputation"].ToString();
}
public CodeProjectMember GetMember(int memberId)
{
var connection = new SqliteConnection("Data Source=" + dbPath);
connection.Open();
CodeProjectMember member = new CodeProjectMember();
member.Id = memberId;
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT [Key], [Name], [ArticleCnt], [BlogCnt], [Reputation], [IsMe] FROM [Member] WHERE [Key]=" + memberId;
var r = command.ExecuteReader();
while (r.Read())
{
FillMemberFromDataReader(member, r);
}
}
connection.Close();
return member;
}