public int AddNewPhoto(Photo photo)
{
string queryString = @"insert into dbo.Photos(Name, Description, UploadDate, FileName, Owner) OUTPUT Inserted.ID values (@Name, @Description, @UploadDate, @FileName, @Owner);";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(queryString, connection))
{
command.Parameters.Add(new SqlParameter("@Name", photo.Name));
command.Parameters.Add(new SqlParameter("@Description", photo.Description));
command.Parameters.Add(new SqlParameter("@UploadDate", photo.UploadDate));
command.Parameters.Add(new SqlParameter("@FileName", photo.FileName));
command.Parameters.Add(new SqlParameter("@Owner", photo.Owner));
photo.Id = (int)command.ExecuteScalar();
}
}
return photo.Id;
}
public JsonResult Upload()
{
var resultList = filesHelper.UploadAll(HttpContext);
foreach (var imageInfo in resultList)
{
var newPhoto = new Photo
{
Owner = User.Identity.Name,
UploadDate = DateTime.Now,
FileName = imageInfo.name
};
db.AddNewPhoto(newPhoto);
}
JsonFiles files = new JsonFiles(resultList);
bool isEmpty = !resultList.Any();
if (isEmpty)
{
return Json("Error");
}
return Json(files);
}
public List<Photo> GetAllPhotos(string username = "")
{
var result = new List<Photo>();
string queryString = "select * from dbo.vw_Photos"; // TODO: order by UploadDate
if (!string.IsNullOrEmpty(username))
{
queryString += "\n";
queryString += "where Owner=@Owner";
}
queryString += "\n";
queryString += "order by UploadDate desc";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(queryString, connection))
{
if (!string.IsNullOrEmpty(username))
{
command.Parameters.Add(new SqlParameter("@Owner", username));
}
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
var photo = new Photo();
photo.Id = Convert.ToInt32(reader["ID"]);
photo.Name = Convert.ToString(reader["Name"]);
photo.Description = Convert.ToString(reader["Description"]);
photo.UploadDate = Convert.ToDateTime(reader["UploadDate"]);
photo.FileName = Convert.ToString(reader["FileName"]);
photo.Owner = Convert.ToString(reader["Owner"]);
photo.Likes = Convert.ToInt32(reader["Likes"]);
result.Add(photo);
}
}
finally
{
reader.Close();
}
}
}
return result;
}
public Photo GetPhoto(int photoId)
{
var result = new List<Photo>();
string queryString = "select TOP 1 * from dbo.vw_Photos";
queryString += "\n";
queryString += "where ID=@PhotoId";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(queryString, connection))
{
command.Parameters.Add(new SqlParameter("@PhotoId", photoId));
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
var photo = new Photo();
photo.Id = Convert.ToInt32(reader["ID"]);
photo.Name = Convert.ToString(reader["Name"]);
photo.Description = Convert.ToString(reader["Description"]);
photo.UploadDate = Convert.ToDateTime(reader["UploadDate"]);
photo.FileName = Convert.ToString(reader["FileName"]);
photo.Owner = Convert.ToString(reader["Owner"]);
photo.Likes = Convert.ToInt32(reader["Likes"]);
result.Add(photo);
}
}
finally
{
reader.Close();
}
}
}
return result.Count == 1 ? result[0] : null;
}
