CODEWITHSUNDEEP
c#dapper
DinaDee
DinaDee

Reputation: 287

how to minimize using statements on sql db connection commands from C# helper class

Currently, I keep writing the below code every time I have a simple select statement in my c# helper page. 

using (var ctx = new MyDBContext())
    {
        using (var cmd = (SqlCommand)ctx.Database.Connection.CreateCommand())

I figured out a cleaner way to write this for update, but can't get it to work with select. Here is what I did for update:

using (var ctx = new MyDBContext())
  {
   await ctx.Database.ExecuteSqlCommandAsync(@"UPDATE PO SET ESD = @ESD WHERE PoNumber = @PoNumber",new SqlParameter("ESD", (object)ESD ?? DBNull.Value), new SqlParameter("PoNumber", PoNumber));}
   }

and this is what I am trying for Select, but it isn't working

using (var ctx = new MyDBContext())
        {
            string sqlQuery ="SELECT * FROM POStages WHERE PoNumber = @PoNumber";
           poStages =  await ctx.Database.SqlQuery((sqlQuery
                ,new SqlParameter("PoNumber", PoNumber)).ToListAsync(); 
        }

Upvotes: 1

Views: 148

Answers (1)

Vitalii Ivanov
Vitalii Ivanov

Reputation: 3251

SqlParameter should be @PoNumber.

Here is correct syntax, both are valid

await ctx.Database.SqlQuery(
    typeof(DAL.Models.POStages),
    sqlQuery, 
    new SqlParameter("@PoNumber", PoNumber)).ToListAsync();

or 

await ctx.Database.SqlQuery<DAL.Models.POStages>(            
        sqlQuery, 
        new SqlParameter("@PoNumber", PoNumber)).ToListAsync();

Upvotes: 3

Related Questions