How to convert stored procedure result to entity

Question

I'm currently executing my stored procedure below, and it works perfectly. But I can't specify the command timeout.

 var results = await _dbContext.DbContext.Database.SqlQuery<GetOutputDto>(@"[dbo].[GetOutput] " + parameterString, list.ToArray()).ToListAsync();

Now I've change this to the below, and wondering what's the best way to convert the result to an object. I have over 30 properties, so setting each value would be quite tedious. Was wondering if there's a clean solution as Entity Framework solution.

using (var conn = new SqlConnection(_dbContextProvider.DbContext.Database.Connection.ConnectionString))
{
    conn.Open();
    SqlCommand cmd = new SqlCommand(@"[dbo].[GetOutput]", conn);
    cmd.CommandTimeout = 60;
    cmd.CommandType = CommandType.StoredProcedure;

    foreach (var item in list)
    {
        cmd.Parameters.Add(item);
    }

    cmd.ExecuteNonQuery();

    cmd.Connection.Close();

    // How to get the result to entity in a clean manner. 
}

https://stackoverflow.com/questions/6232633/entity-framework-timeouts — Alexander, Jan 21 '19 at 21:15

score 1 · Answer 1 · answered Jan 21 '19 at 18:39

Using System.reflection in those situation is really handy.

public static List<T> Convert<T>(IDataReader dr) where T : class, new()
{
    List<T> list = new List<T>();
    T obj = default(T);
    while (dr.Read()) {
        obj = Activator.CreateInstance<T>();
        foreach (PropertyInfo prop in obj.GetType().GetProperties()) {
            if (!object.Equals(dr[prop.Name], DBNull.Value)) {
                prop.SetValue(obj, dr[prop.Name], null);
            }
        }
        list.Add(obj);
    }
    return list;
}


using (var conn = new SqlConnection(_dbContextProvider.DbContext.Database.Connection.ConnectionString))
{
    conn.Open();
    SqlCommand cmd = new SqlCommand(@"[dbo].[GetOutput]", conn);
    cmd.CommandTimeout = 60;
    cmd.CommandType = CommandType.StoredProcedure;

    foreach (var item in list)
    {
        cmd.Parameters.Add(item);
    }

    using ( var reader = cmd.ExecuteReader() ){
    List<Entity> result = Convert<Entity>(reader); // convert to entity.
    cmd.Connection.Close(); 
    }
}

Hogan · Answer 2 · 2019-01-21T17:38:21.490

Not that hard, do it like this

note, this is lazy eval so it should perform well when there is user IO, still fairly fast in other cases, I've used it in data ETL projects with many records.

public static IEnumerable<dynamic>( /* params */)
{
   // build command object here.

      using (SqlDataReader reader = cmd.ExecuteReader())
      {

        if (reader.Read())  // read the first one to get the columns collection
        {
          var cols = reader.GetSchemaTable()
                       .Rows
                       .OfType<DataRow>()
                       .Select(r => r["ColumnName"]);

          do
          {
            dynamic t = new System.Dynamic.ExpandoObject();

            foreach (string col in cols)
            {
              ((IDictionary<System.String, System.Object>)t)[col] = reader[col];
            }

            yield return t;
          } while (reader.Read());
        }
      }

   // remember to close connection
}

From my simple DB framework https://gist.github.com/hoganlong/b7f5c5e8dde61ae3cd6f

score 0 · Answer 3 · answered Jan 21 '19 at 17:29

I would in all honesty send over as an array and convert to table type within SQL and do the dirty work on the server side. Also a good way to be able to specify the timeout can be done by either the connection strings within your config file or you can also pass that same parameter over to sql with a WAITFOR DELAY.

Cheers!

How to convert stored procedure result to entity

3 Answers3