dbknowledge.cs

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

			param[2].Value = info.ktype;
			param[3].Value = info.kkeywords;
//			param[4].Value = info.kcontent;
			param[4].Value = info.kwid;
			param[5].Value = info.kwname;
			param[6].Value = info.oamark;
			param[7].Value = info.kaid;
			param[8].Value = info.kaname;
//			param[9].Value = info.kaname;

			try
			{
				OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,addSql,param);
				byte[] blob = System.Text.Encoding.Default.GetBytes(info.kcontent);
				this.updateKnowledgeContent(info.id,blob);
			}
			catch(Exception e)
			{						
				throw e;
			}
			finally				
			{
				;
			}
			return info.id;
		}

		private void updateKnowledgeContent(int id,byte[] blob){
			try
			 {  OracleConnection mConn = new OracleConnection(OraHelper.GetOracleConnection());
				OracleDataAdapter photoAdapter;
				DataSet photoDataSet;
				DataTable photoTable;
				DataRow photoRow;

				photoAdapter = new OracleDataAdapter(
					"SELECT id,kcontent,kclength FROM knowledge WHERE id = " + id,mConn);

	
				photoDataSet= new DataSet("knowledge");
				photoAdapter.UpdateCommand = new OracleCommand
					("UPDATE knowledge SET kcontent = :kcontent,kclength=:kclength WHERE id = :id",mConn);
				photoAdapter.UpdateCommand.Parameters.Add(":kcontent",
					OracleType.Blob, blob.Length, "kcontent");
				photoAdapter.UpdateCommand.Parameters.Add(":kclength",
					OracleType.Int32, 32, "kclength");
				photoAdapter.UpdateCommand.Parameters.Add(":id",
					OracleType.Int32, 0, "id");

				photoAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

				// Configures the schema to match with Data Source
				photoAdapter.FillSchema(photoDataSet, SchemaType.Source, "kcontent");

				// Fills the DataSet with 'drivers' table data
				photoAdapter.Fill(photoDataSet,"kcontent");

				// Get the current driver ID row for updation
				photoTable = photoDataSet.Tables["kcontent"];
				photoRow = photoTable.Rows.Find(id);

				// Start the edit operation on the current row in
				// the 'drivvers' table within the dataset.
				photoRow.BeginEdit();
				// Assign the value of the Photo if not empty
				if (blob.Length != 0)
				{
					photoRow["kcontent"] = blob;
					photoRow["kclength"] = blob.Length;
				}
				// End the editing current row operation
				photoRow.EndEdit();

				// Update the database table 'drivers'
				photoAdapter.Update(photoDataSet,"kcontent");

			}
			catch(Exception e)
			{
				throw e;
			}

		}
	
		public void changeAccessAmount(int id, int access)
		{
			string updateSql="";
			if(1==access)
			{
				updateSql = "UPDATE knowledge SET oamount=oamount+1 WHERE id=:id";
			}
			else if(2==access)
			{
				updateSql = "UPDATE knowledge SET iamount=iamount+1 WHERE id=:id";
			}
			else if(3==access){
				updateSql = "UPDATE knowledge SET camount=camount+1 WHERE id=:id";
			}
//			Console.WriteLine(updateSql);
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":id",OracleType.Int32,10),
			};
			param[0].Value = id;

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

		}
	
		public int addFavorite(string cid, int kid)
		{
			string addSql = "INSERT INTO favorite(cid,kid) VALUES(:cid,:kid)";			
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":cid",OracleType.VarChar,60),
					new OracleParameter(":kid",OracleType.Int32,10),
			};
			param[0].Value = cid;
			param[1].Value = kid;

			try
			{
				return OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,addSql,param);
			}
			catch(Exception e)
			{			
				if(e.Message.ToUpper().StartsWith("ORA-00001"))
				{
					return -1;
				}
				throw e;
			}
			finally				
			{
				;
			}

		}
	
		public ArrayList getFavoriteByCId(string cid)
		{
			ArrayList list = new ArrayList();
			string selSql = "SELECT id,ktitle,ktype,kkeywords,kwid,kwname,kwdate,oamark,kaid,kaname,"+
				"oamount,iamount,camount FROM view_favorite WHERE cid=:cid";
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":cid",OracleType.VarChar,60),
			};
			param[0].Value = cid;

			try
			{
				OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,param);
				while(dr.Read())
				{
					KnowledgeInfo info = new KnowledgeInfo();
					info.id = dr.IsDBNull(0)?0:dr.GetInt32(0);;
					info.ktitle = dr.IsDBNull(1)?"":dr.GetString(1);
					info.ktype = dr.IsDBNull(2)?"":dr.GetString(2);
					info.kkeywords = dr.IsDBNull(3)?"":dr.GetString(3);
					//info.kcontent = dr.IsDBNull(4)?"":dr.GetString(4);
					info.kwid = dr.IsDBNull(4)?"":dr.GetString(5);
					info.kwname = dr.IsDBNull(5)?"":dr.GetString(6);
					info.kwdate = dr.IsDBNull(6)?"":dr.GetDateTime(7).ToShortDateString();
					info.oamark = dr.IsDBNull(7)?0:dr.GetInt32(8);
					info.kaid = dr.IsDBNull(8)?"":dr.GetString(9);
					info.kaname = dr.IsDBNull(9)?"":dr.GetString(10);
					info.oamount = dr.IsDBNull(10)?0:dr.GetInt32(11);
					info.iamount = dr.IsDBNull(11)?0:dr.GetInt32(12);
					info.camount = dr.IsDBNull(12)?0:dr.GetInt32(13);

					list.Add(info);
				}
				dr.Close();
			}
			catch(Exception e)
			{
				throw e;
			}
			return list;
		}
	
		public int delFavorite(string cid, int kid)
		{
			string delSql = "DELETE FROM favorite WHERE cid=:cid AND kid=:kid";			
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":cid",OracleType.VarChar,60),
					new OracleParameter(":kid",OracleType.Int32,10),
			};
			param[0].Value = cid;
			param[1].Value = kid;

			try
			{
				return OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,delSql,param);
			}
			catch(Exception e)
			{						
				throw e;
			}
			finally				
			{
				;
			}
		}
		
		public DataSet getFavoriteByCId_DataSet(string cid,string tableName)
		{
			string selSql = "SELECT id,ktitle,ktype,kkeywords,kwid,kwname,kwdate,oamark,kaid,kaname,"+
				"oamount,iamount,camount FROM view_favorite WHERE cid=:cid";
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":cid",OracleType.VarChar,60),
			};
			param[0].Value = cid;

			try
			{
				return OraHelper.ExecuteDataSet(OraHelper.GetOracleConnection(),CommandType.Text,tableName,selSql,param);
			}
			catch(Exception e)
			{
				throw e;
			}
		}
	
		public DataSet getHotspotKnowledge_DataSet(int num,string tableName)
		{
			string selSql = "SELECT id,ktitle,ktype,kkeywords,kwid,kwname,kwdate,oamark,kaid,kaname,"+
				"oamount,iamount,camount FROM (SELECT id,ktitle,ktype,kkeywords,kwid,kwname,kwdate,oamark,kaid,kaname,"+
				"oamount,iamount,camount FROM knowledge ORDER BY camount DESC) WHERE rownum <=:num";
			OracleParameter [] param = new OracleParameter[]
				{
					new OracleParameter(":num",OracleType.Int32,10),
			};
			param[0].Value = num;
			try
			{
				return OraHelper.ExecuteDataSet(OraHelper.GetOracleConnection(),CommandType.Text,tableName,selSql,param);
			}
			catch(Exception e)
			{
				throw e;
			}
		}
	
		public DataSet queryKnowledge_DataSet(KnowledgeInfo info,string tableName)
		{
			StringBuilder selSql = new StringBuilder("SELECT id,ktitle,ktype,kkeywords,kwid,kwname,kwdate,oamark,kaid,kaname,"+
				"oamount,iamount,camount FROM knowledge WHERE 1=1 ");
			//知识标题
			if(null != info.ktitle && !"".Equals(info.ktitle))
			{
				selSql.Append(" AND ktitle LIKE '%"+info.ktitle.Replace("'","''")+"%'");
			}
			//关键字
			if(null != info.kkeywords && !"".Equals(info.kkeywords))
			{
				selSql.Append(" AND kkeywords LIKE '%"+info.kkeywords.Replace("'","''")+"%'");
			}
			//知识分类
			if(null != info.ktype && !"".Equals(info.ktype))
			{
				selSql.Append(" AND ktype LIKE '%,"+info.ktype+",%'");
			}
			selSql.Append(" ORDER BY id DESC");
			try
			{
				return OraHelper.ExecuteDataSet(OraHelper.GetOracleConnection(),CommandType.Text,tableName,selSql.ToString(),null);
			}
			catch(Exception e)
			{
				throw e;
			}
		}
	
		public DataSet getKnowledgeByKType_DataSet(int tid,string tableName)
		{
			string selSql = "SELECT id,ktitle,ktype,kkeywords,kwid,kwname,kwdate,oamark,kaid,kaname,"+
				"oamount,iamount,camount FROM knowledge WHERE ktype LIKE '%,"+tid+",%'";
			
			try
			{
				return OraHelper.ExecuteDataSet(OraHelper.GetOracleConnection(),CommandType.Text,tableName,selSql,null);
			}
			catch(Exception e)
			{
				throw e;
			}
		}

		#endregion

	}
}