首页 > 代码库 > Xamarin.Android 入门实例（4）之实现对 SQLLite 进行添加/修改/删除/查询操作
Xamarin.Android 入门实例（4）之实现对 SQLLite 进行添加/修改/删除/查询操作
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=http://www.mamicode.com/"@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=http://www.mamicode.com/"@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 <ImageButton103 android:src=http://www.mamicode.com/"@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 <ImageButton111 android:src=http://www.mamicode.com/"@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 <LinearLayout120 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 <TextView128 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 <TextView134 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 <TextView142 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 <TextView148 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 <ListView156 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 } else104 if (txtAge.Text.Trim () != "") {105 sColumnName = "by Age";106 GetCursorView (sColumnName, txtAge.Text.Trim ());107 } else108 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.tvCountryShow162 };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.tvCountryShow191 };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 }
Xamarin.Android 入门实例（4）之实现对 SQLLite 进行添加/修改/删除/查询操作
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。