In my latest post I described my first steps to update my LinqPad Cosmos Db driver

The whole idea was to show how the "backend" of my new and updated driver can be used as a standalone component. This makes updating and maintaining the component much better and also the whole thing is much more reusable.

And with that, it dawned on me: I have another crucial library that powers the driver: the ADO.NET Cosmos Db provider. I created the provider specifically for the driver, so currently I only implemented only as much as it is required for the driver. Hopefully with me open-sourcing the component the development of the provider can go on its separate course and progress a little faster.

How to use the provider?

As with every ADO.NET provider, first, you have to create and open a connection:

using (var connection = new CosmosDbSqlConnection("https://localhost:8081", "C2y6yDjf5/R+ob0N8A7Cgv30VRDJIWEHLM+4QDU5DE2nQ9nDuVTqobD4b8mGGyPMbIZnqyMsEcaGQy67XIw/Jw==", "TestDb"))
{
  connection.Open();
}

Then, you have to create a command:

using (var connection = new CosmosDbSqlConnection("https://localhost:8081", "C2y6yDjf5/R+ob0N8A7Cgv30VRDJIWEHLM+4QDU5DE2nQ9nDuVTqobD4b8mGGyPMbIZnqyMsEcaGQy67XIw/Jw==", "TestDb"))
{
  connection.Open();
  using (var command = new CosmosDbSqlCommand(connection, "SELECT * FROM root") { Collection = "TestCollection" })             
}

And finally execute the command and process the results with a data reader:

using (var connection = new CosmosDbSqlConnection("https://localhost:8081", "C2y6yDjf5/R+ob0N8A7Cgv30VRDJIWEHLM+4QDU5DE2nQ9nDuVTqobD4b8mGGyPMbIZnqyMsEcaGQy67XIw/Jw==", "TestDb"))
{
  connection.Open();
  using (var command = new CosmosDbSqlCommand(connection, "SELECT * FROM root") { Collection = "TestCollection" })
  using (var reader = command.ExecuteReader())
  {
    while (reader.Read())
    {
      Console.WriteLine(reader[0]);
    }
    Console.ReadLine();
  }
}

You can also use the ExecuteScalar method of the command (but since CosmosDb queries are strictly read-only, there is no ExecuteNonQuery) and if you want, you can access the fields of the current document by name (but don't forget that this is still a NoSQL store, so don't expect that all the documents have the same keys in them). If you need to add parameters to your query, you can create a CosmosDbSqlParameter and add it to the paramters collection of the command for parameterized queries.

Go and check it out on Github

Not supported features

I'm not going to lie, there are a lot of features that are not supported. Either because I haven't had the time or because I don't really know how to do it properly — after all, ADO.NET providers are for relational data and not NoSQL solutions.

Transactions are not supported, the whole DBType-thing is not supported, the parameters are now identified by name, which means that you cannot change them once it's set. Probably the most important issue to add would be async-support, but since the provider main goal currently is to support the raw SQL feature of LinqPad and LinqPad does not issue queries using the async API, this is not a priority at the moment. If you are missing something, feel free to create an issue on Github or create a PR. Also, don't expect the API to be user-friendly with all kinds of meaningful errors and explicit type-checks :) But again, if you want to make the code-base better by adding some of these, PRs are welcome. I know it is hard without unit tests, but hey — PRs are welcome :)

The next time I'll post about having update the LinqPad driver, I promise ;)