Convert DbContext way to SqlCommand way

Question

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

Winson · Accepted Answer · 2016-11-16 09:46:36Z

6

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

edited Nov 16, 2016 at 9:46

answered Nov 16, 2016 at 9:40

Winson

7876 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Victor Castro Over a year ago

Hello, _dbContext.Database.SqlQuery<TEntity>(sql, parameters); seems to be what I look for but I use ASP.NET Core 1.0 (so EF Core) and .SqlQuery() does not exist. Instead of it, I can use .FromSql(), is it possible to use stored procedure with it ? Thanks

Winson Over a year ago

I think you can take a look this

Victor Castro Over a year ago

Thank you, this is exactly what i want

Nora Palomaki · Accepted Answer · 2017-04-18 19:59:50Z

2

    using (var connection = context.Database.GetDbConnection())
    using (var command = context.Database.GetDbConnection().CreateCommand())
    { 
       command.CommandText = string.Format(sqlQuery, id);
       connection.Open();
       using (var reader = command.ExecuteReader()){}
    }

answered Apr 18, 2017 at 19:59

Nora Palomaki

211 bronze badge

Comments

MMalke · Accepted Answer · 2018-06-20 22:53:56Z

0

using (var connection = new SqlConnection(_dbContext.Database.Connection.ConnectionString))
using (var cmd = new SqlCommand())
{
      cmd.Connection = connection;
      cmd.CommandText = "your query here";
      cmd.CommandType = CommandType.Text;

      cmd.Parameters.Add("@param1", SqlDbType.Int);

      cmd.Connection.Open();
      cmd.ExecuteNonQuery();
}

answered Jun 20, 2018 at 22:53

MMalke

2,1161 gold badge26 silver badges38 bronze badges

Collectives™ on Stack Overflow

Convert DbContext way to SqlCommand way

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related