目的：使用 SQLiteDatabase 创建本地数据库、表，并对数据进行增删改查操作。
引用命名空间：
using Android.App; using Android.Widget; using Android.OS; using Android.Database.Sqlite; using Android.Content; using System.IO; using System.Data; using Mono.Data.Sqlite; using System; using Android.Database; using System.Collections.Generic;
Person类
public class Person { public string Id { get; set; } public string Name { get; set; } public string IdCard { get; set; } public string Sex { get; set; } public string Age { get; set; } }
创建局部变量Localhost_DataBase，以及控件，model类
SQLiteDatabase Localhost_DataBase = null; Person person; List<Person> list = new List<Person>(); ListView lv_Person;
创建数据库
/// <summary> /// 创建数据库 /// </summary> void CreateDataBase() { try { //打开或创建数据库 名称为:AssetsManage.db Localhost_DataBase = OpenOrCreateDatabase("AssetsManage.db", FileCreationMode.Private, null); //判断是否存在数据库 if (string.IsNullOrEmpty(GetSharedPreferences("AssetsManage", 0).GetString("AssetsManage", "")))//未创建数据库 { File.Create(Localhost_DataBase.Path); //继续创建数据表 CreateTable(); //存取已创建数据库信息 GetSharedPreferences("AssetsManage", 0).Edit().PutString("AssetsManage", "OK").Commit(); } } catch { //存取创建数据库时的异常 GetSharedPreferences("Exception", 0).Edit().PutString("DataBaseException", "异常").Commit(); } }
创建表
/// <summary> /// 创建表 /// </summary> void CreateTable() { string db = Localhost_DataBase.Path; var conn = new SqliteConnection("Data Source=" + db); //这里可以创建多张表 var commands = new[] { "CREATE TABLE tb_person (Id int,name varchar(20),sex varchar(80),age varchar(20),idcard varchar(18))", "CREATE TABLE tb_grade (uId int,mathgrade varchar(5),Chinese varchar(5),English varchar(5))" }; try { foreach (var cmd in commands) { using (var sqlitecmd = conn.CreateCommand()) { sqlitecmd.CommandText = cmd; sqlitecmd.CommandType = CommandType.Text; conn.Open(); sqlitecmd.ExecuteNonQuery(); conn.Close(); } } InsertData(); } catch (System.Exception e) { //存取创建数据表时的异常 GetSharedPreferences("Exception", 0).Edit().PutString("DataTableException", "异常").Commit(); } }
向表中插入模拟数据
/// <summary> /// 插入数据 /// </summary> void InsertData() { for(int i=1;i<=10;i++) { ContentValues cv = new ContentValues(); cv.Put("Id", i.ToString()); cv.Put("name", "张三" + i.ToString()); cv.Put("sex", new Random().Next(1, 2) == 1 ? "男" : "女"); cv.Put("age", (20 + i).ToString()); cv.Put("idcard", "21072619971111111" + (i - 1).ToString()); Localhost_DataBase.Insert("tb_person", null, cv); ContentValues cv1 = new ContentValues(); cv1.Put("uId", i); cv1.Put("mathgrade", new Random().Next(1, 100)); cv1.Put("Chinese", new Random().Next(1, 100)); cv1.Put("English", new Random().Next(1, 100)); Localhost_DataBase.Insert("tb_grade", null, cv1); } }
查询表中数据
/// <summary> /// 查询数据 /// </summary> void QueryData() { ICursor ic = Localhost_DataBase.Query("tb_person", null, null, null, null, null, null); //全部查询 //ICursor ic = Localhost_DataBase.Query("tb_person", null, " id =? and name =?", new string[] { "1","张三1" }, null, null, null); //条件查询 for (int i = 0; i < ic.Count; i++) { if (i == 0) //确定游标位置 { ic.MoveToFirst(); } else { ic.MoveToNext(); } person = new Person(); person.Id = ic.GetString(ic.GetColumnIndex("Id")); person.Name = ic.GetString(ic.GetColumnIndex("name")); person.Age = ic.GetString(ic.GetColumnIndex("age")); person.Sex= ic.GetString(ic.GetColumnIndex("sex")); person.IdCard = ic.GetString(ic.GetColumnIndex("idcard")); list.Add(person); } lv_Person.Adapter = new ListViewAdapter(this, list); }
根据条件删除表中数据
/// <summary> /// 删除数据 /// </summary> /// <param name="id"></param> void DeleteData(string id) { Localhost_DataBase.Delete("tb_person", " Id=?", new string[] { id }); //Localhost_DataBase.Delete("tb_person", null, null); //删除表中所有数据 }
根据条件修改表中数据
/// <summary> /// 修改数据 /// </summary> /// <param name="name"></param> void UpdateData(string id) { ContentValues cv = new ContentValues(); cv.Put("name", "张三1000"); Localhost_DataBase.Update("tb_person", cv, " Id=? ", new string[] { id }); }
ListViewAdapter 类
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; namespace LocalhostDataBaseTest { public class ListViewAdapter:BaseAdapter<Person> { Activity context; public List<Person> mings; public ListViewAdapter(Activity context, List<Person> mings) { this.context = context; this.mings = mings; } public override int Count { get { return this.mings.Count; } } public override long GetItemId(int position) { return position; } public override Person this[int position] { get { return this.mings[position]; } } public override View GetView(int position, View convertView, ViewGroup parent) { var itme = this.mings[position]; convertView = LayoutInflater.From(context).Inflate(Resource.Layout.Person_Items, parent, false); TextView item_id = convertView.FindViewById<TextView>(Resource.Id.item_id); TextView item_name = convertView.FindViewById<TextView>(Resource.Id.item_name); TextView item_idcard = convertView.FindViewById<TextView>(Resource.Id.item_idcard); TextView item_sex = convertView.FindViewById<TextView>(Resource.Id.item_sex); TextView item_age = convertView.FindViewById<TextView>(Resource.Id.item_age); item_id.Text = (position + 1).ToString(); item_name.SetText(itme.Name, TextView.BufferType.Normal); item_idcard.SetText(itme.IdCard, TextView.BufferType.Normal); item_sex.SetText(itme.Sex, TextView.BufferType.Normal); item_age.SetText(itme.Age, TextView.BufferType.Normal); return convertView; } } }
OnCreate调用
protected override void OnCreate(Bundle savedInstanceState) { base.OnCreate(savedInstanceState); // Set our view from the "main" layout resource SetContentView(Resource.Layout.Main); lv_Person = FindViewById<ListView>(Resource.Id.listViewPerson); CreateDataBase(); DeleteData("10"); UpdateData("9"); QueryData(); }
到这里就结束了，那里写的不足希望大家补充~
链接: https://pan.baidu.com/s/1QhDkfSXyVlmWEnIU6VGy-g
密码: isa8
