peterhuene / sqlite-net-wp8

A C++/CX wrapper for SQLite functions that sqlite-net depends on. Can be used as an alternative to csharp-sqlite on Windows Phone 8. This library is released under the MIT license.

Call for contributors

I no longer have a Windows Phone device or the time to properly maintain this project. Therefore, I'm asking for someone to take over this project as either the official fork or I can add that person as a contributor to this repository. Please contact me at <mygithubusername>@gmail.com. Thanks!

sqlite-net-wp8

A C++/CX wrapper for SQLite functions that sqlite-net depends on.

This library can be used to directly call into sqlite3.dll on Windows Phone 8 from sqlite-net.

Requirements

• Visual Studio 2012 or later
• Windows Phone 8.0 SDK
• SQLite for Windows Phone 8 extension
• sqlite-net source files

Using sqlite-net-wp8

• Install the sqlite-net-wp8 nuget package into your WP8 project:
  • Select Project -> Manage NuGet Packages...
  • Select Online -> nuget.org
  • Search for sqlite-net-wp8 and install the package.
• Add a reference to the "SQLite for Windows Phone" extension.
  • Select Project -> Add Reference...
  • Select Windows Phone -> Extensions
  • Check "SQLite for Windows Phone" and click OK
• Copy the sqlite-net source files (src/SQLite.cs and src/SQLiteAsync.cs) into your WP8 project.
• Add "USE_WP8_NATIVE_SQLITE" as a conditional build symbol to your project containing the sqlite-net source files (without this, SQLite will use csharp-sqlite):
  • Project -> Properties
  • Click on the Build tab.
  • In the "Configuration" dropdown, select "All configurations".
  • In the "Platform" dropdown, select "All platforms".
  • Append ";USE_WP8_NATIVE_SQLITE" to the "Conditional compilation symbols" textbox.
• Use sqlite-net as you normally would.

Setting the SQLite Temporary Directory

If you are executing SQLite commands that require the temporary directory to be set, call Sqlite3.set_temp_directory before opening your database with sqlite-net:

Sqlite3.set_temp_directory(Windows.Storage.ApplicationData.Current.LocalFolder.Path);

I will open a pull request to sqlite-net to automatically do this when opening a database, but for now you can call this method directly.

sqlite-net-wp8's Issues

SQLite Upgrade to 3.8.1

Peter,

Appreciate your effort to keep this library updated.

Microsoft just update the SQLite version in their Visual Studio.(Express)
for WindowsPhone8.

Thanks.

Sqlite return stmt problems

Hi, i ended up with a problem again, this time inside the SQLite.cs file, looks like the return stmt is causing the problem, i can show you the picture of the problem here: http://shrani.si/f/1y/5d/1pwmFsIw/problem2.jpg

another thing:
I followed a guide where thers code in the app.xaml.cs, the code seems to be copying the sqlite database file from the instalation folder to the local folder. (Is this necessary for the application to run properly?). In any case i get the following error: http://shrani.si/f/3j/gj/4qw0dh1/problem1.jpg

Thanks for helping.

New Sqlite Version

I just noticed that there is a new Sqlite version, 3.8.4.2. Could you provide an updated NuGet package?

Broken support for SQLite 3.7.16.1

Hello,

I've just upgraded the SQLite plugin for VS to version 3.7.16.1 and the sqlite-net-wp8 project isn't working anymore.

I've removed the project from my solution and tried to re-add it but i'm getting an error

"Unable to read the project file ... the imported project "...SQLite.WP80\3.7.16\DesignTime\CommonConfiguration\Neutral\SQLite.WP80.props" was not found. Confirm that the path in the declaration is correct..."

Is there anyway to download the old version of the sqlite plugin until this is fixed?

Support SQLite 3.8.6

Imported projects SQLite.WP80.props not found

Dear Peter,

Thanks for your work on this project. When I try to import Sqlite.vcxproj into my solution I get:

D:\Projects\MvvmCross\sqlite-net-wp8\Sqlite.vcxproj(56,5): The imported project "C:\Program Files (x86)\Microsoft SDKs\Windows Phone\v8.0\ExtensionSDKs\SQLite.WP80\3.8.0\DesignTime\CommonConfiguration\Neutral\SQLite.WP80.props" was not found. Confirm that the path in the declaration is correct, and that the file exists on disk.

I tried uninstalling my SQLite Phone vsix and reinstalling the latest version to no avail. Oh... but wait, I think the latest is 3.8.x not .0. Perhaps that is the issue?

Foreign Keys & "ON DELETE CASCADE"

I wanted to know if Foreign Keys & "ON DELETE CASCADE" functionality is available.
http://sqlite.org/foreignkeys.html

