Introduction to SQL Azure - Johns Hopkins University



Hands-On LabSQL Azure: Introduction to SQL AzureLab version:1.0.3Last updated: DATE \@ "M/d/yyyy" 12/15/2009Contents TOC \h \z \t "Heading 3,2,pp Topic,1,PP Procedure start,3" Overview PAGEREF _Toc248656296 \h 3Exercise 1: Preparing Your SQL Azure Account PAGEREF _Toc248656297 \h 7Task 1 – Provisioning a SQL Azure Account PAGEREF _Toc248656298 \h 7Task 2 – Connecting to SQL Azure and Creating a Database PAGEREF _Toc248656299 \h 11Task 3 – Creating Logins and Database Users PAGEREF _Toc248656300 \h 17Exercise 2: Working with Data Basic DDL and DML PAGEREF _Toc248656301 \h 20Task 1 – Creating Tables and Indexes PAGEREF _Toc248656302 \h 20Exercise 3: Build a Windows Azure Application that Accesses SQL Azure PAGEREF _Toc248656303 \h 24Task 1 – Loading Sample Database to your on premise SQL Database PAGEREF _Toc248656304 \h 25Task 2 – Loading Sample Database to your SQL Azure HolTestDB PAGEREF _Toc248656305 \h 25Task 3 – Creating the Visual Studio Project PAGEREF _Toc248656306 \h 25Exercise 4: Connecting via Client Libraries PAGEREF _Toc248656307 \h 36Task 1 – Opening the Begin Solution and Exploring the Common Functionalities PAGEREF _Toc248656308 \h 36Task 2 – Connecting to SQL Azure Using PAGEREF _Toc248656309 \h 38Task 3 – Connecting to SQL Azure Using ODBC PAGEREF _Toc248656310 \h 43Task 4 – Connecting to SQL Azure Using OLEDB PAGEREF _Toc248656311 \h 48Task 5 – Connecting to SQL Azure Using Linq to SQL PAGEREF _Toc248656312 \h 53Task 6 – Connecting to SQL Azure via Non-Microsoft Technologies PAGEREF _Toc248656313 \h 56// Build connection string PAGEREF _Toc248656314 \h 56Summary PAGEREF _Toc248656315 \h 57OverviewSQL Azure makes the power of Microsoft SQL Server available in a Cloud Hosted offering. Working with SQL Azure should be a familiar experience for most developers because, for the most part, it supports the same tooling and development practices currently used for on premise SQL Server applications.However, there are some small differences between working with SQL Azure and working with on-premise SQL Server. Some of these differences are inherent in the way that SQL Azure has been architected and some will only apply during the Community Technical Preview phase.This hands on lab will walk through a series of simple use cases for SQL Azure such as provisioning your account, creating, and using a database. We will create a simple Windows Azure application to allow us to manipulate the data in the Customer table of a database running in SQL Azure.ObjectivesIn this Hands-On Lab, you will learn how to:Use SQL Azure as a cloud hosted database platform for your applications.You will learn how toProvision a new account in SQL AzureCreate new databases within the context of your accountCreate new users and grant them appropriate permissionsYou will work with SQL Azure toExecute Data Definition Language statements to create tables and indexesExecute Data Manipulation Language to insert and query rowsBuild a simple data driven Page using the graphical controls in Visual Studio 2008Develop against a local SQL Server/SQL Express database before connecting to SQL AzureConnect to SQL Azure Database via Client Libraries. PrerequisitesThe following is required to complete this hands-on lab:Microsoft .NET Framework 3.5 SP1Microsoft Visual Studio 2008 SP1 (or above)SQL Server 2008 R2 Management StudioWindows Azure Tools for Microsoft Visual Studio (November 2009)SetupFor convenience, much of the code used in this hands-on lab is available as Visual Studio code snippets. To check the prerequisites of the lab and install the code snippets:Run the SetupLab.cmd script located in the lab's Source\Setup folder to check dependencies and install any missing prerequisites.Once you have verified every prerequisite, follow the instructions to install the code snippets. Using the Code SnippetsWith code snippets, you have all the code you need at your fingertips. The lab document will tell you exactly when you can use them. For example, To add this code snippet in Visual Studio, you simply place the cursor where you would like the code to be inserted, start typing the snippet name (without spaces or hyphens), in this case LabNameEx01RunmethodCS, watch as Intellisense picks up the snippet name, and then hit the TAB key twice once the snippet you want is selected. The code will be inserted at the cursor location. Figure 1Hit TAB to select the highlighted snippet.Figure 2Hit TAB again and the snippet will expandTo insert a code snippet using the mouse rather than the keyboard, right-click where you want the code snippet to be inserted, select Insert Snippet followed by My Code Snippets and then pick the relevant snippet from the list.To learn more about Visual Studio IntelliSense Code Snippets, including how to create your own, please see Hands-On Lab comprises the following exercises:Preparing Your SQL Azure AccountBasic DDL and DML - Creating Tables and IndexesBuild a Windows Azure Application that Accesses SQL Azure Connecting via Client Libraires Estimated time to complete this lab: 60 minutes.Exercise 1: Preparing Your SQL Azure AccountIn this exercise, you will step through provisioning your new SQL Azure account. You will then connect to that account and create a database, add a new user and then reconnect to SQL Azure so that you can begin working with our database. It is assumed that you already have already obtained a SQL Azure Token, if you have not you can request one from Task 1 – Provisioning a SQL Azure AccountGo to the website Login to your Windows Live account. Figure 1Logging into the Azure Services PortalType in your invitation code and then click Submit.Figure 2Entering a SQL Azure TokenRead the terms of use then click then Accept button.Figure 3Accepting the Terms of UseFill in an admin account name, password and region, and then click Create Server. The Location determines which datacenter the database will reside in.Figure 4Create a server and set administrator passwords.Note: An admin account is a master account used for administering the new server. This should not be used in connection strings where the username and password may be exposed.The password policy requires that this password contain at least one number, one character and one letter and one symbol. In addition, the password cannot be less than six characters nor contain three consecutive characters from the username.The virtual server should now be created and a list showing your projects should be displayed. To view the databases on the virtual server click the project. Figure 5SQL Azure Projects ListThe Server Administration screen allows basic administration of the database server and allows a quick way of viewing the available connection strings.Figure 6SQL Azure Server Management .Your server fully qualified domain name will take the following format: <ServerName>.database..The new firewall features allows a customer to specify an allow list of IP address that can access their SQL Azure Server. Your firewall will deny all connections by default, so be sure to configure your allow list so that existing clients can continue to connect. Figure 7SQL Azure Server Management – Firewall SettingsNote: The application of your firewall settings can take a few momentsYou now have a database server created and ready for the next steps in this lab. This database can be connected to from anywhere in the world.Task 2 – Connecting to SQL Azure and Creating a DatabaseOpen SQL Server Management Studio. Start > All Programs > SQL Server 2008 R2 November CTP > SQL Server Management Studio. You will be presented with a logon dialog.Figure 8SQL Server Management Studio Default Connection DialogEnter your login information that you created in Task 1 ensuring that you selected SQL Server Authentication. SQL Azure currently only supports SQL Server Authentication. Click on the Options tab, and specify the database to connect to as master. Click Connect.Note: Please replace server name with your assigned server, i.e. REPLACE_SERVER_NAME.database. Figure 9Connecting to SQL AzureFigure 10Connect to master databaseYou should now see in your Object Explorer the structure of your database. Notice that your SQL Azure database is no different to an on premise relational database.Figure 11The Object ExplorerClick the New Query button. This will open a similar dialog requesting logon information.Figure 12Creating a New Query WindowYou now have a query window with an active connection to our account. You can test our connection by executing the @@version procedure. Type SELECT @@version into the query window and press the Execute button. You will get a scalar result back, which indicates the edition as Microsoft SQL Azure.Figure 13Retrieving the SQL Azure versionReplace the previous query with a new query SELECT * FROM sys.databases and click ExecuteFigure 14As you can see, we just have our Master database now.Next, you will create a new Database. One of the good things about SQL Azure is that it takes care of much of the management of our database for us including how to manage the underlying data files. This means that our Create Database statement can be very simple. Type Create Database HoLTestDB and click ExecuteFigure 15Creating a New DatabaseNote: You can select which SQL Azure Database edition (Web or Business) is created during the database provisioning process. This is surfaced both in the SQL Azure Portal and in the T-SQL Create Database statement. For example, to create a Business Edition database the T-SQL command would be as follows: CREATE DATABASE HolTestDB (MAXSIZE = 10GB). Once a database has been created, the database size will not be able to be changed.Remembering from above that there is a system view called sys.databases, Execute the query SELECT * FROM sys.databases Figure 16Querying the sys.databases viewNote: The USE <database_name> command does not work with SQL Azure. Therefore, you need to disconnect and reconnect in order to change from the Master database to the new user database you just created.Close the existing Query tab and disconnect from the master database by right clicking the server in the object explorer and selecting Disconnect.Figure 17Disconnect from the master databaseTo start up a new connections click the New Query button. The Connect to Server dialog will open. If necessary, retype your credentials. Click the Options button to expand the options panel and set Connect to Database to HoLTestDB. You will need to type this rather than using the dropdown box. Figure 18Connecting to a specific named databaseYou can check that we are now in the context of our User database executing the query select db_name()Figure 19Querying the database currently in use.Task 3 – Creating Logins and Database UsersMuch like SQL Server, SQL Azure allows us to create additional logins and then assign those logins as users with permissions on a database. In this task, you will create a new login and then create a user that uses the new login in our HoLTestDB database.Connect to the master database again. You may need to clear the Connect to Database combo box from the Options tab.Create a new Login executing the following:T-SQLCREATE LOGIN HoLUser WITH password='Password1'GONote: You should choose your own password for this login account and use it where appropriate throughout the lab. If you do not choose a unique password you should ensure that you DROP Login HoLUser when you have finished the labDisconnect the HoLTestDB database by right clicking your server in the object explorer and selecting Disconnect.Reconnect to the HoLTestDB by clicking Connect->Database Engine in the object explorer. Enter your admin credentials and specify the database name in the connection properties tab.Figure 20Connecting to HoLTestDBIn a New Query window execute the following to create a user from the login HoLUserT-SQL-- Create a new user from the login and executeCREATE USER HoLTestUser FROM LOGIN HoLUserGOAdd the user to the db_owner role of your HoLTestDB executing the following:T-SQL-- Add the new user to the db_owner role and executeEXEC sp_addrolemember 'db_owner', 'HoLTestUser'GONote: By making, our user a member of the db_owner role we have granted a very extensive permission set to the user. In a real world application you should be careful to ensure that you grant users the smallest privilege set possible.Close the Query tab.Reconnect to the database you created (HoLTestDB) but this time using your new HoLUser login. We will be using this login for the rest of the lab.Execute the following query:T-SQLSELECT @@versionFigure 21Querying the SQL Server versionYou now have a database created that we can login into it with our user. In the following exercises, you will start creating some database objects such as tables in this database.Exercise 2: Working with Data Basic DDL and DMLIn this exercise, you will start working with data in our database. This means you will be creating some tables, indexing those tables appropriately, then inserting and querying data.Note: This exercise makes use of the HoLTestDB database that was created in Exercise 1. If you have not yet created this database, please complete Exercise 1.Task 1 – Creating Tables and IndexesIf you closed SQL Server Management Studio, open it again from Start > All Programs > SQL Server 2008 R2 November CTP > SQL Server Management Studio.Connect to the HoLTestDB database using the HoLUser login that you created in the previous exercise.Now you will add a simple table to our database. Execute the following query in a New Query window:T-SQLCREATE TABLE HoLTestTable(MyRowID int PRIMARY KEY CLUSTERED)Note: SQL Azure requires that every table have a clustered index. If you create a table without a clustered index, you will not be able to insert rows into the table until you have created one.Because the clustered index determines the order of rows on disk, and thus affects certain queries, you may choose to place the clustered index on a column other than the primary key column.You will test our table by inserting some rows. Execute the following query:T-SQLINSERT INTO HoLTestTable VALUES (1)GOINSERT INTO HoLTestTable VALUES (2)GOINSERT INTO HoLTestTable VALUES (3)GONow query the rows back out of the database. Execute following the query:T-SQLSELECT * FROM HoLTestTableFigure 22Querying simple data from SQL AzureLet’s drop that table and create something more sophisticated. Execute the following query:T-SQLDROP TABLE HoLTestTableCreate a Customer table by Executing the following SQL Query:T-SQLCREATE TABLE [Customer]([CustomerID] [int] IDENTITY(1,1)NOT NULL PRIMARY KEY CLUSTERED,[Title] [nvarchar](8)NULL,[FirstName] [nvarchar](50)NOT NULL,[LastName] [nvarchar](50)NOT NULL,[EmailAddress] [nvarchar](50)NULL,[Phone] [nvarchar](30)NULL,[Timestamp] [timestamp] NOT NULL)You will add an index on the EmailAddress field. Execute the following query:T-SQLCREATE INDEX IX_Customer_EmailAddressON Customer(EmailAddress)Execute the following query to add a row to the new Customer table:T-SQLINSERT INTO [Customer]([Title],[FirstName],[LastName],[EmailAddress],[Phone]) VALUES('Mr','David','Alexander','davida@','555-1234-5555')Now let’s query the data back out, but, let’s start by enabling the SHOWPLAN_ALL option to show the execution plan. To do that, execute the following query:T-SQLSET SHOWPLAN_ALL ONGOSELECT * FROM Customer WHERE EmailAddress ='davida@'GOSET SHOWPLAN_ALL OFFFigure 23Showing the Query PlanAdd a whole bunch more rows to the database and then look at the query plan again. To do this, Execute the following query to add a stored procedure named AddData. This stored procedure will loop by adding 1 to the counter each time through and adding a new record with the adding an email address of [Counter]davida@:T-SQLCREATE PROCEDURE AddData@NumRows intASDECLARE @counter intSELECT @counter = 1WHILE (@counter < @NumRows)BEGIN INSERT INTO [Customer] ([Title],[FirstName],[LastName],[EmailAddress],[Phone]) VALUES ('Mr','David','Alexander',CAST(@counter as nvarchar)+'davida@','555-1234-5555') SELECT @counter = @counter + 1 ENDNow add 10,000 rows into the database by running the newly created Stored Procedure. Each row will have a unique email address. Execute the following query:T-SQLEXEC AddData 10000Note: It may take some time to generate the 10,000 rows.Execute the following query againT-SQLSET SHOWPLAN_ALL ONGOSELECT * FROM Customer WHERE EmailAddress ='davida@'GOSET SHOWPLAN_ALL OFFFigure 24Showing the Query PlanNote: Notice that the second time around the query optimizer is likely to use the Index that we defined: This is the Index Seek line in the query plan.For the most part, you can use any tool that we might previously have used with SQL Server on premise. For an example of this, look at the query plan graphically. In SQL Server Management Studio press Ctrl-L to display the Estimated Execution Plan.Figure 25Showing the Query PlanExercise 3: Build a Windows Azure Application that Accesses SQL AzureIn this exercise, you will create a simple Windows Azure application to allow us to manipulate the data in the Customer table of the AdventureWorksLT2008 database.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 – Loading Sample Database to your on premise SQL DatabaseOpen a new instance of SQL Server Management studio from Start > All Programs > SQL Server 2008 R2 November CTP > SQL Server Management Studio, and connect to your local instance SQL Server (probably it is .\SQLEXPRESS).Verify that you have the AdventureWorksLT2008 database attached.AdventureWorksLT2008 can be downloaded from here: 2 – Loading Sample Database to your SQL Azure HolTestDBDisconnect from your local instance SQL Server.Connect to the HoLTestDB database on your SQL Azure Server using the HoLUser login that you created in the previous exercises.Go to File | Open | File menu and navigate to the Assets folder inside the Source folder of this Lab. Select the AdventureWorks2008LT_Azure.sql file and click Open.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.Execute the query. This may take some time, as you are creating a subset of the Adventure Works database.Close the query window.Task 3 – Creating the Visual Studio ProjectIn this task you will create a new Visual Studio project for the Windows Azure Web Site: Open Microsoft Visual Studio 2008 elevated as Administrator, from Start | All Programs | Microsoft Visual Studio 2008 right-click Microsoft Visual Studio 2008 and choose Run as Administrator.From the File menu, choose New and then Project. In the New Project dialog, expand the language of your preference (Visual C# or Visual Basic) in the project types list and select Cloud Service.In the Templates list, select Windows Azure Cloud Service. Enter “AdventureWorks” for the project name and the solution name. Ensure Create directory for solution is checked. Click OK to create the project.Figure 26Creating a new Web Cloud ServiceIn the New Cloud Service Project dialog, inside the Roles panel, expand the tab for the language of your choice (Visual C# or Visual Basic), select Web Role from the list of available roles and click the arrow (>) to add an instance of this role to the solution. Before closing the dialog, select the new role in the right panel, click the pencil icon and rename the role as AdventureWorksWeb. Click OK to create the cloud service solution. Figure 27Adding a Web Role to the SolutionWhen the project template has finished creating items, you should be presented with the Default.aspx page. If not open the Default.aspx page.Ensure that you are viewing the Default.aspx page in Design View by clicking the Design button.Drag and drop a GridView control from the Data section of the Toolbox onto the design canvas.Figure 28Adding a Grid ViewNote: 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.From the SmartTag on the GridView you just created choose New data source option on the Choose Data Source combo box.Figure 29Using the Smart TagIn the Wizard choose a Data Source Type of Database and leave the default ID. Click OK.Figure 30Choosing a Data SourceIn the Configure Data Source Wizard, click New Connection.Figure 31Creating a new ConnectionIn the Choose data source dialog, select Microsoft SQL Server and click Continue.In the Add Connection dialog, configure a connection to your local copy of AdventureWorksLT2008 database.Figure 32Configuring a connection to the local serverPress Test Connection you should receive a dialog indicating success. Click OK.Figure 33Confirmation of a successful connectionClick OK to close the Add Connection dialog box.Click Next to continue through the Wizard.Ensure that the Yes, save this connection as is checked and use the name AdventureWorksLTConnectionStringFigure 34Saving the connection stringNote: By saving the connection string to the application configuration file we can easily return later and use the SQL Azure connection string instead. Click Next.Click the Specify a custom SQL statement of stored procedure radio button and then click Next. Note: We cannot use the Specify colums from a table or view option because AdventureWorks uses a named Schema (SalesLT) that we need to explicitly reference.Paste the following statement into the SQL Statement box:T-SQLSELECT [FirstName], [LastName], [CompanyName], [EmailAddress] FROM [SalesLT].[Customer]Figure 35Pasting the SQL stringPress Next.Press Test Query and you should see results returned.Figure 36Testing the queryClick Finish.Press F5 to run the application in the Development Fabric.The application will execute and you will see the list of all customers in the browser:Figure 37Running against the local SQL Server instanceClose the browser and return to Microsoft Visual Studio.Open the web.config file.Find the connectionStrings section and modify the AdventureWorksLTConnectionString connectionString to the following replacing the placeholders for the SQL Azure Server Name (REPLACE_SERVER_NAME) and HoLTestUser password (REPLACE_HoLTestUser_PASSWORD).XML<add name="AdventureWorksLTConnectionString" connectionString="Initial Catalog=HoLTestDB;Data Source=REPLACE_SERVER_NAME.database.;encrypt=true;User ID=HoLUser;Password=REPLACE_HoLUser_PASSWORD;TrustServerCertificate=true;" providerName="System.Data.SqlClient" />Press F5 to run the application once again. This time the list of Customers is being retrieved from SQL Azure.Exercise 4: Connecting via Client LibrariesIn this exercise, you will learn how to use , ODBC, OleDB and Linq to SQL technologies to connect to your SQL Azure database and perform some simple T-SQL operations. In addition, you will see how to connect to the database from other technologies like Java and PHP.Using Microsoft Technologies, you will see that the way in which you interact with your SQL Azure database from your applications is the same as a traditional SQL database. The main differences between the technologies lie in the type of connection and the connection strings used to connect to SQL Azure. After the connection is established, you can then use the appropriate inheritor of the ‘DbCommand’ to issue your commands to SQL Azure.Task 1 – Opening the Begin Solution and Exploring the Common FunctionalitiesYou will test the different Microsoft technologies connecting to SQL Azure and performing some tasks against a new table. To avoid spending time implementing logic that creates, inserts, queries and deletes a table, this exercise provides a begin solution with that common functionalities. This allows you to focus on learning how you can connect to SQL Azure and what are the differences between the proposed technologies. In this task, you will open the ConnectDemoApp solution and explore the SQLAzureConnectionDemo class that you will inherit from for each of the different implementations per technology.Open Microsoft Visual Studio 2008 from Start | All Programs | Microsoft Visual Studio 2008 | Microsoft Visual Studio 2008.Open the begin solution provided for this exercise. To do this, from the File menu, choose Open | Project/Solution. In the Open Project dialog, navigate to the Ex04-ConnectingViaClientLibraries\begin folder inside the Source folder of this Lab. Select the folder of the language of your preference (C# or VB), and open the solution ConnectDemoApp.sln inside the ConnectDemoApp folder. A solution with the following structure should open.Figure 38Connect Demo App solution’s structure (C#)Figure 38Connect Demo App solution’s structure (VB)As mentioned before, you will create a class per technology inheriting from the SQLAzureConnectionDemo abstract class. This has common functionalities to perform basic operations against SQL Azure using the provider that you implement in the derived class. Let’s explain each of the methods of this class to understand how it works and which methods you have to implement in the derived classes: MethodTypeDescriptionConstructorSets the connection property based on the result of the CreateConnection abstract method that will be implemented on the derived class.CreateConnectionAbstractA derived class will be implementing this method in order to create the connection according to the underliying technology.CreateCommandAbstractA derived class will be implementing this method to create a command according to the underlying technology.GetServerNameReturns the server name from the data source. It is a common task required to create the connection to the database.ConnectToSQLAzureDemoExecutes the demo flow against the SQL Azure Database. It gets a command from the derived class using the CreateCommand method and then executes the Execute* methods to create, fill, query and delete a demo table.ExecuteCreateDemoTableStatementExecutes a create table statement to create the “DemoTable” table.ExecuteInsertTestDataStatementExecutes an insert statement against the “DemoTable” table.ExecuteReadInsertedTestDataExecutes a select statement trying to retrieve the data inserted by the previous method and calls the ReadData method to show it in the Console.ReadDataReads the data retrieved from the table and displays it in the Console.ExecuteDropDemoTableExecutes a delete statement removing the “DemoTable” table from the SQL Azure database.Notice that you will only have to override the CreateConnection and CreateCommand methods on the implementation of each of the technologies to successfully create a connection to SQL Azure.Task 2 – Connecting to SQL Azure Using In this task, you will create a class that inherits from the SQLAzureConnectionDemo class and implements the methods to connect to SQL Azure using .Add a new class to the ConnectDemoApp project called AdoConnectionDemo. To do this, right-click the ConnectDemoApp project in the Solution Explorer and select Add | Class. In the Add New Item dialog, make sure that the Class template is selected and set the name to AdoConnectionDemo.cs or AdoConnectionDemo.vb based on the language of your preference.Make sure that you have the following namespace directives at the top of the file:C#using System.mon;using System.Data.SqlClient;Visual BasicImports System.monImports System.Data.SqlClientUpdate the class definition to make it public and to inherit from SQLAzureConnectionDemo. The final implementation should look like the following: C#public class AdoConnectionDemo : SQLAzureConnectionDemo{}Visual BasicPublic Class AdoConnectionDemo Inherits SQLAzureConnectionDemoEnd ClassImplement the class constructor to get the connection information and pass it as parameters to the base class constructor:(Code Snippet – Intro to SQL Azure - Ex04 ADO constructor – C#)C#public AdoConnectionDemo(string userName, string password, string dataSource, string databaseName) : base(userName, password, dataSource, databaseName){}(Code Snippet – Intro to SQL Azure - Ex04 ADO constructor – VB)Visual BasicPublic Sub New(ByVal userName As String, ByVal password As String, ByVal dataSource As String, ByVal databaseName As String) MyBase.New(userName, password, dataSource, databaseName)End SubThe SQLAzureConnectionDemo class delegates the connection construction to the derived class. Override the CreateConnection method to create a SqlConnection in your AdoConnectionDemo class:(Code Snippet – Intro to SQL Azure - Ex04 ADO CreateConnection – C#)C#protected override DbConnection CreateConnection(string userName, string password, string dataSource, string databaseName){ return new SqlConnection(CreateAdoConnectionString(userName, password, dataSource, databaseName));}(Code Snippet – Intro to SQL Azure - Ex04 ADO CreateConnection – VB)Visual BasicProtected Overrides Function CreateConnection(ByVal userName As String, ByVal password As String, ByVal dataSource As String, ByVal databaseName As String) As DbConnection Return New SqlConnection(CreateAdoConnectionString(userName, password, dataSource, databaseName))End FunctionImplement the CreateAdoConnectionString method used by the previous method. This method is responsible for building up the connection string for the Connection, which takes advantage of the SqlConnectionStringBuilder class in the underlying implementation. (Code Snippet – Intro to SQL Azure - Ex04 ADO CreateAdoConnectionString method – C#)C#private string CreateAdoConnectionString(string userName, string password, string dataSource, string databaseName){ // create a new instance of the SQLConnectionStringBuilder SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder { DataSource = dataSource, InitialCatalog = databaseName, Encrypt = true, TrustServerCertificate = false, UserID = userName, Password = password, }; return connectionStringBuilder.ToString();}(Code Snippet – Intro to SQL Azure - Ex04 ADO CreateAdoConnectionString method – VB)Visual BasicPrivate Function CreateAdoConnectionString(ByVal userName As String, ByVal password As String, ByVal dataSource As String, ByVal databaseName As String) As String ' create a new instance of the SQLConnectionStringBuilder Dim connectionStringBuilder As SqlConnectionStringBuilder = New SqlConnectionStringBuilder With {.DataSource = dataSource, .InitialCatalog = databaseName, .Encrypt = True, .TrustServerCertificate = False, .UserID = userName, .Password = password} Return connectionStringBuilder.ToString()End FunctionOverride the CreateCommand method to create an command. Remember that this abstract method is called in the parent class to get the connection and execute the different SQL statement samples.(Code Snippet – Intro to SQL Azure - Ex04 ADO CreateCommand method – C#)C#protected override DbCommand CreateCommand(DbConnection connection){ return new SqlCommand() { Connection = connection as SqlConnection };}(Code Snippet – Intro to SQL Azure - Ex04 ADO CreateCommand method – VB)Visual BasicProtected Overrides Function CreateCommand(ByVal connection As DbConnection) As DbCommand Return New SqlCommand() With {.Connection = TryCast(connection, SqlConnection)}End FunctionThat is all the code required to use an connection. Now you will include some code on the Program.cs or Module1.vb file (based on your language) to test the connection and see how the different operations work.Open the Program.cs (for Visual C# projects) or Module1.vb (for Visual Basic projects) file double-clicking it in the Solution Explorer inside the ConnectDemoApp project.Implement the logic to create an instance of the AdoConnectionDemo class and execute the Demo against SQL Azure.(Code Snippet – Intro to SQL Azure - Ex04 ADO demo implementation – C#)C#static void Main(string[] args){ //Invoke the connection demo Console.WriteLine("Starting the Connection Demo.."); AdoConnectionDemo demo1 = new AdoConnectionDemo(userName, password, datasource, databaseName); demo1.ConnectToSQLAzureDemo(); Console.WriteLine("Demo Complete.. Press any key"); Console.ReadKey();}(Code Snippet – Intro to SQL Azure - Ex04 ADO demo implementation – VB)Visual BasicSub Main() ' Invoke the connection demo Console.WriteLine("Starting the Connection Demo..") Dim demo1 = New AdoConnectionDemo(_userName, _password, _datasource, _databaseName) demo1.ConnectToSQLAzureDemo() Console.WriteLine("Demo Complete.. Press any key") Console.ReadKey()End SubUpdate the placeholders above the main method with the information of your SQL Azure account.Note: This information is related with Exercise 1 where you have created a SQL Azure account, a database and a user. Anyway, you can use whatever SQL Azure account since the demo will create a new table and then remove it right before finishing.Run the application by hitting F5. You should see the following output in a console window.Figure 39Expected output for Connection DemoTask 3 – Connecting to SQL Azure Using ODBCIn this task, you will create a class that inherits from the SQLAzureConnectionDemo class and implements the methods for connect to SQL Azure using ODBC.Add a new class to the ConnectDemoApp project called OdbcConnectionDemo. To do this, right-click the ConnectDemoApp project in the solution explorer and select Add | Class. In the Add New Item dialog, make sure that the Class template is selected and set the name to OdbcConnectionDemo.cs or OdbcConnectionDemo.vb based on the language of your preference.Make sure that you have the following namespace directives at the top of the file:C#using System.mon;using System.Data.Odbc;Visual BasicImports System.monImports System.Data.OdbcUpdate the class definition to make it public and to inherit from SQLAzureConnectionDemo. The final implementation should look like the following: C#public class OdbcConnectionDemo : SQLAzureConnectionDemo{}Visual BasicPublic Class OdbcConnectionDemo Inherits SQLAzureConnectionDemoEnd ClassImplement the class constructor to get the connection information and pass it as parameters to the base class constructor:(Code Snippet – Intro to SQL Azure - Ex04 ODBC constructor – C#)C#public OdbcConnectionDemo(string userName, string password, string dataSource, string databaseName): base (userName, password, dataSource, databaseName){}(Code Snippet – Intro to SQL Azure - Ex04 ODBC constructor – VB)Visual BasicPublic Sub New(ByVal userName As String, ByVal password As String, ByVal dataSource As String, ByVal databaseName As String) MyBase.New(userName, password, dataSource, databaseName)End SubOverride the CreateConnection method to create an OdbcConnection in your OdbcConnectionDemo class:(Code Snippet – Intro to SQL Azure - Ex04 ODBC CreateConnection – C#)C#protected override DbConnection CreateConnection(string userName, string password, string dataSource, string databaseName){ return new OdbcConnection(CreateOdbcConnectionString(userName, password, dataSource, databaseName));}(Code Snippet – Intro to SQL Azure - Ex04 ODBC CreateConnection – VB)Visual BasicProtected Overrides Function CreateConnection(ByVal userName As String, ByVal password As String, ByVal dataSource As String, ByVal databaseName As String) As DbConnection Return New OdbcConnection(CreateOdbcConnectionString(userName, password, dataSource, databaseName))End FunctionImplement the CreateOdbcConnectionString method used by the previous method. This method is responsible for building up the ODBC Drivers connection string. The proposed implementation is using SQL Server Native Client 10.0 as its driver. You can specify any other ODBC driver of your preference here. (Code Snippet – Intro to SQL Azure - Ex04 ODBC CreateOdbcConnectionString method – C#)C#private string CreateOdbcConnectionString(string userName, string password, string dataSource, string databaseName){ string serverName = GetServerName(dataSource); OdbcConnectionStringBuilder connectionStringBuilder = new OdbcConnectionStringBuilder { Driver = "SQL Server Native Client 10.0", }; connectionStringBuilder["Server"] = "tcp:" + dataSource; connectionStringBuilder["Database"] = databaseName; connectionStringBuilder["Uid"] = userName + "@" + serverName; connectionStringBuilder["Pwd"] = password; return connectionStringBuilder.ConnectionString;}(Code Snippet – Intro to SQL Azure - Ex04 ODBC CreateOdbcConnectionString method – VB)Visual BasicPrivate Function CreateOdbcConnectionString(ByVal userName As String, ByVal password As String, ByVal dataSource As String, ByVal databaseName As String) As String Dim serverName As String = GetServerName(dataSource) Dim connectionStringBuilder As OdbcConnectionStringBuilder = New OdbcConnectionStringBuilder With {.Driver = "SQL Server Native Client 10.0"} connectionStringBuilder("Server") = "tcp:" & dataSource connectionStringBuilder("Database") = databaseName connectionStringBuilder("Uid") = userName & "@" & serverName connectionStringBuilder("Pwd") = password Return connectionStringBuilder.ConnectionStringEnd FunctionOverride the CreateCommand method to create an Odbc command. Remember that this abstract method is called in the parent class to get the connection and execute the different SQL statement samples.(Code Snippet – Intro to SQL Azure - Ex04 ODBC CreateCommand method – C#)C#protected override DbCommand CreateCommand(DbConnection connection){ return new OdbcCommand() { Connection = connection as OdbcConnection };}(Code Snippet – Intro to SQL Azure - Ex04 ODBC CreateCommand method – VB)Visual BasicProtected Overrides Function CreateCommand(ByVal connection As DbConnection) As DbCommand Return New OdbcCommand() With {.Connection = TryCast(connection, OdbcConnection)}End FunctionThat is the specific code required to use an Odbc connection. Now you will include some code int the Program.cs or Module1.vb file (based on your language) to test the connection and see how the different operations work.Open the Program.cs (for Visual C# projects) or Module1.vb (for Visual Basic projects) file double-clicking it in the Solution Explorer inside the ConnectDemoApp project.In the main method, implement the logic to create a new instance of the OdbcConnectionDemo class and execute the Demo against SQL Azure. You can add or replace the code from the previous tasks based on if you want to test all the technologies at once or only this one.(Code Snippet – Intro to SQL Azure - Ex04 ODBC demo implementation – C#)C#static void Main(string[] args){ //... //Invoke the ODBC connection demo Console.WriteLine("Starting the ODBC Connection Demo.."); OdbcConnectionDemo demo2 = new OdbcConnectionDemo(userName, password, datasource, databaseName); demo2.ConnectToSQLAzureDemo(); Console.WriteLine("Demo Complete.. Press any key"); Console.ReadKey();}(Code Snippet – Intro to SQL Azure - Ex04 ODBC demo implementation – VB)Visual BasicSub Main() ' ... ' Invoke the ODBC connection demo Console.WriteLine("Starting the ODBC Connection Demo..") Dim demo2 = New OdbcConnectionDemo(_userName, _password, _datasource, _databaseName) demo2.ConnectToSQLAzureDemo() Console.WriteLine("Demo Complete.. Press any key") Console.ReadKey()End SubIf you have not done it before, update the placeholders above the main method with the information of your SQL Azure account.Note: This information is related with Exercise 1 where you created a SQL Azure account, a database and a user. Anyway, you can use whatever SQL Azure account since the demo will create a new table and then remove it right before finishing.Run the application by hitting F5. You should see the following output in a console window.Figure 40Expected output for Odbc Connection DemoTask 4 – Connecting to SQL Azure Using OLEDBIn this task, you will create a class that inherits from the SQLAzureConnectionDemo class and implements the methods for connect to SQL Azure using OLEDB.Add a new class to the ConnectDemoApp project called OleDbConnectionDemo. To do this, right-click the ConnectDemoApp project in the Solution Explorer and select Add | Class. In the Add New Item dialog, make sure that the Class template is selected and set the name to OleDbConnectionDemo.cs or OleDbConnectionDemo.vb based on the language of your preference.Make sure that you have the following namespace directives at the top of the file:C#using System.mon;using System.Data.OleDb;Visual BasicImports System.monImports System.Data.OleDbUpdate the class definition to make it public and to inherit from SQLAzureConnectionDemo. It should look like the following: C#public class OleDbConnectionDemo:SQLAzureConnectionDemo{}Visual BasicPublic Class OleDbConnectionDemo Inherits SQLAzureConnectionDemoEnd ClassImplement the class constructor to get the connection information and pass it as parameters to the base class constructor:(Code Snippet – Intro to SQL Azure - Ex04 OLEDB constructor – C#)C#public OleDbConnectionDemo(string userName, string password, string dataSource, string databaseName) : base(userName, password, dataSource, databaseName){}(Code Snippet – Intro to SQL Azure - Ex04 OLEDB constructor – VB)Visual BasicPublic Sub New(ByVal userName As String, ByVal password As String, ByVal dataSource As String, ByVal databaseName As String) MyBase.New(userName, password, dataSource, databaseName)End SubOverride the CreateConnection method to create an OleDbConnection in your OleDbConnectionDemo class:(Code Snippet – Intro to SQL Azure - Ex04 OLEDB CreateConnection – C#)C#protected override DbConnection CreateConnection(string userName, string password, string dataSource, string databaseName){ return new OleDbConnection(CreateOleDBConnectionString(userName, password, dataSource, databaseName));}(Code Snippet – Intro to SQL Azure - Ex04 OLEDB CreateConnection – VB)Visual BasicProtected Overrides Function CreateConnection(ByVal userName As String, ByVal password As String, ByVal dataSource As String, ByVal databaseName As String) As DbConnection Return New OleDbConnection(CreateOleDBConnectionString(userName, password, dataSource, databaseName))End FunctionImplement the CreateOleDbConnectionString method used by the previous method. This method is responsible for building up the connection string used to create the connection to SQL Azure using OleDb. (Code Snippet – Intro to SQL Azure - Ex04 OLEDB CreateOleDbConnectionString method – C#)C#private string CreateOleDBConnectionString(string userName, string password, string dataSource, string databaseName){ string serverName = GetServerName(dataSource); OleDbConnectionStringBuilder connectionStringBuilder = new OleDbConnectionStringBuilder { Provider = "SQLOLEDB", DataSource = dataSource, }; connectionStringBuilder["Initial Catalog"] = databaseName; connectionStringBuilder["UId"] = userName + "@" + serverName; connectionStringBuilder["Pwd"] = password; return connectionStringBuilder.ConnectionString;}(Code Snippet – Intro to SQL Azure - Ex04 OLEDB CreateOleDbConnectionString method – VB)Visual BasicPrivate Function CreateOleDBConnectionString(ByVal userName As String, ByVal password As String, ByVal dataSource As String, ByVal databaseName As String) As String Dim serverName As String = GetServerName(dataSource) Dim connectionStringBuilder As OleDbConnectionStringBuilder = New OleDbConnectionStringBuilder With {.Provider = "SQLOLEDB", .DataSource = dataSource} connectionStringBuilder("Initial Catalog") = databaseName connectionStringBuilder("UId") = userName & "@" & serverName connectionStringBuilder("Pwd") = password Return connectionStringBuilder.ConnectionStringEnd FunctionOverride the CreateCommand method to create an OleDb command. Remember that this abstract method is called in the parent class to get the connection and execute the different SQL statement samples.(Code Snippet – Intro to SQL Azure - Ex04 OLEDB CreateCommand method – C#)C#protected override DbCommand CreateCommand(DbConnection connection){ return new OleDbCommand() { Connection = connection as OleDbConnection };}(Code Snippet – Intro to SQL Azure - Ex04 OLEDB CreateCommand method – VB)Visual BasicProtected Overrides Function CreateCommand(ByVal connection As DbConnection) As DbCommand Return New OleDbCommand() With {.Connection = TryCast(connection, OleDbConnection)}End FunctionThat is the specific code required to use an OleDb connection. Now you will include some code in the Program.cs or Module1.vb file (based on your language) to test the connection and see how the different operations work.Open the Program.cs (for Visual C# projects) or Module1.vb (for Visual Basic projects) file double-clicking it in the Solution Explorer inside the ConnectDemoApp project.In the main method, implement the logic to create an instance of the OleDbConnectionDemo class and execute the Demo against SQL Azure. You can add or replace the code from the previous tasks based on if you want to test all the technologies at once or only this one.(Code Snippet – Intro to SQL Azure - Ex04 OLEDB demo implementation – C#)C#static void Main(string[] args){ //... //Invoke the OleDB connection demo Console.WriteLine("Starting the OLEDB Connection Demo.."); OleDbConnectionDemo demo3 = new OleDbConnectionDemo(userName, password, datasource, databaseName); demo3.ConnectToSQLAzureDemo(); Console.WriteLine("Demo Complete.. Press any key"); Console.ReadKey();}(Code Snippet – Intro to SQL Azure - Ex04 OLEDB demo implementation – VB)Visual BasicSub Main() ' ... ' Invoke the OleDB connection demo Console.WriteLine("Starting the OLEDB Connection Demo..") Dim demo3 = New OleDbConnectionDemo(_userName, _password, _datasource, _databaseName) demo3.ConnectToSQLAzureDemo() Console.WriteLine("Demo Complete.. Press any key") Console.ReadKey()End SubIf you did not do it before, update the placeholders above the main method with the information of your SQL Azure account.Note: This information is related with Exercise 1 where you have created a SQL Azure account, a database and a user. Anyway, you can use whatever SQL Azure account since the demo will create a new table and then remove it right before finishing.Run the application by hitting F5. You should see the following output in a console window.Figure 41Expected output for Odbc Connection DemoTask 5 – Connecting to SQL Azure Using Linq to SQLYou have connected in three different ways to the database on SQL Azure. The last technology that you are going to try will be Linq to SQL. You will notice that the class implementation for this demo will not inherit from SQLAzureConnectionDemo class since if you use Linq to SQL, you don’t have to manage Connections and Commands, those are administer by the underlying technology and you don’t have to worry about them.Add a new class to the ConnectDemoApp project called LinqToSqlConnectionDemo. To do this, right-click the ConnectDemoApp project in the Solution Explorer and select Add | Class. In the Add New Item dialog, make sure that the Class template is selected and set the name to LinqToSqlConnectionDemo.cs or LinqToSqlConnectionDemo.vb based on the language of your preference.Update the class definition to make it public. It should look like the following: C#public class LinqToSqlConnectionDemo {}Visual BasicPublic Class LinqToSqlConnectionDemoEnd ClassOnly for C#, make sure that you have the following namespace directives at the top of the class:C#using System;using System.Linq;Add the following method to the LinqToSqlConnectionDemo class. This retrieves from the database all the companies names and show them in the Console. To do that, it takes advantage of the AdventureWorksSqlAzureDataContext Linq to SQL class (defined in the AdventureWorksSqlAzure.dbml file).(Code Snippet – Intro to SQL Azure - Ex04 LINQ ConnectToSQLAzure method – C#)C#/// <summary>/// AdventureWorksSqlAzureDataContext takes care of handling your transactions for you /// leaving you free you use Linq to extraxt information stored up in the cloud./// </summary>public void ConnectToSQLAzureDemo(){ AdventureWorksSqlAzureDataContext context = new AdventureWorksSqlAzureDataContext(); // get all company names IQueryable<string> companyNames = from customer in context.Customers select panyName; // display these all on the console foreach (string company in companyNames) { Console.WriteLine(company); }} (Code Snippet – Intro to SQL Azure - Ex04 LINQ ConnectToSQLAzure method – VB)Visual Basic''' <summary>''' AdventureWorksSqlAzureDataContext takes care of handling your transactions for you''' leaving you free you use Linq to extraxt information stored up in the cloud.''' </summary>Public Sub ConnectToSQLAzureDemo() Dim context As New AdventureWorksSQLAzureDataContext() ' get all company names Dim companyNames As IQueryable(Of String) = From customer In context.Customers _ Select panyName ' display these all on the console For Each company As String In companyNames Console.WriteLine(company) Next companyEnd SubAdd the following code to invoke the Linq to SQL demo in the main method of the Program.cs file (for Visual C# projects) or Module1.vb (for Visual Basic projects) file. You can add or replace the code from the previous tasks based on if you want to test all the technologies at once or only this one.(Code Snippet – Intro to SQL Azure - Ex04 LINQ demo implementation – C#)C#static void Main(string[] args){ //... //Invoke the Linq to SQL connection demo Console.WriteLine("Starting the Linq to SQL Connection Demo.."); LinqToSqlConnectionDemo demo4 = new LinqToSqlConnectionDemo(); demo4.ConnectToSQLAzureDemo(); Console.WriteLine("Demo Complete.. Press any key"); Console.ReadKey();}(Code Snippet – Intro to SQL Azure - Ex04 LINQ demo implementation – VB)Visual BasicSub Main() ' ... ' Invoke the Linq to SQL connection demo Console.WriteLine("Starting the Linq to SQL Connection Demo..") Dim demo4 = New LinqToSqlConnectionDemo() demo4.ConnectToSQLAzureDemo() Console.WriteLine("Demo Complete.. Press any key") Console.ReadKey()End SubOpen the app.Config file and change the relevant section to point to your SQL Azure Database, and connect using the test user created earlier in this lab.Note: This step is required since AdventureWorksSQLAzureDataContext class gets the parameters to create the connection from the configuration file.Press F5 to run your application. You should see a long list of company names. Those are retrieved from your database on the SQL Azure Server using Linq to SQL. Figure 42Expected output for Linq to Sql Connection DemoTask 6 – Connecting to SQL Azure via Non-Microsoft TechnologiesIt is trivial to connect to SQL Azure using non-windows technologies.The following PHP version takes on a pattern that you should be familiar with from the previous task. It uses the SQL Server Native Client ODBC driver to establish a connection.PHP<?php? $host = "server.database.";? $dbname = "database";? $dbuser = "user@server";? $dbpwd = "password";? $driver = "{SQL Server Native Client 10.0}";? // Build connection string? $dsn="Driver=$driver;Server=$host;Database=$dbname;Encrypt=true;TrustServerCertificate=true";? if (!($conn = @odbc_connect($dsn, $dbuser, $dbpwd))) {????? die("Connection error: " . odbc_errormsg());? }?? // Got a connection, do what you will?? // Free the connection? @odbc_close($conn);?>Connecting to SQL Azure using JDBC is also trivial. Refer to the following code.JAVA// Build a connection stringString connectionUrl= "jdbc:sqlserver://server.database.;" + "database=mydatabase;encrypt=true;user=user@server;password=*****";// Next, make the sure the SQL Server Driver is loaded. Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); // Then attempt to get a connection. This will null or throw if we can't get a connection.Connection sqlConn = DriverManager.getConnection(connectionUrl);if (sqlConn == null){ System.out.println("Unable to obtain connection. exiting"); System.exit(1);}// Got a connection, do what you will// Free the connection sqlConn.close();SummaryIn this lab, you have looked at the basics of working with SQL Azure. If you have any SQL Server experience you may have found the lab familiar and that is, indeed, the point. Working with SQL Azure should be very familiar to anyone who has worked with SQL Server.You learned to provision your account, to create new databases, logins and users for those databases. You saw that for the most part you could simply create objects in SQL Azure as you would with an on-premise SQL Server. In addition, you created a simple Windows Azure application that is able to consume a SQL Azure database. The approach taken was, developing first against an on premise database and then executing against SQL Azure is one that will be useful for most Windows Azure plus SQL Azure projects.Finally, you saw that creating connections to SQL Azure using Microsoft technologies is the same as creating connections to any normal on premise database. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download