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();
}
精彩评论