C# 削除、追加、更新をした後のデータベースへの出力の方法がわからない。

実現したいこと

C#でDataGridViewに連番、商品名、型番、重量、値段、個数というのがあって表ができています。そして削除、追加、更新、移動、ソートのできるボタンがあり、datagridviewをいじることができます。その後、データベース登録ボタンを押すと、データベースに反映できるようにしたい。そのとき、データベース側ではRegDate(新規に登録されたもののみ)とUpdDate(情報が書き換わったとき)を更新したいです。　　　　　　　　　　　　　　

前提

C#とマイクロソフトのSQLserverをつかっています。

該当のソースコード

僕が組んでみたプログラムなのですが、これだと、一番最後の行が真ん中に来たり、RegDate,UpdDateが更新されなかったりします。
Productクラスを作ってインスタンスをobjectに格納しています。
SQL側は触りたくないです。C#のみで完結させてしまいたいです。

    private bool ClickDbOutPut()
    {
        bool success = false;
        try
        {
            using (SqlConnection conn = new SqlConnection(_conectionString))
            {
                conn.Open();
                SqlTransaction tran = conn.BeginTransaction();

                try
                {
                    List<int> dbSeqNos = new List<int>();
                    using (SqlCommand cmd = new SqlCommand("SELECT SeqNo FROM ProductTable", conn, tran))
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())                            {
                            dbSeqNos.Add((int)reader["SeqNo"]);
                        }
                    }
                    if (dgvList.SelectedRows.Count > 0) 
                    {
                        var selectedRow = dgvList.SelectedRows[0]; //最初に選択されている行を取得。
                        var selectedProduct = selectedRow.Cells[(int)ColProductIdx.Object].Value as Product; 
                        if (selectedProduct != null) 
                        {
                            int deleteSeqNo = selectedProduct.OriginalNo ?? selectedProduct.No;  

                            // 削除
                            SqlCommand deleteCmd = new SqlCommand("delete from ProductTable where SeqNo = @SeqNo", conn, tran); 
                            deleteCmd.Parameters.AddWithValue("@SeqNo", deleteSeqNo); 
                            deleteCmd.ExecuteNonQuery();
                           
                            SqlCommand shiftCmd = new SqlCommand("update ProductTable set SeqNo = SeqNo - 1 where SeqNo > @SeqNo", conn, tran);
                            shiftCmd.Parameters.AddWithValue("@SeqNo", deleteSeqNo); 
                            shiftCmd.ExecuteNonQuery();
                        }
                    }
                    foreach (DataGridViewRow row in dgvList.Rows)
                    {
                        var product = row.Cells[(int)ColProductIdx.Object].Value as Product;

                        if (product == null) continue;
                        int checkSeqNo = product.OriginalNo ?? product.No;
                        // DBから該当レコードを取得
                        SqlCommand selectCmd = new SqlCommand("SELECT * FROM ProductTable WHERE SeqNo = @SeqNo", conn, tran);
                        selectCmd.Parameters.AddWithValue("@SeqNo", checkSeqNo);

                        Product dbProduct = null;
                        using (SqlDataReader reader = selectCmd.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                dbProduct = new Product
                                {
                                    No = (int)reader["SeqNo"],
                                    Name = reader["ProductName"] as string,
                                    ModelNumber = reader["ModelNumber"] as string,
                                    Weight = reader["ProductWeight"] as decimal?,
                                    Price = reader["ProductPrice"] as int?,
                                    Quantity = reader["ProductQuantity"] as int?
                                };
                            }
                        }
                        int targetSeqNo = product.OriginalNo ?? product.No;


                        if (dbProduct != null && ((dbProduct.Name ?? "") != (product.Name ?? "") || (dbProduct.ModelNumber ?? "") != (product.ModelNumber ?? "") || (dbProduct.Weight ?? 0) != (product.Weight ?? 0) || (dbProduct.Price ?? 0) != (product.Price ?? 0) || (dbProduct.Quantity ?? 0) != (product.Quantity ?? 0) || dbProduct.No != product.No))
                        {
                            SqlCommand updateCmd = new SqlCommand(@"
                        UPDATE ProductTable 
                        SET ProductName = @ProductName,
                            ModelNumber = @ModelNumber,
                            ProductWeight = @ProductWeight,
                            ProductPrice = @ProductPrice,
                            ProductQuantity = @ProductQuantity,
                            UpdDate = GETDATE()
                        WHERE SeqNo = @SeqNo", conn, tran);

                            updateCmd.Parameters.AddWithValue("@SeqNo", targetSeqNo);
                            updateCmd.Parameters.AddWithValue("@ProductName", product.Name ?? "");
                            updateCmd.Parameters.AddWithValue("@ModelNumber", product.ModelNumber ?? "");
                            updateCmd.Parameters.AddWithValue("@ProductWeight", (object)product.Weight ?? DBNull.Value);
                            updateCmd.Parameters.AddWithValue("@ProductPrice", (object)product.Price ?? DBNull.Value);
                            updateCmd.Parameters.AddWithValue("@ProductQuantity", (object)product.Quantity ?? DBNull.Value);

                            updateCmd.ExecuteNonQuery();

                        }
                    }
                    foreach (DataGridViewRow row in dgvList.Rows)。
                    {
                        var product = row.Cells[(int)ColProductIdx.Object].Value as Product;

                        if (product == null) continue;

                        int checkSeqNo;
                        if (product.OriginalNo.HasValue)
                        {
                            checkSeqNo = product.OriginalNo.Value;
                        }
                        else
                        {
                            checkSeqNo = -1; 
                        }
                        SqlCommand checkCmd = new SqlCommand("select count(*) from ProductTable where SeqNo = @SeqNo", conn, tran); //コマンド作成
                        checkCmd.Parameters.AddWithValue("@SeqNo", checkSeqNo);        
                        int exists = (int)checkCmd.ExecuteScalar();   

                        if (exists == 0)
                        {
                            SqlCommand maxSeqNoCmd = new SqlCommand("SELECT ISNULL(MAX(SeqNo), 0) FROM ProductTable", conn, tran);
                            int maxSeqNo = (int)maxSeqNoCmd.ExecuteScalar();

                            if (product.No > maxSeqNo + 1)
                            {
                                continue;                                }

                            //SqlCommand shiftCmd = new SqlCommand("UPDATE ProductTable SET SeqNo = SeqNo + 1 WHERE SeqNo >= @SeqNo", conn, tran);
                            //shiftCmd.Parameters.AddWithValue("@SeqNo", product.No);
                            //shiftCmd.ExecuteNonQuery();

                            SqlCommand insertCmd = new SqlCommand(@"INSERT INTO ProductTable  (SeqNo, ProductName, ModelNumber, ProductWeight, ProductPrice, ProductQuantity, RegDate, UpdDate)
                      　　  VALUES (@SeqNo, @ProductName, @ModelNumber, @ProductWeight, @ProductPrice, @ProductQuantity, GETDATE(), GETDATE())", conn, tran);

                            insertCmd.Parameters.AddWithValue("@SeqNo", product.No);
                            insertCmd.Parameters.AddWithValue("@ProductName", product.Name ?? "");
                            insertCmd.Parameters.AddWithValue("@ModelNumber", product.ModelNumber ?? "");
                            insertCmd.Parameters.AddWithValue("@ProductWeight", (object)product.Weight ?? DBNull.Value);
                            insertCmd.Parameters.AddWithValue("@ProductPrice", (object)product.Price ?? DBNull.Value);
                            insertCmd.Parameters.AddWithValue("@ProductQuantity", (object)product.Quantity ?? DBNull.Value);

     insertCmd.ExecuteNonQuery();}}

tran.Commit();
success = true;
foreach (DataGridViewRow row in dgvList.Rows){
var product = row.Cells[(int)ColProductIdx.Object].Value as Product;
if (product != null)
{product.OriginalNo = product.No;}}}
catch (Exception ex) {
tran.Rollback();
MessageBox.Show("DB更新エラー: " + ex.Message); } } }
catch (Exception ex){ }
return success; }

