/// <summary>
/// 刪除物件
/// </summary>
/// <param name="id">主鍵</param>
public void ExecuteDeleteById <T>(Object id)
{
//開啟連結
ISession session = HibernateHelper.GetCurrentSession();
try
{
HibernateHelper.BeginTransaction();
T temp = session.Load <T>(id);
session.Delete(temp);
HibernateHelper.CommitTransaction();
}
//例外
catch (Exception e)
{
HibernateHelper.RollbackTransaction();
log.Error("刪除資料" + typeof(T) + "主鍵為：" + id + "失敗" + e.Message);
throw e;
}
finally
{
//關閉
HibernateHelper.CloseSession();
}
}
//2010/06/28 holer end
public Hashtable SelectHTNativeSQL(string sql)
{
Hashtable ht = new Hashtable();
//開啟連結
ISession session = HibernateHelper.GetCurrentSession();
IDbConnection conn = session.Connection;
IDbCommand cmd = conn.CreateCommand();
try
{
//查詢SQL
cmd.CommandText = sql;
cmd.Connection = conn;
//查詢結果
IDataReader result = cmd.ExecuteReader();
while (result.Read())
{
ht.Add(result.GetString(0).Trim(), result.GetString(1).Trim());
}
//關閉result
if (result != null)
{
result.Close();
}
}
finally
{
//關閉連結
HibernateHelper.CloseSession();
}
return(ht);
}
// <summary>
/// 取得資料庫資料(本地sql)
/// </summary>
/// <param name="sql">資料庫查詢SQL(本地sql)</param>
/// <returns>int 操作訊息(>0成功，否則失敗)</returns>
public int insertNativeSQLGetKey(string sql)
{
int recode = -1;
//開啟連結
ISession session = HibernateHelper.GetCurrentSession();
IDbConnection conn = session.Connection;
IDbCommand cmd = conn.CreateCommand();
try
{
//查詢SQL
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
cmd.CommandText = "select LAST_INSERT_ID()";
cmd.CommandType = CommandType.Text;
recode = int.Parse(cmd.ExecuteScalar().ToString());
}
finally
{
//關閉連結
HibernateHelper.CloseSession();
}
//操作訊息
return(recode);
}
//2009/07/30 jackter end
/// <summary>
/// 不分頁查詢(本地sql)
/// </summary>
/// <param name="sql">sql</param>
/// <returns>結果集</returns>
public IList <T> SelectNativeSQL <T>(string sql)
{
//開啟連結
ISession session = HibernateHelper.GetCurrentSession();
try
{
//操作訊息
ISQLQuery sqlQuery = session.CreateSQLQuery(sql);
//已定義的實體類
if (typeof(T).FullName.StartsWith("Com.Everunion"))
{
return(sqlQuery.SetResultTransformer(Transformers.AliasToBean(typeof(T)))
.List <T>());
}
//其它,如(T=object[])
else
{
return(sqlQuery.List <T>());
}
}
//拋出例外
catch (Exception e)
{
log.Error("SelectNativeSQL.sql:" + sql);
//輸出錯誤
log.Error("SelectNativeSQL.error:" + e.ToString());
throw e;
}
finally
{
//關閉
HibernateHelper.CloseSession();
}
}
/// <summary>
/// 分頁查詢
/// </summary>
/// <param name="sql">sql</param>
/// <returns>結果集</returns>
public IList <T> SelectPage <T>(string sql)
{
//開啟連結
ISession session = HibernateHelper.GetCurrentSession();
try
{
PageUtil pu = new PageUtil();
IQuery query = pu.GetObjectQuery(session, sql, false);
//操作訊息
return(query.List <T>());
}
//拋出例外
catch (Exception e)
{
log.Error("SelectPage.sql:" + sql);
//輸出錯誤
log.Error("SelectPage.error:" + e.ToString());
throw e;
}
finally
{
//關閉
HibernateHelper.CloseSession();
}
}
/// <summary>
/// 新增
/// </summary>
/// <param name="newo">物件</param>
/// <returns>主鍵</returns>
public Object ExecuteInsert(Object newo)
{
ISession session = HibernateHelper.GetCurrentSession();
ITransaction tx = HibernateHelper.BeginTransaction();
try
{
//儲存
Object o = session.Save(newo);
HibernateHelper.CommitTransaction();
return(o);
}
//例外
catch
{
HibernateHelper.RollbackTransaction();
log.Error("新增" + newo + "失敗！");
throw;
}
//關閉
finally
{
HibernateHelper.CloseSession();
}
}
//2009/06/25 dennis add 加入ExecuteAllDelete方法
/// <summary>
/// 根據條件刪除資料
/// </summary>
/// <param name="sql">查詢sql(from test a where a.Id='1' )</param>
public void ExecuteAllDelete(string sql)
{
//開啟連結
ISession session = HibernateHelper.GetCurrentSession();
try
{
HibernateHelper.BeginTransaction();
//刪除
session.Delete(sql);
HibernateHelper.CommitTransaction();
}
//例外
catch (Exception e)
{
HibernateHelper.RollbackTransaction();
log.Error("刪除資料" + sql + "失敗" + e.Message);
throw e;
}
finally
{
//關閉
HibernateHelper.CloseSession();
}
}
/// <summary>
/// 取得新增編號
/// </summary>
/// <typeparam name="T">實體類</typeparam>
/// <param name="IdName">編號屬性名</param>
/// <param name="createdateName">建檔日期屬性名</param>
/// <param name="addHQL">額外HQL</param>
/// <returns>最新的編號</returns>
private static string NewInsertId <T>(string IdPro, string createdatePro, string addHQL)
{
//開啟連結
ISession session = HibernateHelper.GetCurrentSession();
string recode = DateTime.Now.ToString("yyMMdd");
try
{
StringBuilder hql = new StringBuilder();
//hql
hql.Append(" select max(a.").Append(IdPro).Append(") from ").Append(typeof(T).Name)
.Append(" a where a.").Append(createdatePro).Append("=:nowdate ");
//補充倏件
if (!String.IsNullOrEmpty(addHQL))
{
hql.Append("and " + addHQL.Trim());
}
//取得最大序號
string key = session.CreateQuery(hql.ToString())
.SetParameter("nowdate", DateTime.Now.ToString(@"yyyy\/MM\/dd"))
.SetMaxResults(1)
.UniqueResult <string>();
//有單
if (!string.IsNullOrEmpty(key))
{
//加一
string newNumStr = StringUtil.ToStr(StringUtil.ToInt(key.Substring(6)) + 1);
//中間補零返回
recode = recode.PadRight(key.Length - newNumStr.Length, '0') + newNumStr;
}
//無單
else
{
recode += "0001";
}
}
//拋出例外
catch (Exception e)
{
log.Error("CommUtil.cs取最大編號失敗! 可能是連結中斷! " + e.ToString());
if (recode.Length == 6)
{
recode += "0001";
}
}
finally
{
//關閉
HibernateHelper.CloseSession();
}
//操作訊息
return(recode);
}
/// <summary>
/// 取得T物件
/// </summary>
/// <param name="oid">主鍵</param>
public T SelectByKey <T>(Object oid)
{
//開啟連結
ISession session = HibernateHelper.GetCurrentSession();
try
{
return(session.Get <T>(oid));
}
//例外
catch (Exception e)
{
log.Error("SelectByKey.error " + e.Message);
log.Error("SelectByKey.oid " + oid);
throw e;
}
finally
{
//關閉
HibernateHelper.CloseSession();
}
}
//2010/06/28 holer add 取得資料庫資料(本地sql) start
/// <summary>
/// 取得資料庫資料(本地sql)
/// </summary>
/// <param name="sql">資料庫查詢SQL(本地sql)</param>
/// <returns>結果集</returns>
public IList <Hashtable> SelectNativeSQL(string sql)
{
IList <Hashtable> list = new List <Hashtable>();
//開啟連結
ISession session = HibernateHelper.GetCurrentSession();
IDbConnection conn = session.Connection;
IDbCommand cmd = conn.CreateCommand();
try
{
//查詢SQL
cmd.CommandText = sql;
cmd.Connection = conn;
//查詢結果
IDataReader result = cmd.ExecuteReader();
while (result.Read())
{
Hashtable ht = new Hashtable();
for (int i = 0; i < result.FieldCount; i++)
{
ht.Add(result.GetName(i), result.GetValue(i).ToString());
}
//加入資料
list.Add(ht);
}
//關閉result
if (result != null)
{
result.Close();
}
}
finally
{
//關閉連結
HibernateHelper.CloseSession();
}
return(list);
}
/// <summary>
/// 更新物件
/// </summary>
/// <param name="o">待更新的物件</param>
public void ExecuteUpdate(Object o)
{
ISession session = HibernateHelper.GetCurrentSession();
ITransaction tr = HibernateHelper.BeginTransaction();
try
{
session.Merge(o);
HibernateHelper.CommitTransaction();
}
//例外
catch
{
HibernateHelper.RollbackTransaction();
log.Error("更新" + o + "失敗！");
throw;
}
//關閉連結
finally
{
HibernateHelper.CloseSession();
}
}
// <summary>
/// 取得資料庫資料(本地sql)
/// </summary>
/// <param name="sql">資料庫查詢SQL(本地sql)</param>
/// <returns>int 操作訊息(>0成功，否則失敗)</returns>
public int updateNativeSQL(string sql)
{
int recode = -1;
//開啟連結
ISession session = HibernateHelper.GetCurrentSession();
IDbConnection conn = session.Connection;
IDbCommand cmd = conn.CreateCommand();
try
{
//查詢SQL
cmd.CommandText = sql;
cmd.Connection = conn;
recode = cmd.ExecuteNonQuery();
}
finally
{
//關閉連結
HibernateHelper.CloseSession();
}
//操作訊息
return(recode);
}
/// <summary>
/// 查詢(不分頁)
/// </summary>
/// <param name="hql">hql</param>
/// <returns>結果集</returns>
public IList <T> Select <T>(string hql)
{
//開啟連結
ISession session = HibernateHelper.GetCurrentSession();
try
{
//操作訊息
return(session.CreateQuery(hql).List <T>());
}
//拋出例外
catch (Exception e)
{
log.Error("Select.hql:" + hql);
//輸出錯誤
log.Error("Select.error:" + e.ToString());
throw e;
}
finally
{
//關閉
HibernateHelper.CloseSession();
}
}
//2009/06/25 dennis end
//2009/07/01 dennis add 加入SelectPageObjct方法
/// <summary>
/// 單一查詢
/// </summary>
/// <param name="hql">hql</param>
/// <returns>結果集</returns>
public T SelectUnique <T>(string hql)
{
//開啟連結
ISession session = HibernateHelper.GetCurrentSession();
try
{
//操作訊息
return(session.CreateQuery(hql).SetMaxResults(1).UniqueResult <T>());
}
//拋出例外
catch (Exception e)
{
log.Error("GetUniqueObjct.sql:" + hql);
//輸出錯誤
log.Error("GetUniqueObjct.error:" + e.ToString());
throw e;
}
finally
{
//關閉
HibernateHelper.CloseSession();
}
}
