テックメモ – ページ 29 – 自分用のメモです。内容が間違っていたり、作りかけで動作しないコードなどあるのでご注意ください。

2019-01-072020-05-06

SRT100でIPv6の設定、サブセグメント構築

初期化

シリアルケーブルつないでTeraTermで接続できる。
漢字コードはShift-Jisに変更

初期IPアドレス
192.168.100.1

初期パスワード
doremi

非常用パスワード
w,lXlma

administrator
管理者切り替え

cold start
初期化

quit
終了

show config
設定一覧

save
保存

サブセグメント追加

試しに既存セグメント(192.168.11.0/24)に追加。

IPアドレスの設定は、
LAN2(192.168.11.100)
LAN1(192.168.12.1)

デフォルトゲートウェイは、
192.168.11.1

NAT(IPマスカレード)は、
LAN2に外側(192.168.11.100)、内側(自動)

DHCPは中止しておく。

IPv6設定

設定
ipv6 prefix 1 ra-prefix@lan2::/64
ipv6 lan1 address ra-prefix@lan2::1/64
ipv6 lan1 rtadv send 1 o_flag=on
ipv6 lan1 dhcp service server
ipv6 lan2 dhcp service client ir=on
dns server dhcp lan2

確認
show ipv6 address lan1
show status ipv6 dhcp

繋がったけど安定しなかったので中止。

2018-12-05

VBA パス付きAccess(accdb)にアクセス

ちょっと試しに。

Sub MainRoutine(TargetName, BaseYear, RoutineType)

ExecuteResult = False

If RoutineType = "COMPARE_YEAR" Then

QueryString = "select * from xxx"
ExecuteResult = DbExecute(QueryString, TargetName, BaseYear, RoutineType)

End If

Call TmporaryDelete

If True = ExecuteResult Then
  MsgBox "完了しました"
Else
  MsgBox "エラーが発生しています"
End If

End Sub

Function DbExecute(QueryString, TargetName, BaseYear, RoutineType)

On Error GoTo e

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

DbPath = "C:\xxx.accdb"
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & DbPath & "; Jet OLEDB:Database Password=xxx"

cn.Open ConnectionString
rs.Open QueryString, cn

If RoutineType = "COMPARE_YEAR" Then
  If False = CompareYear(rs, TargetName, BaseYear, RoutineType) Then GoTo e
End If

If rs.State = 1 Then rs.Close
Set rs = Nothing
 
If cn.State = 1 Then cn.Close
Set cn = Nothing

DbExecute = True
Exit Function

e:

If rs.State = 1 Then rs.Close
Set rs = Nothing
 
If cn.State = 1 Then cn.Close
Set cn = Nothing

DbExecute = False

End Function

Function CompareYear(ByVal rs, TargetName, BaseYear, RoutineType)

On Error GoTo e

Call TmporaryDelete
Set w = Sheets.Add: w.Name = "tmp"

SheetName = "年度比較(" & BaseYear - 1 & "-" & BaseYear & ")_" & TargetName

If True = ExistSheet(SheetName) Then
  MsgBox "シート名が重複しています"
  GoTo e
End If

Sheets("(雛形)年度比較").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = SheetName

'ActiveSheet.Cells(1, 1).CopyFromRecordset rs

CompareYear = True
Exit Function

e:

CompareYear = False

End Function

Function ExistSheet(SheetName)

For Each w In Sheets
  If w.Name = SheetName Then
    ExistSheet = True
    Exit Function
  End If
Next

ExistSheet = False

End Function

Sub TmporaryDelete()

Application.DisplayAlerts = False

For Each w In Sheets
  If w.Name = "tmp" Then Sheets("tmp").Delete
Next

Application.DisplayAlerts = True

End Sub

100

101

102

103

104

105

106

107

108

109

110

111

Sub MainRoutine(TargetName, BaseYear, RoutineType)

ExecuteResult = False

If RoutineType = "COMPARE_YEAR" Then

QueryString = "select * from xxx"

ExecuteResult = DbExecute(QueryString, TargetName, BaseYear, RoutineType)

End If

Call TmporaryDelete

If True = ExecuteResult Then

MsgBox "完了しました"

Else

MsgBox "エラーが発生しています"

End If

End Sub

Function DbExecute(QueryString, TargetName, BaseYear, RoutineType)

On Error GoTo e

Set cn = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")

DbPath = "C:\xxx.accdb"

ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & DbPath & "; Jet OLEDB:Database Password=xxx"

cn.Open ConnectionString

rs.Open QueryString, cn

If RoutineType = "COMPARE_YEAR" Then

If False = CompareYear(rs, TargetName, BaseYear, RoutineType) Then GoTo e

End If

If rs.State = 1 Then rs.Close

Set rs = Nothing

If cn.State = 1 Then cn.Close

Set cn = Nothing

DbExecute = True

Exit Function

If rs.State = 1 Then rs.Close

Set rs = Nothing

If cn.State = 1 Then cn.Close

Set cn = Nothing

DbExecute = False

End Function

Function CompareYear(ByVal rs, TargetName, BaseYear, RoutineType)

On Error GoTo e

Call TmporaryDelete

Set w = Sheets.Add: w.Name = "tmp"

SheetName = "年度比較(" & BaseYear - 1 & "-" & BaseYear & ")_" & TargetName

If True = ExistSheet(SheetName) Then

MsgBox "シート名が重複しています"

GoTo e

End If

Sheets("(雛形)年度比較").Copy After:=Sheets(Sheets.Count)

ActiveSheet.Name = SheetName

'ActiveSheet.Cells(1, 1).CopyFromRecordset rs

CompareYear = True

Exit Function

CompareYear = False

End Function

Function ExistSheet(SheetName)

For Each w In Sheets

If w.Name = SheetName Then

ExistSheet = True

Exit Function

End If

ExistSheet = False

End Function

Sub TmporaryDelete()

Application.DisplayAlerts = False

For Each w In Sheets

If w.Name = "tmp" Then Sheets("tmp").Delete

Application.DisplayAlerts = True

End Sub

2018-12-01

C# 遅延評価、IEnumerable

        public Form1()
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();

            //遅延評価

            //var c = Enumerable.Range(1, 2).Select(x => stop(x));
            //この表記だけだと即時評価されないので1秒もかからない

            //var c = Enumerable.Range(1, 2).Select(x => stop(x)).ToArray();
            //このようにToArrayやToListをつけると、10秒かかる。

            sw.Stop();
            Debug.Print(sw.Elapsed.ToString());

            //コレクションの読取専用

            //IEnumerable
            //IReadOnlyCollection / ICollection
            //IReadOnlyList       / IList
            //ReadOnlyCollection  / List

            //IReadOnlyCollectionは IEnumerable + Countのみ。
            //IReadOnlyListは IReadOnlyCollection + インデクサ。
            //ReadOnlyCollection とあるがIListを実装している。

        }

        private int stop(int n)
        {
            Debug.Print(n.ToString());
            Thread.Sleep(1000 * 5);
            return n;
        }

public Form1()

{

Stopwatch sw = new Stopwatch();

sw.Start();

//遅延評価

//var c = Enumerable.Range(1, 2).Select(x => stop(x));

//この表記だけだと即時評価されないので1秒もかからない

//var c = Enumerable.Range(1, 2).Select(x => stop(x)).ToArray();

//このようにToArrayやToListをつけると、10秒かかる。

sw.Stop();

Debug.Print(sw.Elapsed.ToString());

//コレクションの読取専用

//IEnumerable

//IReadOnlyCollection / ICollection

//IReadOnlyList / IList

//ReadOnlyCollection / List

//IReadOnlyCollectionは IEnumerable + Countのみ。

//IReadOnlyListは IReadOnlyCollection + インデクサ。

//ReadOnlyCollection とあるがIListを実装している。

}

private int stop(int n)

{

Debug.Print(n.ToString());

Thread.Sleep(1000 * 5);

return n;

}

2018-11-232020-09-02

Windows上にサーバー作成 Nginx/PHP/MariaDB/WordPress

以前は必要になるとXamppを使ってたけど、今回はNginx環境を手動で作ってみる。

Nginx

Windows用のzipファイルをダウンロードし解凍する。設定ファイルはもともとのコメント部を消して、とりあえずこんな感じ。

nginx-1.19.2/conf/nginx.conf

