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 SysDictTypeDao : IDisposable
|
{
|
public void Dispose()
|
{
|
}
|
|
private static SysDictTypeDao _instance;
|
public static SysDictTypeDao Instance
|
{
|
get {
|
if (_instance == null)
|
{
|
_instance = new SysDictTypeDao();
|
}
|
return _instance;
|
}
|
}
|
|
|
public List<SysDictType> LoadInfoList(string dictName, int pageSize, int PageIndex, out int recordcount)
|
{
|
|
List<SysDictType> list = new List<SysDictType>();
|
recordcount = 0;
|
try
|
{
|
StringBuilder builder = new StringBuilder();
|
builder.Append("from SYS_DICT_TYPE where 1=1 AND DEL_FLAG=0 ");
|
if (!string.IsNullOrEmpty(dictName))
|
{
|
builder.Append(" and DICT_NAME like '%" + dictName + "%'");
|
}
|
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 CREATE_TIME ", OracleHelper.Connection ))
|
{
|
while (reader2.Read())
|
{
|
SysDictType o = new SysDictType();
|
if (this.ReadInfo(reader2, o))
|
{
|
list.Add(o);
|
}
|
}
|
}
|
}
|
catch (Exception exception)
|
{
|
LogHelper.Error(exception);
|
return list;
|
}
|
return list;
|
|
}
|
|
//根据条件获取字典类型列表
|
public List<SysDictType> getAllList(SysDictType bean)
|
{
|
|
List<SysDictType> list = new List<SysDictType>();
|
try
|
{
|
StringBuilder builder = new StringBuilder();
|
builder.Append("from SYS_DICT_TYPE where 1=1 AND DEL_FLAG=0 ");
|
if (!string.IsNullOrEmpty(bean.DictName))
|
{
|
builder.Append(" and DICT_NAME like '%" + bean.DictName + "%'");
|
}
|
|
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())
|
{
|
SysDictType o = new SysDictType();
|
if (this.ReadInfo(reader, o))
|
{
|
list.Add(o);
|
}
|
}
|
}
|
}
|
catch (Exception exception)
|
{
|
LogHelper.Error(exception);
|
return list;
|
}
|
return list;
|
|
}
|
|
//添加字典类型
|
public bool Add(SysDictType o)
|
{
|
StringBuilder stringBuilder = new StringBuilder();
|
stringBuilder.Append("INSERT INTO SYS_DICT_TYPE ( ");
|
stringBuilder.Append(" DICT_ID, DICT_TYPE, DICT_NAME, STATUS, CREATE_BY, CREATE_TIME, UPDATE_BY, UPDATE_TIME, REMARK ");
|
stringBuilder.Append(" ) ");
|
stringBuilder.Append(" VALUES (:DICT_ID, :DICT_TYPE, :DICT_NAME, :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_TYPE", (object)o.DictType),
|
new OracleParameter(":DICT_NAME", (object)o.DictName),
|
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(SysDictType o)
|
{
|
if (o==null || string.IsNullOrEmpty(o.DictId) )
|
return false;
|
//return OracleHelper.ExecuteSql("delete from SYS_DICT_TYPE where DICT_ID=:DICT_ID", OracleHelper.Connection, new OracleParameter(":DICT_ID", (object)dictId)) > 0;
|
return OracleHelper.ExecuteSql("UPDATE SYS_DICT_TYPE 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(SysDictType o)
|
{
|
OracleParameter[] cmdParms = new OracleParameter[] {
|
new OracleParameter(":DICT_ID", (object)o.DictId),
|
new OracleParameter(":DICT_TYPE", (object)o.DictType),
|
new OracleParameter(":DICT_NAME", (object)o.DictName),
|
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_TYPE SET ");
|
stringBuilder.Append(" DICT_TYPE=:DICT_TYPE ");
|
stringBuilder.Append(" , DICT_NAME =:DICT_NAME ");
|
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 SysDictType GetByDictId(string dictId)
|
{
|
SysDictType o = new SysDictType();
|
if (string.IsNullOrEmpty(dictId))
|
return o;
|
using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from SYS_DICT_TYPE 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 SysDictType GetByDictType(string dictType)
|
{
|
SysDictType o = new SysDictType();
|
if (string.IsNullOrEmpty(dictType))
|
return o;
|
using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from SYS_DICT_TYPE where DICT_TYPE=:DICT_TYPE"), OracleHelper.Connection, new OracleParameter(":DICT_TYPE", (object)dictType)))
|
{
|
if (((DbDataReader)reader).Read())
|
{
|
this.ReadInfo(reader, o);
|
return o;
|
}
|
}
|
return o;
|
}
|
|
//更新启用或停用
|
public bool UpdateStatus(SysDictType o)
|
{
|
if (o==null || string.IsNullOrEmpty(o.DictId))
|
return false;
|
return OracleHelper.ExecuteSql("UPDATE SYS_DICT_TYPE 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;
|
}
|
|
//判断是否在产品中使用
|
public int SysDictTypeUseCount(string dictType)
|
{
|
string str = string.Format("SELECT count(*) as Count from SYS_DICT_DATA where DICT_TYPE=:DICT_TYPE ");
|
List<OracleParameter> list = new List<OracleParameter>();
|
int num = 0;
|
using (OracleDataReader reader = OracleHelper.ExecuteReader(str.ToString(), OracleHelper.Connection, new OracleParameter(":DICT_TYPE", (object)dictType)))
|
{
|
while (((DbDataReader)reader).Read())
|
num = this.ReadCount(reader);
|
}
|
return num;
|
}
|
|
//统计记录数
|
private int ReadCount(OracleDataReader reader)
|
{
|
OracleReaderWrapper wrapper = new OracleReaderWrapper(reader);
|
return wrapper.GetInt("count", 0);
|
}
|
|
//数据封装
|
private bool ReadInfo(OracleDataReader reader, SysDictType o)
|
{
|
OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
|
o.DictId = oracleReaderWrapper.GetString("DICT_ID", "");
|
o.DictType = oracleReaderWrapper.GetString("DICT_TYPE", "");
|
o.DictName = oracleReaderWrapper.GetString("DICT_NAME", "");
|
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;
|
}
|
|
}
|
|
}
|
