private BreweryPhoto GetBreweryProfilePhoto(int brewID) { string sql = @" SET LOCK_TIMEOUT 600; SELECT * FROM breweryPhotos WHERE brewery_id = @brewID and profile_pic = 1"; BreweryPhoto brewphoto = new BreweryPhoto(); try { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlCommand cmd = new SqlCommand(sql + _getLastIdSQL, conn); cmd.Parameters.AddWithValue("@brewID", brewID); var reader = cmd.ExecuteReader(); while (reader.Read()) { brewphoto = (MakeBreweryPhoto(reader)); } } } catch (Exception) { return(brewphoto); } return(brewphoto); }
private BreweryPhoto MakeBreweryPhoto(SqlDataReader reader) { BreweryPhoto breweryPhoto = new BreweryPhoto(); breweryPhoto.BreweryPhotoID = Convert.ToInt32(reader["BreweryPhotoID"]); breweryPhoto.Filename = Convert.ToString(reader["FILE_NAME"]); breweryPhoto.BreweryID = Convert.ToInt32(reader["brewery_id"]); breweryPhoto.ProfilePic = Convert.ToBoolean(reader["profile_pic"]); breweryPhoto.BreweryPhotoID = 0; var nullCheck = (reader["FILE_NAME"]); if (nullCheck != DBNull.Value) { breweryPhoto.Filename = Convert.ToString(reader["FILE_NAME"]); } else { breweryPhoto.Filename = "empty"; } return(breweryPhoto); }
public int GetLastAddedBrewPhotoID(int brewID) { int photoID = 0; BreweryPhoto photo = new BreweryPhoto(); //string sql = "select SCOPE_IDENTITY() as BreweryPhotoID, FILE_NAME, brewery_id,profile_pic from breweryPhotos where brewery_id = @brewID"; string sql = "SELECT cast(max(BreweryPhotoID) as int) as BreweryPhotoID from breweryPhotos where brewery_id=@brewID"; //SELECT COALESCE(max(BreweryPhotoID), 1) FROM breweryPhotos WHERE brewery_id = 1; using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@brewID", brewID); photoID = (int)cmd.ExecuteScalar(); } return(photoID); }