使用ADO.Net传递多行以插入表的最佳方法是什么？ [英] What is the best way to pass multiple rows to insert into a table using ADO.Net?

问题描述

您好，



业务场景是我们公开了一个由Biztalk服务器调用的Web服务，该服务将XML字符串作为输入参数传递。



我们将XML字符串反序列化为类对象。主对象将具有单独的属性，包括列表对象。



例如，让我们假设类结构如下。

Hello,

The business scenario is that we exposed a web service that is being called by the biztalk server that passes the XML string as an input parameter.

We deserialize the XML string into class object. The main object will have individual properties including the list object.

For Example, Lets assume that class structure is as below.

Class ClassInfo
{
    public string ClassNumber{get;set;}
    
    public string ClassName{get;set}
    
    public List<student> StudentList{get;set;}
    
    public List<professor> ProfessorsList{get;set;}

} 

让我们假设我需要将ClassNumber和ClassName插入一个表，一种新的外观值，以及StudentList和ProfessorsList到两个不同的表..



如果我要从ADO .NET向表中插入多行（StudentList和ProfessorsList列出多个表），我会看到以下场景，我想知道是否有比这更好的方法。 ...另外，我想将所有参数作为输入参数传递给为我插入的存储过程。



1.在内部调用SP每个学生和教授的每个循环。

2.使用表类型参数将集合传递给SP。

Lets assume that I would need to insert ClassNumber and ClassName to one table, kind of a new look value, and StudentList and ProfessorsList to two different tables..

If I were to insert multiple rows into a table( StudentList and ProfessorsList list multiple tables) from ADO .NET, I see the following scenarios, I would like to know if there is any better approach than this.... Also, I would like to pass all the parameters as an input parameters to the stored procedure that does the insertion for me.

1. Calling SP inside for each loop for each student and professor.
2. passing collection to SP using table type parameter.

推荐答案

使用 SqlBulkCopy （示例 [ ^ ]）是一个批量插入的方法。

另一种选择可能是使用xml文件 - 使用xml文件批量插入sql数据库 [ ^ ]。

Using SqlBulkCopy (example[^]) is one way of doing a bulk insert.
Another option could be using an xml file - Bulk insert into sql database using xml file[^].

这篇关于使用ADO.Net传递多行以插入表的最佳方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！