C# EF Correct way to use ExecuteSqlCommand

Question

i have been trying a method that takes in a column name and a value i wish to update on a basic test db and i cannot seem to get and results using the SqlParameters? When i hard code the query i see the db table is updated?

Be great to understand what i am doing wrong with the following query:

public bool UpdateTestDataTable(string TID, string ColumnName, string ColumnValue)
{
    try
    {
        if (!string.IsNullOrEmpty(TID))
        {
            using (var db = new TestDBContext())
            {
                var rId = db.TempDb.SingleOrDefault(p => p.TID == TID);

                string s_q_el = @"UPDATE dbo.TempDb
                               SET @Column = @NewValue
                               WHERE TID = @TID
                               AND id = @DbId;";


                //This Works
                //string sql2 = @"UPDATE dbo.TempDb
                //               SET TestData1 = 'TestingABC'
                //               WHERE TID = '66A46552E9A0B912457CE804A54CE1AF'
                //               AND id = @DbId;";

                var col = new SqlParameter("@Column", SqlDbType.NVarChar)
                {
                    Value = ColumnName
                };

                var nval = new SqlParameter("@NewValue", SqlDbType.NVarChar)
                {
                    Value = ColumnValue
                };
                var paid = new SqlParameter("@PAID", SqlDbType.NVarChar)
                {
                    Value = TID
                };
                var id = new SqlParameter("@DbId", SqlDbType.Int)
                {
                    Value = rId.Id
                };


                db.Database.ExecuteSqlCommand(s_q_el, parameters: new[] { col, nval, paid, id});

                //db.Database.ExecuteSqlCommand(sql2);
                db.SaveChanges();

                return true;
            }
        }
        else
        {
            return false;
        }
    }
    catch(Exception UADT_EX)
    {
        Console.WriteLine($"Error: {UADT_EX.message}");
        return false;
    }
}

Calling with this:

UpdateTestDataTable("66A46552E9A0B912457CE804A54CE1AF", "TestData1","BLAHBLAH123");

I have tested quite a number of ways but figure i am totally missing the obvious here? The reason for this approach is i would like a single function to update various column data throughout a test app.

Many thanks

EDIT

I updated the query based on the comments received but would the following be bad practice?

string s_q_el = $"UPDATE dbo.TempDb SET {ColumnName}= @NewValue WHERE TID = @TID AND id = @DbId;";

As this now produces the correct result

Answer 1

You can do that as you showed, slightly modified:

string s_q_el = $@"UPDATE dbo.TempDb
                  SET {ColumnName} = @NewValue
                  WHERE TID = @TID
                  AND id = @DbId;";

var nval = new SqlParameter("@NewValue", SqlDbType.NVarChar)
{
   Value = ColumnValue
};
var paid = new SqlParameter("@PAID", SqlDbType.NVarChar)
{
   Value = TID
};
var id = new SqlParameter("@DbId", SqlDbType.Int)
{
   Value = rId.Id
};

db.Database.ExecuteSqlCommand(s_q_el, nval, paid, id);

Or you can also do this:

string s_q_el = $@"UPDATE dbo.TempDb
                  SET {ColumnName} = {{0}}
                  WHERE TID = {{1}}
                  AND id = {{2}};";

db.Database.ExecuteSqlCommand(s_q_el, nval, paid, id);

In the latter sample, SQL is still sent to backend using parameters, Linq automatically creates them as parameters named @p0, @p1 ... and binds accordingly. This might have problems like not choosing the right data type intended but would work most of the time.

Using ExecuteSqlCommand is sometimes very handy and preferred over getting the entity, updating and saving back (ie: a bulk update or delete - IOW if your criteria might return multiple rows to update).

Answer 2

Since you have an Entity Model, why not just use Reflection. Like this helper method on your DbContext:

public void UpdateEntity<T>(int id, string propertyName, string propertyValue) where T:class
{
    var entity = this.Set<T>().Find(id);
    var prop = typeof(T).GetProperty(propertyName);
    var val = Convert.ChangeType(propertyValue, prop.PropertyType);
    prop.SetValue(entity, val);
    this.SaveChanges();
}

If you know the key property name, you can even do this without first fetching the entity from the database. EG

public void UpdateEntity<T>(int id, string keyPropertyName, string propertyName, string propertyValue) where T:class, new()
{

    var entity = new T();
    typeof(T).GetProperty(keyPropertyName).SetValue(entity,Id);
    this.Set<T>().Add(entity);

    var prop = typeof(T).GetProperty(propertyName);
    var val = Convert.ChangeType(propertyValue, prop.PropertyType);
    prop.SetValue(entity, val);


    this.Entry(entity).State = EntityState.Unchanged;
    foreach (var p in this.Entry(entity).Properties)
    {
        p.IsModified = p.Metadata.Name == propertyName;
    }
    this.SaveChanges();
}

Answer 3

As stated in comments, you can't parameterize column names, but you can build a stored procedure that could consume parameters and build SQL from them:

CREATE PROCEDURE updateFromParameters 
  @column varchar(max),
  @newValue varchar(max),
  @TID varchar(max),
  @id int
AS

DECLARE @s_q_el varchar(max) 

SET @s_q_el = 'UPDATE dbo.TempDB ' 
    + 'SET ' + @column + ' = ''' + @newValue + ''''
    + 'WHERE TID = ''' + @TID + ''''
    + 'id = ' + @id

EXEC @s_q_el

Then just execute the stored procedure from your C# code:

using (var command = new SqlCommand("updateFromParameters", conn) ) {

   command.CommandType = CommandType.StoredProcedure;

   // add your parameters here

   conn.Open();
   command.ExecuteNonQuery();
}