Free Board Project 4 by Silverlight 5

이번에는 하나의 테이블을 추가해서 두개의 테이블을 서로 연결 시켜 놓고, CRUD를 하는 방법에 대해서 설명한다.

참고적으로.. 지금 개발하는 Silverlight 5, WCF RIA Service Support EF 4.1, SQL Server Compact 4.0을 이용한 예제 프로그램은 전세계에서 아마 이 프로그램이 유일할 듯하다.. 유사 프로그램도 없어서 하나 하나 찾아서 만드는게 쉽지 않다..그러나.. 끝까지 완성해서 실버라이트 공개 게시판이 자유롭게 만들어 지도록 하겠다...언넝 만들어야징;;(혼자 만들면 심심한데..ㅜㅜ 리플로 놀아주면 좋겠다는..쿨럭)

 
1. BOARD_LIST 테이블 추가

SL5_BOARD.Web -> Model Folder -> BOARD_LIST.cs 파일 추가

 using System;
using System.ComponentModel.DataAnnotations;

namespace SL5_BOARD.Web.Model
{
    public class BOARD_LIST
    {
        [Key]
        public int BOARD_LIST_IDX { get; set; }
        public string LIST_TITLE { get; set; }
        public string LIST_CONTENT { get; set; }
        public bool LIST_ATTACH_YN { get; set; }
        public int LIST_SEQ { get; set; }
        public int LIST_LEVEL { get; set; }
        public int LIST_VIEW_COUNT { get; set; }
        public string LIST_WRITER_NAME { get; set; }
        [DataType(DataType.Password)]  //패스워드를 저장할때 어떻게좀 해볼려고 써봤는데..잘 앙됨
        public string LIST_PASSWORD { get; set; }
        public string LIST_KIND { get; set; }
        public int LIST_COMPLETE_GB { get; set; }
        public bool LIST_OPEN_YN { get; set; }
        public int LIST_GOOD_COUNT { get; set; }
        public int LIST_PUBLIC_GB { get; set; }
        public DateTime REG_DT { get; set; }
        public int REG_IDX { get; set; }
        public DateTime? UPT_DT { get; set; }
        public int? UPT_IDX { get; set; }

        //여기가 중요!!
        //BOARD_MAIN 테이블과 연결 고리 음 일명 뽀링키가 되는데 형태가 오브젝트다
        //그리고 진짜 키 값은 바로 아래 필드에 존재하는데..사용 방법은 나중에

        public virtual BOARD_MAIN BoardMain { get; set; }
        public int BOARD_MAIN_IDX { get; set; }

        //생성자에서 초기값을 넣어 준다..귀찮으니 여기서 한번만 지정해 놓는다..int, bool형은 꼭 있어야 하더라는
        //이렇게 하는 것이 싫으면 위에 필드 타입에 ?를 붙여 주면 된다.

        public BOARD_LIST()
        {
            LIST_ATTACH_YN = false;
            LIST_SEQ = 0;
            LIST_LEVEL = 1;
            LIST_VIEW_COUNT = 0;
            LIST_COMPLETE_GB = 0;
            LIST_OPEN_YN = true;
            LIST_GOOD_COUNT = 0;
            LIST_PUBLIC_GB = 0;
        }
    }
}

2. BOARD_MAIN 테이블 수정

using System;
using System.Collections.ObjectModel;
using System.ComponentModel.DataAnnotations;

namespace SL5_BOARD.Web.Model
{
    public class BOARD_MAIN
    {
        [Key]
        public int BOARD_MAIN_IDX { get; set; }
        public string BOARD_NAME { get; set; }
        public string BOARD_TYPE { get; set; }
        public bool MEMBER_CHECK { get; set; }
        public string BOARD_DESC { get; set; }
        public string BOARD_STATE { get; set; }
        public DateTime REG_DT { get; set; }
        public int REG_IDX { get; set; }
        public DateTime? UPT_DT { get; set; }
        public int? UPT_IDX { get; set; }

        //BOARD_MAIN은 여러개의 BOARD_LIST를 가질 수 있다. 그래서 그런 연결관계에 대해서 메인에도 표시를
        //해주어야 한다. 역시 여기서도 옵저블컬렉션을 사용해서 세트를 만들었다.

