自已做的框架RJ 第八篇----------------函数

函数在SQL中算是比较常用的了 当中分为几种 最常用的自定义函数有两种 标量函数和表值函数

标量函数

先建个函数

CREATE FUNCTION [dbo].[ScalarSUM]
(    
    @int int,
    @int2 int
)
RETURNS int 
AS
begin
RETURN @int*@int2
end

然后用生成工具生成代码

public class ScalarSUM:BaseEntity
    {
        public readonly static String TABLENAME = "dbo.ScalarSUM";

          private static ScalarSUM ___singleton;

        public static ScalarSUM Instance()
        {
            if(___singleton==null)
            {
                ___singleton = new ScalarSUM();
            }
            return ___singleton;
        }

        private ScalarSUM()
        {

        }
        
        public override string ___GetTableName() { return TABLENAME; }
    }

代码中

 var s = RJ.DModel.Product.SelectSqlSectionFor;
            s.AddColumn<ScalarSUM>((a, b) => b.Function(a.ProductID,5));

var s = RJ.DModel.Product.SelectSqlSectionFor;
            s.Where<ScalarSUM>((a, b) => a.ClickDegree_int==b.Function(a.ProductID,5));

生成SQL

SELECT [dbo].ScalarSUM([Product].[ProductID],@pl6vs9d4t0jo6vd) FROM [Product]

SELECT * FROM [Product] WHERE [Product].[ClickDegree_int] = dbo.ScalarSUM([Product].[ProductID],@pk5eam29lqp6ula)

表值函数

CREATE FUNCTION [dbo].[ProductTypeFunctionName]
(    
    @ProductTypeID int
)
RETURNS TABLE 
AS
RETURN 
(
with a as 
(
select ProductTypeID,ProductTypeParentID from ProductType 
where ProductTypeID=@ProductTypeID
 UNION ALL 
select q.ProductTypeID ,q.ProductTypeParentID from  ProductType q,a where   q.ProductTypeParentID=a.ProductTypeID

)
select distinct productid from a ,dbo.ProductSubtype b
where a.ProductTypeID =b.ProductTypeID
)

生成代码 

public sealed class ProductTypeFunctionName:RJ.Sql.BaseEntity
    {
        
        public readonly static String TABLENAME = "ProductTypeFunctionName";

        private static ProductTypeFunctionName ___singleton;

        public static ProductTypeFunctionName Instance()
        {
            if(___singleton==null)
            {
                ___singleton = new ProductTypeFunctionName();
            }
            return ___singleton;
        }

        private ProductTypeFunctionName()
        {

        }

        public readonly static QueryColumn _ProductId = new QueryColumn((TABLENAME + ".[ProductId]"), System.Data.DbType.Int32);
        public QueryColumn ProductId { get { return _ProductId; } }

        
        public override string ___GetTableName() { return TABLENAME; }
    }

代码中

var t = new RJ.Sql.SelectSqlSection<ProductTypeFunctionName>(2).AddColumn(a => a.ProductId);

  var s1 = RJ.DModel.Product.SelectSqlSectionFor;
            s1.AddFormFun<ProductTypeFunctionName>((a,b) => a.ProductID==b.ProductId,2);

 var s = RJ.DModel.Product.SelectSqlSectionFor;
            s.Where(a=>a.ProductID.In(t));

SELECT [ProductTypeFunctionName].[ProductId] FROM ProductTypeFunctionName(@pveenih6h8o22ps)

SELECT * FROM [Product] , ProductTypeFunctionName(@pr00tk80fsgxoqb) [ProductTypeFunctionName]  WHERE [Product].[ProductID] = [ProductTypeFunctionName].[ProductId]

SELECT * FROM [Product] WHERE [Product].[ProductID] IN (SELECT [ProductTypeFunctionName].[ProductId] FROM ProductTypeFunctionName(@pe2gqh5m9txbo7y))

RJ框架更新中~~~~