Castle,ActiveRecord,使用,存储过程,实体类
这几日就在研究这个问题
寻遍网上,也没找到个解决方法只有
在NHibernate里执行存储过程 里面操作NHibernate的一点方法
于是动手写成了以下这个类
当然,只能保证测试可以通过,可能还有很多问题,要一个一个解决
IDictionary<string,object> p=new Dictionary<string,Object>();//参数
p.Add("userid", 10000);//参一
p.Add("id", 17002);//参二
p.Add("groupid", 51);//参三
PropertyBag.Add("list",
ChAlumna.CastleExt.Execute.GetList<LogToAccount>(//执行
"Note_Select", p
));
PropertyBag.Add是MonoRail里的一个系统字典,这里测试很正常
但是,有以下不中足(我想到的)
这几日就在研究这个问题
寻遍网上,也没找到个解决方法只有
在NHibernate里执行存储过程 里面操作NHibernate的一点方法
于是动手写成了以下这个类
当然,只能保证测试可以通过,可能还有很多问题,要一个一个解决
1
namespace
ChAlumna.CastleExt
2
{
3
using Castle.ActiveRecord;
4 using Castle.ActiveRecord.Framework;
5 using Castle.ActiveRecord.Framework.Config;
6 using System;
7
8 using NHibernate;
9 using NHibernate.Cfg;
10 using NHibernate.Expression;
11 using System.Collections.Generic;
12 using System.Text;
13 using System.Data;
14 public class Execute
15
{
16 /// <summary>
17 /// 使用存储过程填充实体类,邹健,2008 1 24
18 /// </summary>
19 /// <typeparam name="T">实体类</typeparam>
20 /// <param name="spname">存储过程名</param>
21 /// <param name="idict">参数字典</param>
22
/// <returns>一个实体类Ilist</returns>
23 public static IList<T> GetList<T>(string spname, IDictionary<String, Object> idict) {
24 Type type = typeof(T);
25 //Chsword.DoDataBase d = new Chsword.DoDataBase();
26 StringBuilder sp = new StringBuilder();
27 foreach (string key in idict.Keys) {
28 sp.AppendFormat(":{0},", key);
29 }
30 if (sp.Length != 0)
31 sp.Length--;
32 string map = String.Format(@"<sql-query name='{0}'>
33<return class='{1}'/>
34exec {0} {2}
35</sql-query>",
36 spname,
37 type.Name,
38 sp.ToString()
39 );
40
41 Execute.CreateQueryMapping(type, map);//创建一个SQL-Query
42 ISessionFactoryHolder holder = ActiveRecordMediator.GetSessionFactoryHolder();
43
44 ISession session = holder.CreateSession(type);
45 IQuery query = session.GetNamedQuery(spname);
46
47 foreach (string key in idict.Keys) {
48 query = query.SetParameter(key, idict[key]);
49 }
50 return query.List<T>();
51 }
52 /// <summary>
53 /// 创建一个Sql-Query
54 /// </summary>
55 /// <param name="type">类型,做为Key</param>
56 /// <param name="xml">Sql-Query结点</param>
57 public static void CreateQueryMapping(Type type, string xml) {
58 ISessionFactoryHolder holder = ActiveRecordMediator.GetSessionFactoryHolder();
59 Configuration config = holder.GetConfiguration(holder.GetRootType(type));
60
61 // xml = ;
62 config.AddXmlString(
63 string.Format("<hibernate-mapping xmlns='{0}' assembly='{1}' namespace='{2}'>{3}</hibernate-mapping>",
64 Configuration.MappingSchemaXMLNS,
65 type.Assembly.FullName,
66 type.Namespace,//命名空间
67 xml//内容即<Sql-Query />
68 )
69 );
70 //return config.NamedSQLQueries.Count.ToString();
71 }
72}
73
}
使用方法如下
namespace
ChAlumna.CastleExt2
{3
using Castle.ActiveRecord;4
using Castle.ActiveRecord.Framework;5
using Castle.ActiveRecord.Framework.Config;6
using System;7
8
using NHibernate;9
using NHibernate.Cfg;10
using NHibernate.Expression;11
using System.Collections.Generic;12
using System.Text;13
using System.Data;14
public class Execute15
{16
/// <summary>17
/// 使用存储过程填充实体类,邹健,2008 1 2418
/// </summary>19
/// <typeparam name="T">实体类</typeparam>20
/// <param name="spname">存储过程名</param>21
/// <param name="idict">参数字典</param>22
/// <returns>一个实体类Ilist</returns>23
public static IList<T> GetList<T>(string spname, IDictionary<String, Object> idict) {24
Type type = typeof(T);25
//Chsword.DoDataBase d = new Chsword.DoDataBase();26
StringBuilder sp = new StringBuilder();27
foreach (string key in idict.Keys) {28
sp.AppendFormat(":{0},", key);29
}30
if (sp.Length != 0)31
sp.Length--;32
string map = String.Format(@"<sql-query name='{0}'>33
<return class='{1}'/>34
exec {0} {2}35
</sql-query>",36
spname,37
type.Name,38
sp.ToString()39
);40
41
Execute.CreateQueryMapping(type, map);//创建一个SQL-Query42
ISessionFactoryHolder holder = ActiveRecordMediator.GetSessionFactoryHolder();43
44
ISession session = holder.CreateSession(type);45
IQuery query = session.GetNamedQuery(spname);46
47
foreach (string key in idict.Keys) {48
query = query.SetParameter(key, idict[key]);49
}50
return query.List<T>();51
}52
/// <summary>53
/// 创建一个Sql-Query54
/// </summary>55
/// <param name="type">类型,做为Key</param>56
/// <param name="xml">Sql-Query结点</param>57
public static void CreateQueryMapping(Type type, string xml) {58
ISessionFactoryHolder holder = ActiveRecordMediator.GetSessionFactoryHolder();59
Configuration config = holder.GetConfiguration(holder.GetRootType(type));60
61
// xml = ;62
config.AddXmlString(63
string.Format("<hibernate-mapping xmlns='{0}' assembly='{1}' namespace='{2}'>{3}</hibernate-mapping>",64
Configuration.MappingSchemaXMLNS,65
type.Assembly.FullName,66
type.Namespace,//命名空间67
xml//内容即<Sql-Query />68
)69
);70
//return config.NamedSQLQueries.Count.ToString();71
}72
}73
}
IDictionary<string,object> p=new Dictionary<string,Object>();//参数
p.Add("userid", 10000);//参一
p.Add("id", 17002);//参二
p.Add("groupid", 51);//参三
PropertyBag.Add("list",
ChAlumna.CastleExt.Execute.GetList<LogToAccount>(//执行
"Note_Select", p
));
PropertyBag.Add是MonoRail里的一个系统字典,这里测试很正常
但是,有以下不中足(我想到的)
- 参数还不能实现out
- 不能用Many One来关联类,只能按存储过程中的字段来建一个新类,或将原实体类扩展
- 困了,想不出来了,有兴趣的同学自己试试吧
![[MySQL]存储过程](https://ucc.alicdn.com/3mfxe2r26qbaq/developer-article1632621/20241101/f9e47d1cdc9947d492b09f397a02c7b6.jpeg?x-oss-process=image/resize,h_160,m_lfit)
![2024Mysql And Redis基础与进阶操作系列（9）作者——LJS[含MySQL存储过程之局部、系统变量、参数传递、流程控制-判断/case具体详步骤；注意点及常见报错问题所对应的解决方法]](https://ucc.alicdn.com/pic/developer-ecology/56pfyutwi4tv4_c53df19098d2427391fe36eeb35f84c8.png?x-oss-process=image/resize,h_160,m_lfit)