dbsysoperator.cs

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

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

namespace CallCenter.OracleDAL
{
	/// <summary>
	/// 
	/// </summary>
	public class DBSysoperator:ISysoperator
	{
		public DBSysoperator()
		{
			// 
			// TODO: 在此处添加构造函数逻辑
			//
		}
	
		#region ISysoperator 成员

		public int addSysoperator(SysoperatorInfo info)
		{
			if( info == null)
				throw new Exception("The Sysoperator Object is null,Can't Save Null object!");
		
			//判断登陆名称是否重复
			if(info.lname.Equals(this.getSysoperatorByLname(info.lname).lname))
			{
				return -1;
			}
			//判断坐席ID是否重复
			if(this.getSysoperatorByOid(info.oid,info.lname))
			{
				return -2;
			}
		
			string addSql = "INSERT INTO sysoperator(lname,oname,opassword,olevel,omenuid,odescription,oid,otelnumber) VALUES(:lname,:oname,:opassword,:olevel,:omenuid,:odescription,:oid,:otelnumber)";			
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":lname",OracleType.VarChar,60),
					new OracleParameter(":oname",OracleType.VarChar,60),
					new OracleParameter(":opassword",OracleType.VarChar,60),
					new OracleParameter(":olevel",OracleType.VarChar,60),
					new OracleParameter(":omenuid",OracleType.VarChar,200),
					new OracleParameter(":odescription",OracleType.VarChar,200),
				new OracleParameter(":oid",OracleType.Int32,10),
				new OracleParameter(":otelnumber",OracleType.VarChar,20),
			};
			param[0].Value = info.lname;
			param[1].Value = info.oname;
			param[2].Value = info.opassword;
			param[3].Value = info.olevel;
			param[4].Value = info.omenuid;
			param[5].Value = info.odescription;
			param[6].Value = info.oid;
			param[7].Value = info.otelnumnber;

