I'm trying to call a stored procedure that accepts a table value parameter. I know that this isn't directly supported in Entity Framework yet but from what I understand you can do it using the ExecuteStoreQuery
command off of the ObjectContext
. I have a generic entity framework repository where I have the following ExecuteStoredProcedure
method:
public IEnumerable<T> ExecuteStoredProcedure<T>(string procedureName, params object[] parameters)
{
StringBuilder command = new StringBuilder();
command.Append("EXEC ");
command.Append(procedureName);
command.Append(" ");
// Add a placeholder for each parameter passed in
for (int i = 0; i < parameters.Length; i++)
{
if (i > 0)
command.Append(",");
command.Append("{" + i + "}");
}
return this.context.ExecuteStoreQuery<T>(command.ToString(), parameters);
}
The command string ends up like this:
EXEC someStoredProcedureName {0},{1},{2},{3},{4},{5},{6},{7}
I tried to run this method on a stored procedure that accepts a table valued parameter and it breaks. I read here that the parameters needed to be of type SqlParameter
and the table valued parameter needs to have the SqlDbType
set to Structured
. So I did this and I get an error stating:
The table type parameter p6 must have a valid type name
So, I set the SqlParameter.TypeName to the name of the user defined type I created on the database and then when I run the query I get the following truly helpful error:
Incorrect syntax near '0'.
I can get the query to run if I revert back to ADO.NET and and execute a data reader but I was hoping to get it to work using the data context.
Is there a way to pass a table value parameter using ExecuteStoreQuery
? Also, I am actually using Entity Framework Code First and casting the DbContext
to an ObjectContext
to get the ExecuteStoreQuery
method available. Is this necessary or can I do this against the DbContext
as well?
UPDATE
I've added support for this on Nuget Package - https://github.com/Fodsuk/EntityFrameworkExtras#nuget (EF4,EF5,EF6)
Check out the GitHub repository for code examples.
Slightly off question, but none the less useful for people trying to pass user-defined tables into a stored procedure. After playing around with Nick's example and other Stackoverflow posts, I came up with this:
class Program
{
static void Main(string[] args)
{
var entities = new NewBusinessEntities();
var dt = new DataTable();
dt.Columns.Add("WarningCode");
dt.Columns.Add("StatusID");
dt.Columns.Add("DecisionID");
dt.Columns.Add("Criticality");
dt.Rows.Add("EO01", 9, 4, 0);
dt.Rows.Add("EO00", 9, 4, 0);
dt.Rows.Add("EO02", 9, 4, 0);
var caseId = new SqlParameter("caseid", SqlDbType.Int);
caseId.Value = 1;
var userId = new SqlParameter("userid", SqlDbType.UniqueIdentifier);
userId.Value = Guid.Parse("846454D9-DE72-4EF4-ABE2-16EC3710EA0F");
var warnings = new SqlParameter("warnings", SqlDbType.Structured);
warnings.Value= dt;
warnings.TypeName = "dbo.udt_Warnings";
entities.ExecuteStoredProcedure("usp_RaiseWarnings_rs", userId, warnings, caseId);
}
}
public static class ObjectContextExt
{
public static void ExecuteStoredProcedure(this ObjectContext context, string storedProcName, params object[] parameters)
{
string command = "EXEC " + storedProcName + " @caseid, @userid, @warnings";
context.ExecuteStoreCommand(command, parameters);
}
}
and the stored procedure looks like this:
ALTER PROCEDURE [dbo].[usp_RaiseWarnings_rs]
(@CaseID int,
@UserID uniqueidentifier = '846454D9-DE72-4EF4-ABE2-16EC3710EA0F', --Admin
@Warnings dbo.udt_Warnings READONLY
)
AS
and the user-defined table looks like this:
CREATE TYPE [dbo].[udt_Warnings] AS TABLE(
[WarningCode] [nvarchar](5) NULL,
[StatusID] [int] NULL,
[DecisionID] [int] NULL,
[Criticality] [int] NULL DEFAULT ((0))
)
Constraints I found include:
ExecuteStoreCommand
have to be in order with the parameters in your stored procedure