ORM(NHibernate)使用之三

[zheteng_003]

EntityControl.cs类：

第一部分（通过事物执行增、删、改）：
namespace Finance.Common
{
public class EntityControl
{
private static EntityControl entity;
private string _AssemblyName;
static readonly object padlock = new object();

public static EntityControl CreateEntityControl(string AssemblyName)
{
if (entity == null)
{
lock (padlock)
{
if (entity == null)
{
entity = new EntityControl();
entity._AssemblyName = AssemblyName;
}
}
}
return entity;
}

public void AddEntity(Object entity)
{
ISession session = SessionFactory.OpenSession(_AssemblyName);
ITransaction transaction = session.BeginTransaction();
try
{
session.Save(entity);
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
session.Close();
}
}

public void UpdateEntity(Object entity, Object key)
{
ISession session = SessionFactory.OpenSession(_AssemblyName);
ITransaction transaction = session.BeginTransaction();
try
{
session.Update(entity, key);
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
session.Close();
}
}

public void DeleteEntity(object entity)
{
ISession session = SessionFactory.OpenSession(_AssemblyName);
ITransaction transaction = session.BeginTransaction();

try
{
session.Delete(entity);
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
session.Close();
}
}
第二部分，根据传入的条件执行查询
public Object GetEntity(string table, string idFieldName, int id)
{
Object obj;
string query = "From " + table + " Where " + idFieldName + " = " + id + "";
ISession session = SessionFactory.OpenSession(_AssemblyName);

obj = session.CreateQuery(query).UniqueResult();

session.Close();

return obj;
}


public object GetEntityByWhere(string table, string where)
{
Object obj=null;
string query = "From " + table;
if (!String.IsNullOrEmpty(where) && where != "")
{
query += " where "+where;
}
ISession session = SessionFactory.OpenSession(_AssemblyName);
IList list=session.CreateQuery(query).List();
if(list.Count>0)
{
obj =list[0];
问题一：“list”对象指什么？？？list[0]怎么是obj对象呢？？？
}
session.Close();
return obj;
}

第三部分，分页
/// <summary>
/// 单表分页一
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="table"></param>
/// <param name="where"></param>
/// <param name="orderBy"></param>
/// <returns></returns>
问题三：什么叫做“单表分页”？？？怎样实现的呢？？？
public IList GetEntitesPage(int pageIndex, int pageSize, string table, string where, string orderBy)
{
string query = "From " + table;
if (!String.IsNullOrEmpty(where) && where != "")
{
query += " Where " + where;
}
if (!String.IsNullOrEmpty(orderBy) && orderBy != "")
{
query += " Order By " + orderBy;
}

if (pageIndex < 1)
{
pageIndex = 1;
}
if (pageSize < 1)
{
pageSize = 1;
}

IList lst;

ISession session = SessionFactory.OpenSession(_AssemblyName);

lst = session.CreateQuery(query).SetFirstResult(pageSize * (pageIndex - 1)).SetMaxResults(pageSize).List();

session.Close();

return lst;
}
public string GetPageSet(int pageIndex, int pageSize, string tableName, string where, string urlFormat, int mode)
{
ISession session = SessionFactory.OpenSession(_AssemblyName);
int recordCount = DirectRun.GetRecordCount(session, tableName, where);
session.Close();
return SplitPage.GetPageSet(pageIndex, pageSize, recordCount, urlFormat, mode);
}

问题四：“多表分页”指什么？？？又是怎样实现的呢？？？
/// <summary>
/// 多表分页二
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="hql"></param>
/// <param name="where"></param>
/// <param name="urlFormat"></param>
/// <param name="mode"></param>
/// <returns></returns>
public string GetManyPageSet( string query, string where,int pageIndex, int pageSize, string urlFormat, int mode)
{
ISession session = SessionFactory.OpenSession(_AssemblyName);
if (!String.IsNullOrEmpty(where) && where != "")
{
query += " Where " + where;
}
IQuery q = SessionFactory.OpenSession(_AssemblyName).CreateQuery(query);
int recordCount = q.List().Count;
session.Close();

return SplitPage.GetPageSet(pageIndex, pageSize, recordCount, urlFormat, mode);
}
public IList GetManyTablePage(string query,string where, string orderBy,int pageIndex, int pageSize, string table)
{

if (!String.IsNullOrEmpty(where) && where != "")
{
query += " Where " + where;
}
if (!String.IsNullOrEmpty(orderBy) && orderBy != "")
{
query += " Order By " + orderBy;
}

if (pageIndex < 1)
{
pageIndex = 1;
}
if (pageSize < 1)
{
pageSize = 1;
}

IList lst;

ISession session = SessionFactory.OpenSession(_AssemblyName);

lst = session.CreateQuery(query).SetFirstResult(pageSize * (pageIndex - 1)).SetMaxResults(pageSize).List();

session.Close();

return lst;
}

[zheteng_003]

第四部分（使用了using）：
public IList GetDataByQuery(string query)
{

IList lst;

ISession session = SessionFactory.OpenSession(_AssemblyName);

lst = session.CreateQuery(query).List();

session.Close();

return lst;
}
public IList GetEntities(string table, string where, string orderBy)
{
string query = "from " + table;
if (!String.IsNullOrEmpty(where) && where != "")
{
query += " where " + where;
}
if (!String.IsNullOrEmpty(orderBy) && orderBy != "")
{
query += " order By " + orderBy;
}

IList lst;
ISession session = SessionFactory.OpenSession(_AssemblyName);
lst = session.CreateQuery(query).List();
session.Close();
return lst;
}
public object GetObjectById(Type type, int id)
{
using (ISession session = SessionFactory.OpenSession(_AssemblyName))
{
return session.Load(type, id);
}
}

public object GetObjectById(Type type, int id, bool allowNull)
{
if (!allowNull)
{
return GetObjectById(type, id);
}
else
{
using (ISession session = SessionFactory.OpenSession(_AssemblyName))
{
return session.Get(type, id);
}
}
}

public T GetObjectById<T>(int id)
{
using (ISession session = SessionFactory.OpenSession(_AssemblyName))
{
return session.Get<T>(id);
}
}

public object GetObjectByDescription(Type type, string propertyName, string description)
{
object obj;
using (ISession session = SessionFactory.OpenSession(_AssemblyName))
{
ICriteria crit = session.CreateCriteria(type);
crit.Add(Expression.Eq(propertyName, description));
obj = crit.UniqueResult();
session.Close();
return obj;
问题五：这是什么实现方式？？？怎样实现的？？？
}
}
public object GetObjectByDescription(Type type, Hashtable ht)
{
object obj;
using (ISession session = SessionFactory.OpenSession(_AssemblyName))
{
ICriteria crit = session.CreateCriteria(type);
IDictionaryEnumerator e=ht.GetEnumerator();
while (e.MoveNext())
{
crit.Add(Expression.Eq(e.Key.ToString (),e.Value));
}
obj = crit.UniqueResult();
session.Close();
return obj;
}
}
public IList GetAll(Type type)
{
return GetAll(type, null);
}

public IList GetAll(Type type, params string[] sortProperties)
{
using (ISession session = SessionFactory.OpenSession(_AssemblyName))
{

ICriteria crit = session.CreateCriteria(type);
if (sortProperties != null)
{
foreach (string sortProperty in sortProperties)
{
crit.AddOrder(Order.Asc(sortProperty));
}
}
return crit.List();
}
}

/// <summary>
/// Get all objects of T.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public IList<T> GetAll<T>()
{
return GetAll<T>(null);
}

/// <summary>
/// Get all objects of T.
/// </summary>
/// <param name="sortProperties"></param>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public IList<T> GetAll<T>(params string[] sortProperties)
{
using (ISession session = SessionFactory.OpenSession(_AssemblyName))
{

ICriteria crit = session.CreateCriteria(typeof(T));
if (sortProperties != null)
{
foreach (string sortProperty in sortProperties)
{
crit.AddOrder(Order.Asc(sortProperty));
}
}
return crit.List<T>();
}
}

/// <summary>
/// Get all objects of T that match the given criteria.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="criteria">NHibernate DetachedCriteria instance.</param>
/// <returns></returns>
/// <remarks>
/// Be careful to not use this one from the UI layer beacuse it ties the UI to NHibernate.
/// </remarks>
public IList<T> GetAllByCriteria<T>(DetachedCriteria criteria)
{
using (ISession session = SessionFactory.OpenSession(_AssemblyName))
{
ICriteria crit = criteria.GetExecutableCriteria(session);
return crit.List<T>();
}
}

/// <summary>
/// Get all objects of T for the given id's.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="ids"></param>
/// <returns></returns>
public IList<T> GetByIds<T>(int[] ids)
{
ISession session = SessionFactory.OpenSession(_AssemblyName);
ICriteria crit = session.CreateCriteria(typeof(T))
.Add(Expression.In("Id", ids));
return crit.List<T>();
}

public int ExecuteNonQuery(string sqlString)
{
ISession session = SessionFactory.OpenSession(_AssemblyName);
int num = DirectRun.ExecuteNonQuery(session, sqlString);
session.Close();
return num;
}

}
}

