Question

Metadata is information about the data in the database. Metadata in SQLite contains information about the tables and columns, in which we store data. Number of rows affected by an SQL statement is metadata. Number of rows and columns returned in a result set belong to metadata as well.

Metadata in SQLite can be obtained using the PRAGMA command. SQLite objects may have attributes, which are metadata. Finally, we can also obtain specific metatada from querying the SQLite system sqlite_master table.

using System;
using Mono.Data.Sqlite;

public class Example
{

  static void Main() 
  {
    string cs = "URI=file:test.db";

    string nrows = null;

    try {
      Console.Write("Enter rows to fetch: ");
      nrows = Console.ReadLine();
    } catch (FormatException e)
    {
      Console.WriteLine(e.ToString());
    }

    using (SqliteConnection con = new SqliteConnection(cs))
    {
      con.Open();

      using (SqliteCommand cmd = con.CreateCommand())
      {

        cmd.CommandText = "SELECT * FROM Cars LIMIT @Id";
        cmd.Prepare();

        cmd.Parameters.AddWithValue("@Id", Int32.Parse(nrows));

        int cols = 0;
        int rows = 0;

        using (SqliteDataReader rdr = cmd.ExecuteReader())
        {

          cols = rdr.FieldCount;
          rows = 0;

          while (rdr.Read()) 
          {
            rows++;
          }

          Console.WriteLine("The query fetched {0} rows", rows);
          Console.WriteLine("Each row has {0} cols", cols);
        }  
      }

      con.Close();
    }
  }
}

In the above example, we get the number of rows and columns returned by a query.

try {
  Console.Write("Enter rows to fetch: ");
  nrows = Console.ReadLine();
} catch (FormatException e)
{
  Console.WriteLine(e.ToString());
}

The example asks for the number of rows on the command line.

cmd.CommandText = "SELECT * FROM Cars LIMIT @Id";
cmd.Prepare();

cmd.Parameters.AddWithValue("@Id", Int32.Parse(nrows));

We limit the selected rows to the number provided to the program.

cols = rdr.FieldCount;

The number of returned columns can be easily get from the FieldCount property of the SqliteDataReader object.

while (rdr.Read()) 
{
  rows++;
}

We count the number of rows in the result set.

$ mono fields_rows.exe 
Enter rows to fetch: 4
The query fetched 4 rows
Each row has 3 cols

Output.

Column headers

Next we will show, how to print column headers with the data from a database table.

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())
        {
          Console.WriteLine(String.Format("{0, -3} {1, -8} {2, 8}", 
            rdr.GetName(0), rdr.GetName(1), rdr.GetName(2)));

          while (rdr.Read()) 
          {
            Console.WriteLine(String.Format("{0, -3} {1, -8} {2, 8}", 
              rdr.GetInt32(0), rdr.GetString(1), rdr.GetInt32(2)));
          }
        }
      }

      con.Close();
    }
  }
}

In this program, we select 5 rows from the Cars table with their column names.

using (SqliteDataReader rdr = cmd.ExecuteReader())

We create a SqliteDataReader object.

Console.WriteLine(String.Format("{0, -3} {1, -8} {2, 8}", 
  rdr.GetName(0), rdr.GetName(1), rdr.GetName(2)));

We get the names of the columns with the GetName() method of the reader. The String.Format() method is used to format the data.

while (rdr.Read()) 
{
  Console.WriteLine(String.Format("{0, -3} {1, -8} {2, 8}", 
    rdr.GetInt32(0), rdr.GetString(1), rdr.GetInt32(2)));
}     

We print the data that was returned by the SQL statement to the terminal.

$ dmcs columns.cs -r:Mono.Data.Sqlite.dll
$ mono columns.exe 
Id  Name    Price
1   Audi    52642
2   Mercedes  57127
3   Skoda    9000
4   Volvo     29000
5   Bentley  350000

Ouput of the program.

Affected rows

