Create a FULLTEXT INDEX with PetaPoco

Question

Im trying to create a fulltext index with petapoco in my sql database. My programming language is C# Here is a small snippet of the code i'm try to execute: Small note: the method this.GetFullTextIndexViewCommand(keyDataField, databaseViewName, formDefinition.FullTextSearch.DataEntity, fields); Is rather long, it generates the command for creating the View based on the given fields.

using (var db = new PetaPoco.Database(this.connectionStringName))
     {
        var keyDataField = formDefinition.Fields.Single(f => f.FormField == formDefinition.KeyField).DataField;
        var fields = formDefinition.Fields;
        var form = formDefinition.Form;
        var databaseViewName = string.Concat(FullTextIndexViewPrefix, form);
        var fullTextIndexCommand = $"CREATE FULLTEXT INDEX ON [{this.Schema}].{databaseViewName}(SearchText) KEY INDEX IX_{databaseViewName} ON {FullTextCatalogName}";
        var fullTextIndexViewCommand = this.GetFullTextIndexViewCommand(keyDataField, databaseViewName, formDefinition.FullTextSearch.DataEntity, fields);
        var uniqueIndexCommand = $"CREATE UNIQUE CLUSTERED INDEX IX_{databaseViewName} ON [{this.Schema}].{databaseViewName}({keyDataField})";

        db.Execute(fullTextIndexViewCommand);
        db.Execute(uniqueIndexCommand);
        db.Execute(fullTextIndexCommand);
     }

I can confirm that after these statements, the FullTextIndexViewCommand is successfully executed. The uniqueIndexCommand is successfully executed. But the FullTextIndexCommand gives me the following error (i recreated the error in SQL management studio hence the extra line, but the error is the same):

Msg 574, Level 16, State 0, Line 2
CREATE FULLTEXT INDEX statement cannot be used inside a user transaction.

I have tried to do a couple of things:

• I read about the reason of this error and i totally get that.
• Put the db.Execute(fullTextIndexCommand) inside it's own using statement. No luck
• Verified that no other logic is creating a transaction.
• If i watch the db variable i see: _transaction: null and _transactionCancelled: false
• I read also a little about the transaction scope, but i think i don't need that.

What i want to do:

I want to create the FULLTEXT INDEX without a transaction.

Kind regards

Answer 1

After a day and a halve i finally found the answer. The problem was that we are using ASP.Net Boilerplate. ABP uses Unit Of Work

ASP.NET Boilerplate opens a database connection (it may not be opened immediately, but opened in first database usage, based on ORM provider implementation) and begins a transaction when entering a unit of work method.

I basicly had to disable the use of transactions in this method for this to work.