0

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.

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.

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

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.

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

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.

T D
  • 1
  • 2

2 Answers2

1

Assumed you're already doing these:

  1. Defining table type in User-Defined Table Types in your database (often known as TVP, see reference section below);
  2. Adding parameter to pass DataTable to stored procedure (e.g. @SourceTable).

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

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

Alternative with AddWithValue:

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

Example usage in SqlConnection block:

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)

Tetsuya Yamamoto
  • 24,297
  • 8
  • 39
  • 61
0

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.

T D
  • 1
  • 2