Insight.Database - using Insert with OpenWithTransaction

Question

First question ever :)

I'm a newbie at using the Insight.Database library but I think I'm using this in the right way. I'm running the (simplified) code below from a XUnit Test in another project in the same solution. The exception is thrown on the line with the connTran.Insert, and if I break on the logging function in the CATCH block and look at the message in the exception, it gives the error error CS7069: Reference to type 'DbConnectionWrapper' claims it is defined in 'Insight.Database', but it could not be found, but then the debugger will also break on the connTran.Rollback() line with A transaction has not been created for this connection.

What's weird is that I have used the same code in another Test in the same solution and test project but with a flat entity and it operated fine.

I'm using Visual Studio 2015 Enterprise. The debugger is also not behaving properly - the hovering over variables, etc doesn't work while "inside" the transaction. I've found a very similar github issue on Github here but no resolution that I can use.

This is the Insert code I am using - I have also tried connTran.Insert but I get the same result.

DbConnectionWrapper connTran = null;
try
{
   using (connTran = _dbconnection.OpenWithTransaction())
   {
   var lookupHeader = connTran.QueryResults<Results>("usp_Lookup_InsertHeader", entity);
   connTran.Commit();
   }
}
catch(Exception ex)
{
   logException(ex.Message);       
   connTran.Rollback();
   throw;
}

The entity object looks like this:

public class LookupEntity
    {
        [RecordId]
        public int LookupHeaderId { get; set; }
        public string LookupHeaderName { get; set; }
        public string LookupHeaderDescription { get; set; }
        public string LookupHeaderCategory { get; set; }
        public string LookupHeaderType { get; set; }
        public string LookupBPMObjectName { get; set; }
        public string LookupBPMMethodName { get; set; }
        public string LookupBPMInputParams { get; set; }
        public string LookupBPMExtraParams { get; set; }
        public string LookupBPMOutputDataSetName { get; set; }
        public string LookupBPMOutputNameNode { get; set; }
        public string LookupBPMOutputValueNode { get; set; }
        public string LookupBPMOutputActiveNode { get; set; }
        public int Active { get; set; }
        public int Cache { get; set; }
        public int CsysLastUpdateBy { get; set; }
        public DateTime? CsysLastUpdateDate { get; set; }
        public int CsysInsertBy { get; set; }
        public DateTime? CsysInsertDate { get; set; }
        public string CsysTimeStamp { get; set; }
        public string CsysTag { get; set; }
        public int CsysOwnerId { get; set; }
        public string CsysOwnerType { get; set; }
        public int CsysRecordStatus { get; set; }

        [ChildRecords]
        public List<LookupDetail> LookupDetails { get; set; }
}

Answer 1

Well...a bit more messing around and poking through the Insight source code, I got past my original issue, and encountered a few more. I'm going to post my findings here in case the Insight author has a look.

The answer to my original issue was to restructure my TRY..CATCH to be inside the USING - this may have been obvious, maybe it wasn't but now I know :) So my code turned into this:

using (var connTran = _dbconnection.OpenWithTransaction())
{
   try
   {
       connTran.Insert("usp_Lookup_InsertHeader", entity, new { lookupHeader = entity });
       connTran.Commit();
   }
   catch(Exception ex)
   {
      logException(ex.Message);       
      connTran.Rollback();
      throw;
   } 
}

Note I could also get rid of declaring the connTran variable outside of the using.

Since I'm using SQL 2008, I was keen to use the Table Value Parameters with the Insert stored procedure. This gave me my next head-scratcher - which may be related to out-of-date documentation.

The doco states that code like this:

connTran.Insert("usp_Lookup_InsertHeader", entity);

would insert the record and then map the new identity value back into the entity, assuming the returned id field and entity property names match up (which they do). The Insert stored procedure has a signature like this:

CREATE PROCEDURE [dbo].[usp_Lookup_InsertHeader] 
    @lookupHeader [lookupHeaderType] READONLY

Insight kept complaining that the "lookupHeader" parameter was not defined, so I eventually stumbled across something elsewhere in the doco that turned my code into this:

connTran.Insert("usp_Lookup_InsertHeader", entity, new { lookupHeader = entity });

Now Insight is happy :)

The third issue then became datetime values.

The CsysLastUpdateDate property in the entity was defined as DateTime?. In the SQL Type for the TVP, the CsysLastUpdateDate field was defined as DateTime. In my Test, I set the CsysLastUpdateDate to DateTime.Now

Watching SQL Profiler, I found that the SQL text Insight was posting included the milliseconds in what is now a string representation of the datetime value. There is a sample of this text below with the string datetime - '2016-06-16 18:03:32.5510000'.

declare @p1 dbo.lookupHeaderType
insert into @p1 values(0,N'timbo.test',N'',N'',N'',N'',N'',N'',N'',N'',N'',N'',N'',1,1,2,'2016-06-16 18:03:32.5510000',2,'2016-06-16 18:03:32.5510000',N'',N'',1,N'cSysSite',1)

When SQL tried to execute that text to create the TVP, it errored with a datetime conversion issue. If I manually edited the milliseconds out of the datetime string and executed the text, the TVP created properly.

With some more playing around I discovered that declaring the CsysLastUpdateDate field in the Type as a DateTime2 field, the SQL that Insight was sending executed aok and the Insert worked happily.

I'm not sure if I've found bugs or these are just newbie learnings, but I hope this helps the next person :)