Xamarin Android （VS2019）之 Sqlite 数据库使用

Sqlite数据库 挺好用的，也不用其他权限，默认存储在内部存储的位置。

需要引用的包

Install-Package Mono.Data.Sqlite.Portable -Version 1.0.3.5
Install-Package sqlite-net-pcl -Version 1.7.335

模型如下:

    [Table("Info")]
    public class Info
    {
        [PrimaryKey, AutoIncrement, Column("Id")]
        public int Id { get; set; }
        [MaxLength(20)]
        public string Contet { get; set; }
    }

SqliteHelper

    /// <summary>
    /// 内置数据库的 dbhelper
    /// </summary>
    public class SqliteHelper
    {
        public string DbName;
        public SqliteHelper(string dbname)
        {
            this.DbName = dbname;
        }
        private string StoragePath()
        {
            var dbPath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), $"{DbName}.db3");
            return dbPath;
        }
        //执行不查询
        //返回符合条件的条数
        public int ExecuteNonQuery(string cmdText, params object[] parameters)
        {
            using (var conn = new SQLite.SQLiteConnection(StoragePath()))
            {
                return conn.Execute(cmdText, parameters);
            }
        }
        //获取查询的单条数据
        public T ExecuteScalar<T>(string cmdText, params object[] parameters)
        {
            using (var conn = new SQLite.SQLiteConnection(StoragePath()))
            {
                return conn.ExecuteScalar<T>(cmdText, parameters);
            }
        }
        public CreateTableResult CreateTable<T>()
        {
            using (var conn = new SQLite.SQLiteConnection(StoragePath()))
            {
                return conn.CreateTable<T>();
            }
        }
        public int Insert<T>(T result)
        {
            using (var conn = new SQLite.SQLiteConnection(StoragePath()))
            {
                return conn.Insert(result);
            }
        }
        public T Get<T>(object PK) where T : new()
        {
            using (var conn = new SQLite.SQLiteConnection(StoragePath()))
            {
                return conn.Get<T>(PK);
            }
        }
        public List<T> GetList<T>() where T : new()
        {
            using (var conn = new SQLite.SQLiteConnection(StoragePath()))
            {
                return conn.Table<T>().ToList();
            }
        }
        public int Delete<T>(object PK) where T : new()
        {
            using (var conn = new SQLite.SQLiteConnection(StoragePath()))
            {
                return conn.Delete<T>(PK);
            }
        }
        public List<T> Query<T>(string query, params object[] args) where T : new()
        {
            using (var conn = new SQLite.SQLiteConnection(StoragePath()))
            {
                return conn.Query<T>(query, args);
            }
        }
    }

安卓代码 (MainActivity.cs)

 public class MainActivity : AppCompatActivity
    {
        SqliteHelper sqliteHelper;
        protected override void OnCreate(Bundle savedInstanceState)
        {
            base.OnCreate(savedInstanceState);
            Xamarin.Essentials.Platform.Init(this, savedInstanceState);
            // Set our view from the "main" layout resource
            SetContentView(Resource.Layout.activity_main);
            sqliteHelper = new SqliteHelper("info");
            var btn = FindViewById<Button>(Resource.Id.button1);
            btn.Click += Btn_Click1; ;
        }

        private void Btn_Click1(object sender, System.EventArgs e)
        {
            var text = FindViewById<TextView>(Resource.Id.textView1);
            sqliteHelper.CreateTable<Info>();
            var a = sqliteHelper.Insert(new Info() { Contet = "你好" });
            text.Text += $"新增{a}行\r\n";
            var b = sqliteHelper.Get<Info>(1);
            var c = sqliteHelper.GetList<Info>();
            text.Text += $"查询到的数据{b.Contet}\r\n";
        }
}

 

视图部分 (activity_main.xml)

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout
    xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical">
    <TextView
        android:id="@+id/textView1"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_centerInParent="true"
        android:scrollHorizontally="false"
        android:inputType="textMultiLine"
        android:singleLine="false"
        android:text="Hello World!" />
    <Button
        android:id="@+id/button1"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="点击查询"/>
</LinearLayout>

最后结果如下