Example #1
0
        public DataModel.Media AddMedia(DataModel.Media newMedia)
        {
            string addQuery;

            if (newMedia.File == null)
            {
                addQuery = "INSERT INTO Song (Title, Singer) VALUES ('" + newMedia.Title + "', '" + ((Song)newMedia).Singer + "');"
                           + " SELECT CAST(scope_identity() AS int);";
            }
            else
            {
                addQuery = "INSERT INTO Song (Title, Singer, File) VALUES ('" + newMedia.Title + "', '" + ((Song)newMedia).Singer + "', '"
                           + "0x" + BitConverter.ToString(newMedia.File).Replace("-", "") + "');"
                           + " SELECT CAST(scope_identity() AS int);";
            }

            try
            {
                using (SqlConnection cn = MediaDB.GetConnection())
                {
                    using (SqlCommand cmd = new SqlCommand(addQuery, cn))
                    {
                        cn.Open();
                        newMedia.Id = (int)cmd.ExecuteScalar();
                    }
                }
            }
            catch (SqlException)
            {
                throw new SaveMediaFailedException();
            }
            return(newMedia);
        }
Example #2
0
        public bool UpdateMedia(DataModel.Media updateMedia)
        {
            int    updateCount = 0;
            string updateQuery;

            if (updateMedia.File == null)
            {
                updateQuery = "UPDATE Song SET Title = '" + updateMedia.Title + "', Singer = '" + ((Song)updateMedia).Singer + "' WHERE Id = " + updateMedia.Id;
            }
            else
            {
                updateQuery = "UPDATE Song SET Title = '" + updateMedia.Title + "', Singer = '" + ((Song)updateMedia).Singer + "', File = '"
                              + "0x" + BitConverter.ToString(updateMedia.File).Replace("-", "")
                              + "' WHERE Id = " + updateMedia.Id;
            }

            try
            {
                using (SqlConnection cn = MediaDB.GetConnection())
                {
                    using (SqlCommand cmd = new SqlCommand(updateQuery, cn))
                    {
                        cn.Open();
                        updateCount = cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException)
            {
                throw new UpdateMediaFailedException();
            }
            return(updateCount > 0);
        }
Example #3
0
        public bool DeleteMedia(DataModel.Media oldMedia)
        {
            int    deleteCount = 0;
            string deleteQuery = "DELETE FROM Song WHERE Id = " + oldMedia.Id;

            try
            {
                using (SqlConnection cn = MediaDB.GetConnection())
                {
                    using (SqlCommand cmd = new SqlCommand(deleteQuery, cn))
                    {
                        cn.Open();
                        deleteCount = cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException)
            {
                throw new RemoveMediaFailedException();
            }
            return(deleteCount > 0);
        }
Example #4
0
        public byte[] GetMediaFile(int songId)
        {
            byte[] musicFile    = null;
            string SqlStatement = "SELECT File FROM Song WHERE Id = @songId";

            try
            {
                using (SqlConnection cn = MediaDB.GetConnection())
                {
                    using (SqlCommand cmd = new SqlCommand(SqlStatement, cn))
                    {
                        cmd.Parameters.AddWithValue("@songId", songId);
                        cn.Open();
                        musicFile = (byte [])cmd.ExecuteScalar();
                    }
                }
            }
            catch (SqlException e)
            {
                throw new MediaReadFailedException(e);
            }
            return(musicFile);
        }
Example #5
0
        public ObservableCollection <DataModel.Media> GetAllMedia()
        {
            ObservableCollection <DataModel.Media> songs = new ObservableCollection <DataModel.Media>();
            string SqlStatement = "SELECT Id, Title, Singer FROM Song";

            try {
                using (SqlConnection cn = MediaDB.GetConnection())
                {
                    using (SqlCommand cmd = new SqlCommand(SqlStatement, cn))
                    {
                        cn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            int  idPos     = dr.GetOrdinal("Id");
                            int  titlePos  = dr.GetOrdinal("Title");
                            int  singerPos = dr.GetOrdinal("Singer");
                            Song song;
                            while (dr.Read())
                            {
                                song = new Song
                                {
                                    Id     = (int)dr[idPos],
                                    Title  = dr[titlePos].ToString(),
                                    Singer = dr[singerPos].ToString()
                                };
                                songs.Add(song);
                            }
                        }
                    }
                }
            }
            catch (SqlException e)
            {
                throw new MediaReadFailedException(e);
            }
            return(songs);
        }