开发者

Temporary tables how to create before transaction and then use inside transaction

I write code in c#.

I want to create temporary table before transaction begins and then use it within transaction, however when I try to do it I get error within transaction it estates that "Table does not exist". What is the proper way of doing it ?

SqlConnection sqlConnection = new SqlConnection( "connstring" );
sqlConnection.Open();

string temp = string.Format( "CREATE TABLE dbo.#temp (id INT);" );
DbCommand command = database.GetSqlStringCommand( temp );
database.ExecuteNonQuery( command ); //here is the problem when I add argument , transaction it works
//fill data in temporary table
//...
// open transaction
SqlT开发者_开发知识库ransaction transaction = sqlConnection.BeginTransaction();

//Here I try to read from temp table I have some DbCommand readCommand
database.ExecuteNonQuery( readCommand, transaction ); 


How about just re-ordering the statements as such, so that the table creation takes place inside the transaction:

SqlConnection sqlConnection = new SqlConnection( "connstring" );
sqlConnection.Open();

// open transaction
SqlTransaction transaction = sqlConnection.BeginTransaction();

string temp = string.Format( "CREATE TABLE dbo.#temp (id INT);" );
DbCommand command = database.GetSqlStringCommand( temp );
database.ExecuteNonQuery( command, transaction  ); //here is the problem when I add argument , transaction it works

//Here I try to read from temp table I have some DbCommand readCommand
database.ExecuteNonQuery( readCommand, transaction ); 


The following code work perfectly.

    static void Main(string[] args)
    {
        string conStr = "Integrated Security=true;Initial Catalog=sushma;server=(local)";

        SqlConnection sqlConnection = new SqlConnection(conStr);
        sqlConnection.Open();

        SqlCommand DbCommand = new SqlCommand("CREATE TABLE dbo.#temp (id INT);", sqlConnection);
        DbCommand.ExecuteNonQuery();

        SqlTransaction transaction = sqlConnection.BeginTransaction();
        DbCommand.CommandText = "SELECT * FROM dbo.#temp";
        DbCommand.Transaction = transaction;
        SqlDataReader dr = DbCommand.ExecuteReader();
        dr.Close();            

        transaction.Commit();
        Console.WriteLine("what is the issue");
        Console.ReadKey();
    }
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