In the following example, we will find out how many changes have been done by a particular SQL command.

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();

      using (SqliteCommand cmd = new SqliteCommand(con))
      {
        cmd.CommandText = "CREATE TABLE Friends(Id INT, Name TEXT)";
        cmd.ExecuteNonQuery();
        cmd.CommandText = "INSERT INTO Friends VALUES(1, 'Tom')";
        cmd.ExecuteNonQuery();
        cmd.CommandText = "INSERT INTO Friends VALUES(2, 'Jane')";
        cmd.ExecuteNonQuery();
        cmd.CommandText = "INSERT INTO Friends VALUES(3, 'Rebekka')";
        cmd.ExecuteNonQuery();
        cmd.CommandText = "INSERT INTO Friends VALUES(4, 'Lucy')";
        cmd.ExecuteNonQuery();
        cmd.CommandText = "INSERT INTO Friends VALUES(5, 'Robert')";
        cmd.ExecuteNonQuery();

        cmd.CommandText = "DELETE FROM Friends WHERE Id IN (3, 4, 5)";
        int n = cmd.ExecuteNonQuery();

        Console.WriteLine("The statement has affected {0} rows", n);
        
       }

       con.Close();
    }
  }
}

We create a Friends table in memory. In the last SQL command, we delete three rows. The ExecuteNonQuery() method returns the number of rows affected by the last SQL command.

cmd.CommandText = "DELETE FROM Friends WHERE Id IN (3, 4, 5)";

In this SQL statement, we delete three rows.

int n = cmd.ExecuteNonQuery();

We find out the number of changes done by the last SQL statement.

$ mono affected.exe 
The statement has affected 3 rows

Example output.

Table schema

There is a GetSchemaTable() method which returns metadata about each column. It returns many values, among others the column name, column size, the base table name or whether the column is unique or not.

using System;
using System.Data;
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 3";   

      using (SqliteCommand cmd = new SqliteCommand(stm, con))
      {

        using (SqliteDataReader rdr = cmd.ExecuteReader())
        {
          DataTable schemaTable = rdr.GetSchemaTable();

          foreach (DataRow row in schemaTable.Rows)
          {
            foreach (DataColumn col in schemaTable.Columns)
              Console.WriteLine(col.ColumnName + " = " + row[col]);
            Console.WriteLine();
          }
        }
      }

      con.Close();
     }
  }
}

The example prints lots of metadata about table columns.

DataTable schemaTable = rdr.GetSchemaTable();

We get the schema table.

foreach (DataRow row in schemaTable.Rows)
{
  foreach (DataColumn col in schemaTable.Columns)
    Console.WriteLine(col.ColumnName + " = " + row[col]);
  Console.WriteLine();
}

We go through the schema table rows, which hold the metadata, and print them to the console.

$ dmcs schema.cs -r:Mono.Data.Sqlite.dll -r:System.Data.dll
$ mono schema.exe 
ColumnName = Id
ColumnOrdinal = 0
ColumnSize = 8
NumericPrecision = 19
NumericScale = 0
IsUnique = True
IsKey = True
...

Excerpt from the example output.

Table names

In our last example related to the metadata, we will list all tables in the test.db database.

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 name FROM sqlite_master
        WHERE type='table' ORDER BY name";   

      using (SqliteCommand cmd = new SqliteCommand(stm, con))
      {
        using (SqliteDataReader rdr = cmd.ExecuteReader())
        {
          while (rdr.Read()) 
          {
            Console.WriteLine(rdr.GetString(0));
          }
        }
      }  
      
      con.Close();  
    }
  }
}

The code example prints all available tables in the chosen database to the terminal.

string stm = @"SELECT name FROM sqlite_master
  WHERE type='table' ORDER BY name"; 

The table names are retrieved from the sqlite_master table.

$ mono tables.exe 
Cars
Friends
Images

These were the tables on our system.

In this part of the SQLite C# tutorial, we have worked with database metadata.