Database Reference
In-Depth Information
Listing 13-3.
Ownership chaining: CLR part
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlMoney GetOrderTotalCLR(SqlInt32 orderId)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand(
@"select @Result = sum(Quantity * Price)
from dbo.OrderLineItems
where OrderId = @OrderId", conn);
cmd.Parameters.Add("@OrderId", SqlDbType.Int).Value = orderId;
cmd.Parameters.Add("@Result", SqlDbType.Float).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
return new SqlMoney((double)cmd.Parameters[1].Value);
}
}
Listing 13-4.
Ownership chaining: T-SQL part
create function dbo.GetOrderTotal(@OrderId int)
returns money
as
begin
return
(
select sum(Quantity * Price) as Total
from dbo.OrderLineItems
where OrderId = @OrderId
)
end
go
create view dbo.vOrdersTSQL(OrderId, OrderTotal)
as
select o.OrderId, dbo.GetOrderTotal(o.OrderId)
from dbo.Orders o
go
create view dbo.vOrdersCLR(OrderId, OrderTotal)
as
select o.OrderId, dbo.GetOrderTotalCLR(o.OrderId)
from dbo.Orders o
go
grant select on object::dbo.vOrdersTSQL to [Bob]
grant select on object::dbo.vOrdersCLR to [Bob]
go