[zhulong1111]

N长的代码 。。。。头昏中。。

[种草德鲁伊]

我来帮你接分吧，别浪费了。

[zhulong1111]

IList list=session.CreateQuery(query).List();
if(list.Count>0)
{
obj =list[0];
问题一：“list”对象指什么？？？list[0]怎么是obj对象呢？？？
}
你这里没有list<T> 那你取的值肯定为object啊

[zheteng_003]

[Quote=引用 4 楼 zhulong1111 的回复:]
IList list=session.CreateQuery(query).List();
if(list.Count>0)
{
obj =list[0];
问题一：“list”对象指什么？？？list[0]怎么是obj对象呢？？？
}
你这里没有list<T> 那你取的值肯定为object啊
[/Quote]

来个人解答下呗。。。

[小豪]

好长~不过不知道能不能看懂~

[子夜__]

ICriteria crit = session.CreateCriteria(type);
crit.Add(Expression.Eq(propertyName, description));
obj = crit.UniqueResult();
session.Close();
return obj;

QBC查询。

[sswp7]

好长啊，接分了。。。

[sswp7]

顶个。。。

[Lisliefor]

问题一：“list”对象指什么？？？list[0]怎么是obj对象呢？？？
List是一种集合类型，它一般用于未知元素个数，未知元素类型的场合，它有默认的长度，当元素个数长度超过默认长度，会再次分配默认长度的内存空间。
list里放的任何对象都是object类型，如果你在声明的时候，没有指定类型，那么在使用时，就需要做一个显示的类型转换。
加入，list里放的是一些string。

