locked
How to save the order of the rows in the DataGrid? RRS feed

  • Question

  • In MVVM Light and Entity Framework I have written the following code that, quite simply, it allows me to display a list of customers within a Datagrid. My problem is that I can't, once you have changed the order of the rows by moving them or at the top or bottom, to save these changes. I initially decided to use the ObservableCollection Move, but unfortunately I can not find a good solution.

    MyContext:

    public partial class Customer
        {
            public int ID { get; set; }
            public string LastName { get; set; }
            public string FirstName { get; set; }
        }
        public class MyContext : DbContext
        {
            public MyContext()
                : base("name=MyContext")
            {
                Database.SetInitializer<MyContext>(new CreateDatabaseIfNotExists<MyContext>());
            }
            public DbSet<Customer> Customers { get; set; }
        }

    XAML:

    <Grid x:Name="LayoutRoot">
            <DataGrid ItemsSource="{Binding CustomerOC, Mode=TwoWay, UpdateSourceTrigger=PropertyChanged}" SelectedItem="{Binding SelectedItem, Mode=TwoWay, UpdateSourceTrigger=PropertyChanged}" AutoGenerateColumns="False" SelectionUnit="FullRow"  SelectionMode="Single" IsReadOnly="True" HorizontalAlignment="Left" Margin="10,45,0,0" VerticalAlignment="Top" Height="214" Width="200">
                <DataGrid.Columns>
                    <DataGridTextColumn Header="ID" Binding="{Binding ID}" />
                    <DataGridTextColumn Header="FirstName" Binding="{Binding FirstName}" />
                    <DataGridTextColumn Header="LastName" Binding="{Binding LastName}" Width="*" />
                </DataGrid.Columns>
            </DataGrid>
            <Button Content="Select" Command="{Binding SearchCommand}" HorizontalAlignment="Left" Margin="10,10,0,0" VerticalAlignment="Top" Width="75"/>
            <Button Content="Save" Command="{Binding SaveCommand}" HorizontalAlignment="Left" Margin="90,10,0,0" VerticalAlignment="Top" Width="75"/>
            <Button Content="Up" Command="{Binding UpCommand}" HorizontalAlignment="Left" Margin="215,48,0,0" VerticalAlignment="Top" Width="37"/>
            <Button Content="Down" Command="{Binding DownCommand}" HorizontalAlignment="Left" Margin="215,73,0,0" VerticalAlignment="Top" Width="37"/>
        </Grid>

    MainViewModel:

    public class MainViewModel : ViewModelBase
        {
            List<Customer> entityCustomer;
    
            ObservableCollection<Customer> _CustomerOC;
            public ObservableCollection<Customer> CustomerOC
            {
                get
                {
                    return _CustomerOC;
                }
                set
                {
                    Set(() => CustomerOC, ref _CustomerOC, value);
                }
            }
            private Customer _SelectedItem;
            public Customer SelectedItem
            {
                get
                {
                    return _SelectedItem;
                }
                set
                {
                    Set(() => SelectedItem, ref _SelectedItem, value);
                }
            }
    
            private int _Id;
            public int ID
            {
                get
                {
                    return _Id;
                }
    
                set
                {
                    Set(() => ID, ref _Id, value);
                }
            }
    
            private string _FirstName = string.Empty;
            public string FirstName
            {
                get
                {
                    return _FirstName;
                }
    
                set
                {
                    Set(() => FirstName, ref _FirstName, value);
                }
            }
    
            private string _LastName = string.Empty;
            public string LastName
            {
                get
                {
                    return _LastName;
                }
    
                set
                {
                    Set(() => LastName, ref _LastName, value);
                }
            }
            public RelayCommand SearchCommand { get; private set; }
            public RelayCommand UpCommand { get; private set; }
            public RelayCommand DownCommand { get; private set; }
            public RelayCommand SaveCommand { get; private set; }
            public MainViewModel()
            {
                SearchCommand = new RelayCommand(() => Search());
                UpCommand = new RelayCommand(() => Up());
                DownCommand = new RelayCommand(() => Down());
                SaveCommand = new RelayCommand(() => Save());
            }
            private void Search()
            {
                using (var ctx = new MyContext())
                {
                    ctx.Configuration.LazyLoadingEnabled = false;
                    ctx.Configuration.ProxyCreationEnabled = false;
    
                    entityCustomer = ctx.Customers.ToList();
    
                    //datagrid
                    CustomerOC = new ObservableCollection<Customer>(entityCustomer);
                }
            }
            private void Up()
            {
                var currentIndex = CustomerOC.IndexOf(SelectedItem);
    
                if (currentIndex > 0)
                {
                    int upIndex = currentIndex - 1;
                    //move the items
                    CustomerOC.Move(upIndex, currentIndex);
                }
            }
            private void Down()
            {
                var currentIndex = CustomerOC.IndexOf(SelectedItem);
    
                if (currentIndex + 1 < CustomerOC.Count)
                {
                    int downIndex = currentIndex + 1;
                    //move the items
                    CustomerOC.Move(downIndex, currentIndex);
                }
            }
            private void Save()
            {
                using (var ctx = new MyContext())
                {
                    foreach (Customer c in entityCustomer)
                    {
                        ctx.Customers.Attach(c);
                        ctx.SaveChanges();
                    }
                }
            }
        }

    • Moved by Caillen Monday, May 18, 2015 2:18 AM
    Saturday, May 16, 2015 3:28 PM

