Entity Framework 6 - How to determine stored procedure input parameters

Question

I'm using EF6 and I've imported some stored procedures. Is there a way I can programmatically determine what the input parameters are for a particular procedure? I'd like to auto-generate some C# code to execute any stored procedure, but I first need to be able to determine what the input parameters are.

For example, I currently have imported a stored procedure named SellerModify. I have C# code to execute this stored procedure that looks, in part, as follows:

DbContext.Database.ExecuteSqlCommand("SellerModify @MarketName, @BankLocationCountryCode, @BankAccountOwnerName”, 
    new SqlParameter("@MarketName", seller.MarketName),
    new SqlParameter("@BankLocationCountryCode", seller.BankLocationCountryCode),                   
    new SqlParameter("@BankAccountOwnerName", seller.BankAccountOwnerName));

I'd like to autogenerate this code but I need to be able to determine the input parameters first.

Answer 1

What you're looking for is the equivelant of ADO.NET's SqlCommandBuilder.DeriveParameters() method. Unfortunately, to my knowledge, Entity Framework doesn't offer anything similar to this.

That said, assuming a Microsoft SQL Server database, you could query the database beforehand to get the list of parameters. This can be done using something similar to the following SQL:

SELECT      PARAMETER_NAME, 
            DATA_TYPE
FROM        information_schema.parameters
WHERE       specific_name = 'SellerModify'

I know this isn't exactly what you're looking for, but it may provide an alternative approach for solving your problem.

Answer 2

I was also looking for the similar solution but could not find anything until now. So I combined EF, SqlCommand, and SqlCommandBuilder.DeriveParameters(cmd).

/// <summary>
/// Prepare the SQL
/// </summary>
/// <param name="sqlText">sqlText contains stored procedure name</param>
/// <returns>Returns the prepared SQL</returns>
public (string sqlText, List<SqlParameter> parameters) PrepareSQL(string sqlText)
{
    List<SqlParameter> parameters = new List<SqlParameter>();

    using (var conn = new SqlConnection(db.Database.Connection.ConnectionString))
    {
        using (var cmd = new SqlCommand(sqlText, conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            conn.Open();
            SqlCommandBuilder.DeriveParameters(cmd);

            foreach (SqlParameter item in cmd.Parameters)
            {
                if (item.Direction != ParameterDirection.ReturnValue)
                {
                    sqlText += item.ParameterName + ",";
                }

                if (item.Direction == ParameterDirection.Output || item.Direction == ParameterDirection.InputOutput)
                {
                    item.Direction = ParameterDirection.Output;
                    parameters.Add(item);
                }
            }

            sqlText = sqlText.Trim(',');
            cmd.Parameters.Clear();
        }
    }

    return (sqlText, parameters);
}

You can use item.Direction == ParameterDirection.Input for the input parameters in the if statement.

And you can consume it like this:

//Stored Procedure
string sqlText = "Report_Portfolio_Statement_Investor_Wise";
List<SqlParameter> parameters;
(sqlText, parameters) = PrepareSQL(sqlText);

// Input Parameters 
// I enter manually for now, could be automated later
parameters.AddRange(
    new List<SqlParameter>() 
    {
        new SqlParameter("@clientCode", clientCode),
        new SqlParameter("@reportDate", reportDate),
        new SqlParameter("@fromDate", fromDate)
    }
);

var data = db.Database.SqlQuery<PortfolioStatementInvestorReportVM>(sqlText, parameters.ToArray()).ToList();