?? dbserviceprovider.cs
字號:
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 DBServiceProvider:CallCenter.IDAL.IServiceProvider
{
public DBServiceProvider()
{
//
// TODO: 在此處添加構造函數邏輯
//
}
#region IServiceProvider 成員
public int addServiceProvider(ServiceProviderInfo info)
{
if( info == null)
throw new Exception("The ServiceProvider Object is null,Can't Save Null object!");
info.id = OraHelper.createSequence("se_serviceprovider");
string addSql = "INSERT INTO serviceprovider(id,sname,stype,sdate,slinkname,stelephone,sfax,semail,sweb,ctype,ckeywords,saddress,position,sbrief,sgood,sbad) VALUES(:id,:sname,:stype,to_date(:sdate,'yyyy-mm-dd'),:slinkname,:stelephone,:sfax,:semail,:sweb,:ctype,:ckeywords,:saddress,:position,:sbrief,:sgood,:sbad)";
OracleParameter [] param = new OracleParameter[]
{
new OracleParameter(":id",OracleType.Int32,10),
new OracleParameter(":sname",OracleType.VarChar,60),
new OracleParameter(":stype",OracleType.Int32,10),
new OracleParameter(":sdate",OracleType.VarChar,20),
new OracleParameter(":slinkname",OracleType.VarChar,60),
new OracleParameter(":stelephone",OracleType.VarChar,60),
new OracleParameter(":sfax",OracleType.VarChar,60),
new OracleParameter(":semail",OracleType.VarChar,60),
new OracleParameter(":sweb",OracleType.VarChar,60),
new OracleParameter(":ctype",OracleType.VarChar,100),
new OracleParameter(":ckeywords",OracleType.VarChar,200),
new OracleParameter(":saddress",OracleType.VarChar,100),
new OracleParameter(":position",OracleType.Int32,10),
new OracleParameter(":sbrief",OracleType.VarChar,2000),
new OracleParameter(":sgood",OracleType.Int32,10),
new OracleParameter(":sbad",OracleType.Int32,10),
};
param[0].Value = info.id;
param[1].Value = info.sname;
param[2].Value = info.stype;
param[3].Value = info.sdate;
param[4].Value = info.slinkname;
param[5].Value = info.stelephone;
param[6].Value = info.sfax;
param[7].Value = info.semail;
param[8].Value = info.sweb;
param[9].Value = info.ctype;
param[10].Value = info.ckeywords;
param[11].Value = info.saddress;
param[12].Value = info.position;
param[13].Value = info.sbrief;
param[14].Value = info.sgood;
param[15].Value = info.sbad;
try
{
OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,addSql,param);
}
catch(Exception e)
{
Console.WriteLine(e.Message.ToString());
Console.WriteLine(e.StackTrace);
throw e;
}
finally
{
;
}
return info.id;
}
public ServiceProviderInfo getServiceProviderById(int id)
{
ServiceProviderInfo info = new ServiceProviderInfo();
string selSql = "SELECT id,sname,stype,sdate,slinkname,stelephone,sfax,semail,sweb,ctype,ckeywords,saddress,position,sbrief,sgood,sbad,stname FROM view_ser_pro_type WHERE id=:id";
OracleParameter [] param = new OracleParameter[]
{
new OracleParameter(":id",OracleType.Int32,10),
};
param[0].Value = id;
try
{
OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,param);
if(dr.Read())
{
info.id = id;
info.sname=dr.IsDBNull(1)?"":dr.GetString(1);
info.stype=dr.IsDBNull(2)?0:dr.GetInt32(2);
info.sdate = dr.IsDBNull(3)?"":dr.GetDateTime(3).ToShortDateString();
info.slinkname=dr.IsDBNull(4)?"":dr.GetString(4);
info.stelephone=dr.IsDBNull(5)?"":dr.GetString(5);
info.sfax=dr.IsDBNull(6)?"":dr.GetString(6);
info.semail=dr.IsDBNull(7)?"":dr.GetString(7);
info.sweb=dr.IsDBNull(8)?"":dr.GetString(8);
info.ctype=dr.IsDBNull(9)?"":dr.GetString(9);
info.ckeywords=dr.IsDBNull(10)?"":dr.GetString(10);
info.saddress=dr.IsDBNull(11)?"":dr.GetString(11);
info.position=dr.IsDBNull(12)?0:dr.GetInt32(12);
info.sbrief=dr.IsDBNull(13)?"":dr.GetString(13);
info.sgood=dr.IsDBNull(14)?0:dr.GetInt32(14);;
info.sbad=dr.IsDBNull(15)?0:dr.GetInt32(15);
info.stname=dr.IsDBNull(16)?"":dr.GetString(16);
}
dr.Close();
}
catch(Exception e)
{
throw e;
}
return info;
}
public ArrayList getServiceProviderByCkeywords(string keywords)
{
ArrayList list = new ArrayList();
string selSql = "SELECT id,sname,stype,sdate,slinkname,stelephone,sfax,semail,sweb,ctype,ckeywords,saddress,position,sbrief,sgood,sbad,stname FROM view_ser_pro_type WHERE ckeywords LIKE '%"+keywords.Replace("'","''")+"%'";
try
{
OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,null);
while(dr.Read())
{
ServiceProviderInfo info = new ServiceProviderInfo();
info.id = dr.IsDBNull(0)?0:dr.GetInt32(0);
info.sname=dr.IsDBNull(1)?"":dr.GetString(1);
info.stype=dr.IsDBNull(2)?0:dr.GetInt32(2);
info.sdate = dr.IsDBNull(3)?"":dr.GetDateTime(3).ToShortDateString();
info.slinkname=dr.IsDBNull(4)?"":dr.GetString(4);
info.stelephone=dr.IsDBNull(5)?"":dr.GetString(5);
info.sfax=dr.IsDBNull(6)?"":dr.GetString(6);
info.semail=dr.IsDBNull(7)?"":dr.GetString(7);
info.sweb=dr.IsDBNull(8)?"":dr.GetString(8);
info.ctype=dr.IsDBNull(9)?"":dr.GetString(9);
info.ckeywords=dr.IsDBNull(10)?"":dr.GetString(10);
info.saddress=dr.IsDBNull(11)?"":dr.GetString(11);
info.position=dr.IsDBNull(12)?0:dr.GetInt32(12);
info.sbrief=dr.IsDBNull(13)?"":dr.GetString(13);
info.sgood=dr.IsDBNull(14)?0:dr.GetInt32(14);;
info.sbad=dr.IsDBNull(15)?0:dr.GetInt32(15);
info.stname=dr.IsDBNull(16)?"":dr.GetString(16);
list.Add(info);
}
dr.Close();
}
catch(Exception e)
{
throw e;
}
return list;
}
public ArrayList queryServiceProvider(ServiceProviderInfo info)
{
ArrayList list = new ArrayList();
StringBuilder selSql = new StringBuilder("SELECT id,sname,stype,sdate,slinkname,stelephone,sfax,semail,sweb,ctype,ckeywords,saddress,position,sbrief,sgood,sbad,stname FROM view_ser_pro_type WHERE 1=1 ");
//服務商名稱
if(null != info.sname && !"".Equals(info.sname))
{
selSql.Append(" AND sname LIKE '%"+info.sname.Replace("'","''")+"%'");
}
//服務商分類
if(0 != info.stype)
{
selSql.Append(" AND stype ="+info.stype);
}
//聯系人
if(null != info.slinkname && !"".Equals(info.slinkname))
{
selSql.Append(" AND slinkname LIKE '%"+info.slinkname.Replace("'","''")+"%'");
}
//服務內容分類
if(null != info.ctype && !"".Equals(info.ctype))
{
selSql.Append(" AND ctype LIKE '%,"+info.ctype+",%'");
}
//服務內容關鍵字
if(null != info.ckeywords && !"".Equals(info.ckeywords))
{
selSql.Append(" AND ckeywords LIKE '%"+info.ckeywords.Replace("'","''")+"%'");
}
//服務商地址
if(null != info.saddress && !"".Equals(info.saddress))
{
selSql.Append(" AND saddress LIKE '%"+info.saddress.Replace("'","''")+"%'");
}
selSql.Append(" ORDER BY id DESC");
Console.WriteLine(selSql.ToString());
try
{
OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql.ToString(),null);
while(dr.Read())
{
ServiceProviderInfo ninfo = new ServiceProviderInfo();
ninfo.id = dr.IsDBNull(0)?0:dr.GetInt32(0);
ninfo.sname=dr.IsDBNull(1)?"":dr.GetString(1);
ninfo.stype=dr.IsDBNull(2)?0:dr.GetInt32(2);
ninfo.sdate = dr.IsDBNull(3)?"":dr.GetDateTime(3).ToShortDateString();
ninfo.slinkname=dr.IsDBNull(4)?"":dr.GetString(4);
ninfo.stelephone=dr.IsDBNull(5)?"":dr.GetString(5);
ninfo.sfax=dr.IsDBNull(6)?"":dr.GetString(6);
ninfo.semail=dr.IsDBNull(7)?"":dr.GetString(7);
ninfo.sweb=dr.IsDBNull(8)?"":dr.GetString(8);
ninfo.ctype=dr.IsDBNull(9)?"":dr.GetString(9);
ninfo.ckeywords=dr.IsDBNull(10)?"":dr.GetString(10);
ninfo.saddress=dr.IsDBNull(11)?"":dr.GetString(11);
ninfo.position=dr.IsDBNull(12)?0:dr.GetInt32(12);
ninfo.sbrief=dr.IsDBNull(13)?"":dr.GetString(13);
ninfo.sgood=dr.IsDBNull(14)?0:dr.GetInt32(14);;
ninfo.sbad=dr.IsDBNull(15)?0:dr.GetInt32(15);
ninfo.stname=dr.IsDBNull(16)?"":dr.GetString(16);
//Console.WriteLine(ninfo.stname);
list.Add(ninfo);
}
dr.Close();
}
catch(Exception e)
{
throw e;
}
return list;
}
public void changeServiceProviderGrade(int id, int gtype, int grade)
{
string updateSql = "";
if(0==gtype)
{
updateSql = "UPDATE serviceprovider SET sbad=sbad+:grade WHERE id=:id";
}
else if(1==gtype)
{
updateSql = "UPDATE serviceprovider SET sgood=sgood+:grade WHERE id=:id";
}
OracleParameter [] param = new OracleParameter[]
{
new OracleParameter(":grade",OracleType.Int32,10),
new OracleParameter(":id",OracleType.Int32,10),
};
param[0].Value = grade;
param[1].Value = id;
try
{
OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,updateSql,param);
}
catch(Exception e)
{
throw e;
}
finally
{
;
}
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -