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

isnt it deprecated in core? if its not any examples how to set it? i am a web forms developer making the transition, its prety diferent ^^ — Luciano Pinto, Oct 03 '16 at 19:56
When you say data set yoou mean the data set class in ADO.NET or the result from database server ? — H. Herzl, Oct 03 '16 at 20:07
Yup it works, the problem is that i need to return an object to be serilized in jason from the execution of a stored procedure but data set doenst exist also in core any changes on how to acomplish this? — Luciano Pinto, Oct 03 '16 at 20:25
You want to return an object but without have a definition (class) to serialize in web api ? — H. Herzl, Oct 03 '16 at 20:28
Exacly :) since the SP creates dynamic SQL that crates tables over a meta data structure , so i realy dont know with tables ate gona come out and wich colums its has — Luciano Pinto, Oct 03 '16 at 20:33
I have updated my answer for return a dynamic object, put that code into your web api, that should work :) — H. Herzl, Oct 03 '16 at 20:45

H. Herzl · Accepted Answer · 2016-10-03T20:43:55.200

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.

Stored procedures EF and NET CORE

1 Answers1

Linked