none
Creating and inserting value in #Temp table using ADO.NET 2.0 RRS feed

  • Question

  • Hi,

     

    I have a requirement to create #Temp table in Sybase database and insert values to it.

     

    I use following code:

     

    DbCommand dbCreateTable;

    dbCreateTable = provider.CreateCommand();

    dbCreateTable.Connection = conn;

    dbCreateTable.CommandText ="Create table #MyTemp (Id varchar(10))";

    dbCreateTable.ExecuteNonQuery();

    string[] insertValues = {"Insert into #Mytemp values ('TestString1')",

    "Insert into #Mytemp values ('TestString2')"};

     

    DbCommand dbInsertData = provider.CreateCommand();

    dbInsertData.Connection = conn;

    foreach (String insertStr in insertValues)

    {

    dbInsertData.CommandText = insertStr;

    dbInsertData.ExecuteNonQuery();

    }

     

    Code creates the Temp table but when it comes to insert statement, it throws error saying "Temp table not found".

    Reason can be Create and Insert statement gets executed as 2 different sessions.

     

    How to get the above requirement work fine?

     

    Thank you.

    HV

     

    Wednesday, January 23, 2008 8:55 AM

All replies

  • Make sure that database connection is not closed between calls and those different calls to database do not open different connections. Temp table survives only if connection is still alive and visible ONLY to that single connection (session). As soon as session ends (connection closed) temp tables dropped automatically. If you need to work with temp tables, you need to be sure that all SQL statements executed against same opened connection.

    Wednesday, January 23, 2008 11:24 AM
    Moderator