23.04.27 Day63

MovieFinder

MySQL 사용

1) 누겟 - MySql.Data 설치
2) MySQL 스키마 생성

MainWindow.xaml과 Logics/Commons.cs 소스코드 변경

Logics/Commons.cs

using MahApps.Metro.Controls;
using MahApps.Metro.Controls.Dialogs;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;

namespace wpf11_MovieFinder.Logics
{
    public class Commons
    {
        // 연결 문자열 담을 변수
        // SQL Server용
        public static readonly string connString = "Data Source=localhost;" +
                                                    "Initial Catalog=pknu;" +
                                                    "Persist Security Info=True;"+
                                                    "User ID=sa;"+
                                                    "Password=12345";

        //MySQL용
        public static readonly string myConnString = "Server=localhost;" +
                                                     "Port=3306;" +
                                                     "Database=miniproject;" +
                                                     "Uid=root;" +
                                                     "Pwd=12345;";

        //매트로 다이얼로그창을 위한 정적 메서드
        public static async Task<MessageDialogResult> ShowMessageAsync(string title, string message, 
            MessageDialogStyle style = MessageDialogStyle.Affirmative)
        {
            return await ((MetroWindow)Application.Current.MainWindow).ShowMessageAsync(title, message, style, null);
        }
    }
}

MainWindow.xaml.cs

            try
            {
                // MySQL DB 데이터 입력(테스트용)
                using (MySqlConnection conn = new MySqlConnection(Commons.myConnString))
                {
                    if (conn.State == System.Data.ConnectionState.Closed) conn.Open();

                    var query = @"INSERT INTO FavoriteMovieItem
                                           (id
                                           ,Title
                                           ,Original_Title
                                           ,Realease_Date
                                           ,Original_Language
                                           ,Adult
                                           ,Popularity
                                           ,Vote_Average
                                           ,Poster_Path
                                           ,Overview
                                           ,Reg_Date)
                                     VALUES
                                           (@id
                                           ,@Title
                                           ,@Original_Title
                                           ,@Realease_Date
                                           ,@Original_Language
                                           ,@Adult
                                           ,@Popularity
                                           ,@Vote_Average
                                           ,@Poster_Path
                                           ,@Overview
                                           ,@Reg_Date)";
                    var insRes = 0;
                    foreach (MovieItem item in GrdResult.SelectedItems)
                    {
                        MySqlCommand cmd = new MySqlCommand(query, conn);
                        cmd.Parameters.AddWithValue("@id", item.Id);
                        cmd.Parameters.AddWithValue("@Title", item.Title);
                        cmd.Parameters.AddWithValue("@Original_Title", item.Original_Title);
                        cmd.Parameters.AddWithValue("@Realease_Date", item.Release_Date);
                        cmd.Parameters.AddWithValue("@Original_Language", item.Original_Language);
                        cmd.Parameters.AddWithValue("@Adult", item.Adult);
                        cmd.Parameters.AddWithValue("@Popularity", item.Popularity);
                        cmd.Parameters.AddWithValue("@Vote_Average", item.Vote_Average);
                        cmd.Parameters.AddWithValue("@Poster_Path", item.Poster_Path);
                        cmd.Parameters.AddWithValue("@Overview", item.Overview);
                        cmd.Parameters.AddWithValue("@Reg_Date", DateTime.Now);

                        insRes += cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                await Commons.ShowMessageAsync("오류", $"DB저장 오류 {ex.Message}");
            }

MySQL에 삽입된것을 확인할 수 있음

즐겨찾기 보기

private async void BtnViewFavorite_Click(object sender, RoutedEventArgs e)
        {
            this.DataContext = null;
            TxtMovieName.Text = string.Empty;

            List<FavoriteMovieItem> list = new List<FavoriteMovieItem>();
            try
            {
                using(SqlConnection conn = new SqlConnection(Commons.connString))
                {
                    if (conn.State == ConnectionState.Closed) conn.Open();
                    var query = @"SELECT id
                                      ,Title
                                      ,Original_Title
                                      ,Realease_Date
                                      ,Original_Language
                                      ,Adult
                                      ,Popularity
                                      ,Vote_Average
                                      ,Poster_Path
                                      ,Overview
                                      ,Reg_Date
                                  FROM FavoriteMovieItem
                                  ORDER BY id ASC";
                    var cmd = new SqlCommand(query, conn);
                    var adapter = new SqlDataAdapter(cmd);
                    var dSet = new DataSet();
                    adapter.Fill(dSet, "FavoriteMovieItem");
                    foreach(DataRow dr in dSet.Tables["FavoriteMovieItem"].Rows)
                    {
                        list.Add(new FavoriteMovieItem
                        {
                            Id = Convert.ToInt32(dr["id"]),
                            Title = Convert.ToString(dr["Title"]),
                            Original_Title = Convert.ToString(dr["Original_Title"]),
                            Original_Language = Convert.ToString(dr["Original_Language"]),
                            Adult = Convert.ToBoolean(dr["Adult"]),
                            Popularity = Convert.ToDouble(dr["Popularity"]),
                            Vote_Average = Convert.ToDouble(dr["Vote_Average"]),
                            Poster_Path = Convert.ToString(dr["Poster_Path"]),
                            Overview = Convert.ToString(dr["Overview"]),
                            Reg_Date = Convert.ToDateTime(dr["Reg_Date"])
                        });
                    }
                    this.DataContext = list;
                    isFavorite = true;
                }
            }
            catch(Exception ex)
            {
                await Commons.ShowMessageAsync("오류", $"DB조회 오류 {ex.Message}");
            }
        }

즐겨찾기 삭제

private async void BtnDelFavorite_Click(object sender, RoutedEventArgs e)
        {
            if(isFavorite==false)//DB에서 가져온 목록이 아니기에 삭제할수없도록
            {
                await Commons.ShowMessageAsync("오류", "즐겨찾기만 삭제할 수 있습니다!");
                return;
            }
            if(GrdResult.SelectedItems.Count==0)//영화 선택 X
            {
                await Commons.ShowMessageAsync("오류", "삭제할 영화를 선택하세요.");
                return;
            }
            try // 삭제
            {
                using(SqlConnection conn = new SqlConnection(Commons.connString))
                {
                    if (conn.State == ConnectionState.Closed) conn.Open();

                    var query = "DELETE FROM FavoriteMovieItem WHERE id = @id";
                    var delRes = 0;
                    foreach(FavoriteMovieItem item in GrdResult.SelectedItems)
                    {
                        SqlCommand cmd = new SqlCommand(query, conn);
                        cmd.Parameters.AddWithValue("@id", item.Id);

                        delRes += cmd.ExecuteNonQuery();
                    }
                    if(delRes==GrdResult.SelectedItems.Count)
                    {
                        await Commons.ShowMessageAsync("삭제", "DB삭제 성공!");
                    }
                    else
                    {
                        await Commons.ShowMessageAsync("삭제", "DB삭제 오류!");
                    }
                }
            }
            catch(Exception ex)
            {
                await Commons.ShowMessageAsync("오류", $"DB 삭제 오류 {ex.Message}");
            }
            BtnViewFavorite_Click(sender, e);//즐겨찾기 보기 이벤트 핸들러 실행 -> 삭제와 동시에 다시 뿌려줌
        }