“合并"列出数据库操作 [英] "Merging" list operations with database

问题描述

这是场景.

页面加载后，用户正在获取其列表，例如电话".在此过程中，他可以修改，删除，添加一些记录，而无需数据库交互.此后，他可以单击保存"按钮来填充对数据库的所有更改.

我的问题是:

在用户表演结束时，我有:

列出< userphones> dbPhones//用户交互之前来自数据库的电话

列出< userphones> userPhones//以任何方式修改的电话列表.

第一种明显的解决方案是清除数据库条目并从userPhones添加所有新项目.那不是我想要的东西.我想出了其他一些方式，例如存储删除的ID或Union，Merge，Linq utils除外，但其效率仍然不够.我想这是一个非常普遍的问题，因此希望能从经验丰​​富的程序员那里获得一些帮助.

在此先感谢.

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.

推荐答案

我的技术是将所有id传递给存储过程.您可以使用XML进行此操作.

使用XML数据类型，可以删除dbPhone中不在userPhone的ID中的ID，更新匹配的记录，并在一个过程调用中插入新的记录.

这是一个SQL Server 2005 XML示例.希望这可以帮助.您可以将其剪切并粘贴到查询窗口中，并且该窗口将起作用(对于SQL 2005+)

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

进一步阅读:在MSDN上 [

Further reading: at MSDN[^]

这篇关于“合并"列出数据库操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！