SQL Azure Support in Visual Studio 2010
Visual Studio 2010 supports data connections to SQL Azure, query designing, data manipulation, and data binding operations in your C#/VB applications.
Connecting to a SQL Azure Database
SQL Azure can be viewed as a subset of SQL Server 2008. Therefore, when you add a SQL Azure data connection inside Visual Studio (using .NET Framework Data Provider for SQL Server, i.e. SqlClient) , the user experience is basically the same as connecting to an on-premise SQL Server 2008 database. Here’s a screenshot showing the data connection to a SQL Azure database “Northwind”:
For more information on how to get a SQL Azure database, refer to the Getting Started Guide for SQL Azure.
When you expand the SQL Azure data connection node inside Server Explorer, you may notice that some nodes may not be available compared with a normal SQL Server 2008 connection. This is because these features are not supported by SQL Azure. The Visual Studio Properties Window can show properties for SQL Azure data connection properties as well as properties for SQL Azure database objects. However, SQL Server database or database object properties not supported in SQL Azure are either hidden or disabled (grayed out).
The following is part of the SQL Azure data connection properties. Note the data connection Type and Version:
Manipulating Data
After you have made a SQL Azure database connection, you can view and edit the data of a table or view. To manipulate data for a table from Server Explorer, right click the table, and choose “Show Table Data”. For views, the context menu is “Show Results”. In the following screenshot, the data of table Categories are shown in the data grid, allowing for inserts, updates, or deletes:
Designing Queries
The Query Designer of Visual Studio 2010 fully supports SQL Azure databases. To create a new query from Server Explorer, right click the data connection node, and choose “New Query”. The Query Designer will open and you have the same experience as working with an on-premise SQL Server database.
The following is a screenshot of Query Designer in action:
Data Definition
Although Table Designer, Database Diagrams and Script Editors are currently not supported in Visual Studio, you can use Query Designer to execute some basic SQL statements. Here are the steps to create a table in your SQL Azure database:
1. From Server Explorer, right click SQL Azure data connection node, and choose “New Query”.
2. Close the Add Table dialog.
3. In the SQL Pane, type desired SQL statements ( using semicolon to separate multiple statements). For example:
CREATE TABLE TestTable01 (id int PRIMARY KEY, name VARCHAR(30))
4. Execute SQL (from context menu, menu bar, Data top menu, or Ctrl-R)
5. There will be a dialog warning that CREATE TABLE statement is not supported by Query Designer. Click Continue button, as Query Designer will then pass the raw SQL statement to the database engine.
6. You should receive a message stating “Query execution succeeded.” Refresh the Tables node in Server Explorer and the table “TestTable01” should appear.
Here’s the screenshot of the above actions:
Alternatively, you can use SQLCmd to connect to your SQL Azure database and execute queries. For example:
sqlcmd -S MyAzureServer.database.windows.net -d Northwind -U MyAdmin@ MyAzureServer -P MyPassword –i INPUT_SQL_FILE
When you write your SQL scripts, note that some T-SQL statements are not supported by SQL Azure. In addition, if a feature is deprecated in SQL Server 2008, it becomes unsupported in SQL Azure. To find out what SQL statements are supported by SQL Azure, refer to the following links in MSDN:
· Supported Transact-SQL Statements (SQL Azure Database)
· Partially Supported Transact-SQL Statements (SQL Azure Database)
· Unsupported Transact-SQL Statements (SQL Azure Database)
Data Binding
Once you are able to connect to your SQL Azure database within Visual Studio, you can treat the data connection as a normal on-premise SQL Server database connection, and bind data in your applications using DataSet, Entity Framework or other data access technologies.
Suppose you already have your SQL Azure data connection inside Server Explorer, when you add a new data source in your C#/VB project, the connection will automatically show up in Data Source Configuration Wizard. For example, the following is a screenshot of the Entity Data Model Wizard (which can be invoked through the Data Source Configuration Wizard) when there is already a SQL Azure data connection inside Server Explorer:
For more information on how to use Data Sources in your applications, check the following blog posts:
· WPF Data Binding: Creating a Master-Details form in Visual Studio 2010
· WPF Data Binding: How to Bind DataSet to WPF Designer
Enjoy your data in the cloud!