Using SQL Table valued parameter with Entity Framework

Question

I am using Entity Framework v4.0 in my project to connect to a database. I am in a situation to pass a List as input parameter to a stored procedure and do some manipulations in SP and get back List as SP result. I know table valued parameter is one option. But after some investigation I found it is not possible to use Table Valued parameter in Entity Framework. Is there any other way without using Table Valued Parameter to do it through Entity framework?

Is there any chance you could upgrade to EF5? It is supported — MaxSC
– MaxSC, Commented Oct 23, 2013 at 15:33

mayabelle · Accepted Answer · 2013-10-23 15:35:40Z

5

You can still pass a TVP to a stored procedure even if you are using entity framework.

Example:

// Create metadata records
IEnumerable<SqlDataRecord> sqlDataRecords = new List<SqlDataRecord>();

// Create a list of SqlDataRecord objects from your list of entities here

SqlConnection storeConnection = (SqlConnection)((EntityConnection)ObjectContext.Connection).StoreConnection;
try
{
    using (SqlCommand command = storeConnection.CreateCommand())
    {
        command.Connection = storeConnection;
        storeConnection.Open();

        SqlParameter[] sqlParameters = parameters.ToArray();
        command.CommandText = YourStoredProcedureName;
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add(new SqlParameter("YourTVPName", SqlDbType.Structured)
                            {
                                Value = sqlDataRecords,
                                TypeName = "dbo.Your_Table_Type"
                            });

        using (DbDataReader reader = command.ExecuteReader())
        {
            // Read results
        }
    }
}
finally
{
    storeConnection.Close();
}

answered Oct 23, 2013 at 15:35

mayabelle

10.1k10 gold badges40 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sadhurthan Over a year ago

Thankssss!!!! That helps.... But how to get back the list from Stored Procedure ?? ExecuteReader does it ??

mayabelle Over a year ago

See here for info on reading the results: msdn.microsoft.com/en-us/library/haa3afyz.aspx

Collectives™ on Stack Overflow

Using SQL Table valued parameter with Entity Framework

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related