The purpose of this exercise is to demonstrate just how simple it is to work with SQL Azure and Windows Azure using the graphical Visual Studio 'drag and drop' approaches.

There are certain system level features of SQL Server that tools like Visual Studio depend on for the graphical developer features, which are currently not supported in SQL Azure. What we will do instead is create our application against our local instance of SQL Server 2008. Then we will be able to simply change the connection string at release time. This is a useful pattern for developing Windows Azure applications that rely on SQL Azure.

Task 1 – Load Sample Database to your on premise SQL Database

1. Open a new instance of SQL Server Management studio, and connect to your local instance SQL Server.
2. Verify that you have the AdventureWorksLT2008 database attached.

   AdventureWorksLT2008 can be downloaded from here: http://www.codeplex.com/MSFTDBProdSamples

Task 2 – Load Sample Database to your SQL Azure HolTestDB

1. In the Object Explorer right click on HolTestDB and create a new query.
2. Paste in to the query window the SQL found in the Assets folder.

   Note:
   This script contains a cleaned up export script from the AdventureWorksLT2008 sample database. Future versions of SQL Server Management Studio will have specific support for SQL Azure avoiding the need to clean up the exported scripts.

3. Execute the query. This may take some time, as you are creating a subset of the Adventure Works database.
4. Close the query window.

Task 3 – Creating the Visual Studio Project

In this task you will create a new Visual Studio project for the Windows Azure Web Site:

1. Open Microsoft Visual Studio 2008 elevated as Administrator, from Start | All Programs | Microsoft Visual Studio 2008right-clickMicrosoft Visual Studio 2008 and choose Run as Administrator.
2. If the User Account Control dialog appears, click Continue.
3. From the File menu, choose New and then Project.
4. Create a new Web Cloud Service using the configuration settings from the screenshot below.

   

   Figure 1 Creating a new Web Cloud Service

5. Now Select the appropriate(VB or C#) ASP .NET Web Role from the list in the dialog and click the arrowto add it to your solution.

   

   Figure 2 Adding a Web Role to the Solution

6. Rename the WebRole to AdventureWorksWeb by clicking the pencil icon.
7. Click the OK button to create the solution.
8. When the project template has finished creating items you should be presented with the Default.aspx page. If not open the Default.aspx page.
9. Ensure that you are viewing the Default.aspx page in Design View by clicking the Design button
10. Drag and drop a GridView control from the Datasection of the Toolbox onto the design canvas.

    

    Figure 3 Adding a Grid View

    Note:

    Normally we would simply configure our datasource at this point using the Wizard. However, the Add Connection dialog in Visual Studio does not currently work in this ctp, so for this exercise we will need to manually create a connection string first. To simplify debugging we will start by testing against our local SQL Server 2008 instance before changing our connection string to point to SQL Azure.

11. From the SmartTagon the GridView you just created choose New data source.

    

    Figure 4 Using the Smart Tag

12. In the Wizard choose a Data Source Type of Database and leave the default ID.

    

    Figure 5 Choosing a Data Source

13. Click OK
14. In the Configure Data Source Wizard, click New Connection.

    

    Figure 6 Creating a new Connection

15. In the Add Connectiondialog configure a connection to your local copy of AdventureWorksLT

    

    Figure 7 Configuring a connection to the local server

16. Press Test Connection you should receive a dialog indicating success

    

    Figure 8 Confirmation of a successful connection

17. Click OK to close the Add Connectiondialog box.
18. Click Next to continue through the Wizard
19. Ensure that the Yes, save this connection as is checked and use the nameAdventureWorksLTConnectionString

    

    Figure 9 Saving the connection string

    Note:
    By saving the connection string to the application configuration file we can easily return later and use the SQL Azure connection string instead.

20. Click Next
21. Choose the table named Customer and check the FirstName, LastName, CompanyName aEmailAddress.

    

    Figure 10 Choosing query columns

22. Select the SELECT statement at the bottom of the dialog and press Ctrl-C to copy to the clipboard.
23. Click the radio button to Specify a custom SQL statement of stored procedureWe need to do this because AdventureWorks uses a named Schema (SalesLT) that we need to explicitly reference.
24. Click Next button
25. Paste the statement you copied into the SQL Statement box.

    

    Figure 11 Pasting the SQL string

26. Change the statement to read

    SELECT [FirstName], [LastName], [CompanyName], [EmailAddress] FROM [SalesLT].[Customer]
    

27. Press Next
28. Press Test Query and you should see results returned.

    

    Figure 12 Testing the query

29. Click Finish
30. Press F5 to run the application in the Azure Development Fabric
31. The application will execute and you will see the list of all customers in the browser

    

    Figure 13 Running against the local SQL Server instance

32. Close the browser and return to Visual Studio
33. Open the web.config file
34. Find the connectionStringssection and modify the connectionString to the following.

    "AdventureWorksLTConnectionString" connectionString="Initial Catalog=HoLTestDB;Data Source=REPLACE_SERVER_NAME.database.windows.net;encrypt=true;User ID=HoLTestUser;Password=REPLACE_HoLTestUser_PASSWORD;TrustServerCertificate=true;" providerName="System.Data.SqlClient" />
    

    Note:
    You will need to update this string with your SQL Azure server name, and the credentials of the HolTestUser you created in previous exercises.

35. Press F5 to run the application once again. This time the list of Customers is being retrieved from SQL Azure