        public virtual ObservableCollection<BOARD_LIST> BoardLists { get; set; }

        //BOARD_MAIN의 생성자
        public BOARD_MAIN()
        {
            BOARD_NAME = "신규 게시판";
            MEMBER_CHECK = false;
            //생성자에서 리스트 인스턴스 시켜준다.
            this.BoardLists = new ObservableCollection<BOARD_LIST>();
        }
    }
}

3. SL5_BOARD_DBCONTEXT 수정

using System.Data.Entity;
using System.Web;

namespace SL5_BOARD.Web.Model
{
    public class SL5_BOARD_DBCONTEXT : DbContext
    {
        public SL5_BOARD_DBCONTEXT()
            : base("SL5_BOARD")
        {
            if (HttpContext.Current == null)
            {
                Database.SetInitializer<SL5_BOARD_DBCONTEXT>(null);
            }
        }

        public DbSet<BOARD_MAIN> BOARD_MAINS { get; set; }

        //이넘 하나만 추가하면 된다.
        public DbSet<BOARD_LIST> BOARD_LISTS { get; set; }
    }
}

4. F6을 눌러서 빌드해 본다.

아마 100% 에러 날것이다.. 에..그 이유는 데이터 베이스에 내용이 변경되면 새로 만들어 주는 명령이 있는데..그 명령 EF4.1 버전에서 어떻게 사용하는지를 찾다가 포기했기 때문이다..ㅜㅜ 그래서 일단은 데이터베이스에 변경사항이 있을 경우 DB를 지워줘야 한다.(물론 수동이다..^^;;) 그래서 프로젝 끝나기 전까지는 웬만하면 데이터 마니 앙넣을려고 한다;;

SL5_BOARD.Web 프로젝트에 App_Data 폴더에 가서 SL5_BOARD.sdf 파일을 지우고 다시 F6을 눌러준다. 정상 적으로 빌드가 되었다면 다음으로 넘어간다.


5. SL5_BOARDDomainService.cs 파일 수정

#region BOARD_LIST
//기본적으로 전체 호출 하는 넘

public IQueryable<BOARD_LIST> GetBoardList()
{
    return this.DbContext.BOARD_LISTS;
}

//BOARD_MAIN_IDX 값을 받아서 조건에 만족하는 데이터만 반환, 참고로 LINQ 공부도 병행해야 한다..
public IQueryable<BOARD_LIST> GetBoardListByBoardMain(int BoardMainIDX)
{
    var query = from p in this.DbContext.BOARD_LISTS
                where p.BOARD_MAIN_IDX == BoardMainIDX
                orderby p.BOARD_LIST_IDX descending
                select p;

    return query.AsQueryable();
}

public void InsertBoardList(BOARD_LIST entity)
{
    DbEntityEntry<BOARD_LIST> entityEntry = this.DbContext.Entry(entity);
    if ((entityEntry.State != EntityState.Detached))
    {
        entityEntry.State = EntityState.Added;
    }
    else
    {
        this.DbContext.BOARD_LISTS.Add(entity);
    }
}

public void UpdateBoardList(BOARD_LIST entity)
{
    this.DbContext.BOARD_LISTS.AttachAsModified(entity, this.ChangeSet.GetOriginal(entity), this.DbContext);
}

public void DeleteBoardList(BOARD_LIST entity)
{
    DbEntityEntry<BOARD_LIST> entityEntry = this.DbContext.Entry(entity);
    if ((entityEntry.State != EntityState.Deleted))
    {
        entityEntry.State = EntityState.Deleted;
    }
    else
    {
        this.DbContext.BOARD_LISTS.Attach(entity);
        this.DbContext.BOARD_LISTS.Remove(entity);
    }
}
#endregion

수정 후 F6눌러서 컴파일을 꼭 해준다.


6. SL5_BOARD 프로젝트로 넘어간다.

MainPage.xaml의 디자인을 변경한다.

화면 구성은 대충 이정도 쯤인데.. 화면 구성만 바뀐것이 아니라 내부에 xaml단의 코딩이 변경되었다.

중요 변경사항은..riacontrol을 삭제, 그리드가 LayoutRoot의 DataContext에 있는 데이터를 바인딩 하도록 변경 되었다.

