Fetching dynamic columns using StoredProcedure in EntityFramework

Sudip — Fri, 16 Jun 2023 15:00:09 +0000

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.

Response Compression in a .Net Core API

Sudip — Fri, 16 Jun 2023 14:54:36 +0000

By enabling response compression, you can reduce the size of the responses sent by your API, improving network efficiency and overall performance.

Install the package Microsoft.AspNetCore.ResponseCompression

Configure it as shown in the code snippet.

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.ResponseCompression;
using Microsoft.Extensions.DependencyInjection;
using System.IO.Compression;

public class Startup
{
    public void ConfigureServices(IServiceCollection services)
    {
        services.AddResponseCompression(options =>
        {
            options.EnableForHttps = true; // Enable compression for HTTPS requests
            options.Providers.Add<BrotliCompressionProvider>(); // Use Brotli compression
            options.Providers.Add<GzipCompressionProvider>(); // Use Gzip compression
        });

        services.Configure<BrotliCompressionProviderOptions>(options =>
        {
            options.Level = CompressionLevel.Fastest; // Set the compression level for Brotli
        });

        services.Configure<GzipCompressionProviderOptions>(options =>
        {
            options.Level = CompressionLevel.Optimal; // Set the compression level for Gzip
        });

        // Other service configurations...
    }

    public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
    {
        // Other app configurations...

        app.UseResponseCompression();

        // Other app middleware...
    }
}

Thank you.
Please follow me for more articles.

Forem: Sudip

Fetching dynamic columns using StoredProcedure in EntityFramework

What would be the solution?

Response Compression in a .Net Core API