[Xamarin – Android] Make CRUD Operation With Sqllite (1)

We will learn step to make CRUD (Create, Read, Update, Delete) operation in android with xamarin and use sqllite database. You can learn more about xamarin with see example app in xamarin android sample here http://docs.xamarin.com/samples/android/all/, and you can see example for this topic (sqllite) here http://docs.xamarin.com/samples/NotePad-Mono.Data.Sqlite/.

In your application, dont forget to define sqllite class with call
using Mono.Data.Sqlite;

then, create a class to define your connection and your database operation. Okey, just create new class with name “SqlliteClass.cs”. Insert this code :

         // define name of sqllite database
         private static string db_file = "litedata.db3";

         // get connection from sqllite
         private static SqliteConnection GetConnection ()
             var dbPath = Path.Combine (Environment.GetFolderPath (Environment.SpecialFolder.Personal), db_file);
             bool exists = File.Exists (dbPath);
             if (!exists)  // if file not exist, create new file
                 SqliteConnection.CreateFile (dbPath);

             var conn = new SqliteConnection ("Data Source=" + dbPath);

             if (!exists) // if its new file/database, call function CreateDatabase
                 CreateDatabase (conn);

             return conn;

         // this function use for create new database
         private static void CreateDatabase (SqliteConnection connection)
             // command to create new table
             var sql = "CREATE TABLE MYNOTE (Id INTEGER PRIMARY KEY AUTOINCREMENT, title VARCHAR(255), Body ntext, Modified datetime);";

             connection.Open (); // open connection

             using (var cmd = connection.CreateCommand ()) {
                 cmd.CommandText = sql;   // set command with string in sql
                 cmd.ExecuteNonQuery ();   // execute

             // create new data in table MYNOTE
             sql = "INSERT INTO MYNOTE (title, Body, Modified) VALUES (@title, @Body, @Modified);";

             using (var cmd = connection.CreateCommand ()) {
                 // insert command that will execute and insert some parameter
                 cmd.CommandText = sql;
                 cmd.Parameters.AddWithValue ("@title", "Sample Title");
                 cmd.Parameters.AddWithValue ("@Body", "Sample Body Note");
                 cmd.Parameters.AddWithValue ("@Modified", DateTime.Now);

                 cmd.ExecuteNonQuery ();

             connection.Close ();

We have been created a class to call connection and set connection with a table if it doesnt exist before. Now, we will try to show all data in first activity. I will show it in listview, so i will set my layout with listview. This is step to show list data in our activity :

– make new layout, ex : listlayout.axml and insert make like this :

  <?xml version="1.0" encoding="utf-8"?>
 <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
             android:id="@+id/title" />

– in sqlliteclass, create function to call all data

                public static IEnumerable GetAllNotes ()
			var sql = "SELECT * FROM MYNOTE;";
			using (var conn = GetConnection ()) {
				conn.Open ();

				using (var cmd = conn.CreateCommand ()) {
					cmd.CommandText = sql;
					using (var reader = cmd.ExecuteReader ()) {
						while (reader.Read ())
							yield return new Note (reader.GetInt32 (0), reader.GetString (1), reader.GetString (1),reader.GetDateTime (3));

		public static async Task<Note[]> GetAllNotesAsync()
			var sql = "SELECT * FROM MYNOTE;";
			List notes = new List ();
			using (var conn = GetConnection ()) {
				await conn.OpenAsync ();
				using (var cmd = conn.CreateCommand ()) {
					cmd.CommandText = sql;

					using (var reader = await cmd.ExecuteReaderAsync ()) {
						while (await reader.ReadAsync ())
							notes.Add (new Note (reader.GetInt32 (0),reader.GetString (1),  reader.GetString (2), reader.GetDateTime (3)));
			return notes.ToArray ();

– make your activity like this :

         protected override async void OnCreate (Bundle bundle)
             base.OnCreate (bundle);
             ListView.SetOnCreateContextMenuListener (this);
             await NoteSync();

          async Task NoteSync()
             var data = await SqlLiteClass.GetAllNotesAsync ();
             var adapeter=new NoteAdapter (this, this, Resource.Layout.listlayout, data); // call NoteAdapter Class
             ListAdapter = adapeter;

– You can see we have NoteAdapter class. That class use for define where and why data from query will display in application. So, create new class name NoteAdapter and insert this code to that class :

         private Activity activity;
         public NoteAdapter(Activity activity, Context context, int layoutId, object[] dobject) 
             : base(context, layoutId, dobject)
             this.activity = activity;

         public override View GetView (int position, View convertView, ViewGroup parent)
             var item = (Note)this.GetItem (position); // get current data and define it in "item" variable, but
             // convert it to type "Note" before we do that
             var view = (convertView ?? activity.LayoutInflater.Inflate (Resource.Layout.second, parent, false)) as LinearLayout;
             // set data we want to show with layout
             view.FindViewById<TextView> (Resource.Id.title).Text = item.Title;

             return view;

– Again, we have a new type “Note”. Note is one of our class that use for make us easy maintain data in table MYNOTE with code. Create new class with name “Note” and insert this code :

     class Note : Java.Lang.Object  // dont forget, this is java.lang.object
         public long Id { get; set; }
         public string Title{ get; set;}
         public string Body { get; set; }
         public DateTime ModifiedTime { get; set; }

         public Note ()
             Id = -1;
             Title = string.Empty;
             Body = string.Empty;

         public Note (long id, string title, string body, DateTime modified)
             Id = id;
             Title = title;
             Body = body;
             ModifiedTime = modified;

         public override string ToString ()
             return ModifiedTime.ToString ();

Finish, if you run your application you will see this :


Actually you will see only one data. Second data is from insert proccess that i will past later..

Finish.. Hope it help..

Happy coding..

One Response to [Xamarin – Android] Make CRUD Operation With Sqllite (1)

  1. Pingback: [Xamarin - Android] Make CRUD Operation With Sqllite (2) | Sabitlabscode

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: