DinaDee DinaDee - 3 months ago 9
C# Question

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

Answer

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