MainPage.xaml의 전체 소스다. 일단 지금은 타이틀리 커플드로 작업하고 차 후에 MVVM모델로 변경한다.

<UserControl x:Class="SL5_BOARD.MainPage"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    mc:Ignorable="d" xmlns:sdk="http://schemas.microsoft.com/winfx/2006/xaml/presentation/sdk"
    xmlns:my="clr-namespace:SL5_BOARD.Web.Model"
    xmlns:my1="clr-namespace:SL5_BOARD.Web.DomainService"
    d:DesignHeight="300" d:DesignWidth="400" Loaded="UserControl_Loaded">

    <Grid x:Name="LayoutRoot" Background="White">
        <Grid.RowDefinitions>
            <RowDefinition Height="Auto" />
            <RowDefinition Height="94*" />
            <RowDefinition Height="Auto" />
            <RowDefinition Height="148*" />
        </Grid.RowDefinitions>
       
        <sdk:DataGrid AutoGenerateColumns="False"
                      ItemsSource="{Binding Path=BOARD_MAINs}"
                      Name="bOARD_MAINDataGrid"
                      RowDetailsVisibilityMode="VisibleWhenSelected"
                      Grid.Row="1" SelectionChanged="bOARD_MAINDataGrid_SelectionChanged">
            <sdk:DataGrid.Columns>
                <sdk:DataGridTextColumn x:Name="bOARD_MAIN_IDXColumn" Binding="{Binding Path=BOARD_MAIN_IDX, Mode=OneWay}" Header="BOARD MAIN IDX" IsReadOnly="True" Width="SizeToHeader" />
                <sdk:DataGridTextColumn x:Name="bOARD_NAMEColumn" Binding="{Binding Path=BOARD_NAME}" Header="BOARD NAME" Width="SizeToHeader" />
                <sdk:DataGridTextColumn x:Name="bOARD_TYPEColumn" Binding="{Binding Path=BOARD_TYPE}" Header="BOARD TYPE" Width="SizeToHeader" />
                <sdk:DataGridCheckBoxColumn x:Name="mEMBER_CHECKColumn" Binding="{Binding Path=MEMBER_CHECK}" Header="MEMBER CHECK" Width="SizeToHeader" />
                <sdk:DataGridTextColumn x:Name="bOARD_DESCColumn" Binding="{Binding Path=BOARD_DESC}" Header="BOARD DESC" Width="SizeToHeader" />
                <sdk:DataGridTextColumn x:Name="bOARD_STATEColumn" Binding="{Binding Path=BOARD_STATE}" Header="BOARD STATE" Width="SizeToHeader" />
                <sdk:DataGridTemplateColumn x:Name="rEG_DTColumn" Header="REG DT" Width="SizeToHeader">
                    <sdk:DataGridTemplateColumn.CellEditingTemplate>
                        <DataTemplate>
                            <sdk:DatePicker SelectedDate="{Binding Path=REG_DT, Mode=TwoWay, NotifyOnValidationError=true, ValidatesOnExceptions=true}" />
                        </DataTemplate>
                    </sdk:DataGridTemplateColumn.CellEditingTemplate>
                    <sdk:DataGridTemplateColumn.CellTemplate>
                        <DataTemplate>
                            <TextBlock Text="{Binding Path=REG_DT, StringFormat=\{0:d\}}" />
                        </DataTemplate>
                    </sdk:DataGridTemplateColumn.CellTemplate>
                </sdk:DataGridTemplateColumn>

                <sdk:DataGridTextColumn x:Name="rEG_IDXColumn" Binding="{Binding Path=REG_IDX}" Header="REG IDX" Width="SizeToHeader" />
                <sdk:DataGridTemplateColumn x:Name="uPT_DTColumn" Header="UPT DT" Width="SizeToHeader">
                    <sdk:DataGridTemplateColumn.CellEditingTemplate>
                        <DataTemplate>
                            <sdk:DatePicker SelectedDate="{Binding Path=UPT_DT, Mode=TwoWay, NotifyOnValidationError=true, ValidatesOnExceptions=true}" />
                        </DataTemplate>
                    </sdk:DataGridTemplateColumn.CellEditingTemplate>
                    <sdk:DataGridTemplateColumn.CellTemplate>
                        <DataTemplate>
                            <TextBlock Text="{Binding Path=UPT_DT, StringFormat=\{0:d\}}" />
                        </DataTemplate>
                    </sdk:DataGridTemplateColumn.CellTemplate>
                </sdk:DataGridTemplateColumn>
                <sdk:DataGridTextColumn x:Name="uPT_IDXColumn" Binding="{Binding Path=UPT_IDX}" Header="UPT IDX" Width="SizeToHeader" />
            </sdk:DataGrid.Columns>
        </sdk:DataGrid>
        <Button Content="Insert" Height="23" HorizontalAlignment="Left" Name="button1" VerticalAlignment="Top" Width="75" Click="button1_Click" Margin="97,0,0,0" />
        <Button Content="Update" Height="23" HorizontalAlignment="Left" Margin="178,0,0,0" Name="button2" VerticalAlignment="Top" Width="75" Click="button1_Click"/>
        <Button Content="Delete" Height="23" HorizontalAlignment="Left" Margin="259,0,0,0" Name="button3" VerticalAlignment="Top" Width="75" Click="button1_Click"/>

        <sdk:DataGrid AutoGenerateColumns="False" Grid.Row="3"
                      ItemsSource="{Binding Path=BOARD_LISTs}"
                      Name="bOARD_LISTDataGrid"
                      RowDetailsVisibilityMode="VisibleWhenSelected">
            <sdk:DataGrid.Columns>
                <sdk:DataGridTextColumn x:Name="bOARD_LIST_IDXColumn" Binding="{Binding Path=BOARD_LIST_IDX, Mode=OneWay}" Header="BOARD LIST IDX" IsReadOnly="True" Width="SizeToHeader" />
                <sdk:DataGridTextColumn x:Name="bOARD_MAIN_IDXColumn1" Binding="{Binding Path=BOARD_MAIN_IDX}" Header="BOARD MAIN IDX" Width="SizeToHeader" />
                <sdk:DataGridCheckBoxColumn x:Name="lIST_ATTACH_YNColumn" Binding="{Binding Path=LIST_ATTACH_YN}" Header="LIST ATTACH YN" Width="SizeToHeader" />
                <sdk:DataGridTextColumn x:Name="lIST_COMPLETE_GBColumn" Binding="{Binding Path=LIST_COMPLETE_GB}" Header="LIST COMPLETE GB" Width="SizeToHeader" />
                <sdk:DataGridTextColumn x:Name="lIST_CONTENTColumn" Binding="{Binding Path=LIST_CONTENT}" Header="LIST CONTENT" Width="SizeToHeader" />
                <sdk:DataGridTextColumn x:Name="lIST_GOOD_COUNTColumn" Binding="{Binding Path=LIST_GOOD_COUNT}" Header="LIST GOOD COUNT" Width="SizeToHeader" />
                <sdk:DataGridTextColumn x:Name="lIST_KINDColumn" Binding="{Binding Path=LIST_KIND}" Header="LIST KIND" Width="SizeToHeader" />
                <sdk:DataGridTextColumn x:Name="lIST_LEVELColumn" Binding="{Binding Path=LIST_LEVEL}" Header="LIST LEVEL" Width="SizeToHeader" />
                <sdk:DataGridCheckBoxColumn x:Name="lIST_OPEN_YNColumn" Binding="{Binding Path=LIST_OPEN_YN}" Header="LIST OPEN YN" Width="SizeToHeader" />
                <sdk:DataGridTextColumn x:Name="lIST_PASSWORDColumn" Binding="{Binding Path=LIST_PASSWORD}" Header="LIST PASSWORD" Width="SizeToHeader" />
                <sdk:DataGridTextColumn x:Name="lIST_PUBLIC_GBColumn" Binding="{Binding Path=LIST_PUBLIC_GB}" Header="LIST PUBLIC GB" Width="SizeToHeader" />
                <sdk:DataGridTextColumn x:Name="lIST_SEQColumn" Binding="{Binding Path=LIST_SEQ}" Header="LIST SEQ" Width="SizeToHeader" />
                <sdk:DataGridTextColumn x:Name="lIST_TITLEColumn" Binding="{Binding Path=LIST_TITLE}" Header="LIST TITLE" Width="SizeToHeader" />
                <sdk:DataGridTextColumn x:Name="lIST_VIEW_COUNTColumn" Binding="{Binding Path=LIST_VIEW_COUNT}" Header="LIST VIEW COUNT" Width="SizeToHeader" />
                <sdk:DataGridTextColumn x:Name="lIST_WRITER_NAMEColumn" Binding="{Binding Path=LIST_WRITER_NAME}" Header="LIST WRITER NAME" Width="SizeToHeader" />
                <sdk:DataGridTemplateColumn x:Name="rEG_DTColumn1" Header="REG DT" Width="SizeToHeader">
                    <sdk:DataGridTemplateColumn.CellEditingTemplate>
                        <DataTemplate>
                            <sdk:DatePicker SelectedDate="{Binding Path=REG_DT, Mode=TwoWay, NotifyOnValidationError=true, ValidatesOnExceptions=true}" />
                        </DataTemplate>
                    </sdk:DataGridTemplateColumn.CellEditingTemplate>
                    <sdk:DataGridTemplateColumn.CellTemplate>
                        <DataTemplate>
                            <TextBlock Text="{Binding Path=REG_DT, StringFormat=\{0:d\}}" />
                        </DataTemplate>
                    </sdk:DataGridTemplateColumn.CellTemplate>
                </sdk:DataGridTemplateColumn>
                <sdk:DataGridTextColumn x:Name="rEG_IDXColumn1" Binding="{Binding Path=REG_IDX}" Header="REG IDX" Width="SizeToHeader" />
                <sdk:DataGridTemplateColumn x:Name="uPT_DTColumn1" Header="UPT DT" Width="SizeToHeader">
                    <sdk:DataGridTemplateColumn.CellEditingTemplate>
                        <DataTemplate>
                            <sdk:DatePicker SelectedDate="{Binding Path=UPT_DT, Mode=TwoWay, NotifyOnValidationError=true, ValidatesOnExceptions=true, TargetNullValue=''}" />
                        </DataTemplate>
                    </sdk:DataGridTemplateColumn.CellEditingTemplate>
                    <sdk:DataGridTemplateColumn.CellTemplate>
                        <DataTemplate>
                            <TextBlock Text="{Binding Path=UPT_DT, StringFormat=\{0:d\}}" />
                        </DataTemplate>
                    </sdk:DataGridTemplateColumn.CellTemplate>
                </sdk:DataGridTemplateColumn>
                <sdk:DataGridTextColumn x:Name="uPT_IDXColumn1" Binding="{Binding Path=UPT_IDX}" Header="UPT IDX" Width="SizeToHeader" />
            </sdk:DataGrid.Columns>
        </sdk:DataGrid>
        <TextBlock HorizontalAlignment="Left" Name="textBlock1" Text="BOARD MAIN :" VerticalAlignment="Center" Margin="8,4,0,3" />
        <Button Content="Insert List" Height="23" HorizontalAlignment="Left" Margin="97,6,0,0" Name="button4" VerticalAlignment="Top" Width="75" Grid.Row="2" Click="button4_Click" />
        <Button Content="Update List" Height="23" HorizontalAlignment="Left" Margin="178,6,0,0" Name="button5" VerticalAlignment="Top" Width="75" Grid.Row="2"  Click="button4_Click"/>
        <Button Content="Delete List" Height="23" HorizontalAlignment="Left" Margin="259,6,0,0" Name="button6" VerticalAlignment="Top" Width="75" Grid.Row="2"  Click="button4_Click"/>
        <TextBlock HorizontalAlignment="Left" Margin="8,10,0,9" Name="textBlock2" Text="BOARD LIST :" VerticalAlignment="Center" Grid.Row="2" />
    </Grid>
