How do I specify a schema qualifier for a table in a C# SqlCommand?

Question

Working code is of the form:

string sql = "SELECT foo from myTable";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
contractTable = new DataTable();
da.Fill(contractTable);

Now the database schema has been changed from dbo to mySchema, it's not my user's default schema, and I don't have admin rights to change it. When I try to run the code I get:

System.Data.SqlClient.SqlException: Invalid object name 'myTable'.

OK, I understand, it's not finding it as it's not dbo. any more.

I tried adding the line:

cmd.Parameters.AddWithValue("@SchemaName", 'mySchema');

but that had no observable effect.

Then based on How to use a SqlCommand Parameter to specify the schema name for a select query

I tried adding the lines to my SQL

DECLARE @SchemaName sysname
SET @SchemaName = 'mySchema'
SELECT foo FROM @SchemaName.[myTable]

but that gives me an invalid syntax error at the table name when executing the SQL SELECT

What's the correct syntax for this?

Answer 1

If your SQL is indeed your code, this should work:

string aSchema = "newSchema";
string sql = "SELECT foo from " + aSchema + ".myTable"; 

But it does have the danger of SQL Injection

Additionally, you could put the schema name in your config file and reference the config entry in your C# code. This would protect you in the future in the event that the schema changes again.

string aSchema = ConfigurationManager.AppSettings.Get("schema_name");
string sql = "SELECT foo from " + aSchema + ".myTable";