【WPF】PostgreSQL に接続してデータを取得して表示する

2017年8月8日2019年1月29日C#,開発

おはようございます。

今回は、PostgreSQLを使ってデータを検索、データグリッドに表示してみます。
お決まりですが、プログラムは前回までのものを流用します。

【WPF】ProgressRingを使って時間のかかる処理を分かりやすくする | ドラブロ –

今回は、WEBでよく見かける処理中のローディング画像をWPF画面でも表示できるようにしてみました。

https://www.doraxdora.com/blog/2017/07/28/post-1873/

また、PostgreSQLをインストールしていない場合は次の記事を参照して、パソコンにインストールしてください。

PostgreSQL 9.6.3 をインストールしてテーブルを作成する | ドラブロ – let byg

PostgreSQLのダウンロード、インストール方法、DB、テーブル作成からデータの登録、抽出までをやってみ ...

https://www.doraxdora.com/blog/2017/07/25/post-1748/

1. Nuget でパッケージをダウンロード
2. スキーマの作成
3. テーブルの作成
4. 設定ファイルの修正
5. プログラム修正
6. 起動してみる
7. まとめ

Nuget でパッケージをダウンロード

ソリューションエクスプローラーからプロジェクトを選択、右クリックし
「Nuget パッケージの管理」を選択

Nuget パッケージ管理画面が表示されるので、
検索窓に「Npgsql 6」を入力し、「EntityFramework6.Npgsql」を選択、
インストールボタンをクリックします。

プレビュー画面が表示される場合は「OK」ボタンをクリックします。

出力ビューに「終了」が表示されれば完了です。

スキーマの作成

PostgreSQL ではスキーマ単位の管理をしておかないと困ることになります。

スキーマ指定しないでテーブル作成すると「public」スキーマに作成されるのですが、
C#からの接続時には必ず（やってみた限りでは）スキーマ指定しないといけないので、まずはスキーマを作成しておきます。

CREATE SCHEMA dora;

1	CREATESCHEMAdora;

テーブルの作成

作成したスキーマににテーブルを作成
SQLite の時と同じテーブル、データを用意します。

このとき、テーブル名やカラム名は小文字にしておくこと。

CREATE TABLE IF NOT EXISTS dora.mstkind (
        kind_cd CHAR(2) NOT NULL
        , kind_name VARCHAR(20)
        , primary key (kind_cd)
);


CREATE TABLE IF NOT EXISTS dora.tblcat (
        no INTEGER NOT NULL
        , name VARCHAR(20) NOT NULL
        , sex CHAR(3) NOT NULL
        , age INTEGER DEFAULT 0 NOT NULL
        , kind_cd CHAR(2) DEFAULT '00' NOT NULL
        , favorite VARCHAR(40)
        , PRIMARY KEY (no)
);

INSERT INTO DORA.MSTKIND VALUES ('01', 'キジトラ');
INSERT INTO DORA.MSTKIND VALUES ('02', '長毛種（不明）');
INSERT INTO DORA.MSTKIND VALUES ('03', 'ミケ（っぽい）');
INSERT INTO DORA.MSTKIND VALUES ('04', 'サビ');
INSERT INTO DORA.MSTKIND VALUES ('09', 'その他');
INSERT INTO DORA.TBLCAT VALUES('1','そら','♂','6','01','犬の人形');
INSERT INTO DORA.TBLCAT VALUES('2','りく','♂','5','02','人間');
INSERT INTO DORA.TBLCAT VALUES('3','うみ','♀','4','03','高級ウェットフード');
INSERT INTO DORA.TBLCAT VALUES('4','こうめ','♀','2','04','横取りフード');

CREATETABLEIFNOTEXISTSdora.mstkind(

kind_cdCHAR(2)NOT NULL

,kind_nameVARCHAR(20)

,primarykey(kind_cd)

);

CREATETABLEIFNOTEXISTSdora.tblcat(

noINTEGERNOT NULL

,nameVARCHAR(20)NOT NULL

,sexCHAR(3)NOT NULL

,ageINTEGERDEFAULT0NOT NULL

,kind_cdCHAR(2)DEFAULT'00'NOT NULL

,favoriteVARCHAR(40)

,PRIMARYKEY(no)

);

INSERTINTODORA.MSTKINDVALUES('01','キジトラ');

INSERTINTODORA.MSTKINDVALUES('02','長毛種（不明）');

INSERTINTODORA.MSTKINDVALUES('03','ミケ（っぽい）');

INSERTINTODORA.MSTKINDVALUES('04','サビ');

INSERTINTODORA.MSTKINDVALUES('09','その他');

INSERTINTODORA.TBLCATVALUES('1','そら','♂','6','01','犬の人形');

INSERTINTODORA.TBLCATVALUES('2','りく','♂','5','02','人間');

INSERTINTODORA.TBLCATVALUES('3','うみ','♀','4','03','高級ウェットフード');

INSERTINTODORA.TBLCATVALUES('4','こうめ','♀','2','04','横取りフード');

設定ファイルの修正

次のようにします。
重要な部分は、「entityFramework」タグと「system.data」タグ。

App.config

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
  </startup>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v13.0" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
      <provider invariantName="Npgsql" type="Npgsql.NpgsqlServices, EntityFramework6.Npgsql" />
    </providers>
  </entityFramework>
  <system.data>
    <DbProviderFactories>
      <remove invariant="Npgsql" />
      <add name="Npgsql Data Provider" invariant="Npgsql" support="FF" description=".Net Framework Data Provider for Postgresql" type="Npgsql.NpgsqlFactory, Npgsql" />
    </DbProviderFactories>
</system.data>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="Npgsql" publicKeyToken="5d8b90d52f46fda7" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-3.2.5.0" newVersion="3.2.5.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>

<?xml version="1.0"encoding="utf-8"?>

</configSections>

</startup>

</parameters>

</defaultConnectionFactory>

</providers>

</entityFramework>

<system.data>

</DbProviderFactories>

</system.data>

</dependentAssembly>

</assemblyBinding>

</runtime>

</configuration>

プログラム修正

新規クラスの追加

DbContextを継承したクラスを作成します。
このクラスでデータベースへの接続、Entityへのマッピングなどを行います。
デフォルトのスキーマを変更する場合、「OnModelCreating」メソッドにて設定可能です。

PgDbContext.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data.Entity;
using Npgsql;

namespace WpfApp1
{
    class PgDbContext : DbContext
    {
        private const string ConnectionString = "Server=localhost;User ID=USER01;Password=USER01;Database=DB01;port=5432";

        // コンストラクタにて接続文字列を設定
        public PgDbContext() : base(new NpgsqlConnection(ConnectionString), true) { }

        public DbSet<Kind> Kinds { get; set; }
        public DbSet<Cat> Cats { get; set; }

        // スキーマを変更する場合にはここに設定
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            //Configure default schema
            modelBuilder.HasDefaultSchema("dora");
        }
    }
}

usingSystem;

usingSystem.Collections.Generic;

usingSystem.Linq;

usingSystem.Text;

usingSystem.Threading.Tasks;

usingSystem.Data.Entity;

usingNpgsql;

namespaceWpfApp1

