Database Reference
In-Depth Information
int id = 15; // the product category
string sql = "proc_CalculateCost";
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("categoryId", SqlDbType.Float);
cmd.Parameters[0].Value = id;
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
try
{
if (dr.Read())
cost = (float)dr[0];
}
finally
{
dr.Close();
conn.Close();
}
The code for the stored procedure looks something like this:
CREATE PROC proc_CalculateCost
@categoryId int
AS
DECLARE @i intDECLARE @cost float
SET @cost = 0.0
SET @i = (SELECT count(*) FROM category WHERE ID = @categoryId)
WHILE (@i > 0)
BEGIN
SET @cost = @cost + 0.25*(SELECT Min(dollars) FROM ...)
SET @i = @i - 1
END
SELECT @cost
The advantage of calling a stored procedure is that you don't need to fetch the necessary records
across the Internet to calculate the cost figure. The stored procedure runs where the data is located and
returns only a single value in this case.
Provider Statistics
Last but not least, let's look at the ADO.NET library's performance metrics to obtain the library's point of
view from a performance standpoint. The library doesn't return CPU metrics or any other SQL Azure
metric; however, it can provide additional insights when you're tuning applications, such as giving you
the number of roundtrips performed to the database and the number of packets transferred.
As previously mentioned, the number of packets returned by a database call is becoming more
important because it can affect the overall response time of your application. If you compare the
number of packets returned by a SQL statement against a regular SQL Server installation to the number
of packets returned when running the same statement against SQL Azure, chances are that you see more
