WPF > LocalDBの内容取得 + 書換え ##migrated

動作環境

Windows 8.1 Pro (64bit)
Microsoft Visual Studio 2017 Community
SQLServer LocalDB

概要

LocalDB使用
mdfファイルにてテーブル(Customer)作成
テーブルをDataGridに表示
Updateボタンにより、SQL文を用いてmdfを書き換え
- 名前を変更する

ADO.NET関連の資料やサンプルコードを読み続けているが、まだまだ消化不良。

参考

ADO.NET関連
- 猫の気ままなC#日記　さん
- http://okwakatta.net/sampleCode.html
BindableBase関連
- WPF 4.5入門 by 大田一希さん

code

以下のようにしてみた。

MainWindow.xaml.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
//
using System.Data;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using System.ComponentModel;
using System.Runtime.CompilerServices;

namespace _170629_t1935_DataTableToObsCol
{
    public partial class MainWindow : Window
    {
        private ObservableCollection<CustomerItem> myData;

        public MainWindow()
        {
            InitializeComponent();
        }

        static DataTable GetTable()
        {
            SqlConnection conn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();
            DataTable dt = new DataTable("dt_Customer");

            //列の定義
            dt.Columns.Add("ID", typeof(Int32));
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("Age", typeof(Int32));
            dt.Columns.Add("Gender", typeof(string));

            conn.ConnectionString = @"Data Source=(localdb)\ProjectsV13;
                          AttachDbFilename=D:\SQLServer\LocalDB\sample_170625_t1150.mdf;
                          Integrated Security=True;
                          Connect Timeout=30;
                          User Instance=False";

            using (SqlDataAdapter adpt = new SqlDataAdapter())
            {
                cmd.Connection = conn;
                cmd.CommandText = "SELECT * FROM Customer";
                adpt.SelectCommand = cmd;

                adpt.TableMappings.Add("Customer", "dt_Customer");
                adpt.TableMappings["Customer"].ColumnMappings.Add("Id", "Id");
                adpt.TableMappings["Customer"].ColumnMappings.Add("Name", "Name");
                adpt.TableMappings["Customer"].ColumnMappings.Add("Age", "Age");
                adpt.TableMappings["Customer"].ColumnMappings.Add("Gender", "Gender");
                //
                adpt.MissingMappingAction = MissingMappingAction.Ignore;

                adpt.Fill(dt);
            }

            return dt;
        }

        public void ExecuteNonQuery(string sql)
        {
            using (var cmd = new SqlCommand())
            {
                var conn = new SqlConnection();
                conn.ConnectionString = @"Data Source=(localdb)\ProjectsV13;
                          AttachDbFilename=D:\SQLServer\LocalDB\sample_170625_t1150.mdf;
                          Integrated Security=True;
                          Connect Timeout=30;
                          User Instance=False";

                conn.Open();
                var trs = conn.BeginTransaction(IsolationLevel.ReadCommitted);

                try
                {
                    cmd.CommandText = sql;
                    cmd.Connection = conn;
                    cmd.Transaction = trs;
                    cmd.ExecuteNonQuery();

                    trs.Commit();
                }
                catch (Exception ex)
                {
                    trs.Rollback();
                    throw;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        public void UpdateTable()
        {
            DataTable TestTable = GetTable();

            myData = new ObservableCollection<CustomerItem>();
            foreach (var row in TestTable.Rows)
            {
                var obj = new CustomerItem()
                {
                    Id = (int)((DataRow)row).ItemArray[0],
                    Name = (string)((DataRow)row).ItemArray[1],
                    Age = (int)((DataRow)row).ItemArray[2],
                    Gender = (string)((DataRow)row).ItemArray[3]
                };
                myData.Add(obj);
            }
            dataGrid1.ItemsSource = myData;
        }

        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            UpdateTable();
        }

        private void uxUpdate_Click(object sender, RoutedEventArgs e)
        {
            // change name of the first item
            if (myData[0].Name == "7of9") {
                ExecuteNonQuery("UPDATE Customer SET name= 'Annika' WHERE Id = 1");
            }
            else
            {
                ExecuteNonQuery("UPDATE Customer SET name= '7of9' WHERE Id = 1");
            }
            UpdateTable();
        }
    }

    public class BindableBase : INotifyPropertyChanged
    {
        public event PropertyChangedEventHandler PropertyChanged;

        protected virtual bool SetProperty<T>(ref T field, T value,
            [CallerMemberName]string propertyName = null)
        {
            if (Equals(field, value)) { return false; }
            field = value;
            var h = this.PropertyChanged;
            if (h != null)
            {
                h(this, new PropertyChangedEventArgs(propertyName));
            }
            return true;
        }
    }

    public class CustomerItem : BindableBase
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
        public string Gender { get; set; }
    }
}

MainWindow.xaml

<Window x:Class="_170629_t1935_DataTableToObsCol.MainWindow"
        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"
        xmlns:local="clr-namespace:_170629_t1935_DataTableToObsCol"
        mc:Ignorable="d"
        Title="MainWindow" Height="350" Width="525" Loaded="Window_Loaded">
    <Grid>
        <StackPanel>
            <Button Content="Update" x:Name="uxUpdate" Click="uxUpdate_Click"/>
            <DataGrid x:Name="dataGrid1"/>
        </StackPanel>
    </Grid>
</Window>

結果

ボタンを押すごとに7of9<->Annikaの名前変更となる。

未消化の点

UpdateTable()を使うことによりDataGridのItemsSourceを書き換えている。
この時点で画面描画が更新される。

そうなるとBindableBaseを使う意味がないような気がする。

UpdateTable()での書き換えはMVVMの作法ではないような気もする。