Executing stored procedure with User Defined Data Table parameter in Web API POST method

889 views Asked by At

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

0

There are 0 answers