I see commits related to updating to SQLite 3.8 so I think that it would be available.
Just wanted to confirm though. Wanted to know if the error is in my code.

Just to give some reference. I am doing "DELETE FROM tablename" without any "WHERE" property and have tables referencing the primary key column of this table but the other tables retain their rows.
And I have done the following:
dbConn.Execute("PRAGMA foreign_keys=ON");

Have you experienced any memory related problems?

I am currently looking into the cause of a memory leak in one application that I had make compatible for wp8.

I have tested sqlite-net-wp8 from nuget in one of the applications and adding for example 10.000 insert statements in a transaction on button press increases the memory usage without releasing it anytime soon.

My example consisted in a button to run the transaction every time it is pressed and 2 labels that were updated with the current memory usage and peek memory usage.

sadly I don't have any code now because I lost the solution to a computer failure, but if needed, the code can be easily reproduced.

my question remains if you or anybody else experienced any memory related issues while using sqlite-net and/or sqlite-net-wp8?

thank you!

Update README for sqlite-net reference

Now that the changes to sqlite-net have been merged upstream, update the README to remove references to the custom fork of sqlite-net.

Test with the latest out of upstream too.

database update issue

Any idea how to update my local SQLite database ?

The project cannot be loaded after the update of SQLite.WP80 to version 3.7.17

Now if I try to import the existing project, a popup error appear: " Unable to read the project file Sqlite.vcxproj .... The imported project "C:\Program Files (x86)\Microsoft SDKs\Windows Phone\v.8.0\ExtensionSDKs\SQLite.WP80\3.7.16.2\DesignTime\CommonConfiguration\Neutral\SQLite.WPLite.WP80.props was not found. COnfirm that the path in the declaration is correct, and that the file exists on disk".
In fact now the extension is in an other directory (C:\Program Files (x86)\Microsoft SDKs\Windows Phone\v8.0\ExtensionSDKs\SQLite.WP80\3.7.17).
How can I solve the problem if I could't open the project?

Can't Open In VS2015

This Project Can't open in VS2015. I got a error.

'SQLite.SQLite3' does not contain a definition for 'EnableLoadExtension'

Using VS2012 and the instruction of using sqlite-net-wp8 there is an error:
'SQLite.SQLite3' does not contain a definition for 'EnableLoadExtension'.

Requested Windows Runtime type 'Sqlite.Sqlite3'

I publish windows phone 8 app to store, and a lot of users not have trouble with him, but some time i get this error and i don't know why.

System.TypeLoadException: Requested Windows Runtime type 'Sqlite.Sqlite3' is not registered. ---> System.Runtime.InteropServices.COMException: Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG))
--- End of inner exception stack trace ---
at System.StubHelpers.StubHelpers.GetWinRTFactoryObject(IntPtr pCPCMD)
at Sqlite.Sqlite3.sqlite3_open_v2(String filename, Database& db, Int32 flags, String zVfs)
at SQLite.SQLiteConnection..ctor(String databasePath, SQLiteOpenFlags openFlags, Boolean storeDateTimeAsTicks)
at SQLite.SQLiteConnectionWithLock..ctor(SQLiteConnectionString connectionString)
at SQLite.SQLiteConnectionPool.Entry..ctor(SQLiteConnectionString connectionString)
at SQLite.SQLiteConnectionPool.GetConnection(SQLiteConnectionString connectionString)
at SQLite.SQLiteAsyncConnection.TableT
at CraigsList.DataAccess.Providers.LocalDataProvider.d__2f.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at

Anybody know how fix this?!)

Out-of-the-box error

Hi, I tried to add the project to an existing solution and got. In my case my extensions are installed elsewhere - which is probably the issue.

---------------------------
Microsoft Visual Studio
---------------------------
Unable to read the project file "Sqlite.vcxproj".

D:\Loqu8\smelt\sqlite-net-wp8\Sqlite.vcxproj(56,5): The imported project "C:\Program Files (x86)\Microsoft SDKs\Windows Phone\v8.0\ExtensionSDKs\SQLite.WP80\3.7.16.1\DesignTime\CommonConfiguration\Neutral\SQLite.WP80.props" was not found. Confirm that the path in the <Import> declaration is correct, and that the file exists on disk.
---------------------------
OK   
---------------------------

Upgrade reference of SQLite SDK when installing the package

Hi Peter,
kudos for putting together the nuget package, it's simplifying my team's work a lot.

I have one suggestion for you. Do you think it might be possible to update SDK reference when installing the package?

Currently we have to manually change it, see the following diff:

