SQL Azure with ASP Dot Net

Introduction

SQL Azure (SSDS – SQL Server Data Services) is a cloud
database system offered by Microsoft. We interact with the SQL Azure services
by either issuing statements to it though a command prompt or developing Dot
Net applications. This article will introduce and demonstrate development of
SQL Azure ASP Dot Net applications.

Connecting

Connecting to SQL Azure is similar to connecting to a
traditional SQL Server. A connection string is created then opened. To begin
with SQL Azure, create an account from the Microsoft Azure web site. Once the
account is created, log in and navigate to the Server Administration page.
Select the master database (master is automatically created for us when the
Azure account is created), and then select “Connection Strings”. A popup will
display the connection string for ADO Dot Net and ODBC. Copy the ADO Dot Net
string. On the next tab, Firewall Settings, create a rule to allow your
client machine access as shown below.

The IP address will be your outside connection address. The
firewall tool will display the IP address you connected from. SQL Azure
services are firewall protected, so each client requiring access will need a
rule. If you’re developing from a machine which receives a DHCP address from
an internet service provider, then a new rule will need to be created each time
a new address is received (unless you know the IP address range, then it can be
specified here). Allow up to five minutes for the new firewall rule to go
into effect.

Create a new Dot Net web site and aspx page from Visual
Studio. In the Page Load section, add using statement s for System.Data and System.Data.SQLClient
just as we would for a traditional SQL Server web page. Save the connection
string as a variable and pass it to the SqlConnection object as shown below:


protected void Page_Load(object sender, EventArgs e)
{

string sConn = “Server=tcp:qdpapn8.database.windows.net;
Database=master;
User ID=dons;
Password=mypassword;Trusted_Connection=False”;

SqlConnection objConn = new SqlConnection(sConn);
objConn.Open();

objConn.Close();

}

The connection string ID and password were specified when
the Azure account was first created. The server section specifies the fully
qualified name. In this example, qdpapn8, is my personal unique server that
SQL
Azure auto generated for my account. My personal server name is appended to the public
“database.windows.net” domain to create the Fully Qualified Name (FQN). Note
the “tcp” prefix to the server name. SQL Azure listens on TCP port 1433, so our
client workstation must allow outbound traffic on this port. The
Trusted Connection will always be set to False,
as Windows Logins are not supported on SQL Azure. Executing this web page should result in a blank
Internet Explorer window. If an error is thrown, it will be displayed there
instead.

CREATE DATABASE

Now that we have a valid connection string, we’ll create a test
database. The TSQL syntax for SQL Azure is usually identical to
traditional SQL Server. A full list of supported statements, limitations, and
options is available from this Microsoft web site: Transact SQL
Reference SQL Azure Database
. In this example, we’ll pass a CREATE DATABASE statement
to a SqlCommand object as shown below. Remember to add the System.Data and System.Data.SQLClient
using statements to all examples in this article.


protected void Page_Load(object sender, EventArgs e)
{

string sConn = “Server=tcp:qdpapn8.database.windows.net;Database=master;User ID=dons;
Password=mypwd;Trusted_Connection=False”;

SqlConnection objConn = new SqlConnection(sConn);
objConn.Open();

string sSQL = “CREATE DATABASE TestDB”;

SqlCommand objCmd = new SqlCommand(sSQL, objConn);
objCmd.CommandType = System.Data.CommandType.Text;
objCmd.ExecuteNonQuery();

objConn.Close();
}

The CREATE DATABASE statement was saved as a string and then
passed as a Text Command statement. After executing this page, a new 1 GB
database was created as shown in the Server Administration web page.

Currently, there are two different database sizes, 1 GB or
10 GB, each with a different pricing policy.

CREATE TABLE

To create a Table in the new database, change the connection
string specifying the new Database Name and change the statement to a typical CREATE
TABLE as shown below. This example will create a two-column table.


protected void Page_Load(object sender, EventArgs e)
{
string sConn = “Server=tcp:qdpapn8.database.windows.net;Database=TestDB;User ID=dons;
Password=mypwd;Trusted_Connection=False”;

SqlConnection objConn = new SqlConnection(sConn);
objConn.Open();

string sSQL = “CREATE TABLE myTestTable
( FirstName varchar(50) NOT NULL, LastName varchar(50) NULL,
CONSTRAINT PK_TestTable PRIMARY KEY (FirstName) )”;

SqlCommand objCmd = new SqlCommand(sSQL, objConn);
objCmd.CommandType = System.Data.CommandType.Text;
objCmd.ExecuteNonQuery();

objConn.Close();
}

