Xamarin.Android 入门实例（4）之实现对 SQLLite 进行添加/修改/删除/查询操作

1.Main.axml

 

<?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">
    <TableLayout
        android:minWidth="25px"
        android:minHeight="25px"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_gravity="center_horizontal"
        android:id="@+id/tableLayout1">
        <TableRow
            android:background="@android:drawable/bottom_bar"
            android:id="@+id/tableRow2"
            android:layout_height="40dp">
            <TextView
                android:text="姓名"
                android:textAppearance="?android:attr/textAppearanceSmall"
                android:layout_width="34.0dp"
                android:layout_height="23.3dp"
                android:id="@+id/tvName"
                android:layout_marginRight="0.0dp"
                android:layout_column="0"
                android:layout_marginTop="20dp"
                android:textColor="#fff" />
            <EditText
                android:inputType="textPersonName"
                android:layout_width="80px"
                android:layout_height="wrap_content"
                android:id="@+id/txtName"
                android:layout_gravity="center_vertical"
                android:layout_column="1" />
            <TextView
                android:text="年龄"
                android:textAppearance="?android:attr/textAppearanceSmall"
                android:layout_width="30.7dp"
                android:layout_height="24.0dp"
                android:id="@+id/tvAge"
                android:layout_marginTop="20dp"
                android:textColor="#ffffff" />
            <EditText
                android:inputType="number"
                android:layout_width="50dp"
                android:layout_height="wrap_content"
                android:id="@+id/txtAge"
                android:layout_gravity="center_vertical" />
            <TextView
                android:text="国家"
                android:textAppearance="?android:attr/textAppearanceSmall"
                android:layout_width="33.3dp"
                android:layout_height="22.7dp"
                android:id="@+id/tvCountry"
                android:layout_marginRight="0.0dp"
                android:layout_marginTop="20dp"
                android:textColor="#fff" />
            <EditText
                android:layout_width="100dp"
                android:layout_height="wrap_content"
                android:id="@+id/txtCountry"
                android:layout_gravity="center_vertical"
                android:layout_marginRight="0dp" />
        </TableRow>
        <TableRow
            android:id="@+id/tableRow3">
            <TextView
                android:layout_width="fill_parent"
                android:layout_height="wrap_content"
                android:id="@+id/tvMsg"
                android:layout_column="1" />
        </TableRow>
    </TableLayout>
    <LinearLayout
        android:orientation="horizontal"
        android:layout_width="fill_parent"
        android:layout_height="40dp"
        android:paddingLeft="10dp"
        android:id="@+id/llButtons"
        android:background="@android:drawable/bottom_bar"
        android:layout_marginBottom="0dp">
        <ImageButton
            android:src="@drawable/add"
            android:layout_width="75px"
            android:paddingLeft="0dp"
            android:layout_height="wrap_content"
            android:background="@android:color/transparent"
            android:id="@+id/imgAdd"
            android:layout_marginTop="0.0dp"
            android:paddingTop="0dp"
            android:maxHeight="32px"
            android:maxWidth="32px"
            android:minHeight="32px"
            android:minWidth="32px" />
        <ImageButton
            android:src="@drawable/edit"
            android:layout_width="75px"
            android:paddingLeft="0dp"
            android:layout_height="wrap_content"
            android:background="@android:color/transparent"
            android:id="@+id/imgEdit"
            android:paddingTop="0dp" />
        <ImageButton
            android:src="@drawable/delete"
            android:layout_width="75px"
            android:paddingLeft="0dp"
            android:layout_height="wrap_content"
            android:background="@android:color/transparent"
            android:id="@+id/imgDelete"
            android:paddingTop="0dp" />
        <ImageButton
            android:src="@drawable/find"
            android:layout_width="75px"
            android:paddingLeft="0dp"
            android:layout_height="wrap_content"
            android:background="@android:color/transparent"
            android:id="@+id/imgFind"
            android:paddingTop="0dp" />
    </LinearLayout>
    <LinearLayout
        android:orientation="horizontal"
        android:minWidth="25px"
        android:minHeight="25px"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:paddingLeft="10dp"
        android:id="@+id/llHeader">
        <TextView
            android:text="编号"
            android:layout_width="66.7dp"
            android:layout_height="wrap_content"
            android:id="@+id/tvIdShowR"
            android:textColor="@android:color/white" />
        <TextView
            android:text="姓名"
            android:layout_width="72.0dp"
            android:layout_height="wrap_content"
            android:textColor="@android:color/white"
            android:id="@+id/tvPersonShowR"
            android:layout_marginLeft="20dp"
            android:layout_marginRight="0.0dp" />
        <TextView
            android:text="年龄"
            android:layout_width="58.7dp"
            android:layout_height="wrap_content"
            android:textColor="@android:color/white"
            android:id="@+id/tvAgeShowR" />
        <TextView
            android:text="国家"
            android:layout_width="65.3dp"
            android:layout_height="wrap_content"
            android:textColor="@android:color/white"
            android:id="@+id/tvCountryShowR"
            android:layout_marginLeft="20dp" />
    </LinearLayout>
    <ListView
        android:minWidth="25px"
        android:minHeight="25px"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:paddingLeft="10dp"
        android:id="@+id/lvTemp" />
