1.Main.axml
1 <?xml version="1.0" encoding="utf-8"?> 2 <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" 3 android:orientation="vertical" 4 android:layout_width="fill_parent" 5 android:layout_height="fill_parent"> 6 <TableLayout 7 android:minWidth="25px" 8 android:minHeight="25px" 9 android:layout_width="fill_parent" 10 android:layout_height="wrap_content" 11 android:layout_gravity="center_horizontal" 12 android:id="@+id/tableLayout1"> 13 <TableRow 14 android:background="@android:drawable/bottom_bar" 15 android:id="@+id/tableRow2" 16 android:layout_height="40dp"> 17 <TextView 18 android:text="姓名" 19 android:textAppearance="?android:attr/textAppearanceSmall" 20 android:layout_width="34.0dp" 21 android:layout_height="23.3dp" 22 android:id="@+id/tvName" 23 android:layout_marginRight="0.0dp" 24 android:layout_column="0" 25 android:layout_marginTop="20dp" 26 android:textColor="#fff" /> 27 <EditText 28 android:inputType="textPersonName" 29 android:layout_width="80px" 30 android:layout_height="wrap_content" 31 android:id="@+id/txtName" 32 android:layout_gravity="center_vertical" 33 android:layout_column="1" /> 34 <TextView 35 android:text="年龄" 36 android:textAppearance="?android:attr/textAppearanceSmall" 37 android:layout_width="30.7dp" 38 android:layout_height="24.0dp" 39 android:id="@+id/tvAge" 40 android:layout_marginTop="20dp" 41 android:textColor="#ffffff" /> 42 <EditText 43 android:inputType="number" 44 android:layout_width="50dp" 45 android:layout_height="wrap_content" 46 android:id="@+id/txtAge" 47 android:layout_gravity="center_vertical" /> 48 <TextView 49 android:text="国家" 50 android:textAppearance="?android:attr/textAppearanceSmall" 51 android:layout_width="33.3dp" 52 android:layout_height="22.7dp" 53 android:id="@+id/tvCountry" 54 android:layout_marginRight="0.0dp" 55 android:layout_marginTop="20dp" 56 android:textColor="#fff" /> 57 <EditText 58 android:layout_width="100dp" 59 android:layout_height="wrap_content" 60 android:id="@+id/txtCountry" 61 android:layout_gravity="center_vertical" 62 android:layout_marginRight="0dp" /> 63 </TableRow> 64 <TableRow 65 android:id="@+id/tableRow3"> 66 <TextView 67 android:layout_width="fill_parent" 68 android:layout_height="wrap_content" 69 android:id="@+id/tvMsg" 70 android:layout_column="1" /> 71 </TableRow> 72 </TableLayout> 73 <LinearLayout 74 android:orientation="horizontal" 75 android:layout_width="fill_parent" 76 android:layout_height="40dp" 77 android:paddingLeft="10dp" 78 android:id="@+id/llButtons" 79 android:background="@android:drawable/bottom_bar" 80 android:layout_marginBottom="0dp"> 81 <ImageButton 82 android:src="@drawable/add" 83 android:layout_width="75px" 84 android:paddingLeft="0dp" 85 android:layout_height="wrap_content" 86 android:background="@android:color/transparent" 87 android:id="@+id/imgAdd" 88 android:layout_marginTop="0.0dp" 89 android:paddingTop="0dp" 90 android:maxHeight="32px" 91 android:maxWidth="32px" 92 android:minHeight="32px" 93 android:minWidth="32px" /> 94 <ImageButton 95 android:src="@drawable/edit" 96 android:layout_width="75px" 97 android:paddingLeft="0dp" 98 android:layout_height="wrap_content" 99 android:background="@android:color/transparent" 100 android:id="@+id/imgEdit" 101 android:paddingTop="0dp" /> 102 <ImageButton 103 android:src="@drawable/delete" 104 android:layout_width="75px" 105 android:paddingLeft="0dp" 106 android:layout_height="wrap_content" 107 android:background="@android:color/transparent" 108 android:id="@+id/imgDelete" 109 android:paddingTop="0dp" /> 110 <ImageButton 111 android:src="@drawable/find" 112 android:layout_width="75px" 113 android:paddingLeft="0dp" 114 android:layout_height="wrap_content" 115 android:background="@android:color/transparent" 116 android:id="@+id/imgFind" 117 android:paddingTop="0dp" /> 118 </LinearLayout> 119 <LinearLayout 120 android:orientation="horizontal" 121 android:minWidth="25px" 122 android:minHeight="25px" 123 android:layout_width="fill_parent" 124 android:layout_height="wrap_content" 125 android:paddingLeft="10dp" 126 android:id="@+id/llHeader"> 127 <TextView 128 android:text="编号" 129 android:layout_width="66.7dp" 130 android:layout_height="wrap_content" 131 android:id="@+id/tvIdShowR" 132 android:textColor="@android:color/white" /> 133 <TextView 134 android:text="姓名" 135 android:layout_width="72.0dp" 136 android:layout_height="wrap_content" 137 android:textColor="@android:color/white" 138 android:id="@+id/tvPersonShowR" 139 android:layout_marginLeft="20dp" 140 android:layout_marginRight="0.0dp" /> 141 <TextView 142 android:text="年龄" 143 android:layout_width="58.7dp" 144 android:layout_height="wrap_content" 145 android:textColor="@android:color/white" 146 android:id="@+id/tvAgeShowR" /> 147 <TextView 148 android:text="国家" 149 android:layout_width="65.3dp" 150 android:layout_height="wrap_content" 151 android:textColor="@android:color/white" 152 android:id="@+id/tvCountryShowR" 153 android:layout_marginLeft="20dp" /> 154 </LinearLayout> 155 <ListView 156 android:minWidth="25px" 157 android:minHeight="25px" 158 android:layout_width="fill_parent" 159 android:layout_height="wrap_content" 160 android:paddingLeft="10dp" 161 android:id="@+id/lvTemp" /> 162 </LinearLayout>
2.Activity1.cs
1 using System; 2 using System.Collections.Generic; 3 using Android.App; 4 using Android.Content; 5 using Android.Runtime; 6 using Android.Views; 7 using Android.Widget; 8 using Android.OS; 9 10 namespace MyDatabaseDemo 11 { 12 /// <summary> 13 /// Main Activity1 to start applicaton. 14 /// </summary> 15 [Activity (Label = "My Database Demo", MainLauncher = true)] 16 public class Activity1 : Activity 17 { 18 /// <summary> 19 /// The mdtemp is object of MyDatabase class. 20 /// </summary> 21 MyDatabase mdTemp; 22 /// <summary> 23 /// The txtName, txtAge, txtCountry are object of EditText. 24 /// </summary> 25 EditText txtName, txtAge, txtCountry; 26 /// <summary> 27 /// The tvMsg is object of TextView. 28 /// </summary> 29 TextView tvMsg; 30 /// <summary> 31 /// The imgAdd, imgEdit, imgDelete, imgFind are object of ImageButton 32 /// </summary> 33 ImageButton imgAdd, imgEdit, imgDelete, imgFind; 34 /// <summary> 35 /// Raises the create event for application. 36 /// </summary> 37 /// <param name='bundle'> 38 /// Bundle. 39 /// </param> 40 protected override void OnCreate (Bundle bundle) 41 { 42 base.OnCreate (bundle); 43 44 // Set our view from the "main" layout resource 45 SetContentView (Resource.Layout.Main); 46 47 // Get our button from the layout resource, 48 // and attach an event to it 49 50 // initialization of database class object. 51 mdTemp = new MyDatabase ("person_db"); 52 // get ImageButton object instance from resource. 53 imgAdd = FindViewById<ImageButton> (Resource.Id.imgAdd); 54 imgEdit = FindViewById<ImageButton> (Resource.Id.imgEdit); 55 imgDelete = FindViewById<ImageButton> (Resource.Id.imgDelete); 56 imgFind = FindViewById<ImageButton> (Resource.Id.imgFind); 57 // set images on image button from resource. 58 imgAdd.SetImageResource (Resource.Drawable.add); 59 imgEdit.SetImageResource (Resource.Drawable.save); 60 imgDelete.SetImageResource (Resource.Drawable.delete); 61 imgFind.SetImageResource (Resource.Drawable.find); 62 // get EditText object instance from resource. 63 txtName = FindViewById<EditText> (Resource.Id.txtName); 64 txtAge = FindViewById<EditText> (Resource.Id.txtAge); 65 txtCountry = FindViewById<EditText> (Resource.Id.txtCountry); 66 tvMsg = FindViewById<TextView> (Resource.Id.tvMsg); 67 68 tvMsg.Text = mdTemp.Message; 69 70 // 添加 ImageButton click event for imgAdd, imgEdit, imgDelete, imgFind. 71 imgAdd.Click += delegate { 72 //database call add record function AddRecord(). 73 mdTemp.AddRecord (txtName.Text, int.Parse (txtAge.Text), txtCountry.Text); 74 tvMsg.Text = mdTemp.Message; 75 txtName.Text = txtAge.Text = txtCountry.Text = ""; 76 }; 77 //编辑 78 imgEdit.Click += delegate { 79 int iId = -1; 80 int.TryParse (tvMsg.Text, out iId); 81 //database call update record function UpdateRecord(). 82 mdTemp.UpdateRecord (iId, txtName.Text, int.Parse (txtAge.Text), txtCountry.Text); 83 tvMsg.Text = mdTemp.Message; 84 txtName.Text = txtAge.Text = txtCountry.Text = ""; 85 }; 86 //删除 87 imgDelete.Click += delegate { 88 int iId = -1; 89 int.TryParse (tvMsg.Text, out iId); 90 //database call delete record function DeleteRecord(). 91 mdTemp.DeleteRecord (iId); 92 tvMsg.Text = mdTemp.Message; 93 txtName.Text = txtAge.Text = txtCountry.Text = ""; 94 }; 95 //查找 96 imgFind.Click += delegate { 97 98 //database call search record function GetCursorView(). 99 string sColumnName = ""; 100 if (txtName.Text.Trim () != "") { 101 sColumnName = "by Name"; 102 GetCursorView (sColumnName, txtName.Text.Trim ()); 103 } else 104 if (txtAge.Text.Trim () != "") { 105 sColumnName = "by Age"; 106 GetCursorView (sColumnName, txtAge.Text.Trim ()); 107 } else 108 if (txtCountry.Text.Trim () != "") { 109 sColumnName = "by Country"; 110 GetCursorView (sColumnName, txtCountry.Text.Trim ()); 111 } else { 112 GetCursorView (); 113 sColumnName = "All"; 114 } 115 tvMsg.Text = "Search " + sColumnName + "."; 116 }; 117 118 // get ListView object instance from resource and add ItemClick, EventHandler. 119 ListView lvTemp = FindViewById<ListView> (Resource.Id.lvTemp); 120 lvTemp.ItemClick += new EventHandler<AdapterView.ItemClickEventArgs> (ListView_ItemClick); 121 122 123 } 124 /// <summary> 125 /// Lists the view_ item click. 126 /// </summary> 127 /// <param name='sender'> 128 /// object sender. 129 /// </param> 130 /// <param name='e'> 131 /// ItemClickEventArgs e. 132 /// </param> 133 void ListView_ItemClick (object sender, AdapterView.ItemClickEventArgs e) 134 { 135 // get TextView object instance from resource layout record_view.axml. 136 TextView tvIdShow = e.View.FindViewById<TextView> (Resource.Id.tvIdShow); 137 TextView tvPersonShow = e.View.FindViewById<TextView> (Resource.Id.tvPersonShow); 138 TextView tvAgeShow = e.View.FindViewById<TextView> (Resource.Id.tvAgeShow); 139 TextView tvCountryShow = e.View.FindViewById<TextView> (Resource.Id.tvCountryShow); 140 // read value and wirte in EditText object. 141 txtName.Text = tvPersonShow.Text; 142 txtAge.Text = tvAgeShow.Text; 143 txtCountry.Text = tvCountryShow.Text; 144 //record id is write in TextView object to update or delete record. 145 tvMsg.Text = tvIdShow.Text; 146 } 147 /// <summary> 148 /// Gets the cursor view to show all record. 149 /// </summary> 150 protected void GetCursorView () 151 { 152 Android.Database.ICursor icTemp = mdTemp.GetRecordCursor (); 153 if (icTemp != null) { 154 icTemp.MoveToFirst (); 155 ListView lvTemp = FindViewById<ListView> (Resource.Id.lvTemp); 156 string[] from = new string[] {"_id","Name","Age","Country" }; 157 int[] to = new int[] { 158 Resource.Id.tvIdShow, 159 Resource.Id.tvPersonShow, 160 Resource.Id.tvAgeShow, 161 Resource.Id.tvCountryShow 162 }; 163 // creating a SimpleCursorAdapter to fill ListView object. 164 SimpleCursorAdapter scaTemp = new SimpleCursorAdapter (this, Resource.Layout.record_view, icTemp, from, to); 165 lvTemp.Adapter = scaTemp; 166 } else { 167 tvMsg.Text = mdTemp.Message; 168 } 169 } 170 /// <summary> 171 /// Gets the cursor view. 172 /// </summary> 173 /// <param name='sColumn'> 174 /// column filed of MyTable is Name,Age,Country. 175 /// </param> 176 /// <param name='sValue'> 177 /// Value as user input. 178 /// </param> 179 protected void GetCursorView (string sColumn, string sValue) 180 { 181 Android.Database.ICursor icTemp = mdTemp.GetRecordCursor (sColumn, sValue); 182 if (icTemp != null) { 183 icTemp.MoveToFirst (); 184 ListView lvTemp = FindViewById<ListView> (Resource.Id.lvTemp); 185 string[] from = new string[] {"_id","Name","Age","Country" }; 186 int[] to = new int[] { 187 Resource.Id.tvIdShow, 188 Resource.Id.tvPersonShow, 189 Resource.Id.tvAgeShow, 190 Resource.Id.tvCountryShow 191 }; 192 // creating a SimpleCursorAdapter to fill ListView object. 193 SimpleCursorAdapter scaTemp = new SimpleCursorAdapter (this, Resource.Layout.record_view, icTemp, from, to); 194 lvTemp.Adapter = scaTemp; 195 } else { 196 tvMsg.Text = mdTemp.Message; 197 } 198 } 199 } 200 }
3.MyDatabase.cs
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 6 using Android.App; 7 using Android.Content; 8 using Android.OS; 9 using Android.Runtime; 10 using Android.Views; 11 using Android.Widget; 12 using Android.Database.Sqlite; 13 using System.IO; 14 15 namespace MyDatabaseDemo 16 { 17 public class MyDatabase 18 { 19 /// <summary> 20 /// SQLiteDatabase object sqldTemp to handle SQLiteDatabase. 21 /// </summary> 22 private SQLiteDatabase sqldTemp; 23 /// <summary> 24 /// The sSQLquery for query handling. 25 /// </summary> 26 private string sSQLQuery; 27 /// <summary> 28 /// The sMessage to hold message. 29 /// </summary> 30 private string sMessage; 31 /// <summary> 32 /// The bDBIsAvailable for database is available or not. 33 /// </summary> 34 private bool bDBIsAvailable; 35 /// <summary> 36 /// Initializes a new instance of the <see cref="MyDatabaseDemo.MyDatabase"/> class. 37 /// </summary> 38 public MyDatabase () 39 { 40 sMessage = ""; 41 bDBIsAvailable = false; 42 } 43 /// <summary> 44 /// Initializes a new instance of the <see cref="MyDatabaseDemo.MyDatabase"/> class. 45 /// </summary> 46 /// <param name='sDatabaseName'> 47 /// Pass your database name. 48 /// </param> 49 public MyDatabase (string sDatabaseName) 50 { 51 try { 52 sMessage = ""; 53 bDBIsAvailable = false; 54 CreateDatabase (sDatabaseName); 55 } catch (SQLiteException ex) { 56 sMessage = ex.Message; 57 } 58 } 59 /// <summary> 60 /// Gets or sets a value indicating whether this <see cref="MyDatabaseDemo.MyDatabase"/> database available. 61 /// </summary> 62 /// <value> 63 /// <c>true</c> if database available; otherwise, <c>false</c>. 64 /// </value> 65 public bool DatabaseAvailable { 66 get{ return bDBIsAvailable;} 67 set{ bDBIsAvailable = value;} 68 } 69 /// <summary> 70 /// 消息 71 /// </summary> 72 /// <value> 73 /// The message. 74 /// </value> 75 public string Message { 76 get{ return sMessage;} 77 set{ sMessage = value;} 78 } 79 /// <summary> 80 /// 创建数据库 81 /// </summary> 82 /// <param name='sDatabaseName'> 83 /// Pass database name. 84 /// </param> 85 public void CreateDatabase (string sDatabaseName) 86 { 87 try { 88 sMessage = ""; 89 string sLocation = System.Environment.GetFolderPath (System.Environment.SpecialFolder.Personal); 90 string sDB = Path.Combine (sLocation, sDatabaseName); 91 bool bIsExists = File.Exists (sDB); 92 if (!bIsExists) { 93 sqldTemp = SQLiteDatabase.OpenOrCreateDatabase (sDB, null); 94 sSQLQuery = "CREATE TABLE IF NOT EXISTS " + 95 "MyTable " + 96 "(_id INTEGER PRIMARY KEY AUTOINCREMENT,Name VARCHAR,Age INT,Country VARCHAR);"; 97 sqldTemp.ExecSQL (sSQLQuery); 98 sMessage = "New database is created."; 99 } else { 100 sqldTemp = SQLiteDatabase.OpenDatabase (sDB, null, DatabaseOpenFlags.OpenReadwrite); 101 sMessage = "Database is opened."; 102 } 103 bDBIsAvailable = true; 104 } catch (SQLiteException ex) { 105 sMessage = ex.Message; 106 } 107 } 108 /// <summary> 109 /// 添加记录 110 /// </summary> 111 /// <param name='sName'> 112 /// Pass name. 113 /// </param> 114 /// <param name='iAge'> 115 /// Pass age. 116 /// </param> 117 /// <param name='sCountry'> 118 /// Pass country. 119 /// </param> 120 public void AddRecord (string sName, int iAge, string sCountry) 121 { 122 try { 123 sSQLQuery = "INSERT INTO " + 124 "MyTable " + 125 "(Name,Age,Country)" + 126 "VALUES('" + sName + "'," + iAge + ",'" + sCountry + "');"; 127 sqldTemp.ExecSQL (sSQLQuery); 128 sMessage = "Record is saved."; 129 } catch (SQLiteException ex) { 130 sMessage = ex.Message; 131 } 132 } 133 /// <summary> 134 /// 更新记录 135 /// </summary> 136 /// <param name='iId'> 137 /// Pass record ID. 138 /// </param> 139 /// <param name='sName'> 140 /// Pass name. 141 /// </param> 142 /// <param name='iAge'> 143 /// Pass age. 144 /// </param> 145 /// <param name='sCountry'> 146 /// Pass country. 147 /// </param> 148 public void UpdateRecord (int iId, string sName, int iAge, string sCountry) 149 { 150 try { 151 sSQLQuery = "UPDATE MyTable " + 152 "SET Name='" + sName + "',Age='" + iAge + "',Country='" + sCountry + "' " + 153 "WHERE _id='" + iId + "';"; 154 sqldTemp.ExecSQL (sSQLQuery); 155 sMessage = "Record is updated: " + iId; 156 } catch (SQLiteException ex) { 157 sMessage = ex.Message; 158 } 159 } 160 /// <summary> 161 /// 删除记录 162 /// </summary> 163 /// <param name='iId'> 164 /// Pass ID. 165 /// </param> 166 public void DeleteRecord (int iId) 167 { 168 try { 169 sSQLQuery = "DELETE FROM MyTable " + 170 "WHERE _id='" + iId + "';"; 171 sqldTemp.ExecSQL (sSQLQuery); 172 sMessage = "Record is deleted: " + iId; 173 } catch (SQLiteException ex) { 174 sMessage = ex.Message; 175 } 176 } 177 /// <summary> 178 /// 获取当前记录游标 179 /// </summary> 180 /// <returns> 181 /// The record cursor. 182 /// </returns> 183 public Android.Database.ICursor GetRecordCursor () 184 { 185 Android.Database.ICursor icTemp = null; 186 try { 187 sSQLQuery = "SELECT * FROM MyTable;"; 188 icTemp = sqldTemp.RawQuery (sSQLQuery, null); 189 if (!(icTemp != null)) { 190 sMessage = "Record not found."; 191 } 192 } catch (SQLiteException ex) { 193 sMessage = ex.Message; 194 } 195 return icTemp; 196 } 197 /// <summary> 198 /// 获取符合检索条件的记录游标 199 /// </summary> 200 /// <returns> 201 /// The record cursor. 202 /// </returns> 203 /// <param name='sColumn'> 204 /// column filed of MyTable is Name,Age,Country. 205 /// </param> 206 /// <param name='sValue'> 207 /// Value as user input. 208 /// </param> 209 public Android.Database.ICursor GetRecordCursor (string sColumn, string sValue) 210 { 211 Android.Database.ICursor icTemp = null; 212 try { 213 sSQLQuery = "SELECT * FROM MyTable WHERE " + sColumn + " LIKE '" + sValue + "%';"; 214 icTemp = sqldTemp.RawQuery (sSQLQuery, null); 215 if (!(icTemp != null)) { 216 sMessage = "Record not found."; 217 } 218 } catch (SQLiteException ex) { 219 sMessage = ex.Message; 220 } 221 return icTemp; 222 } 223 /// <summary> 224 /// 释放非托管资源并执行其他清理操作之前被垃圾回收 225 /// <see cref="MyDatabaseDemo.MyDatabase"/> 226 /// </summary> 227 ~MyDatabase () 228 { 229 try { 230 sMessage = ""; 231 bDBIsAvailable = false; 232 sqldTemp.Close (); 233 } catch (SQLiteException ex) { 234 sMessage = ex.Message; 235 } 236 } 237 } 238 }