|
|
using Common;
|
using Model;
|
using Oracle.DataAccess.Client;
|
using System;
|
using System.Collections.Generic;
|
using System.Data.Common;
|
using System.Text;
|
|
namespace Dao
|
{
|
public class GwMobileAreaDao : IDisposable
|
{
|
public List<GwMobileArea> MobilAreaPageList(string mobileSegment, string city, string province, string cardType, out int recordcount, int pageSize, int PageIndex)
|
{
|
List<GwMobileArea> list = new List<GwMobileArea>();
|
recordcount = 0;
|
try
|
{
|
StringBuilder stringBuilder = new StringBuilder();
|
stringBuilder.Append("from GW_MOBILEAREA where 1=1");
|
OracleParameter[] oracleParameterArray = new OracleParameter[4];
|
if (!string.IsNullOrEmpty(mobileSegment))
|
{
|
oracleParameterArray[0] = new OracleParameter(":MOBILE_SEGMENT", (OracleDbType)126);
|
((DbParameter)oracleParameterArray[0]).Value = (object)("%" + mobileSegment + "%");
|
stringBuilder.Append(" and MOBILE_SEGMENT like :MOBILE_SEGMENT");
|
}
|
if (!string.IsNullOrEmpty(city))
|
{
|
oracleParameterArray[1] = new OracleParameter(":CITY", (OracleDbType)126);
|
((DbParameter)oracleParameterArray[1]).Value = (object)city;
|
stringBuilder.Append(" and CITY=:CITY ");
|
}
|
if (!string.IsNullOrEmpty(province))
|
{
|
oracleParameterArray[2] = new OracleParameter(":PROVINCE", (OracleDbType)126);
|
((DbParameter)oracleParameterArray[2]).Value = (object)province;
|
stringBuilder.Append(" and PROVINCE=:PROVINCE ");
|
}
|
if (!string.IsNullOrEmpty(cardType))
|
{
|
oracleParameterArray[3] = new OracleParameter(":CARD_TYPE", (OracleDbType)126);
|
((DbParameter)oracleParameterArray[3]).Value = (object)cardType;
|
stringBuilder.Append(" and CARD_TYPE=:CARD_TYPE");
|
}
|
using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + stringBuilder.ToString(), OracleHelper.Connection, oracleParameterArray))
|
{
|
while (((DbDataReader)reader).Read())
|
recordcount = this.ReadCount(reader);
|
}
|
using (OracleDataReader reader = OracleHelper.ExecuteReader(PubConstant.doOracleSql(PageIndex, pageSize, recordcount, "select * " + stringBuilder.ToString()).ToString(), OracleHelper.Connection, oracleParameterArray))
|
{
|
while (((DbDataReader)reader).Read())
|
{
|
GwMobileArea o = new GwMobileArea();
|
if (this.ReadInfo(reader, o))
|
list.Add(o);
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
LogHelper.Error(ex);
|
return list;
|
}
|
return list;
|
}
|
|
public List<GwMobileArea> GetMobilAreaList()
|
{
|
string sql = "SELECT distinct PROVINCE FROM GW_MOBILEAREA";
|
List<GwMobileArea> list = new List<GwMobileArea>();
|
using (OracleDataReader reader = OracleHelper.ExecuteReader(sql, OracleHelper.Connection))
|
{
|
while (((DbDataReader)reader).Read())
|
{
|
GwMobileArea o = new GwMobileArea();
|
if (this.ReadInfo(reader, o))
|
list.Add(o);
|
}
|
}
|
return list;
|
}
|
|
public bool UpdateMobileArea(GwMobileArea o)
|
{
|
return OracleHelper.ExecuteSql("update GW_MOBILEAREA set SEGMENT=:SEGMENT,CITY=:CITY,PROVINCE=:PROVINCE,CARD_TYPE=:CARD_TYPE,LAST_UPDATE_TIME=sysdate where ID=:ID", OracleHelper.Connection, new OracleParameter(":ID", (object)o.ID), new OracleParameter(":MOBILE_SEGMENT", (object)o.MobileSegment), new OracleParameter(":CITY", (object)o.City), new OracleParameter(":PROVINCE", (object)o.Province), new OracleParameter(":CARD_TYPE", (object)o.CardType)) > 0;
|
}
|
|
public GwMobileArea GetMobileArea(int id)
|
{
|
GwMobileArea o = new GwMobileArea();
|
using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT * FROM GW_MOBILEAREA WHERE ID=:ID", OracleHelper.Connection, new OracleParameter(":ID", (object)id)))
|
{
|
if (((DbDataReader)reader).Read())
|
{
|
if (!this.ReadInfo(reader, o))
|
throw new Exception("ReadInfo发生异常");
|
return o;
|
}
|
}
|
return o;
|
}
|
|
public bool AddMobileArea(GwMobileArea item)
|
{
|
return OracleHelper.ExecuteSql("INSERT INTO GW_MOBILEAREA(ID,SEGMENT,CITY,PROVINCE,CARD_TYPE,LAST_UPDATE_TIME,IS_DELETED) VALUES(GW_MOBILEAREA_ID_SEQ.NEXTVAL,:SEGMENT,:CITY,:PROVINCE,:CARD_TYPE, sysdate,IS_DELETED)", OracleHelper.Connection, new OracleParameter(":MOBILE_SEGMENT", (object)item.MobileSegment), new OracleParameter(":CITY", (object)item.City), new OracleParameter(":PROVINCE", (object)item.Province), new OracleParameter(":CARD_TYPE", (object)item.CardType), new OracleParameter(":IS_DELETED", (object)item.IsDeleted)) > 0;
|
}
|
|
private bool ReadInfo(OracleDataReader reader, GwMobileArea o)
|
{
|
OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
|
o.ID = oracleReaderWrapper.GetInt("ID", 0);
|
o.MobileSegment = oracleReaderWrapper.GetString("MOBILE_SEGMENT", "");
|
o.City = oracleReaderWrapper.GetString("CITY", "");
|
o.Province = oracleReaderWrapper.GetString("PROVINCE", "");
|
o.CardType = oracleReaderWrapper.GetString("CARD_TYPE", "");
|
o.LastUpdateTime = oracleReaderWrapper.GetDateTime("LAST_UPDATE_TIME");
|
o.IsDeleted = oracleReaderWrapper.GetInt("IS_DELETED", 0);
|
return true;
|
}
|
|
private int ReadCount(OracleDataReader reader)
|
{
|
try
|
{
|
return new OracleReaderWrapper(reader).GetInt("count", 0);
|
}
|
catch (Exception ex)
|
{
|
LogHelper.Error(ex);
|
return 0;
|
}
|
}
|
|
public void Dispose()
|
{
|
}
|
}
|
}
|
