Stored procedures EF and NET CORE

Question

I am building a WEB API to generate JSON objects in .net core

The thing is the data sets are generated in SQL stored procedures (using dynamic SQL) and i dont know the type of objects that are returned so i can map it to a concrete model, since the output columns change depending on the parameters.

Does any one know ho to retrive the data set from the BD in net core 1.0 with or without using EF?

Browsed a lot and can only find ansers that use models

Thanks in advance

Answer 1

You can add the following dependencies for your project in project.json file:

• System.Data.Common
• System.Data.SqlClient

As you can see in the next image:

Rebuild your project and you can code something like this:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Dynamic;

namespace ConsoleApp1
{
    public class Program
    {
        public static IEnumerable<dynamic> GetData(String cmdText)
        {
            using (var connection = new SqlConnection("server=(local);database=Northwind;integrated security=yes;"))
            {
                connection.Open();

                using (var command = new SqlCommand(cmdText, connection))
                {
                    using (var dataReader = command.ExecuteReader())
                    {
                        var fields = new List<String>();

                        for (var i = 0; i < dataReader.FieldCount; i++)
                        {
                            fields.Add(dataReader.GetName(i));
                        }

                        while (dataReader.Read())
                        {
                            var item = new ExpandoObject() as IDictionary<String, Object>;

                            for (var i = 0; i < fields.Count; i++)
                            {
                                item.Add(fields[i], dataReader[fields[i]]);
                            }

                            yield return item;
                        }
                    }
                }
            }
        }

        public static void Main(String[] args)
        {
            foreach (dynamic row in GetData("select * from Shippers"))
            {
                Console.WriteLine("Company name: {0}", row.CompanyName);
                Console.WriteLine();
            }

            Console.ReadKey();
        }
    }
}

Please let me know if this is useful.