I'm quite new in xamarin and have a task to create some kind of personal organiser on Android. Main feature is creating notes, todos, events in different activities and then save, delete, edit in various screens too. For this reason, I use SQLiteOpenHelper to create database in service to have an access from each activity and then call methods from it. My service is hybrid so i bind in each activity while it works. Unfortunately, SQLiteOpenHelper.OnCreate doesn't call. I logged most of possible mistaken parts and it showed a miss in creating DB. Here is my code.
Services.cs
using Android.Util;
using Android.OS;
using Android.Content;
using Android.App;
using FinallyApp;
using Android.Database;
using OrgDatabase1;
using Android.Database.Sqlite;
using System;
namespace DatabaseService
{
[Service(Name = "com.xamarin.DBservice")]
public class DBConnectService : Service
{
DatabaseHelper DbHelper;
SQLiteDatabase db;
public IBinder Binder { get; private set; }
public override StartCommandResult OnStartCommand(Intent intent, StartCommandFlags flags, int startId)
{
Log.Debug("DemoService", "DemoService started");
return StartCommandResult.NotSticky;
}
public override void OnCreate()
{
// This method is optional to implement
base.OnCreate();
DbHelper = new DatabaseHelper(this);
Log.Debug("INSERVICE", "OncreateService");
db = DbHelper.ReadableDatabase;
}
public override IBinder OnBind(Intent intent)
{
// This method must always be implemented
Log.Debug("Service.OnBind", "OnBinded");
this.Binder = new DBConnectBinder(this);
return this.Binder;
}
public override bool OnUnbind(Intent intent)
{
// This method is optional to implement
return base.OnUnbind(intent);
}
public override void OnDestroy()
{
// This method is optional to implement
Binder = null;
base.OnDestroy();
}
public ICursor GetTable(string table_name)
{
switch (table_name)
{
case DatabaseHelper.NOTE_TABLE:
string[] projection = { DatabaseHelper.COLUMN_NAME, DatabaseHelper.COLUMN_DESCRIPTION, DatabaseHelper.COLUMN_CREATION_DATE };
var cursor = db.Query(DatabaseHelper.NOTE_TABLE, projection, null, null, null, null, null);
return cursor;
case DatabaseHelper.EVENT_TABLE:
return null;
case DatabaseHelper.TODO_TABLE:
return null;
}
return null;
}
public ContentValues GetItem(string table_name, int position)
{
switch (table_name)
{
case DatabaseHelper.NOTE_TABLE:
string[] projection = { DatabaseHelper.COLUMN_NAME, DatabaseHelper.COLUMN_DESCRIPTION, DatabaseHelper.COLUMN_CREATION_DATE };
var cursor = db.Query(DatabaseHelper.NOTE_TABLE, projection, null, null, null, null, null);
cursor.MoveToPosition(position);
using (ContentValues item = new ContentValues())
{
item.Put(DatabaseHelper.COLUMN_NAME, cursor.GetString(cursor.GetColumnIndex(DatabaseHelper.COLUMN_NAME)));
item.Put(DatabaseHelper.COLUMN_DESCRIPTION, cursor.GetString(cursor.GetColumnIndex(DatabaseHelper.COLUMN_DESCRIPTION)));
item.Put(DatabaseHelper.COLUMN_CREATION_DATE, cursor.GetString(cursor.GetColumnIndex(DatabaseHelper.COLUMN_CREATION_DATE)));
item.Put(DatabaseHelper.COLUMN_ID, cursor.GetInt(cursor.GetColumnIndex(DatabaseHelper.COLUMN_ID)));
return item;
}
case DatabaseHelper.EVENT_TABLE:
return null;
case DatabaseHelper.TODO_TABLE:
return null;
}
return null;
}
public void InsertNote(string name, string description)
{
using (ContentValues contentV = new ContentValues())
{
contentV.Put(DatabaseHelper.COLUMN_NAME, name);
contentV.Put(DatabaseHelper.COLUMN_DESCRIPTION, description);
contentV.Put(DatabaseHelper.COLUMN_CREATION_DATE, DateTime.Now.ToString("g"));
db.Insert(DatabaseHelper.COLUMN_NAME, null, contentV);
}
}
public void DeleteItem(string table_name, int id)
{
db.Delete(table_name, DatabaseHelper.COLUMN_ID + "=" + id.ToString(), null);
}
public void UpdateItem(string table_name, int id, ContentValues values)
{
db.Update(table_name, values, DatabaseHelper.COLUMN_ID + "=" + id.ToString(), null);
}
}
public class DBConnectBinder : Binder
{
public DBConnectBinder(DBConnectService service)
{
this.Service = service;
}
public DBConnectService Service { get; private set; }
public ICursor GetTable(string table_name)
{
return Service.GetTable(table_name);
}
public ContentValues GetItem(string table_name, int position)
{
return Service.GetItem(table_name, position);
}
public void InsertNote(string name, string description)
{
Service.InsertNote(name, description);
}
public void DeleteItem(string table_name, int id)
{
Service.DeleteItem(table_name, id);
}
public void UpdateItem(string table_name, int id, ContentValues values)
{
Service.UpdateItem(table_name, id, values);
}
}
public class DBConnectionServiceConnection : Java.Lang.Object, IServiceConnection
{
public DBConnectionServiceConnection(Activity activity)
{
IsConnected = false;
Binder = null;
this.activity = activity;
}
private Activity activity;
public bool IsConnected { get; private set; }
public DBConnectBinder Binder { get; private set; }
public void OnServiceConnected(ComponentName name, IBinder service)
{
Binder = service as DBConnectBinder;
IsConnected = this.Binder != null;
Log.Debug("OnConnected", "Connected");
if (IsConnected)
{
Log.Debug("IsConnected", "true");
if (activity as MainActivity != null)
{
MainActivity act = (MainActivity)activity;
act.GetDataFromService();
}
else if (activity as MainActivity != null)
{
NoteItemPageActivity act = (NoteItemPageActivity)activity;
act.GetItemFromService();
}
else if (activity as CreationActivity != null)
{
CreationActivity act = (CreationActivity)activity;
act.PrepareForCreate();
}
}
}
public void OnServiceDisconnected(ComponentName name)
{
IsConnected = false;
Binder = null;
}
}
}
MainActivity.cs
using Android.App;
using Android.Content;
using Android.OS;
using Android.Runtime;
using Android.Support.Design.Widget;
using Android.Support.V7.App;
using Android.Views;
using Android.Widget;
using Android.Support.V7.Widget;
using OrgDatabase1;
using Android.Database;
using Android.Database.Sqlite;
using Toolbar = Android.Support.V7.Widget.Toolbar;
using DatabaseService;
using Android.Util;
namespace FinallyApp
{
[Activity(MainLauncher = true)]
public class MainActivity : AppCompatActivity, BottomNavigationView.IOnNavigationItemSelectedListener
{
TextView textMessage;
BottomNavigationView navigation;
ICursor cursor;
RecyclerView mRecyclerView;
RecyclerView.LayoutManager mLayoutManager;
NoteAlbumAdapter mAdapter;
Toolbar toolbar;
DBConnectionServiceConnection Dbconnection;
protected override void OnCreate(Bundle bundle)
{
base.OnCreate(bundle);
Xamarin.Essentials.Platform.Init(this, bundle);
SetContentView(Resource.Layout.activity_main);
textMessage = FindViewById<TextView>(Resource.Id.message);
navigation = FindViewById<BottomNavigationView>(Resource.Id.navigation);
navigation.SetOnNavigationItemSelectedListener(this);
mRecyclerView = FindViewById<RecyclerView>(Resource.Id.recyclerView);
mLayoutManager = new LinearLayoutManager(this);
mRecyclerView.SetLayoutManager(mLayoutManager);
StartService(new Intent(this, typeof(DBConnectService)));
toolbar = FindViewById<Toolbar>(Resource.Id.toolbar);
SetSupportActionBar(toolbar);
toolbar.SetTitle(Resource.String.title_note);
}
protected override void OnStart()
{
base.OnStart();
if (Dbconnection == null)
{
this.Dbconnection = new DBConnectionServiceConnection(this);
}
Intent serviceToStart = new Intent(this, typeof(DBConnectService));
BindService(serviceToStart, this.Dbconnection, Bind.AutoCreate);
}
public void GetDataFromService()
{
Log.Debug("status", "InActivity");
if (cursor == null)
{
Log.Debug("status", "ifCursorNull");
cursor = Dbconnection.Binder.GetTable(DatabaseHelper.NOTE_TABLE);
mAdapter = new NoteAlbumAdapter(cursor);
mAdapter.ItemClick += OnNoteItemClick;
mRecyclerView.SetAdapter(mAdapter);
}
else mAdapter.NotifyDataSetChanged();
}
protected override void OnResume()
{
base.OnResume();
}
void OnNoteItemClick(object sender, int position)
{
var intent = new Intent(this, typeof(NoteItemPageActivity));
intent.PutExtra("position", position);
StartActivity(intent);
}
public override void OnRequestPermissionsResult(int requestCode, string[] permissions, [GeneratedEnum] Android.Content.PM.Permission[] grantResults)
{
Xamarin.Essentials.Platform.OnRequestPermissionsResult(requestCode, permissions, grantResults);
base.OnRequestPermissionsResult(requestCode, permissions, grantResults);
}
public bool OnNavigationItemSelected(IMenuItem item)
{
switch (item.ItemId)
{
case Resource.Id.navigation_note:
textMessage.SetText(Resource.String.title_note);
toolbar.SetTitle(Resource.String.title_note);
toolbar.SetBackgroundResource(Resource.Color.NoteMainColor);
return true;
case Resource.Id.navigation_todo:
textMessage.SetText(Resource.String.title_todo);
toolbar.SetTitle(Resource.String.title_todo);
toolbar.SetBackgroundResource(Resource.Color.TodoMainColor);
return true;
case Resource.Id.navigation_event:
textMessage.SetText(Resource.String.title_event);
toolbar.SetTitle(Resource.String.title_event);
toolbar.SetBackgroundResource(Resource.Color.EventMainColor);
return true;
}
return false;
}
protected override void OnSaveInstanceState(Bundle savedInstanceState)
{
base.OnSaveInstanceState(savedInstanceState);
}
protected override void OnRestoreInstanceState(Bundle savedInstanceState)
{
base.OnRestoreInstanceState(savedInstanceState);
mAdapter.NotifyDataSetChanged();
}
public override bool OnCreateOptionsMenu(IMenu menu)
{
MenuInflater.Inflate(Resource.Menu.top_menus, menu);
var delete_item = menu.FindItem(Resource.Id.menu_delete);
delete_item.SetVisible(false);
var edit_item = menu.FindItem(Resource.Id.menu_edit);
edit_item.SetVisible(false);
var save_item = menu.FindItem(Resource.Id.menu_save);
save_item.SetVisible(false);
var cancel_item = menu.FindItem(Resource.Id.menu_cancel);
cancel_item.SetVisible(false);
return base.OnCreateOptionsMenu(menu);
}
public override bool OnOptionsItemSelected(IMenuItem item)
{
switch (item.ItemId)
{
case Resource.Id.menu_create:
var current_section = navigation.SelectedItemId;
switch (current_section)
{
case Resource.Id.navigation_note:
var intent = new Intent(this, typeof(CreationActivity));
intent.PutExtra("CreationType", "Note");
StartActivity(intent);
return true;
case Resource.Id.navigation_todo:
var intent2 = new Intent(this, typeof(CreationActivity));
intent2.PutExtra("CreationType", "Todo");
StartActivity(intent2);
return true;
case Resource.Id.navigation_event:
var intent3 = new Intent(this, typeof(CreationActivity));
intent3.PutExtra("CreationType", "Event");
StartActivity(intent3);
return true;
}
return true;
}
return base.OnOptionsItemSelected(item);
}
protected override void OnStop()
{
UnbindService(Dbconnection);
base.OnStop();
}
protected override void OnDestroy()
{
StopService(new Intent(this, typeof(DBConnectService)));
base.OnDestroy();
}
}
}
SQLite.cs
using Mono.Data.Sqlite;
using System.Collections.Generic;
using System.Threading.Tasks;
using System;
using System.IO;
using System.Data;
using Android.Database.Sqlite;
using Android.Database;
using Android.Content;
using Android.Util;
namespace OrgDatabase1
{
public class DatabaseHelper : SQLiteOpenHelper
{
public const string NOTE_TABLE = "Notes";
public const string EVENT_TABLE = "Events";
public const string TODO_TABLE = "Todos";
public const string COLUMN_ID = "_id";
public const string COLUMN_NAME = "name";
public const string COLUMN_CREATION_DATE = "creation_date";
public const string COLUMN_DESCRIPTION = "description";
public const string COLUMN_DEADLINE = "deadline";
public static readonly string DbName = "database.db";
public static readonly int DatabaseVersion = 1;
public DatabaseHelper(Context context) : base(context, DbName, null, DatabaseVersion) { }
public override void OnCreate(SQLiteDatabase db)
{
db.ExecSQL("CREATE TABLE [" + NOTE_TABLE + "] ([" + COLUMN_ID + "] INTEGER PRIMARY KEY AUTOINCREMENT, [" + COLUMN_NAME + "] TEXT NOT NULL, [" + COLUMN_DESCRIPTION + "] TEXT, [" + COLUMN_CREATION_DATE + "] TEXT NOT NULL);");
Log.Debug("DATABASE","Access");
}
public override void OnUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
}
}
}
Thanks.
