# SQLite
###### tags: `Forms`, `sqlite`

:::success
* [Todo](https://github.com/xamarin/xamarin-forms-samples/blob/master/Todo/Todo/Data/TodoItemDatabase.cs)
* [usage](https://developer.xamarin.com/guides/android/data-and-cloud-services/data-access/part-3-using-sqlite-orm/)
* [understanding SQLite - Xamarin University](https://www.youtube.com/watch?v=YCgwZdZxfwo)
* [Storing and Retrieving Data with SQLite NET - Xamarin University](https://www.youtube.com/watch?v=FdqdqVXCdnQ)
* [Xamarin Forms Working with Exist Database](https://www.youtube.com/watch?v=KVR8larJCDY)
:::

### [What is SQLite](http://www.sqlitetutorial.net/what-is-sqlite/)
SQLite is a software library that provides a relational database management system. Light weight in terms of setup, database administration, and required resource.

###### SQLite has the following noticeable features:
* self-contained
* serverless
* zero-configuration
* transactional

### Common usage
#### Define Class (With Table name!!)

[Table("Items")]
public class Stock {
    [PrimaryKey, AutoIncrement, Column("_id")]
    public int Id { get; set; }
    
    [MaxLength(8)]
    public string Symbol { get; set; }
}

#### Get All

// method 1
database.Table<TodoItem>().ToListAsync();

// method 2
database.QueryAsync<TodoItem>("SELECT * FROM [TodoItem]");

#### Create or Connect a Database
>You do not need to check if the file already exists – it will automatically be created if required, otherwise the existing database file will be opened.

var db = new SQLiteConnection (dbPath);

#### dbPath

// Android
Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), dbName);

// iOS
var personalFolder = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
string libraryFolder = Path.Combine(personalFolder, "..", "Library", "Databases");
var path = Path.Combine(libraryFolder, dbName);

##### 補充～～ [data storage](https://www.youtube.com/watch?v=Xrh6ZJcxtZ8)
#### Create or Get Table

// Creat Table with data structure of `stock`
db.CreateTable<Stock> ();

// Connect to Table whose data structure is `stock`
db.Table<Stock>();

#### Save Data

// save new object
db.Insert(newStock); 

// alter existing object
db.Update(existingStock);