?? dbservicenote.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 DBServiceNote:ISPServiceNote
{
public DBServiceNote()
{
//
// TODO: 在此處添加構造函數邏輯
//
}
#region ISPServiceNote 成員
public void updateSPServiceNote(SPServiceNoteInfo info)
{
if( info == null)
throw new Exception("The SPServiceNote Object is null,Can't Save Null object!");
string updateSql = "UPDATE spservicenote SET sid=:sid,sname=:sname,wbid=:wbid,sdate=to_date(:sdate,'yyyy-mm-dd'),cid=:cid,cname=:cname,scontent=:scontent,grade=:grade,ccomment=:ccomment WHERE id=:id";
OracleParameter [] param = new OracleParameter[]
{
new OracleParameter(":sid",OracleType.Int32,10),
new OracleParameter(":sname",OracleType.VarChar,100),
new OracleParameter(":wbid",OracleType.Int32,10),
new OracleParameter(":sdate",OracleType.VarChar,20),
new OracleParameter(":cid",OracleType.Int32,10),
new OracleParameter(":cname",OracleType.VarChar,60),
new OracleParameter(":scontent",OracleType.VarChar,1000),
new OracleParameter(":grade",OracleType.Int32,10),
new OracleParameter(":ccomment",OracleType.VarChar,1000),
new OracleParameter(":id",OracleType.Int32,10),
};
param[0].Value = info.sid;
param[1].Value = info.sname;
param[2].Value = info.wbid;
param[3].Value = info.sdate;
param[4].Value = info.cid;
param[5].Value = info.cname;
param[6].Value = info.scontent;
param[7].Value = info.grade;
param[8].Value = info.comment;
param[9].Value = info.id;
try
{
OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,updateSql,param);
}
catch(Exception e)
{
throw e;
}
finally
{
;
}
}
public SPServiceNoteInfo getSPServiceNoteById(int id)
{
SPServiceNoteInfo info = new SPServiceNoteInfo();
string selSql = "SELECT id,sid,sname,wbid,sdate,cid,cname,scontent,grade,ccomment,gshow FROM view_spservicenote_grade 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.sid = dr.IsDBNull(1)?0:dr.GetInt32(1);
info.sname = dr.IsDBNull(2)?"":dr.GetString(2);
info.wbid = dr.IsDBNull(3)?0:dr.GetInt32(3);
info.sdate = dr.IsDBNull(4)?"":dr.GetDateTime(4).ToShortDateString();
info.cid = dr.IsDBNull(5)?0:dr.GetInt32(5);
info.cname = dr.IsDBNull(6)?"":dr.GetString(6);
info.scontent = dr.IsDBNull(7)?"":dr.GetString(7);
info.grade = dr.IsDBNull(8)?0:dr.GetInt32(8);
info.comment = dr.IsDBNull(9)?"":dr.GetString(9);
info.gshow = dr.IsDBNull(10)?"":dr.GetString(10);
}
dr.Close();
}
catch(Exception e)
{
throw e;
}
return info;
}
public void delSPServiceNoteById(string ids)
{
string delSql = "DELETE FROM spservicenote WHERE id IN("+ids+")";
try
{
OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,delSql,null);
}
catch(Exception e)
{
throw e;
}
finally
{
;
}
}
public void delSPServiceNoteBySIds(string sids)
{
string delSql = "DELETE FROM spservicenote WHERE sid IN("+sids+")";
try
{
OraHelper.ExecuteNonQuery(OraHelper.GetOracleConnection(),CommandType.Text,delSql,null);
}
catch(Exception e)
{
throw e;
}
finally
{
;
}
}
public ArrayList getSPServiceNoteBySId(int sid)
{
ArrayList list = new ArrayList();
string selSql = "SELECT id,sid,sname,wbid,sdate,cid,cname,scontent,grade,ccomment,gshow FROM view_spservicenote_grade WHERE sid=:sid ORDER BY sdate DESC";
OracleParameter [] param = new OracleParameter[]
{
new OracleParameter(":sid",OracleType.Int32,10),
};
param[0].Value = sid;
try
{
OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,param);
while(dr.Read())
{
SPServiceNoteInfo info = new SPServiceNoteInfo();
info.id = dr.IsDBNull(0)?0:dr.GetInt32(0);
info.sid = dr.IsDBNull(1)?0:dr.GetInt32(1);
info.sname = dr.IsDBNull(2)?"":dr.GetString(2);
info.wbid = dr.IsDBNull(3)?0:dr.GetInt32(3);
info.sdate = dr.IsDBNull(4)?"":dr.GetDateTime(4).ToShortDateString();
info.cid = dr.IsDBNull(5)?0:dr.GetInt32(5);
info.cname = dr.IsDBNull(6)?"":dr.GetString(6);
info.scontent = dr.IsDBNull(7)?"":dr.GetString(7);
info.grade = dr.IsDBNull(8)?0:dr.GetInt32(8);
info.comment = dr.IsDBNull(9)?"":dr.GetString(9);
info.gshow = dr.IsDBNull(10)?"":dr.GetString(10);
list.Add(info);
}
dr.Close();
}
catch(Exception e)
{
Console.WriteLine(e.Message.ToString());
Console.WriteLine(e.StackTrace);
throw e;
}
return list;
}
public ArrayList getSPServiceNoteByWBId(int wbid)
{
ArrayList list = new ArrayList();
string selSql = "SELECT id,sid,sname,wbid,sdate,cid,cname,scontent,grade,ccomment,gshow FROM view_spservicenote_grade WHERE wbid=:wbid ORDER BY id ASC";
OracleParameter [] param = new OracleParameter[]
{
new OracleParameter(":wbid",OracleType.Int32,10),
};
param[0].Value = wbid;
try
{
OracleDataReader dr = OraHelper.ExecuteReader(OraHelper.GetOracleConnection(),CommandType.Text,selSql,param);
while(dr.Read())
{
SPServiceNoteInfo info = new SPServiceNoteInfo();
info.id = dr.IsDBNull(0)?0:dr.GetInt32(0);
info.sid = dr.IsDBNull(1)?0:dr.GetInt32(1);
info.sname = dr.IsDBNull(2)?"":dr.GetString(2);
info.wbid = dr.IsDBNull(3)?0:dr.GetInt32(3);
info.sdate = dr.IsDBNull(4)?"":dr.GetDateTime(4).ToShortDateString();
info.cid = dr.IsDBNull(5)?0:dr.GetInt32(5);
info.cname = dr.IsDBNull(6)?"":dr.GetString(6);
info.scontent = dr.IsDBNull(7)?"":dr.GetString(7);
info.grade = dr.IsDBNull(8)?0:dr.GetInt32(8);
info.comment = dr.IsDBNull(9)?"":dr.GetString(9);
info.gshow = dr.IsDBNull(10)?"":dr.GetString(10);
list.Add(info);
}
dr.Close();
}
catch(Exception e)
{
throw e;
}
return list;
}
public int addSPServiceNote(SPServiceNoteInfo info)
{
if( info == null)
throw new Exception("The SPServiceNote Object is null,Can't Save Null object!");
info.id = OraHelper.createSequence("se_spservicenote");
// Console.WriteLine(info);
string addSql = "INSERT INTO spservicenote(id,sid,sname,wbid,sdate,cid,cname,scontent,grade,ccomment) VALUES(:id,:sid,:sname,:wbid,to_date(:sdate,'yyyy-mm-dd'),:cid,:cname,:scontent,:grade,:ccomment)";
OracleParameter [] param = new OracleParameter[]
{
new OracleParameter(":id",OracleType.Int32,10),
new OracleParameter(":sid",OracleType.Int32,10),
new OracleParameter(":sname",OracleType.VarChar,100),
new OracleParameter(":wbid",OracleType.Int32,10),
new OracleParameter(":sdate",OracleType.VarChar,20),
new OracleParameter(":cid",OracleType.Int32,10),
new OracleParameter(":cname",OracleType.VarChar,60),
new OracleParameter(":scontent",OracleType.VarChar,1000),
new OracleParameter(":grade",OracleType.Int32,10),
new OracleParameter(":ccomment",OracleType.VarChar,1000),
};
param[0].Value = info.id;
param[1].Value = info.sid;
param[2].Value = info.sname;
param[3].Value = info.wbid;
param[4].Value = info.sdate;
param[5].Value = info.cid;
param[6].Value = info.cname;
param[7].Value = info.scontent;
param[8].Value = info.grade;
param[9].Value = info.comment;
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 DataSet getSPServiceNoteByWBId_DataSet(int wbid,string tableName)
{
string selSql = "SELECT id,sid,sname,wbid,sdate,cid,cname,scontent,grade,ccomment,gshow FROM view_spservicenote_grade WHERE wbid=:wbid ORDER BY id ASC";
OracleParameter [] param = new OracleParameter[]
{
new OracleParameter(":wbid",OracleType.Int32,10),
};
param[0].Value = wbid;
try
{
return OraHelper.ExecuteDataSet(OraHelper.GetOracleConnection(),CommandType.Text,tableName,selSql,param);
}
catch(Exception e)
{
throw e;
}
}
public DataSet getSPServiceNoteBySId_DataSet(int sid,string tableName)
{
string selSql = "SELECT id,sid,sname,wbid,sdate,cid,cname,scontent,grade,ccomment,gshow FROM view_spservicenote_grade WHERE sid=:sid ORDER BY sdate DESC";
OracleParameter [] param = new OracleParameter[]
{
new OracleParameter(":sid",OracleType.Int32,10),
};
param[0].Value = sid;
try
{
return OraHelper.ExecuteDataSet(OraHelper.GetOracleConnection(),CommandType.Text,tableName,selSql,param);
}
catch(Exception e)
{
Console.WriteLine(e.Message.ToString());
Console.WriteLine(e.StackTrace);
throw e;
}
}
#endregion
}
}
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -