パス付きaccdbにエクセルVBAからアクセス

ちょっと試しに。

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

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;

}

ASP.NET Core MVCからMySQLに接続

APS.NET Core MVCは以前に数回さわっただけで、まだ使い始めたばかりなので色々調べながら試している状態。

NuGetより
MySql.Data.EntityFrameworkCore
をインストール

appsettings.json

以下を追加。

"ConnectionStrings": {
"MySQL": "server=xxx; ssl mode=none; port=xxx; user=xxx; password=xxx; database=xxx"
},

"ConnectionStrings": {

"MySQL": "server=xxx; ssl mode=none; port=xxx; user=xxx; password=xxx; database=xxx"

Startup.cs

using Microsoft.EntityFrameworkCore;
を追加。

ConfigureServices
の中に以下を追加。

services.AddDbContext<Models.MvcBasicContext>(options => options.UseMySQL(
Configuration.GetConnectionString("MySQL")));

1 2	services.AddDbContext<Models.MvcBasicContext>(options => options.UseMySQL( Configuration.GetConnectionString("MySQL")));

MvcBasicContext.cs

作成。

    public class MvcBasicContext : DbContext
    {
        public DbSet<Member> Members { get; set; }
        
        public MvcBasicContext(DbContextOptions options): base(options)
        {

        }
    }

public class MvcBasicContext : DbContext

{

public DbSet<Member> Members { get; set; }

public MvcBasicContext(DbContextOptions options): base(options)

{

}

Member.cs

作成。

    public class Member
    {
        public int Id { get; set; }
        public string Name { get; set; }

    }

public class Member

{

public int Id { get; set; }

public string Name { get; set; }

}

MySQL

VPS上のMySQLに今回は手動でテーブルを準備。

Controller

追加。

        private readonly MvcBasicContext _MvcBasicContext;

        public HomeController(MvcBasicContext mvcBasicContext)
        {
            _MvcBasicContext = mvcBasicContext;
        }

        public IActionResult Index()
        {
            return View(_MvcBasicContext.Members.ToList());
        }

private readonly MvcBasicContext _MvcBasicContext;

public HomeController(MvcBasicContext mvcBasicContext)

{

_MvcBasicContext = mvcBasicContext;

}

public IActionResult Index()

{

return View(_MvcBasicContext.Members.ToList());

}

View

試しに呼び出してみる。

@model IEnumerable<test_app.Models.Member>
@foreach (var i in Model)
{
    <p>@i.Id</p>
    <p>@i.Name</p>
}

@model IEnumerable<test_app.Models.Member>

@foreach (var i in Model)

{

}

CentOSに.NET Coreを入れる + FTPの設定

https://www.microsoft.com/net/learn/dotnet/hello-world-tutorial
を参考にする。

sudo rpm -Uvh https://packages.microsoft.com/config/rhel/7/packages-microsoft-prod.rpm
sudo yum install dotnet-sdk-2.1

基本アプリ

dotnet new console -o myapp
cd myapp
dotnet run

ASP.NET CoreWeb

ASP.NET CoreWebアプリケーション
のプロジェクトを作成

Webアプリケーション(モデルビューコントローラー)
を選択。

HTTPS用の構成
のチェックを外す。

ビルド＞xxxの発行
にて作成されたファイルを転送し、転送フォルダにて、
dotnet xxx.dll
のように実行する。

その他

dotnet –info
でバージョン確認できる。

FTPの設定

VSから発行したいのでFTPを入れる。

インストール

yum install -y vsftpd

設定ファイル

/etc/vsftpd/vsftpd.conf

変更

anonymous_enable=NO
listen=YES
listen_ipv6=NO
tcp_wrappers=NO

コメントアウト削除

ascii_upload_enable=YES
ascii_download_enable=YES
chroot_local_user=YES
ls_recurse_enable=YES

追加

use_localtime=YES
allow_writeable_chroot=YES

起動

systemctl start vsftpd

スタートアップ

systemctl enable vsftpd

ファイアーウォール

firewall-cmd –add-service=ftp –zone=public –permanent
firewall-cmd –reload
firewall-cmd –list-all –zone=public

ユーザー制御

userlist_enableを有効にすると、userlist_denyも有効になる。
ブラックリストがデフォなので対象のユーザー名を削除のみ実行。

ブラックリスト(デフォルト)
userlist_enable=YES
userlist_deny=YES or 記述なし

ホワイトリスト
userlist_enable=YES
userlist_deny=NO

ファイルのパス(必要なら)
userlist_file=/etc/vsftpd/user_list

userlist_enableを優先度が高い禁止リスト
/etc/vsftpd/ftpusers

user_listとftpusersから対象のユーザー名を削除する。

WindowsでNginx+PHP+MariaDB環境

以前は必要になるとxamppを使ってたけど、Nginxの環境はないっぽいので、zipを利用して手動で作ってみる。

Nginx

設定ファイルはもともとのコメント部を消して、とりあえずこんな感じ。
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;
        server_name _;
        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;

server_name _;

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;

}

PHP

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

php.ini-development
をコピーしてphp.iniにする。

MariaDB

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

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

ここはちょっと怪しいけど、特にiniを作らなくて相対パスで動いてそう、要調査かな。

バッチ作成

終了は強制にしている。

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

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

start mariadb/bin/mysql.exe -u root

1	start mariadb/bin/mysql.exe -u root

接続

Lanを経由して見る場合、ファイアーウォールに新しい規則を追加する。
とりあえずプライベートの80を許可するだけ見れた。

WordPress

試しにWordpressを入れて見ると、DBの情報入れて送信ところでエラーになった。

エラーの原因は、
mysql_connect()がない。
クオートしない文字列のグローバル定数がない。
ってことらしい。

load.php
の下記の部分に@追加。
$wpdb = @new wpdb( DB_USER, DB_PASSWORD, DB_NAME, DB_HOST );

php.ini
で、
extension_dir = “ext”
のコメントアウトを消す。
extension=php_mysqli.dll
を追記した。
(もともとコメントアウトされているのとはファイル名が違ったので)

LAN経由で見たらcssとかがおかしくなっていた。
設定＞一般＞アドレスのところでlocalhostとなっていたので、閲覧しているPCから正しくたどり着けなかった模様。IPアドレスに変更。

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();

}

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);

}

};

}

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;

}

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;

}

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));

}

テックメモ

当ブログは自分用のメモです。昔のメモをそのまま移行しただけの記事もあり一部不正確な記述もあります。

パス付きaccdbにエクセルVBAからアクセス

C# 遅延評価、IEnumerable

ASP.NET Core MVCからMySQLに接続

appsettings.json

Startup.cs

MvcBasicContext.cs

Member.cs

MySQL

Controller

View

CentOSに.NET Coreを入れる + FTPの設定

基本アプリ

ASP.NET CoreWeb

その他

FTPの設定

WindowsでNginx+PHP+MariaDB環境

Nginx

PHP

MariaDB

バッチ作成

接続

WordPress

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

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

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

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

C# INotifyPropertyChanged