具有表值参数的 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 调用此扩展过程的正确数据类型是什么。

任何帮助将不胜感激。

具有表值参数的 C# CLR 存储过程

此代码中有很多问题需要解决:

  1. 整体设计似乎不太适合Microsoft SQL Server世界,而更适合像PostgreSQL甚至Oracle(也许还有其他人?(这样的RDBMS。看起来您正在尝试使SQL Server像这些其他系统之一一样工作(或者至少期望它这样做(,但事实并非如此。我之所以这样说,是因为您应该花一些时间阅读各种数据类型、事务处理以及基于集与基于迭代/基于游标的方法。

  2. 如何在 C# 中正确传递表值参数

    好吧,您确实i_wordParam正确设置为SqlDbType.Structured。而且您必须i_wordParam.TypeName正确设置为 T_WORD .仅供参考,只有即席查询才需要设置TypeName,而不是通过CommandType.StoredProcedure调用存储过程时。

    因此,如果你有一个DataTableSqlDataReader或方法已经在 C# 代码中返回IEnumerable<SqlDataRecord>,那么你可以将其传递给SP_LOG当前设置该交互的方式。

  3. 用于

    从 SQL Server 调用此扩展过程的正确数据类型是什么。

    问题

    (至少是主要问题(是您无法将 TVP 传递给 SQLCLR 对象。如创建过程的 MSDN 页中所述:

    • 表值或游标数据类型不能用作参数。

    TVP 不是数据类型:它们是表类型。用户定义的表类型用于创建表变量。因此,虽然将 .NET Object 类型映射到 T-SQL SQL_VARIANT数据类型正确,但无法为SQL_VARIANT分配表变量。这就是您遇到操作数类型冲突的原因:T_WORD与sql_variant错误不兼容

  4. 正如@Damien_The_Unbeliever在对该问题的评论中已经指出的那样,您确实不应该对用户对象使用 sp_ 前缀,因为它是为系统保留的,并导致首先检查该对象的master数据库。

  5. 除非你有非常明确的理由这样做,否则我不会使用SET IMPLICIT_TRANSACTIONS ON.您应该通过 BEGIN TRAN 显式启动事务。如果在调用此过程之前已经启动事务(即 @@TRANCOUNT 在此过程开始时为> 0(,则末尾的COMMIT将导致错误,因为 @@TRANCOUNT 的值在过程结束时会比在开始时少。

    如果使用隐式事务的原因是在@i_word为空时不启动事务,则可以通过在开头添加以下内容来退出进程:

    IF (NOT EXISTS(SELECT COUNT(*) FROM @i_word))
    BEGIN
      RETURN;
    END;
    
  6. 您没有任何错误检查或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;
    
  7. 关于while (@i <=(SELECT COUNT(*) FROM @i_word))

    如果你需要一个 WHILE 循环(你不需要;稍后会详细介绍(,那么你不想在循环中执行COUNT(*),因为它每次都会被评估。

  8. 关于 WHILE 循环的结构:

    你永远不会递增@i,因此你有一个无限循环。如果需要 WHILE 循环,则在使用 SQL Server 2008 或更高版本时需要SET @i += 1;,如果使用 SQL Server 2005,则需要SET @i = @i + 1;

  9. 首先不需要 WHILE 循环。表值参数就是:表(虽然是表变量,但仍然如此(。因此,它们可以加入等。以下是更有效的方法:

    INSERT INTO LG_REPORT
      SELECT @i_msg, tmp.SWC_Value
      FROM   @i_word;
    
  10. 插入时,您确实应该指定字段名称。因此INSERT INTO LG_REPORT (column1, column2)而不仅仅是INSERT INTO LG_REPORT.不指定字段名称会导致将字段添加到LG_REPORT并忘记更新此INSERT并失败的情况。或者,也许您的字段顺序不正确。诸如此类的东西。

  11. 虽然它不会导致错误,但您(每个人,真的(应该养成用分号结束每个查询的习惯。