Fetching dynamic columns using StoredProcedure in EntityFramework

Sometimes, you have to return dynamic columns with names e.g. column-1, column-2, …, column-n from the stored procedure. Since the column is varying in terms of name and number, it is difficult to map to our Model class.

What would be the solution?

One of the solutions is returning IDictionary. The code is given below.

public async Task<IEnumerable<IDictionary<string, object>>> GetDynamicDataFromSPAsync(string storedProcedureName, string connectionString, object[]? parameter = null)
    {
        List<Dictionary<string, object>> items = new List<Dictionary<string, object>>();

        if (connectionString is null)
        {
            return items;
        }
        using (var connection = new SqlConnection(connectionString))
        {
            await connection.OpenAsync();
            using (var command = new SqlCommand(storedProcedureName, connection))
            {
                if (parameter is not null)
                {
                    command.Parameters.AddRange(parameter);
                }
                command.CommandType = CommandType.StoredProcedure;
                using (var reader = await command.ExecuteReaderAsync())
                {
                    if (reader.HasRows)
                    {
                        while (await reader.ReadAsync())
                        {
                            Dictionary<string, object> obj = new Dictionary<string, object>();

                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                string columnName = reader.GetName(i);
                                object columnValue = reader.GetValue(i);
                                obj[columnName] = columnValue;
                            }
                            items.Add(obj);
                        }
                    }
                }
            }
        }
        return items;

    }

you can write the object[] parameter as shown below.

var parameter= new object[]
            {
             new SqlParameter("@PersonId", SqlDbType.Int) { Value = 1},                  
             new SqlParameter("@CountryCode", SqlDbType.VarChar) { Value = "AU"}
            };

Thank you.

Blog

Fetching dynamic columns using StoredProcedure in EntityFramework

Sudip

What would be the solution?

Join Our Newsletter. No Spam, Only the good stuff.

Related