none
Entity Framework - Execute Stored Procedures With Table Valued Patameter RRS feed

  • Question

  • How to pass a table valued parameter to a stored procedure in Entity Framework using ExecuteSqlCommand or SqlQuery method?

    Following is my code - 

    DataTable dataTable=new DataTable();

    dataTable.Columns.Add("col1", typeof(int));
    dataTable.Columns.Add("col2", typeof(bool));

    dbContext.Database.ExecuteSqlCommand("exec stored_proc @tvp",
                            new SqlParameter() { SqlDbType = SqlDbType.Structured, ParameterName = "@tvp", Value = dataTable }
                            );

    This throws an exception saying 'The table type parameter '@tvp' must have a valid type name.'

    Friday, April 10, 2015 12:10 PM

Answers

  • You must set the TypeName property of the SqlParameter to the name of your table type that you have defined in the database:

                SqlParameter param = new SqlParameter();
                param.SqlDbType = SqlDbType.Structured;
                param.ParameterName = "@tvp";
                param.TypeName = "dbo.YourTableType";
                param.Value = dataTable;
                dbContext.Database.ExecuteSqlCommand("exec stored_proc @tvp", param);

    Of course you must define the table type in the database first:

    CREATE TYPE dbo.YourTableType AS TABLE
        ( col1 int, col1 bit )

    Please remember to the following page on MSDN for more information: https://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx

    Hope that helps.

    Please also remember to close your threads by marking helpful posts as answer and then start a new thread if you have a new question. Please don't ask several questions in the same thread.

    Friday, April 10, 2015 2:11 PM

All replies

  • Get the either e.StackTrace from the exception handler or from the popup window "Copy to Clipboard" and then paste into notepad.  The '@' is used with a SQL Server database and may not be valid with other databases.  An Access database want the parameters to be in the same order as the database.

    It looks like the parameter name in the database isn't matching the parameter you are using"@tmp".


    jdweng

    Friday, April 10, 2015 1:03 PM
  • Parameter name is same and database is SQL Server.
    Friday, April 10, 2015 1:08 PM
  • You must set the TypeName property of the SqlParameter to the name of your table type that you have defined in the database:

                SqlParameter param = new SqlParameter();
                param.SqlDbType = SqlDbType.Structured;
                param.ParameterName = "@tvp";
                param.TypeName = "dbo.YourTableType";
                param.Value = dataTable;
                dbContext.Database.ExecuteSqlCommand("exec stored_proc @tvp", param);

    Of course you must define the table type in the database first:

    CREATE TYPE dbo.YourTableType AS TABLE
        ( col1 int, col1 bit )

    Please remember to the following page on MSDN for more information: https://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx

    Hope that helps.

    Please also remember to close your threads by marking helpful posts as answer and then start a new thread if you have a new question. Please don't ask several questions in the same thread.

    Friday, April 10, 2015 2:11 PM