是否可以将对象传递给存储过程以进行更新,删除和插入操作?如何? [英] 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屋!