chrisbray
asked on
How can I make my C# Parameterised query work properly using IDbCommand?
Hi Guys,
I have set up an UPDATE function to simply reset the value of a specified toggle field. The hard coded version of the SQL statement works fine, but the parameterized version is not updated with the parameter values. However, when I do exactly the same thing to obtain data via an ExecuteScalar() for example, it works perfectly.
Code:
/// <summary>
/// Set a single Boolean value in a specified table field, e.g. a Toggle Field
/// </summary>
/// <param name="tableName">A string representing the name of the table to update</param>
/// <param name="idField">A string representing the name of the ID field</param>
/// <param name="id">An Int64 value representing the ID of the record to update</param>
/// <param name="toggleField">A string representing the name of the field to update</param>
/// <param name="newValue">The new boolean value to assign to the field</param>
/// <returns></returns>
public bool SetBooleanValue(string tableName, string idField, Int64 id, string toggleField, bool newValue)
{
IDbCommand command = providerFactory.CreateComm and();
command.Connection = connection;
// command.CommandText = "UPDATE Currencies SET Inactive = true WHERE CurrencyId = 174"; <-- this works
command.CommandText = "UPDATE @Table SET @ToggleField = @NewValue WHERE @IDField = @ID";// <-- this doesn't!!
IDbDataParameter parameter;
parameter = command.CreateParameter();
parameter.ParameterName = "@Table";
parameter.Value = tableName;
parameter.DbType = DbType.String;
command.Parameters.Add(par ameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@ToggleField";
parameter.Value = toggleField;
parameter.DbType = DbType.String;
command.Parameters.Add(par ameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@NewValue";
parameter.Value = newValue;
parameter.DbType = DbType.Boolean;
command.Parameters.Add(par ameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@IDField";
parameter.Value = idField;
parameter.DbType = DbType.String;
command.Parameters.Add(par ameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@ID";
parameter.Value = id;
parameter.DbType = DbType.Int64;
command.Parameters.Add(par ameter);
bool result;
connection.Open();
result = (command.ExecuteNonQuery() == 1);
connection.Close();
return result;
}
When the code is run, I get an error 'Table @Table does not exist'.
_________________
Chris Bray
Vertical Software
http://www.verticalsoftware.co.uk
I have set up an UPDATE function to simply reset the value of a specified toggle field. The hard coded version of the SQL statement works fine, but the parameterized version is not updated with the parameter values. However, when I do exactly the same thing to obtain data via an ExecuteScalar() for example, it works perfectly.
Code:
/// <summary>
/// Set a single Boolean value in a specified table field, e.g. a Toggle Field
/// </summary>
/// <param name="tableName">A string representing the name of the table to update</param>
/// <param name="idField">A string representing the name of the ID field</param>
/// <param name="id">An Int64 value representing the ID of the record to update</param>
/// <param name="toggleField">A string representing the name of the field to update</param>
/// <param name="newValue">The new boolean value to assign to the field</param>
/// <returns></returns>
public bool SetBooleanValue(string tableName, string idField, Int64 id, string toggleField, bool newValue)
{
IDbCommand command = providerFactory.CreateComm
command.Connection = connection;
// command.CommandText = "UPDATE Currencies SET Inactive = true WHERE CurrencyId = 174"; <-- this works
command.CommandText = "UPDATE @Table SET @ToggleField = @NewValue WHERE @IDField = @ID";// <-- this doesn't!!
IDbDataParameter parameter;
parameter = command.CreateParameter();
parameter.ParameterName = "@Table";
parameter.Value = tableName;
parameter.DbType = DbType.String;
command.Parameters.Add(par
parameter = command.CreateParameter();
parameter.ParameterName = "@ToggleField";
parameter.Value = toggleField;
parameter.DbType = DbType.String;
command.Parameters.Add(par
parameter = command.CreateParameter();
parameter.ParameterName = "@NewValue";
parameter.Value = newValue;
parameter.DbType = DbType.Boolean;
command.Parameters.Add(par
parameter = command.CreateParameter();
parameter.ParameterName = "@IDField";
parameter.Value = idField;
parameter.DbType = DbType.String;
command.Parameters.Add(par
parameter = command.CreateParameter();
parameter.ParameterName = "@ID";
parameter.Value = id;
parameter.DbType = DbType.Int64;
command.Parameters.Add(par
bool result;
connection.Open();
result = (command.ExecuteNonQuery()
connection.Close();
return result;
}
When the code is run, I get an error 'Table @Table does not exist'.
_________________
Chris Bray
Vertical Software
http://www.verticalsoftware.co.uk
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Jim,
Your solution works fine, but I still think it sucks big time that you cannot parameterize whatever you want - what a half arsed system that really is!!
The points are yours, my friend, and thanks for the quick response.
Chris Bray.
Your solution works fine, but I still think it sucks big time that you cannot parameterize whatever you want - what a half arsed system that really is!!
The points are yours, my friend, and thanks for the quick response.
Chris Bray.
Chris - I did not mean it absolutely cannot be done, simply that I have never seen anyone attemp it before. If it's possible, I do not know the syntax.
The big value of using bind parameters for your queries lies in statement reuse in the DB server. A big chunk of the time spent in processing the query is analyzing it to produce the most efficient execution plan. When you do not use bind parameters, statements are never reused. When you do, they can be. The system keeps a cache of prepared plans, and hashes the statement to generate a way to identify it quickly. Changing anything in the statement, even just adding a space, causes a new hash value to be generated, and the cached statement cannot be located, so it caches another one.
Jim
The big value of using bind parameters for your queries lies in statement reuse in the DB server. A big chunk of the time spent in processing the query is analyzing it to produce the most efficient execution plan. When you do not use bind parameters, statements are never reused. When you do, they can be. The system keeps a cache of prepared plans, and hashes the statement to generate a way to identify it quickly. Changing anything in the statement, even just adding a space, causes a new hash value to be generated, and the cached statement cannot be located, so it caches another one.
Jim
My pleasure. Good luck.
ASKER
It seems odd that the table and column can't be parameterised - I would have thought that a most basic requirement of parameterisation!!
After all, if you have to build a string with the column and the table before you start, why not add the other parameters whilst you are at it???
Surely there must be a way??
Chris Bray.