How to bend ADO.Net to my liking...

[cartoper]

Data:

A line (row) containing multiple fields (columns)

Requirement:

1: Allow new lines to be inserted at any point
2: Allow deletion of any line
3: Keep the lines in the correct order

Examples:

When given a table with three lines:

Line 1
Line 2
Line 3

1: An insert after Line 2 makes Line 3 become Line 4 and the new Line
will be Line 3
2: A delete of Line 2 will make Line 3 become Line 2

Outside of ADO.Net, the lines would simply be a link list to make
insertion and deletion quick and easy. The objective is to use a
DataSet so that it can simply be bound to a DataGridView for display.

The best solution I have been able to come up with is to have an
ordinal number field that is hidden that initially increments by some
large amount, say 1000:

Line 1 (1000)
Line 2 (2000)
Line 3 (3000)

After inserting a line after Line 2 would result in this:

Line 1 (1000)
Line 2 (2000)
Line 3 (2500)
Line 4 (3000)

I imagine that this is an age old problem in relational databases and
there is a better solution. Does anyone know of a better way?

 

[Marina Levit [MVP]]

In a relational database you would need to have a sequence column that you
can sort on, so you get the rows in the correct order. That, or you need
another column you can sort on - but you always need the sort. Otherwise,
the rows will be returned in whatever order they happened to be stored in,
which depends on the indexes you have set up, however that database engine
is implemented and other things.

In a datatable you can add rows whereever you want, delete whatever rows you
want. If you have a sequence column that you keep updated accordingly, you
can maintain row order between now and the next time you retrieve your data.

 

[W.G. Ryan - MVP]

You can create an additional column which you can sort on and just set the
positions there. Or, you can just create the collection you want and bind
to it - you just need to implement the IList interface.

 

[cartoper]

In a relational database you would need to have a sequence column that you
can sort on, so you get the rows in the correct order. That, or you need
another column you can sort on - but you always need the sort. Otherwise,

Ok, I know all the stuff you said about how relational databases
normally work. I would like to explore the concept of a sequence
column a bit more...

Is there any way to implement a sequence column in such a way that it
is easy to insert an infinite number of rows in the middle of the
sequence?

A little bit more about what I am doing exactly: I am trying to take
my data and fit it nicely into the .Net 2.0 DataGridView and simplify
the GUI work as much as possible. The data is going to be coming from
a proprietary engine, not a rational database. I am still learning
about DataAdapters, but I believe I will be implementing my own
DataAdapter for the engine. Initially I thought I could simply have a
sequence column in the DataSet and change the values of all the rows
that needed to be updated.

The problem is that a key requirement for the application is to trace
changes in the data. If a line is added after 2 so a column in 3
changes from 3 to 4, then the DataSet will show that both lines 3 and
lines 4 changed, I only want the DataSet to reflect that a line 3 was
inserted.

Thoughts?

 

[Lloyd Dupont]

The problem is that a key requirement for the application is to trace

changes in the data. If a line is added after 2 so a column in 3
changes from 3 to 4, then the DataSet will show that both lines 3 and
lines 4 changed, I only want the DataSet to reflect that a line 3 was
inserted.

Thoughts?

I don't really get what you mean.

But let's just say that a DataTable hold 2 copies of any line, the original
line and the modified line.
Also each line contains a flag which say wether it's newly inserted /
deleted / changed / unchanged.

Does it helps?

 

[Marina Levit [MVP]]

But if the dataset doesn't reflect the line 3 is now line 4, then it won't
be updated in the database, and you will have two lines that will both think
they are line 3.

 

[Cor Ligthert [MVP]]

Cartoper,

Every dataset (as well strongly typed) holds datatables (what you are actual
working with)
Every datatable has a defaultview that can be set using the sort property to
any sequence you want.

If you want to show them in different order than you can add extra views,
which have the name dataviews.

I hope this helps,

Cor

 

[Guest]

Data:

A line (row) containing multiple fields (columns)

Requirement:

1: Allow new lines to be inserted at any point

You mean a specific point in the tree

1
NEW LINE
3

Can't be done, but it can be facilitated with DataViews and a sort.

2: Allow deletion of any line

You can delete anything you desire in any DataSet.

3: Keep the lines in the correct order

Once again, the underlying data will not be ordered, but you can set up the
view of the UI based on a sorted field, which will facilitate your needs.

It sounds, to me, like you want a linked list implementation. That is not
what an ADO.NET DataSet does. You can mock it by inserting rows with some
sort of ordering column, but this will have to be envisioned in the data
structure and facilitated by creating a column (which probably will not be
shown in the UI). The DataSet, itself, is like a relational database where
ordering does not matter.

Your other option is to move to object and create a linked list
implementation.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Guest]

In order to solve your problem in ADO.NET, you will have to create an
artificial column that orders the data. The numbering scheme you use is your
choice, but realize that small increments, like 1, 2 and 3 will require
reordering everytime there is a change. On the other hand, large increments
will require you have a system for inserts that are aware at what level each
insert is:

1000
Next insert at 100 increments
Then 10 increments
Then 1 increments

You can use floating point numbers, if that makes it easier.

Your data adapter then has to respect that number to put items back into
your data structure in the correct order. This too, can be processor
intensive.

A linked list might make a bit of the business logic easier, but will still
have the same issue when storing a list that has been whacked pretty hard
(reordered with inserts and deletes).

Adding an artificial column that orders should be fairly easy. You will have
to decide what mechanism you are going to use (reorder with each change or
inserts of small increments). And, you will have to figure out how to store
the data efficiently, as reordered lists, esp. large ones, can kill your
system.

I would try to stay in the byte world as much as possible with the transfer
back to persisted storage. It will be MUCH faster. On the other hand, it is
also the area most prone to error, so include unit tests to validate your
data during dev.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[michael.lang]

I would highly recommend a linked list implementation. You can do this
via data and stored procedures. I recommend custom entities in
gereral, but I don't see why that is required.

Table: Line
Columns:
LineID (int) NOT NULL
PreviousLineID (int) NULL
(other columns storing your point detail data)

Then create a stored procedure for inserting a point in the list.

CREATE PROCEDURE InsertLine ( @PreviousLineId Int = null ) AS
Declare @LineID int
Insert Line (PreviousLineID, ..., ... ) VALUES (@PreviousLineID, ...,
....)
Select @LineID = SCOPE_IDENTITY()

IF @PreviousLineID NOT NULL
BEGIN /* prevent attaching all root lines to this new line */
UPDATE Line SET PreviousLineID = @LineID
WHERE PreviousLineID = @PreviousLineID
END

GO /* END of InsertLine */

I typed this procedure on the fly and untested, please excuse if it
isn't perfect syntax. Basically I save the new line attached to the
previous, then I do a separate update to attach any line that was
attached to the previous line to the new line. As long as this
procedure is used to insert all lines, then there should only be one
line attached to any other line. IF you want to allow duplicates
attached to a given line, remove the update statement.

I hope this helps,
Michael Lang
XQuiSoft LLC
http://www.xquisoft.com/