Friday, 11 September 2009

MonoTouch with SQLite "Corporate Phonebook"

UPDATE [3-May-12]: The latest code for this sample is now on github (CorporateDirectory). It contains the latest version of SQLite.cs and minor bugfixes. Firstly, let me begin by saying this sample totally relies on the contribution of Frank Krueger who posted the SQLClient.cs code to the MonoTouch mailing list
Here is the code. Consider it released into the public domain. If there's interest, I can start a Google code project or something.
Many thanks Frank! This is a VERY simple application using that library: it lists 'employees' from an SQLite database and allows you to call or email them:
*
* note: may not adhere to iPhone user interface guidelines!

The c# code itself can be downloaded (26Kb) or browsed below:
This first sample only reads from SQLite on the iPhone - just a single database call
using (var db = new SQLiteClient.SQLiteConnection("phonebook")) {
db.Open();
var users = db.Query(
"SELECT Firstname, Lastname, Work, Mobile,
Department, Email
FROM Phonebook ORDER BY Lastname"
, 1000);
listData = users.ToList();
}
which queries data that was set-up in the SQLite Database Browser and included with the application as a 'Content' file within the MonoTouch project.
Creating a database using SQLite Database Browser


It uses UITableViewDelegate, UITableViewDataSource and UIAlertViewDelegate implementations to populate the scrolling list and react to touch 'events'.

When you touch a row, we use the OpenUrl method discussed previously to trigger a call or email.

Future additions to this sample might include alphabetized sections, search function and a proper 'user page' rather than using UIAlertView. Perhaps some hierarchical navigation and an online 'updater' function as well?

p.s. yes, I shouldn't have used INTEGER for the telephone numbers in SQLite... they seem to be overflowing. I will convert them to TEXT in a future post..

