[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.

UPDATE DATE

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:orientation="vertical"
     android:layout_width="fill_parent"
     android:layout_height="fill_parent">
     <EditText
         android:layout_width="match_parent"
         android:layout_height="wrap_content"
         android:id="@+id/editText1" />
     <TextView
         android:text="Detail"
         android:textAppearance="?android:attr/textAppearanceLarge"
         android:layout_width="match_parent"
         android:layout_height="41.6dp"
         android:id="@+id/textView1" />
     <EditText
         android:layout_width="match_parent"
         android:layout_height="328.0dp"
         android:id="@+id/editText2" />
     <Button
         android:text="Save"
         android:layout_width="match_parent"
         android:layout_height="wrap_content"
         android:id="@+id/button1" />
 </LinearLayout>

– 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
                     return;

                 // 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));
                         else
                             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 :
Screenshot_2014-01-16-09-27-28

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 :

Screenshot_2014-01-16-09-53-07

Screenshot_2014-01-16-09-49-18

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…

Advertisements

[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:orientation="horizontal"
     android:layout_width="fill_parent"
     android:layout_height="fill_parent"
     android:minWidth="25px"
     android:minHeight="25px">
     <LinearLayout
         android:layout_width="fill_parent"
         android:layout_height="wrap_content"
         android:orientation="vertical"
         android:paddingTop="6px">
         <TextView
             android:layout_width="match_parent"
             android:layout_height="wrap_content"
             android:id="@+id/title" />
     </LinearLayout>
 </LinearLayout>

– 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 :

1

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

Finish.. Hope it help..

Happy coding..

[Xamarin – Android] Work With Multi Activity

This is simple example how to work with multi activity. We have MainActivity in default, and need to make another activity. Right click on your project, select menu “Add” and “New File”. In left menu select “Android” and select “Android Activity” in center menu. You can set make a new layout for that activity with add new layout in “Resources-layout-yourNameLayout.axml”. You can call that layout with edit onCreate function in your new Activity :

         protected override void OnCreate (Bundle bundle)
         {
             base.OnCreate (bundle);
             SetContentView (Resource.Layout.yourNameLayout); // this is code to set you layout
         }

From previous post application (https://sabitlabscode.wordpress.com/2014/01/13/xamarin-android-create-hello-world/), i will call second activity when we click to button in the launch activity. You can change this :

 
 protected override void OnCreate (Bundle bundle)
         {
             base.OnCreate (bundle);
             SetContentView (Resource.Layout.Main);

             // Get our button from the layout resource,
             // and attach an event to it
             Button button = FindViewById<Button> (Resource.Id.myButton);

             button.Click += delegate {
                 // this is old code
                 //button.Text = string.Format ("Hello World");

                 //change like this :
                 var second=new Intent(this,typeof(YourNewActivity));  // define your new activity
                 // if you want to pass data from current activity to another activity, do this
                 // in new activity you can call with with name "data1" (like code below)
                 second.PutExtra("data1","This is data 1");  
                 // call activity
                 StartActivity(second);
             };
         }

Finish.. Hope it help..
Happy coding..

[Xamarin – Android] Create Hello World

Recently when i open internet i often see ad about Xamarin, and i think about do an android project with this software. Okey, im very new learn about xamarin. But i think its good to share what i learn about xamarin.

Xamarin’s not free, but as starter you can use it for free. See this link to know about license https://store.xamarin.com/. If its not free, why you must use this? I dont know, but i want to know how C# performance when run android apps and for now i just interest with this tools, but you can see here http://xamarin.com/how-it-works to know why you must use xamarin.

The first think i learn (and always in other programming learn) is how to create hello world app.

– After you installed xamarin, open your xamarin studio.

– Select “new”, and in “C#” menu you must select “Android” menu and select “Android Application” (dont forget to set application name). Select “OK”.

– In your project, open “MainActivity.cs”. You will see this code :

  [Activity (Label = "hello", MainLauncher = true)]  // define this activity is launch activity
     public class MainActivity : Activity  // define class
     {
         int count = 1;

         protected override void OnCreate (Bundle bundle)  // define function when activity create
         {
             base.OnCreate (bundle);

             // Set our view from the "main" layout resource
             SetContentView (Resource.Layout.Main);

             // Get our button from the layout resource,
             // and attach an event to it
             Button button = FindViewById<Button> (Resource.Id.myButton);

             button.Click += delegate {
                 button.Text = string.Format ("{0} clicks!", count++);
             };
         }
     }

That code will show  a button and when you click the button will show total click you click. Where the button come from? see code in “Resource/layout/Main.axml”. So change that code like this :

  [Activity (Label = "hello", MainLauncher = true)]
     public class MainActivity : Activity
     {
         protected override void OnCreate (Bundle bundle)
         {
             base.OnCreate (bundle);

             // Set our view from the "main" layout resource
             SetContentView (Resource.Layout.Main);

             // Get our button from the layout resource,
             // and attach an event to it
             Button button = FindViewById<Button> (Resource.Id.myButton);

             button.Click += delegate {
                 button.Text = string.Format ("Hello World");
             };
         }
     }

save it..
Run your application.. you will see this :1

2Finish… hope it help..

Happy coding…

%d bloggers like this: