dbother.cs

c#编写的汽车销售公司erp进销存系统

using System;
using CallCenter.IDAL;
using CallCenter.Modules;
using System.Data;
using System.Data.OracleClient;
using System.Collections;
using System.Text;

namespace CallCenter.OracleDAL {
	/// <summary>
	/// 
	/// </summary>
	public class DBOther:IOther {
		public DBOther() {
			// 
			// TODO: 在此处添加构造函数逻辑
			//
		}


		#region IOther 成员

		public ArrayList getAppraiseGrade() {
			ArrayList list = new ArrayList();
			string selSql = "SELECT gmark,gshow FROM appraisegrade ORDER BY gmark DESC";
			
			try {
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,null);
				while(dr.Read()) {
					OtherInfo info = new OtherInfo();
					info.gmark =dr.IsDBNull(0)?0:dr.GetInt32(0);
					info.gshow = dr.IsDBNull(1)?"":dr.GetString(1);	
				
					info.id = dr.IsDBNull(0)?0:dr.GetInt32(0);
					info.content =dr.IsDBNull(1)?"":dr.GetString(1);	

					list.Add(info);
				}
				dr.Close();
			}
			catch(Exception e) {
				throw e;
			}
			return list;
		}

		public ArrayList getSPType() {
			ArrayList list = new ArrayList();
			string selSql = "SELECT id,stname FROM sptype";
			
			try {
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,null);
				while(dr.Read()) {
					OtherInfo info = new OtherInfo();
					info.sptid =dr.IsDBNull(0)?0:dr.GetInt32(0);
					info.sptname = dr.IsDBNull(1)?"":dr.GetString(1);		
			
					info.id = dr.IsDBNull(0)?0:dr.GetInt32(0);
					info.content =dr.IsDBNull(1)?"":dr.GetString(1);

					list.Add(info);
				}
				dr.Close();
			}
			catch(Exception e) {
				throw e;
			}
			return list;
		}

		public ArrayList getWBType() {
			ArrayList list = new ArrayList();
			string selSql = "SELECT id,wbtype FROM wbtype ORDER BY id ASC";
			
			try {
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,null);
				while(dr.Read()) {
					OtherInfo info = new OtherInfo();
					info.wbtid =dr.IsDBNull(0)?0:dr.GetInt32(0);
					info.wbtype = dr.IsDBNull(1)?"":dr.GetString(1);		
			
					info.id = dr.IsDBNull(0)?0:dr.GetInt32(0);
					info.content =dr.IsDBNull(1)?"":dr.GetString(1);

					list.Add(info);
				}
				dr.Close();
			}
			catch(Exception e) {
				throw e;
			}
			return list;
		}
	
		public ArrayList getWBState() {
			ArrayList list = new ArrayList();
			string selSql = "SELECT id,wbstate FROM wbstate ORDER BY id ASC";
			
			try {
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,null);
				while(dr.Read()) {
					OtherInfo info = new OtherInfo();
					info.wbsid =dr.IsDBNull(0)?0:dr.GetInt32(0);
					info.wbstate = dr.IsDBNull(1)?"":dr.GetString(1);		
			
					info.id = dr.IsDBNull(0)?0:dr.GetInt32(0);
					info.content =dr.IsDBNull(1)?"":dr.GetString(1);

					list.Add(info);
				}
				dr.Close();
			}
			catch(Exception e) {
				throw e;
			}
			return list;
		}

		public int addAppraiseGrade(int id, string grade) {
			string strSel = "SELECT gmark FROM appraisegrade WHERE gmark=:gmark OR gshow=:gshow";
			string strAdd = "INSERT INTO appraisegrade(gmark,gshow) VALUES(:gmark,:gshow)";
			OracleParameter [] param = new OracleParameter[] {
																 new OracleParameter(":gmark",OracleType.Int32,10),
																 new OracleParameter(":gshow",OracleType.VarChar,60),																 
			};
			param[0].Value=id;
			param[1].Value=grade;
			try {
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,strSel,param);
				if(dr.Read()) {
					return -1;
				}				
				dr.Close();
				return OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,strAdd,param);
			}
			catch(Exception e) {
				throw e;
			}
		}
	
		public int addSPType(int id, string type) {
			string strSel = "SELECT id FROM sptype WHERE id=:id OR stname=:stname";
			string strAdd = "INSERT INTO sptype(id,stname) VALUES(:id,:stname)";
			OracleParameter [] param = new OracleParameter[] {
																 new OracleParameter(":id",OracleType.Int32,10),
																 new OracleParameter(":stname",OracleType.VarChar,60),																 
			};
			param[0].Value=id;
			param[1].Value=type;
			try {
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,strSel,param);
				if(dr.Read()) {
					return -1;
				}				
				dr.Close();
				return OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,strAdd,param);
			}
			catch(Exception e) {
				throw e;
			}
		}
	
		public int addWBState(int id, string state) {
			string strSel = "SELECT id FROM wbstate WHERE id=:id OR wbstate=:wbstate";
			string strAdd = "INSERT INTO wbstate(id,wbstate) VALUES(:id,:wbstate)";
			OracleParameter [] param = new OracleParameter[] {
																 new OracleParameter(":id",OracleType.Int32,10),
																 new OracleParameter(":wbstate",OracleType.VarChar,60),																 
			};
			param[0].Value=id;
			param[1].Value=state;
			try {
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,strSel,param);
				if(dr.Read()) {
					return -1;
				}				
				dr.Close();
				return OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,strAdd,param);
			}
			catch(Exception e) {
				throw e;
			}
		}
	
		public int addWBType(int id, string type) {
			string strSel = "SELECT id FROM wbtype WHERE id=:id OR wbtype=:wbtype";
			string strAdd = "INSERT INTO wbtype(id,wbtype) VALUES(:id,:wbtype)";
			OracleParameter [] param = new OracleParameter[] {
																 new OracleParameter(":id",OracleType.Int32,10),
																 new OracleParameter(":wbtype",OracleType.VarChar,60),																 
			};
			param[0].Value=id;
			param[1].Value=type;
			try {
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,strSel,param);
				if(dr.Read()) {
					return -1;
				}				
				dr.Close();
				return OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,strAdd,param);
			}
			catch(Exception e) {
				throw e;
			}
		}
	
		public int delAppraiseGrade(string ids) {
			// TODO:  添加 DBOther.delAppraiseGrade 实现
			return 0;
		}
	
		public int delSPType(string ids) {
			// TODO:  添加 DBOther.delSPType 实现
			return 0;
		}
	
		public int delWBState(string ids) {
			// TODO:  添加 DBOther.delWBState 实现
			return 0;
		}
	
		public int delWBType(string ids) {
			// TODO:  添加 DBOther.delWBType 实现
			return 0;
		}
	
		public int updateAppraiseGrade(int id, string grade) {
			string strSel = "SELECT gmark FROM appraisegrade WHERE gmark<>:gmark AND gshow=:gshow";
			string strUpd = "UPDATE appraisegrade SET gshow=:gshow WHERE gmark=:gmark";
			OracleParameter [] param = new OracleParameter[] {
																 new OracleParameter(":gmark",OracleType.Int32,10),
																 new OracleParameter(":gshow",OracleType.VarChar,60),																 
			};
			param[0].Value=id;
			param[1].Value=grade;
			try {
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,strSel,param);
				if(dr.Read()) {
					return -1;
				}				
				dr.Close();
				return OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,strUpd,param);
			}
			catch(Exception e) {
				throw e;
			}
		}
	
		public int updateSPType(int id, string type) {
			string strSel = "SELECT id FROM sptype WHERE id<>:id AND stname=:stname";
			string strUpd = "UPDATE sptype SET stname=:stname WHERE id=:id";
			OracleParameter [] param = new OracleParameter[] {
																 new OracleParameter(":id",OracleType.Int32,10),
																 new OracleParameter(":stname",OracleType.VarChar,60),																 
			};
			param[0].Value=id;
			param[1].Value=type;
			try {
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,strSel,param);
				if(dr.Read()) {
					return -1;
				}				
				dr.Close();
				return OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,strUpd,param);
			}
			catch(Exception e) {
				throw e;
			}
		}
	
		public int updateWBState(int id, string state) {
			string strSel = "SELECT id FROM wbstate WHERE id<>:id AND wbstate=:wbstate";
			string strUpd = "UPDATE wbstate SET wbstate=:wbstate WHERE id=:id";
			OracleParameter [] param = new OracleParameter[] {
																 new OracleParameter(":id",OracleType.Int32,10),
																 new OracleParameter(":wbstate",OracleType.VarChar,60),																 
			};
			param[0].Value=id;
			param[1].Value=state;
			try {
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,strSel,param);
				if(dr.Read()) {
					return -1;
				}				
				dr.Close();
				return OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,strUpd,param);
			}
			catch(Exception e) {
				throw e;
			}
		}
	
		public int updateWBType(int id, string type) {
			string strSel = "SELECT id FROM wbtype WHERE id<>:id AND wbtype=:wbtype";
			string strUpd = "UPDATE wbtype SET wbtype=:wbtype WHERE id=:id";
			OracleParameter [] param = new OracleParameter[] {
																 new OracleParameter(":id",OracleType.Int32,10),
																 new OracleParameter(":wbtype",OracleType.VarChar,60),																 
			};
			param[0].Value=id;
			param[1].Value=type;
			try {
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,strSel,param);
				if(dr.Read()) {
					return -1;
				}				
				dr.Close();
				return OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,strUpd,param);
			}
			catch(Exception e) {
				throw e;
			}
		}

		public ArrayList getDictironary() {
			ArrayList list = new ArrayList();
			string selSql = "SELECT id,enname,chname FROM dictionarymanage ORDER BY id ASC";
			
			try {
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,null);
				while(dr.Read()) {
					OtherInfo info = new OtherInfo();
					info.dmid =dr.IsDBNull(0)?0:dr.GetInt32(0);
					info.enname = dr.IsDBNull(1)?"":dr.GetString(1);
					info.chname = dr.IsDBNull(2)?"":dr.GetString(2);

					list.Add(info);
				}
				dr.Close();
			}
			catch(Exception e) {
				throw e;
			}
			return list;
		}
	}


	#endregion
	
}