How can I connect to a SQL Azure DB table from my Windows store app?

Question

Based on this link: http://msdn.microsoft.com/en-us/library/windowsazure/ee336243.aspx

I'm trying this code to connect to SQL Azure database and insert a row:

// The values being assigned to the StringBuilder members are set previously/not shown
    SqlConnectionStringBuilder connStringBuilder = new SqlConnectionStringBuilder();
    connStringBuilder.DataSource = dataSource;
    connStringBuilder.InitialCatalog = databaseName;
    connStringBuilder.Encrypt = true;
    connStringBuilder.TrustServerCertificate = false;
    connStringBuilder.UserID = userName;
    connStringBuilder.Password = password;

    using (SqlConnection conn = new SqlConnection(connStringBuilder.ToString()))
    {
        using (SqlCommand command = conn.CreateCommand())
        {
            conn.Open();
            command.CommandText =
                "INSERT INTO T1 (col1, col2) values (1, 'string 1'), (2, 'string 2'), (3, 'string 3')";
            int rowsAdded = command.ExecuteNonQuery();
        }
        conn.Close();
    }

Trying to, that is - SqlConnectionStringBuilder, SqlConnection, and SqlCommand are not recognized/resolvable. Do I need to install a separate ADO.NET package for this to work, or what's the deal?

UPDATE

By adding System.Data.dll to my project references (on my machine, from C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.5), I could get those classes to be recognized/resolved, but still get compile-time errors, namely:

Error 1 The base class or interface 'System.ComponentModel.Component' in assembly 'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' referenced by type 'System.Data.Common.DbConnection' could not be resolved c:\Program Files (x86)\Reference Assemblies\Microsoft\Framework.NETFramework\v4.5\System.Data.dll

and:

Error 2 The base class or interface 'System.ComponentModel.Component' in assembly 'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' referenced by type 'System.Data.Common.DbCommand' could not be resolved c:\Program Files (x86)\Reference Assemblies\Microsoft\Framework.NETFramework\v4.5\System.Data.dll

UPDATE 2

Adding SQL.Data as a reference allowed the various types to be resolved, however a different problem then prevented the app from compiling, namely:

Cannot find type System.SystemException in module mscorlib.dll

Removing SQL.Data from References eliminated that problem.

Answer 1

You'll need to use (or build) a service interface, you can't access Windows Azure SQL Database (nee SQL Azure) directly from a Windows 8 store app, and I'd contend you shouldn't even if you could. Here are two primary reasons why:

1. SQL Database supports only SQL Server authentication. That means each client device will have the keys to the kingdom in terms of the database login. If that login gets compromised, you have a significant problem on your hands.

2. Access to SQL Database is managed via a firewall on the server, and only traffic from whitelisted IP addresses is allowed to enter the server. That pretty much means you'd have to open up the firewall completely to ANY IP address 0.0.0.0 to 255.255.255.255 since you won't know that IP ranges your clients will come in on.

Check out Windows Azure Mobile Services, it provides a secure RESTful CRUD front-end to a Windows Azure SQL Database. I have a blog post that uses Mobile Services to manage a global leaderboard, which may be of help as an example.

Answer 2

System.Data is unavailable to Metro apps. If you still want to use SQL, you can use Sqlite, or make the Azure SQL DB as a data server. Otherwise you can use LocalStorage instead