C# - EX05 SHOWDATA, UPDATA 추가

아래 코딩에 이어서 SHOW DATA 와 UPDATE DATA 을 활용 해서 코딩하기!
SHOW DATA는 수정후 어디어디 어떻게 변경이 되었는지 알려주고 UPDATE DATA는 수정된 부분을 DB에 업데이트 해주는 기능!

버튼 2개를 추가!!  그리고 코딩후!

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Ex05
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "SERVER=.;DATABASE=Northwind;INTEGRATED SECURITY=SSPI;";

            //2. 명령 객체 생성 및 [SQL, 연결객체] 설정
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "SELECT customerid, companyname, contactname, contacttitle, country, phone, fax FROM customers ";

            //데이터 처리 어댑처 사용
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();

            // Open - > read &set -> Close 를 해준다.
            //이미 열려있으면 Open 및 Close 생략 해준다.
            da.Fill(ds, "Customers"); //DataSEt에 Customers 테이블 만들고 그 테이블에 데이터를 저장

            dataGridView1.DataSource = ds;
            dataGridView1.DataMember = "Customers"; //DataSet 내부의 테이블 이름

        }

        //DataGridView 셀을 클릭 했을때 이벤트
        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if(e.RowIndex < 0)
            {
                return;
            }
                                 //클릭한 행 가져온다(e.RowIndex)
            DataGridViewRow row = dataGridView1.Rows[e.RowIndex];
                         // 그 셀중에 0 번째 값을 가져온다. 문자열로 변환해 넘겨준다
            string customerId = row.Cells[0].Value.ToString();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "SERVER=.;DATABASE=Northwind;INTEGRATED SECURITY=SSPI;";

            //2. 명령 객체 생성 및 [SQL, 연결객체] 설정
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText =
            "SELECT orderid,orderdate,requireddate,shipcountry " +
            "FROM orders WHERE customerid = @customerid";
            cmd.Parameters.AddWithValue("@customerid", customerId);

          
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds, "Orders");
            //dataGridView2.DataSource = ds.Tables[0];
            dataGridView2.DataSource = ds.Tables["Orders"];

        }

        private void dataGridView2_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex < 0)
                return;

            DataGridViewRow row = dataGridView2.Rows[e.RowIndex];
            int orderId = (int)row.Cells[0].Value;

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
                "SERVER=.;" +
                "DATABASE=Northwind;" +
                "INTEGRATED SECURITY=SSPI;";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText =
            "SELECT orderid, productid, unitprice, quantity, discount " +
            "FROM [order details] WHERE orderid = @orderid";
            cmd.Parameters.AddWithValue("@orderid", orderId);

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            //DataTable dt = new DataTable();
            //da.Fill(dt);
            //dataGridView3.DataSource = dt;

            //DATASHOW 와 DATAUPDATA를 하기 위해 윗 부분 주석 아래 추가
            DataSet ds = new DataSet();
            da.Fill(ds, "OrderDetails");
            dataGridView3.DataSource = ds;
            dataGridView3.DataMember = "OrderDetails";

        }

        private void SetCommand(SqlDataAdapter da)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
                "SERVER=.;" +
                "DATABASE=Northwind;" +
                "INTEGRATED SECURITY=SSPI;";

            string isql =
                "INSERT INTO [Order Details] " +
                "VALUES (@OrderID, @ProductID, @Quantity, " +
                "@UnitPrice, @Discount)";
            SqlCommand icmd = new SqlCommand(isql, conn);
            icmd.Parameters.Add(
                "@OrderID", SqlDbType.Int, 4, "OrderID");
            icmd.Parameters.Add(
                "@ProductID", SqlDbType.Int, 4, "ProductID");
            icmd.Parameters.Add(
                "@Quantity", SqlDbType.SmallInt, 2, "Quantity");
            icmd.Parameters.Add(
                "@UnitPrice", SqlDbType.Money, 8, "UnitPrice");
            icmd.Parameters.Add(
                "@Discount", SqlDbType.Real, 4, "Discount");
            da.InsertCommand = icmd;

            ///////////////////////////////////////////////

            string usql =
                "UPDATE [Order Details] SET " +
                "Quantity = @Quantity, UnitPrice = @UnitPrice, " +
                "Discount = @Discount WHERE " +
                "OrderID = @OrderID AND ProductID = @ProductID";
            SqlCommand ucmd = new SqlCommand(usql, conn);
            ucmd.Parameters.Add(
                "@OrderID", SqlDbType.Int, 4, "OrderID"); // 마지막 전달인자는 데이터 테이블의 컬럼명
            ucmd.Parameters.Add(
                "@ProductID", SqlDbType.Int, 4, "ProductID");
            ucmd.Parameters.Add(
                "@Quantity", SqlDbType.SmallInt, 2, "Quantity");
            ucmd.Parameters.Add(
                "@UnitPrice", SqlDbType.Money, 8, "UnitPrice");
            ucmd.Parameters.Add(
                "@Discount", SqlDbType.Real, 4, "Discount");
            da.UpdateCommand = ucmd;

            /////////////////////////////////////////////

            string dsql =
                "DELETE [Order Details] WHERE " +
                "OrderID = @OrderID AND ProductID = @ProductID";
            SqlCommand dcmd = new SqlCommand(dsql, conn);
            dcmd.Parameters.Add(
                "@OrderID", SqlDbType.Int, 4, "OrderID");
            dcmd.Parameters.Add(
                "@ProductID", SqlDbType.Int, 4, "ProductID");
            da.DeleteCommand = dcmd;
        }

        private void ShowDataTable()
        {
            DataSet ds =
                dataGridView3.DataSource as DataSet;

            if (ds != null)
            {
                //DataTable dt = ds.Tables["OrderDetails"];
                DataTable dt = ds.Tables[0];

                string message = string.Empty;
                foreach (DataRow row in dt.Rows)
                {
                    foreach (DataColumn dc in dt.Columns)
                    {
                        object originalValue, currentValue;
                        if (row.RowState == DataRowState.Added)
                        {
                            originalValue = "추가된 행";
                            currentValue = row[dc, DataRowVersion.Current];
                        }
                        else if (row.RowState == DataRowState.Deleted)
                        {
                            originalValue = row[dc, DataRowVersion.Original];
                            currentValue = "삭제된 행";
                        }
                        else
                        {
                            originalValue = row[dc, DataRowVersion.Original];
                            currentValue = row[dc, DataRowVersion.Current];
                        }
                        message +=
                            string.Format(
                            "[{0} | {1}]", originalValue, currentValue);
                    }
                    message +=
                        string.Format(
                        "[{0}]\r\n", row.RowState);
                }
                MessageBox.Show(message);
            }
        }

        //SHOWDATA 버튼 클릭
        private void button2_Click(object sender, EventArgs e)
        {
            ShowDataTable();
        }

        //UPDATE 버튼 클릭
        private void button3_Click(object sender, EventArgs e)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            SetCommand(da);

            DataSet ds = dataGridView3.DataSource as DataSet;
            da.Update(ds.Tables[0]);

            MessageBox.Show("수정했습니다.");

        }
    }
}