using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Text;
|
using Model;
|
using Common;
|
using Oracle.ManagedDataAccess.Client;
|
using System.Data.Common;
|
|
namespace Dao
|
{
|
/// <summary>
|
/// 字典数据操作
|
/// </summary>
|
public class SysDictDataDao : IDisposable
|
{
|
public void Dispose()
|
{
|
}
|
|
private static SysDictDataDao _instance;
|
public static SysDictDataDao Instance
|
{
|
get {
|
if (_instance == null)
|
{
|
_instance = new SysDictDataDao();
|
}
|
return _instance;
|
}
|
}
|
|
|
public List<SysDictData> LoadInfoList(string dictLabel, string dictType, int pageSize, int PageIndex, out int recordcount)
|
{
|
|
List<SysDictData> list = new List<SysDictData>();
|
recordcount = 0;
|
try
|
{
|
StringBuilder builder = new StringBuilder();
|
builder.Append("from SYS_DICT_DATA where 1=1 AND DEL_FLAG=0 ");
|
if (!string.IsNullOrEmpty(dictLabel))
|
{
|
builder.Append(" and DICT_LABEL like '%" + dictLabel + "%'");
|
}
|
if (!string.IsNullOrEmpty(dictType))
|
{
|
builder.Append(" and DICT_TYPE = '" + dictType + "'");
|
}
|
using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + builder.ToString(), OracleHelper.Connection))
|
{
|
while (reader.Read())
|
{
|
recordcount = this.ReadCount(reader);
|
}
|
}
|
using (OracleDataReader reader2 = OracleHelper.ExecuteReader(PubConstant.doOracleSql(PageIndex, pageSize, recordcount, "select * " + builder.ToString()).ToString() + " order by DICT_TYPE, DICT_SORT ", OracleHelper.Connection ))
|
{
|
while (reader2.Read())
|
{
|
SysDictData o = new SysDictData();
|
if (this.ReadInfo(reader2, o))
|
{
|
list.Add(o);
|
}
|
}
|
}
|
}
|
catch (Exception exception)
|
{
|
LogHelper.Error(exception);
|
return list;
|
}
|
return list;
|
|
}
|
|
//根据条件获取数据列表
|
public List<SysDictData> getAllList(SysDictData bean)
|
{
|
|
List<SysDictData> list = new List<SysDictData>();
|
try
|
{
|
StringBuilder builder = new StringBuilder();
|
builder.Append("from SYS_DICT_DATA where 1=1 AND DEL_FLAG=0 ");
|
if (!string.IsNullOrEmpty(bean.DictLabel))
|
{
|
builder.Append(" and DICT_LABEL like '%" + bean.DictLabel + "%'");
|
}
|
|
if (!string.IsNullOrEmpty(bean.DictValue))
|
{
|
builder.Append(" and DICT_VALUE like '%" + bean.DictValue + "%'");
|
}
|
|
if (!string.IsNullOrEmpty(bean.DictType))
|
{
|
builder.Append(" and DICT_TYPE = '" + bean.DictType + "'");
|
}
|
|
if (bean.ParanSource == -1)
|
{
|
builder.Append(" AND (PARAN_SOURCE != " + bean.ParanSource + " OR PARAN_SOURCE IS NULL) ");
|
}
|
else
|
{
|
builder.Append(" AND PARAN_SOURCE = " + bean.ParanSource + " ");
|
}
|
|
if (bean.Status == -1)
|
{
|
builder.Append(" AND (STATUS != " + bean.Status + " OR STATUS IS NULL) ");
|
}
|
else
|
{
|
builder.Append(" AND STATUS = " + bean.Status + " ");
|
}
|
|
using (OracleDataReader reader = OracleHelper.ExecuteReader("select * " + builder.ToString() + " order by CREATE_TIME ", OracleHelper.Connection))
|
{
|
while (reader.Read())
|
{
|
SysDictData o = new SysDictData();
|
if (this.ReadInfo(reader, o))
|
{
|
list.Add(o);
|
}
|
}
|
}
|
}
|
catch (Exception exception)
|
{
|
LogHelper.Error(exception);
|
return list;
|
}
|
return list;
|
|
}
|
|
//添加信息
|
public bool Add(SysDictData o)
|
{
|
StringBuilder stringBuilder = new StringBuilder();
|
stringBuilder.Append("INSERT INTO SYS_DICT_DATA ( ");
|
stringBuilder.Append(" DICT_ID, DICT_VALUE, DICT_LABEL, DICT_TYPE, DICT_SORT, STATUS, CREATE_BY, CREATE_TIME, UPDATE_BY, UPDATE_TIME, REMARK ");
|
stringBuilder.Append(" ) ");
|
stringBuilder.Append(" VALUES (:DICT_ID, :DICT_VALUE, :DICT_LABEL, :DICT_TYPE, :DICT_SORT, :STATUS, :CREATE_BY, :CREATE_TIME, :UPDATE_BY, :UPDATE_TIME, :REMARK ");
|
stringBuilder.Append(" ) ");
|
|
string sql = stringBuilder.ToString();
|
|
return OracleHelper.ExecuteSql(sql,
|
OracleHelper.Connection,
|
new OracleParameter(":DICT_ID", (object)o.DictId),
|
new OracleParameter(":DICT_VALUE", (object)o.DictValue),
|
new OracleParameter(":DICT_LABEL", (object)o.DictLabel),
|
new OracleParameter(":DICT_TYPE", (object)o.DictType),
|
new OracleParameter(":DICT_SORT", (object)o.DictSort),
|
new OracleParameter(":STATUS", (object)o.Status),
|
new OracleParameter(":CREATE_BY", (object)o.CreateBy),
|
new OracleParameter(":CREATE_TIME", (object)o.CreateTime),
|
new OracleParameter(":UPDATE_BY", (object)o.UpdateBy),
|
new OracleParameter(":UPDATE_TIME", (object)o.UpdateTime),
|
new OracleParameter(":REMARK", (object)o.Remark)
|
) > 0;
|
}
|
|
//删除
|
public bool Delete(SysDictData o)
|
{
|
if (o == null || string.IsNullOrEmpty(o.DictId))
|
return false;
|
//return OracleHelper.ExecuteSql("delete from SYS_DICT_DATA where DICT_ID=:DICT_ID", OracleHelper.Connection, new OracleParameter(":DICT_ID", (object)dictId)) > 0;
|
return OracleHelper.ExecuteSql("UPDATE SYS_DICT_DATA SET DEL_FLAG = 2, UPDATE_BY=:UPDATE_BY, UPDATE_TIME=:UPDATE_TIME where DICT_ID=:DICT_ID", OracleHelper.Connection, new OracleParameter(":UPDATE_BY", (object)o.UpdateBy), new OracleParameter(":UPDATE_TIME", (object)o.UpdateTime), new OracleParameter(":DICT_ID", (object)o.DictId)) > 0;
|
|
}
|
|
//更新信息
|
public bool Update(SysDictData o)
|
{
|
OracleParameter[] cmdParms = new OracleParameter[] {
|
new OracleParameter(":DICT_ID", (object)o.DictId),
|
new OracleParameter(":DICT_VALUE", (object)o.DictValue),
|
new OracleParameter(":DICT_LABEL", (object)o.DictLabel),
|
new OracleParameter(":DICT_TYPE", (object)o.DictType),
|
new OracleParameter(":DICT_SORT", (object)o.DictSort),
|
new OracleParameter(":STATUS", (object)o.Status),
|
new OracleParameter(":CREATE_BY", (object)o.CreateBy),
|
new OracleParameter(":CREATE_TIME", (object)o.CreateTime),
|
new OracleParameter(":UPDATE_BY", (object)o.UpdateBy),
|
new OracleParameter(":UPDATE_TIME", (object)o.UpdateTime),
|
new OracleParameter(":REMARK", (object)o.Remark)
|
};
|
|
StringBuilder stringBuilder = new StringBuilder();
|
stringBuilder.Append("UPDATE SYS_DICT_DATA SET ");
|
stringBuilder.Append(" DICT_VALUE=:DICT_VALUE ");
|
stringBuilder.Append(" , DICT_LABEL =:DICT_LABEL ");
|
stringBuilder.Append(" DICT_TYPE=:DICT_TYPE ");
|
stringBuilder.Append(" , DICT_SORT =:DICT_SORT ");
|
stringBuilder.Append(" , STATUS =:STATUS ");
|
stringBuilder.Append(" , CREATE_BY =:CREATE_BY ");
|
stringBuilder.Append(" , CREATE_TIME =:CREATE_TIME ");
|
stringBuilder.Append(" , UPDATE_BY =:UPDATE_BY ");
|
stringBuilder.Append(" , UPDATE_TIME =:UPDATE_TIME ");
|
stringBuilder.Append(" , REMARK=:REMARK ");
|
stringBuilder.Append(" WHERE DICT_ID=:DICT_ID ");
|
|
string sql = stringBuilder.ToString();
|
return (OracleHelper.ExecuteSql(sql, OracleHelper.Connection, cmdParms) > 0);
|
}
|
|
//获取信息
|
public SysDictData GetByDictId(string dictId)
|
{
|
SysDictData o = new SysDictData();
|
if (string.IsNullOrEmpty(dictId))
|
return o;
|
using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from SYS_DICT_DATA where DICT_ID=:DICT_ID"), OracleHelper.Connection, new OracleParameter(":DICT_ID", (object)dictId)))
|
{
|
if (((DbDataReader)reader).Read())
|
{
|
this.ReadInfo(reader, o);
|
return o;
|
}
|
}
|
return o;
|
}
|
|
//获取信息
|
public SysDictData GetByDictValue(string dictType, string dictValue)
|
{
|
SysDictData o = new SysDictData();
|
if (string.IsNullOrEmpty(dictValue))
|
return o;
|
using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from SYS_DICT_DATA where DICT_TYPE=:DICT_TYPE AND DICT_VALUE=:DICT_VALUE"), OracleHelper.Connection, new OracleParameter(":DICT_TYPE", (object)dictType), new OracleParameter(":DICT_VALUE", (object)dictValue)))
|
{
|
if (((DbDataReader)reader).Read())
|
{
|
this.ReadInfo(reader, o);
|
return o;
|
}
|
}
|
return o;
|
}
|
|
//获取信息
|
public SysDictData Get(string dictId)
|
{
|
SysDictData o = new SysDictData();
|
if (string.IsNullOrEmpty(dictId))
|
return o;
|
using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from SYS_DICT_DATA where DICT_ID=:DICT_ID"), OracleHelper.Connection, new OracleParameter(":DICT_ID", (object)dictId)))
|
{
|
if (((DbDataReader)reader).Read())
|
{
|
this.ReadInfo(reader, o);
|
return o;
|
}
|
}
|
return o;
|
}
|
|
//更新启用或停用
|
public bool UpdateStatus(SysDictData o)
|
{
|
if (o == null || string.IsNullOrEmpty(o.DictId))
|
return false;
|
return OracleHelper.ExecuteSql("UPDATE SYS_DICT_DATA SET STATUS=:STATUS, UPDATE_BY=:UPDATE_BY, UPDATE_TIME=:UPDATE_TIME where DICT_ID=:DICT_ID", OracleHelper.Connection, new OracleParameter(":STATUS", (object)o.Status), new OracleParameter(":UPDATE_BY", (object)o.UpdateBy), new OracleParameter(":UPDATE_TIME", (object)o.UpdateTime), new OracleParameter(":DICT_ID", (object)o.DictId)) > 0;
|
|
}
|
|
//统计记录数
|
private int ReadCount(OracleDataReader reader)
|
{
|
OracleReaderWrapper wrapper = new OracleReaderWrapper(reader);
|
return wrapper.GetInt("count", 0);
|
}
|
|
//数据封装
|
private bool ReadInfo(OracleDataReader reader, SysDictData o)
|
{
|
OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
|
o.DictId = oracleReaderWrapper.GetString("DICT_ID", "");
|
o.DictValue = oracleReaderWrapper.GetString("DICT_VALUE", "");
|
o.DictLabel = oracleReaderWrapper.GetString("DICT_LABEL", "");
|
o.DictType = oracleReaderWrapper.GetString("DICT_TYPE", "");
|
o.DictSort = oracleReaderWrapper.GetInt("DICT_SORT", 1);
|
o.ParanSource = oracleReaderWrapper.GetInt("PARAN_SOURCE", 1);
|
o.DelFlag = oracleReaderWrapper.GetInt("DEL_FLAG", 0);
|
o.Status = oracleReaderWrapper.GetInt("STATUS", 0);
|
o.CreateBy = oracleReaderWrapper.GetString("CREATE_BY", "");
|
o.CreateTime = oracleReaderWrapper.GetDateTime("CREATE_TIME");
|
o.UpdateBy = oracleReaderWrapper.GetString("UPDATE_BY", "");
|
o.UpdateTime = oracleReaderWrapper.GetDateTime("UPDATE_TIME");
|
o.Remark = oracleReaderWrapper.GetString("REMARK", "");
|
return true;
|
}
|
|
}
|
|
}
|
