I want to execute a stored procedure that has a user-defined table type in a Web API controller.
Here is code for the controller:
public IHttpActionResult PostTableForDTProductCodePO([FromBody] List<TableForDTProductCodePO> tableForDTProductCodePO)
{
if (tableForDTProductCodePO == null)
return BadRequest("Unusable resources.");
if (tableForDTProductCodePO.Count <= 0)
return BadRequest("Unusable resources.");
DataTable dtable = ToDataTable(tableForDTProductCodePO);
using (InspectionDBEntities dbCon = new InspectionDBEntities())
{
try
{
var response = dbCon.Database.SqlQuery<object>(
"exec dbo.InsertValueToTableForDTProductCodePO @TempTable",
dtable).ToString();
dbCon.SaveChanges();
return Ok("Done");
}
catch (Exception ex)
{
return BadRequest(ex.ToString());
}
}
}
Here is method to create DataTable:
public DataTable ToDataTable<TableForDTProductCodePO>(List<TableForDTProductCodePO> tableForDTProductCodePO)
{
PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(TableForDTProductCodePO)); ;
DataTable table = new DataTable();
for (int i = 0; i < props.Count; i++)
{
PropertyDescriptor prop = props[i];
table.Columns.Add(prop.Name, prop.PropertyType);
}
object[] values = new object[props.Count];
foreach (TableForDTProductCodePO item in tableForDTProductCodePO)
{
for (int i = 0; i < values.Length; i++)
{
values[i] = props[i].GetValue(item);
}
table.Rows.Add(values);
}
return table;
}
Here is the stored procedure code:
ALTER PROCEDURE [dbo].[InsertValueToTableForDTProductCodePO]
@TempTable DTProductCodePO READONLY
AS
BEGIN
INSERT INTO TableForDTProductCodePO
SELECT *
FROM @TempTable
END
I am getting no error, but the data is not added to the database. From Postman, I am getting right data in the controller parameters.
Any help?
Thank you