创建自定义函数和存储过程
打开 ALinq 安装目录下的 Oracle(ODP)Samples 解决方案,然后运行 Sample3_StoreProcedure 项目,点击 "Click to Create Procedures" 按钮,当然,在运行前你得先修改连接字符串,如果你还没有修改的话。在创建完存储过程后,请将所它们编译。
将数据库的函数映射到方法
一、简单函数映射
我们首先来进行映射的是 Upper 函数,这是一个将字符串转为大写的函数,这个函数的一个输入字符串参数,并且返回也是字符串。
应该我们可以创建一个如下的方法映射到该函数去。
![ContractedBlock.gif](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
public
partial
class
NorthwindDataContext : ALinq.DataContext
{
[ALinq.Mapping.Function(IsComposable = true , Name = " UPPER " )]
public string Upper(
[ALinq.Mapping.Parameter(DbType = " VarChar " )]
string text)
{
throw new NotSupportedException();
}
}
{
[ALinq.Mapping.Function(IsComposable = true , Name = " UPPER " )]
public string Upper(
[ALinq.Mapping.Parameter(DbType = " VarChar " )]
string text)
{
throw new NotSupportedException();
}
}
下面我们来运行一段代码,测试一下。
![ContractedBlock.gif](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
static
void
Main(
string
[] args)
{
var dc = new NorthwindDataContext()
{
Log = Console.Out
};
var names = dc.Products.Select(o => dc.Upper(o.Productname)).ToList();
foreach (var name in names)
Console.WriteLine(name);
}
{
var dc = new NorthwindDataContext()
{
Log = Console.Out
};
var names = dc.Products.Select(o => dc.Upper(o.Productname)).ToList();
foreach (var name in names)
Console.WriteLine(name);
}
运行结果:
二、返回结果集的函数映射
在 Server Explorer 面板中,展开 NORTHWIND 连接,并找到 Packages->PKG2->GET_ALL_CATEGORIES 函数。
我们现在来将它映谢到 DataContext 类的方法。双击打开,代码如下:
![ContractedBlock.gif](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
PACKAGE BODY PKG2
IS
FUNCTION GET_ALL_CATEGORIES RETURN MYTYPE IS
MYCS MYTYPE;
BEGIN
OPEN MYCS FOR SELECT CATEGORYID, CATEGORYNAME, DESCRIPTION
FROM CATEGORIES;
RETURN MYCS;
END GET_ALL_CATEGORIES;
END PKG2;
FUNCTION GET_ALL_CATEGORIES RETURN MYTYPE IS
MYCS MYTYPE;
BEGIN
OPEN MYCS FOR SELECT CATEGORYID, CATEGORYNAME, DESCRIPTION
FROM CATEGORIES;
RETURN MYCS;
END GET_ALL_CATEGORIES;
END PKG2;
从代码我们可以看出,它是返回一个数据集,并且是没有参数的,对于返回单一类型的结果集,我们要使用的是 ALinq.ISingleResult<T> 类型。方法定义如下:
![ContractedBlock.gif](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
public
partial
class
NorthwindDataContext : ALinq.DataContext
{
[ALinq.Mapping.Function(Name = " PKG2.GET_ALL_CATEGORIES " )]
public ALinq.ISingleResult < Category > GetAllCategories()
{
var result = ExecuteMethodCall( this , (MethodInfo)MethodBase.GetCurrentMethod());
return (ISingleResult < Category > )result.ReturnValue;
}
}
{
[ALinq.Mapping.Function(Name = " PKG2.GET_ALL_CATEGORIES " )]
public ALinq.ISingleResult < Category > GetAllCategories()
{
var result = ExecuteMethodCall( this , (MethodInfo)MethodBase.GetCurrentMethod());
return (ISingleResult < Category > )result.ReturnValue;
}
}
调用该方法的代码:
![ContractedBlock.gif](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
static
void
Main(
string
[] args)
{
var dc = new NorthwindDataContext() { Log = Console.Out };
var categories = dc.GetAllCategories();
foreach (var category in categories)
Console.WriteLine( " {0} {1} " , category.Categoryid, category.Categoryname);
}
{
var dc = new NorthwindDataContext() { Log = Console.Out };
var categories = dc.GetAllCategories();
foreach (var category in categories)
Console.WriteLine( " {0} {1} " , category.Categoryid, category.Categoryname);
}
运行结果: