VS2015 Xamarin使用经验4-SQLite ORM

新建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;
        }
    }