VS2015 Xamarin使用经验4-SQLite ORM

来源：互联网发布：8051单片机复位电路编辑：程序博客网时间：2024/06/05 11:03

新建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的价值。

阅读全文

0 0