how to move rows down or up in a datagridview bound to a database

Question

I need to move the selected row up or down in the datagridview when the button is clicked, but the datagridview is bound to an sql database. At first I used this code, but an error occurred, since it is impossible to programmatically add a row to the datagridview collection if the control is bound to data. Is it possible to organize the movement of rows in the database via datagridview?

private void btnUp_Click(object sender, EventArgs e)
{
    DataGridView dgv = gridTasks;
    try
    {
        int totalRows = dgv.Rows.Count;
        // get index of the row for the selected cell
        int rowIndex = dgv.SelectedCells[ 0 ].OwningRow.Index;
        if ( rowIndex == 0 )
            return;
        // get index of the column for the selected cell
        int colIndex = dgv.SelectedCells[ 0 ].OwningColumn.Index;
        DataGridViewRow selectedRow = dgv.Rows[ rowIndex ];
        dgv.Rows.Remove( selectedRow );
        dgv.Rows.Insert( rowIndex - 1, selectedRow );
        dgv.ClearSelection();
        dgv.Rows[ rowIndex - 1 ].Cells[ colIndex ].Selected = true;
    }
    catch { }
}
private void btnUp_Click(object sender, EventArgs e)
{
    DataGridView dgv = gridTasks;
    try
    {
        int totalRows = dgv.Rows.Count;
        // get index of the row for the selected cell
        int rowIndex = dgv.SelectedCells[ 0 ].OwningRow.Index;
        if ( rowIndex == 0 )
            return;
        // get index of the column for the selected cell
        int colIndex = dgv.SelectedCells[ 0 ].OwningColumn.Index;
        DataGridViewRow selectedRow = dgv.Rows[ rowIndex ];
        dgv.Rows.Remove( selectedRow );
        dgv.Rows.Insert( rowIndex - 1, selectedRow );
        dgv.ClearSelection();
        dgv.Rows[ rowIndex - 1 ].Cells[ colIndex ].Selected = true;
    }
    catch { }
}

Answer 1

If bound to an sql database means the DataGridView.DataSource is a DataTable then consider using a BindingSource.

Create a form level BindingSource variable, take your DataTable and assign the BindingSource.DataSource to your table.

Add the following class to your project which is responsible for moving row up/down via the BindingSource.

Notes,

In code if there is a sort order set it needs to be remove which is done in the code below.

To edit a cell, double click or 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;

    }

}

In your form, for your up/down buttons.

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

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

What I've done is set DataGridView.SelectionMode to FullRowSelect as without this when moving row up/down the current cell moves to the first cell which to the user may look odd.

Note: To get a say the current row data via a button click.

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

In the gif below, the first two columns would not normally be shown, they are there to see for demo purpose only. I use the first column to save the order to the database table to persist the order.

EDIT

Simple usage with mocked data

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

See the following SQL which targets a table named OrderDetails. Will add the column and set all existing rows initial values.

Run this query to add the new colum

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;

Then run this query to populate values

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

This gives an idea how to run them with Dapper where SqlStatements class has the SQL above.

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 });
    }
}

Then in code to update row positions write code to perform an UPDATE statement on each row in the DataTable.

Note: I have an article on Microsoft TechNet but not a good idea to post the link here as the site is going away in two months,