SALES: 1-800-867-1380

How to: Connect to Windows Azure SQL Database Through ASP.NET

You can bind data from Microsoft Windows Azure SQL Database to ASP.NET controls just as you would with SQL Server. The SqlDataSource class is compatible with Windows Azure SQL Database, so you can convert your existing solutions with only a change to the connection string.

noteNote
Not all Transact-SQL statements are supported in Windows Azure SQL Database. For more information, see Transact-SQL Reference (Windows Azure SQL Database).

For general considerations about connecting to databases in SQL Database, see Guidelines for Connecting to Windows Azure SQL Database.

ASP.NET Data Binding to SQL Database

In this example, bind the ASP.NET GridView control to the SQL Database from a service hosted in Windows Azure. By using the GridView control, you can insert, update, and delete data from a SQL Database without writing any code.

To follow this example, you must install the Windows Azure SDK and the Windows Azure Tools for Microsoft Visual Studio. However, most of these steps apply to an ASP.NET project. For more information about Windows Azure, see Windows Azure SDK. You must also initialize the Development Storage service installed with the Windows Azure Tools using the DSInit utility. Development Storage must be initialized only one time. For more information, see About Development Storage.

Create the TestDb Database

  1. Connect to the SQL Database server by using the sqlcmd utility. For more information, see How to: Connect to Windows Azure SQL Database Using sqlcmd.

  2. Run the following Transact-SQL commands:

    1> CREATE DATABASE TestDb;
    2> GO
    1> QUIT
    
  3. Connect to TestDb and run the following Transact-SQL commands:

    1> CREATE TABLE Table1 (Col1 int primary key, Col2 varchar(20));
    2> GO
    1> INSERT INTO Table1 (Col1, Col2) VALUES (1, 'string1'), (2, 'string2');
    2> GO
    1> QUIT
    

Create the GridView Control

  1. In Visual Studio, on the File menu, click New and select Project.

    noteNote
    You must run Visual Studio as an administrator to use the Windows Azure Development Fabric.

  2. In the New Project dialog box, in the Installed templates list, select Cloud for either Visual C# or Visual Basic.

  3. In the Templates list, select Windows Azure Project. Type DataBinding for the name and then click OK.

  4. In the New Windows Azure Project dialog box, select ASP.NET Web Role from the Roles list and then click the arrow to add it to the Windows Azure Solution list. Click OK.

  5. In Default.aspx, click Design to switch to the design view.

  6. Drag the GridView control from the Toolbox to the design surface.

  7. In the Properties window for the GridView, set AutoGenerateDeleteButton and AutoGenerateEditButton to true.

Bind the Control to the Data Source

  1. In Solution Explorer, in the Web Role project, double-click Web.config.

  2. In Web.config, add the following:

    
    <connectionStrings>
      <add name="SQLAzureConnection" connectionString="Data Source=<ProvideServerName>.database.windows.net;Initial Catalog=TestDb;User ID=<ProvideUserName>;Password=<ProvidePassword>;Encrypt=true;Trusted_Connection=false;"
        providerName="System.Data.SqlClient" />
    </connectionStrings>
    

    This adds the connection string to your SQL Database.

  3. In Default.aspx, click Source to switch to the source view.

  4. Add the following markup to create a SqlDataSource control for your connection:

    
    <asp:SqlDataSource ID="SqlAzureDataSource" runat="server" 
       ConnectionString="<%$ ConnectionStrings:SQLAzureConnection %>" 
       InsertCommand="INSERT INTO [Table1] ([Col1], [Col2]) VALUES (@Col1, @Col2)" 
       SelectCommand="SELECT * FROM [Table1]" 
       UpdateCommand="UPDATE [Table1] SET [Col2] = @Col2 WHERE [Col1] = @Col1"
       DeleteCommand="DELETE FROM [Table1] WHERE [Col1] = @Col1">
       <UpdateParameters>
          <asp:Parameter Name="Col2" Type="String" />
          <asp:Parameter Name="Col1" Type="Int32" />
       </UpdateParameters>
       <InsertParameters>
          <asp:formParameter Name="Col1" FormField="TextBox1" />
          <asp:formParameter Name="Col2" FormField="TextBox2" />
       </InsertParameters>
       <DeleteParameters>
          <asp:Parameter Name="Col1" Type="Int32" />
       </DeleteParameters>
    </asp:SqlDataSource>
    

    This includes the SELECT, INSERT, UPDATE, and DELETE logic for the control.

  5. Add the following attributes to the markup of your GridView control: DataSourceID="SqlAzureDataSource" and DataKeyNames="Col1".

  6. Press F5 to run the solution.

See Also

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

Show:
© 2014 Microsoft