User defined table type throws error when passed from MVC

Question

I'm using a user-defined table type parameter to a stored procedure in SQL Server 2016 for inserting multiple records from ASP.NET MVC.

On debugging from Visual Studio 2013(local dev system), execution gets completed successfully but wherein when the same web page is accessed from a dev server url, it throws below error.

The table type parameter '@Customers' must have a valid type name.

I have explicitly given the type name in c# code. This logic works from local dev system but not after publishing to a dev site even though the site points to same database.

Can anyone please help on this?

.NET Framework : 4.5
SQL Server Version : 2016

User-defined table type:

CREATE TYPE [dbo].[CustomerType] AS TABLE(
    [CustId] [int] NULL,
    [CustName] [varchar](100) NULL,
    [Country] [varchar](50) NULL
)

Stored procedure:

Create PROCEDURE spInsertCustomers
@Customers dbo.CustomerType READONLY
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO Customers
    SELECT * FROM @Customers
END

MVC Code:

public ActionResult Index()
       {
           try
           {
               TestUDFTable();
               ViewBag.Message = "SUCCESS";
           }
           catch (Exception ex)
           {
               ViewBag.Message = ex.Message;
           }
           return View();
       }

 

public void TestUDFTable()
        {
             string connectionString = string.Empty;
            connectionString = ConfigurationManager.ConnectionStrings["appcon"].ToString();

            DataTable dt = new DataTable();
            dt.Columns.Add("CustId");
            dt.Columns.Add("CustName");
            dt.Columns.Add("Country");

            int counter = 1;
            while (counter < 3)
            {
                DataRow row = dt.NewRow();
                row["CustId"] = counter;
                row["CustName"] = "Customer-" + counter;
                row["Country"] = "USA";

                dt.Rows.Add(row);
                counter++;
            }
            SqlParameter Parameter = new SqlParameter("@Customers", dt);
            Parameter.TypeName = "dbo.CustomerType";
            Parameter.SqlDbType = SqlDbType.Structured;

            using (var connection = new SqlConnection(connectionString))
            {
                var cmd = new SqlCommand();
                cmd.Connection = connection;
                cmd.Parameters.Add(Parameter);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "spInsertCustomers";
                connection.Open();
                cmd.ExecuteNonQuery();
                connection.Close();
            }
        }

Answer 1

Hi Prashant!

 

I think it's a problem of hosting server iis application pool size, you could resolved this just this line of code in webconfig

 

<connectionStrings>
<add name="ConnectionString"
connectionString="Data Source=localhost;
Min Pool Size=0; //set pool size
Max Pool Size=100;
Pooling=true;
Initial Catalog=Tracking;
Integrated Security=True;"
providerName="System.Data.SqlClient"/>
</connectionStrings>