With a perfect timing, as soon as I’ve published my previous post about using the chsarp-sqlite engine in combination with sqlite-net, Peter Huene has released a porting of the famous library for Windows Phone 8. What does it mean? That, finally, we are able to use the native SQLite engine that has been released as a Visual Studio extension and that we can use a common library to share our data layer with a Windows Store app for Windows 8.
At the moment, the project isn’t available on NuGet yet and requires two steps: the first one is to add a native class, that acts as a wrapper for the functions used by sqlite-net, and the second is to download a specific sqlite-net version, where the developer has replaced the usage of the csharp-sqlite engine with the native one.
Let’s start!
Please welcome GitHub
Both projects are hosted on GitHub (a popular website to host open source projects that also acts as a source control system based on Git), so the best way to download and use both them is using Git: you can also download the project in a single zip file but, this way, every time the developer will change something you’ll have to download everything again and add the new project to your solution. If you’re not familiar with Git, the easiest way to use it is to download GitHub for Windows, which is a Windows client that is able to connect to repositories hosted on GitHub and to keep files in sync with the server.
Just download and install the application from here: after you’ve launched it you’ll have to configure it for the first time. You’ll need to have a valid GitHub account: if you don’t have it, simply go to the website and create one. Once you’ve done you should see a window like this:
Unless you’ve already used GitHub and you already own one or more repositories, the window will be empty. Now go to the GitHub website and, specifically, to the sqlite-net-wp8 repository, that is available at the URL https://github.com/peterhuene/sqlite-net-wp8. At the top of the page, in the toolbar, you’ll find a button labeled Clone in Windows. Click on it and make sure that you’ve logged in in the website with the same credentials you used for the application, otherwise you’ll be redirected to the page to download the GitHub client.
Once you’ve done it the GitHub client will be opened and the repository will be automatically added to the local repositories list: after a while (the progress bar will show you the status of the operation) the whole repository will be downloaded in the default location, that is the folder C:\Users\User\Documents\GitHub\ (where User is your Windows username). Inside it you’ll find a folder called sqlite-net-wp8: that is the project that we need to add to our solution.
Since we’re already playing with GitHub, let’s download also the sqlite-net fork adapted to work with Windows Phone 8: repeat the operations we’ve just made on the repository available at the URL https://github.com/peterhuene/sqlite-net.
The last thing to do is to make sure you’ve installed the SQLite for Windows Phone extension, that is available from the Visual Studio Gallery.
Now that we have everything we need, we can start working on our Windows Phone 8 project.
Let’s play with SQLite
The first thing to do is to open Visual Studio 2012 and to create a Windows Phone 8 application. Once you have it, it’s time to add to the solution the sqlite-net-wp8 project we’ve downloaded from GitHub: simply right click on the solution, choose Add existing project and look for the file Sqlite.vcxproj in the sqlite-net-wp8 folder (that should be C:\Users\User\Documents\GitHub\sqlite-net-wp8). You’ll see the new project added in the Solution Explorer: it will have a different icon than the Windows Phone project, since it’s written in native code and not in C#.
As I’ve previously explained, this is just the native wrapper for some of the functions used by sqlite-net: now we need to add the real sqlite-net and we do that by simply copying the Sqlite.cs and SqliteAsync.cs files that are stored inside the src folder of the solution (that will be available, as for the other one, in the C:\Usesr\User\Documents\GitHub folder) into our project. We can do that by simply right clicking on the Windows Phone project and choosing Add existing item.
Now we need to add a reference in our Windows Phone application both to the sqlite-net-wp8 library and to the SQLite engine: right click on your project, choose Add reference and, in the Solution tab, look for the sqlite library; after that, look for the SQLite for Windows Phone library, that is available in the Windows Phone – Extensions section.
UPDATE: the developer, to keep supporting also the C# engine I’ve talked about in my previous post, has recently added a new requirement to use his library; you’ll have to add a specific contitional build symbol, in order to properly use the native engine. To do that, right click on your project (the one that contains the Sqlite.cs and SqliteAsync.cs files you’ve previously added), choose Properties, click on the Build tab and, in the Conditional compilation symbols textbox add at the end the following symbol: USE_WP8_NATIVE_SQLITE. In a standard Windows Phone 8 project, you should have something like this:
SILVERLIGHT;WINDOWS_PHONE;USE_WP8_NATIVE_SQLITE
And now? Now we can simply copy and paste the code we’ve already seen in the original post about Windows 8 or in the more recent post about csharp-sqlite: since all these libraries are based on sqlite-net, the code needed to interact with the database and to create or read data will be exactly the same. Here are the usual examples I make about doing common operations:
UPDATE: as some readers have pointed out in the comments, with the previous code eveyrthing was working fine, but the database file was missing in the local storage. As the sqlite-net-wp8 developer pointed me out, there’s a difference between the Windows 8 and the Windows Phone version of the library. In Windows 8 you don’t have to set the path, it’s automatically created in the root of the local storage, unless you specify differently. In Windows Phone 8, instead, you have to pass the full path of the local storage where you want to create the databse: the code below has been updated to reflect this change.
//create the database
private async void CreateDatabase()
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path, "people.db"), true);
await conn.CreateTableAsync<Person>();
}
//insert some data
private async void Button_Click_1(object sender, RoutedEventArgs e)
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path, "people.db"), true);
Person person = new Person
{
Name = "Matteo",
Surname = "Pagani"
};
await conn.InsertAsync(person);
}
//read the data
private async void Button_Click_2(object sender, RoutedEventArgs e)
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path, "people.db"), true);
var query = conn.Table<Person>().Where(x => x.Name == "Matteo");
var result = await query.ToListAsync();
foreach (var item in result)
{
Debug.WriteLine(string.Format("{0}: {1} {2}", item.Id, item.Name, item.Surname));
}
}
Be careful!
There are some things to keep in mind when you work with this library and SQLite. The first one is that, actually, both libraries are not available on NuGet: you’ll have to keep them updated by using GitHub for Windows and, from time to time, by syncing the repositories, in order to have your local copy updated with the changes. If you’re going to add the sqlite-net-wp8 project to the solution, like I did in the post, you won’t have to do anything, you’ll just have to rebuild your project. In case of the sqlite-net fork, instead, since we’ve simply copied the files, you’ll need to overwrite the old ones with new ones, in case they are updated. Or, even better, you can add the two files as a link from the original project: this way you’ll simply have to update the libraries from GitHub to see the updates in your application.
The second important thing to consider is that the sqlite-net-wp8 library is built against a specific SQLite version: if the SQLite team releases an update to the Visual Studio extension (so that Visual Studio is going to prompt you that there’s an update to install), don’t update it until the sqlite-net-wp8 project has been updated. Otherwise, many references will be missing and you won’t be able to open the project at all.
Have fun!
Thank you for this post. This is exactly what I needed today.
Ok, I’m trying that. Database working, but i can’t find *.sql file in Isolated Storage and database rewrite after updating the application.
I have the same problem as Alex. The database doesn’t seem to be written to Isolated Storage. Did you manage to fix this Alex?
Hi Jon and Alex, I can confirm the problem, I’ve tried by myself. I’ve contacted Peter, the porting’s developer, I will update the post as soon as I have more info to share.
Hi, I’ve update the code in the post to fix the problem you’ve reported. Thanks for pointing me out!
I just went through this tutorial (4:30 PM CST) and I am still getting the “Community” issue on this line: using Sqlite3 = Community.CsharpSqlite.Sqlite3;
Can you leave a reply with the update as well?
Nevermind! I had the Conditional statement incorrect!
Thanks for the update. I also pointed to update the repository to reflect the latest Sqlite for Windows Phone version in the Visual Studio Gallery.
Although everything is working fine in the emulator, I’m trying to deploy to a real WP8 device (Nokia Lumia 820), and there is where I have the problem. The build fails with:
Deployment failed because an app with target platform x86 cannot be deployed to Device. If the target platform is win32/ x86, select an emulator. If the target platform is ARM, select Device.
Any steps I’m missing? Anyone manage to use the suggested solution configuration to build and deploy an app in a real device?
Thanks for the great article!
Hi, sqlite engine can’t be built both for ARM and X86, but you have to set just one target platform. To do it, click on the Debug / Release dropdown and choose Configuration Manager. In this windows set ARM as platform in the Build dropdown and try again.
Thanks, it works fine! I totally forgot the configuration manager, I was just trying to verify the project properties. It works great, now I have a great foundation to refactor my new app to target WP8 and W8
Thanks again for the great article and helpful tips. It covers nicely the gaps left by Microsoft with their “partial” support for sqlite for WP developers!
Thanks Matteo, it’s all working now.
Great article. I’ll be using this as the storage foundation for my next app. Can’t thank you enough!
Hi,
Is there a way to access the database that is created? I have determined that its directory is: “C:\Data\Users\DefApps\AppData\{DBB9F7C7-0D4B-4019-8450-82FAD1DE51A8}\Local\people.db” but I don’t have access to this and can’t open it.
Apart from that,t hanks for your tutorial, everything worked out great!
Hi, the database is created in the local storage of the emulator or the device, so it isn’t stored on your hard drive like a normal file. You can use an utility like Windows Phone Power Tools to access to the isolated storage of your application and download the file. You can download it from http://wptools.codeplex.com/
Hello, I am able to run my app fine on a virtual device provided by the emulator. But when I try to deploy it on a real WP8 device, I get the error:
“The type or namespace name ‘Community’ could not be found (are you missing a using directive or an assembly reference?)”
Thank you.
Are you deploying to the device using Debug or Release configuration? Make sure that the compilation symbol described in the post is added in the project’s properties for every configuration.
Thank you! I had added the compilation symbol only to the emulator and had not added it to the device itself. Everything works great now, thanks!
Any thoughts about doing an article about a “clean” way to using sqlite with a project that has a platform specific part and a PCL for the common part. What would be the best strategy? Create an abstraction in the PCL to access sqlite? etc.
I’m sure there are a lot of people right now working on solutions and ideas on how to combine sqlite, multiplatform projects and PCLs…
I am using codeplex tool “Sqlite Client for Windows Phone” available at http://sqlitewindowsphone.codeplex.com/releases. Is it allowed to use? it is working fine in my Windows phone 8 and 7.5 emulators. I haven’t tested my app in the device yet. does the Microsoft WP store accepts my app with it or do I need to change to this new library “sqlite-net”? Please suggest..
Hi, that tool is good, the downside is that you’re using a custom C# engine and not the original sqlite engine developed by the sqlite team. This way you loose all the updates made by the team and you won’t be able to share easily your code with a Windows 8 application. More or less it’s the same approach I’ve explained in this post http://wp.qmatteoq.com/a-workaround-to-use-sqlite-in-a-windows-phone-8-application/, pros and cons are the same.
Thanks Matteo for quick reply. I am almost done with my app coding and I am about to publish to WP market place. right now I am not planning to do windows 8 version of it. So, I want to submit without changing to new sqlite-net implementation. hoping, it won’t be a problem for passing certification test.
Have you done this yet?
Hi Matteo,
Found this postvery interesting.
If you can make a post which explains creating a common datalayer which can be used for both windows phone 8 and windows 8,that would be really great.
Thanks in advance.
Hi , First of all thanks for the great post 🙂 , i have few doubts 1) does the same process work even for windows phone 7 ????,( i am developing a windows phone app , i would like to target all windows phone devices i.e, windows phone 7, 8) 2) sqlite support for windows phone 7 ??? please let me know , looking forward for your reply
Hi, here are your answers:
1) No, Windows Phone 7 is not supported using this procedure because the native SQLite engine is available just for the Windows Runtime (so Windows Phone 8 and Windows 8).
2) You can take a look at this post of mine: http://wp.qmatteoq.com/a-workaround-to-use-sqlite-in-a-windows-phone-8-application/ The solution explained uses a C# engine that works also with Windows Phone 7.
thanks a lot for the reply , this cleared my doubt .
Windows phone 8 app how to Import an already existing SQLite database file ????
hi, i have a followed your post “Import an already existing SQLite database in a Windows 8 application” in and successfully implemented the way you have guided in the post and imported the existing sqlite database in my windows 8 app , but now i am trying to implement the same thing in my windows phone 8 app, since the file is saved not on the hard disc but saved saved in local storage of emulator /device , how how can i check every time that file is present or not on application 1st page load and if there, don’t do anything if not there copy the data present in exisisting external sqlite db file present in project to that emulator/device isolated storage. i request you please let me know on how to implement it , it would be very helpful to me i request you please let me know on how to implement it , looking forward to your reply . thanks in advance
You have to follow the same approach described in my post about Windows 8: the code sample will work also on Windows Phone 8, since the APIs of the Windows Runtime used to work with the storage are the same in the two platforms.
I’ve been using this for a bit and it works great. A problem I have is that my app works great until I submit it to the app store. I’ve been submitting betas and have yet to figure out why it doesn’t work after being submitted. I’ll update if I ever figure it out.
Hi and thanks for the tutorial.
After spending some hours setting up sqlite I finally got rid of all the errors and could compile. However when I try to run this example I’m getting “An exception of type ‘System.BadImageFormatException’ occurred in SQLtesting.DLL but was not handled in user code” error.
This happens on “await conn.InsertAsync(person); ” line.
Any idea on what might be wrong?
Hey Teo, do you think you can send me a sample of your project I can play with? With just the information you gave me, I can’t understand what’s going on.
hi..you are doing a great job..
i just want your help..i followed your process for database access
for wp8 it just work fine..
and now i want to access only the column of a table from my database but not have any clue..
plzz..reply..and if you can add something more about using sqlite
in wp8 about query purpose plzz..do..
To do that, you should do a manual query against the database, otherwise you have to get the whole object and get only the information you need.
1028: The native API api-ms-win-core-interlocked-l1-2-0.dll:InterlockedCompareExchange() isn’t allowed in assembly sqlite3.dll. Update it and then try again.
I Got This Problem While Uploading xap file In Store Help Me Fix It For windows phone 8
Please check that the app has been compiled for ARM and not for x86 or Any CPU, since SQLite engine can’t be compiled for the Any CPU target.
Hi.. Thanks alot for your post… very helpfull.
Is SQlite better than SQl CE for windows phone 8 applications w.r.t performance ?
Yes, the downside is that SQLite is still not flexible and powerful as using SQL CE with LINQ to SQL (for example, in managing relations).
I need some help. Do you have a code snippet of when the app first starts up, it checks if the db exists, and if does not it creates it. I’m having a major issue. I created a db using SQLite Expert Personal and its quick and works fine. I created a new project and updated all of SQLite to latest version. I run my app, write data to a table and it never gets written and throws no errors. I cannot figure out the problem. So I’m starting from scratch.
Is the database you’ve created with the tool copied in the storage the first time the app is executed?
How do I retrieve the last inserted record row id?
I tried something like this:
cmd.CommandText = “select last_insert_rowid() as id from myTable”;
var temp = cmd.ExecuteQuery();
Is there any documentation on this anywhere?
It’s enough to access to the id property of the object you’ve just inserted.
Here is a sample where I show a message with the ID of the record I’ve just added to the table.
private async void OnInsertDataClicked(object sender, RoutedEventArgs e){
SQLiteAsyncConnection conn = new SQLiteAsyncConnection("people.db");
Person person = new Person
{
Name = "Matteo",
Surname = "Pagani"
};
await conn.InsertAsync(person);
MessageBox.Show(person.Id.ToString());
}
Thank you. I’m doing it slightly different
using (var db = new SQLite.SQLiteConnection(System.IO.Path.Combine(ApplicationData.Current.LocalFolder.Path, “myDB.db”), true))
{
int sucess;
sucess = db.Insert(new myTable()
{
TableID = _vm.TableID,
Description = _vm.Description,
TableDT = _vm.TableDT
});
}
I’m using a viewmodel I have created. How would I get the ID doing it this way?
Instead of using creating a new myTable instance inside the Insert() method, create it outside and pass it to the Insert() method. Or is there any specific reason why you’re using this code?
Hi, thank you for the write-up! Is it not safe to use the praeclarum version of sqlite-net alongside sqlite-net-wp8?
There are some differences between Windows Phone and Windows 8, so the official sqlite-net version from praeclarum doesn’t work Windows Phone, but just with Windows Store apps for Windows 8.
I’m waiting on praeclarum to accept a pull request that fixes a build break introduced by an pull request unrelated to WP8. Once that pull request is accepted, I will update the instructions to use the original sqlite-net repo and delete my fork.
That’s great news! Ping me when it will happen, so that I can update the post.
Hey Matt,
I’ve done all you’ve said, the SQlite project is added and the SQLite.cs and SQLiteAsync.cs too. I have references to Sqlite and SQLite for Windows Phone.
I’ve also updated my phone app by putting “using SQLite” and “using Sqlite”.
The compilation symbols are updated too, for every configuration.
After I’ve entered all code, and I try to build or debug it, I get several errors. These are:
Error 1 The name ‘Path’ does not exist in the current context
Error 2 The name ‘ApplicationData’ does not exist in the current context
Error 7 The name ‘Debug’ does not exist in the current context
Any ideas how I can resolve this?
Thanks in advance!
I think you’re missing the namespaces to work with the local storage, that are
using System.IO;
using Windows.Storage;
hi sir….i got the following problem when i click on the insert / show button of the project…i done every thing as mentioned in the post….
the error is…..
“An exception of type ‘SQLite.SQLiteException’ occurred in PhoneApp11.DLL but was not handled in user code”
it gives me error in the ” return stmt;” of SQLite.cs class.
i have searched google but i found nothing….???? plz help me.
Hi, can you upload the project somewhere (on Skydrive or Dropbox, for example) and send me the link to download it? So I can give it a look.
respected sir…this is the link to download my application…
https://skydrive.live.com/?cid=634069DE8E33CE9D&id=634069DE8E33CE9D!156
Hi, I’ve found two issues in your code:
1) The first one is that the createdatabase metod is never called, so the database is never created. I’ve changed the code to call the method in the Loaded event of the page.
2) The debug information you are printing are wrong, because you’re doing you’ve added to the String.Format statement 3 parameters, but you have only 2 of them. The correct code should be:
foreach (var item in result)
{
Debug.WriteLine("{0}: {1}", item.id, item.name);
}
With these changes, the app is working fine for me.
Cheers
Hi, thanks for posting these examples! I was having a problem inserting data and I was making the same mistake. The db was not being created. 🙂
Hi I have used this code but when I add in the conditional for the project in the SQLite.cs file for WP it gives the errors saying that SqLite could not be found and this error comes up on in the beginning of the file here #if USE_CSHARP_SQLITE
using Sqlite3 = Community.CsharpSqlite.Sqlite3;
using Sqlite3DatabaseHandle = Community.CsharpSqlite.Sqlite3.sqlite3;
using Sqlite3Statement = Community.CsharpSqlite.Sqlite3.Vdbe;
#elif USE_WP8_NATIVE_SQLITE
using Sqlite3 = Sqlite.Sqlite3;
using Sqlite3DatabaseHandle = Sqlite.Database;
using Sqlite3Statement = Sqlite.Statement;
#else
using Sqlite3DatabaseHandle = System.IntPtr;
using Sqlite3Statement = System.IntPtr;
#endif
Hi, have you added to your project a reference both to the GitHub project and to the SQLite runtime for Windows Phone, as described in the post?
Thanks for the quick reply I got it to work. IT was exactly what you thought
plz sir upload the modified project some where…i want to check…best regards
Hi, here is the working project: http://sdrv.ms/137NCNX
Keep in mind that I had to change the SQLite references, since mine was in a different folder, so you’ll have to rearrange them.
I am try to check if a table is exist like this:
var coon = new SQLiteConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path, databaseName), true);
var r = coon.Execute(“select count(*) from sqlite_master where type =’table’ and name=’?'”, typeof(T).FullName);
But it throw an exception.How to check if a table is exist and see if create is needed?Thanks a lot.
Hi, the method CreateTableAsync() already does that: if the table already exists, it isn’t created again.
ok,even if I do not need to check if table is exist when I am trying to create table,’get table’ action Table() will throw exception when table not exist.If existential checking is not supported,the exception is only thing can be used to ensure table’s existence,right?
Yes, but I suggest to call the methods to create the tables every time the app starts, instead of catching exception. This way, you’ll be sure that the tables are there.
I am getting error in read method while reading data on button click event.
var result = await query.ToListAsync();
Error : the await operator can only be used within an async method.Consider Marking this method with ‘async’ modifier & changing it return type to “TASK” .
& If i removes await then compiler gives me error in foreach saying that foreach cannot operate on variables of System.Threading.Task.Task<System.Collection.Generic.List>
Hi, it seems that you didn’t mark the method that contains your code with the async keyword. It should be something like this:
public void async GetData()
{
var result = await query.ToListAsync();
}
First Of all Thanks For reply …
But Buddy “ToListAsync” is the inbuilt method of
SQLiteAsync.cs class so i cant change the return type.The return type of “ToListAsync” method is predefined & it is Task
.
So What To Do Now ? I am not writting my own code I am just copying the above code which you have given as example above.help if you get the scenario of my doubt.
Maybe there was a misunderstanding. I’m not saying that you have to change the return type of the method, I’m asking if you have marked the method where you are calling ToListAsync() (in my sample, the button event handler) with the async keyword.
Something like this:
private async void Button_Click_1(object sender, RoutedEventArgs e)
Notice the async keyword between private and void
Friend Thank You Very Much I got it. That’s My Problem Sorry I wont Get that in first time. Thank You So Much.
Thank you for this,
It just what I wanted. But the question I have is that still I cant use the library (like in using System.Data.SQLite). I tried to install it from project-> Manage NuGet packages-> online but it tells me <>
Waiting for your reaction
Did you follow the steps described in the post? You don’t have to install it using NuGet, but you have two downloads the two projects (sqlite-net and sqlite-net-wp8) from GitHub.
First of all thanks for tutorial. I have one question/issue, I successfully add data to tables, but when I launch application second time, changes are discarded ? Here is my code
SQLiteAsyncConnection sqLiteConnection = new SQLiteAsyncConnection(db_path, true);
await sqLiteConnection.InsertAsync(new Scores()
{
BiletID = 1,
Date = DateTime.Now.ToString(),
Score = _20
});
Is there something I have to do to save changes ? Or am I doing something wrong ?
For getting results I use following code
using(var dbCon= new SQLiteConnection(db_path))
{
var _scores = dbCon.Table().ToList();
}
As I said it works fine until I re-run application, changes are discarded, and _scores returns nothing.
Hi, which is the the value of the db_path string?
It should be a full path to the local isolated storage, like in the samples:
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path, "people.db"), true);
Otherwise, you’ll get the behavior you described: the application is able to save the data in the database, but when the app is closed data is lost.
Hi,
i am getting some error in sqlite.cs file. When i debug the application i am getting in this part
public static Result Open(string filename, out Sqlite3.sqlite3 db)
{
return (Result) Sqlite3.sqlite3_open(filename, out db);
}
public static Result Open(string filename, out Sqlite3.sqlite3 db, int flags, IntPtr zVfs)
{
return (Result)Sqlite3.sqlite3_open_v2(filename, out db, flags, null);
}
can u please say me. I am very new to windows phone 8.
Did you follow the blog post? Usually this error happens when you forgot a step, like adding the SQLite Runtime or the GitHub project as a reference.
Thank you so much..
I need one more help. I need auto increment for one column (ID) in the data base. So can you please suggest for me.
You can find a sample in the original Windows 8 library mentioned in the post: http://wp.qmatteoq.com/using-sqlite-in-your-windows-8-metro-style-applications/
You can add the AutoIncrement attribute to a property to have its value automatically generated:
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
Thank you so much qmatteoq.
Hai
Can i create another table and access both the tables by using this code
Sure, you can create as many table as you want and access them in the same described in the post. Just create additional classes and call the CreateTableAsync() method for every table.
Thank you so much
Can you please say me how to assign a forigenkey for a id.
Hi, sqlite-net doesn’t support foreign keys and relationships. If you have this requirement, you have to take a look at another wrapper: http://wp.qmatteoq.com/a-new-sqlite-wrapper-for-windows-phone-8-and-windows-8-the-basics/
Hai,
how can i get data from two different tables which are in same data base.
can u give an example please
If you’re talking about relationships, they’re not supported in sqlite-net. You have to do nested queries if you want to retrieve data that is connected between two or more tables.
can we use update query in this.
Sure, just retrieve the object you want to modify, change the needed properties and call the UpdateAsync() method, passing as parameter the edited object.
Error 1 Metadata file ‘C:\Users\sathwikenukonda\git\Windows\My Medi\Debug\Sqlite\Sqlite.winmd’ could not be found C:\Users\sathwikenukonda\git\Windows\My Medi\My Medi\CSC My Medi
i am getting this error
that i am missing a file at this point
Did you add a reference to the native SQL Lite engine? You need to download the appropriate Visual Studio extension, as described in the post
Can u please give a sample code for update query by using this wapper. It will be very much helpful.
qmatteoq, please how do you specify “Foreign Key” and “References”?
If I want to graft my wp project to windows 8,ofcourse ths wp8 wrapper is not suit. So can I only use the wrapper sqlite-net from nuget?
Yes, exactly. However, the code you wrote to use sqlite-net on WP8 will work without changes on Windows 8, APIs are the same.
Hai
Can u please give an example for update query by using this wrapper.
it should extract one particular row when we selected it and then updated data should go into the table..
Can u please help me out for this
Here is a sample of an update query to update a person’s name:
private async void OnUpdateButtonClicked(object sender, RoutedEventArgs e) { SQLiteAsyncConnection conn = new SQLiteAsyncConnection(Path. Combine(ApplicationData.Current.LocalFolder.Path, "people.db"), true);
Person person = await conn.Table
await conn.UpdateAsync(person); }
Hai,
wait conn.Table ().Where(x =>
Iam getting an error here can u please explain me why i am getting an error here..
Hi Sathwik,
can I please ask to post your questions just once? Due to family / work duties I can’t always reply to comments immediately, but it doesn’t mean I didn’t read them 🙂
Regarding your question, please make sure that you’ve added the System.Linq namespace to your class, otherwise all the Linq methods (like Where() or FirstOrDefault()) won’t be available.
hai
Sry for ur inconvenience actually i got stocked in the project so u r the person giving me the reply’s soon so i am repeatedly posting. I am new to windows phone 8 development so i am learning each and every topic from posts itself. thanks for your reply’s your posts are really amazing thank you.
hai,
i am retrieving the data from person table and appending that data to the observable collection list and calling the selected indexed item to show specific data to the user
int index = 0;
protected override void OnNavigatedTo(System.Windows.Navigation.NavigationEventArgs e)
{
string selectedIndex = “”;
if (NavigationContext.QueryString.TryGetValue(“selectedItem”, out selectedIndex))
{
index = int.Parse(selectedIndex);
DataContext = App.ViewModel.Items[index];
}
base.OnNavigatedTo(e);
App.userid1 = App.ViewModel.Items[index].Lineone;
}
by using this i am getting the specific row which i have selected. now i want to update the some more data to this row and update it in the table so can u please help me
I guess that App.ViewModel.Items[index] returns the Person object selected by the person. You have to cast it as Person (Person person = App.ViewModel.Items[index] as Person), to change the properties you want to edit and to call the UpdateAsync() method of the SQLiteAsyncConnection object, passing as parameter the edited Person object.
Person person = App.ViewModel.Items[index] as Person;
person.Name = "Matteo";
await conn.UpdateAsync(person);
THanks for your reply, This is not working qmatteoq can please assist me to update that particular row by calling the Id of that row.
Hi, what do you mean with “is not working”? Can you give more details about the problem?
hai,
Thanks for ur reply,initially i am inserting some data row in the table, later i have to insert some more data into that particular row in the table. so need to update that rows usually.
THanku so much qmatteoq, Atlast i have done it successfully.
you helped me a lot in working with sqlite,and i thank you a lot for ur paitance 🙂 and for all ur reply’s.
Good to know 🙂 Feel free to contact me again if you have further troubles.
surely 🙂
I got some confuse on auto ‘AutoIncrement’ primary key. When I need to delete one row from my table by this ‘await connection.DeleteAsync(obj)’,but it can not delete it bucause when I init my ‘obj’,I didn’t(cann’t) tell it what primary key is its.So, how can I delete one row when using ‘AutoIncrement’ primary key?
Hi, you shouldn’t delete an item by passing to the DeleteAsync() method a new object, but you should first retrieve it from the table.
Something like:
private async void OnReadDataClicked(object sender, RoutedEventArgs e)
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path, "people.db"), true);
Person person = await conn.Table
await conn.DeleteAsync(person);
}
Thanks you… Very much
Great tutorial I referenced so far..
as a begineer
Do sqlite-net support database encryption？
No, you need to manually encrypt the database file on the storage if you have this requirement.
All right. Maybe I need to find a way to create connection with ecrypted database file. Thank you anyway.
can anyone please tell me how to pass connection string to encrypted sqlite db in windows phone 8?
Take a look on a free tool Valentina Studio. Super thing!!!
I highly recommend – is best manager for SQLite http://www.valentina-db.com/en/valentina-studio-overview
Thanks, it seems to be really interesting, I will surely take a look!
In SQLCE we use pulling and pushing to create tables and data from SQLServer. Can we use similar commands for SQLITE DB, when creating the DB?
Will this work with Xamarin. I mean Shall we use the same for Android and IOS SQLite db.
I’ve never tried, but it seems that there’s a Xamarin compatible version of the sqlite-net library.
I’m having trouble getting it to create a table. I’m using a custom class with mostly string properties, but a few List<KeyValuePair> as well. It throws an error saying it doesn’t know what to do with Lists or Dictionary (what I was using before).
Any ideas on how to work around this?
Thanks,
SonofNun
Hi, SQLite-net doesn’t support relationships, so unfortunately you can’t do that.
C6011 Dereferencing null pointer Dereferencing NULL pointer ‘value’. Sqlite sqlitewp8.cpp 203
‘value’ may be NULL 203
‘value’ is dereferenced, but may still be NULL 203
Hi, when do you exactly get this error?
When I run code Analysis for solution, I got this error. And another thing is that please let me know steps to add application on app store that have SQLite database. because I am not able to add my application on app store.
It’s been a while since I have submitted a SQLite based application on the Windows Store, I don’t remember having problems, except for the fact that I needed to prepare specific packages for the different architectures (x86, x64 and ARM).
Thank you very much!
For your help, I’ve fixed my problem.
Thank you very much!
For this article, I could fix my SQL problem!
Any idea why I don’t have a “Build” tab in Visual Studio Express 2012 for Windows Phone in properties of my project? Any ideas on how to get around this?
Weird. Are you sure are you selecting the right project and not the solution? I don’t have Visual Studio Express installed, so unfortunately I can’t check right now.
Recently I have downloaded SQLite for Windows Phone 8.1 from the below link. http://visualstudiogallery.msdn.microsoft.com/5d97faf6-39e3-4048-a0bc-adde2af75d1b
Now In Project setting i did changes by changing the “conditional compilation symbols” to NETFX_CORE;WINDOWS_PHONE_APP;USE_WP8_NATIVE_SQLITE
I am getting error from the below code saying
Error 3 The type or namespace name ‘Sqlite’ could not be found (are you missing a using directive or an assembly reference?) C:\XYZ\PhonePOC\SQLLiteApp\WindowsRuntimeComponent1\DataStore\SqlHelpers\SQLite.cs 40 17 WindowsRuntimeComponent1
#elif USE_WP8_NATIVE_SQLITE
using Sqlite3 = Sqlite.Sqlite3;
using Sqlite3DatabaseHandle = Sqlite.Database;
using Sqlite3Statement = Sqlite.Statement;
#else
Please let me know How to use the “sqlite-net” in the Windows Phone 8.1 (Without silver light)
Thanks in advance.
Earlier It was working fine in Windows Phone Apps such as
Blank App(Windows Phone 8.1)
Blank App (Windows Phone 8.1 silver light )
But it is not working in
Windows runtime component (Windows Phone 8.1).
when i was build after adding reference to the Windows runtime component project below error was getting.
Error 5 Windows Runtime type ‘Windows.Foundation.Point’ was found in multiple referenced winmd files. Please remove either ‘C:\Program Files (x86)\Microsoft SDKs\WindowsPhoneApp\v8.1\ExtensionSDKs\Microsoft.VCLibs\12.0\References\CommonConfiguration\neutral\platform.winmd’ or ‘C:\Program Files (x86)\Windows Phone Kits\8.1\References\CommonConfiguration\Neutral\Windows.winmd’ from the list of referenced files. C:\Synch\Ranga\PhonePOC\SQLLiteApp\WindowsRuntimeComponent1\WINMDEXP WindowsRuntimeComponent1
When i removed the platform.winmd from the ExtensionSDKs. It works perfectly fine.
Are you still facing the issue? I haven’t understood if the second comment you made means that you’ve fixed it or not. Thanks!
Does anyone know if there is any updated SQLite version available to use in Windows phone 8.1. When I use the github SQLite project in windows phone 8.1 project, I am unable to reference the sqllite project. The reference is working properly with window phone 8 version. So, I believe SQLite is not supporting 8.1 yet. Anyone have alternatives to use this in WP 8.1?
Hi, there’s a specific Visual Studio extension to add the SQLite engine to a Windows Phone 8.1 application. You can download it from: http://visualstudiogallery.msdn.microsoft.com/5d97faf6-39e3-4048-a0bc-adde2af75d1b
Thank You for such a great post !
First of all , I wanna let you know that I am Beginner in developing windows apps.
So rather than talking of the code , I need guidance.
I followed your post and everything worked fine. I just created sample database , inserted data, retrieved etc. , All good (Thanks to you).
1) But I want to implement a existing database (.sql format) , so how to do that ?
2) Furthermore, rather than creating a database(going into all that stuff) , we can also create a list and add items to that, because in database we are inserting data manually via objects. So what’s the use of database ?
Again, I am a beginner , just trying to learn , No offence please 🙂
Hi Umang,
I’m glad you’ve found my post useful!
Here are the answers to your questions:
1) I’ve published a post about this topic: it’s valid both for Windows and Windows Phone 8. You can read it here: http://wp.qmatteoq.com/import-an-already-existing-sqlite-database-in-a-windows-8-application/ or ObservableCollection ) data isn’t automatically persisted, so when the app is closed, the content of the list is lost.
2) Database is persisted in the isolated storage, so even if you close and reopen the app the data isn’t lost. If you simply insert your data in a collection (like List
And don’t worry, there are no stupid questions, there’s always something new to learn 🙂 Cheers
Thank You ! I got my answer. I followed your post and could implement an existing database in my app. Thanks a lot again. 🙂