Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How can I make my C# Parameterised query work properly using IDbCommand?

Posted on 2007-07-21
5
Medium Priority
?
1,208 Views
Last Modified: 2012-08-13
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.CreateCommand();
            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(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = "@ToggleField";
            parameter.Value = toggleField;
            parameter.DbType = DbType.String;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = "@NewValue";
            parameter.Value = newValue;
            parameter.DbType = DbType.Boolean;
            command.Parameters.Add(parameter);
            parameter = command.CreateParameter();

            parameter.ParameterName = "@IDField";
            parameter.Value = idField;
            parameter.DbType = DbType.String;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = "@ID";
            parameter.Value = id;
            parameter.DbType = DbType.Int64;
            command.Parameters.Add(parameter);

            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
0
Comment
Question by:chrisbray
  • 3
  • 2
5 Comments
 
LVL 22

Accepted Solution

by:
JimBrandley earned 2000 total points
ID: 19540631
You only need to have the ampersands on bind variable names, not tables and columns. Try:

 command.CommandText = "UPDATE Currencies SET Inactive = @NewValue WHERE CurrencyId = @ID";

That will work. I have never seen anyone bind table names and column names before. If you want something generic, you could construct the SQL in code, say pass in table name, various column names in update clause and others in where clause and bind parameters. That method works well for us.

0
 
LVL 3

Author Comment

by:chrisbray
ID: 19540666
Hi Jim,

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.
0
 
LVL 3

Author Comment

by:chrisbray
ID: 19540687
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.
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 19540696
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
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 19540697
My pleasure. Good luck.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question