I am trying to return the values from a column in a table in the database using ExecuteScalar()
.
The software is throwing an exception on ExecuteScalar()
stating Must declare the table variable "@tableName".
However, @tableName is declared so I am not sure why I am getting this error.
Code:
public object GetListOfDatabases(string tableName)
{
switch (tableName)
{
case "PMS":
tableName = "CALCULATION_SCHEDULE";
break;
case "UKPMS":
tableName = "UKPMS_CALCULATION_SCHEDULE";
break;
case "RCI":
tableName = "RCI_CALCULATION_SCHEDULE";
break;
}
if(tableName != null)
{
var connection = calculationContext.Database.GetDbConnection();
if (connection.State.Equals(ConnectionState.Closed))
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = @"
SELECT DATABASE_NAME FROM @tableName
";
var tableNameParam = command.CreateParameter();
tableNameParam.ParameterName = "@tableName";
tableNameParam.Value = tableName;
command.Parameters.Add(tableNameParam);
return command.ExecuteScalar();
}
}
else
{
return null;
}
}
Here is a similar query from the same interface that works, I don't understand why this one works and the other one doesn't:
public bool tableExists(string schema, string tableName)
{
var connection = calculationContext.Database.GetDbConnection();
if (connection.State.Equals(ConnectionState.Closed))
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = @"
SELECT 1 FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @Schema
AND TABLE_NAME = @TableName";
var schemaParam = command.CreateParameter();
schemaParam.ParameterName = "@Schema";
schemaParam.Value = schema;
command.Parameters.Add(schemaParam);
var tableNameParam = command.CreateParameter();
tableNameParam.ParameterName = "@TableName";
tableNameParam.Value = tableName;
command.Parameters.Add(tableNameParam);
return command.ExecuteScalar() != null;
}
}
Table names cannot be parameterised.
You'll have to tackle this by string interpolation or similar:
command.CommandText = $@"SELECT DATABASE_NAME FROM {tableName}";
Of course this opens your query to SQL injection, so you need to white-list table names.
Maybe by throwing an exception:
switch (tableName)
{
case "PMS":
tableName = "CALCULATION_SCHEDULE";
break;
case "UKPMS":
tableName = "UKPMS_CALCULATION_SCHEDULE";
break;
case "RCI":
tableName = "RCI_CALCULATION_SCHEDULE";
break;
default:
throw new ArgumentException("Invalid table");
}