worker_processes  1;

events {
    worker_connections  1024;
}

http {

    include mime.types;
    default_type application/octet-stream;
    sendfile on;
    keepalive_timeout 65;

    server {
        listen 80;
        root html;
        index index.php index.html;

        location ~ \.php$ {
            fastcgi_pass 127.0.0.1:9000;
            fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
            include fastcgi_params;
        }
    }
}

worker_processes 1;

events {

worker_connections 1024;

}

http {

include mime.types;

default_type application/octet-stream;

sendfile on;

keepalive_timeout 65;

server {

listen 80;

root html;

index index.php index.html;

location ~ \.php$ {

fastcgi_pass 127.0.0.1:9000;

fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;

include fastcgi_params;

}

Nginxフォルダのパスに日本語があると起動しない場合があるので注意。

PHP

ダウンロードして解凍したら、phpというフォルダでnginxの中に入れる。
(nginx-1.19.2/php)

php.ini-development
をコピーしてphp.iniにし、
extension_dir=”ext”
extension=php_mysqli.dll
を追記する。

MariaDB

ダウンロードして解凍したら、mariadbというフォルダでnginxの中に入れる。
(nginx-1.19.2/mariadb)

nginx-1.19.2/mariadb/bin
の中にある、mysql_install_db.exeを実行すると、
nginx-1.19.2/mariadb/data
が作成され、my.iniも入っている。
(Nginxフォルダを移動したらmy.iniを修正しないと起動しない)

手動でDB作成等に使う場合、まずbinの中のmysqld.exeを起動。次にコマンドプロンプトからmysql -u root のような感じでログインできる。

・設定
nginx-1.19.2/mariadb/bin
に移動しmysqldを起動後、
mysql -u root
とするとログインできる。

mysql> set password = password(“1234”);

・データベース作成
mysql> create database wpd character set utf8;
(utf8を指定しないとWPのインストールでエラー)

起動・終了バッチ

start.bat

start nginx.exe
start /min php/php-cgi.exe -b 127.0.0.1:9000
start mariadb/bin/mysqld.exe

start nginx.exe

start /min php/php-cgi.exe -b 127.0.0.1:9000

start mariadb/bin/mysqld.exe

my.iniを自動で書き換える場合このような感じ

echo [mysqld] > ./mariadb/data/my.ini
set p=%~dp0mariadb/data
echo datadir=%p:\=/% >> ./mariadb/data/my.ini
echo [client] >> ./mariadb/data/my.ini
set p=%~dp0mariadb/lib/plugin
echo plugin-dir=%p:\=/% >> ./mariadb/data/my.ini

start nginx.exe
start /min php/php-cgi.exe -b 127.0.0.1:9000
start mariadb/bin/mysqld.exe

echo [mysqld] > ./mariadb/data/my.ini

set p=%~dp0mariadb/data

echo datadir=%p:\=/% >> ./mariadb/data/my.ini

echo [client] >> ./mariadb/data/my.ini

set p=%~dp0mariadb/lib/plugin

echo plugin-dir=%p:\=/% >> ./mariadb/data/my.ini

start nginx.exe

start /min php/php-cgi.exe -b 127.0.0.1:9000

start mariadb/bin/mysqld.exe

stop.bat

nginx.exe -s quit
Taskkill /f /im php-cgi.exe
mariadb/bin/mysqladmin.exe -u root -p1234 shutdown

nginx.exe -s quit

Taskkill /f /im php-cgi.exe

mariadb/bin/mysqladmin.exe -u root -p1234 shutdown

あるいは全て強制終了
(こうしないとプロセスが残る場合あり)

Taskkill /f /im nginx.exe
Taskkill /f /im php-cgi.exe
Taskkill /f /im mysqld.exe

Taskkill /f /im nginx.exe

Taskkill /f /im php-cgi.exe

Taskkill /f /im mysqld.exe

mysql.bat

mysqlのディレクトリに移動するためだけのバッチ

cd %~dp0mariadb/bin
cmd /k

1 2	cd %~dp0mariadb/bin cmd /k

WordPress

nginx-1.19.2\html\wp
にWordPressを解凍したので、ブラウザから、
http://localhost/wp
にアクセス。

DBの情報を入れて送信したところ、
サイトに重大なエラーがありました。
と表示されエラーになった。
(１度目のインストール)

・DBをリセット
フォルダを手動で削除せず、mysqlのコマンドからdrop databaseする。
そして再度create databaseする。

・wp-config.phpを削除
wp-config-sample.phpを複写し、名前をwp-config.phpに変更する。

wp-config.phpが存在しないと自動でwp-config.phpは作成されるが、ここでは手動でwp-config.phpを設定する。

wp-config.phpの以下の部分をtrueに変更する。
define( ‘WP_DEBUG’, true);

以下の部分にDB情報を記入する。
define( ‘DB_NAME’, ‘wpd’ );
define( ‘DB_USER’, ‘root’ );
define( ‘DB_PASSWORD’, ‘1234’ );

再度挑戦するが、
サイトに重大なエラーがありました。
と表示されるだけで、特にエラー内容は表示されない。
(２度目のインストール)

Nginxのログ(logs/error.log)
を確認すると、
Fatal error: Maximum execution time of 30 seconds exceeded
とあるので、
php.iniで下記の部分を30から120に変更
max_execution_time = 120
(３度目のインストール)

これでインストールできた。

LAN経由(外部)から見る場合、ファイアーウォールに新しい規則を追加する。
もしCSSが適用されていない場合は、
設定＞一般＞アドレス
をIPアドレスに変更してみる。

2018-11-212020-02-06

C# 簡易インクリメンタルサーチ②

選択後に複数の値を取得したかったのでListをやめて、DataTableで処理。

using System.Data;
using System.Linq;
using System.Windows.Forms;

namespace ProductionManagement
{
    public partial class MasterForm : Form
    {
        public bool IsClick = false;
        public string ClickKey { set; get; }
        public string ClickValue { set; get; }

        public MasterForm(IColumnInfo columnInfo, DataTable dataTable)
        {
            InitializeComponent();

            DataTable baseMemberTable =
                dataTable.DefaultView.ToTable(true, new string[] { columnInfo.PrimaryKey, columnInfo.DisplayColumn });

            listBox1.DataSource = baseMemberTable;
            listBox1.ValueMember = columnInfo.PrimaryKey;
            listBox1.DisplayMember = columnInfo.DisplayColumn;

            textBox1.TextChanged += (s, e) =>
            {
                string keyword = textBox1.Text.ToString();

                if(keyword == "")
                {
                    listBox1.DataSource = baseMemberTable;
                    return;
                }
                
                var rowCollection = baseMemberTable.AsEnumerable()
                    .Where(r => r[columnInfo.DisplayColumn].ToString().Contains(keyword));

                if (rowCollection.Count() == 0)
                {
                    listBox1.DataSource = baseMemberTable.Clone();
                    return;
                }
                
                DataTable displayMemberTable = rowCollection.CopyToDataTable();
                listBox1.DataSource = displayMemberTable;

            };

            listBox1.KeyDown += (s, e) =>
            {
                if (e.KeyCode != Keys.Enter) return;
                ConfirmValue();
            };

            listBox1.KeyDown += (s, e) =>
            {
                if (e.KeyCode == Keys.Escape) this.Close();
            };

            listBox1.DoubleClick += (s, e) =>
            {
                ConfirmValue();
            };
        }

        private void ConfirmValue()
        {
            if (listBox1.SelectedValue == null) return;

            IsClick = true;
            ClickKey = listBox1.SelectedValue.ToString();
            ClickValue = listBox1.Text;

            this.Close();
        }

    }
}

using System.Data;

using System.Linq;

using System.Windows.Forms;

namespace ProductionManagement

{

public partial class MasterForm : Form

{

public bool IsClick = false;

public string ClickKey { set; get; }

public string ClickValue { set; get; }

public MasterForm(IColumnInfo columnInfo, DataTable dataTable)

{

InitializeComponent();

DataTable baseMemberTable =

dataTable.DefaultView.ToTable(true, new string[] { columnInfo.PrimaryKey, columnInfo.DisplayColumn });

listBox1.DataSource = baseMemberTable;

listBox1.ValueMember = columnInfo.PrimaryKey;

listBox1.DisplayMember = columnInfo.DisplayColumn;

textBox1.TextChanged += (s, e) =>

{

string keyword = textBox1.Text.ToString();

if(keyword == "")

{

listBox1.DataSource = baseMemberTable;

return;

}

var rowCollection = baseMemberTable.AsEnumerable()

.Where(r => r[columnInfo.DisplayColumn].ToString().Contains(keyword));

if (rowCollection.Count() == 0)

{

listBox1.DataSource = baseMemberTable.Clone();

return;

}

DataTable displayMemberTable = rowCollection.CopyToDataTable();

listBox1.DataSource = displayMemberTable;

};

listBox1.KeyDown += (s, e) =>

{

if (e.KeyCode != Keys.Enter) return;

ConfirmValue();

};

listBox1.KeyDown += (s, e) =>

{

if (e.KeyCode == Keys.Escape) this.Close();

};

listBox1.DoubleClick += (s, e) =>

{

ConfirmValue();

};

}

private void ConfirmValue()

{

if (listBox1.SelectedValue == null) return;

IsClick = true;

ClickKey = listBox1.SelectedValue.ToString();

ClickValue = listBox1.Text;

this.Close();

}

実際にソフトを使ってもらっていると、頻繁に更新されるマスタなのでDBはちょっと面倒。正確性はそこまで必要ないけど手入力の表記ゆれは避けたいという場面が多い。

それならテキストファイルだろうということで修正してみた。

using System.IO;
using System.Text;
using System.Data;
using System.Linq;
using System.Windows.Forms;

namespace ProductionManagement
{
    public partial class MasterForm : Form
    {
        public bool IsClick = false;
        public string ClickedValue { set; get; }

        public MasterForm(string filePath)
        {
            InitializeComponent();

            DataTable baseTable = new DataTable();
            baseTable.Columns.Add("DisplayColumn", typeof(string));

            using (StreamReader sr = new StreamReader(filePath, Encoding.GetEncoding("UTF-8")))
            {
                while (sr.Peek() > -1)
                {
                    var newRow = baseTable.NewRow();
                    newRow["DisplayColumn"] = sr.ReadLine();
                    baseTable.Rows.Add(newRow);
                }
            }

            listBox1.DataSource = baseTable;
            listBox1.DisplayMember = "DisplayColumn";

            textBox1.TextChanged += (s, e) => UpdateValue("DisplayColumn", baseTable);

            KeyEventAttach();
        }


        private void UpdateValue(string key, DataTable baseTable)
        {
            string keyword = textBox1.Text.ToString();

            if (keyword == "")
            {
                listBox1.DataSource = baseTable;
                return;
            }

            var rowCollection = baseTable.AsEnumerable()
                .Where(r => r[key].ToString().Contains(keyword));

            if (rowCollection.Count() == 0)
            {
                listBox1.DataSource = baseTable.Clone();
            }
            else
            {
                listBox1.DataSource = rowCollection.CopyToDataTable();
            }

        }

        public void KeyEventAttach()
        {
            listBox1.KeyDown += (s, e) =>
            {
                if (e.KeyCode != Keys.Enter) return;
                ConfirmValue();
            };

            listBox1.KeyDown += (s, e) =>
            {
                if (e.KeyCode == Keys.Escape) this.Close();
            };

            listBox1.DoubleClick += (s, e) =>
            {
                ConfirmValue();
            };

        }

        private void ConfirmValue()
        {
            if (listBox1.SelectedValue == null) return;
            IsClick = true;
            ClickedValue = listBox1.Text;
            this.Close();
        }
    }
}

using System.IO;

using System.Text;

using System.Data;

using System.Linq;

using System.Windows.Forms;

namespace ProductionManagement

{

public partial class MasterForm : Form

{

public bool IsClick = false;

public string ClickedValue { set; get; }

public MasterForm(string filePath)

{

InitializeComponent();

DataTable baseTable = new DataTable();

baseTable.Columns.Add("DisplayColumn", typeof(string));

using (StreamReader sr = new StreamReader(filePath, Encoding.GetEncoding("UTF-8")))

{

while (sr.Peek() > -1)

{

var newRow = baseTable.NewRow();

newRow["DisplayColumn"] = sr.ReadLine();

baseTable.Rows.Add(newRow);

}

listBox1.DataSource = baseTable;

listBox1.DisplayMember = "DisplayColumn";

textBox1.TextChanged += (s, e) => UpdateValue("DisplayColumn", baseTable);

KeyEventAttach();

}

private void UpdateValue(string key, DataTable baseTable)

{

string keyword = textBox1.Text.ToString();

if (keyword == "")

{

listBox1.DataSource = baseTable;

return;

}

var rowCollection = baseTable.AsEnumerable()

.Where(r => r[key].ToString().Contains(keyword));

if (rowCollection.Count() == 0)

{

listBox1.DataSource = baseTable.Clone();

}

else

{

listBox1.DataSource = rowCollection.CopyToDataTable();

}

public void KeyEventAttach()

{

listBox1.KeyDown += (s, e) =>

{

if (e.KeyCode != Keys.Enter) return;

ConfirmValue();

};

listBox1.KeyDown += (s, e) =>

{

if (e.KeyCode == Keys.Escape) this.Close();

};

listBox1.DoubleClick += (s, e) =>

{

ConfirmValue();

};

}

private void ConfirmValue()

{

if (listBox1.SelectedValue == null) return;

IsClick = true;

ClickedValue = listBox1.Text;

this.Close();

}

2018-11-13

C# 簡易インクリメンタルサーチ

コンボボックス単体で処理しようとしたけど、DroppedDownをいじるとマウスが動かなくなるので、とりあえずテキストボックス、リストボックスで対応。

using System.Data;
using System.Linq;
using System.Collections.Generic;
using System.Windows.Forms;

namespace ProductionManagement
{
    public partial class MasterDialog : Form
    {
        private string _TableKey = "name";
        private List<string> _BaseMemberList = new List<string>();
        private List<string> _DisplayMemberList = new List<string>();

        private DataTable CreateDataTable(List<string> memberList)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(_TableKey);

            foreach (string m in memberList)
            {
                dt.Rows.Add((dt.NewRow()[_TableKey] = m));
            }
            return dt;
        }

        public MasterDialog()
        {
            InitializeComponent();

            _BaseMemberList = new List<string> {
                "abc", "123", "0000", "999", "xyz","123123", "a",
                "abc", "123", "0000", "999", "xyz","123123", "a"
            };

            listBox1.DataSource = CreateDataTable(_BaseMemberList);
            listBox1.DisplayMember = _TableKey;
            listBox1.ValueMember = _TableKey;

            textBox1.TextChanged += (s, e) =>
            {
                string keyword = ((TextBox)s).Text.ToString();

                if(keyword == "")
                {
                    listBox1.DataSource = CreateDataTable(_BaseMemberList);
                }
                else
                {
                    _DisplayMemberList = _BaseMemberList.Where(x => x.Contains(keyword)).ToList();
                    listBox1.DataSource = CreateDataTable(_DisplayMemberList);
                }

            };
        }
    }
}

using System.Data;

using System.Linq;

using System.Collections.Generic;

using System.Windows.Forms;

namespace ProductionManagement

{

public partial class MasterDialog : Form

{

private string _TableKey = "name";

private List<string> _BaseMemberList = new List<string>();

private List<string> _DisplayMemberList = new List<string>();

private DataTable CreateDataTable(List<string> memberList)

{

DataTable dt = new DataTable();

dt.Columns.Add(_TableKey);

foreach (string m in memberList)

{

dt.Rows.Add((dt.NewRow()[_TableKey] = m));

}

return dt;

}

public MasterDialog()

{

InitializeComponent();

_BaseMemberList = new List<string> {

"abc", "123", "0000", "999", "xyz","123123", "a",

"abc", "123", "0000", "999", "xyz","123123", "a"

};

listBox1.DataSource = CreateDataTable(_BaseMemberList);

listBox1.DisplayMember = _TableKey;

listBox1.ValueMember = _TableKey;

textBox1.TextChanged += (s, e) =>

{

string keyword = ((TextBox)s).Text.ToString();

if(keyword == "")

{

listBox1.DataSource = CreateDataTable(_BaseMemberList);

}

else

{

_DisplayMemberList = _BaseMemberList.Where(x => x.Contains(keyword)).ToList();

listBox1.DataSource = CreateDataTable(_DisplayMemberList);

}

};

}

2018-11-082018-11-08

C# MySQLを利用するサンプル

MySQLからデータを持ってきてデータグリッドビューに入れて検索のところまで書いた状態。業務に関わる部分は問題ないように変更してある。

Db.cs

using System;
using System.Data;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace ProductionManagement
{
    class Db
    {
        private string ConnectionString = "server=xxx; ssl mode=none; port=xxx; user=xxx; password=xxx; database=xxx";

        public DataTable ExecuteQuery(string query)
        {
            using (MySqlConnection con = new MySqlConnection(ConnectionString))
            {
                try
                {
                    con.Open();
                    MySqlCommand mySqlCommand = new MySqlCommand(query, con);
                    using (MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
                    {
                        DataTable dt = new DataTable();
                        dt.Load(mySqlDataReader);
                        return dt;
                    }
                        
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                    return new DataTable();
                }
            }
        }

        public void ExecuteNonQuery(string query)
        {
            using (MySqlConnection con = new MySqlConnection(ConnectionString))
            {
                try
                {
                    con.Open();
                    MySqlCommand mySqlCommand = new MySqlCommand(query, con);
                    mySqlCommand.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }
            }
        }

    }
}

using System;

using System.Data;

using System.Windows.Forms;

using MySql.Data.MySqlClient;

namespace ProductionManagement

{

class Db

{

private string ConnectionString = "server=xxx; ssl mode=none; port=xxx; user=xxx; password=xxx; database=xxx";

public DataTable ExecuteQuery(string query)

{

using (MySqlConnection con = new MySqlConnection(ConnectionString))

{

try

{

con.Open();

MySqlCommand mySqlCommand = new MySqlCommand(query, con);

using (MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())

{

DataTable dt = new DataTable();

dt.Load(mySqlDataReader);

return dt;

}

catch (Exception e)

{

MessageBox.Show(e.Message);

return new DataTable();

}

public void ExecuteNonQuery(string query)

{

using (MySqlConnection con = new MySqlConnection(ConnectionString))

{

try

{

con.Open();

MySqlCommand mySqlCommand = new MySqlCommand(query, con);

mySqlCommand.ExecuteNonQuery();

}

catch (Exception e)

{

MessageBox.Show(e.Message);

}

OrderColumnInfo.cs

using System.Collections.Generic;

namespace ProductionManagement
{
    enum SelectType { None, Target }
    enum UpdateType { None, Target }
    enum SearchType { None, TargetNormal, TargetTerm }

    class OrderColumnInfo
    {
        public string ColumnName;
        public SelectType SelectTarget;
        public UpdateType UpdateTarget;
        public SearchType SearchTarget;
        
        public static List<OrderColumnInfo> CreateColumnInfoList()
        {
            List<OrderColumnInfo> listColumnInfo = new List<OrderColumnInfo>();

            listColumnInfo.Add(new OrderColumnInfo()
            {
                ColumnName = "受注ID",
                SelectTarget = SelectType.Target,
                UpdateTarget = UpdateType.None,
                SearchTarget = SearchType.None
            });

            listColumnInfo.Add(new OrderColumnInfo()
            {
                ColumnName = "型式CD",
                SelectTarget = SelectType.Target,
                UpdateTarget = UpdateType.Target,
                SearchTarget = SearchType.None
            });

            listColumnInfo.Add(new OrderColumnInfo()
            {
                ColumnName = "型式",
                SelectTarget = SelectType.Target,
                UpdateTarget = UpdateType.Target,
                SearchTarget = SearchType.TargetNormal
            });

            listColumnInfo.Add(new OrderColumnInfo()
            {
                ColumnName = "得意先CD",
                SelectTarget = SelectType.Target,
                UpdateTarget = UpdateType.Target,
                SearchTarget = SearchType.None
            });

            listColumnInfo.Add(new OrderColumnInfo()
            {
                ColumnName = "得意先",
                SelectTarget = SelectType.Target,
                UpdateTarget = UpdateType.Target,
                SearchTarget = SearchType.TargetNormal
            });

            listColumnInfo.Add(new OrderColumnInfo()
            {
                ColumnName = "工番",
                SelectTarget = SelectType.Target,
                UpdateTarget = UpdateType.Target,
                SearchTarget = SearchType.TargetNormal
            });

            listColumnInfo.Add(new OrderColumnInfo()
            {
                ColumnName = "内外作",
                SelectTarget = SelectType.Target,
                UpdateTarget = UpdateType.Target,
                SearchTarget = SearchType.TargetNormal
            });

            listColumnInfo.Add(new OrderColumnInfo()
            {
                ColumnName = "伝票納期",
                SelectTarget = SelectType.Target,
                UpdateTarget = UpdateType.Target,
                SearchTarget = SearchType.TargetTerm
            });

            listColumnInfo.Add(new OrderColumnInfo()
            {
                ColumnName = "受注数量",
                SelectTarget = SelectType.Target,
                UpdateTarget = UpdateType.Target,
                SearchTarget = SearchType.None
            });

            listColumnInfo.Add(new OrderColumnInfo()
            {
                ColumnName = "伝票金額",
                SelectTarget = SelectType.Target,
                UpdateTarget = UpdateType.Target,
                SearchTarget = SearchType.None
            });

            listColumnInfo.Add(new OrderColumnInfo()
            {
                ColumnName = "合計金額",
                SelectTarget = SelectType.None,
                UpdateTarget = UpdateType.None,
                SearchTarget = SearchType.None
            });

            listColumnInfo.Add(new OrderColumnInfo()
            {
                ColumnName = "本社備考",
                SelectTarget = SelectType.Target,
                UpdateTarget = UpdateType.Target,
                SearchTarget = SearchType.TargetNormal
            });

            listColumnInfo.Add(new OrderColumnInfo()
            {
                ColumnName = "管理備考",
                SelectTarget = SelectType.Target,
                UpdateTarget = UpdateType.Target,
                SearchTarget = SearchType.TargetNormal
            });

            listColumnInfo.Add(new OrderColumnInfo()
            {
                ColumnName = "登録日",
                SelectTarget = SelectType.Target,
                UpdateTarget = UpdateType.Target,
                SearchTarget = SearchType.TargetTerm
            });
            return listColumnInfo;
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

using System.Collections.Generic;

namespace ProductionManagement

{

enum SelectType { None, Target }

enum UpdateType { None, Target }

enum SearchType { None, TargetNormal, TargetTerm }

class OrderColumnInfo

{

public string ColumnName;

public SelectType SelectTarget;

public UpdateType UpdateTarget;

public SearchType SearchTarget;

public static List<OrderColumnInfo> CreateColumnInfoList()

{

List<OrderColumnInfo> listColumnInfo = new List<OrderColumnInfo>();

listColumnInfo.Add(new OrderColumnInfo()

{

ColumnName = "受注ID",

SelectTarget = SelectType.Target,

UpdateTarget = UpdateType.None,

SearchTarget = SearchType.None

});

listColumnInfo.Add(new OrderColumnInfo()

{

ColumnName = "型式CD",

SelectTarget = SelectType.Target,

UpdateTarget = UpdateType.Target,

SearchTarget = SearchType.None

});

listColumnInfo.Add(new OrderColumnInfo()

{

ColumnName = "型式",

SelectTarget = SelectType.Target,

UpdateTarget = UpdateType.Target,

SearchTarget = SearchType.TargetNormal

});

listColumnInfo.Add(new OrderColumnInfo()

{

ColumnName = "得意先CD",

SelectTarget = SelectType.Target,

UpdateTarget = UpdateType.Target,

SearchTarget = SearchType.None

});

listColumnInfo.Add(new OrderColumnInfo()

{

ColumnName = "得意先",

SelectTarget = SelectType.Target,

UpdateTarget = UpdateType.Target,

SearchTarget = SearchType.TargetNormal

});

listColumnInfo.Add(new OrderColumnInfo()

{

ColumnName = "工番",

SelectTarget = SelectType.Target,

UpdateTarget = UpdateType.Target,

SearchTarget = SearchType.TargetNormal

});

listColumnInfo.Add(new OrderColumnInfo()

{

ColumnName = "内外作",

SelectTarget = SelectType.Target,

UpdateTarget = UpdateType.Target,

SearchTarget = SearchType.TargetNormal

});

listColumnInfo.Add(new OrderColumnInfo()

{

ColumnName = "伝票納期",

SelectTarget = SelectType.Target,

UpdateTarget = UpdateType.Target,

SearchTarget = SearchType.TargetTerm

});

listColumnInfo.Add(new OrderColumnInfo()

{

ColumnName = "受注数量",

SelectTarget = SelectType.Target,

UpdateTarget = UpdateType.Target,

SearchTarget = SearchType.None

});

listColumnInfo.Add(new OrderColumnInfo()

{

ColumnName = "伝票金額",

SelectTarget = SelectType.Target,

UpdateTarget = UpdateType.Target,

SearchTarget = SearchType.None

});

listColumnInfo.Add(new OrderColumnInfo()

{

ColumnName = "合計金額",

SelectTarget = SelectType.None,

UpdateTarget = UpdateType.None,

SearchTarget = SearchType.None

});

listColumnInfo.Add(new OrderColumnInfo()

{

ColumnName = "本社備考",

SelectTarget = SelectType.Target,

UpdateTarget = UpdateType.Target,

SearchTarget = SearchType.TargetNormal

});

listColumnInfo.Add(new OrderColumnInfo()

{

ColumnName = "管理備考",

SelectTarget = SelectType.Target,

UpdateTarget = UpdateType.Target,

SearchTarget = SearchType.TargetNormal

});

listColumnInfo.Add(new OrderColumnInfo()

{

ColumnName = "登録日",

SelectTarget = SelectType.Target,

UpdateTarget = UpdateType.Target,

SearchTarget = SearchType.TargetTerm

});

return listColumnInfo;

}

OrderListForm.cs

using System.Drawing;
using System.Windows.Forms;

namespace ProductionManagement
{
    public partial class OrderListForm : Form
    {
        private OrderService orderService = new OrderService();

        public OrderListForm()
        {
            InitializeComponent();

            DataGridViewProperty();
            DataGridView.DataSource = orderService.DataTableToModelList();

            DataGridViewEvent();
            ButtonEvent();
            TextBoxEvent();
        }

        public string BeforeChangeValue { set; get; }

        private void DataGridViewEvent()
        {
            DataGridView.DoubleClick += orderService.CreateModel(this);

            DataGridView.CellEnter += (sender, e) =>
            {
                BeforeChangeValue = DataGridView.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
            };

            DataGridView.CellValueChanged += orderService.InsertUpdateFlag(this);
            DataGridView.Columns["UpdateFlag"].Visible = false;
        }

        private void ButtonEvent()
        {
            SearchButton.Click += orderService.CreateSearchValueList(this);

            ClearButton.Click += (sender, e) => {
                foreach (Control c in Controls)
                {
                    if (c.GetType() == typeof(TextBox))
                    {
                        ((TextBox)c).Text = "";
                    }
                }
                DataGridView.DataSource = orderService.DataTableToModelList();
            };

            SaveButton.Click += orderService.CreateUpdateValueList(this);
        }

        private void TextBoxEvent()
        {
            foreach(Control c in Controls)
            {
                if(c.GetType() == typeof(TextBox))
                {
                    ((TextBox)c).LostFocus += orderService.TextBoxValueParse(this);

                    ((TextBox)c).KeyDown += (sender, e) => {
                        if (e.KeyCode == Keys.Enter && ModifierKeys == Keys.Control)
                        {
                            SearchButton.Focus();
                            SearchButton.PerformClick();
                        }
                    };
                }
            }
        }

        private void DataGridViewProperty()
        {
            DataGridView.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            DataGridView.ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.False;
            DataGridView.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.DisableResizing;
            DataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None;
            DataGridView.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.None;
            DataGridView.Font = new Font("メイリオ", 10);
            DataGridView.RowTemplate.Height = 22;
            DataGridView.AllowUserToAddRows = false;

            typeof(DataGridView).
                GetProperty("DoubleBuffered", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic).
                SetValue(DataGridView, true, null);
        }
    }
}

using System.Drawing;

using System.Windows.Forms;

namespace ProductionManagement

{

public partial class OrderListForm : Form

{

private OrderService orderService = new OrderService();

public OrderListForm()

{

InitializeComponent();

DataGridViewProperty();

DataGridView.DataSource = orderService.DataTableToModelList();

DataGridViewEvent();

ButtonEvent();

TextBoxEvent();

}

public string BeforeChangeValue { set; get; }

private void DataGridViewEvent()

{

DataGridView.DoubleClick += orderService.CreateModel(this);

DataGridView.CellEnter += (sender, e) =>

{

BeforeChangeValue = DataGridView.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();

};

DataGridView.CellValueChanged += orderService.InsertUpdateFlag(this);

DataGridView.Columns["UpdateFlag"].Visible = false;

}

private void ButtonEvent()

{

SearchButton.Click += orderService.CreateSearchValueList(this);

ClearButton.Click += (sender, e) => {

foreach (Control c in Controls)

{

if (c.GetType() == typeof(TextBox))

{

((TextBox)c).Text = "";

}

DataGridView.DataSource = orderService.DataTableToModelList();

};

SaveButton.Click += orderService.CreateUpdateValueList(this);

}

private void TextBoxEvent()

{

foreach(Control c in Controls)

{

if(c.GetType() == typeof(TextBox))

{

((TextBox)c).LostFocus += orderService.TextBoxValueParse(this);

((TextBox)c).KeyDown += (sender, e) => {

if (e.KeyCode == Keys.Enter && ModifierKeys == Keys.Control)

{

SearchButton.Focus();

SearchButton.PerformClick();

}

};

}

private void DataGridViewProperty()

{

DataGridView.SelectionMode = DataGridViewSelectionMode.FullRowSelect;

DataGridView.ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.False;

DataGridView.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.DisableResizing;

DataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None;

DataGridView.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.None;

DataGridView.Font = new Font("メイリオ", 10);

DataGridView.RowTemplate.Height = 22;

DataGridView.AllowUserToAddRows = false;

typeof(DataGridView).

GetProperty("DoubleBuffered", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic).

SetValue(DataGridView, true, null);

}

OrderModel.cs

using System;
namespace ProductionManagement
{
    class OrderModel
    {
        public bool HasParseError = false;
        public string UpdateFlag { set; get; }

        private int OrderID;
        public object 受注ID
        {
            set
            {
                if (OrderID == 0)
                {
                    OrderID = int.Parse(Convert.ToString(value));
                }
            }
            get
            {
                return OrderID;
            }
        }

        private int ModelCD;
        public object 型式CD
        {
            set
            {
                try
                {
                    ModelCD = int.Parse(Convert.ToString(value));
                }
                catch
                {
                    HasParseError = true;
                }
            }
            get
            {
                return ModelCD;
            }
        }

        private string ModelName;
        public object 型式
        {
            set { ModelName = Convert.ToString(value); }
            get { return ModelName; }
        }

        private int CustomerCD;
        public object 得意先CD
        {
            set
            {
                try
                {
                    CustomerCD = int.Parse(Convert.ToString(value));
                }
                catch
                {
                    HasParseError = true;
                }
            }
            get
            {
                return CustomerCD;
            }
        }

        private string CustomerName;
        public object 得意先
        {
            set { CustomerName = Convert.ToString(value); }
            get { return CustomerName; }
        }

        private string Serial;
        public object 工番
        {
            set { Serial = Convert.ToString(value); }
            get { return Serial; }
        }

        private string InOut;
        public object 内外作
        {
            set
            {
                if ("内作" == Convert.ToString(value) || "外作" == Convert.ToString(value))
                {
                    InOut = Convert.ToString(value);
                }
                else
                {
                    HasParseError = true;
                    return;
                }
            }
            get
            {
                return InOut;
            }
        }

        private DateTime DueDate;
        public object 伝票納期
        {
            set
            {
                try
                {
                    DueDate = DateTime.Parse(Convert.ToString(value));
                }
                catch
                {
                    HasParseError = true;
                }
            }
            get
            {
                return DueDate.ToString("yyyy-MM-dd");
            }
        }

        private int Quantity;
        public object 受注数量
        {
            set
            {
                try
                {
                    Quantity = int.Parse(Convert.ToString(value));
                }
                catch
                {
                    HasParseError = true;
                }
            }
            get { return Quantity; }
        }

        private decimal UnitPrice;
        public object 伝票金額
        {
            set
            {
                try
                {
                    UnitPrice = decimal.Parse(Convert.ToString(value));
                }
                catch
                {
                    HasParseError = true;
                }
            }
            get { return UnitPrice; }
        }

        public object 合計金額
        {
            get
            {
                return int.Parse(Convert.ToString(受注数量)) * decimal.Parse(Convert.ToString(伝票金額));
            }
        }

        private string Notice;
        public object 本社備考
        {
            set { Notice = Convert.ToString(value); }
            get { return Notice; }
        }

        private string Remark;
        public object 管理備考
        {
            set { Remark = Convert.ToString(value); }
            get { return Remark; }
        }

        private DateTime Registered;
        public object 登録日
        {
            set
            {
                try
                {
                    Registered = DateTime.Parse(Convert.ToString(value));
                }
                catch
                {
                    HasParseError = true;
                }
            }
            get
            {
                return Registered.ToString("yyyy-MM-dd");
            }
        }
    }

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

using System;

namespace ProductionManagement

{

class OrderModel

{

public bool HasParseError = false;

public string UpdateFlag { set; get; }

private int OrderID;

public object 受注ID

{

set

{

if (OrderID == 0)

{

OrderID = int.Parse(Convert.ToString(value));

}

get

{

return OrderID;

}

private int ModelCD;

public object 型式CD

{

set

{

try

{

ModelCD = int.Parse(Convert.ToString(value));

}

catch

{

HasParseError = true;

}

get

{

return ModelCD;

}

private string ModelName;

public object 型式

{

set { ModelName = Convert.ToString(value); }

get { return ModelName; }

}

private int CustomerCD;

public object 得意先CD

{

set

{

try

{

CustomerCD = int.Parse(Convert.ToString(value));

}

catch

{

HasParseError = true;

}

get

{

return CustomerCD;

}

private string CustomerName;

public object 得意先

{

set { CustomerName = Convert.ToString(value); }

get { return CustomerName; }

}

private string Serial;

public object 工番

{

set { Serial = Convert.ToString(value); }

get { return Serial; }

}

private string InOut;

public object 内外作

{

set

{

if ("内作" == Convert.ToString(value) || "外作" == Convert.ToString(value))

{

InOut = Convert.ToString(value);

}

else

{

HasParseError = true;

return;

}

get

{

return InOut;

}

private DateTime DueDate;

public object 伝票納期

{

set

{

try

{

DueDate = DateTime.Parse(Convert.ToString(value));

}

catch

{

HasParseError = true;

}

get

{

return DueDate.ToString("yyyy-MM-dd");

}

private int Quantity;

public object 受注数量

{

set

{

try

{

Quantity = int.Parse(Convert.ToString(value));

}

catch

{

HasParseError = true;

}

get { return Quantity; }

}

private decimal UnitPrice;

public object 伝票金額

{

set

{

try

{

UnitPrice = decimal.Parse(Convert.ToString(value));

}

catch

{

HasParseError = true;

}

get { return UnitPrice; }

}

public object 合計金額

{

get

{

return int.Parse(Convert.ToString(受注数量)) * decimal.Parse(Convert.ToString(伝票金額));

}

private string Notice;

public object 本社備考

{

set { Notice = Convert.ToString(value); }

get { return Notice; }

}

private string Remark;

public object 管理備考

{

set { Remark = Convert.ToString(value); }

get { return Remark; }

}

private DateTime Registered;

public object 登録日

{

set

{

try

{

Registered = DateTime.Parse(Convert.ToString(value));

}

catch

{

HasParseError = true;

}

get

{

return Registered.ToString("yyyy-MM-dd");

}

OrderQuery.cs

using System.Collections.Generic;
using System.Text.RegularExpressions;

namespace ProductionManagement
{
    class OrderQuery
    {
        public string SearchString(List<string> listSearch)
        {
            if (listSearch.Count == 0)
            {
                return SelectString();
            }
            else
            {
                return SelectString() + " where " + string.Join(" and ",listSearch.ToArray());
            }
        }

        public string SelectString()
        {
            string tableName = "受注データ";

            string targetColumn = "";

            foreach (OrderColumnInfo orderColumnInfo in OrderColumnInfo.CreateColumnInfoList())
            {
                if (orderColumnInfo.SelectTarget == SelectType.None) continue;
                targetColumn += orderColumnInfo.ColumnName + ",";
            }
            targetColumn = new Regex(@",$").Replace(targetColumn, "");

            return "select " + targetColumn + " from " + tableName;
        }

    }
}

using System.Collections.Generic;

using System.Text.RegularExpressions;

namespace ProductionManagement

{

class OrderQuery

{

public string SearchString(List<string> listSearch)

{

if (listSearch.Count == 0)

{

return SelectString();

}

else

{

return SelectString() + " where " + string.Join(" and ",listSearch.ToArray());

}

public string SelectString()

{

string tableName = "受注データ";

string targetColumn = "";

foreach (OrderColumnInfo orderColumnInfo in OrderColumnInfo.CreateColumnInfoList())

{

if (orderColumnInfo.SelectTarget == SelectType.None) continue;

targetColumn += orderColumnInfo.ColumnName + ",";

}

targetColumn = new Regex(@",$").Replace(targetColumn, "");

return "select " + targetColumn + " from " + tableName;

}

OrderSchema.cs

using System.Text.RegularExpressions;
using System.Collections.Generic;

namespace ProductionManagement
{
    class OrderSchema
    {
        private string ColumnName;
        private string DataType;
        private string Constraints;

        public string CreateTableString()
        {
            string tableName = "受注データ";

            string targetColumn = "";
            foreach (OrderSchema schema in new OrderSchema().CreateSchemaList())
            {
                targetColumn += schema.ColumnName + " " + schema.DataType + " " + schema.Constraints + ",";
            }
            targetColumn = new Regex(@",$").Replace(targetColumn, "");

            return "drop table if exists " + tableName + ";" +
            "create table " + tableName + " (" + targetColumn + ") default character set sjis";
        }

        public List<OrderSchema> CreateSchemaList()
        {
            List<OrderSchema> listSchema = new List<OrderSchema>();

            listSchema.Add(new OrderSchema()
            {
                ColumnName = "受注ID",
                DataType = "int",
                Constraints = "not null primary key auto_increment"
            });

            listSchema.Add(new OrderSchema()
            {
                ColumnName = "型式CD",
                DataType = "int",
                Constraints = ""
            });

            listSchema.Add(new OrderSchema()
            {
                ColumnName = "型式",
                DataType = "varchar(32)",
                Constraints = ""
            });

            listSchema.Add(new OrderSchema()
            {
                ColumnName = "得意先CD",
                DataType = "int",
                Constraints = ""
            });

            listSchema.Add(new OrderSchema()
            {
                ColumnName = "得意先",
                DataType = "varchar(32)",
                Constraints = ""
            });

            listSchema.Add(new OrderSchema()
            {
                ColumnName = "工番",
                DataType = "varchar(32)",
                Constraints = ""
            });

            listSchema.Add(new OrderSchema()
            {
                ColumnName = "内外作",
                DataType = "enum('内作','外作')",
                Constraints = ""
            });

            listSchema.Add(new OrderSchema()
            {
                ColumnName = "伝票納期",
                DataType = "date",
                Constraints = ""
            });

            listSchema.Add(new OrderSchema()
            {
                ColumnName = "受注数量",
                DataType = "int",
                Constraints = ""
            });

            listSchema.Add(new OrderSchema()
            {
                ColumnName = "伝票金額",
                DataType = "decimal",
                Constraints = ""
            });

            listSchema.Add(new OrderSchema()
            {
                ColumnName = "本社備考",
                DataType = "varchar(255)",
                Constraints = ""
            });

            listSchema.Add(new OrderSchema()
            {
                ColumnName = "管理備考",
                DataType = "varchar(255)",
                Constraints = ""
            });

            listSchema.Add(new OrderSchema()
            {
                ColumnName = "登録日",
                DataType = "date",
                Constraints = ""
            });
            return listSchema;
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

using System.Text.RegularExpressions;

using System.Collections.Generic;

namespace ProductionManagement

{

class OrderSchema

{

private string ColumnName;

private string DataType;

private string Constraints;

public string CreateTableString()

{

string tableName = "受注データ";

string targetColumn = "";

foreach (OrderSchema schema in new OrderSchema().CreateSchemaList())

{

targetColumn += schema.ColumnName + " " + schema.DataType + " " + schema.Constraints + ",";

}

targetColumn = new Regex(@",$").Replace(targetColumn, "");

return "drop table if exists " + tableName + ";" +

"create table " + tableName + " (" + targetColumn + ") default character set sjis";

}

public List<OrderSchema> CreateSchemaList()

{

List<OrderSchema> listSchema = new List<OrderSchema>();

listSchema.Add(new OrderSchema()

{

ColumnName = "受注ID",

DataType = "int",

Constraints = "not null primary key auto_increment"

});

listSchema.Add(new OrderSchema()

{

ColumnName = "型式CD",

DataType = "int",

Constraints = ""

});

listSchema.Add(new OrderSchema()

{

ColumnName = "型式",

DataType = "varchar(32)",

Constraints = ""

});

listSchema.Add(new OrderSchema()

{

ColumnName = "得意先CD",

DataType = "int",

Constraints = ""

});

listSchema.Add(new OrderSchema()

{

ColumnName = "得意先",

DataType = "varchar(32)",

Constraints = ""

});

listSchema.Add(new OrderSchema()

{

ColumnName = "工番",

DataType = "varchar(32)",

Constraints = ""

});

listSchema.Add(new OrderSchema()

{

ColumnName = "内外作",

DataType = "enum('内作','外作')",

Constraints = ""

});

listSchema.Add(new OrderSchema()

{

ColumnName = "伝票納期",

DataType = "date",

Constraints = ""

});

listSchema.Add(new OrderSchema()

{

ColumnName = "受注数量",

DataType = "int",

Constraints = ""

});

listSchema.Add(new OrderSchema()

{

ColumnName = "伝票金額",

DataType = "decimal",

Constraints = ""

});

listSchema.Add(new OrderSchema()

{

ColumnName = "本社備考",

DataType = "varchar(255)",

Constraints = ""

});

listSchema.Add(new OrderSchema()

{

ColumnName = "管理備考",

DataType = "varchar(255)",

Constraints = ""

});

listSchema.Add(new OrderSchema()

{

ColumnName = "登録日",

DataType = "date",

Constraints = ""

});

return listSchema;

}

OrderService.cs

using System;
using System.Collections.Generic;
using System.Windows.Forms;
using System.Data;
using System.Reflection;
using System.Drawing;

namespace ProductionManagement
{
    class OrderService
    {
        public EventHandler TextBoxValueParse(OrderListForm f)
        {
            return (sender, e) =>
            {
                TextBox target = (TextBox)sender;

                foreach (MemberInfo memberInfo in typeof(OrderModel).GetMembers())
                {
                    if (target.Name == memberInfo.Name || target.Name.Replace("_","") == memberInfo.Name)
                    {
                        OrderModel OrderModel = new OrderModel();
                        
                        PropertyInfo p = typeof(OrderModel).GetProperty(memberInfo.Name);
                        p.SetValue(OrderModel, target.Text);

                        if (OrderModel.HasParseError)
                        {
                            target.Text = "";
                        }
                        else
                        {
                            target.Text = p.GetValue(OrderModel).ToString();
                        }
                    }
                }
            };
        }

        public DataGridViewCellEventHandler InsertUpdateFlag(OrderListForm f)
        {
            return (sender, e) =>
            {
                DataGridViewRow targetRow = ((DataGridView)sender).Rows[e.RowIndex];
                string targetValue = targetRow.Cells[e.ColumnIndex].Value.ToString();

                if (targetValue == f.BeforeChangeValue) return;

                targetRow.Cells["UpdateFlag"].Value = "1";
                targetRow.DefaultCellStyle.ForeColor = Color.Blue;
            };
        }

        public EventHandler CreateUpdateValueList(OrderListForm f)
        {
            return (sender, e) =>
            {

            };
        }

        public EventHandler CreateSearchValueList(OrderListForm f)
        {
            return (sender, e) =>
            {
                List<string> listSearch = new List<string>();

                foreach (OrderColumnInfo columnInfo in OrderColumnInfo.CreateColumnInfoList())
                {
                    if (columnInfo.SearchTarget == SearchType.None) continue;

                    if(columnInfo.SearchTarget == SearchType.TargetNormal)
                    {
                        string targetString = f.Controls[columnInfo.ColumnName].Text;
                        if (targetString != "")
                        {
                            listSearch.Add(columnInfo.ColumnName + " like '%" + targetString + "%'");
                        }
                    }

                    if (columnInfo.SearchTarget == SearchType.TargetTerm)
                    {
                        string targetStringFrom = f.Controls[columnInfo.ColumnName].Text;
                        string targetStringTo = f.Controls["_" + columnInfo.ColumnName].Text;

                        if (targetStringFrom != "" && targetStringTo != "")
                        {
                            listSearch.Add("(" + 
                                columnInfo.ColumnName + " >= '" + targetStringFrom + "' and " +
                                columnInfo.ColumnName + " <= '" + targetStringTo + "')");
                        }

                        if (targetStringFrom != "" && targetStringTo == "")
                        {
                            listSearch.Add("(" + 
                                columnInfo.ColumnName + " >= '" + targetStringFrom + "' or " + 
                                columnInfo.ColumnName + " is null)");
                        }

                        if (targetStringFrom == "" && targetStringTo != "")
                        {
                            listSearch.Add("(" + 
                                columnInfo.ColumnName + " <= '" + targetStringTo + "' or " + 
                                columnInfo.ColumnName + " is null)");
                        }

                    }
                }
                ((DataGridView)f.Controls["DataGridView"]).DataSource = DataTableToModelList(new OrderQuery().SearchString(listSearch));
            };
        }

        public EventHandler CreateModel(OrderListForm f)
        {
            return (sender, e) =>
            {
                OrderModel orderModel = new OrderModel()
                {
                    //todo 件数0のときのエラー処理
                    受注ID = ((DataGridView)sender).SelectedRows[0].Cells["受注ID"].Value
                };
                MessageBox.Show(orderModel.受注ID.ToString());
            };
        }

        public List<OrderModel> DataTableToModelList(string sql = "")
        {
            if (sql == "") sql = new OrderQuery().SelectString();
            System.Diagnostics.Debug.Print(sql);

            List<OrderModel> listOrderModel = new List<OrderModel>();
            DataTable dt = new Db().ExecuteQuery(sql);

            for (int r = 0; r < dt.Rows.Count; r++)
            {
                OrderModel orderModel = new OrderModel()
                {
                    受注ID = dt.Rows[r]["受注ID"],
                    型式CD = dt.Rows[r]["型式CD"],
                    型式 = dt.Rows[r]["型式"],
                    得意先CD = dt.Rows[r]["得意先CD"],
                    得意先 = dt.Rows[r]["得意先"],
                    工番 = dt.Rows[r]["工番"],
                    内外作 = dt.Rows[r]["内外作"],
                    伝票納期 = dt.Rows[r]["伝票納期"],
                    受注数量 = dt.Rows[r]["受注数量"],
                    伝票金額 = dt.Rows[r]["伝票金額"],
                    本社備考 = dt.Rows[r]["本社備考"],
                    管理備考 = dt.Rows[r]["管理備考"],
                    登録日 = dt.Rows[r]["登録日"]
                };
                listOrderModel.Add(orderModel);
            }
            return listOrderModel;
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

using System;

using System.Collections.Generic;

using System.Windows.Forms;

using System.Data;

using System.Reflection;

using System.Drawing;

namespace ProductionManagement

{

class OrderService

{

public EventHandler TextBoxValueParse(OrderListForm f)

{

return (sender, e) =>

{

TextBox target = (TextBox)sender;

foreach (MemberInfo memberInfo in typeof(OrderModel).GetMembers())

{

if (target.Name == memberInfo.Name || target.Name.Replace("_","") == memberInfo.Name)

{

OrderModel OrderModel = new OrderModel();

PropertyInfo p = typeof(OrderModel).GetProperty(memberInfo.Name);

p.SetValue(OrderModel, target.Text);

if (OrderModel.HasParseError)

{

target.Text = "";

}

else

{

target.Text = p.GetValue(OrderModel).ToString();

}

};

}

public DataGridViewCellEventHandler InsertUpdateFlag(OrderListForm f)

{

return (sender, e) =>

{

DataGridViewRow targetRow = ((DataGridView)sender).Rows[e.RowIndex];

string targetValue = targetRow.Cells[e.ColumnIndex].Value.ToString();

if (targetValue == f.BeforeChangeValue) return;

targetRow.Cells["UpdateFlag"].Value = "1";

targetRow.DefaultCellStyle.ForeColor = Color.Blue;

};

}

public EventHandler CreateUpdateValueList(OrderListForm f)

{

return (sender, e) =>

{

};

}

public EventHandler CreateSearchValueList(OrderListForm f)

{

return (sender, e) =>

{

List<string> listSearch = new List<string>();

foreach (OrderColumnInfo columnInfo in OrderColumnInfo.CreateColumnInfoList())

{

if (columnInfo.SearchTarget == SearchType.None) continue;

if(columnInfo.SearchTarget == SearchType.TargetNormal)

{

string targetString = f.Controls[columnInfo.ColumnName].Text;

if (targetString != "")

{

listSearch.Add(columnInfo.ColumnName + " like '%" + targetString + "%'");

}

if (columnInfo.SearchTarget == SearchType.TargetTerm)

{

string targetStringFrom = f.Controls[columnInfo.ColumnName].Text;

string targetStringTo = f.Controls["_" + columnInfo.ColumnName].Text;

if (targetStringFrom != "" && targetStringTo != "")

{

listSearch.Add("(" +

columnInfo.ColumnName + " >= '" + targetStringFrom + "' and " +

columnInfo.ColumnName + " <= '" + targetStringTo + "')");

}

if (targetStringFrom != "" && targetStringTo == "")

{

listSearch.Add("(" +

columnInfo.ColumnName + " >= '" + targetStringFrom + "' or " +

columnInfo.ColumnName + " is null)");

}

if (targetStringFrom == "" && targetStringTo != "")

{

listSearch.Add("(" +

columnInfo.ColumnName + " <= '" + targetStringTo + "' or " +

columnInfo.ColumnName + " is null)");

}

((DataGridView)f.Controls["DataGridView"]).DataSource = DataTableToModelList(new OrderQuery().SearchString(listSearch));

};

}

public EventHandler CreateModel(OrderListForm f)

{

return (sender, e) =>

{

OrderModel orderModel = new OrderModel()

{

//todo 件数0のときのエラー処理

受注ID = ((DataGridView)sender).SelectedRows[0].Cells["受注ID"].Value

};

MessageBox.Show(orderModel.受注ID.ToString());

};

}

public List<OrderModel> DataTableToModelList(string sql = "")

{

if (sql == "") sql = new OrderQuery().SelectString();

System.Diagnostics.Debug.Print(sql);

List<OrderModel> listOrderModel = new List<OrderModel>();

DataTable dt = new Db().ExecuteQuery(sql);

for (int r = 0; r < dt.Rows.Count; r++)

{

OrderModel orderModel = new OrderModel()

{

受注ID = dt.Rows[r]["受注ID"],

型式CD = dt.Rows[r]["型式CD"],

型式 = dt.Rows[r]["型式"],

得意先CD = dt.Rows[r]["得意先CD"],

得意先 = dt.Rows[r]["得意先"],

工番 = dt.Rows[r]["工番"],

内外作 = dt.Rows[r]["内外作"],

伝票納期 = dt.Rows[r]["伝票納期"],

受注数量 = dt.Rows[r]["受注数量"],

伝票金額 = dt.Rows[r]["伝票金額"],

本社備考 = dt.Rows[r]["本社備考"],

管理備考 = dt.Rows[r]["管理備考"],

登録日 = dt.Rows[r]["登録日"]

};

listOrderModel.Add(orderModel);

}

return listOrderModel;

}

2018-10-30

C# プリミティブな値をクラス

ちょっと試しに。

    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

            List<TestModel> MyModel = new List<TestModel>();

            for (int i = 0; i < 10000; i++)
            {
                MyModel.Add(
                    new TestModel()
                    {
                        Name = "taro",
                        Quantity = new Quantity(i)
                    }
                );
            }

            MessageBox.Show(
                MyModel[9999].NameReverse() + " さん。" + 
                MyModel[9999].Quantity.Value.ToString()
                );

        }
    }

    class TestModel
    {
        public string NameReverse()
        {
            return string.Join("",Name.ToCharArray().Reverse());
        }
        public string Name;
        public Quantity Quantity;
    }

    class Quantity
    {
        public int Value { get; }
        public Quantity(int qty)
        {
            Value = qty;
        }
    }

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

List<TestModel> MyModel = new List<TestModel>();

for (int i = 0; i < 10000; i++)

{

MyModel.Add(

new TestModel()

{

Name = "taro",

Quantity = new Quantity(i)

}

);

}

MessageBox.Show(

MyModel[9999].NameReverse() + " さん。" +

MyModel[9999].Quantity.Value.ToString()

);

}

class TestModel

{

public string NameReverse()

{

return string.Join("",Name.ToCharArray().Reverse());

}

public string Name;

public Quantity Quantity;

}

class Quantity

{

public int Value { get; }

public Quantity(int qty)

{

Value = qty;

}

2018-10-29

C# INotifyPropertyChanged

前回のイベント、デリゲートと似た内容。

    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

            var p = new Person();

            p.PropertyChanged += (s,e) => {
                MessageBox.Show(e.PropertyName);
            };

            //プロパティが変更されるとイベントが発火。
            p.Name = "jiro";
        }
    }

    class Person : INotifyPropertyChanged
    {
        private string _name;
        public string Name
        {
            get { return _name; }
            set
            {
                if (Equals(_name, value)) return;
                _name = value;
                OnPropertyChanged(_name);
                
            }
        }
        
        public event PropertyChangedEventHandler PropertyChanged;

        protected virtual void OnPropertyChanged(string name)
        {
            if (PropertyChanged == null) return;
            PropertyChanged(this, new PropertyChangedEventArgs(name));
        }

    }

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

var p = new Person();

p.PropertyChanged += (s,e) => {

MessageBox.Show(e.PropertyName);

};

//プロパティが変更されるとイベントが発火。

p.Name = "jiro";

}

class Person : INotifyPropertyChanged

{

private string _name;

public string Name

{

get { return _name; }

set

{

if (Equals(_name, value)) return;

_name = value;

OnPropertyChanged(_name);

}

public event PropertyChangedEventHandler PropertyChanged;

protected virtual void OnPropertyChanged(string name)

{

if (PropertyChanged == null) return;

PropertyChanged(this, new PropertyChangedEventArgs(name));

}

2018-10-27

C# IEnumerable

ちょいメモ

    class MyEnum : IEnumerable<String>
    {
        public IEnumerator<string> GetEnumerator()
        {
            for(int i = 1; i <= 10; i++)
            {
                yield return i.ToString();
            }
        }

        //IEnumerator<string>はIEnumerableを継承しているので、
        //２つとも実装。ただし、下記の方は直接指定しないとアクセスできない。

        IEnumerator IEnumerable.GetEnumerator()
        {
            for (int i = 11; i <= 20; i++)
            {
                yield return i.ToString();
            }
        }
    }

    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

            var m = new MyEnum();
            foreach (string s in m)
            {
                System.Diagnostics.Debug.Print(s);//1-10
            }

            IEnumerable n = new MyEnum();
            foreach (string s in n)
            {
                System.Diagnostics.Debug.Print(s);//10-20
            }
        }

    }

class MyEnum : IEnumerable<String>

{

public IEnumerator<string> GetEnumerator()

{

for(int i = 1; i <= 10; i++)

{

yield return i.ToString();

}

//IEnumerator<string>はIEnumerableを継承しているので、

//２つとも実装。ただし、下記の方は直接指定しないとアクセスできない。

IEnumerator IEnumerable.GetEnumerator()

{

for (int i = 11; i <= 20; i++)

{

yield return i.ToString();

}

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

var m = new MyEnum();

foreach (string s in m)

{

System.Diagnostics.Debug.Print(s);//1-10

}

IEnumerable n = new MyEnum();

foreach (string s in n)

{

System.Diagnostics.Debug.Print(s);//10-20

}