</UserControl>


7. MainPage.xaml.cs 전체

using System;
using System.Linq;
using System.ServiceModel.DomainServices.Client;
using System.Windows;
using System.Windows.Controls;
using SL5_BOARD.Web.DomainService;
using SL5_BOARD.Web.Model;

namespace SL5_BOARD
{
    public partial class MainPage : UserControl
    {
        //도메인 서비스를 사용하기 위한 변수 선언
        SL5_BOARDDomainContext dsContext;
        
        public MainPage()
        {
            InitializeComponent();

            //도메인 서비스 인스턴스
            dsContext = new SL5_BOARDDomainContext();
            //LayoutRoot.DataContext에 바인딩..이렇게 하면 하위 컨트롤들의 DataContext에도 모두 함께 바인딩 됨
            LayoutRoot.DataContext = dsContext;
        }

        //BOARD_MAIN INSERT, UPDATE, DELETE
        private void button1_Click(object sender, RoutedEventArgs e)
        {
            Button btn = sender as Button;
            switch (btn.Content.ToString())
            {
                case "Insert":
                    //riacontrol을 빼고 심플하게 정리
                    BOARD_MAIN bm = new BOARD_MAIN();
                    bm.REG_DT = DateTime.Now;
                    bm.REG_IDX = -1;
                    dsContext.BOARD_MAINs.Add(bm);
                    dsContext.SubmitChanges();
                    break;
                case "Update":
                    dsContext.SubmitChanges();
                    break;
                case "Delete":
                    //그리드에 선택된 내용 확인
                    if ((bOARD_MAINDataGrid.SelectedItem as BOARD_MAIN) != null)
                    {
                        dsContext.BOARD_MAINs.Remove(bOARD_MAINDataGrid.SelectedItem as BOARD_MAIN);
                        dsContext.SubmitChanges();
                    }
                    break;
            }
        }

