//Update
public void UpdatePhoto(PhotosDO photo)
{
//Opening connection
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
//Use stored procedure to update photo
SqlCommand command = new SqlCommand("UPDATE_PHOTO", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@PhotoID", photo.PhotoId);
command.Parameters.AddWithValue("@AlbumID", photo.AlbumId);
command.Parameters.AddWithValue("@PhotoLocation", photo.PhotoLocation);
command.Parameters.AddWithValue("@PhotoName", photo.PhotoName);
command.Parameters.AddWithValue("@PhotoDate", photo.PhotoDate);
command.Parameters.AddWithValue("@Description", photo.Description);
//Open connection and excecute nonquery
connection.Open();
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw ex;
}
}
//View photos by albumId
public List <PhotosDO> ViewPhotosById(long album)
{
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
//Creating a new SqlCommand to use a stored procedure.
SqlCommand enterCommand = new SqlCommand("READ_PHOTO_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 photos object.
foreach (DataRow row in dataTable.Rows)
{
PhotosDO mappedPhotos = MapAllPhotos(row);
photos.Add(mappedPhotos);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(photos);
}
//Read
public List <PhotosDO> ReadPhotos()
{
//todo: Instanciate list here.
try
{
//Creating connection
using (SqlConnection connection = new SqlConnection(connectionString))
{
//Use stored procedure to view all albums
SqlCommand command = new SqlCommand("READ_PHOTO", 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)
{
PhotosDO mappedPhotos = new PhotosDO();
mappedPhotos.AlbumId = (long)row[0];
photos.Add(mappedPhotos);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(photos);
}
/// <summary>
/// Mapping a List from the Presentation Layer To Data Layer.
/// </summary>
/// <param name="from">List from the Presentation Layer</param>
/// <returns></returns>
public static List <PhotosDO> MapPoToDO(List <PhotosPO> from, string filePath)
{
List <PhotosDO> to = new List <PhotosDO>();
try
{
foreach (PhotosPO item in from)
{
PhotosDO mappedItem = MapPoToDO(item, filePath);
to.Add(mappedItem);
}
}
catch (Exception ex)
{
throw ex;
}
return(to);
}
public static PhotosDO MapPoToDO(PhotosPO from, string filePath)
{
PhotosDO to = new PhotosDO();
try
{
to.PhotoId = from.PhotoId;
to.AlbumId = from.AlbumId;
to.PhotoLocation = filePath;
to.PhotoName = from.PhotoName;
to.PhotoDate = from.PhotoDate;
to.Description = from.Description;
}
catch (Exception e)
{
throw e;
}
return(to);
}
public static PhotosPO MapDoToPO(PhotosDO from)
{
PhotosPO to = new PhotosPO();
try
{
to.PhotoId = from.PhotoId;
to.AlbumId = from.AlbumId;
to.PhotoLocation = from.PhotoLocation;
to.PhotoName = from.PhotoName;
to.PhotoDate = from.PhotoDate;
to.Description = from.Description;
}
catch (Exception e)
{
throw e;
}
return(to);
}
//Map all photos from a datarow to an PhotosDO
public PhotosDO MapAllPhotos(DataRow row)
{
PhotosDO mappedPhotos = new PhotosDO();
try
{
mappedPhotos.AlbumId = (long)row["AlbumId"];
mappedPhotos.PhotoId = (long)row["PhotoID"];
mappedPhotos.PhotoLocation = row["PhotoLocation"].ToString();
mappedPhotos.PhotoName = row["PhotoName"].ToString();
mappedPhotos.PhotoDate = (DateTime)row["PhotoDate"];
mappedPhotos.Description = row["Description"].ToString();
}
catch (Exception ex)
{
throw ex;
}
return(mappedPhotos);
}
//View photo by photoId
public PhotosDO ViewPhotoByPhotoId(long photo)
{
PhotosDO photoInfo = new PhotosDO();
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
//Creating a new SqlCommand to use a stored procedure.
SqlCommand enterCommand = new SqlCommand("READ_PHOTO_BY_PHOTOID", connection);
enterCommand.CommandType = CommandType.StoredProcedure;
enterCommand.Parameters.AddWithValue("@PhotoID", photo);
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 photos object.
foreach (DataRow row in dataTable.Rows)
{
photoInfo.AlbumId = (long)row["AlbumID"];
photoInfo.PhotoLocation = row["PhotoLocation"].ToString();
photoInfo.PhotoName = row["PhotoName"].ToString();
photoInfo.PhotoDate = (DateTime)row["PhotoDate"];
photoInfo.Description = row["Description"].ToString();
}
}
}
catch (Exception ex)
{
throw ex;
}
return(photoInfo);
}
