具有表值参数的 C# CLR 存储过程
本文关键字:CLR 存储过程 参数 值参 | 更新日期: 2023-09-27 18:19:46
我有一个带有表值参数的 SQL Server 存储过程
CREATE TYPE T_WORD AS TABLE
(
SWC_Index INT IDENTITY,
SWC_Value VARCHAR(MAX)
)
CREATE PROCEDURE SP_LOG
@i_msg VARCHAR(4000) ,
@i_word T_WORD READONLY
AS
BEGIN
SET IMPLICIT_TRANSACTIONS ON
declare @i int
SET @i = 1
while (@i <=(SELECT COUNT(*) FROM @i_word))
begin
INSERT INTO LG_REPORT
values(@i_msg,(select SWC_Value from @i_word where SWC_Index = @i))
end
IF @@TRANCOUNT > 0
COMMIT
END
我需要编写一个 C# CLR 包装器,该包装器只是在新连接中执行此过程。
C# 代码如下所示
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
namespace SQLCLR
{
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void exec_SP_LOG(Object i_msg, Object i_word)
{
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
SqlCommand Command = new SqlCommand();
SqlParameter i_msgParam = new SqlParameter("@i_msg", SqlDbType.VarChar);
SqlParameter i_wordParam = new SqlParameter("@i_word", SqlDbType.Structured);
i_wordParam.TypeName = "T_WORD";
i_msgParam.Value = i_msg;
i_wordParam.Value = i_word;
Command.Parameters.Add(i_msgParam);
Command.Parameters.Add(i_wordParam);
Command.CommandText = "exec SP_LOG @i_msg, @i_word";
Command.Connection = connection;
connection.Open();
Command.ExecuteNonQuery();
connection.Close();
}
}
}
}
将 dll 添加到 SQL Server 并创建过程后
CREATE PROCEDURE [dbo].[exec_SP_LOG]
@i_msg sql_variant ,
@i_word sql_variant
AS
EXTERNAL NAME [SQLCLR].[SQLCLR.StoredProcedures].[exec_SP_LOG]
GO
我尝试执行此功能
declare @typ1 T_WORD
insert into @typ1(SWC_Value) values('djhgfj')
insert into @typ1(SWC_Value) values('dfhdf')
exec exec_SP_LOG 't1', @typ1
但是我收到以下错误
Msg 206,级别 16,状态 2,过程 exec_SP_LOG,第 0
行 操作数类型冲突:T_WORD与sql_variant不兼容
看起来 C# 中表值参数的数据类型有问题。
问题是如何在 C# 中正确传递表值参数,因此用于从 SQL Server 调用此扩展过程的正确数据类型是什么。
任何帮助将不胜感激。
此代码中有很多问题需要解决:
-
整体设计似乎不太适合Microsoft SQL Server世界,而更适合像PostgreSQL甚至Oracle(也许还有其他人?(这样的RDBMS。看起来您正在尝试使SQL Server像这些其他系统之一一样工作(或者至少期望它这样做(,但事实并非如此。我之所以这样说,是因为您应该花一些时间阅读各种数据类型、事务处理以及基于集与基于迭代/基于游标的方法。
-
如何在 C# 中正确传递表值参数
好吧,您确实
i_wordParam
正确设置为SqlDbType.Structured
。而且您必须i_wordParam.TypeName
正确设置为T_WORD
.仅供参考,只有即席查询才需要设置TypeName
,而不是通过CommandType.StoredProcedure
调用存储过程时。因此,如果你有一个
DataTable
、SqlDataReader
或方法已经在 C# 代码中返回IEnumerable<SqlDataRecord>
,那么你可以将其传递给SP_LOG
当前设置该交互的方式。 -
用于
问题从 SQL Server 调用此扩展过程的正确数据类型是什么。
(至少是主要问题(是您无法将 TVP 传递给 SQLCLR 对象。如创建过程的 MSDN 页中所述:
- 表值或游标数据类型不能用作参数。
TVP 不是数据类型:它们是表类型。用户定义的表类型用于创建表变量。因此,虽然将 .NET
Object
类型映射到 T-SQLSQL_VARIANT
数据类型正确,但无法为SQL_VARIANT
分配表变量。这就是您遇到操作数类型冲突的原因:T_WORD与sql_variant错误不兼容。 -
正如@Damien_The_Unbeliever在对该问题的评论中已经指出的那样,您确实不应该对用户对象使用
sp_
前缀,因为它是为系统保留的,并导致首先检查该对象的master
数据库。 -
除非你有非常明确的理由这样做,否则我不会使用
SET IMPLICIT_TRANSACTIONS ON
.您应该通过BEGIN TRAN
显式启动事务。如果在调用此过程之前已经启动事务(即@@TRANCOUNT
在此过程开始时为> 0(,则末尾的COMMIT
将导致错误,因为@@TRANCOUNT
的值在过程结束时会比在开始时少。如果使用隐式事务的原因是在
@i_word
为空时不启动事务,则可以通过在开头添加以下内容来退出进程:IF (NOT EXISTS(SELECT COUNT(*) FROM @i_word)) BEGIN RETURN; END;
-
您没有任何错误检查或
:ROLLBACK
逻辑。您应该使用以下方法BEGIN TRY BEGIN TRAN; ... one or more SQL Statements COMMIT; END TRY BEGIN CATCH ROLLBACK; ;THROW; -- introduced in SQL Server 2012 ---- Remove "THROW" and use the following if on SQL Server 2008 -- DECLARE @ErrMessage NVARCHAR(4000) = ERROR_MESSAGE(); -- RAISERROR(@ErrMessage, 16, 1); -- RETURN; END CATCH;
-
关于
while (@i <=(SELECT COUNT(*) FROM @i_word))
如果你需要一个 WHILE 循环(你不需要;稍后会详细介绍(,那么你不想在循环中执行
COUNT(*)
,因为它每次都会被评估。 -
关于 WHILE 循环的结构:
你永远不会递增
@i
,因此你有一个无限循环。如果需要 WHILE 循环,则在使用 SQL Server 2008 或更高版本时需要SET @i += 1;
,如果使用 SQL Server 2005,则需要SET @i = @i + 1;
。 -
首先不需要 WHILE 循环。表值参数就是:表(虽然是表变量,但仍然如此(。因此,它们可以加入等。以下是更有效的方法:
INSERT INTO LG_REPORT SELECT @i_msg, tmp.SWC_Value FROM @i_word;
-
插入时,您确实应该指定字段名称。因此
INSERT INTO LG_REPORT (column1, column2)
而不仅仅是INSERT INTO LG_REPORT
.不指定字段名称会导致将字段添加到LG_REPORT
并忘记更新此INSERT
并失败的情况。或者,也许您的字段顺序不正确。诸如此类的东西。 -
虽然它不会导致错误,但您(每个人,真的(应该养成用分号结束每个查询的习惯。