行動規範の内容に同意します

回答1件

更新パターンとしては以下の５つですかね？
(1) 変更なし
(2) 削除
(3) レコード削除により SeqNo のみ変更された(UpdDate変更なし)
(4) 更新
(5) 追加
これらを管理するために行のステータスと、SeqNo のみ変更されたことを示すフラグを Product クラスに追加します。
さらに変更なしの状態にするメソッドも追加します。

csharp
1using System.ComponentModel;
2using System.Data;
3
4public class Product 
5{
6    public Product()
7    {
8        SeqNo = -1;
9        RowState = DataRowState.Added;
10        ChangeSeqNoOnly = false;
11    }
12
13    public int SeqNo { get; set; }
14    public string ProductName { get; set; }
15
16    // ～略～
17
18    [Browsable(false)] public int OriginalNo { get; set; }
19    [Browsable(false)] public DataRowState RowState { get; set; }
20    [Browsable(false)] public bool ChangeSeqNoOnly { get; set; }
21
22    public void AcceptChanges()
23    {
24        OriginalNo = SeqNo;
25        RowState = DataRowState.Unchanged;
26        ChangeSeqNoOnly = false;
27    }
28}

・RowState は、DataGridView.RowValidated イベントで Unchanged から Modified に変更します。
・ChangeSeqNoOnly は削除行の処理を行ったら Unchanged の行のみ true にします。

