1,AA,"{1,2,3}"
2,BB,"{4,5,6}"
3,CC,
4,DD,"{10,11,12}"
というCSVファイルを読み込み、{1, 2, 3}のような配列型を分解して、DataGridに
No | Name | Favorite_0001 | Favorite_0002 | Favorite_0003 |
---|---|---|---|---|
1 | AA | 1 | 2 | 3 |
2 | BB | 4 | 5 | 6 |
3 | CC | |||
4 | DD | 10 | 11 | 12 |
のように割り当てたいです。
そして、その分解したデータのまま、PostgreSQLに書き込みたいです。
しかし、分解は出来たのですが、Favorite_0001しか定義できておらず、
No | Name | Favorite_0001 | Favorite_0002 | Favorite_0003 |
---|---|---|---|---|
1 | AA | 1 | ||
2 | BB | 4 | ||
3 | CC | |||
4 | DD | 10 |
になってしまっています。
ソース
Cat.cs
C#
1namespace WpfApp1 2{ 3 [Table("tblcat")] 4 class Cat 5 { 6 [Key] 7 [DatabaseGenerated(DatabaseGeneratedOption.None)] 8 [Column("no", Order = 2)] 9 public int No { get; set; } 10 [Key] 11 [DatabaseGenerated(DatabaseGeneratedOption.None)] 12 [Column("name", Order = 1)] 13 public String Name { get; set; } 14 [Column("favorite")] 15 public string? Favorite_0001 { get; set; } 16 //public string? Favorite_0002 { get; set; } 17 //public string? Favorite_0003 { get; set; } 18 } 19}
CatModel.cs
C#
1namespace WpfApp1 2{ 3 public class CatModel 4 { 5 public CatModel(int No, String Name, string? Favorite_0001) 6 //public CatModel(int No, String Name, string? Favorite_0001, string? Favorite_0002, string? Favorite_0003) 7 { 8 this.IsChecked = false; 9 this.No = No; 10 this.Name = Name; 11 this.Favorite_0001 = Favorite_0001; 12 //this.Favorite_0002 = Favorite_0002; 13 //this.Favorite_0003 = Favorite_0003; 14 } 15 public Boolean IsChecked { get; set; } 16 public int No { get; set; } 17 public String Name { get; set; } 18 public string? Favorite_0001 { get; set; } 19 //public string? Favorite_0002 { get; set; } 20 //public string? Favorite_0003 { get; set; } 21 } 22}
MainWindow.xaml
C#
1<Window x:Class="WpfApp1.MainWindow" 2 xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" 3 xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" 4 xmlns:d="http://schemas.microsoft.com/expression/blend/2008" 5 xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" 6 mc:Ignorable="d" 7 Title="List" Height="350" Width="750" 8 BorderThickness="1" 9 > 10 <Grid Height="350" Width="700"> 11 <DataGrid Name="dataGrid" HorizontalAlignment="Left" Margin="10,43,0,0" Width="650" Height="225"> 12 <DataGrid.Columns> 13 <DataGridTemplateColumn IsReadOnly="True" Header="Select" Width="50"> 14 <DataGridTemplateColumn.CellTemplate> 15 <DataTemplate> 16 <CheckBox IsChecked="{Binding IsChecked, Mode=TwoWay, UpdateSourceTrigger=PropertyChanged}" HorizontalAlignment="Center" VerticalAlignment="Center" /> 17 </DataTemplate> 18 </DataGridTemplateColumn.CellTemplate> 19 </DataGridTemplateColumn> 20 <DataGridTextColumn Binding="{Binding No}" ClipboardContentBinding="{x:Null}" Header="No" IsReadOnly="True" Width="50"/> 21 <DataGridTextColumn Binding="{Binding Name}" ClipboardContentBinding="{x:Null}" Header="Name" IsReadOnly="True" Width="100"/> 22 <DataGridTextColumn Binding="{Binding Favorite_0001}" ClipboardContentBinding="{x:Null}" Header="Favorite_0001" IsReadOnly="True" Width="*"/> 23 <DataGridTextColumn Binding="{Binding Favorite_0002}" ClipboardContentBinding="{x:Null}" Header="Favorite_0002" IsReadOnly="True" Width="*"/> 24 <DataGridTextColumn Binding="{Binding Favorite_0003}" ClipboardContentBinding="{x:Null}" Header="Favorite_0003" IsReadOnly="True" Width="*"/> 25 </DataGrid.Columns> 26 </DataGrid> 27 <Button x:Name="imp_button" Content="Import CSV" HorizontalAlignment="Left" Margin="250,273,0,0" VerticalAlignment="Top" Width="75" Height="30" Click="imp_button_Click"/> 28 </Grid> 29</Window>
MainWindow.xaml.cs
C#
1namespace WpfApp1 2{ 3 public partial class MainWindow : Window 4 { 5 public MainWindow() 6 { 7 InitializeComponent(); 8 using (var conn = new NpgsqlConnection("Server=localhost; Port=5432; Database=DB01;User Id=USER01;Password=USER01;")) 9 { 10 conn.Open(); 11 12 using (var command = conn.CreateCommand()) 13 { 14 StringBuilder sb = new StringBuilder(); 15 16 sb.Clear(); 17 sb.Append("CREATE TABLE IF NOT EXISTS dora.tblcat ("); 18 sb.Append(" no INTEGER NOT NULL"); 19 sb.Append(" , name VARCHAR(20) NOT NULL"); 20 sb.Append(" , favorite VARCHAR(40)"); 21 sb.Append(" , PRIMARY KEY (no, name)"); 22 sb.Append(")"); 23 24 command.CommandText = sb.ToString(); 25 command.ExecuteNonQuery(); 26 } 27 Actual_Search(); 28 conn.Close(); 29 } 30 } 31 32 private void Actual_Search() 33 { 34 BackgroundWorker worker = new BackgroundWorker(); 35 worker.DoWork += SearchProcess; 36 worker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(SearchProcessCompleted); 37 worker.RunWorkerAsync(); 38 } 39 40 private void searchData() 41 { 42 using (var context = new PgDbContext()) 43 { 44 var tblCat = context.Cats; 45 IQueryable<Cat> result; 46 result = from x in tblCat 47 select x; 48 this.dataGrid.ItemsSource = result.ToList(); 49 } 50 } 51 52 private void SearchProcess(object sender, DoWorkEventArgs e) 53 { 54 using (var context = new PgDbContext()) 55 { 56 var tblCat = context.Cats; 57 IQueryable<Cat> result; 58 result = from x in tblCat 59 select x; 60 61 List<CatModel> resultList = new List<CatModel>(); 62 foreach (Cat cat in result.ToList()) 63 { 64 resultList.Add(new CatModel(cat.No, cat.Name, cat.Favorite_0001)); 65 //resultList.Add(new CatModel(cat.No, cat.Name, cat.Favorite_0001, cat.Favorite_0002, cat.Favorite_0003)); 66 } 67 e.Result = resultList; 68 } 69 } 70 71 private void SearchProcessCompleted(object sender, RunWorkerCompletedEventArgs e) 72 { 73 this.dataGrid.ItemsSource = e.Result as List<CatModel>; 74 } 75 76 private void imp_button_Click(object sender, RoutedEventArgs e) 77 { 78 OpenFileDialog ofd = new OpenFileDialog(); 79 ofd.FileName = ""; 80 ofd.DefaultExt = "*.csv"; 81 if (ofd.ShowDialog() == false) 82 { 83 return; 84 } 85 86 List<Cat> list = readFile(ofd.FileName); 87 88 int count = 0; 89 using (var context = new PgDbContext()) 90 { 91 var table = context.Cats; 92 93 foreach (Cat cat in list) 94 { 95 if (table.SingleOrDefault(x => x.No == cat.No) == null) 96 { 97 context.Cats.Add(cat); 98 context.SaveChanges(); 99 count++; 100 } 101 } 102 } 103 MessageBox.Show(count + " / " + list.Count + " Data Imported"); 104 105 searchData(); 106 Actual_Search(); 107 } 108 109 private static List<Cat> readFile(string filePath) 110 { 111 FileInfo fileInfo = new FileInfo(filePath); 112 List<Cat> list = new List<Cat>(); 113 using (TextFieldParser tfp = new TextFieldParser(fileInfo.FullName, Encoding.GetEncoding("UTF-8"))) 114 { 115 tfp.TextFieldType = FieldType.Delimited; 116 tfp.Delimiters = new string[] { "," }; 117 tfp.HasFieldsEnclosedInQuotes = true; 118 tfp.TrimWhiteSpace = true; 119 while (!tfp.EndOfData) 120 { 121 string[] fields = tfp.ReadFields(); 122 Cat cat = new Cat(); 123 cat.No = int.Parse(fields[0]); 124 cat.Name = fields[1]; 125 var numbers = Regex.Match(fields[2], @"\{(?<numbers>[\d,]+)\}").Groups["numbers"].Value; 126 var collectionOfNumbers = numbers.Split(','); 127 cat.Favorite_0001 = collectionOfNumbers[0]; 128 //cat.Favorite_0002 = collectionOfNumbers[1]; 129 //cat.Favorite_0003 = collectionOfNumbers[2]; 130 list.Add(cat); 131 } 132 } 133 return list; 134 } 135 } 136}
PgDbContext.cs
C#
1namespace WpfApp1 2{ 3 class PgDbContext : DbContext 4 { 5 private const string ConnectionString = "Server=localhost;User ID=USER01;Password=USER01;Database=DB01;port=5432"; 6 7 public PgDbContext() : base(new NpgsqlConnection(ConnectionString), true) { } 8 9 public DbSet<Cat> Cats { get; set; } 10 11 protected override void OnModelCreating(DbModelBuilder modelBuilder) 12 { 13 modelBuilder.HasDefaultSchema("dora"); 14 Database.SetInitializer<PgDbContext>(null); 15 } 16 } 17}
試したこと
コメントアウト行の通り、Favorite_0002とFavorite_0003を追加しましたが、
PostgresException: 42703: column Extent1.Favorite_0002 does not exist
というエラーが出ます。
別にcollectionOfNumbers
を直接表示させてもいいのですが、その方法が分かりません。
また、PostgreSQLではunnest
という命令を使って配列型を分解できるようですが、
C#上でそれができるのかは分かりません。
回答1件
あなたの回答
tips
プレビュー