Answers

  • While you write, I tried this:

    private void Up()
            {
                var currentIndex = CustomerOC.IndexOf(SelectedItem);
    
                if (currentIndex > 0)
                {
                    int upIndex = currentIndex - 1;
                    
                    int tmp = CustomerOC[currentIndex - 1].CurrentIndex;
                    CustomerOC[currentIndex - 1].CurrentIndex = CustomerOC[currentIndex].CurrentIndex;
                    CustomerOC[currentIndex].CurrentIndex = tmp;
                    CustomerOC.Move(upIndex, currentIndex);
                    //SelectedItem.CurrentIndex -= 1; //CurrentIndex is the new column/property
                }
            }
            private void Down()
            {
                var currentIndex = CustomerOC.IndexOf(SelectedItem);
    
                if (currentIndex + 1 < CustomerOC.Count)
                {
                    int downIndex = currentIndex + 1;
    
                    int tmp = CustomerOC[currentIndex + 1].CurrentIndex;
                    CustomerOC[currentIndex + 1].CurrentIndex = CustomerOC[currentIndex].CurrentIndex;
                    CustomerOC[currentIndex].CurrentIndex = tmp;
                    CustomerOC.Move(downIndex, currentIndex);
                    //SelectedItem.CurrentIndex += 1; ////CurrentIndex is the new column/property
                }
            }
    I noticed with my last edit, which I no longer need to run: SelectedItem. CurrentIndex += 1; SelectedItem. CurrentIndex-= 1;

    Seems to work properly and deleting rows, that moving rows.


    • Edited by piedatt80 Sunday, May 17, 2015 10:23 AM little mod
    • Marked as answer by piedatt80 Monday, May 18, 2015 1:56 PM
    Sunday, May 17, 2015 10:22 AM

