将数据表传递给SQL Server存储过程不起作用

I have an ASP.NET application that passes a Datatable to a web service, then from the web service to a SQL Server stored procedure. When I publish the web application and web service to the server and run, it fails. When I run the application from the local host pointing to the web service on the server, it works fine. When I run the both the web application and web service from localhost, it works fine.

我有一个ASP.NET应用程序,它将Datatable传递给Web服务,然后从Web服务传递到SQL Server存储过程。当我将Web应用程序和Web服务发布到服务器并运行时,它会失败。当我从指向服务器上的Web服务的本地主机运行应用程序时,它工作正常。当我从localhost运行Web应用程序和Web服务时,它工作正常。

I did some troubleshooting and see that the following line is the problem but I am not sure how to solve:

我做了一些故障排除,看到以下行是问题,但我不知道如何解决:

cmdCommit.Parameters.AddWithValue(@SourceTable, dtSource);

When I comment the line above, everything works. When I replace the reference to the DataTable (dtSource) in the line above with a string datatype, it works.

当我评论上面的一行时,一切正常。当我用字符串数据类型替换上面一行中对DataTable(dtSource)的引用时,它可以正常工作。

Here is the entire web method, I am using this code within a try/catch block:

这是整个Web方法,我在try / catch块中使用此代码:

DataTable dtSource = ObjectToData(sourceTable);
dtSource.TableName = TableTypeObject;

using (SqlConnection cnn = new SqlConnection(_cnnSqlCapss))
{
    SqlCommand cmdCommitChange = new SqlCommand("usp_Stored_Procedure", cnn);
    cmdCommitChange.CommandType = CommandType.StoredProcedure;

    cmdCommitChange.Parameters.AddWithValue("@Parm1",  Value1);
    cmdCommitChange.Parameters.AddWithValue("@Parm2", Value2);
    cmdCommitChange.Parameters.AddWithValue("@Parm3", dtSource);

    var returnParameter = cmdCommitChange .Parameters.Add("@ReturnVal", SqlDbType.Int);

    returnParameter.Direction = ParameterDirection.ReturnValue;

    cnn.Open();
    cmdCommitChange .ExecuteNonQuery();

    var result = returnParameter.Value;
    return (int)result;
}

The confusing part is that when I run the web application from the localhost and reference the web service on the server, it works. I don't understand why it fails when I run the web application from the server.

令人困惑的部分是,当我从localhost运行Web应用程序并引用服务器上的Web服务时,它可以工作。我不明白为什么当我从服务器运行Web应用程序时它失败了。

When I comment the line that reference the DataTable everything works.

当我评论引用DataTable的行时,一切正常。

I have tried the following and still no success:

我尝试了以下但仍然没有成功:

SqlParameter tvpParam cmdCommit.Parameters.AddWithValue "@SourceTable", dtSource);  
 tvpParam.SqlDbType = SqlDbType.Structured; 
 tvpParam.TypeName = "dbo.SourceTableType";

Also, The web method is not throwing an exception.

此外,Web方法不会抛出异常。

2 个解决方案

#1

Assumed you're already doing these:

假设你已经在做这些:

Defining table type in User-Defined Table Types in your database (often known as TVP, see reference section below);

在数据库中的用户定义表类型中定义表类型(通常称为TVP,请参阅下面的参考部分);

Adding parameter to pass DataTable to stored procedure (e.g. @SourceTable).

添加参数以将DataTable传递给存储过程(例如@SourceTable)。

Then, you can use SqlDbType.Structured to pass DataTable contents as stored procedure parameter like this:

然后,您可以使用SqlDbType.Structured将DataTable内容作为存储过程参数传递,如下所示:

cmdCommitChange.Parameters.Add("@SourceTable", SqlDbType.Structured).Value = dtSource;

Alternative with AddWithValue:

替代AddWithValue:

cmdCommitChange.Parameters.AddWithValue("@SourceTable", dtSource).SqlDbType = SqlDbType.Structured;

Example usage in SqlConnection block:

SqlConnection块中的示例用法:

using (SqlConnection cnn = new SqlConnection(_cnnSqlCapss))
{
    SqlCommand cmdCommitChange = new SqlCommand("usp_Stored_Procedure", cnn);
    cmdCommitChange.CommandType = CommandType.StoredProcedure;

    cmdCommitChange.Parameters.AddWithValue("@Parm1", Value1);
    cmdCommitChange.Parameters.AddWithValue("@Parm2", Value2);
    cmdCommitChange.Parameters.AddWithValue("@Parm3", Value3);

    // add this line
    cmdCommitChange.Parameters.Add("@SourceTable", SqlDbType.Structured).Value = dtSource;

    cmdCommitChange.Parameters.Add("@ReturnVal", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

    cnn.Open();
    cmdCommitChange.ExecuteNonQuery();

    var result = (int)returnParameter.Value;
    return result;
}

Reference:

Table-Valued Parameters (MS Docs)

表值参数(MS Docs)

#2

I found the problem. I am passing a text value that is too large for a column on my datatable.

我发现了问题。我传递的文本值对于我的数据表中的列来说太大了。

The web service was indeed throwing an exception but there was some code in my application's routine which was preventing me from seeing it.

Web服务确实引发了异常,但是我的应用程序的例程中有一些代码阻止我看到它。

#1