C# listview and database update question

[Jason Huang]

Hi,

In my C# Windows Form MyForm, there's one ListView control listView1 in the
MyForm.
The ListViewItem in the listView1 MATCHING EXACTLY the data in a SQL Server
database table Customer.
e.g.,
Serial ID Name
----------------------------
1 123 John
2 125 Marry
3 201 Leo
4 305 Britney

If we DELETE the first Row [1,123, John] in the listView1, we will reasign
the Serial for the rest Rows.
the result:
Serial ID Name
----------------------------
1 125 Marry
2 201 Leo
3 305 Britney

Then we DELETE everything form the database table Customer.
Later on we INSERT back [1,125,Marry],[2,201,Leo],[3,305,Britney] to
database Customer.

I have a feeling that something Very Wrong with that process, but I don't
know what's the solution.
Would someone give us some advice?
Thanks for help.

Jason

[Jani Järvinen [MVP]]

Re: C# listview and database update question

Hello!
[color=blue]
> If we DELETE the first Row [1,123, John] in the listView1, we will reasign
> the Serial for the rest Rows.
>
> Then we DELETE everything form the database table Customer.
> Later on we INSERT back [1,125,Marry],[2,201,Leo],[3,305,Britney] to
> database Customer.[/color]

Uh oh Jason, if I understood you correctly, then your gut feeling is
correct: you shouldn't be doing things this way. Okay, your solution does
work, but imagine what would happen if you would have something like 1000
customers (or 1,000,000) instead of just three? A performance nightmare to
say the least!

It is often a requirement in applications to have some kind of counter that
runs from 1 to the number of items (customers in this case). However, unless
you have special requirements for example about sorting/item ordering, it is
often enough to only display those numbers on the client, but never store
them on the database.

That is, now your database has the Serial, ID and Name fields. What would
happen if you wouldn't store the Serial in the database at all? If you
choose this route, you could use the ID field (I'm assuming it is unique
among all customer records) as a key to delete data from the database, and
then you would simple recreate all Serials on the client's ListView. This
should be very fast.

So, the Serial number would only be a virtual value that you generate
on-the-fly in the code. With this solution, you would never need to delete
all customers from the database and then recreate them, since the database
wouldn't have the Serial field at all. You could however still sort by Name
and ID, which might be enough for you.

Does this make sense to you? Of course, this is a simplistic solution, but
without knowing more about your specific needs, it if difficult to suggest a
definite solution.

--
Regards,

Mr. Jani Järvinen
C# MVP
Helsinki, Finland
janij@removethi s.dystopia.fi

http://www.saunalahti.fi/janij/