using GetOleDbSchemaTable in a Transaction RRS feed

  • Question

  • is it possible to begin an OleDbTransaction and before commiting it reading the db schema (with GetOleDbSchemaTable) without being locked due to a pending "Create Table" which lock the db schema?

    the step i tried are:
    1 - begin transaction (isolation level: readcommited)
    2 - execute "Create Table ..." statement
    3 - try to read the schema table but it is locked
    4 - after that i want to commit the transaction

    in GetOleDbSchemaTable i did not find anything related to Transactions.

    is there any suggestion.

    Tuesday, March 3, 2009 2:19 PM

All replies

  • Why would you need to call CREATE TABLE inside of transaction? You have single statement and it (CREATE TABLE) is always transactional by itself. You will not be able to create partial table. Unless you did not describe full scenario I would not start transaction at all as there is no need for it.

    Val Mazur (MVP)
    Wednesday, March 4, 2009 10:54 AM
  • the CREATE TABLE statement is only an example. i have to modify the database (creating or altering tables, inserting data, etc...), then doing other things like inserting foreign keys or similar and in order to do that i have to read the schema table. i would like to have all of these operations to be executed in a single transaction.

    however i maybe found a solution. it seems that if the db connection used by GetOleDbSchemaTable is the same of the OleDbTransaction it is capable to read the uncommited tables. can it be correct?
    Wednesday, March 4, 2009 11:03 AM