"Merging" list operations with database

Question

0.00/5 (No votes)

See more:

Here's the scenario.

After page loaded user is getting List of his for example Phones. He can modify, delete, add some records without databse interaction during this process. After this he can click "Save" button to populate all changes to database.

My problem is:

At the end of user's acting i have:

List<userphones> dbPhones //phones from database before user's interaction

List<userphones> userPhones //modified in any way list of phones.

First kinda obvious solution is to clear database entries and add all new items from userPhones. Thats not thing im looking for. I figured out some other ways like storing deleted Id's or Union, Merge, Except Linq utils but its still not efficient enough. I guess its pretty common issue so hoping for some help from more experienced programmers.

Thanks in advance.

Posted 31-Aug-10 4:27am

helianthus87

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gordon Kushner · Answer 1 · 2010-08-31T04:59:00

My technique is to pass in all the ids to the stored procedure. You can do this with XML.

Using the XML data type, you can DELETE the ids in dbPhones that are NOT IN the ids for userPhones, UPDATE the matching records and INSERT the new ones in one procedure call.

Here is a SQL Server 2005 XML Example. Hope this helps. You can cut and paste it into a query window and it will work (for SQL 2005+)

--Assume this is your data table. In real life it would be a SQL server table
DECLARE @DBData TABLE ( DataName varchar(50), DataDesc varchar(50))
INSERT INTO @DBData
VALUES ('One', 'Number One')
INSERT INTO @DBData
VALUES ('Two', 'Number Two')
INSERT INTO @DBData
VALUES ('Three', 'Number Three')
INSERT INTO @DBData
VALUES ('Four', 'Numero Quatro')

SELECT * FROM @DBData

-- This is the data you would pass in.
-- We would expect that One will update, Two, Three and Four
-- will be deleted (since they are not in the data) and
-- Five will be added.

DECLARE @XMLData xml
SELECT @XMLData = '
<root>
	<item>
		<DataName>One</DataName>
		<DataDesc>Number 1 Renamed</DataDesc>
	</item>
	<item>
		<DataName>Five</DataName>
		<DataDesc>Adding Five</DataDesc>
	</item>
</root>'

--Update DataDesc where the items match
UPDATE DBTable
SET DataDesc = CONVERT(varchar(50), XTable.Item.query('./DataDesc/text()'))
FROM @XMLData.nodes('/root/item') AS XTable(Item)
INNER JOIN
	@DBData DBTable ON CONVERT(varchar(50), XTable.Item.query('./DataName/text()')) = DBTable.DataName

SELECT * FROM @DBData

--Insert items that are not in the table yet.
INSERT INTO @DBData(DataName, DataDesc)
SELECT	CONVERT(varchar(50), XTable.Item.query('./DataName/text()')),
		CONVERT(varchar(50), XTable.Item.query('./DataDesc/text()'))
FROM @XMLData.nodes('/root/item') AS XTable(Item)
WHERE CONVERT(varchar(50), XTable.Item.query('./DataName/text()')) NOT IN (SELECT DataName FROM @DBData)

SELECT * FROM @DBData

--Remove items that are not in the XML document/table

DELETE FROM @DBData
WHERE DataName NOT IN (
	SELECT	CONVERT(varchar(50), XTable.Item.query('./DataName/text()'))
	FROM @XMLData.nodes('/root/item') AS XTable(Item))
SELECT * FROM @DBData