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 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 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 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 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 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);
}
}
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 Create(AppUserDTO user)
{
int summary;
using (var connection = new SqlConnection(UsersConnectionString))
{
var command = connection.CreateCommand();
command.CommandText = "INSERT INTO dbo.AppUsers (Username, Password) VALUES(@userName, @userPassword) ";
command.Parameters.AddWithValue("@userName", user.Name);
command.Parameters.AddWithValue("@userPassword", user.Password);
connection.Open();
summary = command.ExecuteNonQuery();
}
if (summary > 0)
{
summary = 0;
using (var connection = new SqlConnection(UsersConnectionString))
{
var command = connection.CreateCommand();
command.CommandText = "INSERT INTO dbo.UsersRoles (Username, Role) VALUES(@userName, 'user') ";
command.Parameters.AddWithValue("@userName", user.Name);
connection.Open();
summary = command.ExecuteNonQuery();
}
if (summary > 0)
{
return(true);
}
else
{
DBconnectException e = new DBconnectException("Can't add role to user");
LogType.AddLog(e);
return(false);
}
}
else
{
DBconnectException e = new DBconnectException("Can't create user");
LogType.AddLog(e);
return(false);
}
}
public bool Delete(string name)
{
int summary;
using (var connection = new SqlConnection(UsersConnectionString))
{
var command = connection.CreateCommand();
command.CommandText = "DELETE FROM dbo.AppUsers WHERE Username=@name;";
command.Parameters.AddWithValue("@name", name);
connection.Open();
summary = command.ExecuteNonQuery();
}
if (summary > 0)
{
return(true);
}
else
{
DBconnectException e = new DBconnectException("Can't delete user");
LogType.AddLog(e);
return(false);
}
}
public bool Delete(Guid id)
{
int summary;
using (var connection = new SqlConnection(UsersConnectionString))
{
var command = connection.CreateCommand();
command.CommandText = "DELETE FROM dbo.Users_Table WHERE Guid= @ID ;";
command.Parameters.AddWithValue("@ID", id);
connection.Open();
summary = command.ExecuteNonQuery();
}
if (summary > 0)
{
return(true);
}
else
{
DBconnectException e = new DBconnectException("Can't delete user-model");
LogType.AddLog(e);
return(false);
}
}
public bool Update(AppUserDTO user)
{
int summary;
using (var connection = new SqlConnection(UsersConnectionString))
{
var command = connection.CreateCommand();
command.CommandText = "UPDATE dbo.AppUsers SET Password = @password WHERE Username = @name ";
command.Parameters.AddWithValue("@password", user.Password);
command.Parameters.AddWithValue("@name", user.Name);
connection.Open();
summary = command.ExecuteNonQuery();
}
if (summary > 0)
{
return(true);
}
else
{
DBconnectException e = new DBconnectException("Can't update user");
LogType.AddLog(e);
return(false);
}
}
bool IUserListDAL.RemoveAward(Guid UserID, Guid AwardID)
{
int summary;
using (var connection = new SqlConnection(UsersConnectionString))
{
var command = connection.CreateCommand();
command.CommandText = "DELETE FROM dbo.UsersAwards WHERE user_id= @UserID AND award_id = @AwardID ;";
command.Parameters.AddWithValue("@UserID", UserID);
command.Parameters.AddWithValue("@AwardID", AwardID);
connection.Open();
summary = command.ExecuteNonQuery();
}
if (summary > 0)
{
return(true);
}
else
{
DBconnectException e = new DBconnectException("Can't remove award from user");
LogType.AddLog(e);
return(false);
}
}