public System.Data.DataSet GetDataSet(string tablename, Com.GainWinSoft.Common.SearchCondition condition, int pagesize, int pageindex)
{
DataSet ds = new DataSet();
IList <CTPmMsPagerNoAR> result = new List <CTPmMsPagerNoAR>();
ISession ss = holder.CreateSession(typeof(CTPmMsPagerNoARDaoOracleImp));
ITransaction tran = ss.BeginTransaction();
try
{
StringBuilder sb = new StringBuilder();
StringBuilder sql = CreateSelectSQL(condition);
sb.AppendLine("select b.*");
sb.AppendLine(" from (select a.*, rownum as rowIndex from (");
sb.AppendLine(sql.ToString());
sb.AppendLine(" ) a");
sb.AppendLine(" ) b");
sb.AppendLine(" where b.rowIndex >" + pageindex * pagesize);
sb.AppendLine(" and b.rowIndex <=" + (pageindex * pagesize + pagesize));
// ISQLQuery query = ss.CreateSQLQuery(sb.ToString());
//
// AddScalar(query);
//
//
// condition.SetParameterValue(query);
//
//
// result = query.SetResultTransformer(Transformers.AliasToBean<CTPmMsPagerNoAR>()).List<CTPmMsPagerNoAR>();
//
// //转换成datatable
// DataTable dt = DataTableUtils.ToDataTable(result);
// dt.TableName = tablename;
// ds.Tables.Add(dt);
IDbCommand command = ss.Connection.CreateCommand();
((OracleCommand)command).BindByName = true;
command.CommandText = sb.ToString();
command.CommandType = CommandType.Text;
condition.SetCommandParameterValue(command);
tran.Enlist(command);
IDataReader rdr = command.ExecuteReader();
DataTable dt = new DataTable();
dt.TableName = tablename;
dt.Load(rdr, LoadOption.Upsert);
DataTable newdt = new DataTable();
string[] columnlist = new string[] { "I_COMPANY_CD", "I_RATE_CLS", "I_CLS_DETAIL_DESC", "I_DL_CURR_CD"
, "I_CURR_DESC", "I_EFF_END_DATE", "I_RATE", "I_CNV_METHOD", "I_CNV_METHOD_DESC" };
foreach (string key in columnlist)
{
DataColumn col = new DataColumn();
col.ColumnName = key;
newdt.Columns.Add(col);
}
foreach (DataRow row in dt.Rows)
{
newdt.ImportRow(row);
}
newdt.TableName = tablename;
ds.Tables.Add(newdt);
command.Dispose();
// IDbCommand command = ss.Connection.CreateCommand();
// ((OracleCommand)command).BindByName = true;
//
// IDbDataParameter para = command.CreateParameter();
// para.ParameterName = "langCd";
// para.DbType = DbType.String;
// para.Value = "zh-CN";
// para.Direction = ParameterDirection.Input;
//
// command.Parameters.Add(para);
//
// para = command.CreateParameter();
// para.ParameterName = "companyCd";
// para.DbType = DbType.String;
// para.Value = "01";
// para.Direction = ParameterDirection.Input;
// command.Parameters.Add(para);
//
// para = command.CreateParameter();
// para.ParameterName = "IITEMENTRYCLS";
// para.DbType = DbType.String;
// para.Value = "00";
// para.Direction = ParameterDirection.Input;
// command.Parameters.Add(para);
//
// para = command.CreateParameter();
// para.ParameterName = "IFACCD";
// para.DbType = DbType.String;
// para.Value = "FAC01";
// para.Direction = ParameterDirection.Input;
// command.Parameters.Add(para);
//
// sb = new StringBuilder();
//
// sb.AppendLine(" select T_PM_MS.*, VTL_CLS_01.I_CLS_DETAIL_DESC,VTL_CLS_02.I_CLS_DETAIL_DESC from T_PM_MS ");
//
// sb.Append(" LEFT JOIN T_CLS_DETAIL_MS VTL_CLS_01 ON ( VTL_CLS_01.I_CLS_CD = '").Append(Constant.ITEM).AppendLine("' AND VTL_CLS_01.I_CLS_DETAIL_CD = T_PM_MS.I_ITEM_CLS ");//品目区分名
// //-------[2.0.0906.0801] 2009.06.08 Fsol)imatomi add str
// sb.Append(" AND VTL_CLS_01.I_LANGUAGE_CD = ").Append(":langCd").AppendLine(")");
// //-------[2.0.0906.0801] 2009.06.08 Fsol)imatomi add end
//
// sb.Append(" LEFT JOIN T_CLS_DETAIL_MS VTL_CLS_02 ON ( VTL_CLS_02.I_CLS_CD = '").Append(Constant.CTRL).AppendLine("' AND VTL_CLS_02.I_CLS_DETAIL_CD = T_PM_MS.I_CTRL_CLS ");//管理区分名
// //-------[2.0.0906.0801] 2009.06.08 Fsol)imatomi add str
// sb.Append(" AND VTL_CLS_02.I_LANGUAGE_CD = ").Append(":langCd").AppendLine(")");
// //-------[2.0.0906.0801] 2009.06.08 Fsol)imatomi add end
//
// sb.AppendLine(" where I_FAC_CD=:IFACCD");
//
// command.CommandText = sb.ToString();
//
// command.CommandType = CommandType.Text;
//
//
//
// tran.Enlist(command);
// IDataReader rdr = command.ExecuteReader();
// DataTable dt = new DataTable();
// dt.TableName = tablename;
// dt.Load(rdr, LoadOption.Upsert);
//
// ds.Tables.Add(dt);
// command.Dispose();
tran.Commit();
}
catch (Castle.ActiveRecord.Framework.ActiveRecordException ex)
{
tran.Rollback();
throw new ApplicationException(ex.Message, ex);
}
catch (DbException ex)
{
tran.Rollback();
throw new ApplicationException(ex.Message, ex);
}
finally
{
tran.Dispose();
holder.ReleaseSession(ss);
}
return(ds);
}
public int GetCount(string key, Com.GainWinSoft.Common.SearchCondition condition)
{
int intCount = 0;
ISession ss = holder.CreateSession(typeof(CTPmMsPagerNoARDaoOracleImp));
ITransaction tran = ss.BeginTransaction();
try
{
StringBuilder sb = new StringBuilder();
StringBuilder sql = CreateSelectSQL(condition);
sb.AppendLine("select count(*) as CNT from (");
sb.AppendLine(sql.ToString());
sb.AppendLine(" ) a");
// ISQLQuery q = ss.CreateSQLQuery(sb.ToString());
// q.AddScalar("CNT", NHibernateUtil.Int32);
//
// condition.SetParameterValue(q);
// intCount = (int)q.UniqueResult();
IDbCommand command = ss.Connection.CreateCommand();
((OracleCommand)command).BindByName = true;
command.CommandText = sb.ToString();
command.CommandType = CommandType.Text;
condition.SetCommandParameterValue(command);
tran.Enlist(command);
intCount = Convert.ToInt32(command.ExecuteScalar());
// IDataReader d = command.ExecuteReader();
// while (d.Read())
// {
// intCount = Convert.ToInt32(d.GetValue(0));
// }
command.Dispose();
tran.Commit();
}
catch (Castle.ActiveRecord.Framework.ActiveRecordException ex)
{
tran.Rollback();
throw new ApplicationException(ex.Message, ex);
}
catch (DbException ex)
{
tran.Rollback();
throw new ApplicationException(ex.Message, ex);
}
finally
{
tran.Dispose();
holder.ReleaseSession(ss);
}
return(intCount);
}
