是否可以将对象传递给存储过程以进行更新，删除和插入操作?如何? [英] Is it possible to pass an object to a store procedure to update ,delete and insert operation? how?

问题描述

我想将对象传递给 SQL-Server 2008 中的存储过程.在存储过程中，我想从对象中获取数据，然后将其插入到表中.在SQL Server中可以吗?如何?这里我的对象是自定义类对象.
请帮助我.

I want to pass an object to a store procedure in SQL-Server 2008. In store procedure I want to take data from my object and then insert that data to a table. Is it possible in SQL-Server? how? Here my object is custom class object.
please help me.

推荐答案

参考-将对象集合作为参数传递进入SQL Server存储过程 [ ^ ].

Refer - Passing an object collection as a parameter into SQL Server stored procedure[^].

报价:

假设使用SQL Server 2008+，则可以使用表值参数进行此操作.首先在SQL Server中创建表类型:

Assuming SQL Server 2008+, you can do this using a table-valued parameter. First in SQL Server create a table type:

CREATE TYPE dbo.HobbiesTVP AS TABLE
(
  HobbyID INT PRIMARY KEY,
  HobbyName NVARCHAR(50),
  HobbyTypeID INT
);

然后，您的存储过程将显示:

Then your stored procedure would say:

@Hobbies dbo.HobbiesTVP READONLY

在C#中(很抱歉，我不知道vb.net的等效语言)如下(但是，如果您只有一个UserID，则不必成为集合的一部分，对吗?):

正如Steve所指出的那样，

In C# (sorry I don''t know vb.net equivalent) it would be as follows (but if you just have one UserID, this doesn''t need to be part of the collection, does it?):

// as Steve pointed out, you may need to have your hobbies in a DataTable.

DataTable HobbyDataTable = new DataTable();
HobbyDataTable.Columns.Add(new DataColumn("HobbyID"));
HobbyDataTable.Columns.Add(new DataColumn("HobbyName"));
HobbyDataTable.Columns.Add(new DataColumn("HobbyTypeID"));

// loop through objHobbyCollection and add the values to the DataTable,
// or just populate this DataTable in the first place

using (connObject)
{
    SqlCommand cmd = new SqlCommand("dbo.User_Update", connObject);
    cmd.CommandType = CommandType.StoredProcedure;
    // other params, e.g. @UserID
    SqlParameter tvparam = cmd.Parameters.AddWithValue("@Hobbies", HobbyDataTable);
    tvparam.SqlDbType = SqlDbType.Structured;
    // ...presumably ExecuteNonQuery()
}

另请参阅- SQL中的表值参数Server 2008和.NET(C#) [

Also look at - Table Value Parameters in SQL Server 2008 and .NET (C#)[^]

是的，有可能-只需使用一个参数(或多个参数)

SQL存储过程参数 [ ^ ]

Yes, it''s possible - just use a parameter (or more than one)

SQL stored procedure parameters[^]

ALTER PROC usp_Getdetails
(
       @Parameter       INT,
       @Id		INT,
       @BookName	VARCHAR(100),
       @Price		INT,
       @CreatedBy       INT, 
       @LastUpDatedBy   INT, 
       @return		INT	= NULL OUTPUT,
       @errorId		INT	= NULL OUTPUT,
       @errorMessage	VARCHAR(1000) = NULL OUTPUT  
  )    
  AS
  SET NOCOUNT ON 
  BEGIN
  BEGIN TRY
  BEGIN TRAN
  
  IF NOT EXISTS (SELECT 1 FROM Getdetails where Id = @Id)
  BEGIN
  
  IF @Parameter =1
  
  BEGIN
  
  INSERT Getdetails
  (
   BookName,    Price,      ActiveYN,   
   DeletedYN,   CreatedOn,  CreatedBy,  LastUpDatedOn
  )
  VALUES
  (  
  @BookName,	@Price,      1,
  0,            GETDATE(),   @CreatedBy,  GETDATE()
  )
  
  END   
   
  END   
   
  ELSE
     
  BEGIN
        
  IF @Parameter =2
        
     BEGIN
     
     UPDATE Getdetails
     
     SET DeletedYN = 1,
            LastUpDatedOn = GETDATE()
            WHERE Id=@Id
         
    END
    
    ELSE
   
    IF @Parameter =3
   
    BEGIN
    UPDATE Getdetails
    SET
          BookName	=	@BookName,
          Price		=	@Price,
          ActiveYN	=       1,
          DeletedYN	=	0,
          LastUpDatedBy	=	@LastUpDatedBy
          
          WHERE Id=@Id
       END
       
   --   SELECT @errorMessage = ''Record Already Exists''
        
        END
        COMMIT TRAN
        END TRY
        BEGIN CATCH
        ROLLBACK TRAN
        
        SELECT @return              = -1,
                     @errorId       =  ERROR_NUMBER(),
                     @errorMessage  =  ERROR_MESSAGE()
    RETURN -1
    END CATCH
               --SELECT @errorMessage = ''Record Inserted Successfully''
       
        
     END
     SET NOCOUNT OFF

这篇关于是否可以将对象传递给存储过程以进行更新，删除和插入操作?如何?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！