csharp
1using System;
2using System.Collections.Generic;
3using System.Data;
4using System.Data.SqlClient;
5using System.Linq;
6using System.Windows.Forms;
7
8public partial class Form2 : Form
9{
10    string _conectionString = "接続文字列";
11
12    public Form2()
13    {
14        InitializeComponent();
15    }
16
17    private void Form2_Load(object sender, EventArgs e)
18    {
19        // Product クラスのリストを作って DataSource へ代入
20        var products = new List<Product>();
21        // テストデータ追加
22        products.Add(new Product() { SeqNo = 1, ProductName = "Product1" });
23        products.Add(new Product() { SeqNo = 2, ProductName = "Product2" });
24        products.Add(new Product() { SeqNo = 3, ProductName = "Product3" });
25        foreach (var product in products)
26        {
27            product.AcceptChanges();
28        }
29        dataGridView1.DataSource = products;
30
31        // RowValidated イベントをキャッチ
32        dataGridView1.RowValidated += DataGridView1_RowValidated;
33    }
34
35    private void DataGridView1_RowValidated(object sender, DataGridViewCellEventArgs e)
36    {
37        // 変更のあった行を Modified にする(新規行は初期値の Added のまま)
38        var dgv = (DataGridView)sender;
39        var products = (List<Product>)dgv.DataSource;
40        Product product = products[e.RowIndex];
41        if (product.RowState == DataRowState.Unchanged)
42        {
43            product.RowState = DataRowState.Modified;
44        }
45    }
46
47    // 更新ボタン押下処理
48    private void Button1_Click(object sender, EventArgs e)
49    {
50        var products = (List<Product>)dataGridView1.DataSource;
51        ClickDbOutPut(products);
52    }
53
54    // 更新処理
55
56    private void ClickDbOutPut(List<Product> products)
57    {
58        PreSaveChanges(products);
59        using (SqlConnection conn = new SqlConnection(_conectionString))
60        {
61            conn.Open();
62            SqlTransaction tran = conn.BeginTransaction();
63            try
64            {
65                SaveChanges(conn, products);
66                tran.Commit();
67                PostSaveChages(products);
68            }
69            catch (Exception ex)
70            {
71                tran.Rollback();
72                MessageBox.Show("DB更新エラー: " + ex.Message);
73            }
74        }
75    }
76
77    // 更新前にリストを編集する
78
79    private void PreSaveChanges(List<Product> products)
80    {
81        // SeqNo の最大値を取り出して新規行に SeqNo を割り当てる
82
83        int maxNo = products.Max(p => p.SeqNo);
84        foreach (var product in products.Where(
85                        p => p.RowState == DataRowState.Added))
86        {
87            maxNo++;
88            product.SeqNo = maxNo;
89        }
90
91        // 選択した行を削除し、後続行の SeqNo を -1 する
92
93        foreach (DataGridViewRow deleteRow in dataGridView1.SelectedRows)
94        {
95            int deleteIndex = deleteRow.Index;
96            products[deleteIndex].RowState = DataRowState.Deleted;
97            for (int rowIndex = deleteIndex + 1; 
98                            rowIndex < products.Count; rowIndex++)
99            {
100                var product = products[rowIndex];
101                // 変更されていない行のフラグを On にする
102                if (product.RowState == DataRowState.Unchanged)
103                {
104                    product.ChangeSeqNoOnly = true;
105                    product.RowState = DataRowState.Modified;
106                }
107                product.SeqNo -= 1;
108            }
109        }
110    }
111
112    // 更新処理
113
114    private void SaveChanges(SqlConnection conn, List<Product> products)
115    {
116        foreach (var product in products)
117        {
118            switch (product.RowState)
119            {
120                case DataRowState.Deleted:
121                    // product.OriginalNo で削除
122                    break;
123
124                case DataRowState.Modified:
125                    if (product.ChangeSeqNoOnly)
126                    {
127                        // SeqNo のみ変更する処理
128                    }
129                    else
130                    {
131                        // すべて変更する処理
132                    }
133                    break;
134
135                case DataRowState.Added:
136                    // 行追加処理
137                    break;
138            }
139        }
140    }
141
142    // 次の更新に備えてリセット
143    private void PostSaveChages(List<Product> products)
144    {
145        // 削除行をリストから削除
146        var deleteProducts = products.Where(
147                        p => p.RowState == DataRowState.Deleted).ToArray();
148        foreach (var product in deleteProducts)
149        {
150            products.Remove(product);
151        }
152
153        // 全ての行を Unchanged にする
154        foreach (var product in products)
155        {
156            product.AcceptChanges();
157        }
158    }
159}