Bingue's 程式學習筆記

nuget : sqlite-net-pcl
在ProjectName 建立介面 連線設定

 interface ISQLite
 {
     SQLiteConnection GetConnection();
 }

在 ProjectName.Android 建立 SQL Class

[assembly: Dependency(typeof(SQLite_Android))]


public class SQLite_Android : ISQLite
{
   public SQLite_Android()
   {
   }

   #region ISQLite implementation
   public SQLite.SQLiteConnection GetConnection()
   {
      var sqliteFilename = "DoggyDB.db3";
      var documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal); // Documents folder
      var path = Path.Combine(documentsPath, sqliteFilename);
      var conn = new SQLite.SQLiteConnection(path);

      // Return the database connection
      return conn;
    }
    #endregion
}

在ProjectName 建立 XAML

<StackLayout Orientation="Vertical" VerticalOptions="Center" HorizontalOptions="Center" >
    <Label x:Name="labelMessage" Text="歡迎來到 SQLite 的世界" VerticalOptions="Center" HorizontalOptions="Center" />

    <Label x:Name="labelMessagePath" Text="" VerticalOptions="Center" HorizontalOptions="Center" LineBreakMode="WordWrap"/>
    <Label x:Name="labelMessageWrite" Text="" VerticalOptions="Center" HorizontalOptions="Center" />
    <Button x:Name="button寫入資料庫" Text="寫入資料庫" />

    <Label x:Name="labelMessageRead" Text="" VerticalOptions="Center" HorizontalOptions="Center" />
    <Button x:Name="button從資料庫讀出" Text="從資料庫讀出" />
</StackLayout>

在ProjectName XAML.cs

DoggyDatabase fooDoggyDatabase;
public SqlLitePage ()
{
    InitializeComponent ();

    fooDoggyDatabase = new DoggyDatabase();
    labelMessagePath.Text = $"路徑: {fooDoggyDatabase.DBPath}";

    button寫入資料庫.Clicked += (s, e) =>
    {
       fooDoggyDatabase.DeleteAll();
       fooDoggyDatabase.SaveItem(new MyRecord
       {
           UserName = "Vulcan Lee",
           SelectItem = "一顆蘋果",
           Done = false,
       });
       labelMessageWrite.Text = $"資料已經寫入資料表內";
     };

     button從資料庫讀出.Clicked += (s, e) =>
     {
        var fooItem = fooDoggyDatabase.GetItems().FirstOrDefault();

        labelMessageRead.Text = $"從資料表內讀取: {fooItem.UserName} / {fooItem.SelectItem}";
     };
}

建立操作資料庫類別

public class DoggyDatabase
{
    static object locker = new object();
    public string DBPath { get; set; }
    SQLiteConnection database;

    public DoggyDatabase()
    {
        database = DependencyService.Get<ISQLite>().GetConnection();
        DBPath = database.DatabasePath;
       
        // create the tables
        database.CreateTable<MyRecord>();
    }

    public IEnumerable<MyRecord> GetItems()
    {
        lock (locker)
        {
            return (from i in database.Table<MyRecord>() select i).ToList();
        }
    }

    public IEnumerable<MyRecord> GetItemsNotDone()
    {
         lock (locker)
         {
             return database.Query<MyRecord>("SELECT * FROM [MyRecord] WHERE [Done] = 0");
         }
    }

    public MyRecord GetItem(int id)
    {
         lock (locker)
         {
             return database.Table<MyRecord>().FirstOrDefault(x => x.ID == id);
         }
    }

    public int SaveItem(MyRecord item)
    {
         lock (locker)
         {
             if (item.ID != 0)
             {
                 database.Update(item);
                 return item.ID;
             }
             else
             {
                 return database.Insert(item);
             }
          }
     }

     public int DeleteItem(int id)
     {
         lock (locker)
         {
             return database.Delete<MyRecord>(id);
         }
     }

     public void DeleteAll()
     {
          var fooItems = GetItems().ToList();
          foreach (var item in fooItems)
          {
              DeleteItem(item.ID);
          }
     }
}

建立資料表

public class MyRecord
{
    public MyRecord()
    {
    }

    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }
    public string UserName { get; set; }
    public string SelectItem { get; set; }
    public bool Done { get; set; }
}