none
Change/Move row position in sql compact edition

    Question

  • Hello,

    I have a table with the column  description. Lets imagine that I have the records:

    "a"
    "b"
    "c"

    If this was a List and I wanted to move the "c" to the first position, I would simply remove the "c" and insert it in the 0 index position.

    How can I do this with sql ce?

    I could create another column named position and every time I would want to change the position, I would increment every other positions below. Example, "c" goes to first position:

    position - description
    1 - "a"
    2 - "b"
    3 - "c"

    ->

    1+1 - "a"
    2+1 - "b"
    1- "c"

    ->
    1- "c"
    2 - "a"
    3- "b"

    But if I have lets say 100 thousand rows and I want to move the last one to the first position I have to do the operation 100K times.

    Is there a more effective way to do what I want?

    Thank you,
    ARtur

    Friday, May 02, 2008 2:59 PM

Answers

  • Yes Artur, you guessed right. -ve for negative and +ve for positive.

     

    Well there is no SQL CE limitation that should bother you.

     

    Basically you have to come with a good design which can solve your purpose. Let's try to figure out here.

     

    In databases you don't usualy store rows in order. The data stored is in no order at all, its when you query the data out you put order by on it and sort it.

     

    So going by above logic, a simple select * statement from database without order by will not gurantee that rows that are fetched are in order.

     

    Coming back to your list, what you want is that, you can drag items up or down as you wish and whatever changes to you make to data should be reflected in database, and maybe when later you open application again the list is in same order.

     

    What I would suggest you is to use a primary key column in your table, with which you can always uniquely identify the row in database. After you have the primary key, use that as a reference in list, i.e. in your application you can make a list which maintains the order of primary keys.

     

    Later when you display that data list, from your primary key list you get the order in which to display and from the primary key you can retrieve the data that you wish to display.

     

    In simple words:

    On database side:

    A table with value column and a primary column to uniquely identify rows

    On Application side:

    A list that maintains the order of primary that have to be displayed. Upon exiting the application you can save this list in your database or some flat file if you want.

     

    I do not want you to suggest updating all rows with this order because first of all it is heavy operation as you will touch many rows just to change one, second even if you change there is no gurantee things will come out in right fashion until you sort, finally you can achieve this in simpler ways like the one I explained above.

     

    If you could not understand any point, let me know, I will elaborate on that.

    Monday, May 05, 2008 1:23 PM

