I was trying to prevent SQL Injections on my code and it is not updating the parameters. Here is my code.
private static void CheckTableColumns(SqlConnection sqlCon, SqlTransaction sqlTrans,
String tableName, List<TDBColumnInfo> columns)
{
StringBuilder sb = new StringBuilder();
sb.Append("IF NOT EXISTS(SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS ");
sb.Append("WHERE TABLE_NAME = @tableName AND COLUMN_NAME = @columnName) ");
sb.Append("ALTER TABLE @tableName ADD @columnName @dataType");
String sqlFmt = sb.ToString();
SqlCommand cmd = new SqlCommand(sqlFmt);
cmd.Connection = sqlCon;
cmd.Transaction = sqlTrans;
foreach (TDBColumnInfo colInfo in columns)
{
if (colInfo.ColumnName == "RecID")
continue;
SqlParameter tableNameParam = new SqlParameter("tableName", SqlDbType.Text);
SqlParameter columnNameParam = new SqlParameter("columnName", SqlDbType.Text);
SqlParameter dataTypeParam = new SqlParameter("dataType", SqlDbType.Text);
tableNameParam.Value = tableName;
columnNameParam.Value = colInfo.ColumnName;
dataTypeParam.Value = colInfo.DataType;
cmd.Parameters.Add(tableNameParam);
cmd.Parameters.Add(columnNameParam);
cmd.Parameters.Add(dataTypeParam);
cmd.ExecuteNonQuery();
}
}
I also tried adding @'s at the beginning of the string here :
SqlParameter tableNameParam = new SqlParameter("tableName", SqlDbType.Text);
SqlParameter columnNameParam = new SqlParameter("columnName", SqlDbType.Text);
SqlParameter dataTypeParam = new SqlParameter("dataType", SqlDbType.Text);
Still same.