Most Popular Posts

Jan 14, 2014

OneSql Client – World’s First SQL Server Provider for Windows Store Apps

If you work on a SQL Server client apps, you may have noticed that it’s not possible to connect directly to SQL Server from a Windows Store app. Instead, you have to develop a web service, you have to deploy it somewhere in the cloud, you (or your customers) have to pay for running it, and lastly, you have to support it. And all that is because there is no SQL Server provider for Windows Store apps.
Today, I am excited to announce OneSql Client – world’s first SQL Server provider for Windows Store apps!
Overview
OneSql Client is a Windows Runtime component that can be used from any Windows Store app regardless of its language. I wrote a demo app in JavaScript to prove that concept (see link below.)
OneSql Client is freely available as a NuGet package at http://www.nuget.org/packages/OneSql.Client/ or in a raw form from its home location http://onesql.michailov.org/.
Please be advised that this is an early alpha release and it may not behave correctly in many cases. At this point, it is only intended for experimental and learning purposes.
OneSql Client implements the [MS-TDS] protocol from scratch. Well, it doesn’t implement the whole protocol yet. The purpose of this alpha release is to prioritize the remaining work.
API
OneSql exposes API from two namespaces:
  • OneSql.Client.Sql – this namespace contains the actual SQL Server provider, SqlClient, that is ready for consumption by apps. It returns rows as JSON arrays.
  • OneSql.Client.Tds – this namespace contains low-level primitives that could be used to implement your own provider. However, if you need a feature that SqlClient doesn’t offer, I strongly recommend that you first request that feature from SqlClient before trying to implement your own provider.
Limitations
  • Supports SQL Server 2012 or higher. It might be possible to work against SQL Server 2008 and 2008 R2, but that hasn’t been tested.
  • Doesn’t support the following data types:
    • image, text, ntext – these types have been superseded by varbinary(max), varchar(max), and nvarchar(max) respectively.
    • decimal/numeric – these types are too big for JavaScript.
    • UDT – I don’t think there is a base support for these in Windows Runtime.
    • sql_variant – I’ve never needed this type. Hopefully not too many people will be crying for it.
    The above types can still be used in the storage schema or in server-side code. They just can’t be retrieved directly. You’ll have to CONVERT/CAST the respective column to a supported type.
  • Only SQL batches are supported, i.e. parameterized queries are not yet supported. They are on the plan, just not yet.
Getting Started
Download and open the SampleWindowsStoreApp project. Play with it. You’ll most certainly find bugs. Please describe your repro clearly, zip any prerequisite SQL and send it to zlatko+onesql@michailov.org.
You may also see how connections are established, how rows are being fetched, and how to move to the next result set. The documentation is very Spartan at the moment. It is on my plan to provide samples.
Known Issues
Errors encountered by OneSql get lost somewhere among the awaits and the JavaScript promises. I know it is annoying. I’m working on that too.
Support
The best place to get the latest news, updates, and references to resources is https://twitter.com/@OneSql
Articles will continue being published at http://zlatko.michailov.org/.
New releases and other executable artifacts will be published at https://onesql.michailov.org/.
For everything else, send me an email at zlatko+onesql@michailov.org.