All replies

  • You're going to need an extra index of some sort.

    I suggest you add a SortIndex field.

            private int _SortIndex;
            public int SortIndex        {
                get
                {
                    return _SortIndex;
                }
    
                set
                {
                    Set(() => SortIndex, ref _SortIndex, value);
                }
            }

    When you want to move a row up, switch it's index with the previous one's.

    When you want to move a row down, switch it's index with the next one.

    Then you can sort a collectionview on that property or just use some linq to give you the same sort order next time you load your data.

    You could alternatively leverage the collectionview indexof method in a similar way to this:

    http://social.technet.microsoft.com/wiki/contents/articles/26673.aspx#Row_Number

    Obtain the index of the item in the collectionview and set the property..

    You could perhaps do that for all entities when the user has finished and confirms they wish to commit their changes. 


    Hope that helps.

    Technet articles: Uneventful MVVM; All my Technet Articles


    • Edited by Andy ONeill Saturday, May 16, 2015 5:45 PM Added indexof alternative
    Saturday, May 16, 2015 3:44 PM
  • To be able to save the order of the items you must store the order in the database. How and where else are you going to save it and be able to restore it? The ObservableCollection is only stored in memory and will be gone when you close the application.

    So you should add a new column to your Customers table, update your entity model and then set the value of this new property in your UpCommand and DownCommand. Something like this:

            private void Up()
            {
                var currentIndex = CustomerOC.IndexOf(SelectedItem);
    
                if (currentIndex > 0)
                {
                    int upIndex = currentIndex - 1;
                    //move the items
                    CustomerOC.Move(upIndex, currentIndex);
                    SelectedItem.CurrentIndex -= 1; //CurrentIndex is the new column/property
                }
            }
            private void Down()
            {
                var currentIndex = CustomerOC.IndexOf(SelectedItem);
    
                if (currentIndex + 1 < CustomerOC.Count)
                {
                    int downIndex = currentIndex + 1;
                    //move the items
                    CustomerOC.Move(downIndex, currentIndex);
                    SelectedItem.CurrentIndex += 1; ////CurrentIndex is the new column/property
                }
            }
    

    The value of the new property will then be saved when you call the SaveChanged method as usual.

    Then you can just sort by the new property when you load the items from the context:

            private void Search()
            {
                using (var ctx = new MyContext())
                {
                    ctx.Configuration.LazyLoadingEnabled = false;
                    ctx.Configuration.ProxyCreationEnabled = false;
    
                    entityCustomer = ctx.Customers.OrderBy(c => c.CurrentIndex).ToList();
    
                    //datagrid
                    CustomerOC = new ObservableCollection<Customer>(entityCustomer);
                }
    }

    Hope that helps.

    Please remember to close your threads by marking helpful posts as answer and then start a new thread if you have a new question. Please don't ask several questions in the same thread.

    Saturday, May 16, 2015 4:11 PM
  • Thank you both for your help. I modified the MyContext:

    public partial class Customer
        {
            public int ID { get; set; }
            public int CurrentIndex { get; set; } //new property
            public string LastName { get; set; }
            public string FirstName { get; set; }
        }
        public class MyContext : DbContext
        {
            public MyContext()
                : base("name=MyContext")
            {
                Database.SetInitializer<MyContext>(new CreateDatabaseIfNotExists<MyContext>());
            }
            public DbSet<Customer> Customers { get; set; }
        }
    However I noticed this:
    1) when I run the insertion of a new row, CurrentIndex must always be 0 or CurrentIndex + 1?

    2) for example, if the first line CurrentIndex = 1 and the next line CurrentIndex = 2 if I run UP I get both rows with CurrentIndex = 1.

    One more question. Is this correct my new Customer class or I have to use a composite key for CurrentIndex?


    Saturday, May 16, 2015 5:26 PM
  • To be able to save the order of the items you must store the order in the database. How and where else are you going to save it and be able to restore it? The ObservableCollection is only stored in memory and will be gone when you close the application.

    So you should add a new column to your Customers table, update your entity model and then set the value of this new property in your UpCommand and DownCommand. Something like this:

            private void Up()
            {
                var currentIndex = CustomerOC.IndexOf(SelectedItem);
    
                if (currentIndex > 0)
                {
                    int upIndex = currentIndex - 1;
                    //move the items
                    CustomerOC.Move(upIndex, currentIndex);
                    SelectedItem.CurrentIndex -= 1; //CurrentIndex is the new column/property
                }
            }
            private void Down()
            {
                var currentIndex = CustomerOC.IndexOf(SelectedItem);
    
                if (currentIndex + 1 < CustomerOC.Count)
                {
                    int downIndex = currentIndex + 1;
                    //move the items
                    CustomerOC.Move(downIndex, currentIndex);
                    SelectedItem.CurrentIndex += 1; ////CurrentIndex is the new column/property
                }
            }

    The value of the new property will then be saved when you call the SaveChanged method as usual.

    Then you can just sort by the new property when you load the items from the context:

            private void Search()
            {
                using (var ctx = new MyContext())
                {
                    ctx.Configuration.LazyLoadingEnabled = false;
                    ctx.Configuration.ProxyCreationEnabled = false;
    
                    entityCustomer = ctx.Customers.OrderBy(c => c.CurrentIndex).ToList();
    
                    //datagrid
                    CustomerOC = new ObservableCollection<Customer>(entityCustomer);
                }
    }

    Hope that helps.

    Please remember to close your threads by marking helpful posts as answer and then start a new thread if you have a new question. Please don't ask several questions in the same thread.

    This is the same answer I gave, but with a faulted implementation.

    The index will collide.


    Hope that helps.

    Technet articles: Uneventful MVVM; All my Technet Articles

    Saturday, May 16, 2015 5:39 PM
  • >>when I run the insertion of a new row, CurrentIndex must always be 0 or CurrentIndex + 1?

    You need to set the value of the CurrentIndex property of the new entity object yourself. There is indeed nothing in your current data model that stops you from having several customers with the same index but you should probably set the CurrentIndex of a new item to MAX(CurrentIndex) = 1 if you want it to end up at the tail (last) of the list:

    int max = CustomerOC.Max(c => c.CurrentIndex);
    newEntity.CurrentIndex = max + 1;


    >>One more question. Is this correct my new Customer class or I have to use a composite key for CurrentIndex?

    It depends on your data model. If there is nothing in your data model that stops you from having several customers with the same index there is no need to use a composite key in the database. If on the other hand two customer cannot have the same index then you should make the index column part of the identity. How to do this is an Entity Framework/SQL Server question though so please don't ask such questions here in the C# forum.

    Please also remember to close your threads by marking helpful posts as answer.

    And please don't ask several questions in the same thread.

    Saturday, May 16, 2015 5:51 PM
  • Thank you both for your help. I modified the MyContext:

    public partial class Customer
        {
            public int ID { get; set; }
            public int CurrentIndex { get; set; } //new property
            public string LastName { get; set; }
            public string FirstName { get; set; }
        }
        public class MyContext : DbContext
        {
            public MyContext()
                : base("name=MyContext")
            {
                Database.SetInitializer<MyContext>(new CreateDatabaseIfNotExists<MyContext>());
            }
            public DbSet<Customer> Customers { get; set; }
        }
    However I noticed this:
    1) when I run the insertion of a new row, CurrentIndex must always be 0 or CurrentIndex + 1?

    2) for example, if the first line CurrentIndex = 1 and the next line CurrentIndex = 2 if I run UP I get both rows with CurrentIndex = 1.

    One more question. Is this correct my new Customer class or I have to use a composite key for CurrentIndex?


    1)

    I pointed the problem of collision out in Magnus reply.

    In my original version I suggested swapping indexes for that reason.

    You can't possibly have overlooked the fact he copied my technique?

    Just with a faulted version.

    2)

    Using getindex is the simplest approach because you only need to worry about what allocating an actual index when you insert the value.

    Otherwise, you need to decide whether an insert comes last or first.

    3)

    You don't need a composite key, it doesn't have to be a key at all. A regular field will do, unless there are huge numbers of these and then putting thousands of rows in an itemscontrol is a bad idea.


    Hope that helps.

    Technet articles: Uneventful MVVM; All my Technet Articles

    Saturday, May 16, 2015 6:35 PM
  • I read with great interest the evolution of this wonderful thread.

    However, I believe that Andy ONeill is right about the collision of the index. In fact, it would be useful to proceed with an swapping that indexes avoiding the different row have the same index value.

    In other words if I have to move up one item I must run "SelectedItem. CurrentIndex += 1", but at the same time, I have to avoid that at the end of the operation there are two items with the same CurrentIndex, then I must also make sure to subtract 1 to the Currentindex of the Item moved.

    And here the question: in a practical way, how to perform correctly this swapping?


    • Edited by piedatt80 Sunday, May 17, 2015 7:22 AM error
    Saturday, May 16, 2015 9:05 PM
  • Continuing with what I wrote above, I think I've found a viable solution. It would be the following:

    private void Up()
            {
                var currentIndex = CustomerOC.IndexOf(SelectedItem);
    
                if (currentIndex > 0)
                {
                    int upIndex = currentIndex - 1;
                    
                    //move the items
                    CustomerOC[currentIndex - 1].CurrentIndex += 1;
                    CustomerOC.Move(upIndex, currentIndex);
                    SelectedItem.CurrentIndex -= 1; //CurrentIndex is the new column/property
                }
            }
            private void Down()
            {
                var currentIndex = CustomerOC.IndexOf(SelectedItem);
    
                if (currentIndex + 1 < CustomerOC.Count)
                {
                    int downIndex = currentIndex + 1;
    
                    //move the items
                    CustomerOC[currentIndex + 1].CurrentIndex -= 1;
                    CustomerOC.Move(downIndex, currentIndex);
                    SelectedItem.CurrentIndex += 1; ////CurrentIndex is the new column/property
                }
            }

    Surely I can make the code more elegant, but esso seems to work correctly.

    I lookforward to youropinion.

    Sunday, May 17, 2015 8:23 AM
  • Evolution... yep... sorry.

    .

    Looks ok to me.

    You're swapping the indexes.

    I assume it works?

    .

    I kind of like the collectionview.indexof(object) approach.

    More code, but I think rather more elegant and offers some advantages.

    Which may be of no use to you, but perhaps worth mentioning.

    If you stop the user from sorting then you don't really need the index to be the "right" value until it's written back to the database. In fact the index need not play any part.  New records are no problem and a more dynamic scenario easily supported.

    Your current approach is great for small numbers of records.  The user just clicks the key a couple of times. 

    If you ended up with more records they might want drag and drop or something.  If you only care about the index at the end of all the moving stuff about then that'd be relatively easy with .indexof, harder with an approach which changes each index.

    I'm not saying you should definitely change, just a thought.


    Hope that helps.

    Technet articles: Uneventful MVVM; All my Technet Articles

    Sunday, May 17, 2015 8:33 AM
  • The code works properly .... until I delete a row. Always keeping in mind my context (CurrentIndex is a simple int):

    public partial class Customer
        {
            public int ID { get; set; }
            public int CurrentIndex { get; set; }
            public string LastName { get; set; }
            public string FirstName { get; set; }
        }
    But if you proceed with deleting a row and later move another I find myself again in the situation where I have two identical CurrentIndex.
    What should I improve?

    Sunday, May 17, 2015 9:42 AM
  • Ah...

    Deleting rows is a bit of a problem since you would potentially need to change a load of indexes if they need to be current.

    I'm not sure how it causes collisions because I would have though the issue was a gap.

    I guess there aren't a huge number or rows?

    You could turn off row virtualization on the datagrid.

    <DataGrid EnableRowVirtualization="False"

    And just use the row index to set your index property on all your data before you commit the changes.



    Hope that helps.

    Technet articles: Uneventful MVVM; All my Technet Articles

    Sunday, May 17, 2015 10:04 AM
  • While you write, I tried this:

    private void Up()
            {
                var currentIndex = CustomerOC.IndexOf(SelectedItem);
    
                if (currentIndex > 0)
                {
                    int upIndex = currentIndex - 1;
                    
                    int tmp = CustomerOC[currentIndex - 1].CurrentIndex;
                    CustomerOC[currentIndex - 1].CurrentIndex = CustomerOC[currentIndex].CurrentIndex;
                    CustomerOC[currentIndex].CurrentIndex = tmp;
                    CustomerOC.Move(upIndex, currentIndex);
                    //SelectedItem.CurrentIndex -= 1; //CurrentIndex is the new column/property
                }
            }
            private void Down()
            {
                var currentIndex = CustomerOC.IndexOf(SelectedItem);
    
                if (currentIndex + 1 < CustomerOC.Count)
                {
                    int downIndex = currentIndex + 1;
    
                    int tmp = CustomerOC[currentIndex + 1].CurrentIndex;
                    CustomerOC[currentIndex + 1].CurrentIndex = CustomerOC[currentIndex].CurrentIndex;
                    CustomerOC[currentIndex].CurrentIndex = tmp;
                    CustomerOC.Move(downIndex, currentIndex);
                    //SelectedItem.CurrentIndex += 1; ////CurrentIndex is the new column/property
                }
            }
    I noticed with my last edit, which I no longer need to run: SelectedItem. CurrentIndex += 1; SelectedItem. CurrentIndex-= 1;

    Seems to work properly and deleting rows, that moving rows.


    • Edited by piedatt80 Sunday, May 17, 2015 10:23 AM little mod
    • Marked as answer by piedatt80 Monday, May 18, 2015 1:56 PM
    Sunday, May 17, 2015 10:22 AM
  • Working is always top of my list of priorities :^)

    Hope that helps.

    Technet articles: Uneventful MVVM; All my Technet Articles

    Sunday, May 17, 2015 2:18 PM
  • Thank you all. I close this useful, at least for me, thread.
    Monday, May 18, 2015 1:56 PM