Getting Started

Opening a Connection

To get started, you need to get an instance of DbProviderFactory that implements the Table Storage integration. To do so, make invoke the GetFactory method of DbProviderFactories with the provider name Magurany.Data.TableStorageClient.

DbProviderFactory factory = DbProviderFactories.GetFactory("Magurany.Data.TableStorageClient");

This gives you access to the all the classes for accessing Table Storage.

Now you can create a connection and set the connection string, which you can get from the CloudConfigurationManager class.

using(DbConnection connection = factory.GetConnection())
{
	connection.ConnectionString = CloudConfigurationManager.GetSetting("StorageConnectionString");
	connection.Open();
}

Querying Entities

Create a command instance and execute a query. When you create an instance of a DbCommand class via the CreateCommand() method of the connection instance, the connection will be set for you. Note that the example uses the CreateCommand() and AddParameter() extension methods. CreateCommand() allows you to specify the command text as a parameter and AddParameter() handles creating and adding a parameter to the command instance.

DbProviderFactory factory = DbProviderFactories.GetFactory("Magurany.Data.TableStorageClient");

using(DbConnection connection = factory.CreateConnection())
{
	connection.ConnectionString = CloudConfigurationManager.GetSetting("StorageConnectionString");
	connection.Open();

	DbCommand selectCommand = connection.CreateCommand("GET /MyTable()?$filter=PartitionKey eq @PartitionKey");
	selectCommand.AddParameter("PartitionKey", DbType.String, "MyPartitionKey");

	using(DbDataReader reader = selectCommand.ExecuteReader())
	{
		while(reader.Read())
		{
			// Do something awesome
		}
	}
}

You can also use a DbDataAdapter to populate a DataTable. The AddMappedParameter() method is an extension method similar to AddParameter and simplifies the process of creating and adding parameters to the command instance.

DbProviderFactory factory = DbProviderFactories.GetFactory("Magurany.Data.TableStorageClient");

using(DbConnection connection = factory.CreateConnection())
{
	connection.ConnectionString = CloudConfigurationManager.GetSetting("StorageConnectionString");
	connection.Open();

	DbCommand selectCommand = connection.CreateCommand("GET /MyTable()?$filter=PartitionKey eq @PartitionKey");
	selectCommand.AddParameter("PartitionKey", DbType.String, "MyPartitionKey");

	DataTable table = new DataTable();

	DbDataAdapter adapter = factory.CreateDataAdapter();
	adapter.SelectCommand = selectCommand;
	adapter.Fill(table);
}

It's worth noting the query syntax. Instead of attempting to translate SQL query syntax, the command expects an HTTP method and URI. The URI should follow the same syntax defined by the Azure Table Storage query syntax. Also note that you do not have to encode the URIs. The provider will handle that for you.

Inserting Entities

Now that you can query Table Storage entities, you will want to insert some. Create a DbCommand instance with a POST query URI. Since the provider doesn't support SQL query syntax, you need a way to specify the values of the fields to insert. To do so, add parameters to the DbCommand instance. The names of the parameters will become the table field names.

DbProviderFactory factory = DbProviderFactories.GetFactory("Magurany.Data.TableStorageClient");

using(DbConnection connection = factory.CreateConnection())
{
	connection.ConnectionString = CloudConfigurationManager.GetSetting("StorageConnectionString");
	connection.Open();

	DbCommand insertCommand = connection.CreateCommand("POST /MyTable()");
	insertCommand.AddParameter("PartitionKey", DbType.String, "MyPartitionKey");
	insertCommand.AddParameter("RowKey", DbType.String, "RowKey1");
	insertCommand.AddParameter("MyField", DbType.String, "Some Data");
	insertCommand.ExecuteNonQuery();
}

You can also insert entities from a DataTable with a DbDataAdapter.

DataTable table = new DataTable();
table.Columns.Add("PartitionKey");
table.Columns.Add("RowKey");
table.Columns.Add("MyField");

DataRow row = table.NewRow();
row["PartitionKey"] = "MyPartitionKey";
row["RowKey"] = "RowKey2";
row["MyField"] = "Some Data";

table.Rows.Add(row);

DbProviderFactory factory = DbProviderFactories.GetFactory("Magurany.Data.TableStorageClient");

using(DbConnection connection = factory.CreateConnection())
{
	connection.ConnectionString = CloudConfigurationManager.GetSetting("StorageConnectionString");
	connection.Open();

	DbCommand insertCommand = connection.CreateCommand("POST /MyTable()");
	insertCommand.AddMappedParameter("PartitionKey", DbType.String, "PartitionKey");
	insertCommand.AddMappedParameter("RowKey", DbType.Guid, "RowKey");
	insertCommand.AddMappedParameter("MyField", DbType.String, "MyField");

	DbDataAdapter adapter = factory.CreateDataAdapter();
	adapter.InsertCommand = insertCommand;
	adapter.Update(table);
}

