public IEnumerable <Donor> FindAll()
{
Log.Info("Entering find all function");
IList <Donor> donors = new List <Donor>();
var connection = DbUtils.GetConnection();
{
Log.Info("DB connection established");
using (var command = connection.CreateCommand())
{
Log.Info("Creating command");
command.CommandText = "select * from Donors";
using (var dataR = command.ExecuteReader())
{
while (dataR.Read())
{
int did = dataR.GetInt32(0);
string name = dataR.GetString(1);
string address = dataR.GetString(2);
string phoneNumber = dataR.GetString(3);
Donor donor = new Donor(did, name, address, phoneNumber);
donors.Add(donor);
}
}
}
}
Log.InfoFormat("Exiting with {0} elements in list", donors.Count);
return(donors);
}
public void Delete(int id)
{
Log.InfoFormat("Entering delete function with value {0}", id.ToString());
var connection = DbUtils.GetConnection();
{
Log.Info("DB connection established");
using (var command = connection.CreateCommand())
{
Log.Info("Creating command");
command.CommandText =
"delete from Donors where Did = @did";
var did = command.CreateParameter();
did.ParameterName = "@did";
did.Value = id;
command.Parameters.Add(did);
int result = command.ExecuteNonQuery();
if (result == 0)
{
Log.InfoFormat("NOT EXECUTED: {0}", command.CommandText);
}
else
{
Log.InfoFormat("Executed command: {0}", command.CommandText);
}
}
}
}
public Donor FindOne(int id)
{
Log.InfoFormat("Entering findOne with value {0}", id);
var connection = DbUtils.GetConnection();
{
Log.Info("DB connection established");
using (var command = connection.CreateCommand())
{
Log.Info("Creating command");
command.CommandText = "select * from Donors where Did=@did";
IDbDataParameter paramId = command.CreateParameter();
paramId.ParameterName = "@did";
paramId.Value = id;
command.Parameters.Add(paramId);
using (var dataR = command.ExecuteReader())
{
if (dataR.Read())
{
int did = dataR.GetInt32(0);
string name = dataR.GetString(1);
string address = dataR.GetString(2);
string phoneNumber = dataR.GetString(3);
Donor donor = new Donor(did, name, address, phoneNumber);
Log.InfoFormat("Exiting findOne with value {0}", donor);
return(donor);
}
}
}
Log.InfoFormat("Exiting findOne with value {0}", null);
return(null);
}
}
public IEnumerable <Case> FindAll()
{
Log.Info("Entering function findAll");
IList <Case> cases = new List <Case>();
var connection = DbUtils.GetConnection();
{
Log.Info("Connection established");
using (var command = connection.CreateCommand())
{
Log.Info("Creating command");
command.CommandText = "select * from Cases";
using (var dataR = command.ExecuteReader())
{
while (dataR.Read())
{
int id = dataR.GetInt32(0);
string name = dataR.GetString(1);
double totalSum = dataR.GetDouble(2);
Case givenCase = new Case(id, name, totalSum);
cases.Add(givenCase);
}
}
}
Log.InfoFormat("Exiting findAll with {0} elements in list", cases.Count);
return(cases);
}
}
public Case FindOne(int id)
{
Log.InfoFormat("Entering function find with values {0}", id);
var connection = DbUtils.GetConnection();
{
Log.Info("Connection established");
using (var command = connection.CreateCommand())
{
Log.Info("Creating command");
command.CommandText = "select * from Cases where Cid = @cid";
var cid = command.CreateParameter();
cid.ParameterName = "@cid";
cid.Value = id;
command.Parameters.Add(cid);
using (var dataR = command.ExecuteReader())
{
if (dataR.Read())
{
string name = dataR.GetString(1);
double totalSum = dataR.GetDouble(2);
Case givenCase = new Case(id, name, totalSum);
Log.InfoFormat("Exiting findOne with value : {0}", givenCase.ToString());
return(givenCase);
}
}
}
Log.InfoFormat("Exiting findOne with value : {0}", null);
return(null);
}
}
public void Update(int caseId, double sumToAdd)
{
Log.InfoFormat("Entering update function with value {0} and {1}", caseId, sumToAdd);
var connection = DbUtils.GetConnection();
{
Log.Info("DB connection established");
using (var command = connection.CreateCommand())
{
Log.Info("Creating command");
command.CommandText =
"update Cases set TotalSumDonated = @sum where Cid = @cid";
var cid = command.CreateParameter();
cid.ParameterName = "@cid";
cid.Value = caseId;
command.Parameters.Add(cid);
var sum = command.CreateParameter();
sum.ParameterName = "@sum";
sum.Value = sumToAdd;
command.Parameters.Add(sum);
int result = command.ExecuteNonQuery();
if (result == 0)
{
Log.InfoFormat("NOT EXECUTED: {0}", command.CommandText);
}
else
{
Log.InfoFormat("Executed command: {0}", command.CommandText);
}
}
}
}
public int Add(Donor donor)
{
Log.InfoFormat("Entering add function with value {0}", donor.ToString());
IDbConnection connection = DbUtils.GetConnection();
{
Log.Info("DB connection established");
using (var command = connection.CreateCommand())
{
Log.Info("Creating command");
command.CommandText =
"insert into Donors(Name, Address, PhoneNumber) values (@name, @address, @phoneNumber)";
var name = command.CreateParameter();
name.ParameterName = "@name";
name.Value = donor.Name;
command.Parameters.Add(name);
var address = command.CreateParameter();
address.ParameterName = "@address";
address.Value = donor.Address;
command.Parameters.Add(address);
var phoneNumber = command.CreateParameter();
phoneNumber.ParameterName = "@phoneNumber";
phoneNumber.Value = donor.PhoneNumber;
command.Parameters.Add(phoneNumber);
int result = command.ExecuteNonQuery();
if (result == 0)
{
Log.InfoFormat("NOT EXECUTED: {0}", command.CommandText);
}
else
{
Log.InfoFormat("Executed command: {0}", command.CommandText);
}
}
using (var command = connection.CreateCommand())
{
command.CommandText = "select * from Donors order by Did desc";
using (var dataR = command.ExecuteReader())
{
if (dataR.Read())
{
int did = dataR.GetInt32(0);
return(did);
}
throw new ValidationException();
}
}
}
}
public void Update(Donor donor)
{
Log.InfoFormat("Entering update function with value {0}", donor.ToString());
var connection = DbUtils.GetConnection();
{
Log.Info("DB connection established");
using (var command = connection.CreateCommand())
{
Log.Info("Creating command");
command.CommandText =
"update Donors set Name = @name, Address = @address, PhoneNumber = @phoneNumber where Did = @did";
var did = command.CreateParameter();
did.ParameterName = "@did";
did.Value = donor.Id;
command.Parameters.Add(did);
var name = command.CreateParameter();
name.ParameterName = "@name";
name.Value = donor.Name;
command.Parameters.Add(name);
var address = command.CreateParameter();
address.ParameterName = "@address";
address.Value = donor.Address;
command.Parameters.Add(address);
var phoneNumber = command.CreateParameter();
phoneNumber.ParameterName = "@phoneNumber";
phoneNumber.Value = donor.PhoneNumber;
command.Parameters.Add(phoneNumber);
int result = command.ExecuteNonQuery();
if (result == 0)
{
Log.InfoFormat("NOT EXECUTED: {0}", command.CommandText);
}
else
{
Log.InfoFormat("Executed command: {0}", command.CommandText);
}
}
}
}
public Volunteer FindOne(string username, string password)
{
Log.InfoFormat("Entering function find with values {0}, {1}", username, password);
var connection = DbUtils.GetConnection();
{
Log.Info("Connection established");
using (var command = connection.CreateCommand())
{
Log.Info("Creating command");
command.CommandText =
"select * from Volunteers where Username = @username and Password = @password";
var usernameParam = command.CreateParameter();
usernameParam.ParameterName = "@username";
usernameParam.Value = username;
command.Parameters.Add(usernameParam);
var passwordParam = command.CreateParameter();
passwordParam.ParameterName = "@password";
passwordParam.Value = password;
command.Parameters.Add(passwordParam);
using (var dataR = command.ExecuteReader())
{
Log.Info("Command executed");
if (dataR.Read())
{
int id = dataR.GetInt32(0);
Log.InfoFormat("Id read : {0}", id);
Volunteer volunteer = new Volunteer(id, username, password);
Log.InfoFormat("Exited with value {0}", volunteer);
return(volunteer);
}
}
}
}
Log.Info("Exiting findOne with value null");
return(null);
}
public void Add(Donation donation)
{
Log.InfoFormat("Entering add function with value {0}", donation.ToString());
IDbConnection connection = DbUtils.GetConnection();
{
Log.Info("DB connection established");
using (var command = connection.CreateCommand())
{
Log.Info("Creating command");
command.CommandText =
"insert into Donations(Did, Cid, SumDonated) values (@did, @cid, @sumDonated)";
var did = command.CreateParameter();
did.ParameterName = "@did";
did.Value = donation.DonorId;
command.Parameters.Add(did);
var cid = command.CreateParameter();
cid.ParameterName = "@cid";
cid.Value = donation.CaseId;
command.Parameters.Add(cid);
var sumDonated = command.CreateParameter();
sumDonated.ParameterName = "@sumDonated";
sumDonated.Value = donation.SumDonated;
command.Parameters.Add(sumDonated);
int result = command.ExecuteNonQuery();
if (result == 0)
{
Log.InfoFormat("NOT EXECUTED: {0}", command.CommandText);
}
else
{
Log.InfoFormat("Executed command: {0}", command.CommandText);
}
}
}
}