Note that the CREATE TABLE statement included a key, in this
case a PRIMARY KEY. Heap tables (tables without an index) are not allowed in
SQL Azure.

INSERT

The syntax for inserting data into SQL Azure is identical to
traditional SQL Server. Here we’ve modified the SQL string with an INSERT.
The rest of the code, SQLConnection, SQLCommand, etc., are identical to the
previous examples.


string sSQL = “INSERT INTO myTestTable (FirstName, LastName) VALUES (‘bob’, ‘smith’) “;

SELECT

To receive the newly inserted row back, drag a GridView
control onto a new page and modify the SQL string to a SELECT statement. Then we’ll
bind the GridView to the SQLCommand object and execute the Reader as shown
below:


protected void Page_Load(object sender, EventArgs e)
{
string sConn = “Server=tcp:qdpapn8.database.windows.net;Database=TestDB;User ID=dons;
Password=mypwd;Trusted_Connection=False”;

SqlConnection objConn = new SqlConnection(sConn);
objConn.Open();

string sSQL = “SELECT * FROM myTestTable”;

SqlCommand objCmd = new SqlCommand(sSQL, objConn);
objCmd.CommandType = System.Data.CommandType.Text;

myGridView.DataSource = objCmd.ExecuteReader();
myGridView.DataBind();

objConn.Close();
}

The result will be our one inserted row being returned as
shown below:

Stored Procedures

In my opinion, one of the best features of SQL Azure is the
ability to create Stored Procedures in the cloud. This is a rare feature, most
cloud databases do not support stored procedures and instead database code must
be passed as a string. By using Stored Procedures, we create a clear
separation between database code and application code. In addition, it’s easy
to share the same stored procedure between many web pages and at the same time
we avoid SQL Injection attacks. The syntax for creating a Stored Procedure in
the cloud is the same as traditional SQL Server. In this example, we’ll simply
modify the SQL string as shown below:


protected void Page_Load(object sender, EventArgs e)
{

string sConn = “Server=tcp:qdpapn8.database.windows.net;Database=TestDB;User ID=dons;
Password=mypassword;Trusted_Connection=False”;

SqlConnection objConn = new SqlConnection(sConn);
objConn.Open();

string sSQL = “CREATE PROCEDURE GetNames AS SELECT * FROM myTestTable”;

SqlCommand objCmd = new SqlCommand(sSQL, objConn);
objCmd.CommandType = System.Data.CommandType.Text;

objCmd.ExecuteNonQuery();
objConn.Close();

}

To execute the Stored Procedure, we’ll drag a GridView onto
the form; bind that grid to the SqlCommand object, and then call the ExecuteReader
as shown below.


protected void Page_Load(object sender, EventArgs e)
{
string sConn = “Server=tcp:qdpapn8.database.windows.net;Database=TestDB;User ID=dons;
Password=mypassword;Trusted_Connection=False”;

SqlConnection objConn = new SqlConnection(sConn);
objConn.Open();

SqlCommand objCmd = new SqlCommand(“dbo.GetNames”, objConn);
objCmd.CommandType = System.Data.CommandType.StoredProcedure;

myGridView.DataSource = objCmd.ExecuteReader();
myGridView.DataBind();

objConn.Close();
}

Conclusion

Working with SQL Azure (SQL Server Data Services SSDS)
leverages our existing TSQL and Dot Net skills. The TSQL for the cloud usually
has the same syntax as a traditional SQL Server, and calling the cloud from a web
page uses the same commands and readers as other familiar data sources. Having
the ability to create relationships, use TSQL, and execute stored procedures in
the cloud are strong benefits of SQL Azure.

»


See All Articles by Columnist
Don Schlichting

Don Schlichting
Don Schlichting
Don Schlichting is a Microsoft MSCE, MCDBA and an Oracle Certified Associate. Working at Professional Control Corporation as the IT Director,.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles