CODEWITHSUNDEEP
c#sqlssmssqlcommandexecutenonquery
Mwspencer
Mwspencer

Reputation: 1183

Creating #temp table using SqlCommand.ExecuteNonQuery()

Hello I am trying to create a #temp table using SqlCommand.ExecuteNonQuery. When I execute the non query no error occurs, but also no table is created. If I try creating a normal table it works fine. How do I create a #temp table using C#'s SqlCommand Class.

Query that works: 

    string createTempTable = $@"CREATE TABLE [dbo].[Holdings](
                                [Fund] [varchar](25) NULL,
                                [PercentFund] [float] NULL,
                                [CommittedCapital] [float] NULL,
                                [DryPowder] [float] NULL,
                                [SDCashBalance] [float] NULL,
                                [PendingTrades] [float] NULL,
                                [PendingCapital] [float] NULL,
                                [MgmtFee] [float] NULL,
                                [AdjustedCash] [float] NULL,
                                [AsOfDate] [datetime] NULL
                            );";
    //SqlCommand cmd = new SqlCommand(createTempTable, conn);
    //cmd.ExecuteNonQuery();

    SQL.NonQuery(createTempTable, dbName, serverName, false, 300);


public static void NonQuery(string query,
                            string databaseName = "MyDataBase",
                            string serverAddress = "MYServerAddress",
                            bool useServiceAcct = false,
                            int commandTimeout = 30)
{
    string connString = GetConnectionString(serverAddress, databaseName, useServiceAcct);
    using (SqlConnection sqlConn = new SqlConnection(connString))
    using (SqlCommand cmd = new SqlCommand(query, sqlConn))
    {
        sqlConn.Open();
        cmd.CommandTimeout = commandTimeout;
        cmd.ExecuteNonQuery();
    }
}

Query that Doesn't seem to work, but does not give an error:

string createTempTable = $@"CREATE TABLE [dbo].[#Holdings](
                            [Fund] [varchar](25) NULL,
                            [PercentFund] [float] NULL,
                            [CommittedCapital] [float] NULL,
                            [DryPowder] [float] NULL,
                            [SDCashBalance] [float] NULL,
                            [PendingTrades] [float] NULL,
                            [PendingCapital] [float] NULL,
                            [MgmtFee] [float] NULL,
                            [AdjustedCash] [float] NULL,
                            [AsOfDate] [datetime] NULL
                        );";
//SqlCommand cmd = new SqlCommand(createTempTable, conn);
//cmd.ExecuteNonQuery();

SQL.NonQuery(createTempTable, dbName, serverName, false, 300);

Upvotes: 0

Views: 676

Answers (1)

sfuqua
sfuqua

Reputation: 5853

As @jonesopolis points out in a comment, the table disappears when you close the connection. If you are trying to create a temp table that will be available to another connection, then you could use a global temp with double hashmarks: ##Holdings. Good discussion on the two: Local vs Global SQL Server Temporary Tables.

Upvotes: 2

Related Questions