0

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

Pro-n-apps
  • 55
  • 12
  • Table valued parameter can not accept List of C# class objects. You need to pass DataTable as value for the table-valued parameters. – Chetan Feb 12 '18 at 04:25
  • it says The table type parameter 'p0' must have a valid type name – Pro-n-apps Feb 12 '18 at 04:54
  • https://stackoverflow.com/questions/17817997/system-argumentexception-the-table-type-parameter-must-have-a-valid-type-name – Chetan Feb 12 '18 at 05:08
  • I was already on that.. No use for me. I do not know. No Error.. – Pro-n-apps Feb 12 '18 at 05:10
  • Instead of directly passing the table you cab create sqlparameter explicitly with typeName and value set to it. And then pass the parameter to the method.`var p = new SqlParameter("@TempTable"); p.TypeName = "DTProductCodePO"; p.Value= dtable;` and pass `p` to the `SqlQuery` instead of `dtable`. – Chetan Feb 12 '18 at 05:19
  • I did that mate.. No use – Pro-n-apps Feb 12 '18 at 05:19
  • https://stackoverflow.com/questions/10409576/pass-table-valued-parameter-using-ado-net – superstator Feb 12 '18 at 05:37

0 Answers0