Alter table SqlCommand

Question

I am trying to change the datatype of one of the columns in a table using SqlCommand with parameters, but it doesn't work. Here is my code:

    Dictionary<string,string> dict = new Dictionary<string,string>();
    dict.Add("@TableName",TableColumnArray[0].ToString( ));
    dict.Add("@ColumnName",TableColumnArray[1].ToString( ));
    DBSql.ExecSQLStatement( "ALTER TABLE @TableName ALTER COLUMN @ColumnName varchar(MAX)",dict,connectionStringName);

    public static void ExecSQLStatement (string strsql,Dictionary<string,string> dict,string  connectionStringName)
    {
        SqlConnection con = CreateSqlConnectionStr(connectionStringName);
        SqlCommand cmd = new SqlCommand(strsql,con);
        foreach(string dictKey in dict.Keys)
        {
            cmd.Parameters.Add(new SqlParameter(dictKey,dict[dictKey]));
        }
        con.Open( );
        cmd.ExecuteNonQuery( );
        con.Close( );
    }

But the code keeps throwing an error:"Incorrect syntax near @TableName". I cannot find the solution to this problem. I could try to use stored procedures, but I really want to know why the code is not working. I usually use SqlCommand with parameters for select,insert statements, but it seems it doesnt work with alter statements?

John Woo · Accepted Answer · 2013-03-03 13:58:37Z

6

because by default, tableName and column names CANNOT BE PARAMETERIZED. One way you can do to avoid sql injection is to create a User Define Function that check if the tableName is valid or not. Then concatenate the name on the string. eg,

Here's the UDF

private bool IsValidColumnNameOrTableName(string tablecolumnName)
{
    // other codes
    return returnValue;
}

answered Mar 3, 2013 at 13:58

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

4 Comments

coffeeak Over a year ago

Thanks for the help. I will be looking into that!

Gigi Over a year ago

Interesting... maybe provide a link to some documentation that explains why there is this constraint on parameters?

John Woo Over a year ago

@Gigi here stackoverflow.com/questions/372033/…

coffeeak Over a year ago

Votes up for the idea because I was trying to avoid sql injection by not using string concatenations.

Moha Dehghan · Accepted Answer · 2013-03-03 13:59:08Z

1

You cannot use parameters in DDL statements. You should create the statement string dynamically:

DBSql.ExecSQLStatement(
    "ALTER TABLE " + TableColumnArray[0] + " ALTER COLUMN " + TableColumnArray[1] + " varchar(MAX)",
    dict,connectionStringName);

answered Mar 3, 2013 at 13:59

Moha Dehghan

18.5k3 gold badges60 silver badges74 bronze badges

Comments

shibormot · Accepted Answer · 2013-03-03 13:59:14Z

0

you need specify table name and column name exactly:

"ALTER TABLE " + TableColumnArray[0].ToString( ) + " ALTER COLUMN " + TableColumnArray[1].ToString( ) + "varchar(MAX)"

sql server does not allow syntax where table names and column names are variable values

answered Mar 3, 2013 at 13:59

shibormot

1,6382 gold badges12 silver badges24 bronze badges

1 Comment

coffeeak Over a year ago

I don't want to use string concatenations. Thanks for replying though

Collectives™ on Stack Overflow

Alter table SqlCommand

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related