/// <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(); } }