Export (0) Print
Expand All

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

Updated: December 6, 2013

You can bind data from Microsoft Microsoft Azure SQL Database to ASP.NET controls just as you would with SQL Server. The SqlDataSource class is compatible with Microsoft 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 Microsoft Azure SQL Database. For more information, see Azure SQL Database Transact-SQL Reference.

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

ASP.NET Data Binding to SQL Database

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

To follow this example, you must install the Azure SDK and the Azure Tools for Microsoft Visual Studio. However, most of these steps apply to an ASP.NET project. For more information about Azure, see Azure SDK. You must also initialize the Development Storage service installed with the 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 Azure SQL Database server by using the sqlcmd utility. For more information, see How to: Connect to 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 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 Azure Project. Type DataBinding for the name and then click OK.

  4. In the New Azure Project dialog box, select ASP.NET Web Role from the Roles list and then click the arrow to add it to the 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 Azure 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

Community Additions

ADD
Show:
© 2014 Microsoft