-    <SDKReference Include="SQLite.WP80, Version=3.8.2">
+    <SDKReference Include="SQLite.WP80, Version=3.8.3">

Cheers,
Juan

Relationships

Hello

I would like to know, if it is possible to create relationships between entities by using sqlite-net-wp8.

Compilation error

Hello,

I'm trying to build test app with sqlite-net-wp8 project but during the compilation I see this error:
Error 1 error C1510: Cannot load language resource clui.dll. C:\test\projects\PhoneApp1\sqlite-net-wp8-master\CL Sqlite
Error 2 Metadata file 'C:\test\projects\PhoneApp1\Debug\Sqlite\Sqlite.winmd' could not be found C:\test\projects\PhoneApp1\PhoneApp1\CSC PhoneApp1

How can I resolve this problem?

Thank you.

[sqlite3_bind_parameter_index] method always return 0

using this project on windows phone 8, with native SQLite 3.7.15.1 , sqlite3_bind_parameter_index always return 0. Any help, thank!

Error while submitting an app to the store

While submitting an app to the store, I get an error - Native API not allowed. The error message is pasted in the image below.

Extension version 3.7.5.12

There is a new Visual Studio extension that updates SQLite to 3.7.15.2. I modified my local repository easily, and it seems to be working. I cannot commit my changes but I guess that the master version will have to be updated to be in sync.

problem with sqlite

Hello,

I am new with window phone application, i have developed an window phone app in c# in which i have used sqlite as well i had integrated sqlite successfully but when i have changed my machine and i ran my application on another machine then it was showing error in sqlite project, i tried to reload it but failed every time it failed then i removed it and tried to add it again then i am getting the following error please help me to resolve this problem .

Improve multiplatform support for visual studio

Hi Peter, I'm working with the 3.8.3 version of your nuget package and I'm having problems when switching from ARM to x86 platform in visual studio.

Actually that totally makes if you look at the reference that was added to the csproj:

<Reference Include="Sqlite, Version=255.255.255.255, Culture=neutral, processorArchitecture=MSIL">
    <SpecificVersion>False</SpecificVersion>
    <HintPath>..\..\mywp8solution\packages\sqlite-net-wp8.3.8.3\lib\windowsphone8\Sqlite.winmd</HintPath>
</Reference>

I fixed this by manually editing the reference to

    <HintPath>..\..\mywp8solution\packages\sqlite-net-wp8.3.8.3\build\$(Platform)\windowsphone8

hence supporting both x86 and ARM targets.

Do you think the package could be upgraded for automatically supporting this scenario w/o manually editing the csproj?

Thanks,
Juan

UpdateAll Exception

Hi,

I recently updated the solution to the latest version of Sqlite-Net-WP8, but now I see that whenever I try to call UpdateAll, I get the following exception :

{SQLite.SQLiteException: SQL logic error or missing database
at SQLite.SQLiteConnection.Update(Object obj, Type objType)
at SQLite.SQLiteConnection.Update(Object obj)
at SQLite.SQLiteConnection.<>c__DisplayClass17.b__16()
at SQLite.SQLiteConnection.RunInTransaction(Action action)
at SQLite.SQLiteConnection.UpdateAll(IEnumerable objects)

This used to work in the previous version I had, so not sure what is different?

Thanks

How can download sqlite.vcxproj ?

Hi all.

I download this project but this .zip doesn't contains this file and I need this file to follow the README.md.

Regards.

Problem Value cannot be null

Hello,
in a my database have a value null.
if i calling this code in a device WP8 receive errors. Works with emulator. Why?

CODE:
list List;
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(Path, "mydb.sqlite"));
List = await conn.QueryAsync(sql, _params);

receive error:

