System.Data.SQLite

Hello,

I've installed the 1.0.32.0 SQLite from the binary MSI installer. I'm using VS2005 TeamEdition under Windows Server 2003 OS. I've created a simple compact framework application targeting Windows Mobile 5.0 devices. It consists of a single form on which a datagrid is placed. The datagrid is linked to a table from a typed DataSet. The DataSet itself has been automatically generated using the designer - from an SQLite database containing 5 relatively simple tables.

Initially everything worked fine, I've successfully compiled, deployed and run the application on a terget device.

Every time the form is opened in the designer I need to wait about 2-3 minutes under heavy disk activity. Further, almost any change to the form - adding additional components, removing, changing properties, etc. leads to the same long waiting for the operation to complete. I found out that under my local user's Local Settings\Application Data\Microsoft\VisualStudio\8.0\ProjectAssemblies a series of directories are being created everytime something is changed in the form (or the form is being loaded into designer). Currently, during open almost 200 directories are being created. 99% ot those contain the single file System.Data.SQLite.dll (395K) - looks like the CF2 version of the DLL, not the one residing in the GAC.

I've checked the GAC - it contains only two SQLite libraries - the SQLite.Designer (1.0.31.0) and System.Data.SQLite (1.0.32.0). I've run the VS2005 setup and reinstalled it, then reinstalled the SQLite msi - nothing changed.

 I've done nothing special in the test application project. However, I tried creating a new project from scratch following the same procedure - adding my database connection, the datasource, the datagrid. I get the same results - constantly increasing number of temporary System.Data.SQLite assemblies being created under ProjectAssemblies.

Any help, wouls be appreciated

Interesting.  I'll have to try and duplicate this on my system.  Designing for a CF project was a nightmare to get working, and required many hours of support e-mails back and forth with Microsoft before I finally stumbled on the solution myself.  It may be that what you're experiencing is yet one more artifact from this. 

As for the GAC ... You can't put the CF binary in the GAC because it targets an ARM processor.  During design-time, VS2005 uses the desktop binary.  It compiles your typed datasets and things in the background and its this process that's probably generating all the bogus binaries.

Although the CF project references the CF version of the SQLite DLL, it's not until deployment that the CF version is actually used.

Robert

 

Thanks, for responding so quickly.

I've tried to google references to similar issues. Indeed there seems to be some problem related to VS2005. One of the reported cases suggested for a workaround to disable the "AutoToolboxPopulate" feature: Tools \ Options \ Windows Forms Designer, then under the "Toolbox" category, set the AutoToolboxPopulate" property to False. Close VS IDE, and reopen the solution.

Unfortunately this has no effect what so ever, at least not for me.

I'm going to try a clean windows XP setup with clean VS2005 (this time Pro), and see if the problem will reappear. If not, I'll just plan a reinstallation of my production workstation.

Marin

I'm using VS2005 Team Edition for Developers (MSDN Premium subscription) so that's what I'll be diagnosing the bug on.  I'm sure it will apply across the board.

Robert

 

Any news on this yet? I have the same problem. Tried it on two different comps, with the same result. Unfortunatly, this makes it impossible for me to use SQLite... And i really want to use it =).

 

//Regards, Torbjorn

Yes, I have made some progress on this.  Running Visual Studio in a debugger reveals its generating thousands of type load exceptions trying to load the CF version of the DLL on the desktop.  I'm trying to figure out how to force VS to load the desktop version for the purposes of compiling the typed dataset on the fly.

Robert

 

This is fixed in the 1.0.34.0 release coming up this weekend.  Thanks for the report!  I had seen VS's slowness pulling up a Compact Framework form before with a typed dataset, but I had not attributed it to SQLite.  Technically it wasn't really SQLite, but I've found a workaround and that's all that matters :)

Robert

 

That's a very good news Robert, cannot wait to try it out.

Keep up the good work, lots of people do appreciate it!
Marin

Has anyone ever seen any MS documentation on implementing a CF ADO.NET 2.0 provider and supporting the VS design-time environment? 

Me neither.

You wouldn't believe what I had to do to track this down.  When a typed dataset exists in your VS project, VS will compile it in the background when it thinks it needs it.  For example, when you open a form with a DataGrid on it.

To compile it, VS will copy over the SQLite library to a temp folder and run the compile against it.  In this case, the SQLite library it copies is the CF library which of course will not run on the desktop.  As a matter of fact, trying to load any DLL/exe marked as retargetable on the desktop will result in a TypeLoadException.

However, you CAN place retargetable assemblies in the GAC and if a better match for the platform exists in the GAC, the binding will redirect to the closer match.

You also can't turn off the retargetable flag in the CF assembly -- if you do then typed datasets break completely in CF projects.

So what was the answer?  You'll love this ...

For the redistributable mixed CF DLL, I had to turn off the Retargetable flag in the assembly.  Then I had to build another pure MSIL assembly without the interop library and WITH the Retargetable flag to place that DLL in the GAC as a placeholder.

So what happens is this:

1. VS Copies the redist CF DLL to the temp folder.  It's not marked Retargetable, so the metadata can be loaded.
2. During the assembly loading phase, the GAC is consulted and an entry is found for this assembly, so binding is redirected from the disk DLL to the GAC entry.
3. The GAC entry is marked as retargetable, so assembly binding is redirected yet again to the x86 GAC entry which is the desktop DLL.
4. The typed dataset is now compiled using the desktop DLL.

You know, if it gets any hairier, I'm going to need a sturdier pair of shears.

 

Great news (the fix that is, not the problem =) )!

Thanks a lot for your effort on this, it's _really_ appreciated.

//Regards, Torbjorn

Hi! It seems that this problem is occurring again... In my ProjectAssemblies folder I have currently 1008 subfolders, each containing a System.Data.SQLite dll file. Does anyone know how to fix this? Hmm... seems that the SQLite version in ProjectAssemblies is 1.0.46.0. The version I currently have INSTALLED is 1.0.47.2. Weird... I have tried to uninstall and re-install SQLite, but no change... Regards Carl

Oops. It turns out I was referring another library (of my own), and that lib in turn used the older SQLite version. I have now corrected this to use the latest version, and it works a lot better! Great! Sorry to cause a stir... Carl

I'm using the latest version 1.0.48.0 and this is the only version of sqlite on my computer. I still have thousands of folders below \ProjectAssemblies with only one System.Data.SQLite.DLL in it.

Is there a way to get rid of them? Beside normal folder deletion...

Thanks

Andreas

 

I'm relpying to my own post.

I found that the problem arises, when the CF2 Project has a reference to the sqlite CF library and a form is opened in the form designer.

The solution I use is the following: I made a separate library (dll) for the access classes that use the sqlite dll. This library is NOT referenced in the project. Neither is NO using statement used for my dll. Instead I use an Interface for this dll. When I need access to my dll, I use the following code:

      Assembly ass = Assembly.Load("MySqliteLibrary");
      db = (IDatabase)ass.CreateInstance("data.db.SQLiteLibrary");

Have fun

Andreas