如何有效地将对象保存到 SQL 2000 数据库
过去，每当我的应用程序需要将许多记录插入 SQL 2000 数据库时，我都会调用一个存储过程，每条记录调用一次。 当插入许多记录时，我发现性能受到影响，特别是在使用胖客户端调用 Web 服务来执行数据库调用时。
然后我了解到，如果我传递表示许多记录的 XML 数据，SQL 2000 可以将其保存到临时表中，并且我可以使用同时跨临时表的所有行进行操作的单个 SQL 有效地处理 XML 表中的记录。 这将对 SP 的调用次数减少到一次，并且效率更高，因为 SQL 更新命令同时处理多个记录。
我个人喜欢使用业务对象 (BO) 层，并且没有使用 LINQ 或实体框架的经验，因此目前，我们假设这些工具超出了范围...
在我的 BO 层中，我通常会添加一个“ToDataset” " 函数将对象转换为其中包含一个表的数据集，其中该表包含一个数据行。 数据行本身包含对象中数据的表示。
然后，我将有一个集合类，例如，它是前一个对象的集合。 它也将有一个“ToDataset”方法来创建集合类中所有对象的表示。 该函数将循环访问集合中的对象，并为每个对象调用“ToDataset”函数并将这些信息合并到“累积”数据集中。
当我想要将集合类中的所有记录传递给 SP 时，我会调用 Collection 类的 ToDataset 方法并将 ds 的 XML 传递给 SP。
为了缩小这篇文章的篇幅，我将尽量避免进一步证明这种方法的合理性，并愿意接受您的建议。
优秀的开发人员如何做这样的事情？
在下面的代码示例中，您将看到我如何尝试定义对象，以便它们可以嵌套，但是当您为父对象调用 Todataset 函数时，所有子对象都会使用由基类。 （代码不是那么长，也许我应该发布它。如果我受到攻击，我会发布它）
我预计会有一些可怕的反应。 善待。 :-)
感谢您有兴趣阅读本文的最后。
http://sites.google.com/site/dokmanc/ Home/ObjectToDataset.zip?attredirects=0
In the past, whenever my application needed to insert many records into a SQL 2000 db, I would call a stored procedure, once for each record. When inserting many records, I found that performance suffered, particularly when with a fat client, calling a web service to perform the database call.
Then I learned that if I passed XML data representing many records, that SQL 2000 could save that to a temp table and I could efficiently process the records in the XML table using a single SQL that operated across all the rows of the temp table simultaneously. This reduced the number of calls to the SP to one and also was more efficient because the SQL update commands worked on multiple record simultaneously.
I personally like using a Business Objects (BO) layer and am not experienced with LINQ or Entity Framework, so for the moment, let's assume that these tools are out of scope...
In my BO layer, I will typically add a "ToDataset" function to an object to convert the object to a dataset with one table in it, where that table contains one data row. It is the datarow itself that contains the representation of the data in the object.
Then, I will have a collection class, which is a collection of the previous object, for example. It too, will have a "ToDataset" method to create a representation of all of the objects in the collection class. This function will loop through the objects in the collection, and for each, call the objects "ToDataset" function and merge this information into an "accumulating" dataset.
When I want to pass all of the records in the collection class to a SP, I would call the Collection class's ToDataset method and pass the XML for the ds to the SP.
To keep the size of this post down, I'm going to try to refrain from further justifying this approach and open myself up to your suggestions.
How do good developers do stuff like this?
In the following code sample you'll see how I try to define my objects so that they can be nested but that when you call the Todataset function for a parent object, all of the child objects are rolled into it using a pattern defined by the base class. (The code isn't that long, maybe I should post it. If I get flamed, I'll post it)
I expect some horrified reactions. Be kind. :-)
Thanks for your interest in reading this to the end.
http://sites.google.com/site/dokmanc/Home/ObjectToDataset.zip?attredirects=0
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
绝对很棒的策略。 不要改变任何事情。
Absolutely awesome strategy. Don't change a thing.
您是否查看过 SqlBulkCopy 类？
顺便说一句，我刚刚看到这个问题。 然而，我可能仍然犹豫是否要回答这个问题，甚至犹豫是否要阅读其中的大部分内容。 一旦您解释了您发送 XML 是为了有效地加载数据，您就几乎已经说出了您必须说的所有内容，以便得到问题的答案。
Have you looked at the SqlBulkCopy Class?
BTW, I only just saw this question. However, I still might have hesitated to answer it, or even to read very much of it. Once you'd explained you were sending XML in order to load data efficiently, you had pretty much said all you had to say in order to get the question answered.