LeCintas LeCintas - 22 days ago 7
ASP.NET (C#) Question

Convert DbContext way to SqlCommand way

Today I have an App which work using EF Core, with the DbContext class and looks like this:

public class RcoreContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=rCoreData.TestDb;Trusted_Connection=True;");
}

public DbSet<Hosts> Hosts { get; set; }
public DbSet<Route> Route { get; set; }
}

public class Hosts
{
public int HostsId { get; set; }
public string Host { get; set; }

[NotMapped]
public Dictionary<object, object> Datas { get; set; }

public string DatasJson { get { return JsonConvert.SerializeObject(Datas); } set { Datas = JsonConvert.DeserializeObject<Dictionary<object, object>>(value); } }
}

public class Route
{
public int RouteId { get; set; }
public int HostId { get; set; }
public string RealRoute { get; set; }
public string alias { get; set; }
}


I very like this way because the requests returns are based on existing classes (
Hosts
and
Route
in this code).

The SqlCommand way seems more comfortable to use (it seems to be more PHP-like) but seems to return the entries as
object
. (I read this article)

I would like to know if is it possible to make a mix between the 2 ways and to send a request as a StoredProcedure like SqlCommand do BUT still be based on existing classes for the request return.

Thanks

Answer

if you want to use ADO.NET in EF, you just need to get the current connection string and create the sqlcommand as below:

using (var context = new RcoreContext())
{
   // creates a Command 
   var command = context.Database.Connection.CreateCommand();
   command.CommandType = commandType;
   command.CommandText = sql;

   using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
        for (int i = 0; i < reader.FieldCount; i++)
        {
            Console.WriteLine(reader.GetValue(i));
        }
        Console.WriteLine();
        }
    }
}

and if you just want to execute sql or stored procedure, you can also use below in EF

 _dbContext.Database.ExecuteSqlCommand(sql, parameters);
_dbContext.Database.SqlQuery<TEntity>(sql, parameters);