List list = new List();
list.Add("111");
list.Add("222");
// 循环时
foreach(object obj in list){
    string temp = obj as string;
    // 可以使用temp了
}

// 或者你在声明的时候，指定list存储元素的类型，这是推荐的做法
List<string> list = new List<string>();
// 直接可以这样写了
foreach(string temp in list){

}

问题三：什么叫做“单表分页”？？？怎样实现的呢？？？
单表分页比较简单，一般单表分页需要这个表有一个惟一自增的主键（如果是int类型最好），下面就是一个单表分页的例子：

    -- 每页记录条数
    declare int @pageSize   
    set @pageSize=10
    -- 页码
    declare int @pageIndex
    set @pageIndex=1
    declare varchar(1000) @sql 
    if @pageIndex-1=0
    begin
         set @sql = 'select  top ' +  cast(@pageSize as varchar) + ' from tableA order by aid'
     end
    else
    begin
        set @sql='select top ' + cast(@pageSize as varchar) + '   * from tableA where aid not in ('
        set @sql=@sql+ '    select top' + cast(@pageSize*(@pageIndex-1) as varchar) +' aid from tableA
        set @sql=@sql+ '          order by aid) order by aid'
    end
    exec (@sql)

问题四：“多表分页”指什么？？？又是怎样实现的呢？？？
到公司再说。

