Caching Metadata

For each query you wish to perform, the RSSBus ADO.NET Provider for Amazon SimpleDB must rely on the metadata or schemas that describe the tables queried. If the metadata is not present, the RSSBus ADO.NET Provider for Amazon SimpleDB establishes a separate connection with the live data source to retrieve the metadata before querying the data. This process can be slow and expensive, especially if multiple tables need to be queried.

The solution is to cache table metadata. Instead of retrieving the metadata fresh for each table per each query, caching the metadata locally stores the metadata that describes the table. Thus, the metadata from the live data source need be fetched only once. To enable this feature, simply set a cache location in your connection string and RSSBus ADO.NET Provider for Amazon SimpleDB handles the rest automatically. The RSSBus ADO.NET Provider for Amazon SimpleDB will cache the metadata the first time it is needed and use it onwards.

Do note that altering the schema on the live data source -- say, in the event a column is added or dropped -- is not reflected automatically in the local metadata cache. So you would want to delete the cache file if you expect your data definitions to have changed.

The following code can be used to build the metadata cache. This is especially useful in using the data provider in Entity Framework.

C#

String connectionString = "Cache Location=C:\\cache.db;Access Key=My Amazon Access Key;Secret Key=My Amazon Secret Key;";
using (SimpleDBConnection conn = new SimpleDBConnection(connectionString)) {
  SimpleDBDataAdapter adp = new SimpleDBDataAdapter("SELECT * FROM sys_tables", conn);
  DataTable table = new DataTable();
  adp.Fill(table);
  Console.WriteLine("Total number of tables found: " + table.Rows.Count);

  for (int i = 0; i < table.Rows.Count; i++) {
    SimpleDBCommand cmd = new SimpleDBCommand(String.Format("SELECT * FROM sys_tablecolumns WHERE TableName=[{0}]", (String)table.Rows[i]["TableName"]), conn);
    SimpleDBDataReader rdr = cmd.ExecuteReader();
    Console.WriteLine("Cached metadata for table " + table.Rows[i]["TableName"] + " [" + i + "]");
  }
  Console.WriteLine();
  Console.WriteLine("All tables cached.");
}

VB.NET

Dim connectionString As String = "Cache Location=C:\\cache.db;Access Key=My Amazon Access Key;Secret Key=My Amazon Secret Key;"
Using conn As New SimpleDBConnection(connectionString)
	Dim adp As New SimpleDBDataAdapter("SELECT * FROM sys_tables", conn)
	Dim table As New DataTable()
	adp.Fill(table)
	Console.WriteLine("Total number of tables found: " + table.Rows.Count)

	For i As Integer = 0 To table.Rows.Count - 1
		Dim cmd As New SimpleDBCommand([String].Format("SELECT * FROM sys_tablecolumns WHERE TableName=[{0}]", DirectCast(table.Rows(i)("TableName"), [String])), conn)
		Dim rdr As SimpleDBDataReader = cmd.ExecuteReader()
		Console.WriteLine("Cached metadata for table " + table.Rows(i)("TableName") + " [" + i + "]")
	Next
	Console.WriteLine()
	Console.WriteLine("All tables cached.")
End Using