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); }