C# OracleDatabase.ExecuteNonQuery方法代码示例

 /// <summary>
 /// ��ӽ�ɫ��Ȩ��
 /// </summary>
 /// <param name="roleName">��ɫ����</param>
 /// <param name="privilegeId">Ȩ��ID</param>
 /// <param name="projectIDs">��ĿID����</param>
 /// <returns></returns>
 public static int AddPrivilege(string roleName, int privilegeId, string projectIDs)
 {
     string sql = "INSERT INTO PLM.ROLEINPRIVILEGE_TAB (ROLENAME, PRIVILEGE_ID, PROJECT_ID) VALUES (:rolename, :privilegeid, :projectids)";
     OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
     DbCommand cmd = db.GetSqlStringCommand(sql);
     db.AddInParameter(cmd, "rolename", DbType.String, roleName);
     db.AddInParameter(cmd, "privilegeid", DbType.Int32, privilegeId);
     db.AddInParameter(cmd, "projectids", DbType.String, projectIDs);
     return db.ExecuteNonQuery(cmd);
 }

 /// <summary>
 /// ��ӽ�ɫ�̳�
 /// </summary>
 /// <param name="roleName"></param>
 /// <param name="inheritRoleName"></param>
 /// <returns></returns>
 public static void AddInherit(string roleName, string[] inheritRoleName)
 {
     string sql = "INSERT INTO PLM.ROLEINHERIT_TAB (ROLENAME, INHERITNAME) VALUES (:rolename, :inheritname)";
     OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
     DbCommand cmd = db.GetSqlStringCommand(sql);
     db.AddInParameter(cmd, "rolename", DbType.String, roleName);
     foreach (string inheritName in inheritRoleName)
     {
         if (inheritName == string.Empty) continue;
         if (cmd.Parameters.Contains("inheritname")) cmd.Parameters.RemoveAt("inheritname");
         db.AddInParameter(cmd, "inheritname", DbType.String, inheritName);
         db.ExecuteNonQuery(cmd);
     }
 }

 /// <summary>
 /// Executes the non query.
 /// </summary>
 /// <param name="commandType">Type of the command.</param>
 /// <param name="commandText">The command text.</param>
 /// <returns></returns>
 public static int ExecuteNonQuery(CommandType commandType, string commandText)
 {
     OracleDatabase database = new OracleDatabase(ConnectionString);
     return database.ExecuteNonQuery(commandType, commandText);
 }

 /// <summary>
 /// ���Ľ�ɫ��״̬
 /// </summary>
 /// <returns></returns>
 public static int UpdateState(string roleName, RoleState us)
 {
     //OracleDatabase db = new OracleDatabase(UserSecurity.ConnectionString);
     OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
     string sql = "UPDATE PLM.ROLE_TAB SET STATE=:state WHERE LOWER(ROLENAME)=:rolename";
     DbCommand cmd = db.GetSqlStringCommand(sql);
     db.AddInParameter(cmd, "state", DbType.String, us.ToString());
     db.AddInParameter(cmd, "rolename", DbType.String, roleName.ToLower());
     return db.ExecuteNonQuery(cmd);
 }

 public int Add()
 {
     // Database db = DatabaseFactory.CreateDatabase("oidsConnection");
     OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
     DbCommand cmd = db.GetSqlStringCommand("INSERT INTO plm.MM_IFS_STA_PART_TAB(ERP_PART_NO,STA_PART_NO,PART_NAME,PROJECTID,ACTIVITYSEQ,SITE,STA_IF,CREATOR) VALUES (:erppartno,:staPartno,:partname,:projectid,:actSeq,:site,:staIf,:creator)");
     db.AddInParameter(cmd, "erppartno", DbType.String,ERP_PART_NO);
     db.AddInParameter(cmd, "staPartno", DbType.String, STA_PART_NO);
     db.AddInParameter(cmd, "partname", DbType.String, PART_NAME);
     db.AddInParameter(cmd, "projectid", DbType.String, PROJECTID);
     db.AddInParameter(cmd, "actSeq", DbType.Int32, ACTIVITYSEQ);
     db.AddInParameter(cmd, "site", DbType.String, SITE);
     db.AddInParameter(cmd, "staIf", DbType.String, STA_IF);
     db.AddInParameter(cmd, "creator", DbType.String, CREATOR);
     return db.ExecuteNonQuery(cmd);
 }

 public int Add()
 {
     // Database db = DatabaseFactory.CreateDatabase("oidsConnection");
     OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
     DbCommand cmd = db.GetSqlStringCommand("INSERT INTO PLM.MM_PART_PARAMETER_TAB(description,blockid,systemid,ecprojectid,last_flag,PART_NO,CONTRACT,WEIGHT_SINGLE,PREDICTION_QTY,PREDICTION_ALERT,COEFFICIENT_ERP,PROJECTID,PREDICT_CREATOR,PROJECT_ZONE,DISCIPLINE,FIRSTBATCH_QTY,FIRSTBATCH_DATE,SECONDBATCH_QTY,SECONDBATCH_DATE,PART_TYPE,FINAL_PREDICTION_QTY,OPERATOR,MODIFY_REASON,PREDICT_DATE,UNIT) VALUES (:description,:blockid,:systemid,:ecprojectid,:lastflag,:partno,:site,:weightSingle,:preQty,:pre_alert,:coeficient,:PROJECTID,:creator,:predictzone,:discipline,:firstpreQty,:firstpreDate,:secondpreQty,:secondpreDate,:part_type,:finalpreQty,:operator,:modreason,:predictdate,:unitmeas)");
     db.AddInParameter(cmd, "description", DbType.String, DESCRIPTION);
     db.AddInParameter(cmd, "blockid", DbType.String, BLOCKID);
     db.AddInParameter(cmd, "systemid", DbType.Int32, SYSTEMID);
     db.AddInParameter(cmd, "ecprojectid", DbType.Int32, ECPROJECTID);
     db.AddInParameter(cmd, "lastflag", DbType.Int32, LAST_FLAG );
     db.AddInParameter(cmd, "partno", DbType.String, PART_NO);
     db.AddInParameter(cmd, "site", DbType.String, CONTRACT);
     db.AddInParameter(cmd, "weightSingle", DbType.Decimal, WEIGHT_SINGLE);
     db.AddInParameter(cmd, "preQty", DbType.Decimal, PREDICTION_QTY);
     db.AddInParameter(cmd, "pre_alert", DbType.Decimal, PREDICTION_ALERT);
     db.AddInParameter(cmd, "coeficient", DbType.Decimal, COEFFICIENT_ERP);
     db.AddInParameter(cmd, "PROJECTID", DbType.String, PROJECTID);
     db.AddInParameter(cmd, "creator", DbType.String, PREDICT_CREATOR);
     db.AddInParameter(cmd, "predictzone", DbType.String, PROJECT_ZONE);
     db.AddInParameter(cmd, "discipline", DbType.String, DISCIPLINE);
     db.AddInParameter(cmd, "firstpreQty", DbType.Decimal, FIRSTBATCH_QTY);
     db.AddInParameter(cmd, "firstpreDate", DbType.DateTime, FIRSTBATCH_DATE);
     db.AddInParameter(cmd, "secondpreQty", DbType.Decimal, SECONDBATCH_QTY);
     db.AddInParameter(cmd, "secondpreDate", DbType.DateTime, SECONDBATCH_DATE);
     db.AddInParameter(cmd, "part_type", DbType.String, PART_TYPE);
     db.AddInParameter(cmd, "finalpreQty", DbType.Decimal, FINAL_PREDICTION_QTY);
     db.AddInParameter(cmd, "predictdate", DbType.DateTime, PREDICT_DATE);
     db.AddInParameter(cmd, "operator", DbType.String, OPERATOR);
     db.AddInParameter(cmd, "modreason", DbType.String, MODIFY_REASON);
     db.AddInParameter(cmd, "unitmeas", DbType.String, UNIT);
     return db.ExecuteNonQuery(cmd);
 }

 /// <summary>
 /// ɾ��Ԥ��
 /// </summary>
 /// <param name="contractno">��</param>
 /// <param name="pid">��ĿID</param>
 /// <param name="dpline">��Ŀרҵ</param>
 /// <returns></returns>
 public static void DeleteEstimate(string contractno,string pid,string dpline)
 {
     // Database db = DatabaseFactory.CreateDatabase("oidsConnection");
     OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
     DbCommand cmd = db.GetSqlStringCommand("update PLM.MM_PART_PARAMETER_TAB t set t.last_flag=0 where CONTRACT =:mSite and projectid=:pid and  DISCIPLINE =:dpid");
     db.AddInParameter(cmd, "mSite", DbType.String, contractno);
     db.AddInParameter(cmd, "pid", DbType.String, pid);
     db.AddInParameter(cmd, "dpid", DbType.String, dpline);
     db.ExecuteNonQuery(cmd);
 }

 public int Update()
 {
     // Database db = DatabaseFactory.CreateDatabase("oidsConnection");
     OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
     DbCommand cmd = db.GetSqlStringCommand("update plm.MM_PART_RATION_TAB set DISCIPLINE=:dpname,PART_NO=:partno,CONTRACT=:contract,P_REQUISITION_NO=:reqNo,ISSUED_QTY=:issqty,ISSUED_DATE=:issDate,IF_INVENTORY=:isInv,PURPOSE=:purpose,BLOCK_ID=:BLOCK_ID,REASON_CODE=:reasoncode,DESIGN_CODE=:designcode,REMARK=:information,UPDATE_DATE=sysdate,PROJECT_ID=:projectId,PART_ZONE=:partzone,part_discipline=:partdp,PART_FX= :actId,PART_TYPE=:parttype,unit=:unit,PART_NAME=:partname where RATION_ID=:rationId");
     db.AddInParameter(cmd, "partno", DbType.String, PART_NO);
     db.AddInParameter(cmd, "partname", DbType.String, PART_NAME);
     db.AddInParameter(cmd, "contract", DbType.String, CONTRACT);
     db.AddInParameter(cmd, "reqNo", DbType.String, P_REQUISITION_NO);
     db.AddInParameter(cmd, "issqty", DbType.Single, ISSUED_QTY);
     db.AddInParameter(cmd, "issDate", DbType.Date, ISSUED_DATE);
     db.AddInParameter(cmd, "isInv", DbType.String, IF_INVENTORY);
     db.AddInParameter(cmd, "purpose", DbType.String, PURPOSE);
     db.AddInParameter(cmd, "BLOCK_ID", DbType.String, BLOCK_ID);
     db.AddInParameter(cmd, "reasoncode", DbType.String, REASON_CODE);
     db.AddInParameter(cmd, "designcode", DbType.String, DESIGN_CODE);
     db.AddInParameter(cmd, "information", DbType.String, REMARK);
     db.AddInParameter(cmd, "projectId", DbType.String, PROJECT_ID);
     db.AddInParameter(cmd, "partzone", DbType.String, PART_ZONE);
     db.AddInParameter(cmd, "partdp", DbType.String, PART_DISCIPLINE);
     db.AddInParameter(cmd, "unit", DbType.String, UNIT);
     db.AddInParameter(cmd, "actId", DbType.String, PART_FX);
     db.AddInParameter(cmd, "parttype", DbType.String, PART_TYPE);
     db.AddInParameter(cmd, "rationId", DbType.Int32, RATION_ID);
     return db.ExecuteNonQuery(cmd);
 }

 /// <summary>
 /// ����MEOMSS��������ERP ID
 /// </summary>
 /// <param name="MEOMSSid"></param><param name="ERPid"></param>
 /// <returns></returns>
 public static int UPERPID(int MEOMSSid, int ERPid)
 {
     string sql = "update project_drawing_tab t set t.related_drawing=:ERPid where t.drawing_id=:MEOMSSid";
     OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
     DbCommand cmd = db.GetSqlStringCommand(sql);
     db.AddInParameter(cmd, "MEOMSSid", DbType.Int32, MEOMSSid);
     db.AddInParameter(cmd, "ERPid", DbType.Int32, ERPid);
     return db.ExecuteNonQuery(cmd);
 }

 /// <summary>
 /// parentIndex���μ�1
 /// </summary>
 /// <param name="nodeid"></param>
 /// <param name="beforenode"></param>
 /// <returns></returns>
 public static int UpdateParentIndexDel(int nodeid, int beforenode, int dropindex)
 {
     OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
     string sql = "UPDATE (SELECT PARENT_INDEX FROM PLM.TREENODES_TAB S WHERE S.PARENT_ID=" + nodeid + ")T SET T.PARENT_INDEX=T.PARENT_INDEX-1 WHERE T.PARENT_INDEX<" + dropindex + " AND T.PARENT_INDEX>=" + beforenode;
     DbCommand cmd = db.GetSqlStringCommand(sql);
     return db.ExecuteNonQuery(cmd);
 }

 /// <summary>
 /// ����parentIndexֵ
 /// </summary>
 /// <param name="nodeid"></param>
 /// <param name="num"></param>
 /// <returns></returns>
 public static int UpdateParentIndex(int nodeid,int num)
 {
     OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
     string sql = "UPDATE PLM.TREENODES_TAB SET PARENT_INDEX=" + num + " WHERE ID=" + nodeid;
     DbCommand cmd = db.GetSqlStringCommand(sql);
     return db.ExecuteNonQuery(cmd);
 }

 /// <summary>
 /// ����ϵͳ�ڵĶ����״̬
 /// </summary>
 /// <param name="id"></param>
 public static int UpdateMSSState(string id,int strstate)
 {
     // Database db = DatabaseFactory.CreateDatabase("oidsConnection");
     OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
     string sql = "UPDATE plm.MM_PART_RATION_TAB set state='" + strstate + "' WHERE RATION_ID=:id";
     DbCommand cmd = db.GetSqlStringCommand(sql);
     db.AddInParameter(cmd, "id", DbType.String, id);
     return db.ExecuteNonQuery(cmd);
 }

        public int Add()
        {
            // Database db = DatabaseFactory.CreateDatabase("oidsConnection");
            OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
            DbCommand cmd = db.GetSqlStringCommand("INSERT INTO plm.MM_PART_RATION_TAB(PART_NO,CONTRACT,P_REQUISITION_NO,ISSUED_QTY,ISSUED_DATE,IF_INVENTORY,PURPOSE,BLOCK_ID,REASON_CODE,DESIGN_CODE,REMARK,CREATE_DATE,CREATER,PROJECT_ID,part_zone,part_discipline,part_fx,part_type,unit,PART_NAME) VALUES (:partno,:contract,:requisionNo,:requireqty,:requirDate,:isInventory,:purpose,:blockID,:reasoncode,:designcode,:information,sysdate,:creater,:projectId,:parentProId,:subProId,:actId,:parttype,:unit,:partname)");
            db.AddInParameter(cmd, "partno", DbType.String, PART_NO);
            db.AddInParameter(cmd, "contract", DbType.String, CONTRACT);
            db.AddInParameter(cmd, "requisionNo", DbType.String, P_REQUISITION_NO);
            db.AddInParameter(cmd, "requireqty", DbType.Single, ISSUED_QTY);
            db.AddInParameter(cmd, "requirDate", DbType.Date, ISSUED_DATE);
            db.AddInParameter(cmd, "isInventory", DbType.String, IF_INVENTORY);
            db.AddInParameter(cmd, "purpose", DbType.String, PURPOSE);
            db.AddInParameter(cmd, "blockID", DbType.String, BLOCK_ID);
            db.AddInParameter(cmd, "reasoncode", DbType.String, REASON_CODE);
            db.AddInParameter(cmd, "designcode", DbType.String, DESIGN_CODE);
            db.AddInParameter(cmd, "information", DbType.String, INFORMATION);
            db.AddInParameter(cmd, "creater", DbType.String, CREATER);
            db.AddInParameter(cmd, "projectId", DbType.String, PROJECT_ID);
            db.AddInParameter(cmd, "parentProId", DbType.String, PART_ZONE);
            db.AddInParameter(cmd, "subProId", DbType.String, PART_DISCIPLINE);
            db.AddInParameter(cmd, "actId", DbType.String, PART_FX);
            db.AddInParameter(cmd, "parttype", DbType.String, PART_TYPE);
            db.AddInParameter(cmd, "unit", DbType.String, UNIT);
            db.AddInParameter(cmd, "partname", DbType.String, PART_NAME);

            ;
            return db.ExecuteNonQuery(cmd);
        }

 /// <summary>
 /// ������Ŀ��רҵ����MEOMSS�ĵ�ǰ��ˮ��
 /// </summary>
 /// <param name="dpid"></param>
 /// <returns></returns>
 public static int UPManualNo(int dpid, int pid)
 {
     string sql = "update project_discipline_oid t set t.o_id=t.o_id+1 where t.P_ID=:pid and t.D_ID=:dpid";
     OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
     DbCommand cmd = db.GetSqlStringCommand(sql);
     db.AddInParameter(cmd, "dpid", DbType.Int32, dpid);
     db.AddInParameter(cmd, "pid", DbType.Int32, pid);
     return db.ExecuteNonQuery(cmd);
 }

 /// <summary>
 /// ɾ��Ԥ��
 /// </summary>
 /// <param name="seqNo"></param>
 /// <returns></returns>
 public static int Delete(int seqNo)
 {
     // Database db = DatabaseFactory.CreateDatabase("oidsConnection");
     OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
     DbCommand cmd = db.GetSqlStringCommand("delete PLM.MM_PART_PARAMETER_TAB t where SEQUENCE_ID=:seqNo");
     db.AddInParameter(cmd, "seqNo", DbType.Int32, seqNo);
     return db.ExecuteNonQuery(cmd);
 }