net core - convert reader to json

Question

I am working with .net core 1.0.1. I want to execute a stored procedure from a Sql Server Table. I can connect to the database correctly, now I have the following code:

var con = _context.Database.GetDbConnection();
var cmd = con.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure; 
cmd.CommandText = "procedures.sp_Users";
cmd.Parameters.Add(new SqlParameter("@Command", SqlDbType.VarChar) { Value = "Login" } );
cmd.Parameters.Add(new SqlParameter("@user", SqlDbType.VarChar) { Value = "admin" } );
cmd.Parameters.Add(new SqlParameter("@pass", SqlDbType.VarChar) { Value = "admin" } );

var reader = cmd.ExecuteReader();

So I want to convert this reader to a JSON string for returning it in a controller.

How can I achieve it?

I read I can not use DataTable and DataSet because they are not cross-platform.

You could use `DataReaderConverter` from [JSON.net serialize directly from oledbconnection](https://stackoverflow.com/questions/33835729/json-net-serialize-directly-from-oledbconnection). That converter serializes directly from any `IDataReader`. — dbc, Oct 07 '16 at 18:35
dbc, I started using mssql via .net core today ... and though you seem to be sharing something useful I have to say ... it's just not enough for someone brand new, and that's who would be asking this question. ... elaborate — karezza, Nov 15 '18 at 00:49

score 0 · Accepted Answer · edited May 23 '17 at 12:16

Solved with the following:

var con = _context.Database.GetDbConnection();
var cmd = con.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure; 
cmd.CommandText = "procedures.sp_Users";
cmd.Parameters.Add(new SqlParameter("@Command", SqlDbType.VarChar) { Value = "Login" } );
cmd.Parameters.Add(new SqlParameter("@user", SqlDbType.VarChar) { Value = "admin" } );
cmd.Parameters.Add(new SqlParameter("@pass", SqlDbType.VarChar) { Value = "admin" } );

var retObject = new List<dynamic>();
con.Open();
using (var dataReader = cmd.ExecuteReader())
{
    while (dataReader.Read())
    {
        var dataRow = new ExpandoObject() as IDictionary<string, object>;
        for (var iFiled = 0; iFiled < dataReader.FieldCount; iFiled++)
            dataRow.Add(
                dataReader.GetName(iFiled),
                dataReader.IsDBNull(iFiled) ? null : dataReader[iFiled] // use null instead of {}
            );

        retObject.Add((ExpandoObject)dataRow);
    }
}
return retObject;

As suggested in the following link

net core - convert reader to json

1 Answers1