</LinearLayout>

2.Activity1.cs

using System;
using System.Collections.Generic;
using Android.App;
using Android.Content;
using Android.Runtime;
using Android.Views;
using Android.Widget;
using Android.OS;

namespace MyDatabaseDemo
{
    /// <summary>
    /// Main Activity1 to start applicaton.
    /// </summary>
    [Activity (Label = "My Database Demo", MainLauncher = true)]
    public class Activity1 : Activity
    {
        /// <summary>
        /// The mdtemp is object of MyDatabase class.
        /// </summary>
        MyDatabase mdTemp;
        /// <summary>
        /// The txtName, txtAge, txtCountry are object of EditText.
        /// </summary>
        EditText txtName, txtAge, txtCountry;
        /// <summary>
        /// The tvMsg is object of TextView.
        /// </summary>
        TextView tvMsg;
        /// <summary>
        /// The imgAdd, imgEdit, imgDelete, imgFind are object of ImageButton
        /// </summary>
        ImageButton imgAdd, imgEdit, imgDelete, imgFind;
        /// <summary>
        /// Raises the create event for application.
        /// </summary>
        /// <param name='bundle'>
        /// Bundle.
        /// </param>
        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

            // initialization of database class object.
            mdTemp = new MyDatabase ("person_db");
            // get ImageButton object instance from resource.
            imgAdd = FindViewById<ImageButton> (Resource.Id.imgAdd);
            imgEdit = FindViewById<ImageButton> (Resource.Id.imgEdit);
            imgDelete = FindViewById<ImageButton> (Resource.Id.imgDelete);
            imgFind = FindViewById<ImageButton> (Resource.Id.imgFind);
            // set images on image button from resource.
            imgAdd.SetImageResource (Resource.Drawable.add);
            imgEdit.SetImageResource (Resource.Drawable.save);
            imgDelete.SetImageResource (Resource.Drawable.delete);
            imgFind.SetImageResource (Resource.Drawable.find);
            // get EditText object instance from resource.
            txtName = FindViewById<EditText> (Resource.Id.txtName);
            txtAge = FindViewById<EditText> (Resource.Id.txtAge);
            txtCountry = FindViewById<EditText> (Resource.Id.txtCountry);
            tvMsg = FindViewById<TextView> (Resource.Id.tvMsg);

            tvMsg.Text = mdTemp.Message;

            // 添加 ImageButton click event for imgAdd, imgEdit, imgDelete, imgFind. 
            imgAdd.Click += delegate {
                //database call add record function AddRecord().
                mdTemp.AddRecord (txtName.Text, int.Parse (txtAge.Text), txtCountry.Text);
                tvMsg.Text = mdTemp.Message;
                txtName.Text = txtAge.Text = txtCountry.Text = "";
            };
            //编辑
            imgEdit.Click += delegate {
                int iId = -1;
                int.TryParse (tvMsg.Text, out iId);
                //database call update record function UpdateRecord().
                mdTemp.UpdateRecord (iId, txtName.Text, int.Parse (txtAge.Text), txtCountry.Text);
                tvMsg.Text = mdTemp.Message;
                txtName.Text = txtAge.Text = txtCountry.Text = "";
            };
            //删除
            imgDelete.Click += delegate {
                int iId = -1;
                int.TryParse (tvMsg.Text, out iId);
                //database call delete record function DeleteRecord().
                mdTemp.DeleteRecord (iId);
                tvMsg.Text = mdTemp.Message;
                txtName.Text = txtAge.Text = txtCountry.Text = "";
            };
            //查找
            imgFind.Click += delegate {

                //database call search record function GetCursorView().
                string sColumnName = "";
                if (txtName.Text.Trim () != "") {
                    sColumnName = "by Name";
                    GetCursorView (sColumnName, txtName.Text.Trim ());
                } else
                if (txtAge.Text.Trim () != "") {
                    sColumnName = "by Age";
                    GetCursorView (sColumnName, txtAge.Text.Trim ());
                } else
                if (txtCountry.Text.Trim () != "") {
                    sColumnName = "by Country";
                    GetCursorView (sColumnName, txtCountry.Text.Trim ());
                } else {
                    GetCursorView ();
                    sColumnName = "All";
                }
                tvMsg.Text = "Search " + sColumnName + ".";
            };

            // get ListView object instance from resource and add ItemClick, EventHandler.
            ListView lvTemp = FindViewById<ListView> (Resource.Id.lvTemp);        
            lvTemp.ItemClick += new EventHandler<AdapterView.ItemClickEventArgs> (ListView_ItemClick);


        }
        /// <summary>
        /// Lists the view_ item click.
        /// </summary>
        /// <param name='sender'>
        /// object sender.
        /// </param>
        /// <param name='e'>
        /// ItemClickEventArgs e.
        /// </param>
        void ListView_ItemClick (object sender, AdapterView.ItemClickEventArgs e)
        {
            // get TextView object instance from resource layout record_view.axml.
            TextView tvIdShow = e.View.FindViewById<TextView> (Resource.Id.tvIdShow);
            TextView tvPersonShow = e.View.FindViewById<TextView> (Resource.Id.tvPersonShow);
            TextView tvAgeShow = e.View.FindViewById<TextView> (Resource.Id.tvAgeShow);
            TextView tvCountryShow = e.View.FindViewById<TextView> (Resource.Id.tvCountryShow);
            // read value and wirte in EditText object.
            txtName.Text = tvPersonShow.Text;
            txtAge.Text = tvAgeShow.Text;
            txtCountry.Text = tvCountryShow.Text;
            //record id is write in TextView object to update or delete record.
            tvMsg.Text = tvIdShow.Text;
        }
        /// <summary>
        /// Gets the cursor view to show all record.
        /// </summary>
        protected void GetCursorView ()
        {
            Android.Database.ICursor icTemp = mdTemp.GetRecordCursor ();
            if (icTemp != null) {
                icTemp.MoveToFirst ();
                ListView lvTemp = FindViewById<ListView> (Resource.Id.lvTemp);
                string[] from = new string[] {"_id","Name","Age","Country" };
                int[] to = new int[] {
                    Resource.Id.tvIdShow,
                    Resource.Id.tvPersonShow,
                    Resource.Id.tvAgeShow,
                    Resource.Id.tvCountryShow
                };
                // creating a SimpleCursorAdapter to fill ListView object.
                SimpleCursorAdapter scaTemp = new SimpleCursorAdapter (this, Resource.Layout.record_view, icTemp, from, to);
                lvTemp.Adapter = scaTemp;
            } else {
                tvMsg.Text = mdTemp.Message;
            }
        }
        /// <summary>
        /// Gets the cursor view.
        /// </summary>
        /// <param name='sColumn'>
        /// column filed of MyTable is Name,Age,Country.
        /// </param>
        /// <param name='sValue'>
        /// Value as user input.
        /// </param>
        protected void GetCursorView (string sColumn, string sValue)
        {
            Android.Database.ICursor icTemp = mdTemp.GetRecordCursor (sColumn, sValue);
            if (icTemp != null) {
                icTemp.MoveToFirst ();
                ListView lvTemp = FindViewById<ListView> (Resource.Id.lvTemp);
                string[] from = new string[] {"_id","Name","Age","Country" };
                int[] to = new int[] {
                    Resource.Id.tvIdShow,
                    Resource.Id.tvPersonShow,
                    Resource.Id.tvAgeShow,
                    Resource.Id.tvCountryShow
                };
                // creating a SimpleCursorAdapter to fill ListView object.
                SimpleCursorAdapter scaTemp = new SimpleCursorAdapter (this, Resource.Layout.record_view, icTemp, from, to);
                lvTemp.Adapter = scaTemp;
            } else {
                tvMsg.Text = mdTemp.Message;
            }
        }
    }
}

