Managing Core SQL Server 2008 R2 Technologies - Microsoft SQL Server 2008 R2 Administration - page 187

How to do it...
The SQL Azure connectivity features are divided into two pattern methods, they are code-near
and code-far.
F The code-near connectivity needs data access application deployed on-premises and
the same data center as the SQL Azure database. The applications web interface
connectivity is exposed through Windows Azure web role that is hosted from an ASP.
NET application.
F The code-far connectivity needs the application deployed on-premises and in
different data centers than SQL Azure. The application executes the SQL queries
using the TDS protocol to SQL Azure database.
The required coding techniques for the SQL Azure connectivity feature is used as follows:
Use ADO.NET Connection pooling that will increase code efficiency and remove the need for
the re-login process.
using (SqlConnection conn = new SqlConnection(
"Integrated Security=SSPI;Initial Catalog=DBIA-Azure"))
{
connection.Open();
// Pool Connection-A is created.
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = …;
…
}
}
using (SqlConnection conn = new SqlConnection(…))
{
conn.Open();
// Process-Application-steps.
}
The Parameterization limits the compiles and the two methods are Client-Side and
Server-side.
The Client-side parameterization using ADO.NET , ODBC or sp_executesql is a
recommended approach for query parameterization to re-use one parameterized query plan.
The following is a parameterized query example that uses sp_executesql :
declare @param_value int, @sqlstring nvarchar(500),
@param_definition nvarchar(500), @col2 int;
set @sqlstring = N'select @Sales_person = Person.Name from Person
join Sales on Person.name = Sales.Name where Sales.SaleCount = @
param';