All replies

  • Hi ARtur

     

    Below is some example code on how to perform the requested task. There is many other ways of achieving this as well, but using ResultSet and RecordSet appears to be the fastest and easiest way of accessing information held within a SQL Server Compact 3.X database.

     

    Try out the code, mess around with it and tell me how you got on

     

    Kind Regards

     

    John Galvin

     

    Imports System.Data.SqlServerCe

    Module Module1

    Private Sub MoveRows()

    '1: Create a String to hold the connection command

    Dim strMyConnection As String = My.Settings.BackupConnectionString

    '2: Create a new SQLCe Connection

    Dim SQLConnection As New SqlCeConnection(strMyConnection)

    '3: Create a New SQLCe Command and set the SQLCE Connection

    Dim SQLCeCommand1 As SqlCeCommand = SQLConnection.CreateCommand()

    '4: Set the SQLCECommand to point at the table where the information is stored

    SQLCeCommand1.CommandText = "DatabaseTableName"

    '5: Set Command Type to TableDirect (Insert, Update etc SQLCE Commands are auto created

    SQLCeCommand1.CommandType = CommandType.TableDirect

    '6: Create a ResultSet so you can access the database direct without having to use Datasets and TableAdaptors 1000X Faster

    Dim MyResultSet As SqlCeResultSet = SQLCeCommand1.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)

    '7: Create a RecordSet to store the information you want to change/update in ram once again 1000X faster

    Dim MyRecordSet As SqlCeUpdatableRecord = MyResultSet.CreateRecord()

    Try

    '8: Open the connection

    SQLConnection.Open()

    '9: One Way Of Doing it ONLY EXAMPLES

    MyResultSet.ReadAbsolute(1)

    Dim TestString As String = MyResultSet.GetString(1)

    ' or

    MyRecordSet.GetString(1)

    If TestString = "A" Then ' Just examples

    'MyRecordSet.SetBoolean(1, CBool(True))

    MyRecordSet.SetString(2, CStr("B"))

    MyResultSet.Update()

    End If

    '10: Second Way

    MyResultSet.SetValue(1, CStr("A"))

    '11: Third Way

    While MyResultSet.Read

    MyRecordSet.SetBoolean(1, CBool(True))

    MyRecordSet.SetString(2, CStr("B"))

    End While

    '12 Finally Always Close and Dispose of the objects as they use up ram

    SQLConnection.Close()

    MyResultSet.Close()

    SQLConnection.Dispose()

    MyResultSet.Dispose()

    SQLCeCommand1.Dispose()

    Catch ex As SqlCeException

    End Try

     

    End Sub

    End Module

     

    Please Mark As Answer If This Thread Is HelpFull Thank You!

    Saturday, May 03, 2008 9:03 AM
  • Arthur, you did not give any column description.

    I kind of understood your problem, but if you can elaborate some more it will help designing the solution more effectively.

     

    Basically your problem should surface with any database from what it seems. You want to bring a row to top and shift all down in one step.

     

    Consider this table Table1(col1 bigint, col2 nvarchar(50))

    Basically col2 holds your value and col1 holds position

     

    What you did in your example is, you changed col1 for c to 1 and added 1 to all others.

     

    Now as we have set col1 as bigint, it will fairly support large large values, and there is no stopping you from using -ve values

     

    so basically mark col1 value as "0" for C, and next value as "-1" and so on. You will not have to shift any other rows, just change the col1 value as one less than minimum. Later on sort on Col1 to get results you desire.

     

    Basically the solution will change depending on your requirement:

     

    - if you want the most recent updated/inserted column, you can use rowversion for marking

    - if you want the largest id to shift to front, a combination of max and min can help

    - a primary key and identity can help you find that particular row that you wish

     

    End of day, is it really necessary to shift all records? Or you are just looking for that one record?

     

    I will need some more input from you, but using -ve indexes can help, i.e. use a bigint column as index for each row, once you reach the -ve limit, at that time, its your application logic, you maye drop that column and create new one or anything.

    Monday, May 05, 2008 9:38 AM
  • Thanks for the answers John and Ravi.

    John, your solution seems to work only for the example I gave, am I right?
    Ravi, you put me thinking what is "-ve". Its an abbreviaton for "negative" right?

    My example was weak. I'll try to explain better this time:

    I have a list that will be associated to a listbox. I want to be able to drag and drop rows in the listbox. This will cause the rows to be reordered. I want to be able to drag any row to any position in the list. The row in the list are the rows in a table.

    If this was a List object I would simply remove the row that I wanted to move and insert it at the desired position. How can I do this with sqlce?

    This problem is not specific to slqce, but the answer might be because of the sqlce limitations.

    Best Regards,
    Artur


    Monday, May 05, 2008 11:18 AM
  • Yes Artur, you guessed right. -ve for negative and +ve for positive.

     

    Well there is no SQL CE limitation that should bother you.

     

    Basically you have to come with a good design which can solve your purpose. Let's try to figure out here.

     

    In databases you don't usualy store rows in order. The data stored is in no order at all, its when you query the data out you put order by on it and sort it.

     

    So going by above logic, a simple select * statement from database without order by will not gurantee that rows that are fetched are in order.

     

    Coming back to your list, what you want is that, you can drag items up or down as you wish and whatever changes to you make to data should be reflected in database, and maybe when later you open application again the list is in same order.

     

    What I would suggest you is to use a primary key column in your table, with which you can always uniquely identify the row in database. After you have the primary key, use that as a reference in list, i.e. in your application you can make a list which maintains the order of primary keys.

     

    Later when you display that data list, from your primary key list you get the order in which to display and from the primary key you can retrieve the data that you wish to display.

     

    In simple words:

    On database side:

    A table with value column and a primary column to uniquely identify rows

    On Application side:

    A list that maintains the order of primary that have to be displayed. Upon exiting the application you can save this list in your database or some flat file if you want.

     

    I do not want you to suggest updating all rows with this order because first of all it is heavy operation as you will touch many rows just to change one, second even if you change there is no gurantee things will come out in right fashion until you sort, finally you can achieve this in simpler ways like the one I explained above.

     

    If you could not understand any point, let me know, I will elaborate on that.

    Monday, May 05, 2008 1:23 PM
  • Thanks Ravi, that a good solution.

    My only worry is that because the position info is not in the DB, the sort of the rows may be slow. I want it to work fast with 100K rows. It may seem too much for a listbox, but you can have Picasa with thousands of photos and it still scrolls well.

    Best regards,
    Artur







    Monday, May 05, 2008 1:49 PM
  • I am glad that I could be any help Artur.

     

    As I said earlier, you can save the position info in a separate table while exiting, which can have the foriegn key relationship with your original table. Your list should be smaller than the whole table I assume, so when querying you can use your second table for position info and first table for the data.

     

    I am sorry I have not worked with Picasa much, but from my observation, I don't think they maintain the position info of thumbnails? They actually might be sorting them according to name, date or hits maybe.

     

    Moreover, I feel that Picasa must be multi-threaded application where multiple threads try to load the image thumbnails at same time to make it faster.

     

    In JPEG images or any other images your have to use decoding algorithms to display image. If the display size is small like 1/10 th like in picasa or other pic browsers the decoding time/loading time is very small in comparison to full sized image. It is only when you open the full image the whole image is decoded and displayed.

     

    You can use a similar logic in your application to make things faster, i.e. making it multi-threaded so that these threads read various parts of table and display simultaneously, secondly display only small data initially so that you don't have to read everything from database file.

     

    But still the design is very different, for picasa all those images are different files, so working parallel is easier, but in terms of SQL CE there is only one database file, though multiple connections are allowed.

     

    The appliation will require some nice designing for performance improvements like parallel load of data, some faster decoding algorithm to display/decode data.

    Tuesday, May 06, 2008 7:04 AM
  • Picasa was just to show that a gigantic listbox is not a farfetched example. I will give it a go to see if I can get a smooth result.

    Best Regards,
    Artur
    Tuesday, May 06, 2008 8:45 AM
  •  

    Hi Artur,

     

        Databases are generally for set based access and not list based access. You can, as Ravi said, make a list implementation using SQL CE database though. If performance concerns you, please use an index (based on the position), though, inserts and deletes will have 1 row update + 2 index movements (one delete and one insert), you should get faster respose time while loading the list.

     

        If you don't want even the incremental position changes (for list items) to consume more time, please use some cacheing mechanism, like, at the close of the app, replace (or copy) the list items in order into the database, and exit etc... I think index should not be that bad, though.

     

    Thanks

    Udaya

    Tuesday, May 06, 2008 6:30 PM