動作環境
Windows 8.1 Pro (64bit)
Microsoft Visual Studio 2017 Community
WPF > LocalDBの内容取得 + 書換え
http://qiita.com/7of9/items/9ffc114c9c47a0259ea0
をMVVMバージョンにしてみた。
VideModelBase.cs
@hugo-sb さんのコードを使わせていただきました。
ありがとうございます。
data
LocalDB経由でD:\SQLServer\LocalDB\sample_170625_t1150.mdf
にテーブルCustomerを作成。
code
WPF > Updateボタン押下時にDataGridに追加する > MVVM + ObservableCollection + ICommand
http://qiita.com/7of9/items/b368ed55c9d716dfae3a
のコードにLocalDB接続関連の処理を追加。
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;
namespace _170630_t1800_MVVM_Icmd_DataTable
{
/// <summary>
/// MainWindow.xaml の相互作用ロジック
/// </summary>
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
}
}
MainWindow.xaml
<Window x:Class="_170630_t1800_MVVM_Icmd_DataTable.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:_170630_t1800_MVVM_Icmd_DataTable"
mc:Ignorable="d"
Title="MainWindow" Height="350" Width="525">
<Window.DataContext>
<local:ViewModel/>
</Window.DataContext>
<Grid>
<StackPanel>
<Button Content="Update" x:Name="uxUpdate" Width="100"
Command="{Binding UpdateCommand}"/>
<DataGrid x:Name="dataGrid1" ItemsSource="{Binding myItem}"/>
</StackPanel>
</Grid>
</Window>
ViewModel.cs
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
//
using System.Collections.ObjectModel;
using System.Windows.Input;
using System.Windows;
using System.Data;
using System.Data.SqlClient;
using System.ComponentModel;
using System.Runtime.CompilerServices;
namespace _170630_t1800_MVVM_Icmd_DataTable
{
class ViewModel : ViewModelBase
{
// コンストラクタ
public ViewModel()
{
CreateTable();
//
UpdateCommand = CreateCommand(param => MyUpdateCommand());
}
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 CreateTable()
{
UpdateTable(/*bfNew=*/true);
}
public void UpdateTable(bool bfNew)
{
if (bfNew)
{
myItem = new ObservableCollection<TestItem>();
} else {
myItem.Clear();
}
DataTable TestTable = GetTable();
foreach (var row in TestTable.Rows)
{
var obj = new TestItem()
{
Id = (int)((DataRow)row).ItemArray[0],
Name = (string)((DataRow)row).ItemArray[1],
Age = (int)((DataRow)row).ItemArray[2],
Gender = (string)((DataRow)row).ItemArray[3]
};
myItem.Add(obj);
}
}
public ObservableCollection<TestItem> myItem { get; set; }
public ICommand UpdateCommand { get; private set; }
public void MyUpdateCommand()
{
if (myItem[0].Name == "7of9")
{
ExecuteNonQuery("UPDATE Customer SET name= 'Annika' WHERE Id = 1");
} else
{
ExecuteNonQuery("UPDATE Customer SET name= '7of9' WHERE Id = 1");
}
UpdateTable(/*bfNew=*/false);
}
public class TestItem
{
public int Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public string Gender { get; set; }
}
}
}
結果
さらにUpdateボタンを押していくと7of9とAnnikaの表記が交互に現れる。
Update時の処理
SQL文を実行し、LocalDB経由でmdfファイルを書き換えしている。
そのあとにmyListをいったんクリアし、myListの内容を入れなおしている。
これが標準的な方法かどうかは未消化。
参考: https://stackoverflow.com/questions/21325060/bind-observablecollection-using-mvvm