如果你做多插入电话或通过XML？ [英] Should you make multiple insert calls or pass XML?

问题描述

我有一个帐户的创建过程，基本上当用户注册，我不得不在复式表即用户，档案，地址条目。将有1项在用户表中，个人资料1项，并在地址表中2-3项。这样，最多将有5项。我的问题是我要传递给我的存储过程这是一个XML和解析它在那里或者我应该在我的C＃code创建一个交易对象，保持连接打开并插入地址逐个循环？

I have an account creation process and basically when the user signs up, I have to make entries in mutliple tables namely User, Profile, Addresses. There will be 1 entry in User table, 1 entry in Profile and 2-3 entries in Address table. So, at most there will be 5 entries. My question is should I pass a XML of this to my stored procedure and parse it in there or should I create a transaction object in my C# code, keep the connection open and insert addresses one by one in loop?

你怎么处理这个场景？制罐多个呼叫降低性能，即使连接是打开的？

How do you approach this scenario? Can making multiple calls degrade the performance even though the connection is open?

推荐答案

是一个复杂的方法。在.NET中创建XML和XML三种不同势表中提取的记录是在SQL Server复杂。

If you want to insert records in multiple table then using XML parameter is a complex method. Creating Xml in .net and extracting records from xml for three diffrent tables is complex in sql server.

在一个事务中执行查询是简单的方法，但有些性能会降低那里的.NET code和SQL Server切换。

Executing queries within a transaction is easy approach but some performance will degrade there to switch between .net code and sql server.

最好的办法是使用表参数StoredProcedure的。在.NET code创建三个数据表，并通过他们的存储过程。

Best approach is to use table parameter in storedprocedure. Create three data table in .net code and pass them in stored procedure.

- 创建类型TargetUDT1，TargetUDT2和TargetUDT3每种类型的表中的所有字段，需要插入

--Create Type TargetUDT1,TargetUDT2 and TargetUDT3 for each type of table with all fields which needs to insert

CREATE TYPE [TargetUDT1] AS TABLE
             (
             [FirstName] [varchar](100)NOT NULL,
             [LastName] [varchar](100)NOT NULL,
             [Email] [varchar](200) NOT NULL
             )

- 现在下面的方式写下SP

--Now write down the sp in following manner.

 CREATE PROCEDURE AddToTarget(
     @TargetUDT1 TargetUDT1 READONLY,
     @TargetUDT2 TargetUDT2 READONLY,
     @TargetUDT3 TargetUDT3 READONLY)
     AS
 BEGIN
       INSERT INTO [Target1]
       SELECT * FROM @TargetUDT1

       INSERT INTO [Target2]
       SELECT * FROM @TargetUDT2

       INSERT INTO [Target3]
       SELECT * FROM @TargetUDT3
 END

在.NET中，创建三个数据表和填充值，并调用属正常。

In .Net, Create three data table and fill the value, and call the sp normally.

这篇关于如果你做多插入电话或通过XML？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！