批量操作

WinForm中利用DataGridView控件对数据进行批量操作

 

有时在项目中，我们需要对数据进行批量的操作，比如：批量删除、批量添加数据等，以实现更高效率的执行 在Winform中，我们利用DataGridView控件来对数据进行批量操作。 效果如下图：

示例代码：

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

namespace BatExecute
{
public partial class Form1 : Form
{
//声明一个全局的DataSet
DataSet ds = new DataSet();
//声明一个全局的SqlDataAdapter
SqlDataAdapter da = new SqlDataAdapter();
//声明一个全局的SQLParameter
SqlParameter param = new SqlParameter();

public Form1()
{
InitializeComponent();
}

/// <summary>
/// 窗体加载事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Form1_Load(object sender, EventArgs e)
{
//窗体加载时，在DataGridView中显示数据
using (da = new SqlDataAdapter("select * from product", DBService.Conn))
{
da.Fill(ds);
this.dataGridView1.DataSource = ds.Tables[0];
}
}

/// <summary>
/// 批量添加数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnAdd_Click(object sender, EventArgs e)
{
//批量添加多条数据
string addSQL = "insert into product (productId,productName,productPrice,productDescription)values (@id,@name,@price,@description)";
//在数据源添加新数据
da.InsertCommand = new SqlCommand(addSQL,DBService.Conn);
param = da.InsertCommand.Parameters.Add("@name", SqlDbType.NVarChar, 20);
//建立参数与数据库字段的关系
param.SourceColumn = "productName";
//获取对应列的值
param.SourceVersion = DataRowVersion.Current;

//下面是用来添加其余的列
param = da.InsertCommand.Parameters.Add("@price",SqlDbType.NVarChar,10);
param.SourceColumn = "productPrice";
param.SourceVersion = DataRowVersion.Current;

param = da.InsertCommand.Parameters.Add("@description",SqlDbType.NVarChar,100);
param.SourceColumn = "productDescription";
param.SourceVersion = DataRowVersion.Current;

param = da.InsertCommand.Parameters.Add("@id",SqlDbType.Int);
param.SourceColumn = "productId";
param.SourceVersion = DataRowVersion.Current;

if (ds.HasChanges()) //如果DataSet对象(即DataGridView，DataGridView与DataSet的关系如同人和影子的关系)改变，则返回真
{
MessageBox.Show("批量添加成功！");
da.Update(ds.Tables[0]);
}
}

/// <summary>
/// 批量修改数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnModify_Click(object sender, EventArgs e)
{
//批量修改数据
string modifySQL = "update product set productName=@name,productprice=@price,productDescription=@description where productid=@id";
//用于更新数据源中的数据
da.UpdateCommand = new SqlCommand(modifySQL,DBService.Conn);
//建立参数与数据库字段的关系
param = da.UpdateCommand.Parameters.Add("@name",SqlDbType.NVarChar,20);
param.SourceColumn = "productName";
param.SourceVersion = DataRowVersion.Current;

param = da.UpdateCommand.Parameters.Add("@price",SqlDbType.NVarChar,10);
param.SourceColumn = "productprice";
param.SourceVersion = DataRowVersion.Current;

param = da.UpdateCommand.Parameters.Add("@description",SqlDbType.NVarChar,100);
param.SourceColumn = "productDescription";
param.SourceVersion = DataRowVersion.Current;

param = da.UpdateCommand.Parameters.Add("@id",SqlDbType.NVarChar,10);
param.SourceColumn = "productid";
param.SourceVersion = DataRowVersion.Original;

if (DialogResult.OK == MessageBox.Show("确认修改吗？", "警告！", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning))
{
if (ds.HasChanges())
{
da.Update(ds.Tables[0]);
MessageBox.Show("批量修改成功！");
}
}
}

/// <summary>
/// 批量删除操作
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnDel_Click(object sender, EventArgs e)
{
string deleteSQL = "delete from product where productid=@id";
da.DeleteCommand = new SqlCommand(deleteSQL,DBService.Conn);

param = da.DeleteCommand.Parameters.Add("@id",SqlDbType.Int);
param.SourceColumn = "productid";
param.SourceVersion = DataRowVersion.Original;

ds.Tables[0].Rows[this.dataGridView1.SelectedRows[0].Index].Delete(); //注意这里，如果没写，不能执行删除
da.Update(ds.Tables[0]);
MessageBox.Show("批量删除成功！");

}
}
}