        //BOARD_LIST INSERT, UPDATE, DELETE
        private void button4_Click(object sender, RoutedEventArgs e)
        {
            Button btn = sender as Button;
            switch (btn.Content.ToString())
            {
                case "Insert List":
                    if ((bOARD_MAINDataGrid.SelectedItem as BOARD_MAIN) != null)
                    {
                        BOARD_LIST bl = new BOARD_LIST();
                        dsContext.BOARD_LISTs.Add(bl);
                        bl.REG_DT = DateTime.Now;
                        bl.REG_IDX = -1;

                        //선택된 BOARD_MAIN을 여기다가 쑤셔 넣은 후 저장
                        bl.BoardMain = bOARD_MAINDataGrid.SelectedItem as BOARD_MAIN;

                        dsContext.SubmitChanges();
                    }
                    break;
                case "Update List":
                    dsContext.SubmitChanges();
                    break;
                case "Delete List":
                    if (bOARD_LISTDataGrid.SelectedItem as BOARD_LIST != null)
                    {
                        dsContext.BOARD_LISTs.Remove(bOARD_LISTDataGrid.SelectedItem as BOARD_LIST);
                        dsContext.SubmitChanges();
                    }
                    break;
            }
        }

        //화면 로딩 후 BOARD_MAIN 전체 조회
        private void UserControl_Loaded(object sender, RoutedEventArgs e)
        {
            dsContext.Load(dsContext.GetBoardMainQuery(), LoadBehavior.RefreshCurrent, true);
        }

