Databases Reference
In-Depth Information
{
cost += 0.25 * ...; // calculation logic goes here
}
}
finally
{
dr.Close();
conn.Close();
}
Or you can calculate the cost in a stored procedure and change the previous code to call the stored procedure instead:
float cost = 0.0; // the total cost
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
