How to Pass Table-Valued Parameters to Stored Procedures in Entity Framework?-C++-php.cn

How to Pass Table-Valued Parameters to Stored Procedures in Entity Framework?

Patricia Arquette

Release： 2025-01-12 22:06:43

Original

297 people have browsed it

How to Pass Table-Valued Parameters to Stored Procedures in Entity Framework?

Entity Framework stored procedure table-valued parameters

Entity Framework itself does not directly support passing table-valued parameters to stored procedures. However, a workaround can be implemented using the ObjectContext method of ExecuteStoreQuery .

In the above code, there is a common ExecuteStoredProcedure method in the repository:

<code class="language-csharp">public IEnumerable<T> ExecuteStoredProcedure<T>(string procedureName, params object[] parameters)
{
    StringBuilder command = new StringBuilder();
    command.Append("EXEC ");
    command.Append(procedureName);
    command.Append(" ");

    // 为每个传入的参数添加占位符
    for (int i = 0; i < parameters.Length; i++)
    {
        if (i > 0)
            command.Append(",");

        command.Append("{" + i + "}");
    }

    return this.context.ExecuteStoreQuery<T>(command.ToString(), parameters);
}</code>

Copy after login

When passing table-valued parameters using this method, you may get errors such as "Table type parameter p6 must have a valid type name". To resolve this issue, you must specify SqlDbType as Structured and set SqlParameter.TypeName to the name of the user-defined type (UDT) in the database.

However, even with these modifications, you may still encounter errors related to invalid syntax.

Custom extension method

A custom extension method of the

ObjectContext class can simplify this process:

<code class="language-csharp">public static void ExecuteStoredProcedure(this ObjectContext context, string storedProcName, params object[] parameters)
{
    string command = "EXEC " + storedProcName + " @caseid, @userid, @warnings";

    context.ExecuteStoreCommand(command, parameters);
}</code>

Copy after login

This extension method allows you to call stored procedures directly with table-valued parameters. Example usage is as follows:

<code class="language-csharp">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);</code>

Copy after login

Restrictions

When using this extension method or similar custom methods, please consider the following limitations:

Stored procedure parameters must be in order and correspond to the parameters passed to the extension method.
All columns in the UDT must be specified, even if they have default values.

The above is the detailed content of How to Pass Table-Valued Parameters to Stored Procedures in Entity Framework?. For more information, please follow other related articles on the PHP Chinese website!