Trouble executing SQL generated by SELECT to DROP multiple tables on SQL Server using C#

Question

I have a simple SELECT statement that I use to generate a script to create DROP TABLE statements for tables matching a pattern. The result of this query zero or more rows depending on how many tables match the given pattern. The SELECT statements is

SELECT 'DROP TABLE  ' + NAME FROM sys.tables WHERE NAME LIKE 'myTableName[_]%'  

This works perfectly. I get the results expected:

DROP TABLE  tLocation_101  
DROP TABLE  tLocation_103  
DROP TABLE  tLocation_105  
DROP TABLE  tLocation_106  
DROP TABLE  tLocation_107  

I can paste this into SSMS and it works as expected.

When I try to do this from C# by passing the SELECT statement to ExecuteQuery I get a DataTable with 0 rows. I also tried creating it as a string and calling EXEC on the string. Same result. Since I get no error I'm not sure where I'm going wrong. I also tried placing the statement into a file, reading the file which contains EXEC() and passing that to ExecuteQuery. Same results. I thought this would be trivial. Thanks for any suggestions.

Of course. Sorry about that. ExecuteQuery is a wrapper around SqlDataAdapter that creates a new SqlConnection, creates a new sqlCommand, sets the query as the CommandText for the for sqlCommand, and passes that to a new SqlDataAdapter instance. Then it calls Fill. The SQL statement does have a single quote before myTableName, I don't get any errors (which would occur if the query couldn't be executed), I just get back no results. ExecuteQuery returns a DataTable. The table I get back from this call has 0 rows. I should add that the ExecuteQuery is part of a vigorously tested utility class that has been in production for many years.

The body of ExecuteSQL:

    DataSet ds = new DataSet();
    SqlConnection conn = null;
    SqlCommand cmd = null;
    SqlDataAdapter adapter = null;

    try
    {    
         // this gets the connection string using the server name, database 
         // name, username, pasword, etc
         conn = new SqlConnection(GetConnectionString());
         cmd = new SqlCommand();
         cmd.CommandText = sqlQuery; // this is the statement above which is 
         //passed in
         cmd.CommandTimeout = 0;

         adapter = new SqlAdapter(cmd);
         adapter.SelectCommand = cmd;
         adapter.Fill(ds)
    }
    catch( )
    { ... }
        return ds;
    }

Again I never get any errors or exceptions, I just get back a dataset which has a single table with 0 rows. Thanks again.

Answer 1

have a look at this sample

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += '
DROP TABLE ' 
    + QUOTENAME(s.name)
    + '.' + QUOTENAME(t.name) + ';'
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s
    ON t.[schema_id] = s.[schema_id] 
    WHERE t.name LIKE 'LG_001%';

PRINT @sql;
-- EXEC sp_executesql @sql;

Answer 2

I can't repro this.

eg

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp24
{
    static class DbConnectionExtensions
    {
        public static int ExecuteNonQuery(this SqlConnection conn, String sqlQuery)
        {
            var cmd = conn.CreateCommand();
            cmd.CommandText = sqlQuery; // this is the statement above which is 
                                        //passed in
            cmd.CommandTimeout = 0;
            cmd.Connection = conn;

            return cmd.ExecuteNonQuery();

        }

        public static DataSet ExecuteQuery(this SqlConnection conn, String sqlQuery)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = null;
            SqlDataAdapter adapter = null;

            cmd = new SqlCommand();
            cmd.CommandText = sqlQuery; // this is the statement above which is 
                                        //passed in
            cmd.CommandTimeout = 0;
            cmd.Connection = conn;

            adapter = new SqlDataAdapter(cmd);
            adapter.SelectCommand = cmd;
            adapter.Fill(ds);

            return ds;

        }

    }
    class Program
    {



        static void Main(string[] args)
        {
            using (var conn = new SqlConnection("Server=localhost;integrated security=true;database=tempdb"))
            {
                conn.Open();

                conn.ExecuteNonQuery("create table tLocation_102(id int)");

                var ds = conn.ExecuteQuery("SELECT 'DROP TABLE  ' + NAME FROM sys.tables WHERE NAME LIKE 'tLocation[_]%'  ");

                var dt = ds.Tables[0];
                Console.WriteLine(dt.Rows.Count);

            }



            Console.ReadKey();
        }
    }
}