			try
			{
				OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,addSql,param);
			}
			catch(Exception e)
			{						
				throw e;
			}
			finally				
			{
				;
			}
			return 1;
		}

		public int updateSysoperator(SysoperatorInfo info)
		{
			if( info == null)
				throw new Exception("The Sysoperator Object is null,Can't Save Null object!");
				
			//判断坐席ID是否重复
			if(this.getSysoperatorByOid(info.oid,info.lname))
			{
				return -2;
			}
			string updateSql = "UPDATE sysoperator SET oname=:oname,opassword=:opassword,olevel=:olevel,omenuid=:omenuid,odescription=:odescription,oid=:oid,otelnumber=:otelnumber WHERE lname=:lname";			
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":lname",OracleType.VarChar,60),
					new OracleParameter(":oname",OracleType.VarChar,60),
					new OracleParameter(":opassword",OracleType.VarChar,60),
					new OracleParameter(":olevel",OracleType.VarChar,60),
					new OracleParameter(":omenuid",OracleType.VarChar,200),
					new OracleParameter(":odescription",OracleType.VarChar,200),
					new OracleParameter(":oid",OracleType.Int32,10),
					new OracleParameter(":otelnumber",OracleType.VarChar,20),
			};
			param[0].Value = info.lname;
			param[1].Value = info.oname;
			param[2].Value = info.opassword;
			param[3].Value = info.olevel;
			param[4].Value = info.omenuid;
			param[5].Value = info.odescription;
			param[6].Value = info.oid;
			param[7].Value = info.otelnumnber;

			try
			{
				return OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,updateSql,param);
			}
			catch(Exception e)
			{						
				throw e;
			}
			finally				
			{
				;
			}
		}

		public void saveOpopedom(string lname, string popedom)
		{
			// TODO:  添加 DBSysoperator.saveOpopedom 实现
		}

		public void delSysoperator(string lnames)
		{
			string delSql = "DELETE FROM sysoperator WHERE lname IN('"+lnames.Replace("'","''").Replace(",","','")+"')";
			
			try
			{
				OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,delSql,null);
			}
			catch(Exception e)
			{
				throw e;
			}
			finally
			{
				;
			}
		}

		public SysoperatorInfo login(string lname, string password)
		{
			SysoperatorInfo info = new SysoperatorInfo();
			string selSql = "SELECT lname,oname,olevel,omenuid,odescription,oid,otelnumber FROM sysoperator WHERE lname=:lname AND opassword=:opassword";
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":lname",OracleType.VarChar,10),
					new OracleParameter(":opassword",OracleType.VarChar,10),
			};
			param[0].Value = lname;
			param[1].Value = password;
			try
			{
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,param);
				if(dr.Read())
				{
					info.lname = dr.IsDBNull(0)?"":dr.GetString(0);
					info.oname = dr.IsDBNull(1)?"":dr.GetString(1);
					info.olevel = dr.IsDBNull(2)?"":dr.GetString(2);
					info.omenuid = dr.IsDBNull(3)?"":dr.GetString(3);
					info.odescription = dr.IsDBNull(4)?"":dr.GetString(4);
					info.oid = dr.IsDBNull(5)?0:dr.GetInt32(5);
					info.otelnumnber = dr.IsDBNull(6)?"":dr.GetString(6);
				}
				dr.Close();
			}
			catch(Exception e)
			{
				throw e;
			}
			return info;
		}

		public SysoperatorInfo getSysoperatorByLname(string lname)
		{
			SysoperatorInfo info = new SysoperatorInfo();
			string selSql = "SELECT lname,oname,olevel,omenuid,odescription,oid,otelnumber,opassword FROM sysoperator WHERE lname=:lname";
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":lname",OracleType.VarChar,10),
			};
			param[0].Value = lname;
			try
			{
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,param);
				if(dr.Read())
				{
					info.lname = dr.IsDBNull(0)?"":dr.GetString(0);
					info.oname = dr.IsDBNull(1)?"":dr.GetString(1);
					info.olevel = dr.IsDBNull(2)?"":dr.GetString(2);
					info.omenuid = dr.IsDBNull(3)?"":dr.GetString(3);
					info.odescription = dr.IsDBNull(4)?"":dr.GetString(4);
					info.oid = dr.IsDBNull(5)?0:dr.GetInt32(5);
					info.otelnumnber = dr.IsDBNull(6)?"":dr.GetString(6);
					info.opassword = dr.IsDBNull(7)?"":dr.GetString(7);
				}
				dr.Close();
			}
			catch(Exception e)
			{
				throw e;
			}
			return info;
		}

		public bool getSysoperatorByOid(int oid,string lname)
		{
			bool flag = false;
			SysoperatorInfo info = new SysoperatorInfo();
			string selSql = "SELECT lname,oname,olevel,omenuid,odescription,oid,otelnumber FROM sysoperator WHERE oid=:oid AND lname <> :lname";
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":oid",OracleType.Int32,10),
				new OracleParameter(":lname",OracleType.VarChar,60),
			};
			param[0].Value = oid;
			param[1].Value = lname;
			try
			{
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,param);
				if(dr.Read())
				{
//					info.lname = dr.IsDBNull(0)?"":dr.GetString(0);
//					info.oname = dr.IsDBNull(1)?"":dr.GetString(1);
//					info.olevel = dr.IsDBNull(2)?"":dr.GetString(2);
//					info.omenuid = dr.IsDBNull(3)?"":dr.GetString(3);
//					info.odescription = dr.IsDBNull(4)?"":dr.GetString(4);
//					info.oid = dr.IsDBNull(5)?0:dr.GetInt32(5);
//					info.otelnumnber = dr.IsDBNull(6)?"":dr.GetString(6);
					flag = true;
				}
				dr.Close();
			}
			catch(Exception e)
			{
				throw e;
			}
			return flag;
		}

		public ArrayList getALLSysoperator()
		{
			ArrayList list = new ArrayList();
			string selSql = "SELECT lname,oname,olevel,omenuid,odescription,oid,otelnumber FROM sysoperator";
			try
			{
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,null);
				while(dr.Read())
				{
					SysoperatorInfo info = new SysoperatorInfo();
					info.lname = dr.IsDBNull(0)?"":dr.GetString(0);
					info.oname = dr.IsDBNull(1)?"":dr.GetString(1);
					info.olevel = dr.IsDBNull(2)?"":dr.GetString(2);
					info.omenuid = dr.IsDBNull(3)?"":dr.GetString(3);
					info.odescription = dr.IsDBNull(4)?"":dr.GetString(4);
					info.oid = dr.IsDBNull(5)?0:dr.GetInt32(5);
					info.otelnumnber = dr.IsDBNull(6)?"":dr.GetString(6);

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

		public int changePassword(string lname,string oldpass,string newpass)
		{
			SysoperatorInfo info = this.login(lname,oldpass);
			if(lname.Equals(info.lname))
			{
				info.opassword = newpass;
				return this.updateSysoperator(info);
			}
			else
			{
				return -1;
			}
		}

		public DataSet getALLSysoperator_DataSet(string tableName){
			string selSql = "SELECT lname,oname,olevel,omenuid,odescription,oid,otelnumber,opassword FROM sysoperator";
			try
			{
				return OraHelper.ExecuteDataSet(OraHelper.GetOracleConnection(),CommandType.Text,tableName,selSql,null);
			}
			catch(Exception e)
			{
				throw e;
			}
		}
		#endregion
	}
}