[amandag]

教学贴？

[一片冰心在玉壶]

哥们 去下载个官方文档，这些都有。。

[Lisliefor]

问题四：“多表分页”指什么？？？又是怎样实现的呢？？？
查询字段分布在多个表格中，需要join多个表格，分页稍微复杂一点，需要使用ROW_NUMBER()计算行数，下面是一个查询示例：

SELECT   * FROM (SELECT ROW_NUMBER() OVER (Order By a.aid desc) AS pos, *  FROM TableA as a inner join TableB as b on a.aid=b.aid where a.aid=11) AS sp WHERE pos BETWEEN 1 AND 10


-- 可以将上面的语句写成一个支持多表分页的存储过程
------------------------------------                
--用途：                 
--项目名称：                
--说明：                
--时间：        
------------------------------------                
CREATE procedure [dbo].[SP_GetPage]                
 @tblName   varchar(1000),       -- 表名                
 @strGetFields varchar(1000) = '*',  -- 需要返回的列                
 @strOrder varchar(100)='',      -- 排序的字段名                
 @PageSize   int = 10,          -- 页尺寸                
 @PageIndex  int = 1,           -- 页码                
 @strWhere  varchar(1500) = '',  -- 查询条件 (注意: 不要加 where)                
 @sort varchar(5) ='asc',                
 @RowCount int output                  
                 
AS                
declare @strSQL   varchar(5000)                
declare @DynamicSQL nvarchar(4000)                
if @strWhere !=''                
  set @strWhere=' where '+@strWhere                
SET @DynamicSQL = N'select @RowCount = count(*) from '+@tblName+@strWhere                
                 
  print @RowCount        
  exec sp_executesql @DynamicSQL, N'@RowCount int OUTPUT',@RowCount OUTPUT                
  print @RowCount        
          
          
set @strSQL='SELECT   * FROM (SELECT ROW_NUMBER() OVER (Order By '+@strOrder+' '+@sort+') AS pos,'+@strGetFields+' FROM '+@tblName+@strWhere+') AS sp WHERE pos BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize)                    
        
exec (@strSQL) 

-- 调用方法
declare @ct int
exec [XH_ShowPage] 'TExamCompany as a inner join TCompany as b on a.fCompanyID=b.fCompanyID',
' * ','a.aid', 10, 1, 'a.fCompanyID=11', 'desc',  @ct output

[Lisliefor]

[Quote=引用楼主 zheteng_003 的回复:]
using (ISession session = SessionFactory.OpenSession(_AssemblyName))
{
ICriteria crit = session.CreateCriteria(type);
crit.Add(Expression.Eq(propertyName, description));
obj = crit.UniqueResult();
session.Close();
return obj;
问题五：这是什么实现方式？？？怎样实现的？？？
}
[/Quote]

参考C#中using的用法

简化的try...catch的写法。

[jeje]

好长啊

[sswp7]

[Quote=引用 14 楼 lisliefor 的回复:]
引用楼主 zheteng_003 的回复:
using (ISession session = SessionFactory.OpenSession(_AssemblyName))
{
ICriteria crit = session.CreateCriteria(type);
crit.Add(Expression.Eq(propertyName, description));
obj……
[/Quote]

好友耐心，++++++++++

[失去乐山贼]

[Quote=引用 11 楼 amandag 的回复:]

教学贴？
[/Quote]
+++