Sri Ram

Sri Ram

  • 1.8k
  • 131
  • 43k

User defined table type throws error when passed from MVC

Jan 11 2018 10:51 AM

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:

  1. CREATE TYPE [dbo].[CustomerType] AS TABLE(  
  2.     [CustId] [intNULL,  
  3.     [CustName] [varchar](100) NULL,  
  4.     [Country] [varchar](50) NULL  
  5. )  

Stored procedure:

  1. Create PROCEDURE spInsertCustomers  
  2. @Customers dbo.CustomerType READONLY  
  3. AS  
  4. BEGIN  
  5.     SET NOCOUNT ON;  
  6.     INSERT INTO Customers  
  7.     SELECT * FROM @Customers  
  8. END  
MVC Code:
  1. public ActionResult Index()  
  2.        {  
  3.            try  
  4.            {                  
  5.                TestUDFTable();  
  6.                ViewBag.Message = "SUCCESS";  
  7.            }  
  8.            catch (Exception ex)  
  9.            {  
  10.                ViewBag.Message = ex.Message;  
  11.            }              
  12.            return View();  
  13.        }

 

  1. public void TestUDFTable()  
  2.         {  
  3.              string connectionString = string.Empty;  
  4.             connectionString = ConfigurationManager.ConnectionStrings["appcon"].ToString();  
  5.   
  6.             DataTable dt = new DataTable();  
  7.             dt.Columns.Add("CustId");  
  8.             dt.Columns.Add("CustName");  
  9.             dt.Columns.Add("Country");  
  10.   
  11.             int counter = 1;  
  12.             while (counter < 3)  
  13.             {  
  14.                 DataRow row = dt.NewRow();  
  15.                 row["CustId"] = counter;  
  16.                 row["CustName"] = "Customer-" + counter;  
  17.                 row["Country"] = "USA";  
  18.   
  19.                 dt.Rows.Add(row);  
  20.                 counter++;  
  21.             }  
  22.             SqlParameter Parameter = new SqlParameter("@Customers", dt);  
  23.             Parameter.TypeName = "dbo.CustomerType";  
  24.             Parameter.SqlDbType = SqlDbType.Structured;  
  25.   
  26.             using (var connection = new SqlConnection(connectionString))  
  27.             {  
  28.                 var cmd = new SqlCommand();  
  29.                 cmd.Connection = connection;  
  30.                 cmd.Parameters.Add(Parameter);  
  31.                 cmd.CommandType = CommandType.StoredProcedure;  
  32.                 cmd.CommandText = "spInsertCustomers";  
  33.                 connection.Open();  
  34.                 cmd.ExecuteNonQuery();  
  35.                 connection.Close();  
  36.             }              
  37.         }

Answers (7)