private StringBuilder CreateSelectSQL(SearchCondition condition)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" SELECT ");
sql.AppendLine(" T_RATE_MS.I_RATE_CLS "); // レート区分
sql.AppendLine(" ,T_RATE_MS.I_COMPANY_CD ");
sql.AppendLine(" ,T_CLS_DETAIL_MS.I_CLS_DETAIL_DESC "); // レート区分名
sql.AppendLine(" ,T_RATE_MS.I_DL_CURR_CD "); // 取引通貨
sql.AppendLine(" ,T_CURR_MS.I_CURR_DESC "); // 通貨名
sql.AppendLine(" ,DECODE(T_RATE_MS.I_EFF_END_DATE,99999999,99991231,T_RATE_MS.I_EFF_END_DATE) I_EFF_END_DATE "); // 有効期限
sql.AppendLine(" ,T_RATE_MS.I_RATE "); // レート
sql.AppendLine(" ,T_RATE_MS.I_CNV_METHOD "); // 換算方式
sql.AppendLine(" ,'' as I_CNV_METHOD_DESC"); // 換算方式
sql.AppendLine(" ,T_RATE_MS.I_UPD_TIMESTAMP "); // 更新タイプスタンプ
sql.AppendLine(" ,T_RATE_MS.I_EFF_END_DATE I_EFF_END_DATE_NUM "); //有効期限(データ)
sql.AppendLine(" FROM ");
sql.AppendLine(" T_RATE_MS LEFT JOIN T_CURR_MS ON T_RATE_MS.I_DL_CURR_CD = T_CURR_MS.I_CURR_CD");
sql.Append(" AND T_RATE_MS.I_COMPANY_CD = ").Append(":I_COMPANY_CD");
sql.AppendLine(" LEFT JOIN T_CLS_DETAIL_MS ON T_RATE_MS.I_RATE_CLS = T_CLS_DETAIL_MS.I_CLS_DETAIL_CD");
sql.AppendLine(" AND T_CLS_DETAIL_MS.I_CLS_CD = ").AppendLine(CommonUtil.AddSquote(Constant.RATE));
sql.AppendLine(" AND T_CLS_DETAIL_MS.I_LANGUAGE_CD = ").Append(":I_LANGUAGE_CD");
sql.AppendLine(condition.BuildParameterConditionSql(true));
sql.AppendLine(" ORDER BY T_RATE_MS.I_RATE_CLS, T_RATE_MS.I_DL_CURR_CD, T_RATE_MS.I_EFF_END_DATE ");
return(sql);
}
public int GetCount(string key, SearchCondition condition)
{
int intCount = 0;
ISession ss = holder.CreateSession(typeof(CFunctionPagerNoARDaoOracleImp));
ITransaction tran = ss.BeginTransaction();
try
{
string query = @"select count(FUNCTIONID) as CNT from M_FUNCTION
" + condition.BuildParameterConditionSql(true);
ISQLQuery q = ss.CreateSQLQuery(query);
q.AddScalar("CNT", NHibernateUtil.Int32);
foreach (DictionaryEntry de in condition.ConditionTable)
{
SearchInfo searchInfo = (SearchInfo)de.Value;
q.SetParameter(string.Format("{0}", searchInfo.FieldName), searchInfo.FieldValue);
}
intCount = (int)q.UniqueResult();
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);
}
public DataSet GetDataSet(string tablename, SearchCondition condition, int pagesize, int pageindex)
{
DataSet ds = new DataSet();
IList <CFunctionPagerNoAR> result = new List <CFunctionPagerNoAR>();
ISession ss = holder.CreateSession(typeof(CFunctionPagerNoARDaoOracleImp));
ITransaction tran = ss.BeginTransaction();
try
{
//IDbCommand command = ss.Connection.CreateCommand();
//command.CommandText = @"SELECT * FROM (" + sql + ")";
//foreach (SqlParameter para in paralist)
//{
// OracleParameter op = new OracleParameter(para.ParameterName, para.Value);
// command.Parameters.Add((OracleParameter)op);
//}
//command.CommandType = CommandType.Text;
//tran.Enlist(command);
//IDataReader rdr = command.ExecuteReader();
//command.Dispose();
//DataTable dt = new DataTable();
//dt.TableName = tablename;
//dt.Load(rdr, LoadOption.Upsert);
//ds.Tables.Add(dt);
//result = (IList<MFunctioncatalog>)FindAll(typeof(MFunctioncatalog));
//select b.* from
//(select a.*, rownum as rowIndex from(select * from M_FUNCTION Where (1=1) AND LANGID = 'zh-CN') a) b
//where b.rowIndex > 5 and b.rowIndex <= 10
StringBuilder sb = new StringBuilder();
sb.Append("select b.LANGID grid_langid,");
sb.Append("b.FUNCTIONID grid_functionid,");
sb.Append("b.FUNCTIONNAME grid_functionname,");
sb.Append("b.FUNCTIONPATH grid_functionpath,");
sb.Append("b.CATALOGID grid_catalogid,");
sb.Append("b.FUNCTIONINDEX grid_functionindex,");
sb.Append("b.FUNCTIONIMAGE grid_functionimage");
sb.Append(" from (select a.*, rownum as rowIndex from (");
sb.Append("select * from M_FUNCTION ");
sb.Append(condition.BuildParameterConditionSql(true));
sb.Append(" order by catalogid,functionid,functionindex ");
sb.Append(" ) a");
sb.Append(" ) b");
sb.Append(" where b.rowIndex >" + pageindex * pagesize);
sb.Append(" and b.rowIndex <=" + (pageindex * pagesize + pagesize));
string query = sb.ToString();
ISQLQuery q = ss.CreateSQLQuery(query);
q.AddScalar("grid_langid", NHibernateUtil.String);
q.AddScalar("grid_functionid", NHibernateUtil.Int32);
q.AddScalar("grid_functionname", NHibernateUtil.String);
q.AddScalar("grid_functionpath", NHibernateUtil.String);
q.AddScalar("grid_catalogid", NHibernateUtil.Int32);
q.AddScalar("grid_functionindex", NHibernateUtil.Int32);
q.AddScalar("grid_functionimage", NHibernateUtil.String);
foreach (DictionaryEntry de in condition.ConditionTable)
{
SearchInfo searchInfo = (SearchInfo)de.Value;
q.SetParameter(string.Format("{0}", searchInfo.FieldName), searchInfo.FieldValue);
}
result = q.SetResultTransformer(Transformers.AliasToBean <CFunctionPagerNoAR>()).List <CFunctionPagerNoAR>();
//转换成datatable
DataTable dt = DataTableUtils.ToDataTable(result);
dt.TableName = tablename;
ds.Tables.Add(dt);
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);
}
