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; }