Retrieving data with SqliteDataReader
The SqliteDataReader
is a class used to retrieve data from
the database. It is used with the SqliteCommand
class to
execute an SQL SELECT
statement and then access the returned
rows. It provides fast, forward-only, read-only access to query results.
It is the most efficient way to retrieve data from tables.
Rather than using a constructor, we create an instance of the
SqliteDataReader
by calling the ExecuteReader()
object. While the
method of the
SqliteCommandSqlDataReader
is being used, the associated SqlConnection
serves the SqlDataReader
. No other operations can be performed on the
SqlConnection
other than closing it.
using System; using Mono.Data.Sqlite; public class Example { static void Main() { string cs = "URI=file:test.db"; using(SqliteConnection con = new SqliteConnection(cs)) { con.Open(); string stm = "SELECT * FROM Cars LIMIT 5"; using (SqliteCommand cmd = new SqliteCommand(stm, con)) { using (SqliteDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { Console.WriteLine(rdr.GetInt32(0) + " " + rdr.GetString(1) + " " + rdr.GetInt32(2)); } } } con.Close(); } } }
We get 5 cars from the Cars
table and print them to
the console.
using (SqliteDataReader rdr = cmd.ExecuteReader())
To create an SQLiteDataReader
object, we must call
the ExecuteReader()
method of the
SqliteCommand
object.
while (rdr.Read()) { Console.WriteLine(rdr.GetInt32(0) + " " + rdr.GetString(1) + " " + rdr.GetInt32(2)); }
The Read()
method advances the data reader to the next record.
It returns true if there are more rows; otherwise false. We can retrieve
the value using the array index notation, or use a specific method to access
column values in their native data types. The latter is more efficient.
$ mono retrieve.exe 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000
The first five rows of the Cars
table.
We can retrieve the fields by their column names.
using System; using Mono.Data.Sqlite; public class Example { static void Main() { string cs = "URI=file:test.db"; using(SqliteConnection con = new SqliteConnection(cs)) { con.Open(); string stm = "SELECT * FROM Cars LIMIT 5"; using (SqliteCommand cmd = new SqliteCommand(stm, con)) { using (SqliteDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { Console.Write("{0} ", rdr["Id"]); Console.Write("{0} ", rdr["Name"]); Console.Write("{0} \n", rdr["Price"]); } } } con.Close(); } } }
The example prints 5 rows from the Cars
table. This time we use
the column names to get the table fields.
while (rdr.Read()) { Console.Write("{0} ", rdr["Id"]); Console.Write("{0} ", rdr["Name"]); Console.Write("{0} \n", rdr["Price"]); }
The database table fields are referenced by their column names.
Multiple statements
The ADO.NET specification allows to execute multiple
statements in a single string. In case of queries, the
SqliteDataReader
returns multiple result sets.
It has the NextResult()
method to navigate
through the result sets.
using System; using Mono.Data.Sqlite; public class Example { static void Main() { string cs = "Data Source=:memory:"; using (SqliteConnection con = new SqliteConnection(cs)) { con.Open(); string stm = "SELECT 25; SELECT 44; SELECT 33"; using (SqliteCommand cmd = new SqliteCommand(stm, con)) { using (SqliteDataReader rdr = cmd.ExecuteReader()) { do { rdr.Read(); Console.WriteLine("{0}", rdr.GetInt32(0)); } while (rdr.NextResult()); } } con.Close(); } } }
We have three queries in one SQL string. There will be three result sets.
string stm = "SELECT 25; SELECT 44; SELECT 33";
There are three SELECT
statements. They are separated by the
semicolon character. Each of them will return a single value.
do { rdr.Read(); Console.WriteLine("{0}", rdr.GetInt32(0)); } while (rdr.NextResult());
The Read()
method advances the SqliteDataReader
to the next record. The GetInt32()
method retrieves the
value as a 32-bit signed integer. The NextResult()
advances the data reader to the next result.
$ dmcs multiple.cs -r:Mono.Data.Sqlite.dll $ mono multiple.exe 25 44 33
Running the example.
We have finished reading data with the SqliteDataReader
.