dbknowledge.cs

汽车销售公司ERP进销存系统 汽车销售公司ERP进销存系统

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

			try
			{
				OraHelper.SqlserverExecuteNonQuery(OraHelper.GetSqlserverConnection(),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.GetSqlserverConnection());
//				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",
//					SqlDbType.Blob, blob.Length, "kcontent");
//				photoAdapter.UpdateCommand.Parameters.Add(":kclength",
//					SqlDbType.Int32, 32, "kclength");
//				photoAdapter.UpdateCommand.Parameters.Add(":id",
//					SqlDbType.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);
			SqlParameter [] param = new SqlParameter[]
				{
					new SqlParameter("@id",SqlDbType.Int,4),
			};
			param[0].Value = id;

			try
			{
				OraHelper.SqlserverExecuteNonQuery(OraHelper.GetSqlserverConnection(),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)";			
			SqlParameter [] param = new SqlParameter[]
				{
					new SqlParameter("@cid",SqlDbType.VarChar,60),
					new SqlParameter("@kid",SqlDbType.Int,4),
			};
			param[0].Value = cid;
			param[1].Value = kid;

			try
			{
				return OraHelper.SqlserverExecuteNonQuery(OraHelper.GetSqlserverConnection(),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";
			SqlParameter [] param = new SqlParameter[]
				{
					new SqlParameter("@cid",SqlDbType.VarChar,60),
			};
			param[0].Value = cid;

			try
			{
				SqlDataReader dr = OraHelper.SqlserverExecuteReader(OraHelper.GetSqlserverConnection(),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";			
			SqlParameter [] param = new SqlParameter[]
				{
					new SqlParameter("@cid",SqlDbType.VarChar,60),
					new SqlParameter("@kid",SqlDbType.Int,10),
			};
			param[0].Value = cid;
			param[1].Value = kid;

			try
			{
				return OraHelper.SqlserverExecuteNonQuery(OraHelper.GetSqlserverConnection(),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";
			SqlParameter [] param = new SqlParameter[]
				{
					new SqlParameter("@cid",SqlDbType.VarChar,60),
			};
			param[0].Value = cid;

			try
			{
				return OraHelper.SqlserverExecuteDataSet(OraHelper.GetSqlserverConnection(),CommandType.Text,tableName,selSql,param);
			}
			catch(Exception e)
			{
				throw e;
			}
		}
	
		public DataSet getHotspotKnowledge_DataSet(int num,string tableName)
		{
			string selSql = "SELECT top "+num+" id,ktitle,ktype,kkeywords,kwid,kwname,kwdate,oamark,kaid,kaname,"+
				"oamount,iamount,camount FROM knowledge  ORDER BY camount DESC";
			SqlParameter [] param = new SqlParameter[]
				{
					new SqlParameter("@num",SqlDbType.Int,4),
			};
			param[0].Value = num;
			try
			{
				return OraHelper.SqlserverExecuteDataSet(OraHelper.GetSqlserverConnection(),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.SqlserverExecuteDataSet(OraHelper.GetSqlserverConnection(),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.SqlserverExecuteDataSet(OraHelper.GetSqlserverConnection(),CommandType.Text,tableName,selSql,null);
			}
			catch(Exception e)
			{
				throw e;
			}
		}

		#endregion

	}
}