SQL Parameter as table doesn't work

Question

I want to return a DataTable with this method but SqlParameter is not working properly.

private DataTable GetTable(string tableName)
{
        const string queryString = "SELECT * FROM @TABLE";

        SqlCommand sqlCommand = new SqlCommand(queryString, _sqlConnection);

        SqlParameter sqlParameter = new SqlParameter("@TABLE", SqlDbType.Text)
        {
            Value = tableName
        };

        sqlCommand.Parameters.Add(sqlParameter);

        _sqlConnection.Open();

        SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand);
        DataTable dataTable = new DataTable();

        dataAdapter.Fill(dataTable);

        _sqlConnection.Close();
        dataAdapter.Dispose();

        dataGridViewTable.DataSource = dataTable;
        dataGridViewTable.AutoResizeColumns();

        return dataTable;
}

I am sure connection is successful. Another method is working. This one doesn't. It throws a SqlException.

What does “not working properly” mean? It throws an exception? Show it to us. It gives the wrong answer? What does it give? What did you expect? — Dour High Arch, Feb 24 '16 at 17:49
There should be duplicate on using parameter to replace table name (I can't vote to close, jsut wait till someone copy-pastes answers here) - I.e. - http://stackoverflow.com/questions/17947736/sqlparameter-does-not-allows-table-name-other-options-without-sql-injection-at — Alexei Levenkov, Feb 24 '16 at 17:49
Also, you do not need to open/close the connection when you use fill. You pass the connection object to the SqlCommand object, which you have already done. Fill open and closes automatically. — Randy, Feb 24 '16 at 18:06

m-albert · Accepted Answer · 2016-02-24T22:41:34.523

You can't pass a table name as a parameter. Also, use using to easily close/dispose of disposable resources.

Try this...

private DataTable GetTable(string tableName)
{
    string queryString = "SELECT * FROM [" + tableName + "]";

    DataTable dataTable = new DataTable(tableName);
    using (SqlCommand sqlCommand = new SqlCommand(queryString, _sqlConnection))
    using (SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand))
    {
        _sqlConnection.Open();
        dataAdapter.Fill(dataTable);
        _sqlConnection.Close();
    }
    dataGridViewTable.DataSource = dataTable;
    dataGridViewTable.AutoResizeColumns();
    return dataTable;
}

EDIT: Added square brackets around table name in query to handle names with spaces.

SQL Parameter as table doesn't work

1 Answers1