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

In the previous post https://sabitlabscode.wordpress.com/2014/01/15/xamarin-android-make-crud-operation-with-sqllite-1/, we have been learn about how to show list data from sqllite. In this post we will learn how to add new data and update data.


From the same application (from previous post), we will make and update data activity. Update activity will show when user click one data in the listview. This is the step :

– create a code that will run when we select a list data. Insert this code on your activity :

         protected override void OnListItemClick (ListView l, View v, int position, long id)
             var selected = (Note)ListAdapter.GetItem (position); // get data that we select
             var intent = new Intent (this, typeof (DetailActivity)); // call new activity "DetailActivity"
             intent.PutExtra ("id", selected.Id);  // sent data to "DetailActivity"
             StartActivityForResult (intent, 0); // start new activity

– create new layout for “DetailActivity”, named it with “detail.axml”. Insert this code :

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

– create new activity named it with “DetailActivity” and insert this code :

     public class DetailActivity : Activity
         private Note current_data;  // define Note class to set data that we open
         private EditText ttitle;   // define EditText to set title text
         private EditText tbody;    // define EditText to set body text
         protected override async void OnCreate (Bundle bundle)
             base.OnCreate (bundle);

             SetContentView (Resource.Layout.detail);  // set layout with detail.axml that we set before
             ttitle = FindViewById<EditText> (Resource.Id.editText1);   // set ttitle with EditText from editText1 field
             tbody = FindViewById<EditText> (Resource.Id.editText2);  // set tbody with EditText from editText2 field
             var tbutton = FindViewById<Button> (Resource.Id.button1);  // define tbutton to catch button1 from form

             var id = Intent.GetLongExtra ("id", -1);  // define id with get data that sent, if not exist set it with "-1"

             if (id < 0) // if id < 0 then define current data with empty Note
                 current_data = new Note ();
             else  // else set it by id with call function from SqlliteClass
                 current_data = await SqlLiteClass.GetNoteAsync (id);

             tbutton.Click += async (sender, e) => {  // when tbutton click
                 if (ttitle.Text.Length == 0)  // check if ttitle blank or no, if blank dont do execution

                 // set current data from form value
                 current_data.Title = ttitle.Text;
                 current_data.Body = tbody.Text;

                 await SqlLiteClass.SaveNoteAsync (current_data); // save data
                 var listactivity=new Intent(this,typeof(MainActivity));  //after save data, call MainActivity
                 StartActivity(listactivity); // start main activity

         protected override void OnResume ()
             base.OnResume ();
             ttitle.SetTextKeepState(current_data.Title);// set ttitle with current_data.Title
             tbody.SetTextKeepState (current_data.Body); // set tbody with current_data.Body

– You can see we call function from SqlLiteClass SaveNoteSync and GetNoteAsync, we will create that function in my SqlLiteClass. Insert this code in your sql lite class :

         public static async Task<Note> GetNoteAsync (long id)
             var sql = string.Format ("SELECT * FROM MYNOTE WHERE Id = {0};", id);  // select a note by id
             using (var conn = GetConnection ()) {  // get connection 
                 await conn.OpenAsync ();

                 using (var cmd = conn.CreateCommand ()) {
                     cmd.CommandText = sql;

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

         public static async Task SaveNoteAsync (Note note) // save note
             using (var conn = GetConnection ()) {
                 await conn.OpenAsync ();

                 using (var cmd = conn.CreateCommand ()) {

                     if (note.Id < 0) { // if id < 0 do insert, else do update
                         // command to insert data
                         cmd.CommandText = "INSERT INTO MYNOTE (Title, Body, Modified) VALUES (@Title, @Body, @Modified); SELECT last_insert_rowid();";
                         // define parameter
                         cmd.Parameters.AddWithValue ("@Title", note.Title);
                         cmd.Parameters.AddWithValue ("@Body", note.Body);
                         cmd.Parameters.AddWithValue ("@Modified", DateTime.Now);

                         note.Id = (long)await cmd.ExecuteScalarAsync ();
                     } else {
                         // define update command and set parameter
                         cmd.CommandText = "UPDATE MYNOTE SET Title=@Title, Body = @Body, Modified = @Modified WHERE Id = @Id";
                         cmd.Parameters.AddWithValue ("@Id", note.Id);
                         cmd.Parameters.AddWithValue ("@Title", note.Title);
                         cmd.Parameters.AddWithValue ("@Body", note.Body);
                         cmd.Parameters.AddWithValue ("@Modified", DateTime.Now);

                         await cmd.ExecuteNonQueryAsync ();

Okey, it finsih for update. Try to click one of the data and it will display like this :

How about insert data?

Well, you can do insert data with update activity that we have create before. You can see in the code, i give some condition “if(id<0)”. Its use for check the activity that we run an update or insert data. But, even we can do it directly from previous form we create, we need to make a “menu” that link to “DetailActivity”. This is the step :

– create menu for insert data. Insert this code in MainActivity :

         public const int MENU_ITEM_DELETE = Menu.First; // will use for delete menu
         public const int MENU_ITEM_INSERT = Menu.First + 1; // set item insert

         public override bool OnCreateOptionsMenu (IMenu menu) // create options/menu in mainActivity
             base.OnCreateOptionsMenu (menu);

             // add new menu insert
             menu.Add (0, MENU_ITEM_INSERT, 0, Resource.String.menu_insert)
                 .SetShortcut ('3', 'a');

             return true;

         // set function will do when we select menu
         public override bool OnOptionsItemSelected (IMenuItem item)
             // check what menu we select
             switch (item.ItemId) {
             // if we select menu for insert
             case MENU_ITEM_INSERT:
                 // call detailActivity
                 var intent = new Intent (this, typeof (DetailActivity));
                 // set id with "-1" value, it means "id < 0"
                 intent.PutExtra ("id", -1);

                 StartActivityForResult (intent, 0); call activity
                 return true;

             return base.OnOptionsItemSelected (item);

After do that, run your application and it will display this :



You can see “Insert” options on list menu. And when you click it, it will show a form with empty title and body.

Finish.. Hope it help..

Happy coding…


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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: