Using parametrized parameters WITH sqlcommandbuilder [no dataadapter]

Question

I am trying to use a parametrized query which takes 2 column names and a table name and retrieves the data from a sql server DB.

The problem is it is not possible to parametrize the table name so i found a solution using a sqlcommandbuilder.quoteIdentifer(tablename) and this bit works...but apparently they don't play nice together.

I get exception containing a single word which is the column name If i put the column name by hand it works.

What is wrong here?

    public List<ItemsWithDescription> GetItemsFromDB(string name, string desc, string tableName)
    {
        List<ItemsWithDescription> items = new List<ItemsWithDescription>();
        try
        {
            Status = 1;
            SqlCommandBuilder builder = new SqlCommandBuilder();
            cmd = new SqlCommand("Select @Name, @Desc from "+ builder.QuoteIdentifier(tableName), conn);
            cmd.Parameters.AddWithValue("@Name", name);
            cmd.Parameters.AddWithValue("@Desc", desc);
            using (SqlDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    items.Add(new ItemsWithDescription(dr[name].ToString(), dr[name].ToString() + " | " + dr[desc].ToString()));                        
                }
            }
            items.Sort((x, y) => string.Compare(x.Item, y.Item));
        }
        catch
        {
            Status = -1;
        }
        return items;
    }

Edit: This works but I would prefer to know why both can't be used together:

cmd = new SqlCommand("Select" +
builder.QuoteIdentifier(name) + "," + 
builder.QuoteIdentifier(desc) + "from " +
builder.QuoteIdentifier(tableName), conn);

Soner Gönül · Accepted Answer · 2013-11-13 07:15:06Z

3

You can't parameterize column names. You can't do that in regular SQL actually.

What you need is Dynamic SQL.

If you follow the various newsgroups on Microsoft SQL Server, you often see people asking why they can't do:

SELECT * FROM @tablename
SELECT @colname FROM tbl
SELECT * FROM tbl WHERE x IN (@list)

For all three examples you can expect someone to answer Use dynamic SQL and give a quick example on how to do it. Unfortunately, for all three examples above, dynamic SQL is a poor solution. On the other hand, there are situations where dynamic SQL is the best or only way to go.

Also take a look Table-Valued Parameters if you use SQL Server 2008 and above.

answered Nov 13, 2013 at 7:15

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AngelicCore Over a year ago

Is Dynamic SQL prone to attacks?

Soner Gönül Over a year ago

@AngelicCore I don't think so.. sommarskog.se/dynamic_sql.html#SQL_injection

Collectives™ on Stack Overflow

Using parametrized parameters WITH sqlcommandbuilder [no dataadapter]

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related