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

骑猪看日落 2022-10-05 00:54 145阅读 0赞

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>

最后结果如下

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2tlc3NoZWk_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2tlc3NoZWk_size_16_color_FFFFFF_t_70]: /images/20221005/53c6e49582324bd880def6f332c38bca.png