How do I pass a (table) variable from a C# program to an SQL script

Question

I want to have a function in C# with an argument which will be a table name in an SQL script it reads from. The code looks something like this in C#:

    static void createTable(SqlConnection connection, String tableName, String fileName)
    {
      try
        {
            var fileContent = File.ReadAllText(fileName+".sql");
            var sqlQueries = fileContent.Split(new[] { " GO " }, StringSplitOptions.RemoveEmptyEntries);
            var cmd = new SqlCommand(fileName, connection);
            SqlParameter param = cmd.Parameters.Add("@toBeReplaced", tableName);
            connection.Open();
            foreach (var query in sqlQueries)
            {
                cmd.CommandText = query;
                cmd.ExecuteNonQuery();
            }
            connection.Close();
        }
        catch (SqlException e)
        {
            Console.WriteLine(e.ToString());
        }

    }

When this function runs, I want it to execute the script in fileName.sql where it inserts into the table 'tableName'. The .sql script looks like this:

    use myDatabase
     GO 
    alter table TESTPersonsProject drop constraint FK_TESTPersonsProject_TESTproject
    alter table TESTSideResearch drop constraint FK_TESTSideResearch_TESTproject
    alter table TESTSamples drop constraint FK_TESTSamples_TESTproject
    alter table @toBeReplaced drop constraint PK_TESTProject
    alter table @toBeReplaced add tmp int;
     GO 
    update @toBeReplaced set tmp=projectID where tmp is null
    alter table @toBeReplaced drop column projectID
    alter table @toBeReplaced add id_new int identity(1,1)
    alter table @toBeReplaced add constraint PK_TESTProject primary key (id_new)
    ...

I know I could let the function look ugly and just have the query as a string. However, I do want to keep it clean and be able to use this function for more than just one query.

How do I declare and use this passed variable so that this query can affect more than just one specified table?

p.s. I'm using SQL SERVER 2008

score 0 · Answer 1 · edited May 23 '17 at 10:26

You can't use variables as table or column names in SQL. Column names and table names must be static.

The only way to do what you want to do is to generate the query through string concatenation. You can do the concatenation either in a stored procedure which uses EXECUTE to run the string query, or you can do it in C# and execute the string directly.

Either way, make sure that you validate your input, since both techniques are vulnerable to SQL injection.

See here: Table name as variable

score 0 · Answer 2 · answered Jun 29 '15 at 14:25

You can't use parameters for table names in SQL Server, why not do the replace yourself since you're already reading in the query?

foreach (var query in sqlQueries)
{
    cmd.CommandText = query.Replace("@toBeReplaced", tableName);
    cmd.ExecuteNonQuery();
}

score 0 · Answer 3 · answered Jun 29 '15 at 14:29

You should do something like this since you are not interested in SQL string concatenation.

use myDatabase
     GO 
DECLARE @queryvarchar(MAX)

SET @query= '
    alter table TESTPersonsProject drop constraint FK_TESTPersonsProject_TESTproject
    alter table TESTSideResearch drop constraint FK_TESTSideResearch_TESTproject
    alter table TESTSamples drop constraint FK_TESTSamples_TESTproject
    alter table @toBeReplaced drop constraint PK_TESTProject
    alter table @toBeReplaced add tmp int;
     GO 
    update @toBeReplaced set tmp=projectID where tmp is null
    alter table @toBeReplaced drop column projectID
    alter table @toBeReplaced add id_new int identity(1,1)
    alter table @toBeReplaced add constraint PK_TESTProject primary key (id_new)'

EXEC (@query)

But I believe you are making it more ugly this way than replacing in the C#.

How do I pass a (table) variable from a C# program to an SQL script

3 Answers3