📄 dbserviceprovider.cs
字号:
}
public int updateServiceProvider(ServiceProviderInfo info)
{
if( info == null)
throw new Exception("The ServiceProvider Object is null,Can't Save Null object!");
string updateSql = "UPDATE serviceprovider SET sname=:sname,stype=:stype,sdate=to_date(:sdate,'yyyy-mm-dd'),slinkname=:slinkname,stelephone=:stelephone,sfax=:sfax,semail=:semail,sweb=:sweb,"+
"ctype=:ctype,ckeywords=:ckeywords,saddress=:saddress,position=:position,sbrief=:sbrief,sgood=:sgood,sbad=:sbad WHERE id=:id";
OracleParameter [] param = new OracleParameter[]
{
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),
new OracleParameter(":id",OracleType.Int32,10),
};
param[0].Value = info.sname;
param[1].Value = info.stype;
param[2].Value = info.sdate;
param[3].Value = info.slinkname;
param[4].Value = info.stelephone;
param[5].Value = info.sfax;
param[6].Value = info.semail;
param[7].Value = info.sweb;
param[8].Value = info.ctype;
param[9].Value = info.ckeywords;
param[10].Value = info.saddress;
param[11].Value = info.position;
param[12].Value = info.sbrief;
param[13].Value = info.sgood;
param[14].Value = info.sbad;
param[15].Value = info.id;
try
{
return OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,updateSql,param);
}
catch(Exception e)
{
throw e;
}
finally
{
;
}
}
public ArrayList getServiceProviderByCtype(int ctype)
{
ArrayList list = new ArrayList();
string selSql = "SELECT id,sname,stype,sdate,slinkname,stelephone,sfax,semail,sweb,ctype,ckeywords,saddress,position,sbrief,sgood,sbad FROM serviceprovider WHERE ctype LIKE '%,"+ctype+",%'";
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);
list.Add(info);
}
dr.Close();
}
catch(Exception e)
{
throw e;
}
return list;
}
public int delServiceProvider(string ids)
{
string delSql = "DELETE FROM serviceprovider WHERE id IN("+ids+")";
try
{
return OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,delSql,null);
}
catch(Exception e)
{
throw e;
}
finally
{
;
}
}
public Hashtable getSPType(){
Hashtable ht = new Hashtable();
string selSql = "SELECT id,stname FROM sptype";
try
{
OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,null);
while(dr.Read())
{
ht.Add(dr.GetInt32(0),dr.GetString(1));
}
dr.Close();
}
catch(Exception e)
{
throw e;
}
return ht;
}
public DataSet getServiceProviderByCtype_DataSet(int ctype,string tableName)
{
string selSql = "SELECT id,sname,stype,sdate,slinkname,stelephone,sfax,semail,sweb,ctype,ckeywords,saddress,position,sbrief,sgood,sbad FROM serviceprovider WHERE ctype LIKE '%,"+ctype+",%'";
try
{
return OraHelper.ExecuteDataSet(OraHelper.GetOracleConnection(),CommandType.Text,tableName,selSql,null);
}
catch(Exception e)
{
throw e;
}
}
public DataSet getServiceProviderByCkeywords_DataSet(string keywords,string tableName)
{
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
{
return OraHelper.ExecuteDataSet(OraHelper.GetOracleConnection(),CommandType.Text,tableName,selSql,null);
}
catch(Exception e)
{
throw e;
}
}
public DataSet queryServiceProvider_DataSet(ServiceProviderInfo info,string tableName)
{
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("'","''")+"%'");
}
Console.WriteLine(selSql.ToString());
try
{
return OraHelper.ExecuteDataSet(OraHelper.GetOracleConnection(),CommandType.Text,tableName,selSql.ToString(),null);
}
catch(Exception e)
{
throw e;
}
}
public DataSet getSPType_DataSet(string tableName)
{
string selSql = "SELECT id,stname FROM sptype";
try
{
return OraHelper.ExecuteDataSet(OraHelper.GetOracleConnection(),CommandType.Text,tableName,selSql,null);
}
catch(Exception e)
{
throw e;
}
}
public ArrayList getServiceProviderByPosition(int position)
{
ArrayList list = new ArrayList();
string selSql = "SELECT id,sname,stype,sdate,slinkname,stelephone,sfax,semail,sweb,ctype,ckeywords,saddress,position,sbrief,sgood,sbad FROM serviceprovider WHERE position = :position";
OracleParameter [] param = new OracleParameter[]
{
new OracleParameter(":position",OracleType.Int32,10),
};
param[0].Value = position;
try
{
OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,param);
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);
list.Add(info);
}
dr.Close();
}
catch(Exception e)
{
throw e;
}
return list;
}
#endregion
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -