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数据库）并批量插入数据 /// https://www.cnblogs.com/LifeDecidesHappiness/p/15457433.html /// 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; } } } }
namespace ConfigurationManagerSqlServerVersion { /// <summary> /// CollegeInfo /// </summary> public class CollegeInfo { public string Name { get; set; } public string Type { get; set; } public string Year { get; set; } public string Sponsor { get; set; } public string Remark { get; set; } } }
/* SQL-建表CollegeInfo语句 */ USE TestDB; GO IF EXISTS (SELECT * FROM sys.tables WHERE name = 'CollegeInfo') BEGIN DROP TABLE CollegeInfo; END; CREATE TABLE CollegeInfo ( Name NVARCHAR(100) NOT NULL, Type NVARCHAR(10) NOT NULL, [Year] NVARCHAR(4) NOT NULL DEFAULT '2021', Sponsor NVARCHAR(100) NOT NULL, Remark NVARCHAR(MAX) NOT NULL ); SELECT Name, Type, Year, Sponsor, Remark FROM dbo.CollegeInfo;