using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using ConfigurationManagerSqlServerVersion.Helpers;
using MessageBoxer;

namespace ConfigurationManagerSqlServerVersion
{
    /// <summary>
    ///     WinForm里实现选择一个DataGridView指定行后同步到另外一个DataGridView中（基于Sql Server数据库）并批量插入数据
    ///     LDH @   2021-10-25
    /// </summary>
    public partial class FrmMain : Form
    {
        /// <summary>
        ///     数据库连接字符串
        /// </summary>
        public string ConnectionString = "Data Source =.; Initial Catalog = TestDB; User ID = sa; Password=123456";

        public FrmMain()
        {
            InitializeComponent();
            dgvBottom.CellValidating += DgvBottom_CellValidating;
            dgvBottom.CellEndEdit += DgvBottom_CellEndEdit;

            // 给DataGridView添加右键菜单
            dgvBottom.CellMouseDown += DgvBottom_CellMouseDown;
        }

        /// <summary>
        ///     在DataGridView控件上右键鼠标，弹出右键菜单
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void DgvBottom_CellMouseDown(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
                if (e.RowIndex >= 0)
                {
                    // 若行已是选中状态就不再进行设置
                    if (dgvBottom.Rows[e.RowIndex].Selected == false)
                    {
                        dgvBottom.ClearSelection();
                        dgvBottom.Rows[e.RowIndex].Selected = true;
                    }

                    //弹出操作菜单
                    contextMenuStrip1.Show(MousePosition.X, MousePosition.Y);
                }
        }

        /// <summary>
        ///     单元格结束编辑事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void DgvBottom_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            // Clear the row error in case the user presses ESC.   
            dgvBottom.Rows[e.RowIndex].ErrorText = string.Empty;

            //var dt = (DataTable)dgvBottom.DataSource;
        }

        /// <summary>
        ///     验证不能输入为空
        ///     https://vimsky.com/examples/detail/csharp-event-system.windows.forms.datagridview.cellendedit.html
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void DgvBottom_CellValidating(object sender, DataGridViewCellValidatingEventArgs e)
        {
            var headerText = dgvBottom.Columns[e.ColumnIndex].HeaderText;

            // Abort validation if cell is not in the Type column.
            if (!headerText.Equals("Type")) return;

            // Confirm that the cell is not empty.
            if (string.IsNullOrEmpty(e.FormattedValue.ToString()))
            {
                dgvBottom.Rows[e.RowIndex].ErrorText = "Type Name must not be empty";
                e.Cancel = true;
            }
        }

        private void FrmMain_Load(object sender, EventArgs e)
        {
            BindData();
        }

        private void BindData()
        {
            dgvTop.DataSource = null;
            dgvTop.DataSource = GetDataSource();

            SetStyleForDataGridView(dgvTop, false);
        }

        /// <summary>
        ///     设置DataGridView样式
        /// </summary>
        /// <param name="dgv">DataGridView</param>
        /// <param name="canEditCell">是否可以编辑单元格</param>
        private void SetStyleForDataGridView(DataGridView dgv, bool canEditCell)
        {
            dgv.AllowUserToAddRows = false; // 不向用户显示添加行的选项
            dgv.AllowUserToResizeRows = false; // 禁止用户改变DataGridView所有行的行高  
            dgv.AutoGenerateColumns = false; // 禁止自动生成列
            dgv.SelectionMode = DataGridViewSelectionMode.FullRowSelect; //  整行选取
            dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill; // 使数据区自动充满整个面板
            dgv.EditMode =
                canEditCell ? DataGridViewEditMode.EditOnEnter : DataGridViewEditMode.EditProgrammatically; // 是否可以编辑单元格

            dgv.ClearSelection(); // 取消选中状态
        }

        private DataTable GetDataSource()
        {
            var sql = "SELECT TOP 100 Name,Type,Year,Sponsor,Remark FROM dbo.CollegeInfo";
            var dt = SqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, sql).Tables[0];
            return dt;
        }


        private void dgvTop_SelectionChanged(object sender, EventArgs e)
        {
            dgvBottom.DataSource = null;

            var totalDt = (DataTable)dgvTop.DataSource;

            // 选中Rows放在集合中
            var rowColls = dgvTop.SelectedRows;

            // 克隆一个表结构
            var selectedDt = totalDt.Clone();
            for (var i = 0; i < rowColls.Count; i++)
            {
                var dr = (rowColls[i].DataBoundItem as DataRowView)?.Row;
                selectedDt.ImportRow(dr);
            }

            dgvBottom.DataSource = selectedDt;

            SetStyleForDataGridView(dgvBottom, true);
        }

        private void FrmMain_FormClosing(object sender, FormClosingEventArgs e)
        {
            var dr = ShowMessage.GetUserYesOrNo("您确定现在退出系统吗？", "温馨提示");
            if (dr == DialogResult.Yes)
                Environment.Exit(0);
            else
                e.Cancel = true;
        }

        /// <summary>
        ///     新增选中行记录到数据库
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void tsmiInsert_Click(object sender, EventArgs e)
        {
            var dt = (DataTable)dgvBottom.DataSource;
            var result = BatchInsertIntoDatabase(dt);
            if (result)
            {
                ShowMessage.Notification("批量插入数据库表CollegeInfo中成功！");
                BindData();
            }
        }

        /// <summary>
        ///     一次性把DataTable中的数据插入数据库
        /// </summary>
        /// <param name="source">DataTable数据源</param>
        /// <returns>true - 成功，false - 失败</returns>
        public bool BatchInsertIntoDatabase(DataTable source)
        {
            SqlTransaction tran = null; // 声明一个事务对象
            try
            {
                using (var con = new SqlConnection(ConnectionString))
                {
                    con.Open(); // 打开数据库连接

                    using (tran = con.BeginTransaction())
                    {
                        using (var copy = new SqlBulkCopy(con, SqlBulkCopyOptions.Default, tran))
                        {
                            copy.DestinationTableName = "[CollegeInfo]"; // 指定服务器上目标表的名称
                            copy.WriteToServer(source); // 执行把DataTable中的数据写入DB  注意DataTable表结构要与数据库表结构一样
                            tran.Commit(); // 提交事务

                            return true; // 执行成功
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                tran?.Rollback();

                // 记录日志
                Console.WriteLine(ex.Message);

                return false;
            }
        }
    }
}