MySQLからデータを持ってきてデータグリッドビューに入れて検索のところまで書いた状態。業務に関わる部分は問題ないように変更してある。
Db.cs
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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; } } } |