After either of these examples are run, your table will contain rows with values for the fields PartitionKey, RowKey and MyField.

Updating Entities

Updates can be accomplished by creating a command with an HTTP PUT. Similar to insert commands, you will need to add parameters to the command containing the names and values for the fields you want to update. Note that you have to specify the PartitionKey and the RowKey in the URI to identify the row you wish to update.

using(DbConnection connection = factory.CreateConnection())
{
	connection.ConnectionString = CloudConfigurationManager.GetSetting("StorageConnectionString");
	connection.Open();

	DbCommand updateCommand = connection.CreateCommand("PUT /MyTable(PartitionKey=@PartitionKey,RowKey=@RowKey)");
	updateCommand.AddParameter("PartitionKey", DbType.String, "MyPartitionKey");
	updateCommand.AddParameter("RowKey", DbType.String, "RowKey1");
	updateCommand.AddParameter("MyField", DbType.String, "Some Data Updated");
	updateCommand.ExecuteNonQuery();
}

Once again, you can use a DbDataAdapter to update the table.

DataTable table = new DataTable();
table.Columns.Add("PartitionKey");
table.Columns.Add("RowKey");
table.Columns.Add("MyField");

DataRow row = table.NewRow();
row["PartitionKey"] = "MyPartitionKey";
row["RowKey"] = "RowKey2";
row["MyField"] = "Some Data";

table.Rows.Add(row);
table.AcceptChanges();

row["MyField"] = "Some Data Updated";

DbProviderFactory factory = DbProviderFactories.GetFactory("Magurany.Data.TableStorageClient");

using(DbConnection connection = factory.CreateConnection())
{
	connection.ConnectionString = CloudConfigurationManager.GetSetting("StorageConnectionString");
	connection.Open();

	DbCommand updateCommand = connection.CreateCommand("PUT /MyTable(PartitionKey=@PartitionKey,RowKey=@RowKey)");
	updateCommand.AddMappedParameter("PartitionKey", DbType.String, "PartitionKey");
	updateCommand.AddMappedParameter("RowKey", DbType.String, "RowKey");
	updateCommand.AddMappedParameter("MyField", DbType.String, "MyField");

	DbDataAdapter adapter = factory.CreateDataAdapter();
	adapter.UpdateCommand = updateCommand;
	adapter.Update(table);
}

Deleting Entities

To delete entities, create a DbCommand instance with an HTTP POST URI containing the PartitionKey and RowKey of row you wish to delete.

using(DbConnection connection = factory.CreateConnection())
{
	connection.ConnectionString = CloudConfigurationManager.GetSetting("StorageConnectionString");
	connection.Open();

	DbCommand deleteCommand = connection.CreateCommand("DELETE /MyTable(PartitionKey=@PartitionKey,RowKey=@RowKey)");
	deleteCommand.AddParameter("PartitionKey", DbType.String, "MyPartitionKey");
	deleteCommand.AddParameter("RowKey", DbType.String, "RowKey1");
	deleteCommand.ExecuteNonQuery();
}

As usual, you can use a DbDataAdapter to delete rows from the table.

DataTable table = new DataTable();
table.Columns.Add("PartitionKey");
table.Columns.Add("RowKey");
table.Columns.Add("MyField");

DataRow row = table.NewRow();
row["PartitionKey"] = "MyPartitionKey";
row["RowKey"] = "RowKey2";
row["MyField"] = "Some Data";

table.Rows.Add(row);
table.AcceptChanges();

row.Delete();

DbProviderFactory factory = DbProviderFactories.GetFactory("Magurany.Data.TableStorageClient");

using(DbConnection connection = factory.CreateConnection())
{
	connection.ConnectionString = CloudConfigurationManager.GetSetting("StorageConnectionString");
	connection.Open();

	DbCommand deleteCommand = connection.CreateCommand("DELETE /MyTable(PartitionKey=@PartitionKey,RowKey=@RowKey)");
	deleteCommand.AddMappedParameter("PartitionKey", DbType.String, "PartitionKey");
	deleteCommand.AddMappedParameter("RowKey", DbType.String, "RowKey");

	DbDataAdapter adapter = factory.CreateDataAdapter();
	adapter.DeleteCommand = deleteCommand;
	adapter.Update(table);
}

All of these examples can be found in the src/TableStorageTest/WorkerRole.cs file of the source code.