public bool Create(UserDTO user)
{
int summary;
using (var connection = new SqlConnection(UsersConnectionString))
{
var command = connection.CreateCommand();
command.CommandText = "INSERT INTO dbo.Users_Table (Guid, Name, Date, image_id) VALUES(@UserId, @UserName, @UserBD, @UserImg) ";
command.Parameters.AddWithValue("@UserId", user.Id);
command.Parameters.AddWithValue("@UserName", user.Name);
command.Parameters.AddWithValue("@userBD", user.DateOfBirth);
command.Parameters.AddWithValue("@UserImg", user.Image);
connection.Open();
summary = command.ExecuteNonQuery();
}
if (summary > 0)
{
return(true);
}
else
{
DBconnectException e = new DBconnectException("Can't create user-model");
LogType.AddLog(e);
return(false);
}
}
public bool Create(ImageDTO img)
{
int summary;
using (var connection = new SqlConnection(UsersConnectionString))
{
var command = connection.CreateCommand();
command.CommandText = "INSERT INTO dbo.Images (Name, SmallImage, BigImage, Format ) VALUES(@imgName, @imgSData, @imgBData, @imgFormat)";
command.Parameters.AddWithValue("@imgName", img.Name);
var small = img.DataSmall == null ? (object)DBNull.Value : img.DataSmall;
var big = img.DataBig == null ? (object)DBNull.Value : img.DataBig;
var format = img.Format == null ? (object)DBNull.Value : img.Format;
command.Parameters.Add("@imgSData", SqlDbType.VarBinary, -1).Value = small;
command.Parameters.Add("@imgBData", SqlDbType.VarBinary, -1).Value = big;
command.Parameters.Add("@imgFormat", SqlDbType.VarChar, 50).Value = format;
//command.Parameters.AddWithValue("@imgSData", img.DataSmall==null ? (object)DBNull.Value : img.DataSmall);
//command.Parameters.AddWithValue("@imgBData", img.DataBig == null ? (object)DBNull.Value : img.DataBig);
//command.Parameters.AddWithValue("@imgFormat", img.Format == null ? (object)DBNull.Value : img.Format);
connection.Open();
summary = command.ExecuteNonQuery();
}
if (summary > 0)
{
return(true);
}
else
{
DBconnectException e = new DBconnectException("Can't insert image");
LogType.AddLog(e);
return(false);
}
}
public UserListLogic()
{
string mode;
try
{
mode = ConfigurationManager.AppSettings["DataMode"];
}
catch (Exception e)
{
LogType.AddLog(e);
throw new ConfigurationFileExeption("Configuration file error.", e);
}
switch (mode)
{
case "JSONFiles":
{
DAL = new _EPAM_UserList.DAL.JSONFiles.DAL();
}
break;
case "Collection":
{
DAL = new _EPAM_UserList.DAL.Collection.DAL();
}
break;
case "DB":
{
DAL = new _EPAM_UserList.DAL.DB.UserDAL();
}
break;
}
}
public bool RemoveRole(AppUserDTO user, RoleDTO role)
{
int summary;
using (var connection = new SqlConnection(UsersConnectionString))
{
var command = connection.CreateCommand();
command.CommandText = "DELETE FROM dbo.UsersRoles WHERE Username = @userName AND Role = @roleName ";
command.Parameters.AddWithValue("@userName", user.Name);
command.Parameters.AddWithValue("@roleName", role.Name);
connection.Open();
summary = command.ExecuteNonQuery();
}
if (summary > 0)
{
user.Roles.Remove(role);
return(true);
}
else
{
DBconnectException e = new DBconnectException("Can't remove role from user");
LogType.AddLog(e);
return(false);
}
}
public void Save()
{
string json;
try
{
json = JsonConvert.SerializeObject(Awards,
Formatting.None,
new JsonSerializerSettings()
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
}
);
}
catch (Exception e)
{
LogType.AddLog(e);
throw new JsonException("Can't save to json file", e);
}
using (StreamWriter writer = new StreamWriter("awards.json"))
{
writer.Write(json);
writer.Flush();
writer.Close();
}
}
public bool Create(AwardDTO award)
{
int summary;
using (var connection = new SqlConnection(UsersConnectionString))
{
var command = connection.CreateCommand();
command.CommandText = "INSERT INTO dbo.Awards_Table (Guid, Name, image_id) VALUES(@awardId, @awardName, @awardImage)";
command.Parameters.AddWithValue("@awardId", award.Id);
command.Parameters.AddWithValue("@awardName", award.Name);
command.Parameters.AddWithValue("@awardImage", award.Image);
connection.Open();
summary = command.ExecuteNonQuery();
}
if (summary > 0)
{
return(true);
}
else
{
DBconnectException e = new DBconnectException("Can't create award-model");
LogType.AddLog(e);
return(false);
}
}
public bool AddRole(AppUserDTO user, RoleDTO role)
{
int summary;
using (var connection = new SqlConnection(UsersConnectionString))
{
var command = connection.CreateCommand();
command.CommandText = "INSERT INTO dbo.UsersRoles (Username, Role) VALUES(@userName, @roleName) ";
command.Parameters.AddWithValue("@userName", user.Name);
command.Parameters.AddWithValue("@roleName", role.Name);
connection.Open();
summary = command.ExecuteNonQuery();
}
if (summary > 0)
{
user.Roles.Add(role);
return(true);
}
else
{
DBconnectException e = new DBconnectException("Can't add role to user");
LogType.AddLog(e);
return(false);
}
}
public bool Update(AwardDTO award)
{
int summary;
using (var connection = new SqlConnection(UsersConnectionString))
{
var command = connection.CreateCommand();
command.CommandText = "UPDATE dbo.Awards_Table SET Name = @awardName, image_id = @awardImage WHERE Guid = @awardId; ";
command.Parameters.AddWithValue("@awardName", award.Name);
command.Parameters.AddWithValue("@awardImage", award.Image);
command.Parameters.AddWithValue("@awardId", award.Id);
connection.Open();
summary = command.ExecuteNonQuery();
}
if (summary > 0)
{
return(true);
}
else
{
DBconnectException e = new DBconnectException("Can't update award-model");
LogType.AddLog(e);
return(false);
}
}
public bool Update(ImageDTO img)
{
int summary;
using (var connection = new SqlConnection(UsersConnectionString))
{
var command = connection.CreateCommand();
command.CommandText = "UPDATE dbo.Images SET SmallImage = @imgSData, BigImage = @imgBData Format = @imgFormat WHERE Id = @imgId; ";
var small = img.DataSmall == null ? (object)DBNull.Value : img.DataSmall;
var big = img.DataBig == null ? (object)DBNull.Value : img.DataBig;
var format = img.Format == null ? (object)DBNull.Value : img.Format;
command.Parameters.Add("@imgSData", SqlDbType.VarBinary, -1).Value = small;
command.Parameters.Add("@imgBData", SqlDbType.VarBinary, -1).Value = big;
command.Parameters.Add("@imgFormat", SqlDbType.VarChar, 50).Value = format;
command.Parameters.AddWithValue("@imgId", img.Id);
connection.Open();
summary = command.ExecuteNonQuery();
}
if (summary > 0)
{
return(true);
}
else
{
DBconnectException e = new DBconnectException("Can't change image");
LogType.AddLog(e);
return(false);
}
}
public int Count()
{
try
{
return(Awards.Count());
}
catch (Exception e)
{
LogType.AddLog(e);
throw new CacheAccessException("Can't access to cache!", e);
}
}
public UserDTO Get(Guid id)
{
try
{
return(DAL.Get(id));
}
catch (Exception e)
{
LogType.AddLog(e);
throw e;
}
}
//public void Load()
//{
// DAL.Load();
//}
public void Save()
{
try
{
DAL.Save();
}
catch (Exception e)
{
LogType.AddLog(e);
throw e;
}
}
public bool Create(AwardDTO award)
{
try
{
Awards.Add(award);
return(true);
}
catch (Exception e)
{
LogType.AddLog(e);
throw new CacheAccessException("Can't save data to cache!", e);
}
}
public bool Create(UserDTO user)
{
try
{
Users.Add(user);
return(true);
}
catch (Exception e)
{
LogType.AddLog(e);
throw new CacheAccessException("Can't save data to cache!", e);
}
}
public UserDTO Get(Guid id)
{
try
{
var temp = Users.FirstOrDefault(x => x.Id == id);
return(temp);
}
catch (Exception e)
{
LogType.AddLog(e);
throw new CacheAccessException("Can't save data to cache!", e);
}
}
public AwardDTO Get(Guid id)
{
try
{
var temp = Awards.FirstOrDefault(x => x.Id == id);
return(temp);
}
catch (Exception e)
{
LogType.AddLog(e);
throw new CacheAccessException("Can't access to cache!", e);
}
}
public void Load()
{
try
{
if (File.Exists("awards.json"))
{
Awards = JsonConvert.DeserializeObject <List <AwardDTO> >(File.ReadAllText("awards.json"));
}
}
catch (Exception e)
{
LogType.AddLog(e);
throw new JsonException("Can't load json file.", e);
}
}
public void Load()
{
try
{
if (File.Exists("users.json"))
{
Users = JsonConvert.DeserializeObject <List <UserDTO> >(File.ReadAllText("users.json"));
}
}
catch (Exception e)
{
LogType.AddLog(e);
throw new JsonException("Can't load to json", e);
}
}
bool IUserListDAL.AddAward(Guid UserID, Guid AwardID)
{
int summary = 0;
using (var connection = new SqlConnection(UsersConnectionString))
{
var command = connection.CreateCommand();
var tcommand = connection.CreateCommand();
connection.Open();
tcommand.CommandText = "SELECT user_id, award_id FROM dbo.UsersAwards WHERE user_id= @UserID AND award_id = @AwardID ;";
tcommand.Parameters.AddWithValue("@UserID", UserID);
tcommand.Parameters.AddWithValue("@AwardID", AwardID);
using (SqlDataReader reader = tcommand.ExecuteReader())
{
while (reader.Read())
{
summary++;
}
}
if (summary == 0)
{
command.CommandText = "INSERT INTO dbo.UsersAwards (user_id, award_id) VALUES ( @UserID, @AwardID );";
command.Parameters.AddWithValue("@UserID", UserID);
command.Parameters.AddWithValue("@AwardID", AwardID);
summary = 0;
summary = command.ExecuteNonQuery();
}
else
{
return(false);
}
}
if (summary > 0)
{
return(true);
}
else
{
DBconnectException e = new DBconnectException("Can't add award to user");
LogType.AddLog(e);
return(false);
}
}
public bool Delete(Guid id)
{
try
{
var temp = Awards.FirstOrDefault(x => x.Id == id);
if (temp == null)
{
return(false);
}
Awards.Remove(temp);
return(true);
}
catch (Exception e)
{
LogType.AddLog(e);
throw new CacheAccessException("Can't delete data from cache!", e);
}
}
public bool Update(AwardDTO award)
{
try
{
var temp = Awards.FirstOrDefault(x => x.Id == award.Id);
if (temp == null)
{
return(false);
}
temp.Name = award.Name;
return(true);
}
catch (Exception e)
{
LogType.AddLog(e);
throw new CacheAccessException("Can't access to cache!", e);
}
}
public bool Update(UserDTO user)
{
try
{
var temp = Users.FirstOrDefault(x => x.Id == user.Id);
if (temp == null)
{
return(false);
}
temp.Name = user.Name;
temp.DateOfBirth = user.DateOfBirth;
return(true);
}
catch (Exception e)
{
LogType.AddLog(e);
throw new CacheAccessException("Can't save data to cache!", e);
}
}
public bool Exists(string name)
{
try
{
foreach (var item in Awards)
{
if (item.Name == name)
{
return(true);
}
}
return(false);
}
catch (Exception e)
{
LogType.AddLog(e);
throw new CacheAccessException("Can't access to cache!", e);
}
}
