C# OracleDatabase.ExecuteScalar方法代码示例

 /// <summary>
 /// ����ERPID��ȡ��Ӧ��ECDMSID
 /// </summary>
 /// <param name="id"></param>
 /// <returns></returns>
 public static string FindECDMSID(string id)
 {
     //Database db = DatabaseFactory.CreateDatabase();
     OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
     string sql = "SELECT ECDMSID FROM plm.PROJECT_RELATION_TAB WHERE ERPid=:id";
     DbCommand cmd = db.GetSqlStringCommand(sql);
     db.AddInParameter(cmd, "id", DbType.String, id);
     return Convert.ToString(db.ExecuteScalar(cmd));
 }

 /// <summary>
 /// ����Ȩ�ޱ�ʶ���ж��Ƿ���ڴ�Ȩ��
 /// </summary>
 /// <param name="flag"></param>
 /// <returns></returns>
 public static bool Exist(string flag)
 {
     OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
     string sql = "SELECT PRIVILEGE_FLAG FROM PLM.PRIVILEGE_TAB WHERE PRIVILEGE_FLAG=:flag";
     DbCommand cmd = db.GetSqlStringCommand(sql);
     db.AddInParameter(cmd, "flag", DbType.String, flag);
     object ret = db.ExecuteScalar(cmd);
     return (ret == null || ret == DBNull.Value) ? false : true;
 }

 public static string FindDesc(string id)
 {
     //Database db = DatabaseFactory.CreateDatabase();
     OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
     string sql = "SELECT DESCRIPTION FROM IFSAPP.YRS_REQUISITION_REASON_TAB WHERE REASON_CODE=:id";
     DbCommand cmd = db.GetSqlStringCommand(sql);
     db.AddInParameter(cmd, "id", DbType.String, id);
     return Convert.ToString(db.ExecuteScalar(cmd));
 }

 /// <summary>
 /// �жϽ�ɫ�Ƿ����
 /// </summary>
 /// <param name="roleName"></param>
 /// <returns></returns>
 public static bool Available(string roleName)
 {
     OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
     string sql = "SELECT STATE FROM PLM.ROLE_TAB WHERE ROLENAME=:rolename";
     DbCommand cmd = db.GetSqlStringCommand(sql);
     db.AddInParameter(cmd, "rolename", DbType.String, roleName);
     object state = db.ExecuteScalar(cmd);
     if (state == null || state == DBNull.Value) return false;
     return state.ToString().ToUpper() == RoleState.NORMAL.ToString();
 }

 /// <summary>
 /// 查看是否存在修改通知单
 /// </summary>
 /// <param name="pid"></param>
 /// <param name="drawingno"></param>
 /// <returns></returns>
 public static bool ExistModifyDrawing(string pid, string drawingno)
 {
     OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
     string sql = "select t.modifydrawings from plm.SP_CREATEPDFDRAWING t  where t.projectid='" + pid + "' and t.drawingno='" + drawingno + "' and t.FRONTPAGE is not null AND t.FLAG = 'Y'";
     DbCommand cmd = db.GetSqlStringCommand(sql);
     object ret = db.ExecuteScalar(cmd);
     if (ret == null || ret == DBNull.Value)
         return false;
     return true;
 }

 /// <summary>
 /// 查看合并生成图纸表中是否存在该项
 /// </summary>
 /// <param name="pid"></param>
 /// <param name="drawingno"></param>
 /// <returns></returns>
 public static bool ExistInfo(string pid,string drawingno)
 {
     OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
     string sql = "select count(*) from plm.SP_CREATEPDFDRAWING t  where t.projectid='"+pid+"' and t.drawingno='"+drawingno+"' AND t.FLAG = 'Y'";
     DbCommand cmd = db.GetSqlStringCommand(sql);
     object ret = db.ExecuteScalar(cmd);
     int num = Convert.ToInt32(ret);
     if (num==0)
         return false;
     return true;
 }

 /// <summary>
 /// �ڵ��Ƿ��и�Ȩ������
 /// </summary>
 /// <returns></returns>
 public static bool ExistPrivilege(int privilegeid,int nodeid)
 {
     OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
     //OracleDatabase db = new OracleDatabase(UserSecurity.ConnectionString);
     string sql = "SELECT * FROM PLM.PRIVILEGE_NODE_TAB WHERE PRIVILEGE_ID=:privilegeid AND NODE_ID=:nodeid";
     DbCommand cmd = db.GetSqlStringCommand(sql);
     db.AddInParameter(cmd, "privilegeid", DbType.Int32, privilegeid);
     db.AddInParameter(cmd, "nodeid", DbType.Int32, nodeid);
     object ret = db.ExecuteScalar(cmd);
     if (ret == null || ret == DBNull.Value) return false;
     return true;
 }

        /// <summary>
        /// ���ݽ�ɫ���Ƽ�Ȩ�ޱ�ʶID�ж��Ƿ���и�Ȩ��
        /// </summary>
        /// <param name="privlegeId"></param>
        /// <param name="roleName"></param>
        /// <returns></returns>
        public static bool HavingPrivilege(string roleName, int privlegeId)
        {
            if (!Available(roleName)) return false;
            //Self Privlege Check
            OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
            string sql = "SELECT PRIVILEGE_ID FROM PLM.ROLEINPRIVILEGE_TAB WHERE ROLENAME=:rolename AND PRIVILEGE_ID=:privilegeid";
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "rolename", DbType.String, roleName);
            db.AddInParameter(cmd, "privilegeid", DbType.Int32, privlegeId);
            object ret = db.ExecuteScalar(cmd);
            if (ret != null && ret != DBNull.Value) return true;

            //Inherit Role Privilege Check
            //bool hasPrivilege = false;
            foreach (string inheritRole in FindInheritRole(roleName))
            {
                if (HavingPrivilege(inheritRole, privlegeId)) return true;
            }

            return false;
        }

 public static string FindPartTypeDesc(int typeid)
 {
     OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
     string sql = "SELECT TYPE_DESC FROM plm.MM_PART_TYPE_TAB WHERE TYPEID=:typeid";
     DbCommand cmd = db.GetSqlStringCommand(sql);
     db.AddInParameter(cmd, "typeid", DbType.Int32, typeid);
     return Convert.ToString(db.ExecuteScalar(cmd));
 }

 /// <summary>
 /// ��ý�ɫ״̬
 /// </summary>
 /// <param name="roleName"></param>
 /// <returns></returns>
 public static RoleState GetState(string roleName)
 {
     OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
     string sql = "SELECT STATE FROM PLM.ROLE_TAB WHERE LOWER(ROLENAME)=:rolename";
     DbCommand cmd = db.GetSqlStringCommand(sql);
     db.AddInParameter(cmd, "rolename", DbType.String, roleName.ToLower());
     object s = db.ExecuteScalar(cmd);
     if (s == null || s == DBNull.Value) return RoleState.LOCKED;
     if (string.IsNullOrEmpty(s.ToString())) return RoleState.LOCKED;
     return (RoleState)Enum.Parse(typeof(RoleState), s.ToString());
 }

 public static string FindSiteName(string id)
 {
     //Database db = DatabaseFactory.CreateDatabase();
     OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
     string sql = "SELECT CONTRACT_REF FROM IFSAPP.site_tab WHERE CONTRACT=:id";
     DbCommand cmd = db.GetSqlStringCommand(sql);
     db.AddInParameter(cmd, "id", DbType.String, id);
     return Convert.ToString(db.ExecuteScalar(cmd));
 }

 /// <summary>
 /// ����PROJECTNAME��ȡ��Ӧ��ERP����ĿID
 /// </summary>
 /// <param name="id"></param>
 /// <returns></returns>
 public static string FindERPID(string id)
 {
     //Database db = DatabaseFactory.CreateDatabase();
     OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
     string sql = "SELECT ERPID FROM plm.project_relation_view WHERE projectname_db=:id";
     DbCommand cmd = db.GetSqlStringCommand(sql);
     db.AddInParameter(cmd, "id", DbType.String, id);
     return Convert.ToString(db.ExecuteScalar(cmd));
 }

 public static int GetPartParaCou(string partid, string creator, string projectstr)
 {
     OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
     //  Database db = DatabaseFactory.CreateDatabase("oidsConnection");
     //OracleDatabase db = new OracleDatabase(UserSecurity.ConnectionString);
     string sql = "SELECT count(*) from MM_PART_PARAMETER_TAB where projectid='"+projectstr+"' and part_no=" + partid + " and last_flag=0 and discipline ="+creator;
     DbCommand cmd = db.GetSqlStringCommand(sql);
     return Convert.ToInt32(db.ExecuteScalar(cmd));
 }

        /// <summary>
        /// ���ݽ�ɫ��ͼֽ��Ż�ȡ�Ƿ�������ʱ�ʶ
        /// </summary>
        /// <param name="roleName"></param>
        /// <param name="drawingId"></param>
        /// <returns></returns>
        public static string DrawingAllowedFlag(string roleName, int drawingId)
        {
            OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
            string sql = "SELECT ALLOW_FLAG FROM PLM.ROLEINDRAWING_TAB WHERE ROLENAME=:rolename AND DRAWING_ID=:drawingid";
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "rolename", DbType.String, roleName);
            db.AddInParameter(cmd, "drawingid", DbType.Int32, drawingId);
            object allowFlag = db.ExecuteScalar(cmd);

            if (allowFlag == DBNull.Value || allowFlag == null) return string.Empty;
            return allowFlag.ToString();
        }

 /// <summary>
 /// �������ID����ĿID�ҳ�Ԥ������
 /// </summary>
 /// <param name="partid"></param>
 /// <param name="projectid"></param>
 /// <returns></returns>
 public static decimal FindPartParaSum(int partid, string projectid)
 {
     OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
     //  Database db = DatabaseFactory.CreateDatabase("oidsConnection");
     //OracleDatabase db = new OracleDatabase(UserSecurity.ConnectionString);
     string sql = "SELECT pred_sum_qty FROM plm.mm_part_parasum_view WHERE PARTID="+partid+" and ecprojectid="+projectid ;
     DbCommand cmd = db.GetSqlStringCommand(sql);
     object pdsumqty = db.ExecuteScalar(cmd);
     return (pdsumqty == null || pdsumqty == DBNull.Value) ? Convert.ToDecimal(0) : Convert.ToDecimal(pdsumqty);
 }