        //그리드 셀렉션 체인지 이벤트 처리
        private void bOARD_MAINDataGrid_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            //컨테이너에 데이터가 존재 한다면
            if (dsContext.EntityContainer != null)
            {
                //리스트의 내용을 지워놓고
                dsContext.BOARD_LISTs.Clear();
                //선택된 BOARD_MAIN을 일단 넣궁
                BOARD_MAIN bm = e.AddedItems.Cast<BOARD_MAIN>().FirstOrDefault();
                //BOARD_MAIN에 속해있는 BOARD_LIST만 조회
                dsContext.Load(dsContext.GetBoardListByBoardMainQuery(bm.BOARD_MAIN_IDX), LoadBehavior.RefreshCurrent, true);
            }
        }
    }
}


8. 실행 모습

음..물론 Update, Delete 기능도 잘 동작한다.


9. 나머지 테이블도...
 후딱 만들고 본격적으로 작업에 들어가야 겠다..그런데 역시 Code First라 테이블 만들기가 매우 귀찮은..쿨럭..그냥 Model First에서 모델만들고 한번에 후루룩 만들면 더 쉬울건데..

전체 길이가 너무 길어져서..보기가 좀 불편하지만..거의 모든 소스를 올려 놓은 것이니 다시 잘 만들어 보자. 소스 요청은 리플로 이메일을 남겨주면 된다.