CODEWITHSUNDEEP
c#sql-serverentity-framework
Phoenix
Phoenix

Reputation: 467

How to convert stored procedure result to entity

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. 
}

Upvotes: 1

Views: 2046

Answers (3)

Alen.Toma
Alen.Toma

Reputation: 4870

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(); 
    }
}

Upvotes: 1

Hogan
Hogan

Reputation: 70528

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

Upvotes: 0

Alfredo Arizpe
Alfredo Arizpe

Reputation: 1

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!

Upvotes: 0

Related Questions