Programmatically using C # display a table of SQL Server 2016

Question

The part of my program that I am writing is running some T-SQL code. The program will run in a loop until I stop it. When it passes for the first time, there are no problems. When it runs a second time, I get an error when it tries to run T-SQL code. It says that one of the temporary tables that the code creates already exists in the database. The code before it tries to insert records into the temp table disables the temporary table. I do not know if I configured it correctly in the T-SQL code or just failed to run the T-SQL code that deletes the table if it exists. I also tried resetting the table in C # code, here is what I tried:

cn.Open();
string cmdText = @"BEGIN TRANSACTION; DROP TABLE IF EXISTS #temp850; COMMIT TRANSACTION;";                
SqlCommand command = new SqlCommand(cmdText, cn);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
cn.Close();

and here an error message appears:

There is already an object named '#temp850' in the database.

Can anyone help?

Answer 1

# SQL, .

private static void ExecuteSqlTransaction(string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        SqlCommand command = connection.CreateCommand();

        // Start a local transaction.
        SqlTransaction transaction = connection.BeginTransaction("SampleTransaction");

        // Must assign both transaction object and connection
        // to Command object for a pending local transaction
        command.Connection = connection;
        command.Transaction = transaction;

        try
        {
            command.CommandText =
                "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
            command.ExecuteNonQuery();
            command.CommandText =
                "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
            command.ExecuteNonQuery();

            // Attempt to commit the transaction.
            transaction.Commit();
            Console.WriteLine("Both records are written to database.");
        }
        catch (Exception ex)
        {
            Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
            Console.WriteLine("  Message: {0}", ex.Message);

            // Attempt to roll back the transaction.
            try
            {
                transaction.Rollback();
            }
            catch (Exception ex2)
            {
                // This catch block will handle any errors that may have occurred
                // on the server that would cause the rollback to fail, such as
                // a closed connection.
                Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
                Console.WriteLine("  Message: {0}", ex2.Message);
            }
        }
    }
}

Answer 2

SQL,

IF OBJECT_ID('tempdb..#temp850') IS NOT NULL DROP TABLE #temp850
GO

SELECT 1 col1 INTO #temp850

Answer 3

In sql, you cannot create the same temp table more than once in a single query.

You can delete table # temp85 every time before creating it:

Change this line:

string cmdText = @"BEGIN TRANSACTION; DROP TABLE IF EXISTS #temp850; COMMIT TRANSACTION;";       

** I think this is not a way to delete temporary tables

For this:

string cmdText = @"BEGIN TRANSACTION; if (OBJECT_ID('tempdb..#temp850')>0) drop table #temp850; COMMIT TRANSACTION;";