How can I connect to an Azure DB SQL table from a Windows Store app?

Question

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

I am trying this code to connect to an Azure SQL database and insert the line:

// 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(); } 

An attempt, that is, SqlConnectionStringBuilder, SqlConnection , and SqlCommand not recognized / not allowed. Do I need to install a separate ADO.NET package for this, or what is the deal?

UPDATE

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

Error 1 The base class or interface "System.ComponentModel.Component" in the assembly "System, Version = 4.0.0.0, Culture = neutral, PublicKeyToken = b77a5c561934e089, which is referenced by the type" System.Data.Common.DbConnection ", cannot 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 the assembly "System, Version = 4.0.0.0, Culture = neutral, PublicKeyToken = b77a5c561934e089, which is referenced by the type" System.Data.Common.DbCommand "cannot 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 to resolve various types, however, another problem did not allow compilation of the application, namely:

Cannot find type System.SystemException in module mscorlib.dll

Removing SQL.Data from the links fixed this problem.

Answer 1

You will need to use (or create) a service interface, you cannot access the Windows Azure SQL Azure database directly from the Windows 8 storage application, and I would argue that you shouldn't even. Here are two main reasons:

• The SQL database only supports SQL Server authentication. This means that each client device will have keys to the kingdom in terms of entering the database. If this login is compromised, you have a significant problem at your fingertips.

• Access to the SQL database is controlled through the firewall on the server, and access to the server is allowed only for traffic from white IP addresses. This pretty much means that you will need to fully open the firewall to ANY IP address from 0.0.0 to 255.255.255.255, since you will not know that your clients will include IP ranges.

Check out Windows Azure Mobile Services, provides a secure RESTful CRUD interface for a Windows Azure SQL database. I have a blog post that uses Mobile Services to manage a global leaderboard, which may be useful as an example.

Answer 2

System.Data is not available for Metro applications. If you still want to use SQL, you can use Sqlite or make Azure SQL DB as a data server. Otherwise, you can use LocalStorage instead