SQLiteManager: Selecting data

Selecting all rows from a table

Returning data from our database is pretty easy! If we want all users stored in our database we simply write:

List<User> users = User.LoadAllFromDatabase(); // Returns all users

This will return all objects for "User" class, but we can limit our results by adding conditions.

Adding conditions to SELECT statements

By passing conditions to "LoadAllFromDatabase()" we can limit our results. In this case we return only those users that are named "John Doe":

List<User> users = User.LoadAllFromDatabase(new Condition("name", "John Doe")); // Returns all users with the name "John Doe"

Selecting a specific row from a table

If you just want to return a single object, you can use "LoadFromDatabase()". This method also supports conditions, but can be executed without passing any condition to it. In this case it will return the last row in the table.

User lastUser = User.LoadFromDatabase(); // Last user in table

User specificUser = User.LoadFromDatabase(new Condition("name", "John Doe")); // First user that meets the condition

Raw data access

Of course you can always get the "raw data" from the database directly by using the "Select()" methods under "Database.Instance[tableName]". The only difference between this method in the new way over the DatabaseEntry class is that this method returns column data for each row, whereas the new method returns the object itself defined as a table

ResultSet results = Database.Instance["users"].Select(); // Returns all users

ResultSet results = Database.Instance["users"].Select(new Condition("name", "John Doe")); // Returns all users with the name "John Doe"

Limiting the columns returned by SELECT

By adding a list of columns you can select which columns shall be returned in your result set

ITable userTable = Database.Instance["users"]; // Get user table so we have access to the column definitions

List<IColumn> columns = new List<IColumn>() // Define which columns should be returned
{
  userTable["name"],
  userTable["birthday"]
}

ResultSet result = userTable.Select(columns, new Condition("name", "John Doe"));

Or you can tell SQLiteManager to exclude the columns you provide:

ITable userTable = Database.Instance["users"]; // Get user table so we have access to the column definitions

List<IColumn> columns = new List<IColumn>() // Define which columns should be excluded
{
  userTable["guid"],
  userTable["password"]
}

ResultSet result = userTable.Select(columns, true, new Condition("name", "John Doe"));

Initializing database objects - the other way

If you for example pulled some user data, but excluded all but two columns (as in the example above) you can still turn the raw results into the corresponding object! Just declare a new constructor in your database object (in our case the "User" class) like this:

[SqliteTable]
class User : DatabaseEntry<User>
{
	[SqliteUnique]
	public string Guid {get; set; }
	
	public string Name { get; set; }
	
	public DateTime Birthday { get; set; }
	
	[SqliteNotNull]
	public string Password { get; set; }
	
	[SqliteDefaultValue(true)]
	public bool DarkMode {get; set; }
	
	[SqliteIgnore]
	public Image ProfilePicture {get; set; }
	
	[SqliteForeignKey("mapping_user_posts")] // Tell SQLiteManager that our relationship table will be called "mapping_user_posts"
	public List<Post> Posts {get; set; } // Contains all posts made by this user
	
	public User(TableDefinition<User> table): base(table) { } // Default constructor; required by DatabaseEntry<T>
	
	public User(TableDefinition<User> table, RowData rowData): base(table, rowData) { } // Constructor required to load row data into object
	
	// Constructors, methods, etc...
}

---

Next up: Insert/Update/Delete