{

classPgDbContext:DbContext

{

privateconststringConnectionString="Server=localhost;User ID=USER01;Password=USER01;Database=DB01;port=5432";

// コンストラクタにて接続文字列を設定

publicPgDbContext():base(newNpgsqlConnection(ConnectionString),true){}

publicDbSet<Kind>Kinds{get;set;}

publicDbSet<Cat>Cats{get;set;}

// スキーマを変更する場合にはここに設定

protectedoverridevoidOnModelCreating(DbModelBuilder modelBuilder)

{

//Configure default schema

modelBuilder.HasDefaultSchema("dora");

}

Entityクラスの修正

アノテーションが今までのとちょっと違うので修正します。

Kind.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace WpfApp1
{
    [Table("mstkind")]
    class Kind
    {
        [Key]
        [Column("kind_cd")]
        public String KindCd { get; set; }
        [Column("kind_name")]
        public String KindName { get; set; }
    }
}

usingSystem;

usingSystem.Collections.Generic;

usingSystem.Linq;

usingSystem.Text;

usingSystem.Threading.Tasks;

usingSystem.ComponentModel.DataAnnotations;

usingSystem.ComponentModel.DataAnnotations.Schema;

namespaceWpfApp1

{

[Table("mstkind")]

classKind

{

[Key]

[Column("kind_cd")]

publicStringKindCd{get;set;}

[Column("kind_name")]

publicStringKindName{get;set;}

}

Cat.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace WpfApp1
{
    [Table("tblcat")]
    class Cat
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        [Column("no")]
        public int No { get; set; }
        [Column("name")]
        public String Name { get; set; }
        [Column("sex")]
        public String Sex { get; set; }
        [Column("age")]
        public int Age { get; set; }
        [Column("kind_cd")]
        public String Kind { get; set; }
        [Column("favorite")]
        public String Favorite { get; set; }
    }
}

usingSystem;

usingSystem.Collections.Generic;

usingSystem.Linq;

usingSystem.Text;

usingSystem.Threading.Tasks;

usingSystem.ComponentModel.DataAnnotations;

usingSystem.ComponentModel.DataAnnotations.Schema;

namespaceWpfApp1

{

[Table("tblcat")]

classCat

{

[Key]

[DatabaseGenerated(DatabaseGeneratedOption.None)]

[Column("no")]

publicintNo{get;set;}

[Column("name")]

publicStringName{get;set;}

[Column("sex")]

publicStringSex{get;set;}

[Column("age")]

publicintAge{get;set;}

[Column("kind_cd")]

publicStringKind{get;set;}

[Column("favorite")]

publicStringFavorite{get;set;}

}

宣言の追加

MainWindow.xaml.cs

using Npgsql;
using System.ComponentModel;

1 2	usingNpgsql; usingSystem.ComponentModel;

接続処理の変更

テーブル作成以外、全ての箇所を変更します。

MainWindow.xaml.cs

//using (var conn = new SQLiteConnection("Data Source=SampleDb.sqlite"))
using (var context = new PgDbContext())

1 2	//using (var conn = new SQLiteConnection("Data Source=SampleDb.sqlite")) using(varcontext=newPgDbContext())

テーブル作成は今まで通りコマンドで行います。
（また別途、DbContext でのやり方は紹介します）

テーブル作成クエリの変更

SQLite とは微妙に違うところ（型、長さ）を変更。

MainWindow.xaml.cs

// テーブルが存在しなければ作成する
// 種別マスタ
StringBuilder sb = new StringBuilder();
sb.Append("CREATE TABLE IF NOT EXISTS mstkind (");
sb.Append(" kind_cd CHAR(2) NOT NULL");
sb.Append(" , kind_name VARCHAR(20)");
sb.Append(" , PRIMARY KEY (kind_cd)");
sb.Append(")");
command.CommandText = sb.ToString();
command.ExecuteNonQuery();

// 猫テーブル
sb.Clear();
sb.Append("CREATE TABLE IF NOT EXISTS tblcat (");
sb.Append(" no INTEGER NOT NULL");
sb.Append(" , name VARCHAR(20) NOT NULL");
sb.Append(" , sex CHAR(3) NOT NULL");
sb.Append(" , age INTEGER DEFAULT 0 NOT NULL");
sb.Append(" , kind_cd CHAR(2) DEFAULT '00' NOT NULL");
sb.Append(" , favorite VARCHAR(40)");
sb.Append(" , PRIMARY KEY (no)");
sb.Append(")");

command.CommandText = sb.ToString();
command.ExecuteNonQuery();

// テーブルが存在しなければ作成する

// 種別マスタ

StringBuilder sb=newStringBuilder();

sb.Append("CREATE TABLE IF NOT EXISTS mstkind (");

sb.Append(" kind_cd CHAR(2) NOT NULL");

sb.Append(" , kind_name VARCHAR(20)");

sb.Append(" , PRIMARY KEY (kind_cd)");

sb.Append(")");

command.CommandText=sb.ToString();

command.ExecuteNonQuery();

// 猫テーブル

sb.Clear();

sb.Append("CREATE TABLE IF NOT EXISTS tblcat (");

sb.Append(" no INTEGER NOT NULL");

sb.Append(" , name VARCHAR(20) NOT NULL");

sb.Append(" , sex CHAR(3) NOT NULL");

sb.Append(" , age INTEGER DEFAULT 0 NOT NULL");

sb.Append(" , kind_cd CHAR(2) DEFAULT '00' NOT NULL");

sb.Append(" , favorite VARCHAR(40)");

sb.Append(" , PRIMARY KEY (no)");

sb.Append(")");

command.CommandText=sb.ToString();

command.ExecuteNonQuery();

検索処理の修正

MainWindow.xaml.cs

        /// <summary>
        /// 検索処理（非同期）
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void SearchProcess(object sender, DoWorkEventArgs e)
        {
            // 猫データマスタを取得してコンボボックスに設定する
            //using (var conn = new SQLiteConnection("Data Source=SampleDb.sqlite"))
            // PgDbContext に変更
            using (var context = new PgDbContext())
            {
                String searchName = this.search_name.Text;
                String searchKind = (this.search_kind.SelectedValue as Kind).KindCd;

                // データを取得
                // context から DbSet を取得
                //Table<Cat> tblCat = con.GetTable<Cat>();
                var tblCat = context.Cats;

                // サンプルなので適当に組み立てる
                IQueryable<Cat> result;
                if (searchKind == "")
                {
                    // 名前は前方一致のため常に条件していしても問題なし
                    result = from x in tblCat
                             where x.Name.StartsWith(searchName)
                             orderby x.No
                             select x;
                }
                else
                {
                    result = from x in tblCat
                             where x.Name.StartsWith(searchName) & x.Kind == searchKind
                             orderby x.No
                             select x;

                }
                this.dataGrid.ItemsSource = result.ToList();
            }
        }

/// <summary>

/// 検索処理（非同期）

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

privatevoidSearchProcess(objectsender,DoWorkEventArgse)

{

// 猫データマスタを取得してコンボボックスに設定する

//using (var conn = new SQLiteConnection("Data Source=SampleDb.sqlite"))

// PgDbContext に変更

using(varcontext=newPgDbContext())

{

StringsearchName=this.search_name.Text;

StringsearchKind=(this.search_kind.SelectedValue asKind).KindCd;

// データを取得

// context から DbSet を取得

//Table<Cat> tblCat = con.GetTable<Cat>();

vartblCat=context.Cats;

// サンプルなので適当に組み立てる

IQueryable<Cat>result;

if(searchKind=="")

{

// 名前は前方一致のため常に条件していしても問題なし

result=fromxintblCat

wherex.Name.StartsWith(searchName)

orderbyx.No

selectx;

}

else

{

result=fromxintblCat

wherex.Name.StartsWith(searchName)& x.Kind == searchKind

orderby x.No

select x;

}

this.dataGrid.ItemsSource=result.ToList();

}

コンバーターの修正

データグリッドに表示する際に値を変換している箇所も、DB接続しているため変更します。

KindConverter.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data.Linq;
using System.Data.SQLite;
using System.Data.SQLite.Linq;

// Converter 用
// IValueConverter、CultureInfo
using System.Windows.Data;
using System.Globalization;


namespace WpfApp1
{
    /// <summary>
    /// 種別コンバータークラス.
    /// </summary>
    public class KindConverter : IValueConverter
    {
        /// <summary>
        /// データ変換処理
        /// </summary>
        /// <param name="value"></param>
        /// <param name="targetType"></param>
        /// <param name="parameter"></param>
        /// <param name="culture"></param>
        /// <returns></returns>
        public object Convert(object value, Type targetType, object parameter, CultureInfo culture)
        {
            //using (var conn = new SQLiteConnection("Data Source=SampleDb.sqlite"))
            // PgDbContext に変更
            using (var context = new PgDbContext())
            {
                // データを取得
                //Table<Kind> tblCat = con.GetTable<Kind>();
                //Kind k = tblCat.Single(c => c.KindCd == value as String);
                var kind = context.Kinds.SingleOrDefault(c => c.KindCd == (String)value);
                if (kind != null)
                {
                    return kind.KindName;
                }
                return "";
            }
        }

        /// <summary>
        /// データ復元処理
        /// </summary>
        /// <param name="value"></param>
        /// <param name="targetType"></param>
        /// <param name="parameter"></param>
        /// <param name="culture"></param>
        /// <returns></returns>
        public object ConvertBack(object value, Type targetType, object parameter, CultureInfo culture)
        {
            using (var context = new PgDbContext())
            {
                // データを取得
                //Table<Kind> tblCat = con.GetTable<Kind>();
                //Kind k = tblCat.Single(c => c.KindCd == value as String);
                var kind = context.Kinds.SingleOrDefault(c => c.KindName == value as String);
                if (kind != null)
                {
                    return kind.KindCd;
                }

            }
            return "";
        }
    }
}

usingSystem;

usingSystem.Collections.Generic;

usingSystem.Linq;

usingSystem.Text;

usingSystem.Threading.Tasks;

usingSystem.Data.Linq;

usingSystem.Data.SQLite;

usingSystem.Data.SQLite.Linq;

// Converter 用

// IValueConverter、CultureInfo

usingSystem.Windows.Data;

usingSystem.Globalization;

namespaceWpfApp1

{

/// <summary>

/// 種別コンバータークラス.

/// </summary>

publicclassKindConverter:IValueConverter

{

/// <summary>

/// データ変換処理

/// </summary>

/// <param name="value"></param>

/// <param name="targetType"></param>

/// <param name="parameter"></param>

/// <param name="culture"></param>

/// <returns></returns>

publicobjectConvert(objectvalue,Type targetType,objectparameter,CultureInfo culture)

{

//using (var conn = new SQLiteConnection("Data Source=SampleDb.sqlite"))

// PgDbContext に変更

using(varcontext=newPgDbContext())

{

// データを取得

//Table<Kind> tblCat = con.GetTable<Kind>();

//Kind k = tblCat.Single(c => c.KindCd == value as String);

varkind=context.Kinds.SingleOrDefault(c=>c.KindCd==(String)value);

if(kind!=null)

{

returnkind.KindName;

}

return"";

}

/// <summary>

/// データ復元処理

/// </summary>

/// <param name="value"></param>

/// <param name="targetType"></param>

/// <param name="parameter"></param>

/// <param name="culture"></param>

/// <returns></returns>

publicobjectConvertBack(objectvalue,Type targetType,objectparameter,CultureInfo culture)

{

using(varcontext=newPgDbContext())

{

// データを取得

//Table<Kind> tblCat = con.GetTable<Kind>();

//Kind k = tblCat.Single(c => c.KindCd == value as String);

varkind=context.Kinds.SingleOrDefault(c=>c.KindName==value asString);

if(kind!=null)

{

returnkind.KindCd;

}

return"";

}

起動してみる

無事に起動、検索することができました。

まとめ

ひとまず、元々の処理（SQLite、MySQL）とあまり変更せずに実装することができました。

ちょっと長くなってしまったので、
登録、更新、削除はまた次回とさせていただきます。

ではでは。

2017年8月8日2019年1月29日C#,開発C#,WPF,プログラミング

Posted by doradora