public static string GetRole(int roleID)
{
var roleName = default(string);
DBConnectionSettings.GetFactorySettingsFromConfig();
using (var connection = DBConnectionSettings.factory.CreateConnection())
{
connection.ConnectionString = DBConnectionSettings.connectionString;
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "select RoleName from Roles where RoleID = @roleID;";
var parameter = command.CreateParameter();
parameter.ParameterName = "@roleID";
parameter.Value = roleID;
command.Parameters.Add(parameter);
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
roleName = reader["RoleName"].ToString();
}
}
connection.Close();
}
return(roleName);
}
public static void CreateUser(User user)
{
DBConnectionSettings.GetFactorySettingsFromConfig();
using (var connection = DBConnectionSettings.factory.CreateConnection())
{
connection.ConnectionString = DBConnectionSettings.connectionString;
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "insert into Users (UserLogin, UserPassword, Email, FirstName, LastName, RoleID, Country, City, CreationDate) "
+ "values (@userLogin, @userPassword, @email, @firstName, @lastName, @roleID, @country, @city, getdate());";
var parameter = command.CreateParameter();
parameter.ParameterName = "@userLogin";
parameter.Value = user.UserLogin;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@userPassword";
parameter.Value = OhSnapDAL.Managers.AccountManager.GetHash(user.UserPassword);
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@email";
parameter.Value = user.Email;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@firstName";
parameter.Value = user.FirstName;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@lastName";
parameter.Value = user.LastName;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@roleID";
parameter.Value = user.RoleID == default(int) ? 2 : user.RoleID;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@country";
parameter.Value = user.Country ?? (object)DBNull.Value;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@city";
parameter.Value = user.City ?? (object)DBNull.Value;
command.Parameters.Add(parameter);
command.ExecuteScalar();
connection.Close();
}
}
public static Photo GetPhotoFromDB(int photoID)
{
DBConnectionSettings.GetFactorySettingsFromConfig();
string photoName = default(string);
byte[] byteArray = default(byte[]);
string fileType = default(string);
int userID = default(int);
DateTime uploadDate = default(DateTime);
string likes = default(string);
int likesCount = default(int);
using (var connection = DBConnectionSettings.factory.CreateConnection())
{
connection.ConnectionString = DBConnectionSettings.connectionString;
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "select * from Photos where PhotoID = @photoID;";
var parameter = command.CreateParameter();
parameter.ParameterName = "@photoID";
parameter.Value = photoID;
command.Parameters.Add(parameter);
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
photoName = reader["PhotoName"].ToString();
byteArray = (byte[])reader["ByteArray"];
fileType = reader["FileType"].ToString();
userID = (int)reader["UserID"];
uploadDate = (DateTime)reader["UploadDate"];
likes = (reader["Likes"] ?? string.Empty).ToString();
likesCount = reader["LikesCount"].ToString() == string.Empty ? default(int) : (int)reader["LikesCount"];
}
}
connection.Close();
}
var photo = new Photo(photoID, photoName, byteArray, fileType, userID, uploadDate, likes, likesCount);
return(photo);
}
public static User GetUserFromDB(string userLogin)
{
DBConnectionSettings.GetFactorySettingsFromConfig();
var user = new User();
using (var connection = DBConnectionSettings.factory.CreateConnection())
{
connection.ConnectionString = DBConnectionSettings.connectionString;
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "select * from Users where UserLogin = @userLogin;";
var parameter = command.CreateParameter();
parameter.ParameterName = "@userLogin";
parameter.Value = userLogin;
command.Parameters.Add(parameter);
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
user.UserID = (int)reader["UserID"];
user.UserLogin = userLogin;
user.UserPasswordHash = (byte[])reader["UserPassword"];
user.Email = reader["Email"].ToString();
user.FirstName = reader["FirstName"].ToString();
user.LastName = reader["LastName"].ToString();
user.RoleID = (int)reader["RoleID"];
user.Country = (reader["Country"] ?? string.Empty).ToString();
user.City = (reader["City"] ?? string.Empty).ToString();
user.CreationDate = (DateTime)reader["CreationDate"];
}
}
connection.Close();
}
return(user);
}
public static void DeletePhotoFromDB(int photoID)
{
DBConnectionSettings.GetFactorySettingsFromConfig();
using (var connection = DBConnectionSettings.factory.CreateConnection())
{
connection.ConnectionString = DBConnectionSettings.connectionString;
connection.Open();
var command = connection.CreateCommand();
command.CommandText = string.Format("delete from Photos where PhotoID = @photoID;");
var parameter = command.CreateParameter();
parameter.ParameterName = "@photoID";
parameter.Value = photoID;
command.Parameters.Add(parameter);
command.ExecuteScalar();
connection.Close();
}
}
public static List <User> GetFullUserListFromDB()
{
DBConnectionSettings.GetFactorySettingsFromConfig();
var userList = new List <User>();
using (var connection = DBConnectionSettings.factory.CreateConnection())
{
connection.ConnectionString = DBConnectionSettings.connectionString;
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "select * from Users";
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var user = new User();
user.UserID = (int)reader["UserID"];
user.UserLogin = reader["UserLogin"].ToString();
user.UserPasswordHash = (byte[])reader["UserPassword"];
user.Email = reader["Email"].ToString();
user.FirstName = reader["FirstName"].ToString();
user.LastName = reader["LastName"].ToString();
user.RoleID = (int)reader["RoleID"];
user.Country = (reader["Country"] ?? string.Empty).ToString();
user.City = (reader["City"] ?? string.Empty).ToString();
user.CreationDate = (DateTime)reader["CreationDate"];
userList.Add(user);
}
}
connection.Close();
}
return(userList);
}
public static List <Photo> GetFullPhotoListFromDB()
{
DBConnectionSettings.GetFactorySettingsFromConfig();
var photoList = new List <Photo>();
using (var connection = DBConnectionSettings.factory.CreateConnection())
{
connection.ConnectionString = DBConnectionSettings.connectionString;
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "select * from Photos";
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var photoID = (int)reader["PhotoID"];
var photoName = reader["PhotoName"].ToString();
var byteArray = (byte[])reader["ByteArray"];
var fileType = reader["FileType"].ToString();
var userID = (int)reader["UserID"];
var uploadDate = (DateTime)reader["UploadDate"];
var likes = (reader["Likes"] ?? string.Empty).ToString();
var likesCount = reader["LikesCount"].ToString() == string.Empty ? default(int) : (int)reader["LikesCount"];
var photo = new Photo(photoID, photoName, byteArray, fileType, userID, uploadDate, likes, likesCount);
photoList.Add(photo);
}
}
connection.Close();
}
return(photoList);
}
public static bool IsUserValid(string userLogin, string userPassword)
{
DBConnectionSettings.GetFactorySettingsFromConfig();
var result = false;
using (var connection = DBConnectionSettings.factory.CreateConnection())
{
connection.ConnectionString = DBConnectionSettings.connectionString;
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "select * from Users where UserLogin = @userLogin and UserPassword = @userPassword;";
var parameter = command.CreateParameter();
parameter.ParameterName = "@userLogin";
parameter.Value = userLogin;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@userPassword";
parameter.Value = GetHash(userPassword);
command.Parameters.Add(parameter);
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
result = true;
}
}
connection.Close();
}
return(result);
}
public static void SavePhotoToDB(string photoName, byte[] byteArray, string fileType, int userID)
{
DBConnectionSettings.GetFactorySettingsFromConfig();
using (var connection = DBConnectionSettings.factory.CreateConnection())
{
connection.ConnectionString = DBConnectionSettings.connectionString;
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "insert into Photos (PhotoName, ByteArray, FileType, UserID, UploadDate) values (@photoName, @byteArray, @fileType ,@userID, getdate());";
var parameter = command.CreateParameter();
parameter.ParameterName = "@photoName";
parameter.Value = photoName;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@byteArray";
parameter.Value = byteArray;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@fileType";
parameter.Value = fileType;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@userID";
parameter.Value = userID;
command.Parameters.Add(parameter);
command.ExecuteScalar();
connection.Close();
}
}
public static void LikePhoto(int photoID, int userID)
{
DBConnectionSettings.GetFactorySettingsFromConfig();
var likes = default(string);
var likesCount = default(int);
using (var connection = DBConnectionSettings.factory.CreateConnection())
{
connection.ConnectionString = DBConnectionSettings.connectionString;
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "select Likes, LikesCount from Photos where PhotoID = @photoID";
var parameter = command.CreateParameter();
parameter.ParameterName = "@photoID";
parameter.Value = photoID;
command.Parameters.Add(parameter);
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
likes = (reader["Likes"] ?? string.Empty).ToString();
likesCount = reader["LikesCount"].ToString() == string.Empty ? default(int) : (int)reader["LikesCount"];
}
}
connection.Close();
}
if (!likes.Contains(string.Format(";{0};", userID)))
{
likes += string.Format(";{0};", userID);
likesCount++;
using (var connection = DBConnectionSettings.factory.CreateConnection())
{
connection.ConnectionString = DBConnectionSettings.connectionString;
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "update Photos set Likes = @likes, LikesCount = @likesCount where PhotoID = @photoID;";
var parameter = command.CreateParameter();
parameter.ParameterName = "@photoID";
parameter.Value = photoID;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@likes";
parameter.Value = likes;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@likesCount";
parameter.Value = likesCount;
command.Parameters.Add(parameter);
command.ExecuteScalar();
connection.Close();
}
}
else
{
likes = likes.Replace(string.Format(";{0};", userID), string.Empty);
likesCount--;
using (var connection = DBConnectionSettings.factory.CreateConnection())
{
connection.ConnectionString = DBConnectionSettings.connectionString;
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "update Photos set Likes = @likes, LikesCount = @likesCount where PhotoID = @photoID;";
var parameter = command.CreateParameter();
parameter.ParameterName = "@photoID";
parameter.Value = photoID;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@likes";
parameter.Value = likes;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@likesCount";
parameter.Value = likesCount;
command.Parameters.Add(parameter);
command.ExecuteScalar();
connection.Close();
}
}
}
public T GetParallelAs <T>() where T : class
{
return(DBConnectionSettings.GetParallelConnectionAs <T>("GMBDataConnection"));
}
public T GetAs <T>() where T : class
{
return(DBConnectionSettings.GetConnectionAs <T>("GMBUtilitiesConnection"));
}
public static void EditUser(User user)
{
DBConnectionSettings.GetFactorySettingsFromConfig();
using (var connection = DBConnectionSettings.factory.CreateConnection())
{
connection.ConnectionString = DBConnectionSettings.connectionString;
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "update Users set UserLogin = @userLogin, UserPassword = @userPassword, Email = @email, "
+ "FirstName = @firstName, LastName = @lastName, RoleID = @roleID, Country = @country, City = @city "
+ "where UserID = @userID;";
var parameter = command.CreateParameter();
parameter.ParameterName = "@userID";
parameter.Value = user.UserID;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@userLogin";
parameter.Value = user.UserLogin;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@userPassword";
parameter.Value = OhSnapDAL.Managers.AccountManager.GetHash(user.UserPassword);
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@email";
parameter.Value = user.Email;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@firstName";
parameter.Value = user.FirstName;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@lastName";
parameter.Value = user.LastName;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@roleID";
parameter.Value = user.RoleID;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@country";
parameter.Value = user.Country ?? (object)DBNull.Value;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@city";
parameter.Value = user.City ?? (object)DBNull.Value;
command.Parameters.Add(parameter);
command.ExecuteScalar();
connection.Close();
}
}
