**Course Title:** .NET MAUI App Development **Section Title:** Data Storage and SQLite Integration **Topic:** Performing CRUD operations (Create, Read, Update, Delete) ## Introduction In the previous topic, we introduced SQLite as a lightweight disk-based database that can be used for local storage in .NET MAUI applications. In this topic, we will dive deeper into performing CRUD operations using SQLite. CRUD stands for Create, Read, Update, and Delete, which are the basic operations that you can perform on a database. ## Performing CRUD Operations ### Create Operation To perform a create operation, you can use the `Insert` method provided by the `Table<T>` class in SQLite. The `Insert` method takes an object of type `T` as a parameter, where `T` is the type of the table. Here's an example of how you can create a new record in a `Users` table: ```csharp public class User { [PrimaryKey, AutoIncrement] public int Id { get; set; } public string Name { get; set; } public string Email { get; set; } } public class UserService { private readonly SQLiteConnection _connection; public UserService(SQLiteConnection connection) { _connection = connection; } public void CreateUser(User user) { _connection.Insert(user); } } ``` ### Read Operation To perform a read operation, you can use the `Get` method provided by the `Table<T>` class in SQLite. The `Get` method takes an `int` parameter, which is the primary key of the record you want to retrieve. Here's an example of how you can retrieve a user by their `Id`: ```csharp public class UserService { private readonly SQLiteConnection _connection; public UserService(SQLiteConnection connection) { _connection = connection; } public User GetUser(int id) { return _connection.Get<User>(id); } } ``` Alternatively, you can use the `Table<T>` class to retrieve all records from a table. Here's an example: ```csharp public class UserService { private readonly SQLiteConnection _connection; public UserService(SQLiteConnection connection) { _connection = connection; } public IEnumerable<User> GetAllUsers() { return _connection.Table<User>(); } } ``` ### Update Operation To perform an update operation, you can use the `Update` method provided by the `Table<T>` class in SQLite. The `Update` method takes an object of type `T` as a parameter, where `T` is the type of the table. Here's an example of how you can update a user: ```csharp public class UserService { private readonly SQLiteConnection _connection; public UserService(SQLiteConnection connection) { _connection = connection; } public void UpdateUser(User user) { _connection.Update(user); } } ``` ### Delete Operation To perform a delete operation, you can use the `Delete` method provided by the `Table<T>` class in SQLite. The `Delete` method takes an object of type `T` as a parameter, where `T` is the type of the table. Here's an example of how you can delete a user: ```csharp public class UserService { private readonly SQLiteConnection _connection; public UserService(SQLiteConnection connection) { _connection = connection; } public void DeleteUser(User user) { _connection.Delete(user); } } ``` ## Best Practices When working with SQLite in .NET MAUI applications, it's essential to follow best practices to ensure data integrity and security. Here are some best practices to keep in mind: * Always use parameterized queries to prevent SQL injection attacks. * Always use the `[PrimaryKey, AutoIncrement]` attribute to define the primary key of a table. * Always use the `Table<T>` class to interact with a table, rather than using raw SQL queries. * Always use the `Get` method to retrieve a record, rather than using `Query<T>()`. * Always use the `Update` method to update a record, rather than using `Query<T>()` with a `WHERE` clause. ## Conclusion In this topic, we covered performing CRUD operations using SQLite in .NET MAUI applications. We discussed creating, reading, updating, and deleting records, as well as best practices for working with SQLite. By following these best practices, you can ensure data integrity and security in your applications. ## Additional Resources * [SQLite.NET Documentation](https://sqlite-net.github.io/) * [Microsoft Documentation: .NET MAUI Data Storage](https://docs.microsoft.com/en-us/dotnet/maui/data-storage/) ## What's Next? In the next topic, we will cover using Entity Framework Core with .NET MAUI applications. If you have any questions or need further clarification, please leave a comment below.