通过存储过程插入多个sql行
本文关键字:sql 插入 存储过程 | 更新日期: 2023-09-27 18:18:51
我已经看了一些相关的话题,但我的问题还没有完全回答:
- c# -使用存储过程插入多行
- 在SQL Server上插入更新存储进程
- 高效的多SQL插入
在我的web应用程序后面的代码中运行我的存储过程时,我有以下类型的设置。问题是,我现在面临着插入多个产品的可能性,我希望在一个ExecuteNonQuery
中完成所有操作,而不是在一个foreach loop
中运行n
多次。
我不知道如何做到这一点,或者如果它可以,与我目前的设置。
代码应该是自我解释的,但如果需要澄清,请让我知道。谢谢。
SqlDatabase database = new SqlDatabase(transMangr.ConnectionString);
DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "proc_name", useStoredProc);
database.AddInParameter(commandWrapper, "@ProductID", DbType.Int32, entity._productID);
database.AddInParameter(commandWrapper, "@ProductDesc", DbType.String, entity._desc);
...more parameters...
Utility.ExecuteNonQuery(transMangr, commandWrapper);
Proc
ALTER PROCEDURE [dbo].[Products_Insert]
-- Add the parameters for the stored procedure here
@ProductID int,
@Link varchar(max)
@ProductDesc varchar(max)
@Date DateTime
AS BEGIN
SET NOCOUNT ON;
INSERT INTO [dbo].[Prodcuts]
(
[CategoryID],
[Link],
[Desc],
[Date]
)
VALUES
(
@ProductID,
@Link,
@ProductDesc,
@Date
)
结束
在循环中运行存储过程应该没问题。只要确保你很少提交,而不是每次插入之后。
对于替代方案,您已经找到了关于加载数据的讨论。
就我个人而言,我喜欢insert into myTable (select *, literalValue from someOtherTable);
形式的SQL批量插入但在你的情况下,这可能行不通。
您可以将所有数据作为表值参数传递- MSDN对此有很好的描述
下面这些行应该可以工作
CREATE TABLE dbo.tSegments
(
SegmentID BIGINT NOT NULL CONSTRAINT pkSegment PRIMARY KEY CLUSTERED,
SegCount BIGINT NOT NULL
);
CREATE TYPE dbo.SegmentTableType AS TABLE
(
SegmentID BIGINT NOT NULL
);
CREATE PROCEDURE dbo.sp_addSegments
@Segments dbo.SegmentTableType READONLY
AS
BEGIN
MERGE INTO dbo.tSegments AS tSeg
USING @Segments AS S
ON tSeg.SegmentID = S.SegmentID
WHEN MATCHED THEN UPDATE SET T.SegCount = T.SegCount + 1
WHEN NOT MATCHED THEN INSERT VALUES(tSeg.SegmentID, 1);
结束为循环外的命令定义commandWrapper和参数,然后在循环中您只需分配参数值并执行该过程。
SqlDatabase database = new SqlDatabase(transMangr.ConnectionString);
DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "proc_name", useStoredProc);
database.AddInParameter(commandWrapper, "@ProductID", DbType.Int32 );
database.AddInParameter(commandWrapper, "@ProductDesc", DbType.String);
...more parameters...
foreach (var entity in entitties)
{
database.SetParameterValue(commandWrapper, "@ProductID",entity._productID);
database.SetParameterValue(commandWrapper, "@ProductDesc",entity._desc);
//..more parameters...
Utility.ExecuteNonQuery(transMangr, commandWrapper);
}
从纯粹的方式来看并不理想,但有时会受到框架和库的限制,并且您被迫以某种方式调用存储过程,以某种方式绑定参数,并且连接由池作为框架的一部分进行管理。
在这种情况下,我们发现一种有效的方法是简单地编写带有许多参数的存储过程,通常是一个名称后跟一个数字,例如@ProductId1, @ProductDesc1, @ProductId2, @ProductDesc2,直到您决定的数字,可能是32。
您可以使用某种形式的脚本语言来生成这些行。
您可以让存储过程首先将所有值插入到允许为空的表参数中,然后以类似于Johnv2020的答案的方式对这些数据进行批量插入/合并。您可以先删除空行。
它通常比一次做一个更有效(部分原因是数据库操作本身,部分原因是您的框架在获取连接以调用过程等方面的开销)