Saving BindingSource Row Order

Question

Hello all, here's the problem I'm up against.

I have two tables in an SQL database, for simplicity lets say they are "categories" and "items".

categories
(pk)category_id  |  category_name  |  sort_index

items
(pk)item_id | item_name | category_id | sort_index

I have a combo box that you can select the item, then a listbox displays items for that category. The listbox is bound to a BindingSource which is bound to a relation in my DataSet to allow this.

        // When I bind my listbox, I have it set to sort by the "sort order" column
        bsItems.Sort = "sort_order";


       // This is my code to move the position of the sort_index, 
       // thus "bumping" the item up in the bindingsource

        private void cmdUp_Click(object sender, EventArgs e)
        {
            if (listItems.SelectedIndex > 0)
            {
                DataRow selectedRow = ((DataRowView)bsItems[listItems.SelectedIndex]).Row;
                DataRow aboveRow = ((DataRowView)bsItems[listItems.SelectedIndex - 1]).Row;

                int aboveIndex = (int)aboveRow["sort_index"];
                int belowIndex = (int)selectedRow["sort_index"];

                selectedRow["sort_index"] = aboveIndex;
                aboveRow["sort_index"] = belowIndex;
            }
        }

This all works great. The problem I'm facing is, when the user deletes an item, or changes the value of the category_id on the item (To move the item to a different category). The item leaves the BindingSource list, creating a sort_index gap between the item that was above it and below it.

What I think I need to do, is determine the new category_id. Create a DataView that is filtered by the category_id. Get the Count of items in the DataView, and set it's sort_index to that value. Setting it to the bottom of the (new) list.

I can then loop through all the items in the "old" list setting its "sort_index" to its actual index in the list, removing the "gap".

Is this the recommended way of doing this? It seems like a lot of work to simply save the order of items in a BindingSource.

If this is the best way, what events should I use to perform these changes? BindingSource.ListChanged?

Thank you for your time!

Answer 1

Is the ID a GUID?  Is the ID number in a sequence such as 1,2,3...?

Answer 2

Thanks for your reply!

The item_id and category_id fields are the primary keys. They are auto-increment integers generated by the database server.

The sort_index fields are separate from that. It simply stores the order the user wants the items displayed in the Listbox / Combo Box.

Now, when a user changes the category_id of an item, moving it to another category. The sort index column of the category it leaves has a gap, and the category it is moved to may have a duplicate.

I guess my main question is, what event should I subscribe to in a BindingSource that will fire when a user changes the category_id value? So I can then "clean up" the sort_index order of the category it leaves, and the new category its moved to.

Or, is there a better way of storing the order of rows in a BindingSource?

Answer 3

Hi Jonathan,

  Here is  BindingSource.Sort Property,it seems that it can help you solve the problem.    http://msdn.microsoft.com/en-us/library/system.windows.forms.bindingsource.sort.aspx

Sincerely,

Jason Wang

---

Jason Wang [MSFT]
MSDN Community Support | Feedback to us

Answer 4

Hello, you could setup events at the DataTable level and watch/snoop for changes. The idea is to set these events up at the DataTable level. If you are using a DataSet you should be able to set an event up by referencing the specific DataTable. Below is an example I did in VB.NET and converted so hopefully it has translated okay. Note I used Console.WriteLine to see the results, they will appear in the Output window in the IDE. If the Output window is not open you want to open it prior to running the code.

public class Form1
{
	WithEvents bsData As new BindingSource();
	Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles base.Load();
		LoadMockedData();
		DataGridView1.DataSource = bsData;
	}
	private void LoadMockedData()
	{
		using (DataTable MockedData = new DataTable())
		{
			MockedData.Columns.AddRange(new DataColumn[] { new DataColumn("Process", typeof(System.Boolean)), new DataColumn("PartName", typeof(System.String)), new DataColumn("Type", typeof(System.String)), new DataColumn("Role", typeof(System.String)), new DataColumn("partdesc", typeof(System.String)), new DataColumn("partqty", typeof(System.String)) });
			MockedData.Rows.Add(new object[] {false, "Part A", "Brakes", "Role 3", "Vette Disk brakes", null});
			MockedData.Rows.Add(new object[] {true, "Part 99", "Brakes", "Role C", "VW Disk brakes", "6"});
			MockedData.Rows.Add(new object[] {true, "Part B", "Brakes", "Role W", "Ford bed liner", "6"});
			MockedData.AcceptChanges();
			MockedData.ColumnChanging += SnoopEvents_ColumnChanging;
			MockedData.ColumnChanged += SnoopEvents_ColumnChanged;
			MockedData.RowDeleting += SnoopEvents_RowDeleting;
			MockedData.RowDeleted += SnoopEvents_RowDeleted;
			bsData.DataSource = MockedData;
		}
	}
	private void SnoopEvents_ColumnChanged(object sender, System.Data.DataColumnChangeEventArgs e)
	{
		Console.WriteLine("ColumnChanged  Column [{0}] Current [{1}] Proposed [{2}]", e.Column.ColumnName, e.Row[e.Column.ColumnName], e.ProposedValue);
	}
	private void SnoopEvents_ColumnChanging(object sender, System.Data.DataColumnChangeEventArgs e)
	{
		Console.WriteLine("ColumnChanging Column [{0}] Current [{1}] Propsed [{2}]", e.Column.ColumnName, e.Row[e.Column.ColumnName], e.ProposedValue);
	}
	private void SnoopEvents_RowDeleted(object sender, System.Data.DataRowChangeEventArgs e)
	{
		if (e.Action == DataRowAction.Delete)
		{
			Console.WriteLine("Row Deleted [{0}]", e.Row["PartName", DataRowVersion.Original]);
		}
	}
	private void SnoopEvents_RowDeleting(object sender, System.Data.DataRowChangeEventArgs e)
	{
		if (e.Action == DataRowAction.Delete)
		{
			Console.WriteLine("Row Deleting [{0}]", e.Row["PartName"]);
		}
	}
}

---

KSG