[Xamarin – Android] Make CRUD Operation With Sqllite (2)
January 16, 2014 Leave a comment
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 :
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…
Sabitlabscode 
Komentar