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

1.Main.axml

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

 <?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=""
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="" />
<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="" />
</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 = -;
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 = -;
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;
}
}
}
}
上一篇:【转】Android Fragment中使用SurfaceView切换时闪一下黑屏的解决办法


下一篇:xml 的读写