Nested SQL in C#

Question

I tried to adapt the solution from Understanding of nested SQL in C# Reply 4. But it dont work. I cant find the mistake. Think it is something that the statement cant use a parameter as part of the table name.

 string srcqry = @"USE [" + TableName+ "] " +
                            @"select TABLE_NAME from [INFORMATION_SCHEMA].[TABLES]";
        using (SqlConnection srccon = new SqlConnection(cs))
        using (SqlCommand srccmd = new SqlCommand(srcqry, srccon))
        {
            srccon.Open();
            using (SqlDataReader src = srccmd.ExecuteReader())
            {
                string insqry = @"USE [" + TableName+ "] " + "ALTER SCHEMA "+SchemaNameNew+" TRANSFER [dbo].@tabelle";

                // create new connection and command for insert:
                using (SqlConnection inscon = new SqlConnection(cs))
                using (SqlCommand inscmd = new SqlCommand(insqry, inscon))
                {
                    inscmd.Parameters.Add("@tabelle", SqlDbType.NVarChar, 80);
                    inscon.Open();

                    while (src.Read())
                    {
                        inscmd.Parameters["@tabelle"].Value = src["TABLE_NAME"];
                        inscmd.ExecuteNonQuery();
                    }
                }
            }
        }

I got the error that the statement is wrong in the @tabelle area. Any idea why it wont work? Thanks

A `USE` statement takes a database name, not a table name. Meanwhile, an `ALTER SCHEMA .. TRANSFER` does not accept a parameter at all, so the table name has to be interpolated in the query just like the schema name is. — Jeroen Mostert, Apr 15 '20 at 13:23
Sorry for the misunderstanding. In the 'USE' clause is a database name. I cleaned the code from individual stuff and named it wrong. — Michelangelo, Apr 16 '20 at 05:58

score 0 · Answer 1 · answered Apr 16 '20 at 06:25

I found a working solution.

                while (src.Read())
                {
                    var table= src["TABLE_NAME"];
                    var con = new System.Data.SqlClient.SqlConnection(cs);
                    con.Open();
                    var cmd = new System.Data.SqlClient.SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = @"USE [" + dbname+ "] " + "ALTER SCHEMA " + schemaname+ " TRANSFER [dbo]."+table;
                    cmd.ExecuteNonQuery();
                    con.Close();
                }

Nested SQL in C#

1 Answers1