从数据层向存储过程多次插入值的最佳方法？

发布于 2024-10-18 03:39:03 字数 391 浏览 7 评论 0原文

你好我有 DAL 层，从那里调用存储过程将值插入表中。

例如：-

CREATE PROCEDURE [dbo].[DataInsert]
    @DataName nvarchar(64)
AS
BEGIN
    INSERT INTO 
         table01 (dataname) 
        VALUES 
    (@dataname)
END

现在随着需求的变化，每个客户请求我必须添加值 5 次。那么最佳实践是什么？

我是否从 DAL 调用此存储过程 5 次？

或者

一次性将所有值（可能以逗号分隔）传递给存储过程，然后让存储过程添加 5 次？

顺便提一句。并不总是5次。它是可变的。

原文

Hi
I have DAL Layer, from where invoking a stored procedure to insert values into the table.

E.g.:-

CREATE PROCEDURE [dbo].[DataInsert]
    @DataName nvarchar(64)
AS
BEGIN
    INSERT INTO 
         table01 (dataname) 
        VALUES 
    (@dataname)
END

Now as requirement changed, per client request i have to add values 5 times. So what is the best practice?

Do i call this Stored Procedure 5 times from my DAL?

Pass all the values (may be comma separated) to storedprocedure in one go and then let the stored procedure add it for 5 times?

BTW. Its not always 5 times. It is changeable.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

<逆流佳人身旁 2024-10-25 03:39:03

您可以创建用户定义的表类型；

CREATE TYPE [dbo].[SomeInfo] AS TABLE(
[Id] [int] NOT NULL, 
[SomeValue] [int] NOT NULL )

如此定义您的存储过程；

CREATE PROCEDURE [dbo].[AddSomeStuff]
    @theStuff [SomeInfo] READONLY
AS
BEGIN
    INSERT INTO SOMETABLE ([...columns...])
    SELECT [...columns...] from @theStuff
END

然后，您需要创建一个与架构匹配的数据表（下面称为表）并按如下方式调用存储过程；

var cmd = new SqlCommand("AddSomeStuff", sqlConn) {CommandType = CommandType.StoredProcedure};

var param = new SqlParameter("@theStuff", SqlDbType.Structured) {Value = table};
cmd.Parameters.Add(param);

cmd.ExecuteNonQuery();

You could create a user-defined table type;

CREATE TYPE [dbo].[SomeInfo] AS TABLE(
[Id] [int] NOT NULL, 
[SomeValue] [int] NOT NULL )

Define your stored proc as such;

CREATE PROCEDURE [dbo].[AddSomeStuff]
    @theStuff [SomeInfo] READONLY
AS
BEGIN
    INSERT INTO SOMETABLE ([...columns...])
    SELECT [...columns...] from @theStuff
END

Then you'll need to create a datatable (called table below) that matches the schema and call the stored proc as so;

var cmd = new SqlCommand("AddSomeStuff", sqlConn) {CommandType = CommandType.StoredProcedure};

var param = new SqlParameter("@theStuff", SqlDbType.Structured) {Value = table};
cmd.Parameters.Add(param);

cmd.ExecuteNonQuery();

回复收藏 0 原文

生活了然无味 2024-10-25 03:39:03

顺便说一句，这个过程有效 - 我刚刚编写并测试了它，请参阅下面的结果！

CREATE PROCEDURE [dbo].[DataInsert]
     @DataName nvarchar(max) AS  
BEGIN  
DECLARE @pos SMALLINT, @str VARCHAR(max)  

WHILE @DataName <> ''  
BEGIN  
    SET @pos = CHARINDEX(',', @DataName)  
    IF @pos>0  
        BEGIN  
            SET @str = LEFT(@DataName, @pos-1) 
            SET @DataName = RIGHT(@DataName, LEN(@DataName)-@pos)  
        END  
    ELSE  
        BEGIN  
            SET @str = @DataName 
            SET @DataName = ''  
        END  

   INSERT INTO table01 VALUES(CONVERT(VARCHAR(100),@str)) 

END  
END  
GO

然后运行它：-

EXEC @return_value = [dbo].[DataInsert]
@DataName = N' Five, Bits, of, your, data'

来自 table01 的行：*

位

的

数据

（受影响的 5 行）

btw this proc works - I've just written and tested it see results below!

CREATE PROCEDURE [dbo].[DataInsert]
     @DataName nvarchar(max) AS  
BEGIN  
DECLARE @pos SMALLINT, @str VARCHAR(max)  

WHILE @DataName <> ''  
BEGIN  
    SET @pos = CHARINDEX(',', @DataName)  
    IF @pos>0  
        BEGIN  
            SET @str = LEFT(@DataName, @pos-1) 
            SET @DataName = RIGHT(@DataName, LEN(@DataName)-@pos)  
        END  
    ELSE  
        BEGIN  
            SET @str = @DataName 
            SET @DataName = ''  
        END  

   INSERT INTO table01 VALUES(CONVERT(VARCHAR(100),@str)) 

END  
END  
GO

then run it: -

EXEC @return_value = [dbo].[DataInsert]
@DataName = N'five, bits, of, your, data'

*rows from table01: *

five

bits

your

data

(5 row(s) affected)

回复收藏 0 原文