3.MyDatabase.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Android.App;
using Android.Content;
using Android.OS;
using Android.Runtime;
using Android.Views;
using Android.Widget;
using Android.Database.Sqlite;
using System.IO;

namespace MyDatabaseDemo
{
    public class MyDatabase
    {
        /// <summary>
        /// SQLiteDatabase object sqldTemp to handle SQLiteDatabase.
        /// </summary>
        private SQLiteDatabase sqldTemp;
        /// <summary>
        /// The sSQLquery for query handling.
        /// </summary>
        private string sSQLQuery;
        /// <summary>
        /// The sMessage to hold message.
        /// </summary>
        private string sMessage;
        /// <summary>
        /// The bDBIsAvailable for database is available or not.
        /// </summary>
        private bool bDBIsAvailable;
        /// <summary>
        /// Initializes a new instance of the <see cref="MyDatabaseDemo.MyDatabase"/> class.
        /// </summary>
        public MyDatabase ()
        {
            sMessage = "";
            bDBIsAvailable = false;
        }
        /// <summary>
        /// Initializes a new instance of the <see cref="MyDatabaseDemo.MyDatabase"/> class.
        /// </summary>
        /// <param name='sDatabaseName'>
        /// Pass your database name.
        /// </param>
        public MyDatabase (string sDatabaseName)
        {
            try {
                sMessage = "";
                bDBIsAvailable = false;
                CreateDatabase (sDatabaseName);
            } catch (SQLiteException ex) {
                sMessage = ex.Message;
            }
        }
        /// <summary>
        /// Gets or sets a value indicating whether this <see cref="MyDatabaseDemo.MyDatabase"/> database available.
        /// </summary>
        /// <value>
        /// <c>true</c> if database available; otherwise, <c>false</c>.
        /// </value>
        public bool DatabaseAvailable {
            get{ return bDBIsAvailable;}
            set{ bDBIsAvailable = value;}
        }
        /// <summary>
        /// 消息
        /// </summary>
        /// <value>
        /// The message.
        /// </value>
        public string Message {
            get{ return sMessage;}
            set{ sMessage = value;}
        }
        /// <summary>
        /// 创建数据库
        /// </summary>
        /// <param name='sDatabaseName'>
        /// Pass database name.
        /// </param>
        public void CreateDatabase (string sDatabaseName)
        {
            try {
                sMessage = "";
                string sLocation = System.Environment.GetFolderPath (System.Environment.SpecialFolder.Personal);
                string sDB = Path.Combine (sLocation, sDatabaseName);
                bool bIsExists = File.Exists (sDB);        
                if (!bIsExists) {
                    sqldTemp = SQLiteDatabase.OpenOrCreateDatabase (sDB, null);
                    sSQLQuery = "CREATE TABLE IF NOT EXISTS " +
                        "MyTable " +
                        "(_id INTEGER PRIMARY KEY AUTOINCREMENT,Name VARCHAR,Age INT,Country VARCHAR);";
                    sqldTemp.ExecSQL (sSQLQuery);
                    sMessage = "New database is created.";
                } else {
                    sqldTemp = SQLiteDatabase.OpenDatabase (sDB, null, DatabaseOpenFlags.OpenReadwrite);
                    sMessage = "Database is opened.";
                }
                bDBIsAvailable = true;
            } catch (SQLiteException ex) {
                sMessage = ex.Message;
            }
        }
        /// <summary>
        /// 添加记录
        /// </summary>
        /// <param name='sName'>
        /// Pass name.
        /// </param>
        /// <param name='iAge'>
        /// Pass age.
        /// </param>
        /// <param name='sCountry'>
        /// Pass country.
        /// </param>
        public void AddRecord (string sName, int iAge, string sCountry)
        {
            try {
                sSQLQuery = "INSERT INTO " +
                    "MyTable " +
                    "(Name,Age,Country)" +
                    "VALUES('" + sName + "'," + iAge + ",'" + sCountry + "');";
                sqldTemp.ExecSQL (sSQLQuery);
                sMessage = "Record is saved.";
            } catch (SQLiteException ex) {
                sMessage = ex.Message;
            }
        }
        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name='iId'>
        /// Pass record ID.
        /// </param>
        /// <param name='sName'>
        /// Pass name.
        /// </param>
        /// <param name='iAge'>
        /// Pass age.
        /// </param>
        /// <param name='sCountry'>
        /// Pass country.
        /// </param>
        public void UpdateRecord (int iId, string sName, int iAge, string sCountry)
        {
            try {
                sSQLQuery = "UPDATE MyTable " +
                    "SET Name='" + sName + "',Age='" + iAge + "',Country='" + sCountry + "' " +
                    "WHERE _id='" + iId + "';";
                sqldTemp.ExecSQL (sSQLQuery);
                sMessage = "Record is updated: " + iId;
            } catch (SQLiteException ex) {
                sMessage = ex.Message;
            }
        }
        /// <summary>
        /// 删除记录
        /// </summary>
        /// <param name='iId'>
        /// Pass ID.
        /// </param>
        public void DeleteRecord (int iId)
        {
            try {
                sSQLQuery = "DELETE FROM MyTable " +
                    "WHERE _id='" + iId + "';";
                sqldTemp.ExecSQL (sSQLQuery);
                sMessage = "Record is deleted: " + iId;
            } catch (SQLiteException ex) {
                sMessage = ex.Message;
            }
        }
        /// <summary>
        /// 获取当前记录游标
        /// </summary>
        /// <returns>
        /// The record cursor.
        /// </returns>
        public Android.Database.ICursor GetRecordCursor ()
        {
            Android.Database.ICursor icTemp = null;
            try {
                sSQLQuery = "SELECT * FROM MyTable;";
                icTemp = sqldTemp.RawQuery (sSQLQuery, null);
                if (!(icTemp != null)) {
                    sMessage = "Record not found.";
                }
            } catch (SQLiteException ex) {
                sMessage = ex.Message;
            }
            return icTemp;
        }
        /// <summary>
        /// 获取符合检索条件的记录游标
        /// </summary>
        /// <returns>
        /// The record cursor.
        /// </returns>
        /// <param name='sColumn'>
        /// column filed of MyTable is Name,Age,Country.
        /// </param>
        /// <param name='sValue'>
        /// Value as user input.
        /// </param>
        public Android.Database.ICursor GetRecordCursor (string sColumn, string sValue)
        {
            Android.Database.ICursor icTemp = null;
            try {
                sSQLQuery = "SELECT * FROM MyTable WHERE " + sColumn + " LIKE '" + sValue + "%';";
                icTemp = sqldTemp.RawQuery (sSQLQuery, null);
                if (!(icTemp != null)) {
                    sMessage = "Record not found.";
                }
            } catch (SQLiteException ex) {
                sMessage = ex.Message;
            }
            return icTemp;
        }
        /// <summary>
        /// 释放非托管资源并执行其他清理操作之前被垃圾回收
        /// <see cref="MyDatabaseDemo.MyDatabase"/> 
        /// </summary>
        ~MyDatabase ()
        {
            try {
                sMessage = "";
                bDBIsAvailable = false;        
                sqldTemp.Close ();        
            } catch (SQLiteException ex) {
                sMessage = ex.Message;
            }
        }
    }
}