public RequestEntity GetRequestById(string requestId)
{
_logger.Debug("GetRequestById.");
if (string.IsNullOrEmpty(requestId))
{
throw new ArgumentNullException("RequestId param can not be null.");
}
_logger.Debug("Params: requestId = {0};", requestId.ToString());
RequestEntity result = null;
OraCommand command = new OraCommand("REQ_PKG.SEL_REQ_BYID");
command.CommandType = CommandType.StoredProcedure;
command.AddDBParam(new OraParamInt32("p_id", ParameterDirection.Input, Convert.ToInt32(requestId)));
OraParamRefCursor refCur = new OraParamRefCursor("p_req_cr", ParameterDirection.Output);
command.AddDBParam(refCur);
Execute(command);
if (refCur.ParamValue != null && refCur.ParamValue.Rows.Count > 0)
{
result = ToRequest(refCur.ParamValue.Rows[0]);
result.Tags.Fill(GetRequestTags(requestId));
result.Attaches.Fill(GetRequestAttaches(requestId));
}
return(result);
}
public void TestFuncRefCur()
{
using (IDbConnection conn = new OraConnection())
{
conn.OpenConnection("chipanddale", "chipanddale", "xe");
IDbMgr dbManager = new OraDBMgr(conn, _LogMgr);
// function FuncRefCur(p_int32 in number, p_clob in out number, p_cur out sys_refcursor) return date;
IDbCommand command = new OraCommand("test_pkg.FuncRefCur");
command.CommandType = System.Data.CommandType.StoredProcedure;
OraParamDateTime returnParam = new OraParamDateTime("return", System.Data.ParameterDirection.ReturnValue, null);
command.AddDBParam(returnParam);
command.AddDBParam(new OraParamInt32("p_int32", System.Data.ParameterDirection.Input, null));
command.AddDBParam(new OraParamCLOB("p_clob", System.Data.ParameterDirection.InputOutput, "132"));
OraParamRefCursor refCur = new OraParamRefCursor("p_cur", System.Data.ParameterDirection.Output);
command.AddDBParam(refCur);
using (DbTransaction transaction = new DbTransaction(dbManager))
{
dbManager.Execute(command);
transaction.Success = true;
}
Debug.Assert(refCur.ParamValue.Rows.Count != 0, "p_cur");
Debug.Assert(refCur.ParamValue.Rows[0].ItemArray[0] != null, "p_cur[0][0]");
Debug.Assert(refCur.ParamValue.Rows[0].ItemArray[1] != null, "p_cur[0][1]");
Debug.Assert(refCur.ParamValue.Rows[0].ItemArray[2] != null, "p_cur[0][2]");
Debug.Assert(refCur.ParamValue.Rows[0].ItemArray[3] != null, "p_cur[0][3]");
Debug.Assert(command.Params["p_clob"].GetValue() != null, "p_clob");
Debug.Assert(command.Params["return"].GetValue() != null, "return");
}
}
public BindingCollection <TagEntity> GetRequestTags(string requestId)
{
_logger.Debug("GetRequestTags.");
if (string.IsNullOrEmpty(requestId))
{
throw new ArgumentNullException("RequestId param can not be null.");
}
_logger.Debug("Params: requestId = {0};", requestId.ToString());
BindingCollection <TagEntity> result = new BindingCollection <TagEntity>();
OraCommand command = new OraCommand("REQ_PKG.SEL_REQ_TAG");
command.CommandType = CommandType.StoredProcedure;
command.AddDBParam(new OraParamInt32("p_req_id", ParameterDirection.Input, Convert.ToInt32(requestId)));
OraParamRefCursor refCur = new OraParamRefCursor("p_reqTag_cr", ParameterDirection.Output);
command.AddDBParam(refCur);
Execute(command);
if (refCur.ParamValue != null)
{
foreach (DataRow reqRow in refCur.ParamValue.Rows)
{
result.Add(ToTag(reqRow));
}
}
return(result);
}
void CreateDBCommands()
{
_LoadCommand = new OraCommand(Core.OracleModule.Properties.Settings.Default.LoadCommand);
_LoadCommand.CommandType = CommandType.StoredProcedure;
_LoadCommand.AddDBParam(new OraParamString(Settings.Default.UsernameParam, System.Data.ParameterDirection.Input, ""));
_LoadCommand.AddDBParam(new OraParamString(Settings.Default.SectionParam, System.Data.ParameterDirection.Input, ""));
_LoadCommand.AddDBParam(new OraParamString(Settings.Default.SubSectionParam, System.Data.ParameterDirection.Input, ""));
_LoadCommand.AddDBParam(new OraParamRefCursor(Settings.Default.SettingCursorParam, System.Data.ParameterDirection.Output));
_SaveCommand = new OraCommand(Core.OracleModule.Properties.Settings.Default.SaveCommand);
_SaveCommand.CommandType = CommandType.StoredProcedure;
_SaveCommand.AddDBParam(new OraParamString(Settings.Default.UsernameParam, System.Data.ParameterDirection.Input, ""));
_SaveCommand.AddDBParam(new OraParamString(Settings.Default.SectionParam, System.Data.ParameterDirection.Input, ""));
_SaveCommand.AddDBParam(new OraParamString(Settings.Default.SubSectionParam, System.Data.ParameterDirection.Input, ""));
_SaveCommand.AddDBParam(new OraParamString(Settings.Default.SettingNameParam, System.Data.ParameterDirection.Input, ""));
_SaveCommand.AddDBParam(new OraParamString(Settings.Default.SettingValueParam, System.Data.ParameterDirection.Input, ""));
_SaveCommand.AddDBParam(new OraParamCLOB(Settings.Default.SettingValueCLOBParam, System.Data.ParameterDirection.Input, ""));
_SaveCommand.AddDBParam(new OraParamBLOB(Settings.Default.SettingValueBLOBParam, System.Data.ParameterDirection.Input, (byte[])null));
_ClearCommand = new OraCommand(Core.OracleModule.Properties.Settings.Default.ClearCommand);
_ClearCommand.CommandType = CommandType.StoredProcedure;
_ClearCommand.AddDBParam(new OraParamString(Settings.Default.UsernameParam, System.Data.ParameterDirection.Input, ""));
_ClearCommand.AddDBParam(new OraParamString(Settings.Default.SectionParam, System.Data.ParameterDirection.Input, ""));
}
public void AuthorizateUser(string name, string password)
{
try
{
_logger.Debug("AuthorizateUser.");
if (string.IsNullOrEmpty(name))
{
throw new ArgumentNullException("Name param can not be null.");
}
if (string.IsNullOrEmpty(password))
{
throw new ArgumentNullException("Password param can not be null.");
}
_logger.Debug("Params: name = {0};", name);
OraCommand command = new OraCommand("MAIN_PKG.CONNECT_TO_DB");
command.CommandType = CommandType.StoredProcedure;
OraParamInt32 returnParam = new OraParamInt32("return", ParameterDirection.ReturnValue, null);
command.AddDBParam(returnParam);
command.AddDBParam(new OraParamString("p_userName", ParameterDirection.Input, name));
command.AddDBParam(new OraParamString("p_password", ParameterDirection.Input, password));
Execute(command);
}
catch (Exception ex)
{
throw new AuthorizateExceptions(ex);
}
}
public void TestProcRefCur()
{
using (IDbConnection conn = new OraConnection())
{
conn.OpenConnection("chipanddale", "chipanddale", "xe");
IDbMgr dbManager = new OraDBMgr(conn, _LogMgr);
// procedure ProcRefCur (p_date out date, p_cur in out sys_refcursor) as
IDbCommand command = new OraCommand("test_pkg.ProcRefCur");
command.CommandType = System.Data.CommandType.StoredProcedure;
command.AddDBParam(new OraParamDateTime("p_date", System.Data.ParameterDirection.Output, null));
OraParamRefCursor refCur = new OraParamRefCursor("p_cur", System.Data.ParameterDirection.InputOutput);
command.AddDBParam(refCur);
using (DbTransaction transaction = new DbTransaction(dbManager))
{
dbManager.Execute(command);
transaction.Success = true;
}
Debug.Assert(refCur.ParamValue.Rows.Count != 0, "p_cur");
Debug.Assert(refCur.ParamValue.Rows[0].ItemArray[0] != null, "p_cur[0][0]");
Debug.Assert(refCur.ParamValue.Rows[0].ItemArray[1] != null, "p_cur[0][1]");
Debug.Assert(refCur.ParamValue.Rows[0].ItemArray[2] != null, "p_cur[0][2]");
Debug.Assert(command.Params["p_date"].GetValue() != null, "p_date");
object o = refCur.ParamValue.Rows[0]["blob_"];
byte[] ba = (byte[])o;
}
}
public BindingCollection <UserEntity> GetUsers()
{
_logger.Debug("GetUsers.");
BindingCollection <UserEntity> result = new BindingCollection <UserEntity>();
OraCommand command = new OraCommand("MAIN_PKG.SEL_USER");
command.CommandType = CommandType.StoredProcedure;
OraParamRefCursor refCur = new OraParamRefCursor("p_user_cr", ParameterDirection.Output);
command.AddDBParam(refCur);
Execute(command);
if (refCur.ParamValue != null)
{
foreach (DataRow userRow in refCur.ParamValue.Rows)
{
result.Add(new UserEntity()
{
Id = userRow["ID"].ToString(),
Login = userRow["USER_NAME"].ToString(),
Name = userRow["FULL_NAME"].ToString()
});
}
}
return(result);
}
public void UpdateApp(AppEntity app)
{
if (app == null)
{
throw new NullReferenceException("App param can not be null.");
}
_logger.Debug("UpdateApp. Id = {0}; Name = {1}", app.Id, app.Name);
OraCommand command = new OraCommand("MAIN_PKG.UPD_COMP");
command.CommandType = CommandType.StoredProcedure;
command.AddDBParam(new OraParamInt32("p_id", ParameterDirection.Input, Convert.ToInt32(app.Id)));
command.AddDBParam(new OraParamString("p_newName", ParameterDirection.Input, app.Name));
Execute(command);
}
public void UpdateTag(TagEntity tag)
{
if (tag == null)
{
throw new NullReferenceException("Tag param can not be null.");
}
_logger.Debug("UpdateTag. Id = {0}; Name = {1}", tag.Id, tag.Name);
OraCommand command = new OraCommand("MAIN_PKG.UPD_TAG");
command.CommandType = CommandType.StoredProcedure;
command.AddDBParam(new OraParamInt32("p_id", ParameterDirection.Input, Convert.ToInt32(tag.Id)));
command.AddDBParam(new OraParamString("p_newName", ParameterDirection.Input, tag.Name));
Execute(command);
}
public void TestFuncNullOutParams()
{
using (IDbConnection conn = new OraConnection())
{
conn.OpenConnection("chipanddale", "chipanddale", "xe");
IDbMgr dbManager = new OraDBMgr(conn, _LogMgr);
// function FuncNullOutParams (p_int32 out number, p_double out number, p_date out date, p_string out varchar2, p_clob out clob, p_blob out blob) return blob;
IDbCommand command = new OraCommand("test_pkg.FuncNullOutParams");
command.CommandType = System.Data.CommandType.StoredProcedure;
OraParamString returnParam = new OraParamString("return", System.Data.ParameterDirection.ReturnValue, null);
command.AddDBParam(returnParam);
command.AddDBParam(new OraParamInt32("p_int32", System.Data.ParameterDirection.Output, null));
command.AddDBParam(new OraParamDouble("p_double", System.Data.ParameterDirection.Output, null));
command.AddDBParam(new OraParamDateTime("p_date", System.Data.ParameterDirection.Output, null));
command.AddDBParam(new OraParamString("p_string", System.Data.ParameterDirection.Output, null, 2000));
command.AddDBParam(new OraParamCLOB("p_clob", System.Data.ParameterDirection.Output, null));
command.AddDBParam(new OraParamBLOB("p_blob", System.Data.ParameterDirection.Output, (byte[])null));
using (DbTransaction transaction = new DbTransaction(dbManager))
{
dbManager.Execute(command);
transaction.Success = true;
}
Debug.Assert(returnParam.ParamValue == null, "returnParam");
Debug.Assert(command.Params["p_int32"].GetValue() == null, "p_int32");
Debug.Assert(command.Params["p_double"].GetValue() == null, "p_double");
Debug.Assert(command.Params["p_date"].GetValue() == null, "p_date");
Debug.Assert(command.Params["p_string"].GetValue() == null, "p_string");
Debug.Assert(command.Params["p_clob"].GetValue() == null, "p_clob");
Debug.Assert(command.Params["p_blob"].GetValue() == null, "p_blob");
}
}
public void TestFuncAll()
{
using (IDbConnection conn = new OraConnection())
{
conn.OpenConnection("chipanddale", "chipanddale", "xe");
IDbMgr dbManager = new OraDBMgr(conn, _LogMgr);
// function FuncAll(p_int32 in out number, p_double in out number, p_date in out date, p_string in out varchar2, p_clob in out clob, p_blob in out blob, p_cur out sys_refcursor, p_cur2 in out sys_refcursor) return varchar2;
IDbCommand command = new OraCommand("test_pkg.FuncAll");
command.CommandType = System.Data.CommandType.StoredProcedure;
OraParamString returnParam = new OraParamString("return", System.Data.ParameterDirection.ReturnValue, null, 2000);
command.AddDBParam(returnParam);
command.AddDBParam(new OraParamInt32("p_int32", System.Data.ParameterDirection.InputOutput, null));
command.AddDBParam(new OraParamDouble("p_double", System.Data.ParameterDirection.InputOutput, -1.234));
command.AddDBParam(new OraParamDateTime("p_date", System.Data.ParameterDirection.InputOutput, DateTime.Now));
command.AddDBParam(new OraParamString("p_string", System.Data.ParameterDirection.InputOutput, "INPUT ", 2000));
command.AddDBParam(new OraParamCLOB("p_clob", System.Data.ParameterDirection.InputOutput, null));
command.AddDBParam(new OraParamBLOB("p_blob", System.Data.ParameterDirection.InputOutput, (byte[])null));
OraParamRefCursor refCur = new OraParamRefCursor("p_cur", System.Data.ParameterDirection.Output);
command.AddDBParam(refCur);
OraParamRefCursor refCur2 = new OraParamRefCursor("p_cur2", System.Data.ParameterDirection.InputOutput);
command.AddDBParam(refCur2);
using (DbTransaction transaction = new DbTransaction(dbManager))
{
dbManager.Execute(command);
transaction.Success = true;
}
Debug.Assert(returnParam.ParamValue != null, "returnParam");
Debug.Assert(command.Params["p_int32"].GetValue() != null, "p_int32");
Debug.Assert(command.Params["p_double"].GetValue() != null, "p_double");
Debug.Assert(command.Params["p_date"].GetValue() != null, "p_date");
Debug.Assert(command.Params["p_string"].GetValue() != null, "p_string");
Debug.Assert(command.Params["p_clob"].GetValue() != null, "p_clob");
Debug.Assert(command.Params["p_blob"].GetValue() != null, "p_blob");
Debug.Assert(refCur.ParamValue.Rows.Count != 0, "p_cur");
Debug.Assert(refCur.ParamValue.Rows[0].ItemArray[0] != null, "p_cur[0][0]");
Debug.Assert(refCur.ParamValue.Rows[0].ItemArray[1] != null, "p_cur[0][1]");
Debug.Assert(refCur.ParamValue.Rows[0].ItemArray[2] != null, "p_cur[0][2]");
Debug.Assert(refCur.ParamValue.Rows[0].ItemArray[3] != null, "p_cur[0][3]");
Debug.Assert(refCur2.ParamValue.Rows.Count != 0, "p_cur2");
Debug.Assert(refCur2.ParamValue.Rows[0].ItemArray[0] != null, "p_cur2[0][0]");
Debug.Assert(refCur2.ParamValue.Rows[0].ItemArray[1] != null, "p_cur2[0][1]");
Debug.Assert(refCur2.ParamValue.Rows[0].ItemArray[2] != null, "p_cur2[0][2]");
}
}
public void TestFuncInParams()
{
using (IDbConnection conn = new OraConnection())
{
conn.OpenConnection("chipanddale", "chipanddale", "xe");
IDbMgr dbManager = new OraDBMgr(conn, _LogMgr);
// function FuncInParams (p_int32 in number, p_double in number, p_date in date, p_string in varchar2, p_clob in clob, p_blob in blob) return clob;
IDbCommand command = new OraCommand("test_pkg.FuncInParams");
command.CommandType = System.Data.CommandType.StoredProcedure;
OraParamCLOB returnParam = new OraParamCLOB("return", System.Data.ParameterDirection.ReturnValue, null);
command.AddDBParam(returnParam);
command.AddDBParam(new OraParamInt32("p_int32", System.Data.ParameterDirection.Input, 1234));
command.AddDBParam(new OraParamDouble("p_double", System.Data.ParameterDirection.Input, 1234.23d));
command.AddDBParam(new OraParamDateTime("p_date", System.Data.ParameterDirection.Input, DateTime.Now));
command.AddDBParam(new OraParamString("p_string", System.Data.ParameterDirection.Input, "STRING qwe фывфыв"));
command.AddDBParam(new OraParamCLOB("p_clob", System.Data.ParameterDirection.Input, "CLOB 123123 qweqweqweq йцуйцуйу"));
command.AddDBParam(new OraParamBLOB("p_blob", System.Data.ParameterDirection.Input, new byte[] { 1, 2, 3, 4, 5, 6 }));
using (DbTransaction transaction = new DbTransaction(dbManager))
{
dbManager.Execute(command);
transaction.Success = true;
}
string retStr = returnParam.ParamValue;
Debug.Assert(!string.IsNullOrEmpty(retStr), "FuncInParams");
}
}
public void AddUser(UserEntity user)
{
if (user == null)
{
throw new NullReferenceException("user param can not be null.");
}
_logger.Debug("AddUser. Login = {0}; Name = {1}", user.Login, user.Name);
OraCommand command = new OraCommand("MAIN_PKG.INS_USER");
command.CommandType = CommandType.StoredProcedure;
command.AddDBParam(new OraParamString("p_name", ParameterDirection.Input, user.Login));
command.AddDBParam(new OraParamString("p_fullname", ParameterDirection.Input, user.Name));
Execute(command);
}
public void SetParam(string name, string value)
{
_logger.Debug("SetParam.");
if (string.IsNullOrEmpty(name))
{
throw new ArgumentNullException("Name param can not be null.");
}
_logger.Debug("Params: name = {0};", name);
OraCommand command = new OraCommand("USER_DATA_PKG.SET_FILTER_VALUE");
command.CommandType = CommandType.StoredProcedure;
command.AddDBParam(new OraParamString("p_filterName", ParameterDirection.Input, name));
command.AddDBParam(new OraParamString("p_filterValue", ParameterDirection.Input, value));
Execute(command);
}
public void InsReqTag(string requestId, TagEntity tag)
{
_logger.Debug("InsReqTag.");
if (string.IsNullOrEmpty(requestId))
{
throw new ArgumentNullException("RequestId param can not be null.");
}
if (tag == null)
{
throw new ArgumentNullException("Tag param can not be null.");
}
_logger.Debug("Params: requestId = {0}; tag = {1};", requestId, tag.ToInternalString());
OraCommand command = new OraCommand("REQ_PKG.INS_REQ_TAG");
command.CommandType = CommandType.StoredProcedure;
command.AddDBParam(new OraParamInt32("p_req_id", ParameterDirection.Input, Convert.ToInt32(requestId)));
command.AddDBParam(new OraParamInt32("p_tag_id", ParameterDirection.Input, tag.NumId));
Execute(command);
}
public void CopyReqAttach(string requestIdFrom, string requestIdTo)
{
_logger.Debug("CopyReqAttach.");
if (string.IsNullOrEmpty(requestIdFrom))
{
throw new ArgumentNullException("RequestIdFrom param can not be null.");
}
if (string.IsNullOrEmpty(requestIdTo))
{
throw new ArgumentNullException("RequestIdTo param can not be null.");
}
_logger.Debug("Params: requestIdFrom = {0}; requestIdTo = {1};", requestIdFrom, requestIdTo);
OraCommand command = new OraCommand("REQ_PKG.COPY_REQ_ATT");
command.CommandType = CommandType.StoredProcedure;
command.AddDBParam(new OraParamInt32("p_reqFrom_id", ParameterDirection.Input, Convert.ToInt32(requestIdFrom)));
command.AddDBParam(new OraParamInt32("p_reqTo_id", ParameterDirection.Input, Convert.ToInt32(requestIdTo)));
Execute(command);
}
public void UpdateUser(UserEntity user)
{
if (user == null)
{
throw new NullReferenceException("user param can not be null.");
}
_logger.Debug("UpdateUser. Id = {0};l Login = {1}; Name = {2}", user.Id, user.Login, user.Name);
OraCommand command = new OraCommand("MAIN_PKG.UPD_USER");
command.CommandType = CommandType.StoredProcedure;
command.AddDBParam(new OraParamInt32("p_id", ParameterDirection.Input, Convert.ToInt32(user.Id)));
command.AddDBParam(new OraParamString("p_newName", ParameterDirection.Input, user.Login));
command.AddDBParam(new OraParamString("p_newFullName", ParameterDirection.Input, user.Name));
command.AddDBParam(new OraParamString("p_newReceiveMail", ParameterDirection.Input, null));
command.AddDBParam(new OraParamString("p_newReceiveJabber", ParameterDirection.Input, null));
Execute(command);
}
public byte[] SelAttachBlob(string attachId)
{
_logger.Debug("SelAttachBlob.");
if (string.IsNullOrEmpty(attachId))
{
throw new ArgumentNullException("AttachId param can not be null.");
}
_logger.Debug("Params: attachId = {0};", attachId);
OraCommand command = new OraCommand("REQ_PKG.SEL_REQ_ATT_BLOB");
command.CommandType = CommandType.StoredProcedure;
OraParamBLOB returnParam = new OraParamBLOB("return", ParameterDirection.ReturnValue, (byte[])null);
command.AddDBParam(returnParam);
command.AddDBParam(new OraParamInt32("p_reqAtt_id", ParameterDirection.Input, Convert.ToInt32(attachId)));
Execute(command);
return(returnParam.ParamValue);
}
public void DelReqTag(string requestId, string tagId)
{
_logger.Debug("DelReqTag.");
if (string.IsNullOrEmpty(requestId))
{
throw new ArgumentNullException("RequestId param can not be null.");
}
if (string.IsNullOrEmpty(tagId))
{
throw new ArgumentNullException("TagId param can not be null.");
}
_logger.Debug("Params: requestId = {0};", requestId);
OraCommand command = new OraCommand("REQ_PKG.DEL_REQ_TAG");
command.CommandType = CommandType.StoredProcedure;
command.AddDBParam(new OraParamInt32("p_req_id", ParameterDirection.Input, Convert.ToInt32(requestId)));
command.AddDBParam(new OraParamInt32("p_tag_id", ParameterDirection.Input, Convert.ToInt32(tagId)));
Execute(command);
}
public void TestSQLQuery4()
{
using (IDbConnection conn = new OraConnection())
{
conn.OpenConnection("chipanddale", "chipanddale", "xe");
IDbMgr dbManager = new OraDBMgr(conn, _LogMgr);
IDbCommand command = new OraCommand("update test_tab set int32 = :param1 where string = :param2 returning count(*) into :param3");
command.CommandType = System.Data.CommandType.Text;
command.AddDBParam(new OraParamInt32(":param1", System.Data.ParameterDirection.Input, 222));
command.AddDBParam(new OraParamString(":param2", System.Data.ParameterDirection.Input, "123", 100));
command.AddDBParam(new OraParamInt32(":param3", System.Data.ParameterDirection.Output, null));
using (DbTransaction transaction = new DbTransaction(dbManager))
{
dbManager.Execute(command);
transaction.Success = true;
}
}
}
public void SendMessage(MessageEntity message)
{
_logger.Debug("SendMessage.");
if (message == null)
{
throw new ArgumentNullException("Message param can not be null.");
}
_logger.Debug("Params: Message = {0};", message.ToInternalString());
OraCommand command = new OraCommand("MAIN_PKG.INS_MESS");
command.CommandType = CommandType.StoredProcedure;
command.AddDBParam(new OraParamInt32("p_req_id", ParameterDirection.Input, Convert.ToInt32(message.Key)));
command.AddDBParam(new OraParamInt32("p_receiver", ParameterDirection.Input, message.Receiver.NumId));
command.AddDBParam(new OraParamString("p_subject", ParameterDirection.Input, message.Subject));
command.AddDBParam(new OraParamString("p_body", ParameterDirection.Input, message.Body));
command.AddDBParam(new OraParamInt32("p_type", ParameterDirection.Input, message.ChannelId));
Execute(command);
}
public void InsReqAttach(string requestId, AttachEntity attach)
{
_logger.Debug("InsReqAttach.");
if (string.IsNullOrEmpty(requestId))
{
throw new ArgumentNullException("RequestId param can not be null.");
}
if (attach == null)
{
throw new ArgumentNullException("Attach param can not be null.");
}
_logger.Debug("Params: requestId = {0}; attach = {1};", requestId, attach.ToInternalString());
OraCommand command = new OraCommand("REQ_PKG.INS_REQ_ATT");
command.CommandType = CommandType.StoredProcedure;
command.AddDBParam(new OraParamInt32("p_req_id", ParameterDirection.Input, Convert.ToInt32(requestId)));
command.AddDBParam(new OraParamString("p_att_name", ParameterDirection.Input, attach.Name));
command.AddDBParam(new OraParamBLOB("p_att_data", ParameterDirection.Input, attach.Blob));
Execute(command);
}
public void RemoveOrg(OrgEntity org)
{
if (org == null)
{
throw new NullReferenceException("Org param can not be null.");
}
_logger.Debug("RemoveUser. Id = {0}", org.Id);
OraCommand command = new OraCommand("MAIN_PKG.DEL_ORG");
command.CommandType = CommandType.StoredProcedure;
command.AddDBParam(new OraParamInt32("p_id", ParameterDirection.Input, Convert.ToInt32(org.Id)));
Execute(command);
}
public void DelReqAttach(string attachId)
{
_logger.Debug("DelRequest.");
if (string.IsNullOrEmpty(attachId))
{
throw new ArgumentNullException("AttachId param can not be null.");
}
_logger.Debug("Params: attachId = {0};", attachId);
OraCommand command = new OraCommand("REQ_PKG.DEL_REQ_ATT");
command.CommandType = CommandType.StoredProcedure;
command.AddDBParam(new OraParamInt32("p_att_id", ParameterDirection.Input, Convert.ToInt32(attachId)));
Execute(command);
}
public void AddApp(AppEntity app)
{
if (app == null)
{
throw new NullReferenceException("App param can not be null.");
}
_logger.Debug("AddApp. Name = {0}", app.Name);
OraCommand command = new OraCommand("MAIN_PKG.INS_COMP");
command.CommandType = CommandType.StoredProcedure;
command.AddDBParam(new OraParamString("p_name", ParameterDirection.Input, app.Name));
Execute(command);
}
public void AddTag(TagEntity tag)
{
if (tag == null)
{
throw new NullReferenceException("Tag param can not be null.");
}
_logger.Debug("AddTag. Name = {0}", tag.Name);
OraCommand command = new OraCommand("MAIN_PKG.INS_TAG");
command.CommandType = CommandType.StoredProcedure;
command.AddDBParam(new OraParamString("p_name", ParameterDirection.Input, tag.Name));
Execute(command);
}
public BindingCollection <RequestEntity> GetRequests(RequestListFilterEntity filter)
{
_logger.Debug("GetRequests.");
if (filter == null)
{
throw new ArgumentNullException("Filter param can not be null.");
}
_logger.Debug("Params: filter = {0};", filter.ToInternalString());
BindingCollection <RequestEntity> result = new BindingCollection <RequestEntity>();
_commonDbService.SetParam("FROM", filter.StartDateTimeString);
_commonDbService.SetParam("UNTIL", filter.StopDateTimeString);
_commonDbService.SetParam("STATUS", filter.StatusIdList);
_commonDbService.SetParam("ID_ORG", filter.OrganizationIdtString);
_commonDbService.SetParam("ID_COMP", filter.ApplicationIdtString);
_commonDbService.SetParam("ID_USER", filter.UserIdtString);
_commonDbService.SetParam("ID_CREATOR", filter.CreatorIdtString);
_commonDbService.SetParam("TAGS", filter.TagIdList);
OraCommand command = new OraCommand("REQ_PKG.SEL_REQ");
command.CommandType = CommandType.StoredProcedure;
OraParamRefCursor refCur = new OraParamRefCursor("p_req_cr", ParameterDirection.Output);
command.AddDBParam(refCur);
Execute(command);
if (refCur.ParamValue != null)
{
foreach (DataRow reqRow in refCur.ParamValue.Rows)
{
result.Add(ToRequest(reqRow));
}
}
return(result);
}
public void TestProcNullInParams()
{
using (IDbConnection conn = new OraConnection())
{
conn.OpenConnection("chipanddale", "chipanddale", "xe");
IDbMgr dbManager = new OraDBMgr(conn, _LogMgr);
// procedure ProcInParams (p_int32 in number, p_double in number, p_date in date, p_string in varchar2, p_clob in clob, p_blob in blob);
IDbCommand command = new OraCommand("test_pkg.ProcInParams");
command.CommandType = System.Data.CommandType.StoredProcedure;
command.AddDBParam(new OraParamInt32("p_int32", System.Data.ParameterDirection.Input, null));
command.AddDBParam(new OraParamDouble("p_double", System.Data.ParameterDirection.Input, null));
command.AddDBParam(new OraParamDateTime("p_date", System.Data.ParameterDirection.Input, null));
command.AddDBParam(new OraParamString("p_string", System.Data.ParameterDirection.Input, null));
command.AddDBParam(new OraParamCLOB("p_clob", System.Data.ParameterDirection.Input, null));
command.AddDBParam(new OraParamBLOB("p_blob", System.Data.ParameterDirection.Input, (byte[])null));
dbManager.StartTransaction();
dbManager.Execute(command);
dbManager.RollbackTransaction();
}
}
public void TestMultiTread2()
{
Thread thread1 = new Thread(() =>
{
using (IDbConnection conn = new OraConnection())
{
conn.OpenConnection("chipanddale", "chipanddale", "xe");
IDbMgr dbManager = new OraDBMgr(conn, _LogMgr);
IDbCommand command = new OraCommand("select * from all_objects");
command.CommandType = System.Data.CommandType.Text;
System.Data.DataTable tab = null;
for (int i = 0; i < 50; i++)
{
tab = dbManager.ExecuteSelect(command);
}
}
});
Thread thread2 = new Thread(() =>
{
using (IDbConnection conn = new OraConnection())
{
conn.OpenConnection("chipanddale", "chipanddale", "xe");
IDbMgr dbManager = new OraDBMgr(conn, _LogMgr);
IDbCommand command = new OraCommand("insert into test_tab (int32, double, string, datetime) values (1, 1.2, '123', sysdate)");
command.CommandType = System.Data.CommandType.Text;
using (DbTransaction transaction = new DbTransaction(dbManager))
{
for (int i = 0; i < 1000; i++)
{
dbManager.Execute(command);
}
transaction.Success = true;
}
}
});
Thread thread3 = new Thread(() =>
{
using (IDbConnection conn = new OraConnection())
{
conn.OpenConnection("chipanddale", "chipanddale", "xe");
IDbMgr dbManager = new OraDBMgr(conn, _LogMgr);
IDbCommand command = new OraCommand("begin execute immediate 'create or replace view test_view as select 1 col_name from dual'; end;");
command.CommandType = System.Data.CommandType.Text;
for (int i = 0; i < 100; i++)
{
dbManager.Execute(command);
}
}
});
Thread thread4 = new Thread(() =>
{
using (IDbConnection conn = new OraConnection())
{
conn.OpenConnection("chipanddale", "chipanddale", "xe");
IDbMgr dbManager = new OraDBMgr(conn, _LogMgr);
IDbCommand command = new OraCommand("test_pkg.FuncAll");
command.CommandType = System.Data.CommandType.StoredProcedure;
for (int i = 0; i < 100; i++)
{
command.Params.Clear();
OraParamString returnParam = new OraParamString("return", System.Data.ParameterDirection.ReturnValue, null, 2000);
command.AddDBParam(returnParam);
command.AddDBParam(new OraParamInt32("p_int32", System.Data.ParameterDirection.InputOutput, null));
command.AddDBParam(new OraParamDouble("p_double", System.Data.ParameterDirection.InputOutput, -1.234));
command.AddDBParam(new OraParamDateTime("p_date", System.Data.ParameterDirection.InputOutput, DateTime.Now));
command.AddDBParam(new OraParamString("p_string", System.Data.ParameterDirection.InputOutput, "INPUT ", 2000));
command.AddDBParam(new OraParamCLOB("p_clob", System.Data.ParameterDirection.InputOutput, null));
command.AddDBParam(new OraParamBLOB("p_blob", System.Data.ParameterDirection.InputOutput, (byte[])null));
OraParamRefCursor refCur = new OraParamRefCursor("p_cur", System.Data.ParameterDirection.Output);
command.AddDBParam(refCur);
OraParamRefCursor refCur2 = new OraParamRefCursor("p_cur2", System.Data.ParameterDirection.InputOutput);
command.AddDBParam(refCur2);
using (DbTransaction transaction = new DbTransaction(dbManager))
{
dbManager.Execute(command);
dbManager.Execute(command);
transaction.Success = true;
}
}
}
});
Thread thread5 = new Thread(() =>
{
using (IDbConnection conn = new OraConnection())
{
conn.OpenConnection("chipanddale", "chipanddale", "xe");
IDbMgr dbManager = new OraDBMgr(conn, _LogMgr);
IDbCommand command = new OraCommand("insert into test_tab (int32, double, string, datetime) values (1, 1.2, '123', sysdate)");
command.CommandType = System.Data.CommandType.Text;
using (DbTransaction transaction = new DbTransaction(dbManager))
{
for (int i = 0; i < 1000; i++)
{
dbManager.Execute(command);
}
transaction.Success = true;
}
}
});
Thread thread6 = new Thread(() =>
{
using (IDbConnection conn = new OraConnection())
{
conn.OpenConnection("chipanddale", "chipanddale", "xe");
IDbMgr dbManager = new OraDBMgr(conn, _LogMgr);
IDbCommand command = new OraCommand("insert into test_tab (int32, double, string, datetime) values (1, 1.2, '123', sysdate)");
command.CommandType = System.Data.CommandType.Text;
for (int i = 0; i < 1000; i++)
{
using (DbTransaction transaction = new DbTransaction(dbManager))
{
dbManager.Execute(command);
transaction.Success = true;
}
}
}
});
thread1.Start();
thread2.Start();
thread3.Start();
thread4.Start();
thread5.Start();
thread6.Start();
thread1.Join();
thread2.Join();
thread3.Join();
thread4.Join();
thread5.Join();
thread6.Join();
}
public void UpdRequest(RequestEntity request)
{
_logger.Debug("UpdRequest.");
if (request == null)
{
throw new ArgumentNullException("Request param can not be null.");
}
_logger.Debug("Params: request= {0};", request.ToInternalString());
OraCommand command = new OraCommand("REQ_PKG.UPD_REQ");
command.CommandType = CommandType.StoredProcedure;
command.AddDBParam(new OraParamInt32("p_req_id", ParameterDirection.Input, request.NumId));
command.AddDBParam(new OraParamDateTime("p_req_date", ParameterDirection.Input, request.ReqDateTime));
command.AddDBParam(new OraParamString("p_subject", ParameterDirection.Input, request.Subject));
command.AddDBParam(new OraParamString("p_note", ParameterDirection.Input, request.Comments));
command.AddDBParam(new OraParamInt32("p_orig_id", ParameterDirection.Input, request.Organization.NumId));
command.AddDBParam(new OraParamString("p_contact", ParameterDirection.Input, request.Contact));
command.AddDBParam(new OraParamInt32("p_req_type", ParameterDirection.Input, request.InfoSourceTypeId));
command.AddDBParam(new OraParamInt32("p_comp_id", ParameterDirection.Input, request.Application.NumId));
command.AddDBParam(new OraParamInt32("p_resp_id", ParameterDirection.Input, request.ResponseUser.NumId));
command.AddDBParam(new OraParamInt32("p_req_state", ParameterDirection.Input, request.StateId));
command.AddDBParam(new OraParamString("p_bug_num", ParameterDirection.Input, request.BugNumber));
command.AddDBParam(new OraParamInt32("p_CM_num", ParameterDirection.Input, string.IsNullOrEmpty(request.CMVersion) ? (int?)null : Convert.ToInt32(request.CMVersion)));
command.AddDBParam(new OraParamString("p_ver_num", ParameterDirection.Input, request.ComponentVersion));
command.AddDBParam(new OraParamString("p_important", ParameterDirection.Input, request.IsImportantString));
Execute(command);
}