_exception: {System.ArgumentNullException: Value cannot be null.
at System.StubHelpers.HStringMarshaler.ConvertToNativeReference(String managed, HSTRING_HEADER* hstringHeader)
at Sqlite.Sqlite3.sqlite3_open_v2(String filename, Database& db, Int32 flags, String zVfs)
at SQLite.SQLite3.Open(String filename, Database& db, Int32 flags, IntPtr zVfs)
at SQLite.SQLiteConnection..ctor(String databasePath, SQLiteOpenFlags openFlags, Boolean storeDateTimeAsTicks)
at SQLite.SQLiteConnection..ctor(String databasePath, Boolean storeDateTimeAsTicks)
at SQLite.SQLiteConnectionWithLock..ctor(SQLiteConnectionString connectionString)
at SQLite.SQLiteConnectionPool.Entry..ctor(SQLiteConnectionString connectionString)
at SQLite.SQLiteConnectionPool.GetConnection(SQLiteConnectionString connectionString)
at SQLite.SQLiteAsyncConnection.GetConnection()
at SQLite.SQLiteAsyncConnection.<>c__DisplayClass291.<QueryAsync>b__28() at System.Threading.Tasks.Task1.InnerInvoke()
at System.Threading.Tasks.Task.Execute()

Thank you

wp 8.1

hi,

I can't insrall the nuget package on a wp8.1 plateform, it's maybe only a problem of target.

Can you take a look ?

Thanks

SQLLite 3.8.0

I updated SQL LITE 3.8 and receive erros, What should I upgrade?
Thank you

Sqlite3.set_temp_directory give me exception

Sqlite3.set_temp_directory(Windows.Storage.ApplicationData.Current.LocalFolder.Path);
give me expection on windows phone silverlight 8.1
so I can't use transaction log on FS

Any idea why?

Requested Windows Runtime type 'Sqlite.Sqlite3' is not registered.

HI,

I have an issue, when i tried to launch my Windows phone 8 sample
Every time I tried to open or make any request on SQLite, I have the same exception

A first chance exception of type 'System.TypeLoadException' occurred in MyProject.DLL

The Inner Exception is just a "class not registered"

Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG))
Requested Windows Runtime type 'Sqlite.Sqlite3' is not registered.

I have rebuild my project targeting x86, I have built your last C++ bits (version 3.7)
I couldn't make it work

Maybe, you have any suggestions ? :)

Thank you !

Sebastien

The current version is built against an old SQLite version

Recently SQLite has released the Visual Studio extension that updates SQLite to 3.7.15.1. Your library can't be used (unless you manually edit the project file) because it's built against SQLite 3.7.15.

sqlite3 not registered

I believe this issue is connected with sqlite-net-wp8 - as it only appears when I include that package -
one trying to make a connection in winphone8.1 I get
Requested Windows Runtime type 'Sqlite.Sqlite3' is not registered.
has anyone any ideas. I 've seen references to this on a number of fora but the trail always goes cold

SQLite.cs errors

Hello,
I am testing sqlite-net-WP8 but have trouble getting it to work. When compiling my SQLite solution, I get three errors related to SQLite.cs, see below. Have tried with the previous versions but get the same error, the last one is from sqlite-net-WP8-3.8.0.zip.

Source line 12
The best overloaded method match for 'SQLite.SQLite3.Open (string, out Sqlite.Database, int, System.IntPtr)' has some invalid arguments
Source line 26
Line 12Argument 1: can not convert from 'byte []' to 'string'
Source line 32
'System.Threading.Thread' does not containe a definition for 'VolatileWrite'

I have performed the steps described for using sqlite-net-WP8 but probably done something wrong. Grateful if you can help me.

Regards
Jan Lindgren
[email protected]

Could not build under VS2013

When building in VS2013 with Win32 target platform.

1>------ Build started: Project: Sqlite, Configuration: Debug Win32 ------
1> pch.cpp
1> SqliteWP8.cpp
1>LINK : fatal error LNK1104: cannot open file 'sqlite3.lib'
========== Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==========

Though if change target platform to ARM build succeeds.

Expression is a namespace but used like a type

I went through the tutorial posted here:
http://khatrishashank.wordpress.com/2013/12/05/windows-phone-8-app-with-sqlite/

Once completed I have 8 errors in 1 file - SQLite.cs.

Expression is a namespace but is used like a type.

I probably have some Expression Blend namespace somewhere but I cannot work out how to resolve this. Have you seen this issue before?

Doesn't work with VS13

If i want to add the project to the solution Visual Studio 2013 freezes.

Namespace name cannot be found

My problem is, i settled up everything corrently, but functions are missing. Looks like some functions don't work. i do have everything referenced and sqlite fork installed correctly too.(namespace name SQLiteAsyncConnection could not be found (are you missing a directive or an assembly reference?) , ive made a post on stackoverflow...i really tryed everything but i dont know whats wrong.

stackoverflow: http://stackoverflow.com/questions/16679191/windows-phone-8-sqlite-setup

New version 3.8.8.3

Please can you support 3.8.8.3?

Question: how to deal with native queries?

Hi Peter,

I would like to execute some native queries and currently I don't know, how to deal with at all. Do you have any tips for me? see below

Should I use your wrapper for that? If not how should I solve this. Already I did it within source code but I have some performance problems, of course.

Thx, KR Christian

• Select count(*) as countitems FROM....
• select DEPARTMENT,count(STAFF_ID) as CountStaff, avg(SALARY) as AVGSalary
  from STAFF
  group by DEPARTMENT
  having count(STAFF_ID) >
• complex joins