20 comments:

  1. We are planning to do a similar application for distribution within our company in 2010. We would be interested in following the development of the sample and potentially contributing to its development.

    ReplyDelete
  2. Hey Tony, there'll be a few updates to this sample (I have some ideas) which will always be posted here or at conceptdevelopment.net.

    ReplyDelete
  3. I'm getting the following error when running a variation of this code:

    Unhandled Exception: System.MissingMethodException: Method not found: 'Default constructor not found...ctor() of XactScope.Scope'.
    at System.Activator.CreateInstance (System.Type type, Boolean nonPublic) [0x00000]
    at System.Activator.CreateInstance (System.Type type) [0x00000]
    at System.Activator.CreateInstance[Scope] () [0x00000]
    at SQLiteClient.SQLiteCommand+<ExecuteQuery>c__Iterator0`1[XactScope.Scope].MoveNext () [0x00000]
    at System.Collections.Generic.List`1[XactScope.Scope].AddEnumerable (IEnumerable`1 enumerable) [0x00000]
    at System.Collections.Generic.List`1[XactScope.Scope]..ctor (IEnumerable`1 collection) [0x00000]
    at System.Linq.Enumerable.ToList[Scope] (IEnumerable`1 source) [0x00000]
    at XactScope.AppDelegate.FinishedLaunching (MonoTouch.UIKit.UIApplication app, MonoTouch.Foundation.NSDictionary options) [0x0005a] in /Users/carlmouritsen/Projects/XactScope/Main.cs:49
    at (wrapper managed-to-native) MonoTouch.UIKit.UIApplication:UIApplicationMain (int,string[],intptr,intptr)
    at MonoTouch.UIKit.UIApplication.Main (System.String[] args, System.String principalClassName, System.String delegateClassName) [0x00000]
    at MonoTouch.UIKit.UIApplication.Main (System.String[] args) [0x00000]
    at XactScope.Application.Main (System.String[] args) [0x00000] in /Users/carlmouritsen/Projects/XactScope/Main.cs:14

    My Scope class has a default constructor. Any ideas what I'm doing wrong? Thanks.

    ReplyDelete
  4. Is the XactScope.Scope default constructor actually referenced anywhere in the code? The monotouch compiler will optimise away methods that don't "appear" to be used.

    Check my Main.cs for some seemingly useless code: look for the comment beginning //System.MissingMethodException...

    This is my HACK to ensure the compiler doesn't optimise away my Employee class or any of it's getters/setters. Otherwise the compiler can't "see" them being used (since the only other place is via a generic class db.Query<Employee> but there are no explicit references to the constructor or properties in code).

    There are number of fixes for this:

    1) my 'hack': just reference all the methods/properties manually (even as no-ops) so the compiler keeps them... this is my "first principles" approach in action :)

    2) the MonoTouch team added an attribute to stop the compiler doing this - but I haven't tested it yet (and I'm not even sure if they ended up going with [Serializable] or [Preserve]) which you would need to decorate your class with

    3) there is a switch you can pass to the compiler -linksdkonly in MonoDevelop under Options-iPhone Build-mtouch arguments (courtesy of @simongui). This tells the compiler not to optimize away stuff *your* code.

    HTH

    ReplyDelete
  5. That was it. Thank you.

    ReplyDelete
  6. Do you have an example of inserting into the phonebook using the SQLiteClient.cs?

    ReplyDelete
  7. Craig,
    I tried your example, and although it works, the Application Output gives an error which looks like... 'CorporateDirectoy1(904,0x...) malloc: ***error for object 0x...: pointer being freed was not allocated'

    I adapted some of the code for use in an application i'm developing, and found I didn't have that problem, so I ignored it until upgrading to Snow Leopard and the latest release of the iPhone SDK, at whcih point my app gives me a shed load of those messages and so does your sample. Which is unfortunate.

    I was wondering if you could offer any advice?

    ReplyDelete
  8. @Owen Hadn't noticed that - next time I run up that project I'll look into it. Did you notice whether it happens on both the simulator and device (or just one of them)?

    @Quan - will look at adding some more SQL operations to the example.

    ReplyDelete
  9. @Craig Unfortunately I don't have an iPhone yet, i'm still awaiting delivery, so I've only been able to test on the simulator

    ReplyDelete
  10. Do you have an example that uses a SearchBar with the UISearchDisplayController? i.e. wanting to search through the phone book for a specific person.

    ReplyDelete
  11. pardon my stupidity, I'm getting an error "table not found". I have added the sqlite file to the the project directory, the part I don't get is "included with the application as a 'Content' file within the MonoTouch project.", how do you do that?? I'm new to monodevelop...

    ReplyDelete
  12. @grant - not yet, but it is something I've been thinking about adding. Frank has released a cleaned up, feature added version of the Sqlite wrapper which you might like to try.

    @eddie - Make sure the file is 'in' your project structure (ie. right-click on the project file-Add Files...) then select the file and go to the Properties window and in the Build Action property select "Content".

    ReplyDelete
  13. Is there a reason why my app can't run Update SQL on a device, but it can in the simulator?

    ReplyDelete
  14. Ian - perhaps you could share the exact error you are seeing (both the code you've written and the Output from MonoDevelop). Are you using the _latest_ version of Frank's code?
    It is difficult to comment further without some concrete information on your implementation.

    ReplyDelete
  15. much later then the last post, but have also a question.
    Everything works, I can return all my (3) records out of a table in my database, but then I receive 9times a " .... malloc: *** error for object 0xb346800: pointer being freed was not allocated" , everytime an other object.. What to do?

    ReplyDelete
  16. edit last post:

    it happens on rule 40 of SQLiteCommand
    " cols[i] = MatchColProp (SQLite3.ColumnName (stmt, i), props); "

    have no idea how to fix this?

    ReplyDelete
  17. Hi there,

    this code is very old - looks like it doesn't behave nicely with the latest version of MonoTouch.

    I'm going to upload a new version, but to fix for yourself:

    1) get the latest SQLite code from
    https://github.com/xamarin/mobile-samples/blob/TheNewTasky/Tasky/Tasky.Core/SQLite.cs
    and update the namespace to match

    2) remove the .Open() methods - they're no longer required

    3) in Main.cs add fields for the delegates
    EmployeeDataSource dataSource;
    EmployeeListDelegate @delegate;
    UIAlertViewDelegate alertDelegate;

    4) in Main.cs FinishedLaunching set the delegates and use the variables instead
    dataSource = new EmployeeDataSource(this);
    @delegate = new EmployeeListDelegate(this);
    tableviewEmployee.DataSource = dataSource;
    tableviewEmployee.Delegate = @delegate;
    alertDelegate = new CallAlert (this);

    Good luck. I'll update the post when I get the new code uploaded.

    ReplyDelete
  18. thx for the answer, but still have some issues.
    1) I use this http://conceptdevelopment.net/iPhone/CorpPhonebook01/SQLiteClient.cs.htm code in stead of the big one you showed me. I'm not using mapping so your file looked much better for me.
    2) I'm only yet testing in the console. So no gui elements, only on the console. So I guess my fault could not be in this as well. It only returns this pointer being freed was not allocated when I'm debugging and when I get to this line of code..

    ReplyDelete
  19. The SQLite.cs code you are using is from 2009 - it's almost 3 years old - you should update to the latest code.

    ReplyDelete
  20. I updated the sqlite code I found here on your page and changed here and there some code in comparisation with this you gave me in previous post. Every thing works now without that little b*st*rd of a pointer error :D

    Thanks for your time and responses!

    ReplyDelete