How to: Connect to Azure SQL Database Through ASP.NET
Updated: November 24, 2014
You can bind data from 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.
For general considerations about connecting to databases in Azure SQL Database, see Guidelines for Connecting to Azure SQL Database Programmatically.
Use ASP.NET Web Controls to Bind to an Azure SQL Database
This example shows how to 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.
The Storage Emulator installed with the Azure SDK must be initialized to point to the correct database, as described in Initialize the Storage Emulator by Using the Command-Line Tool.
Review the information in Guidelines for Connecting to Azure SQL Database Programmatically to ensure your firewall and ports have been configured to connect with Azure SQL Database.
Create the TestDb Database using the sqlcmd Utility
Connect to the Azure SQL Database server by using the sqlcmd utility. For information about connecting, see How to: Connect to Azure SQL Database Using sqlcmd.
Run the following Transact-SQL commands:
1> CREATE DATABASE TestDb; 2> GO 1> QUIT
TestDband 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 in Visual Studio
In Visual Studio, on the File menu, click New, and then select Project.
Note You must run Visual Studio as an administrator to use the Azure Development Fabric.
In the New Project dialog box, in the Installed templates list, select Cloud for either Visual C# or Visual Basic.
In the Templates list, select ASP.NET Web Application. Type DataBinding for the name and then click OK.
In the New ASP.NET Project dialog box, select Web Forms from the list of templates and ensure that the Host in the cloud box is checked. Click OK.
In Solution Explorer, double-click Default.aspx, and then click Design to switch to the design view.
Drag the GridView control from the Toolbox to the design surface.
In the Properties window for the GridView, set AutoGenerateDeleteButton and AutoGenerateEditButton to true.
Bind the Control to the Data Source in Visual Studio
In Solution Explorer, double-click Web.config.
In Web.config, replace the current connection string with the following, and provide the appropriate values:
<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>
In Default.aspx, click Source to switch to the source view.
Add the following markup inside the
<asp:Content>tag 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.
Add the following attributes to the markup of your GridView control in Default.aspx:
Press F5 to run the solution. The browser opens and shows the contents of the table, and enables the user to edit and delete the values in the table.