Many apps need to use databases, irrespective of whether the app is database-centric or even just to store pieces of data in a structured form. Android provides SQLite as a database engine and .NET provides ADO.NET as an interface.
This recipe demonstrates how we can make use of ADO.NET to interact with a SQLite database using SQL. It is assumed that you have some SQL knowledge to construct queries.
Using ADO.NET with SQLite is easy and not much different from working with any ADO.NET provider. It is fairly straightforward to create and interact with a SQLite database:
- To start with, we need to add a reference to
System.DataandMono.Data.SQLite. - We can now start selecting what database file we will use. If a database file does not exist, one will be created for us. To do this, we use a connection string:
var databasePath = Path.Combine(FilesDir.AbsolutePath, "database.sqlite"); var connectionString = string.Format("Data Source={0}", databasePath); - We can now create the connection to the database from the connection string. When we open the connection, we initiate the process that allows us to communicate with the database:
using (var conn = new SqliteConnection(connectionString)) { conn.Open(); // use the database here } - Using the open connection, we can start creating the database structure. To create a simple table, we execute normal SQL commands:
string createTable = @" CREATE TABLE IF NOT EXISTS [MyTable] ( id INTEGER PRIMARY KEY AUTOINCREMENT, firstName TEXT, lastName TEXT );"; using (var cmd = new SqliteCommand(createTable, conn)) { cmd.ExecuteNonQuery(); } - Once we have a table, we can insert values into the table by using commands and parameters:
string insertQuery = @" INSERT INTO [MyTable] (firstName, lastName) VALUES (@firstName, @lastName);"; using (var cmd = new SqliteCommand(insertQuery, conn)) { cmd.Parameters.AddWithValue("@firstname", "Bill"); cmd.Parameters.AddWithValue("@lastName", "Gates"); cmd.ExecuteNonQuery(); } - In order to read the data in the database, we use commands and data readers:
string selectQuery = "SELECT * FROM [MyTable]"; List<string> names = new List<string>(); using (var cmd = new SqliteCommand(selectQuery, conn)) using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { names.Add(reader ["lastname"].ToString ()); } } - If we have multiple operations that we need to perform on the database, we can use transactions:
using (var trans = conn.BeginTransaction()) { // a series of operations trans.Commit(); }
ADO.NET provides an interface for working with many different types of data sources, ranging from databases to XML files. Using a means of abstraction, ADO.NET can handle almost any type of database, from servers such as a SQL Server to embedded ones, such as SQLite.
More information about using ADO.NET can be found on the MSDN website: https://msdn.microsoft.com/en-us/library/e80y5yhx.aspx.
When accessing a database, there are a few steps which open a connection, executing commands, and iterating results with readers.
When connecting to a database, we need to make use of a connection. A connection contains all the information required to identify, locate, authenticate, and communicate with the database. In the case of SQLite, we make use of a SqliteConnection type, passing in a connection string that contains a path to the data source or database file.
Once we have a connection, we need to ensure that we open it. This initiates the connection and prepares the connection for communication with the actual database.
Once we have an open connection, we can start executing commands. A command, or a SqliteCommand command in the case of SQLite, contains information about a particular action that we wish to perform. The information includes a SQL query and, optionally, parameters and values, which will be substituted during the execution.
Commands have three basic types of execution: readers, scalars, and nonqueries. For example, when creating a table, no rows or values are returned, so we execute using the ExecuteNonQuery() method as this is an instruction to the database to do something. If we only expect a single value result, we can use the ExecuteScalar() method, which returns a single primitive type. Often, we will request multiple rows from the database, for which we use the ExecuteReader() method that gives us a result in the form of a data reader.
A data reader in the case of SQLite is an instance of the SqliteDataReader type. This provides a means to iterate through the rows returned and handle them as we wish. The reader has several methods that we use to access the data returned. A reader is a forward-only, read-only stream of data that allows us to access data one row at a time. We can check whether any rows are returned using the HasRows property, and we can also check the number of rows returned using the RowsAffected property. As we step through the data reader using the Read() method, we can access individual columns using the indexer or the various getter methods.
We can use transactions to group a series of commands into a single, reversible operation. We use transactions to roll back any and all operations in a series, if any operation in that set fails. Using transactions ensures that our database remains consistent even if there is a failure in either the system or the app.