How to Connect to Azure SQL Database from ASP.NET

By | October 7, 2014

Introduction

You can bind your ASP.Net control to data from Azure SQL Database just as you can with SQL Server Database. You need to use SQLDataSource class which is compatible with Azure SQL Database.
Note: All Transact-SQL statements are NOT supported in Microsoft Azure SQL Database.

 

STEP 1: Create the TestDB Database

1. Connect to the Azure SQL Database server by using the sqlcmd utility.
2. Run the following Script:

 CREATE DATABASE TestDb;
 GO
 QUIT

3. Connect to TestDb and run the following SQL Script:

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

Step 2: Create the GridView

1. In Visual Studio, on the File menu, click New and select Project.
Note: 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.

 

Step 3: Bind the Control to DataSource

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

2. Add the following to the Web.config:

<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.

Leave a Reply