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:
System.Data
and Mono.Data.SQLite
.var databasePath = Path.Combine(FilesDir.AbsolutePath, "database.sqlite"); var connectionString = string.Format("Data Source={0}", databasePath);
using (var conn = new SqliteConnection(connectionString)) { conn.Open(); // use the database here }
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(); }
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(); }
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 ()); } }
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.