目的：使用 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", ).GetString("AssetsManage", "")))//未创建数据库
{
File.Create(Localhost_DataBase.Path);
//继续创建数据表
CreateTable();
//存取已创建数据库信息
GetSharedPreferences("AssetsManage", ).Edit().PutString("AssetsManage", "OK").Commit();
}
}
catch
{
//存取创建数据库时的异常
GetSharedPreferences("Exception", ).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", ).Edit().PutString("DataTableException", "异常").Commit();
}
}
向表中插入模拟数据
/// <summary>
/// 插入数据
/// </summary>
void InsertData()
{
for(int i=;i<=;i++)
{
ContentValues cv = new ContentValues();
cv.Put("Id", i.ToString());
cv.Put("name", "张三" + i.ToString());
cv.Put("sex", new Random().Next(, ) == ? "男" : "女");
cv.Put("age", ( + i).ToString());
cv.Put("idcard", "" + (i - ).ToString());
Localhost_DataBase.Insert("tb_person", null, cv); ContentValues cv1 = new ContentValues();
cv1.Put("uId", i);
cv1.Put("mathgrade", new Random().Next(, ));
cv1.Put("Chinese", new Random().Next(, ));
cv1.Put("English", new Random().Next(, ));
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 = ; i < ic.Count; i++)
{
if (i == ) //确定游标位置
{
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 + ).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("");
UpdateData("");
QueryData();
}
到这里就结束了，那里写的不足希望大家补充~
链接: https://pan.baidu.com/s/1QhDkfSXyVlmWEnIU6VGy-g
密码: isa8