Csharp 如何在绑定到数据库的datagridview中向上或向下移动行

如果绑定到SQL数据库意味着DataGridView.DataSource是DataTable，则考虑使用BindingSource.

创建一个窗体级别的BindingSource变量，获取DataTable并将BindingSource.DataSource分配给您的表.

将以下类添加到您的项目中，该类负责通过BindingSource向上/向下移动行.

备注:

在代码中，如果设置了排序顺序，则需要删除它，这在下面的代码中完成.

若要编辑单元格，请双击或F2.

public static class BindingSourceExtensions
{
    /// <summary>
    /// Move row up by one
    /// </summary>
    /// <param name="sender"></param>
    public static void MoveRowUp(this BindingSource sender)
    {
        if (!string.IsNullOrWhiteSpace(sender.Sort))
        {
            sender.Sort = "";
        }

        var newIndex = Convert.ToInt32((sender.Position == 0) ? 
            0 : 
            sender.Position - 1);

        var dt = (DataTable)sender.DataSource;
        DataRow rowToMove = ((DataRowView)sender.Current).Row;
        var newRow = dt.NewRow();

        newRow.ItemArray = rowToMove.ItemArray;
        dt.Rows.RemoveAt(sender.Position);
        dt.Rows.InsertAt(newRow, newIndex);

        dt.AcceptChanges();

        sender.Position = newIndex;

    }

    /// <summary>
    /// Move row down by one
    /// </summary>
    /// <param name="sender"></param>
    public static void MoveRowDown(this BindingSource sender)
    {
        if (!string.IsNullOrWhiteSpace(sender.Sort))
        {
            sender.Sort = "";
        }

        var upperLimit = sender.Count - 1;
        var newIndex = Convert.ToInt32((sender.Position + 1 >= upperLimit) ? 
            upperLimit : 
            sender.Position + 1);

        var dt = (DataTable)sender.DataSource;
        DataRow rowToMove = ((DataRowView)sender.Current).Row;
        var newRow = dt.NewRow();

        newRow.ItemArray = rowToMove.ItemArray;
        dt.Rows.RemoveAt(sender.Position);
        dt.Rows.InsertAt(newRow, newIndex);

        dt.AcceptChanges();

        sender.Position = newIndex;

    }

}

在您的表单中，用于您的向上/向下按钮.

private void upButton_Click(object sender, EventArgs e)
{
    _bindingSource.MoveRowUp();
}

private void downButton_Click(object sender, EventArgs e)
{
    _bindingSource.MoveRowDown();
}

我所做的是将DataGridView.SelectionMode设置为FullRowSelect，如果不设置此项，则在上下移动行时，当前单元格将移动到第一个单元格，这对用户来说可能很奇怪.

注意:要获得当前行数据，只需点击按钮.

var row = ((DataRowView)_bindingSource.Current).Row;

在下面的gif中，前两列通常不会显示，它们仅用于演示目的.我使用第一列将订单保存到数据库表中以持久化订单.

EDIT个

对模拟数据的简单使用

public partial class Form1 : Form
{
    private readonly BindingSource _bindingSource = new();
    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        DataTable dt = new DataTable();
        dt.Columns.Add(new DataColumn() 
            { ColumnName = "FirstName", DataType = typeof(string) });
        dt.Columns.Add(new DataColumn() 
            { ColumnName = "LastName", DataType = typeof(string) });
        dt.Rows.Add("Karen", "Payne");
        dt.Rows.Add("Anne", "Smith");
        dt.Rows.Add("Jane", "Adams");
        _bindingSource.DataSource = dt;
        dataGridView1.DataSource = _bindingSource;
    }

    private void upButton_Click(object sender, EventArgs e)
    {
        _bindingSource.MoveRowUp();
    }

    private void downButton_Click(object sender, EventArgs e)
    {
        _bindingSource.MoveRowDown();
    }

    private void currentButton_Click(object sender, EventArgs e)
    {
        var row = ((DataRowView)_bindingSource.Current).Row;
        var name = $"{row.Field<string>("FirstName")} " +
                   $"row.Field<string>(\"FirstName\")}}";
    }
}

To persist row position

请参阅以下针对名为OrderDetails表的SQL.将添加该列并设置所有现有行的初始值.

运行此查询以添加新列

IF EXISTS
(
    SELECT *
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'OrderDetails'
          AND COLUMN_NAME = 'RowPosition'
)
BEGIN
    ALTER TABLE dbo.OrderDetails DROP COLUMN RowPosition;
END;

ALTER TABLE dbo.OrderDetails ADD RowPosition INT NULL;

然后运行此查询以填充值

UPDATE dbo.OrderDetails
SET @MaxSurrogateKey=RowPosition = @MaxSurrogateKey + 1
WHERE RowPosition IS NULL;

这给出了如何使用Dapper运行它们的 idea ，其中SqlStatements类具有上面的SQL.

public class Operations
{
    private readonly IDbConnection _cn = 
        new SqlConnection(DataConnections.Instance.MainConnection);

    public async Task ConfigureOrderDetailsForRowPosition()
    {
        await _cn.ExecuteAsync(SqlStatements.OrderDetailsConfiguration);
        await _cn.ExecuteAsync(SqlStatements.OrderDetailsPopulateRowPosition,
            new { MaxSurrogateKey = 0 });
    }
}

然后，在更新行位置的代码中，编写代码对DataTable中的每一行执行UPDATE语句.

注:我在微软TechNet上有一篇文章，但在这里发布链接并不是一个好主意，因为该网站将在两个月后关闭，