📄 dbknowledge.cs
字号:
using System;
using CallCenter.IDAL;
using CallCenter.Modules;
using System.Data;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.Collections;
using System.Text;
namespace CallCenter.OracleDAL
{
/// <summary>
/// 知识库数据操作类
/// </summary>
public class DBKnowledge:IKnowledge
{
public DBKnowledge()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
#region IKnowledge 成员
public void changeKnowledgeOAmark(string ids, int oamark)
{
string updateSql = "UPDATE knowledge SET oamark="+oamark+" WHERE id IN ("+ids+")";
try
{
OraHelper.SqlserverExecuteNonQuery(OraHelper.GetSqlserverConnection(),CommandType.Text,updateSql,null);
}
catch(Exception e)
{
throw e;
}
finally
{
;
}
}
public KnowledgeInfo getKnowledgeById(int id)
{
KnowledgeInfo info = new KnowledgeInfo();
string selSql = "SELECT id,ktitle,ktype,kkeywords,kcontent,kwid,kwname,kwdate,oamark,kaid,kaname,"+
"oamount,iamount,camount,kclength FROM knowledge WHERE id=@id";
SqlParameter [] param = new SqlParameter[]
{
new SqlParameter("@id",SqlDbType.Int,4),
};
param[0].Value = id;
try
{
SqlDataReader dr = OraHelper.SqlserverExecuteReader(OraHelper.GetSqlserverConnection(),CommandType.Text,selSql,param);
if(dr.Read())
{
info.id = id;
info.ktitle = dr.IsDBNull(1)?"":dr.GetString(1);
info.ktype = dr.IsDBNull(2)?"":dr.GetString(2);
info.kkeywords = dr.IsDBNull(3)?"":dr.GetString(3);
// byte[] blob = dr.IsDBNull(4)?new byte[]{}:(byte[])dr["kcontent"];
info.kcontent =dr.IsDBNull(4)?"":dr.GetString(4);
// info.kcontent = dr.IsDBNull(4)?"":dr.GetString(4);
info.kwid = dr.IsDBNull(5)?"":dr.GetString(5);
info.kwname = dr.IsDBNull(6)?"":dr.GetString(6);
info.kwdate = dr.IsDBNull(7)?"":dr.GetDateTime(7).ToShortDateString();
info.oamark = dr.IsDBNull(8)?0:dr.GetInt32(8);
info.kaid = dr.IsDBNull(9)?"":dr.GetString(9);
info.kaname = dr.IsDBNull(10)?"":dr.GetString(10);
info.oamount = dr.IsDBNull(11)?0:dr.GetInt32(11);
info.iamount = dr.IsDBNull(12)?0:dr.GetInt32(12);
info.camount = dr.IsDBNull(13)?0:dr.GetInt32(13);
}
dr.Close();
}
catch(Exception e)
{
throw e;
}
return info;
}
public ArrayList getHotspotKnowledge(int num)
{
ArrayList list = new ArrayList();
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";
SqlParameter [] param = new SqlParameter[]
{
new SqlParameter("@num",SqlDbType.Int,4),
};
param[0].Value = num;
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(4);
info.kwname = dr.IsDBNull(5)?"":dr.GetString(5);
info.kwdate = dr.IsDBNull(6)?"":dr.GetDateTime(6).ToShortDateString();
info.oamark = dr.IsDBNull(7)?0:dr.GetInt32(7);
info.kaid = dr.IsDBNull(8)?"":dr.GetString(8);
info.kaname = dr.IsDBNull(9)?"":dr.GetString(9);
info.oamount = dr.IsDBNull(10)?0:dr.GetInt32(10);
info.iamount = dr.IsDBNull(11)?0:dr.GetInt32(11);
info.camount = dr.IsDBNull(12)?0:dr.GetInt32(12);
list.Add(info);
}
dr.Close();
}
catch(Exception e)
{
Console.WriteLine(e.Message.ToString());
Console.WriteLine(e.StackTrace);
throw e;
}
return list;
}
public int delKnowledgeByIds(string ids)
{
string delSql = "DELETE FROM knowledge WHERE id IN("+ids+")";
try
{
return OraHelper.SqlserverExecuteNonQuery(OraHelper.GetSqlserverConnection(),CommandType.Text,delSql,null);
}
catch(Exception e)
{
throw e;
}
finally
{
;
}
}
public ArrayList queryKnowledge(KnowledgeInfo info)
{
ArrayList list = new ArrayList();
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
{
SqlDataReader dr = OraHelper.SqlserverExecuteReader(OraHelper.GetSqlserverConnection(),CommandType.Text,selSql.ToString(),null);
while(dr.Read())
{
KnowledgeInfo ninfo = new KnowledgeInfo();
ninfo.id = dr.IsDBNull(0)?0:dr.GetInt32(0);;
ninfo.ktitle = dr.IsDBNull(1)?"":dr.GetString(1);
ninfo.ktype = dr.IsDBNull(2)?"":dr.GetString(2);
ninfo.kkeywords = dr.IsDBNull(3)?"":dr.GetString(3);
// info.kcontent = dr.IsDBNull(4)?"":dr.GetString(4);
ninfo.kwid = dr.IsDBNull(4)?"":dr.GetString(4);
ninfo.kwname = dr.IsDBNull(5)?"":dr.GetString(5);
ninfo.kwdate = dr.IsDBNull(6)?"":dr.GetDateTime(6).ToShortDateString();
ninfo.oamark = dr.IsDBNull(7)?0:dr.GetInt32(7);
ninfo.kaid = dr.IsDBNull(8)?"":dr.GetString(8);
ninfo.kaname = dr.IsDBNull(9)?"":dr.GetString(9);
ninfo.oamount = dr.IsDBNull(10)?0:dr.GetInt32(10);
ninfo.iamount = dr.IsDBNull(11)?0:dr.GetInt32(11);
ninfo.camount = dr.IsDBNull(12)?0:dr.GetInt32(12);
list.Add(ninfo);
}
dr.Close();
}
catch(Exception e)
{
Console.WriteLine(e.Message.ToString());
Console.WriteLine(e.StackTrace);
Console.WriteLine(selSql.ToString());
Console.WriteLine(e.TargetSite);
throw e;
}
return list;;
}
public int updateKnowledge(KnowledgeInfo info)
{
if( info == null)
throw new Exception("The Knowledge Object is null,Can't Save Null object!");
string updateSql = "UPDATE knowledge SET ktitle=@ktitle,ktype=@ktype,kkeywords=@kkeywords,kcontent=@kcontent,kwid=@kwid,kwname=@kwname,kwdate=@kwdate,oamark=@oamark,kaid=@kaid,kaname=@kaname WHERE id=@id";
SqlParameter [] param = new SqlParameter[]
{
new SqlParameter("@id",SqlDbType.Int,4),
new SqlParameter("@ktitle",SqlDbType.VarChar,100),
new SqlParameter("@ktype",SqlDbType.VarChar,100),
new SqlParameter("@kkeywords",SqlDbType.VarChar,200),
new SqlParameter("@kcontent",SqlDbType.NText,20000),
new SqlParameter("@kwid",SqlDbType.VarChar,60),
new SqlParameter("@kwname",SqlDbType.VarChar,60),
new SqlParameter("@kwdate",SqlDbType.DateTime,8),
new SqlParameter("@oamark",SqlDbType.Int,4),
new SqlParameter("@kaid",SqlDbType.VarChar,60),
new SqlParameter("@kaname",SqlDbType.VarChar,60),
};
param[0].Value = info.id;
param[1].Value = info.ktitle;
param[2].Value = info.ktype;
param[3].Value = info.kkeywords;
param[4].Value = info.kcontent;
param[5].Value = info.kwid;
param[6].Value = info.kwname;
param[7].Value = info.kwdate;
param[8].Value = info.oamark;
param[9].Value = info.kaid;
param[10].Value = info.kaname;
try
{
// byte[] blob = System.Text.Encoding.Default.GetBytes(info.kcontent);
// this.updateKnowledgeContent(info.id,blob);
return OraHelper.SqlserverExecuteNonQuery(OraHelper.GetSqlserverConnection(),CommandType.Text,updateSql,param);
}
catch(Exception e)
{
throw e;
}
finally
{
;
}
}
public ArrayList getKnowledgeByKType(int tid)
{
ArrayList list = new ArrayList();
string selSql = "SELECT id,ktitle,ktype,kkeywords,kwid,kwname,kwdate,oamark,kaid,kaname,"+
"oamount,iamount,camount FROM knowledge WHERE ktype LIKE '%,"+tid+",%'";
try
{
SqlDataReader dr = OraHelper.SqlserverExecuteReader(OraHelper.GetSqlserverConnection(),CommandType.Text,selSql,null);
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(4);
info.kwname = dr.IsDBNull(5)?"":dr.GetString(5);
info.kwdate = dr.IsDBNull(6)?"":dr.GetDateTime(6).ToShortDateString();
info.oamark = dr.IsDBNull(7)?0:dr.GetInt32(7);
info.kaid = dr.IsDBNull(8)?"":dr.GetString(8);
info.kaname = dr.IsDBNull(9)?"":dr.GetString(9);
info.oamount = dr.IsDBNull(10)?0:dr.GetInt32(10);
info.iamount = dr.IsDBNull(11)?0:dr.GetInt32(11);
info.camount = dr.IsDBNull(12)?0:dr.GetInt32(12);
list.Add(info);
}
dr.Close();
}
catch(Exception e)
{
throw e;
}
return list;
}
public int addKnowledge(KnowledgeInfo info)
{
if( info == null)
throw new Exception("The Knowledge Object is null,Can't Save Null object!");
// info.id = OraHelper.createSequence("se_knowledge");
string addSql = "INSERT INTO knowledge(ktitle,ktype,kkeywords,kcontent,kwid,kwname,kwdate,oamark,kaid,kaname) VALUES(@ktitle,@ktype,@kkeywords,@kcontent,@kwid,@kwname,@kwdate,@oamark,@kaid,@kaname)";
SqlParameter [] param = new SqlParameter[]
{
new SqlParameter("@ktitle",SqlDbType.VarChar,100),
new SqlParameter("@ktype",SqlDbType.VarChar,100),
new SqlParameter("@kkeywords",SqlDbType.VarChar,200),
new SqlParameter("@kcontent",SqlDbType.NText,20000),
// new SqlParameter("@kcontent",SqlDbType.Clob,System.Text.Encoding.Default.GetByteCount(info.kcontent)),
new SqlParameter("@kwid",SqlDbType.VarChar,60),
new SqlParameter("@kwname",SqlDbType.VarChar,60),
new SqlParameter("@kwdate",SqlDbType.DateTime,8),
new SqlParameter("@oamark",SqlDbType.Int,4),
new SqlParameter("@kaid",SqlDbType.VarChar,60),
new SqlParameter("@kaname",SqlDbType.VarChar,60),
// new SqlParameter("@kclength",SqlDbType.Int32,32),
};
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -