public void AddAlbum(AlbumDO newAlbum)
{
SqlConnection connectionToSql = null;
SqlCommand storedProcedure = null;
try
{
connectionToSql = new SqlConnection(_connectionString);
storedProcedure = new SqlCommand("ADD_ALBUM", connectionToSql);
storedProcedure.CommandType = System.Data.CommandType.StoredProcedure;
connectionToSql.Open();
storedProcedure.Parameters.AddWithValue("@AlbumName", newAlbum.AlbumName);
storedProcedure.Parameters.AddWithValue("@AlbumType", newAlbum.AlbumType);
storedProcedure.Parameters.AddWithValue("@UserID", newAlbum.UserID);
storedProcedure.ExecuteNonQuery();
}
catch (Exception ex)
{
LogFile.DataFile(ex: ex);
}
finally
{
if (connectionToSql != null)
{
connectionToSql.Close();
connectionToSql.Dispose();
}
}
}
//Update
public void UpdateAlbum(AlbumDO album)
{
//Opening connection
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
//Use stored procedure to update album
SqlCommand command = new SqlCommand("UPDATE_ALBUM", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@AlbumID", album.AlbumId);
command.Parameters.AddWithValue("@UserID", album.UserId);
command.Parameters.AddWithValue("@AlbumName", album.AlbumName);
command.Parameters.AddWithValue("@AlbumDescription", album.AlbumDescription);
//Open connection and excecute nonquery
connection.Open();
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw ex;
}
}
//Read
public List <AlbumDO> ReadAlbum()
{
try
{
//Opening connection
using (SqlConnection connection = new SqlConnection(connectionString))
{
//Use stored procedure to view all albums
SqlCommand command = new SqlCommand("READ_ALBUM", connection);
command.CommandType = CommandType.StoredProcedure;
connection.Open();
//Using adapter to get table from the datbase
DataTable dataTable = new DataTable();
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(dataTable);
adapter.Dispose();
}
//Put datarow into a list of AlbumDO
foreach (DataRow row in dataTable.Rows)
{
AlbumDO mappedAlbum = MapAllAlbums(row);
albums.Add(mappedAlbum);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(albums);
}
public static AlbumBO AlbumDOtoBO(AlbumDO from)
{
AlbumBO to = new AlbumBO();
to.AlbumID = from.AlbumID;
to.AlbumName = from.AlbumName;
to.AlbumType = from.AlbumType;
to.UserID = from.UserID;
to.PhotoID = from.PhotoID;
to.Photo = from.Photo;
to.PhotoName = from.PhotoName;
return(to);
}
public List <AlbumDO> ReadAllAlbums()
{
List <AlbumDO> albumsList = new List <AlbumDO>();
SqlConnection connectionToSql = null;
SqlCommand storedProcedure = null;
try
{
connectionToSql = new SqlConnection(_connectionString);
storedProcedure = new SqlCommand("READ_ALL_ALBUMS", connectionToSql);
storedProcedure.CommandType = System.Data.CommandType.StoredProcedure;
connectionToSql.Open();
SqlDataReader sqlDataReader = storedProcedure.ExecuteReader();
while (sqlDataReader.Read())
{
AlbumDO album = new AlbumDO();
album.AlbumID = (int)sqlDataReader["AlbumID"];
album.AlbumName = sqlDataReader["AlbumName"] as string;
album.AlbumType = sqlDataReader["AlbumType"] as string;
if (sqlDataReader["UserId"] != DBNull.Value)
{
album.UserID = (long)sqlDataReader["UserID"];
}
else
{
//Nothing at this time
}
albumsList.Add(album);
}
}
catch (Exception ex)
{
LogFile.DataFile(ex: ex);
throw ex;
}
finally
{
if (connectionToSql != null)
{
connectionToSql.Close();
connectionToSql.Dispose();
}
}
return(albumsList);
}
public List <AlbumDO> ViewPhotosInAlbum(int albumID)
{
List <AlbumDO> albumsList = new List <AlbumDO>();
SqlConnection connectionToSql = null;
SqlCommand storedProcedure = null;
try
{
connectionToSql = new SqlConnection(_connectionString);
storedProcedure = new SqlCommand("VIEW_PHOTOS_IN_ALBUM", connectionToSql);
storedProcedure.Parameters.AddWithValue("@AlbumID", albumID);
storedProcedure.CommandType = System.Data.CommandType.StoredProcedure;
connectionToSql.Open();
SqlDataReader sqlDataReader = storedProcedure.ExecuteReader();
while (sqlDataReader.Read())
{
AlbumDO album = new AlbumDO();
albumsList.Add(album);
album.AlbumName = sqlDataReader["AlbumName"] as string;
album.AlbumType = sqlDataReader["AlbumType"] as string;
album.PhotoID = (int)sqlDataReader["PhotoID"];
album.Photo = sqlDataReader["Photo"] as string;
album.PhotoName = sqlDataReader["PhotoName"] as string;
if (sqlDataReader["UserId"] != DBNull.Value)
{
album.UserID = (long)sqlDataReader["UserID"];
}
}
sqlDataReader.Close();
}
catch (Exception ex)
{
LogFile.DataFile(ex: ex);
throw ex;
}
finally
{
if (connectionToSql != null)
{
connectionToSql.Close();
connectionToSql.Dispose();
}
}
return(albumsList);
}
public static AlbumDO MapPoToDO(AlbumPO from)
{
AlbumDO to = new AlbumDO();
try
{
to.AlbumId = from.AlbumId;
to.UserId = from.UserId;
to.AlbumName = from.AlbumName;
to.AlbumDescription = from.AlbumDescription;
}
catch (Exception e)
{
throw e;
}
return(to);
}
/// <summary>
/// Mapping a List from the Presentation Layer
/// </summary>
/// <param name="from">List from the Presentation Layer</param>
/// <returns></returns>
public static List <AlbumDO> MapPoToDO(List <AlbumPO> from)
{
List <AlbumDO> to = new List <AlbumDO>();
try
{
foreach (AlbumPO item in from)
{
AlbumDO mappedItem = MapPoToDO(item);
to.Add(mappedItem);
}
}
catch (Exception e)
{
throw e;
}
return(to);
}
public AlbumDO ReadAlbumByID(int albumID)
{
AlbumDO album = new AlbumDO();
SqlConnection connectionToSql = null;
SqlCommand storedProcedure = null;
try
{
connectionToSql = new SqlConnection(_connectionString);
storedProcedure = new SqlCommand("READ_ALBUM_BY_ID", connectionToSql);
storedProcedure.Parameters.AddWithValue("@AlbumID", albumID);
storedProcedure.CommandType = System.Data.CommandType.StoredProcedure;
connectionToSql.Open();
SqlDataReader sqlDataReader = storedProcedure.ExecuteReader();
sqlDataReader.Read();
album.AlbumID = (int)sqlDataReader["AlbumID"];
album.AlbumName = sqlDataReader["AlbumName"] as string;
album.AlbumType = sqlDataReader["AlbumType"] as string;
if (sqlDataReader["UserId"] != DBNull.Value)
{
album.UserID = (long)sqlDataReader["UserID"];
}
sqlDataReader.Close();
}
catch (Exception ex)
{
LogFile.DataFile(ex: ex);
throw ex;
}
finally
{
if (connectionToSql != null)
{
connectionToSql.Close();
connectionToSql.Dispose();
}
}
return(album);
}
//Map all the albums from a datarow to an AlbumDO
public AlbumDO MapAllAlbums(DataRow row)
{
AlbumDO mappedAlbums = new AlbumDO();
try
{
if (row["AlbumID"] != null)
{
mappedAlbums.AlbumId = (long)row["AlbumID"];
}
mappedAlbums.AlbumName = row["AlbumName"].ToString();
mappedAlbums.AlbumDescription = row["AlbumDescription"].ToString();
}
catch (Exception ex)
{
throw ex;
}
return(mappedAlbums);
}
//Count photos by AlbumId
public AlbumDO CountPhotos(long albumId)
{
AlbumDO count = new AlbumDO();
try
{
//Creating connection
using (SqlConnection connection = new SqlConnection(connectionString))
{
//Use stored procedure to view all albums
SqlCommand command = new SqlCommand("COUNT_PHOTOS_BY_ALBUMID", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@AlbumId", albumId);
connection.Open();
command.ExecuteNonQuery();
//Using adapter to get table from the datbase
DataTable dataTable = new DataTable();
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(dataTable);
adapter.Dispose();
}
//Put datarow into a list of PhotosDO
foreach (DataRow row in dataTable.Rows)
{
AlbumDO mappedCount = new AlbumDO();
mappedCount.PhotoCount = (int)row[0];
mappedCount.AlbumId = albumId;
count = mappedCount;
}
}
}
catch (Exception ex)
{
throw ex;
}
return(count);
}
//View album by albumid
public AlbumDO ViewAlbumById(long album)
{
AlbumDO userAlbum = new AlbumDO();
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
//Creating a new SqlCommand to use a stored procedure.
SqlCommand enterCommand = new SqlCommand("ALBUM_BY_ALBUMID", connection);
enterCommand.CommandType = CommandType.StoredProcedure;
enterCommand.Parameters.AddWithValue("@AlbumID", album);
connection.Open();
//Using SqlDataAdapter to get SQL table.
DataTable dataTable = new DataTable();
using (SqlDataAdapter userAdapter = new SqlDataAdapter(enterCommand))
{
userAdapter.Fill(dataTable);
userAdapter.Dispose();
}
//Putting datarow into a List of the album object.
foreach (DataRow row in dataTable.Rows)
{
userAlbum.UserId = (long)row["UserID"];
userAlbum.AlbumName = row["AlbumName"].ToString();
userAlbum.AlbumDescription = row["AlbumDescription"].ToString();
}
}
}
catch (Exception ex)
{
throw ex;
}
return(userAlbum);
}
//View album by userid
public List <AlbumDO> ViewAlbumByUserId(long user)
{
List <AlbumDO> userAlbum = new List <AlbumDO>();
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
//Creating a new SqlCommand to use a stored procedure.
SqlCommand enterCommand = new SqlCommand("ALBUMS_BY_USERID", connection);
enterCommand.CommandType = CommandType.StoredProcedure;
enterCommand.Parameters.AddWithValue("@UserID", user);
connection.Open();
//Using SqlDataAdapter to get SQL table.
DataTable dataTable = new DataTable();
using (SqlDataAdapter userAdapter = new SqlDataAdapter(enterCommand))
{
userAdapter.Fill(dataTable);
userAdapter.Dispose();
}
//Putting datarow into a List of the album object.
foreach (DataRow row in dataTable.Rows)
{
AlbumDO mappedAlbum = MapAllAlbums(row);
userAlbum.Add(mappedAlbum);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(userAlbum);
}
