using System;

using System.Data;

using System.Windows.Forms;

using DotNet.Utilities;

namespace WindowsFormsApp1

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        DataTableCollection dt = null;

        //查询原始内容

        private void button1_Click(object sender, EventArgs e)

        {

            string sql = @"SELECT r.RegionID, RTRIM(r.RegionDescription)RegionDescription FROM Region AS r";

            dt = SqlHelper.GetTableText(sql, null);

            dataGridView1.DataSource = dt[0];

        }

        //增加内容

        private void button2_Click(object sender, EventArgs e)

        {

            DataRow dr = dt[0].NewRow();

            dr["RegionID"] = textBox2.Text;

            dr["RegionDescription"] = textBox3.Text;

            dt[0].Rows.Add(dr);

        }

        //修改内容

        private void button3_Click(object sender, EventArgs e)

        {

            DataRow dr = dt[0].Rows[dataGridView1.CurrentRow.Index];

            dr.BeginEdit();

            dr["RegionID"] = textBox2.Text;

            dr["RegionDescription"] = textBox3.Text;

            dr.EndEdit();

        }

        //删除内容

        private void button4_Click(object sender, EventArgs e)

        {

            dt[0].Rows[dataGridView1.CurrentRow.Index].Delete();

        }

        //保存内容，根据DataTable生成Sql语句

        private void button5_Click(object sender, EventArgs e)

        {

            textBox4.Clear();

            DataTable dtSave = dt[0].GetChanges();

            if (dtSave != null)

            {

                foreach (DataRow item in dtSave.Rows)

                {

                    if (item.RowState == DataRowState.Added)

                    {

                        string str = @"

                                INSERT INTO Region

                                (

                                    RegionID,

                                    RegionDescription

                                )

                                VALUES

                                (

                                    " + item.ItemArray[0] + @",

                                    '" + item.ItemArray[1] + @"'

                                )

                                ";

                        textBox4.Text += str + "\r\n";

                        textBox4.Text += "----------------------------------------------------------------------------------------" + "\r\n";

                    }

                    else if (item.RowState == DataRowState.Modified)

                    {

                        string str = @"

                                UPDATE Region

                                SET

                                    RegionDescription = '" + item.ItemArray[1] + @"'

                                WHERE RegionID=" + item.ItemArray[0];

                        textBox4.Text += str + "\r\n";

                        textBox4.Text += "----------------------------------------------------------------------------------------" + "\r\n";

                    }

                    else if (item.RowState == DataRowState.Deleted)

                    {

                        string str = @"

                                DELETE FROM Region

                                WHERE RegionID=" + item[0, DataRowVersion.Original];

                        textBox4.Text += str + "\r\n";

                        textBox4.Text += "----------------------------------------------------------------------------------------" + "\r\n";

                    }

                }

                //这里增加事务执行SQL语句到数据库

                //dt[0].AcceptChanges();//保存后清除行状态

            }

        }

        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)

        {

            DataRow dr = dt[0].Rows[dataGridView1.CurrentRow.Index];

            textBox2.Text = dr["RegionID"].ToString();

            textBox3.Text = dr["RegionDescription"].ToString();

        }

    }

}