ExecuteNonQuery is not working to create TempTable SQL Server

Question

The temp table is not being created in the database. I verified that the credentials have access to create a temp table. Copy and pasted the SQL command and it works in SSMS.

No exceptions are thrown when debugging. The cmd variable has the proper connection and SQL text before executing.

I've used the same connection string in other parts of the app to query the server successfully, so there is no issue there.

My goal is to create a temp table, then populate it via SqlBulkCopy then do a merge update then drop the temp table.

EDIT: My error was referencing the wrong table in the DestinationTableName but moreso that I was checking the progress in SSMS with a separate connection that could not see the temp table. Also, the finally statement is redundant. Thanks all!

        string tmpTable = @"create table #TempTable 
                            (
                            [Column1] [varchar](50) NOT NULL,
                            [Column2] [varchar](50) NOT NULL,
                            [Column3] [varchar](50) NOT NULL
                            )";
        string connString = "Data Source=AzureDBServer;" + 
                                "Initial Catalog=Database;" + 
                                "User id=UserId;" + 
                                "Password=Password;";

        using (SqlConnection connection = new SqlConnection(connString))
        {
            connection.Open();
            SqlCommand cmd = new SqlCommand(tmpTable, connection);
            cmd.ExecuteNonQuery();

            try
            {
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                {
                    bulkCopy.DestinationTableName = "#TempTable";
                    bulkCopy.WriteToServer(dataTable);

                    string mergeSql = "<Will eventually have merge statement here>";

                    cmd.CommandText = mergeSql;
                    int results = cmd.ExecuteNonQuery();

                    cmd.CommandText = "DROP TABLE #TempTable";
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception)
            {
                throw;
            }

            finally
            {
                SqlCommand final = new SqlCommand("DROP TABLE #TempTable", connection);
                final.ExecuteNonQuery();

            }

        }

Answer 1

The problem is a simple typo as @MarcGravell pointed out in a comment.

bulkCopy.DestinationTableName = "TempTable";

should be

bulkCopy.DestinationTableName = "#TempTable";

Temp tables have session lifetime if not created in a nested batch or stored procedure.

EG this works:

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

namespace sqlclienttest
{
    class Program
    {
        static void Main(string[] args)
        {
            string tmpTable = @"create table #TempTable 
                                (
                                [Column1] [varchar](50) NOT NULL,
                                [Column2] [varchar](50) NOT NULL,
                                [Column3] [varchar](50) NOT NULL
                                )";
            string connString = "Data Source=xxxx.database.windows.net;" +
                                    "Initial Catalog=Adventureworks;" +
                                    "User id=xxxxxx;" +
                                    "Password=xxxxxx;";

            var dataTable = new DataTable();
            dataTable.Columns.Add("Column1", typeof(string));
            dataTable.Columns.Add("Column2", typeof(string));
            dataTable.Columns.Add("Column3", typeof(string));

            dataTable.BeginLoadData();
            for (int i = 0; i < 10000; i++)
            {
                var r = dataTable.NewRow();
                r[0] = $"column1{i}";
                r[1] = $"column2{i}";
                r[2] = $"column3{i}";
                dataTable.Rows.Add(r);
            }
            dataTable.EndLoadData();

            using (SqlConnection connection = new SqlConnection(connString))
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand(tmpTable, connection);
                cmd.ExecuteNonQuery();

                try
                {
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                    {
                        bulkCopy.NotifyAfter = 1000;
                        bulkCopy.SqlRowsCopied += (s, a) => Console.WriteLine($"{a.RowsCopied} rows");
                        bulkCopy.DestinationTableName = "#TempTable";
                        bulkCopy.WriteToServer(dataTable);

                        //string mergeSql = "<Will eventually have merge statement here>";

                        //cmd.CommandText = mergeSql;
                        //int results = cmd.ExecuteNonQuery();

                        cmd.CommandText = "DROP TABLE #TempTable";
                        cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception)
                {
                    throw;
                }


            }
        }
    }
}

The only caviat is that if the client driver decides to wrap your CREATE TABLE statement in sp_executesql or somesuch, the temp table will have nested batch lifetime, not session lifetime. But System.Data.SqlClient doesn't do this unless you put parameters in batch that creates the temp table.

Answer 2

I'm checking in SSMS using the same credentials, getting the "Invalid object name '#TempTable'" error after the code completes

That's because SSMS is using a different connection, and temp-tables like #Foo are per-connection. You cannot access a #Foo temp-table from any other connection.

It sounds like you want a global temp-table. This is as simple as naming it ##Foo instead of #Foo. Global temp-tables are shared over all connections.