DataGridView使用DataTable作为数据源,操作中拖动视图中的数据行进行排序(不允许新建、编辑和删除等操作只能拖动行进行排序),但是排序结束后保存改变到数据库时却发现操作中拖动了几行在数据库中就会新建几行数据(新建的数据就是操作中拖动的行)。不想遍历整个表进行更新,但是使用SqlCommandBuild方法的Update老多出很多行来,这是为什么呢?
备注:排序结果按照DataGridView的行号排列,更新数据库主要是将行号更新到数据库中,也就是说数据库中的orderid就是DataGridView的行号。
备注:排序结果按照DataGridView的行号排列,更新数据库主要是将行号更新到数据库中,也就是说数据库中的orderid就是DataGridView的行号。
SqlDataAdapter sda;//修改orderid
SqlCommandBuilder scb = new SqlCommandBuilder(sda);
sda.Update(ds);
this.dataGridView1.DataSource = ds.Tables[0];
{
// 数据表
DataTable dataTable = new DataTable();
SqlDataAdapter da = new SqlDataAdapter();
private int indexOfItemUnderMouseToDrag = -1;
private int indexOfItemUnderMouseToDrop = -1;
private int indexOfItemUnderMouseOver = -1; public MainForm()
{
InitializeComponent();
} private void MainForm_Load(object sender, EventArgs e)
{
using (SqlConnection conn = new SqlConnection("data source=127.1;initial catalog=stlivead;user id=sa;password=sa;"))
{
SqlCommand cmd = new SqlCommand("select * from db", conn);
da.SelectCommand = cmd;
da.Fill(dataTable);
} dataGridView.DataSource = dataTable;
} private void dataGridView_MouseDown(object sender, MouseEventArgs e)
{
var hitTest = dataGridView.HitTest(e.X, e.Y);
indexOfItemUnderMouseToDrag = hitTest.RowIndex;
if (indexOfItemUnderMouseToDrag > -1)
{
Size dragSize = SystemInformation.DragSize;
dragBoxFromMouseDown = new Rectangle(new Point(e.X - (dragSize.Width / 2), e.Y - (dragSize.Height / 2)), dragSize);
}
} private void dataGridView_MouseUp(object sender, MouseEventArgs e)
{
dragBoxFromMouseDown = Rectangle.Empty;
} private void dataGridView_DragOver(object sender, DragEventArgs e)
{
Point p = dataGridView.PointToClient(new Point(e.X, e.Y)); var hitTest = dataGridView.HitTest(p.X, p.Y);
if (hitTest.Type != DataGridViewHitTestType.Cell || hitTest.RowIndex == indexOfItemUnderMouseToDrag)
{
e.Effect = DragDropEffects.None;
OnRowDragOver(-1);
return;
} e.Effect = DragDropEffects.Move;
} private void dataGridView_DragDrop(object sender, DragEventArgs e)
{
Point p = dataGridView.PointToClient(new Point(e.X, e.Y)); var hitTest = dataGridView.HitTest(p.X, p.Y);
if (hitTest.Type != DataGridViewHitTestType.Cell || hitTest.RowIndex == indexOfItemUnderMouseToDrag + 1)
return; indexOfItemUnderMouseToDrop = hitTest.RowIndex; // * 执行拖放操作,我觉得可能是这里有问题,因为拖放要移除源行新建一个目的行,不知道为什么数据库更新的时候会自己insert拖动的行 var tempRow = dataTable.NewRow();
tempRow.ItemArray = dataTable.Rows[indexOfItemUnderMouseToDrag].ItemArray;
((DataTable)dataGridView.DataSource).Rows.RemoveAt(indexOfItemUnderMouseToDrag); if (indexOfItemUnderMouseToDrag < indexOfItemUnderMouseToDrop)
indexOfItemUnderMouseToDrop--; ((DataTable)dataGridView.DataSource).Rows.InsertAt(tempRow, indexOfItemUnderMouseToDrop);
//选中拖动的行
dataGridView.Rows[indexOfItemUnderMouseToDrop].Selected = true;
//行号重排
for (int i = 0; i < dataGridView.Rows.Count; i++)
{
dataGridView.Rows[i].Cells["orderid"].Value = i + 1;
}
} private void toolStripButton1_Click(object sender, EventArgs e)
{
SqlCommandBuilder scb = new SqlCommandBuilder(da);
da.Update(dataTable);
dataTable.AcceptChanges();
}
}
for (int i = 0; i < dataGridView.Rows.Count; i++)
{
dataGridView.Rows[i].Cells["orderid"].Value = i + 1;
}
怎么每次拖动时让排序号加1啊!