Bug fix request.

[snowcat]

Can you fix the "Insert helper make db file lock bug" in FAQ link? 
Write SQL is annoying for a class that is testing and changing several times a day. And my db need to be recreated using Initialize(true). 
And there is NO way to get Inserted record's Row ID with Execute.
Please !!! Fix that bug. Please!!!

Update: 
After checked source code I found Insert is using different way what update do. Variable insertCmd keep a connection's duplication and not disposed. Can you change the Insert function structure as Update do.  

Update 2:
I changed SimpleSQL.SQLiteConnection.Insert (object obj, string extra, Type objType, out long rowID)
code to temporary fix this with lower performance when using a lot insert at same time.

public int Insert (object obj, string extra, Type objType, out long rowID)

{

           rowID = -1;



if (obj == null || objType == null) {

return 0;

}



var map = GetMapping (objType);



var cols = map.InsertColumns;

var vals = new object[cols.Length];

for (var i = 0; i < vals.Length; i++) {

vals [i] = cols [i].GetValue (obj);

}



//var insertCmd = map.GetInsertCommand (this, extra);

//SQLite3.Result result;

//string errorMessage;

//var count = insertCmd.ExecuteNonQuery (out result, out errorMessage, vals);



//if (result != SQLite3.Result.Done)

//{

//    throw SQLiteException.New(result, errorMessage);

//}



var insertSql = map.InsertSql( extra );

var count = Execute( insertSql, vals.ToArray() );



if( map.HasAutoIncPK )

{

rowID = SQLite3.LastInsertRowid( Handle );

map.SetAutoIncPK( obj, rowID );

}



return count;

}



[echo17]

Thank you for your code update, that is helpful. I have never been able to get the locking error, so it was difficult for me to know what changes would be useful. Knowing that you were able to get the error and your code fixed it helps tremendously.

I'll get this into the next service release. Thanks!

[snowcat]

The lock is from map.GetInsertCommand: new PreparedSqlLiteInsertCommand (conn);
It duplicates the SQLiteConnection and keep it as cache. If you still want this design, you need dispose all cached connections when db Close and Dispose.

public PreparedSqlLiteInsertCommand GetInsertCommand (SQLiteConnection conn, string extra)

{

if (_insertCommand == null || _insertCommandExtra != extra) {

var insertSql = InsertSql (extra);

_insertCommand = new PreparedSqlLiteInsertCommand (conn); <<<<<<< ------- duplicate connection

_insertCommand.CommandText = insertSql;

_insertCommandExtra = extra;

}

return _insertCommand;

}



internal PreparedSqlLiteInsertCommand (SQLiteConnection conn)

{

Connection = conn;          <<<<<<< ------- duplicate connection

}