当表值参数具有VARBINARY类型时，空DataTable会导致错误 [英] Empty DataTable Causes Errors When Table-Valued Parameter Has VARBINARY Types

问题描述

我有一个C＃web应用程序作为SQL Server的传递;请求详细SQL Server命令进来，我们解析请求，生成必要的.Net类型，然后使用它们执行 SqlCommands 等。结果是C＃Web应用程序需要非常灵活，真的不能对应该的样子做出太多的假设。

我最近解决了当表值参数包含 VARBINARY 类型时引起异常的问题。在一般情况下，我们循环传入请求，并使用适当数量的列和行构造一个 DataTable 。没有指定列的数据类型，并且数据值刚刚插入为对象 s。对于 VARBINARY 类型，将导致以下错误：

从数据类型nvarchar（max）至varbinary（max）不允许。使用CONVERT函数运行此查询。

我发现此StackOverflow文章，并且能够解决问题。请参阅下面的伪代码：

  for（var colNdx = 0; colNdx< requestTvp.Columns.Count; ++ colNdx）
 {
 myDataTable.Columns.Add（）; 
 
 if（requestTvp.Rows.Empty）
 {
 continue; 
} 
 
 if（requestTvp.Rows [0] .Columns [colNdx] .DataType == Bytes）
 {
 myDataTable.Columns [colNdx] .DataType = typeof（SqlBinary）; 
} 
} 
  

从我能说出来，这是非常有用的。当输入的TVP没有行时，会发生问题，因为我没有数据可以检查。在这种情况下，我们只是使用正确数量的列构造一个 DataTable （这在有关TVP的请求元数据中指定），但这些列没有明确设置的数据类型。在这种情况下， DataTable 没有行。这导致了同样的例外：

不允许从数据类型nvarchar（max）到varbinary（max）的隐式转换。使用CONVERT函数运行此查询。

以下是完整的堆栈跟踪：

  System.Data.SqlClient.SqlException（0x80131904）：不允许从数据类型nvarchar（max）到varbinary（max）的隐式转换。使用CONVERT函数运行此查询。表值参数@tvp的数据不符合参数的表类型。 
在System.Data.SqlClient.SqlConnection.OnError（SqlException异常，Boolean breakConnection，Action`1 wrapCloseInAction）
在System.Data.SqlClient.SqlInternalConnection.OnError（SqlException异常，Boolean breakConnection，Action`1在System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning（TdsParserStateObject stateObj，Boolean callerHasConnectionLock，Boolean asyncClose）中的
 $ System.Data.SqlClient.TdsParser.TryRun中的
