C# SelectCommand类代码示例

        public static DataTable GetAllBIBLIOTECA_ITEMREVISTAID1(
       ConnectionInfo pInfo,
        string LV_ID,
        decimal JRNL_REVISTA_ID

   )
        {
            string lQuery = "";
            DataTable lTable = new DataTable();

            lQuery += BIBLIOTECA_ITEMJRNL_REVISTAQD.GetAllBIBLIOTECA_ITEMLIVROID1;
            lQuery += @" where il.JRNL_REVISTA_ID=lvr.JRNL_REVISTA_ID
                        and il.JRNL_REVISTA_ID=" + JRNL_REVISTA_ID + @"
                        and not exists(";
            lQuery += BIBLIOTECA_ITEMJRNL_REVISTAQD.GetAllBIBLIOTECA_ITEMLIVROID1SUB;
            lQuery += @"where ie.cod_item=il.ITEMJRNL_REVISTA_ID
                        and ie.ttab_id='{0}'
                        )";
            lQuery = string.Format(lQuery, LV_ID);

            SelectCommand lSelect = new SelectCommand(lQuery);
            lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));

            return lTable;
        }

        public static DataTable GetAllEscalaMesAtuacao(
            ConnectionInfo pInfo,
            String pESC_DIA,
            decimal pATUA_ID
        )
        {
            string lQuery = "";
            DataTable lTable = new DataTable();

            lQuery = EscalaQD.qEscalaAtividadeList;
            lQuery += " WHERE ESC_STATUS NOT IN ('I','E') ";
            lQuery += " AND A.ATDEF_ID = B.ATDEF_ID ";
            lQuery += string.Format(" AND A.{0} = >>{0} ", EscalaQD._ATUA_ID.Name);
            lQuery += string.Format(" AND to_char(A.{0}, 'mm') = to_char(>>{0}, 'mm')", EscalaQD._ESC_DIA.Name);
            lQuery += string.Format(" AND to_char(A.{0}, 'yyyy') = to_char(>>{0}, 'yyyy')", EscalaQD._ESC_DIA.Name);
            lQuery += "Order By ESC_DIA ";

            SelectCommand lSelect = new SelectCommand(lQuery);

            lSelect.Fields.Add(EscalaQD._ATUA_ID.Name, pATUA_ID, (ItemType)EscalaQD._ATUA_ID.DBType);
            lSelect.Fields.Add(EscalaQD._ESC_DIA.Name, pESC_DIA, (ItemType)EscalaQD._ESC_DIA.DBType);
            lSelect.Fields.Add(EscalaQD._ESC_DIA.Name, pESC_DIA, (ItemType)EscalaQD._ESC_DIA.DBType);

            lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));

            return lTable;
        }

 /// <summary>
 /// 初始化Sql连接语句抽象类
 /// </summary>
 /// <param name="baseCommand">源选择语句</param>
 /// <param name="joinType">连接模式</param>
 /// <param name="currentTableName">当前表格名称</param>
 /// <param name="currentTableField">当前表格主键</param>
 /// <param name="anotherTableField">另个表格主键</param>
 protected AbstractSqlJoin(SelectCommand baseCommand, SqlJoinType joinType, String currentTableName, String currentTableField, String anotherTableField)
 {
     this._joinType = joinType;
     this._currentTableName = currentTableName;
     this._currentTableKeyField = currentTableField;
     this._anotherTableKeyField = anotherTableField;
 }

        public static DataTable GetAtendimentosRealizadosDia(
            string pDOCMOV_REGUSER,
            string pDOCMOV_REGDATE,
            decimal pNUC_ID,
            ConnectionInfo pInfo
        )
        {
            string lQuery = "";
            DataTable lTable = new DataTable();

            lQuery = DocumentoMovimentoQD.qDocumentoMovimentoCount;
            lQuery += string.Format(" WHERE DOCMOV.DOCMOV_STATUS NOT IN ('I','E') AND DOCMOV.CAT_ID IN ({0},{1}) AND DOCMOV.{2} = >>{2} AND TO_CHAR(DOCMOV.{3},'dd/MM/yyyy') = >>{3}", (decimal)Categoria.Atendido, (decimal)Categoria.AtendidoRetorno, DocumentoMovimentoQD._DOCMOV_REGUSER.Name,
                DocumentoMovimentoQD._DOCMOV_REGDATE.Name);
            lQuery += string.Format(" AND EXISTS (SELECT 0 FROM DOCUMENTO DOC WHERE DOC.DOC_ID = DOCMOV.DOC_ID AND DOC.{0} = >>{0})", DocumentoQD._NUC_ID.Name);

            SelectCommand lSelect = new SelectCommand(lQuery);

            lSelect.Fields.Add(DocumentoMovimentoQD._DOCMOV_REGUSER.Name, pDOCMOV_REGUSER, (ItemType)DocumentoMovimentoQD._DOCMOV_REGUSER.DBType);
            lSelect.Fields.Add(DocumentoMovimentoQD._DOCMOV_REGDATE.Name, pDOCMOV_REGDATE, (ItemType)DocumentoMovimentoQD._DOCMOV_REGDATE.DBType);
            lSelect.Fields.Add(DocumentoQD._NUC_ID.Name, pNUC_ID, (ItemType)DocumentoQD._NUC_ID.DBType);

            lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));

            return lTable;
        }

        public static LoginUserDo VerifyLogin(string pEUSR, string pEusr_Password, string pEusr_Type, ConnectionInfo pConnectionInfo)
        {
            bool lReturn;
            string lQuery;
            DataTable lTable;

            lQuery = UsersQD.qUsersList;
            lTable = new DataTable();

            if (pEusr_Type == "1")//Portal Garagem
            {
                lQuery += String.Format(" WHERE {0} = >>{0} AND {1} = >>{1} ", UsersQD._USR_LOGIN.Name, UsersQD._USR_PASSWORD.Name);
                lQuery += " AND USR_STATUS='A' ";

                SelectCommand lSelect = new SelectCommand(lQuery);

                pEusr_Password = APB.Framework.Encryption.Crypto.Encode(pEusr_Password);

                lSelect.Fields.Add(UsersQD._USR_LOGIN.Name, pEUSR, (ItemType)UsersQD._USR_LOGIN.DBType);
                lSelect.Fields.Add(UsersQD._USR_PASSWORD.Name, pEusr_Password, (ItemType)UsersQD._USR_PASSWORD.DBType);

                lTable = lSelect.ReturnData(Instance.CreateDatabase(pConnectionInfo));
            }

            lReturn = (lTable.Rows.Count > 0) ? true : false;

            // User Accept, Check TP_ID
            if (lReturn)
            {
                return new LoginUserDo((decimal)lTable.Rows[0]["USR_ID"], (string)lTable.Rows[0]["USR_LOGIN"]);
            }

            return null;
        }

        public static DataTable GetDIARIAXTIPODESLOCAMENTObyID(
             ConnectionInfo pInfo, string pDIA_ID
         )
        {
            string lQuery = "";
             DataTable lTable = new DataTable();

             lQuery += " SELECT DISTINCT DT.DIA_ID, TD.DESLOC_ID, TD.DESLOC_NOME ";
             lQuery += " FROM DIARIAXTIPODESLOCAMENTO DT, TIPODESLOCAMENTO TD ";
             lQuery += " WHERE DIATIPO_STATUS='A' ";
             lQuery += " AND TD.DESLOC_STATUS='A' ";
             lQuery += " AND DT.DESLOC_ID = TD.DESLOC_ID ";
             lQuery += " AND DT.DESLOC_ID NOT IN (8) ";
             lQuery += " AND DIA_ID = " + pDIA_ID + " ";
             lQuery += " UNION ";
             lQuery += " SELECT DISTINCT DT.DIA_ID, TD.DESLOC_ID, TD.DESLOC_NOME || ' - ' || DT.DIATIPO_OUTROS AS DESLOC_NOME ";
             lQuery += " FROM DIARIAXTIPODESLOCAMENTO DT, TIPODESLOCAMENTO TD ";
             lQuery += " WHERE DIATIPO_STATUS='A' ";
             lQuery += " AND TD.DESLOC_STATUS='A' ";
             lQuery += " AND DT.DESLOC_ID = TD.DESLOC_ID ";
             lQuery += " AND DT.DESLOC_ID = 8 ";
             lQuery += " AND DIA_ID = " + pDIA_ID + " ";

             SelectCommand lSelect = new SelectCommand(lQuery);
             lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));

             return lTable;
        }

        public static string GetPassword(string pSusr_Login, ConnectionInfo pConnectionInfo)
        {
            bool lReturn;
            string lQuery = "";
            string lPassWord = "";
            DataTable lTable;

            lQuery = SystemUserQD.qSystemUserList;
            lQuery += String.Format(" WHERE {0} = >>{0} ", SystemUserQD._SUSR_LOGIN.Name);
            lQuery += " AND SUSR_STATUS='A' ";

            SelectCommand lSelect = new SelectCommand(lQuery);

            lSelect.Fields.Add(SystemUserQD._SUSR_LOGIN.Name, pSusr_Login, (ItemType)SystemUserQD._SUSR_LOGIN.DBType);

            lTable = lSelect.ReturnData(Instance.CreateDatabase(pConnectionInfo));

            lReturn = (lTable.Rows.Count > 0) ? true : false;

            // User Accept, Get Password
            if (lReturn)
            {
                // Decodificar Senha para Envio
                lPassWord = APB.Framework.Encryption.Crypto.Decode(lTable.Rows[0]["SUSR_PASSWORD"].ToString());
            }

            return lPassWord;
        }

        public static DataTable GetAllModulosUsuario(ConnectionInfo pInfo, string id_usuario, string id_sistema)
        {
            string lQuery = "";
             DataTable lTable = new DataTable();

             lQuery = "select distinct t5.* from ";
             lQuery += "        SEG_USUARIOXGRUPO t1";
             lQuery += " join SEG_GRUPO t2";
             lQuery += "      on (t1.GRP_ID = t2.GRP_ID";
             lQuery += "         and t1.SUSR_ID = "+id_usuario;
             lQuery += "         and t1.susr_regstatus='A')";
             lQuery += " join SEG_GRUPOXPROGRAMA t3 ";
             lQuery += "      on (t2.GRP_ID = t3.GRP_ID";
             lQuery += "          and t3.grp_regstatus = 'A')";
             lQuery += " join SEG_PROGRAMA t4";
             lQuery += "      on (t3.PRO_ID = t4.PRO_ID";
             lQuery += "          and t4.pro_regstatus = 'A')";
             lQuery += " join SEG_MODULO t5";
             lQuery += "      on (t4.MOD_ID = t5.MOD_ID";
             lQuery += "          and t5.mod_regstatus = 'A' and t5.sis_id=" + id_sistema + ")";

             SelectCommand lSelect = new SelectCommand(lQuery);
             lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));

             return lTable;
        }

        public static DataTable GetExtratoPortaria(
             ConnectionInfo pInfo, string pPORT_ID
         )
        {
            string lQuery = "";
             DataTable lTable = new DataTable();

             lQuery += @" SELECT DIAP.DIA_ID,PORT.PORT_NUMERO PORTARIA, TO_CHAR(PORT.PORT_REGDATE, 'DD/MM/YYYY')PORT_REGDATE
                        ,DIAQ.DIAQT_DIARIATOTAL TOTAL,TO_CHAR(DIAQ.DIAQT_DATADEINICIO, 'DD/MM/YYYY') INI, TO_CHAR(DIAQ.DIAQT_DATADETERMINO, 'DD/MM/YYYY') FIM
                        ,(SELECT UPPER(CID.CID_DESC) FROM CIDADE CID WHERE CID.CID_ID = DIAQ.CID_ID_LOCALDEPARTIDA) ORIGEM
                        ,(SELECT UPPER(CID.CID_DESC) FROM CIDADE CID WHERE CID.CID_ID = DIAQ.CID_ID_LOCALDEDESTINO) DESTINO
                        FROM PORTARIA PORT LEFT JOIN DIARIAXPORTARIA DIAP
                        ON PORT.PORT_ID = DIAP.PORT_ID
                        LEFT JOIN DIARIAQUANTIDADE DIAQ
                        ON DIAP.DIA_ID = DIAQ.DIA_ID
                        WHERE PORT.PORT_ID = {0}
                        ORDER BY INI ";

             lQuery = string.Format(lQuery, pPORT_ID);

             SelectCommand lSelect = new SelectCommand(lQuery);
             lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));

             return lTable;
        }

        public static int Delete(
           ConnectionInfo pInfo,
           decimal pMATERIAL_ID
        )
        {
            string lQuery = "";
             int lResult;

             DataTable lTable = new DataTable();

             lQuery = " DELETE FROM " + MATDETXFORNECEDORQD.TableName;
             lQuery += " WHERE MATERIAL_ID = " + pMATERIAL_ID;

             SelectCommand lSelect = new SelectCommand(lQuery);
             try
             {
                 lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
                 lResult = 1;
             }
             catch (Exception Err)
             {
                 lResult = 0;
             }

             return lResult;
        }

        public static DataTable GetAllPERIODOXSETORNOME(
          ConnectionInfo pInfo, Decimal pSET_ID
      )
        {
            string lQuery = "";
            DataTable lTable = new DataTable();

            // lQuery = PERIODOXSETORQD.qPERIODOXSETORList;
            //            lQuery += @" WHERE PERQ_STATUS='A'
            //                         AND S.SET_ID = P.SET_ID
            //                         AND S.SET_STATUS='A'
            //                         AND P.PERQ_DIAINICIO <= SYSDATE
            //                         AND P.PERQ_DATAFIM >= SYSDATE
            //                         AND S.SET_ID = " + pSET_ID;

            lQuery += @"  SELECT S.*,P.*
                          from PERIODOXSETOR P , SETOR S WHERE PERQ_STATUS='A'
                          AND S.SET_ID = P.SET_ID
                          AND S.SET_STATUS='A'
                          AND S.SET_ID = " + pSET_ID;

            SelectCommand lSelect = new SelectCommand(lQuery);
            lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));

            return lTable;
        }

        public static DataTable GetAllMEIADIARIABUSCAID(
           ConnectionInfo pInfo, string pCID_DESC
       )
        {
            string lQuery = "";
            DataTable lTable = new DataTable();

            lQuery = MEIADIARIAQD.qMeiaDiariaListCompleteBuscaID;
            lQuery += @"  WHERE M.DIACID_ID_ORIGEM = C1.CID_ID
                        AND M.DIACID_ID_DESTINO = C2.CID_ID
                        AND M.TVD_ID = T.TVD_ID
                        AND C1.UF_ID = U1.UF_ID
                        AND C2.UF_ID = U2.UF_ID
                        AND C1.CID_DESC LIKE '%{0}%'
                        AND C1.CID_STATUS='A'
                        AND T.TVD_STATUS='A'
                        AND C2.CID_STATUS='A'
                        AND M.MEIA_STATUS='A'
                        AND U1.UF_STATUS='A'
                        AND U2.UF_STATUS='A' ";
            lQuery = string.Format(lQuery, pCID_DESC);

            SelectCommand lSelect = new SelectCommand(lQuery);
            lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));

            return lTable;
        }

 /// <summary>
 /// 初始化Sql查询语句类
 /// </summary>
 /// <param name="baseCommand">源Sql语句</param>
 /// <param name="columnName">字段名称</param>
 /// <param name="op">条件运算符</param>
 /// <param name="command">选择语句</param>
 internal SqlBasicCommandCondition(AbstractSqlCommand baseCommand, String columnName, SqlOperator op, SelectCommand command)
     : base(baseCommand)
 {
     this._columnName = columnName;
     this._operator = op;
     this._command = command;
 }

        public static DataTable GetAllRelatorioPendente(
             ConnectionInfo pInfo, string pPESF_proposto, string pDATA
         )
        {
            string lQuery = "";
             DataTable lTable = new DataTable();

             lQuery = RELATORIOVIAGEMQD.qRELATORIOVIAGEMPendente;
             lQuery += @" WHERE D.DIA_STATUS='A'
                            AND P.PES_STATUS='A'
                            AND PC.PRES_STATUS='A'
                            AND PF.PESF_STATUS='A'
                            AND DP.DIAPRO_STATUS='A'
                            AND DOC.DDOC_STATUS='A'
                            and D.PRES_ID = PC.PRES_ID
                            AND D.DOC_ID = DOC.DOC_ID
                            AND D.DIA_ID = DP.DIA_ID
                            AND D.PESF_PROPONENTE = PF.PESF_ID
                            AND P.PES_ID = PF.PES_ID
                            AND (SELECT COUNT(*) FROM RELATORIOVIAGEM WHERE DIAPRO_ID = DP.DIAPRO_ID) = 0
                            AND DP.PESF_PROPOSTO = {0}
                            AND PC.PRES_DATAPRESTACAO <= '{1}'
                            ORDER BY PC.PRES_DATAPRESTACAO";
             lQuery = string.Format(lQuery, pPESF_proposto, pDATA);

             SelectCommand lSelect = new SelectCommand(lQuery);
             lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));

             return lTable;
        }

        public static int Delete(
          ConnectionInfo pInfo,
          string pTRA_ID
       )
        {
            string lQuery = "";
            int lResult;

            DataTable lTable = new DataTable();

            lQuery = " DELETE FROM " + TRAMITACAOQD.TableName;
            lQuery += " WHERE " + TRAMITACAOQD._TRA_ID.Name + " = " + pTRA_ID;

            SelectCommand lSelect = new SelectCommand(lQuery);
            try
            {
                lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo));
                lResult = 1;
            }
            catch (Exception Err)
            {
                lResult = 0;
            }

            return lResult;
        }