One of very important services of type referenced service is Azure SQL service. Unfortunately it is currently not well documented how to use it.
In fact I’m going to describe in this post the scenario of connecting one WebApplication to SQL database hosted in Windows Azure.
This looks like shown at the following picture:
To add SQL Azure as service you just need to add new referenced service as shown below:
Most interesting here are configuration properties:
Artifacts:
You can use artifacts to provision your database. To do that you need to create .dacpac file and to add it to list of artifacts. While provisioning your application, AppFabric SQL service automatically detects the presence of a .dacpac file in your SQL Service and installs this .dacpac file against the database connection string provided.
PrivisioningAction:
Set it to none if you don’t want to provision DB. InstallOfNotExists is default option, which will install .dacpac if exist. If .dacpac does not exist in the list of referenced artifacts, provision will not be done.
DatabaseName:
The real name of database which will be used. Note that the name of database MUST be configured here and NOT in the connection string. When you go to Management Portal of you DB hosted Windows Azure you can get connection string which looks like:
Server=tcp:abcdefgh.database.windows.net,1433;Database=YourDbName;User ID=youruser@abcdefgh;Passwordpassword;Trusted_Connection=False;Encrypt=True;
You need to remove marked part. The connection string without of marked part has to be as connection string property. The parked part shoud be set to value of DataBaseName property (see above).
If you don’t do it as here describe you will experience following error:
Cannot open database "Database" requested by the login. The login failed.
Name:
The logical name of database in your application.
ServerConnection String
If you are not reading my blog regularly, this parameter will probably be the reason why you have landed to this post.
Unprovisioning Action
Can be ignored (none) or used to drop (drop) DB while publishing.
Last, but not least. If you get any problems with IDE when trying to add connection string (like: null reference occurred or similar) just open app.designer.cs file and enter connection string value directly in editor,
by looking for sqlserviceexportdefinition1.ServerConnectionString .
Consume data
After you are done with configuration, let’s build simple application which reads the data from database. For this reason I added ASP.NET service and pasted following code in Page_Load method:
| protected void Page_Load(object sender, EventArgs e)
{ SqlConnection conn = ServiceReferences.CreateImport1(); var cmd = conn.CreateCommand();
cmd.CommandText = "Select * from TItem";
cmd.CommandType = System.Data.CommandType.Text;
conn.Open();
using(var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while(reader.Read())
{
string itemId = reader.GetString(1);
Response.Write(itemId + "<BR/>");
}
}
} |
In the code above all looks familiar instead of the first line. Because AppFabric Applications simplifies development of distributed system, it provides a helper class which implements methods for creating of endpoints of any kind. This class is generated when some service adds the reference to some other service. In this case the ASP.NET application contains a reference to SQL Service endpoint. This is one of major design decisions and the reason why all components are called services.
One WCF endpoint would be defined by some address, binding and contract. The SQL Azure endpoint is defined by connection string. This is the way how to thin about AppFabric services.
Here is the magic line of code:
SqlConnection conn = ServiceReferences.CreateImport1();
The code in helper class ServiceReferences.g.cs s shown below:
| class ServiceReferences { public static System.Data.SqlClient.SqlConnection CreateImport1()
{ return Service.ExecutingService.ResolveImport<System.Data.SqlClient.SqlConnection>("Import1");
} public static System.Data.SqlClient.SqlConnection CreateImport1(System.Collections.Generic.Dictionary<string, object> contextProperties) {
return Service.ExecutingService.ResolveImport<System.Data.SqlClient.SqlConnection>("Import1", null, contextProperties);
}
} |
See also following posts:
Adam’s post:
Configuring, deploying, and monitoring applications using AppFabric Application Manager Justin’s post:
Using MSBuild to deploy your AppFabric Application
Posted
Jul 17 2011, 12:41 AM
by
Damir Dobric