（RunBehavior runBehavior，SqlCommand cmdHandler，SqlDataReader dataStream， BulkCopySimpleResultSet bulkCopyHandler，TdsParserStateObject stateObj，Boolean& dataReady）
在System.Data.SqlClient.SqlDataReader.TryConsumeMetaData（）
在System.Data.SqlClient.SqlDataReader.get_MetaData（）
在System.Data .SqlClient.SqlCommand.FinishExecuteReader（SqlDataReader ds，RunBehavior runBehavior，String resetOptionsString）
在System.Data.SqlClient.SqlCommand.RunExecuteReaderTds（CommandBehavior cmdBehavior，RunBehavior runBehavior ，Boolean returnStream，Boolean async，Int32 timeout，Task&任务，布尔asyncWrite，SqlDataReader ds）
在System.Data.SqlClient.SqlCommand.RunExecuteReader（CommandBehavior cmdBehavior，RunBehavior runBehavior，Boolean returnStream，String方法，TaskCompletionSource`1完成，Int32超时，任务和任务，布尔asyncWrite） 
在System.Data.SqlClient.SqlCommand.RunExecuteReader（CommandBehavior cmdBehavior，RunBehavior runBehavior，Boolean returnStream，String方法）
在System.Data.SqlClient.SqlCommand.ExecuteReader（CommandBehavior行为，String方法）
在System.Data.SqlClient.SqlCommand.ExecuteReader（CommandBehavior行为）
  

老实说，理解为什么问题出现在所有...如果没有数据在 DataTable 甚至导致无效隐式转换？

什么让事情更令人沮丧的是，这个错误发生在请求甚至获取到我的数据库之前，所以我无法使用SQL Server Profiler来计算t / b>

所以这里是我的问题：如何正确地传递一个空的 DataTable 作为表值参数存储过程当相应的用户定义表类型有一个 VARBINARY 字段时？在构建 DataTable 时，关于表值参数的唯一信息是用户定义表类型的名称，它包含的列数，存储过程中的参数名称和请求TVP中包含的值，在这种情况下本质上是一个空集。

解决方案

p>这是我提出的工作。如果数据表是空的，只是绕过表类型的SqlCommand对象声明，然后自己做：

  using（SqlConnection c = new SqlConnection（< connectionString>））
 {
 c.Open（）; 
 
 DataTable emptyTable = new DataTable（）; 
 
 emptyTable.Columns.Add（c1，typeof（int））; 
 emptyTable.Columns.Add（c2，typeof（string））; 
 
 DataRow row = emptyTable.NewRow（）; 
 
 row [c1] = 99; 
 row [c2] = new String（'X'，Int16.MaxValue）; 
 
 //取消注释使表非空
 // emptyTable.Rows.Add（row）; 
 
 SqlCommand cmd = c.CreateCommand（）; 
 
 if（emptyTable.Rows.Count> 0）
 {
 cmd.CommandText =dbo.BOB; 
 cmd.CommandType = System.Data.CommandType.StoredProcedure; 
 SqlParameter p = cmd.Parameters.AddWithValue（@ TP，emptyTable）; 
 
 p.SqlDbType = SqlDbType.Structured; 
 p.TypeName =dbo.KrjVarBin; 
} 
 
 else 
 {
 cmd.CommandType = CommandType.Text; 
 cmd.CommandText =declare @empty as dbo.KrjVarBin; execute dbo.bob @empty; 
} 
 
 cmd.ExecuteNonQuery（）; 
 
} 
  

I have a C# web application that serves as a passthrough to SQL Server; requests that detail SQL Server commands come in, we parse the request, generate the necessary .Net types and then use them to execute SqlCommands and such. The upshot of that is that the C# web application needs to be very flexible and really can't make too many assumptions about what a request "should" look like.

I recently solved a problem that was causing exceptions to be thrown when a table-valued parameter contained VARBINARY types. In the general case, we loop over the incoming request and construct a DataTable with the appropriate number of columns and rows. The data type of the columns weren't specified, and the data values were just inserted as objects. For VARBINARY types, this would result in the following error:

Implicit conversion from data type nvarchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.

I found this StackOverflow post, and was able to solve the problem. Please see my pseudo-code below:

for (var colNdx = 0; colNdx < requestTvp.Columns.Count; ++colNdx)
{
    myDataTable.Columns.Add();

    if (requestTvp.Rows.Empty)
    {
        continue;
    }

    if (requestTvp.Rows[0].Columns[colNdx].DataType == Bytes)
    {
        myDataTable.Columns[colNdx].DataType = typeof(SqlBinary);
    }
}

From what I can tell, this is working great. The problem occurs when the incoming TVP has no rows since I have no data to check against. In this case, we just construct a DataTable with the correct number of columns (this is specified in the request metadata about the TVP), but these columns have no explicitly set data type. The DataTable has no rows in this case. This results in the same exception:

Implicit conversion from data type nvarchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.

Here is the full stack trace:

System.Data.SqlClient.SqlException (0x80131904): Implicit conversion from data type nvarchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query. The data for table-valued parameter "@tvp" doesn't conform to the table type of the parameter.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)

Honestly, I don't understand why the problem is occurring at all...if there's no data in the DataTable what is even causing the invalid implicit conversion?

What makes things even more frustrating, is that this error occurs before the request even gets to my database, so I'm not able to use SQL Server Profiler to figure out exactly what is being sent to the database server.

So here's my question: how do I correctly pass an empty DataTable as table-valued parameter to a stored procedure when the corresponding User-Defined Table Type has a VARBINARY field? The only information I have about the table-valued parameter when I'm constructing the DataTable is the name of the User-Defined Table Type, the number of columns it contains, the name of the parameter in the stored procedure and the values contained in the request TVP, which in this case is essentially an empty set.

解决方案

Here is the work around I have come up with. If the data table is empty, just bypass the SqlCommand objects declaration of the table type and do it yourself:

      using(SqlConnection c = new SqlConnection("<connectionString>"))
        {
            c.Open();

            DataTable emptyTable = new DataTable();

            emptyTable.Columns.Add("c1", typeof(int));
            emptyTable.Columns.Add("c2", typeof(string));

            DataRow row = emptyTable.NewRow();

            row["c1"] = 99;
            row["c2"] = new String('X', Int16.MaxValue);

           // Uncomment to make table non empty
           // emptyTable.Rows.Add(row);

            SqlCommand cmd = c.CreateCommand();

            if (emptyTable.Rows.Count > 0)
            {
                cmd.CommandText = "dbo.BOB";
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                SqlParameter p = cmd.Parameters.AddWithValue("@TP", emptyTable);

                p.SqlDbType = SqlDbType.Structured;
                p.TypeName = "dbo.KrjVarBin";
            }

            else
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "declare @empty as dbo.KrjVarBin; execute dbo.bob @empty";
            }

            cmd.ExecuteNonQuery();

        }

这篇关于当表值参数具有VARBINARY类型时，空DataTable会导致错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！