您应该进行多次插入调用还是传递 XML? [英] Should you make multiple insert calls or pass XML?

问题描述

我有一个帐户创建过程，基本上当用户注册时，我必须在多个表中输入条目，即用户、个人资料、地址.用户表中将有 1 个条目，配置文件中将有 1 个条目，地址表中将有 2-3 个条目.因此，最多将有 5 个条目.我的问题是我应该将这个 XML 传递给我的存储过程并在那里解析它，还是应该在我的 C# 代码中创建一个事务对象，保持连接打开并在循环中一个一个地插入地址?

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?

推荐答案

如果要在多个表中插入记录，那么使用 XML 参数是一种复杂的方法.在.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 代码和 sql server 之间切换时，某些性能会降低.

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

最好的方法是在存储过程中使用表参数.在.net代码中创建三个数据表，并在存储过程中传递.

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中，创建三个数据表并填入数值，正常调用sp.

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

这篇关于您应该进行多次插入调用还是传递 XML?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！