新建Android空白项目,NuGet下载SQLite.Net-PCL
定义实体类:
public class Book
{
[PrimaryKey, AutoIncrement]
public int id { get; set; }
public string Name { get; set; }//书名
public DateTime PublishDate { get; set; }//出版日期
public string Author { get; set; }//作者
public float Price { get; set; }//价格
}
定义数据库,在数据库的构造函数中创建数据表:
public class MyBookDB : SQLiteConnection
{
//定义属性,便于外部访问数据表
public TableQuery<Book> Books { get { return this.Table<Book>(); } }
public MyBookDB(string dbPath) : base(new SQLitePlatformAndroid(), dbPath)
{
//创建数据表
CreateTable<Book>();
}
}
页面凑合一下
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:orientation="vertical"
android:layout_width="match_parent"
android:layout_height="match_parent">
<Button
android:id="@+id/btnAdd"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Add" />
<Button
android:id="@+id/btnModify"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Modify" />
<Button
android:id="@+id/btnDel"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Del" />
<Button
android:id="@+id/btnQuery"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Query" />
<Button
android:id="@+id/btnClear"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Clear" />
<Button
android:id="@+id/btnRefresh"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Refresh" />
<ListView
android:minWidth="25px"
android:minHeight="25px"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:id="@+id/lsvRecords" />
</LinearLayout>
数据库访问代码,增删改查:
public class MainActivity : Activity
{
private ArrayAdapter<string> adapter;
private ListView lsvRecords;
private List<string> records;
protected override void OnCreate(Bundle bundle)
{
base.OnCreate(bundle);
// Set our view from the "main" layout resource
SetContentView(Resource.Layout.Main);
//数据库文件路径
//要求WRITE_EXTERNAL_STORAGE权限
string dbPath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "book.db");
// Get our button from the layout resource,
// and attach an event to it
Button btnAdd = FindViewById<Button>(Resource.Id.btnAdd);
btnAdd.Click += (ss, ee) =>
{
List<Book> books = new List<Book>()
{
new Book() { Name = "射雕英雄传", PublishDate = new DateTime(1960, 1, 1), Author = "金庸", Price = 10.5f },
new Book() { Name = "神雕侠侣", PublishDate = new DateTime(1960, 2, 2), Author = "金庸", Price = 12.5f },
new Book() { Name = "倚天屠龙记", PublishDate = new DateTime(1960, 3, 3), Author = "金庸", Price = 16.5f },
new Book() { Name = "小李飞刀", PublishDate = new DateTime(1965, 5, 5), Author = "古龙", Price = 13.5f },
new Book() { Name = "绝代双骄", PublishDate = new DateTime(1965, 6, 6), Author = "古龙", Price = 15.5f },
};
using (var db = new MyBookDB(dbPath))
{
int count = db.InsertAll(books);
System.Diagnostics.Debug.Print($"{DateTime.Now}, 插入{count}条记录");
}
};
Button btnModify = FindViewById<Button>(Resource.Id.btnModify);
btnModify.Click += (ss, ee) =>
{
using (var db = new MyBookDB(dbPath))
{
var book = db.Books.FirstOrDefault(x => x.Name == "绝代双骄");
if (book != null)
{
book.Price += 1;
int count = db.Update(book);
System.Diagnostics.Debug.Print($"{DateTime.Now}, 修改{count}条记录");
}
}
};
Button btnDel = FindViewById<Button>(Resource.Id.btnDel);
btnDel.Click += (ss, ee) =>
{
using (var db = new MyBookDB(dbPath))
{
int count = db.Books.Delete(x => x.Name == "绝代双骄");
System.Diagnostics.Debug.Print($"{DateTime.Now}, 删除{count}条记录");
}
};
Button btnClear = FindViewById<Button>(Resource.Id.btnClear);
btnClear.Click += (ss, ee) =>
{
using (var db = new MyBookDB(dbPath))
{
int count = db.Books.Delete(x => true);
System.Diagnostics.Debug.Print($"{DateTime.Now}, 清除{count}条记录");
}
};
Button btnQuery = FindViewById<Button>(Resource.Id.btnQuery);
btnQuery.Click += (ss, ee) =>
{
using (var db = new MyBookDB(dbPath))
{
var books = db.Books.Where(x => x.Author == "金庸").OrderByDescending(x => x.PublishDate).ToList();
System.Diagnostics.Debug.Print($"{DateTime.Now}, 查到{books.Count}条记录");
records.Clear();
books.ForEach(x => records.Add($"{x.id}, {x.Name}, {x.Price}, {x.Author}, {x.PublishDate:yyyy-MM-dd}"));
adapter.Clear();
adapter.AddAll(records);
}
};
Button btnRefresh = FindViewById<Button>(Resource.Id.btnRefresh);
btnRefresh.Click += (ss, ee) =>
{
using (var db = new MyBookDB(dbPath))
{
var books = db.Books.ToList();
records.Clear();
books.ForEach(x => records.Add($"{x.id}, {x.Name}, {x.Price}, {x.Author}, {x.PublishDate:yyyy-MM-dd}"));
adapter.Clear();
adapter.AddAll(records);
}
};
records = new List<string>();
records.Add("没有可显示的记录1");
lsvRecords = FindViewById<ListView>(Resource.Id.lsvRecords);
adapter = new ArrayAdapter<string>(this, Android.Resource.Layout.SimpleListItem1, records);
lsvRecords.Adapter = adapter;
}
}
注意:
1,要求WRITE_EXTERNAL_STORAGE权限;
2,更新简单的string列表,用adapter.Clear,adapter.AddAll有效,用adapter.NotifyDataSetChanged无效;
采用了同样的SQLite.Net-PCL ORM,Android和Winform的代码几乎一模一样,可以跨平台共用一套业务代码,真正体现Xamarin的价值。