Visual-studio – Why is the SQLite provider missing from the Server Explorer Add Connection dialogue

I didn't look very well, there's another property added to the project files:

<RestorePackages>true</RestorePackages>

Just have to remove this as well as all these lines manually from all *.csproj files:

  <Import Project="$(SolutionDir)\.nuget\nuget.targets" />

UPDATE:

Turns out it's a persistent little bugger, if you're manually editing your project files, make sure to close the solution and delete all the lines from the project at once, otherwise they're just added again once the project reloads...

UPDATE2:

Delete the .nuget folder from the solution root too

UPDATE3:

A later version of NuGet adds another section that you need to remove:

 <Target Name="EnsureNuGetPackageBuildImports" BeforeTargets="PrepareForBuild">
    <PropertyGroup>
      <ErrorText>This project references NuGet package(s) that are missing on this computer. Enable NuGet Package Restore to download them.  For more information, see http://go.microsoft.com/fwlink/?LinkID=322105. The missing file is {0}.</ErrorText>
    </PropertyGroup>
    <Error Condition="!Exists('$(SolutionDir)\.nuget\NuGet.targets')" Text="$([System.String]::Format('$(ErrorText)', '$(SolutionDir)\.nuget\NuGet.targets'))" />
  </Target>

Update4

Inside the NuGet.Targets located in the .nuget folder, there is another section that gets added to new projects... switch it to false.

<!-- We need to ensure packages are restored prior to assembly resolve -->
<BuildDependsOn Condition="$(RestorePackages) == 'false'">
    RestorePackages;
    $(BuildDependsOn);
</BuildDependsOn>

I had the same problem using the datasets/tableadapters generated with the designer shipped with System.Data.Sqlite.dll version 1.0.82.0 -- after closing the connection we were unable to read the database file using System.IO.FileStream. I was disposing correctly both connection and tableadapters and I was not using connection pooling.

According to my first searches (for example this and this thread) that seemed a problem in the library itself -- either objects not correctly released and/or pooling issues (which I don't use).

After reading your question I tried to replicate the problem using only SQLiteCommand objects and I found that the problem arises when you don't dispose them. Update 2012-11-27 19:37 UTC: this is further confirmed by this ticket for System.Data.SQLite, in which a developer explains that "all SQLiteCommand and SQLiteDataReader objects associated with the connection [should be] properly disposed".

I then turned back on the generated TableAdapters and I saw that there was no implementation of the Dispose method -- so in fact the created commands were not disposed. I implemented it, taking care of disposing all the commands, and I have got no problem.

Here's the code in C#, hope this helps. Please note that the code is converted from the original in Visual Basic, so expect some conversion errors.

//In Table Adapter    
protected override void Dispose(bool disposing)
{
   base.Dispose(disposing);

    Common.DisposeTableAdapter(disposing, _adapter, _commandCollection);
}

public static class Common
{
    /// <summary>
    /// Disposes a TableAdapter generated by SQLite Designer
    /// </summary>
    /// <param name="disposing"></param>
    /// <param name="adapter"></param>
    /// <param name="commandCollection"></param>
    /// <remarks>You must dispose all the command,
    /// otherwise the file remains locked and cannot be accessed
    /// (for example, for reading or deletion)</remarks>
    public static void DisposeTableAdapter(
        bool disposing,
        System.Data.SQLite.SQLiteDataAdapter adapter,
        IEnumerable<System.Data.SQLite.SQLiteCommand> commandCollection)
    {
        if (disposing) {
            DisposeSQLiteTableAdapter(adapter);

            foreach (object currentCommand_loopVariable in commandCollection)
            {
                currentCommand = currentCommand_loopVariable;
                currentCommand.Dispose();
            }
        }
    }

    public static void DisposeSQLiteTableAdapter(
            System.Data.SQLite.SQLiteDataAdapter adapter)
    {
        if (adapter != null) {
            DisposeSQLiteTableAdapterCommands(adapter);

            adapter.Dispose();
        }
    }

    public static void DisposeSQLiteTableAdapterCommands(
            System.Data.SQLite.SQLiteDataAdapter adapter)
    {
        foreach (object currentCommand_loopVariable in {
            adapter.UpdateCommand,
            adapter.InsertCommand,
            adapter.DeleteCommand,
            adapter.SelectCommand})
        {
            currentCommand = currentCommand_loopVariable;
            if (currentCommand != null) {
                currentCommand.Dispose();
            }
        }
    }
}

Update 2013-07-05 17:36 UTC gorogm's answer highlights two important things:

• according to the changelog on the official site of System.Data.SQLite, starting from version 1.0.84.0 the above code should not be needed, since the library takes care of this. I haven't tested this, but in the worst case you only need this snippet:

  //In Table Adapter    
  protected override void Dispose(bool disposing)
  {
    base.Dispose(disposing);
  
    this.Adapter.Dispose();
  }
  

• about the implementation of the Dispose call of the TableAdapter: it is is better to put this in a partial class, so that a dataset regeneration does not affected this code (and any additional code you may need to add).