C# SelectCommand.ReturnScalar方法代码示例

        /// <summary>
        /// Retorna o count, espera já uma query de count
        /// </summary>
        /// <param name="pConnectionString">String de conexão com o banco</param>
        /// <param name="pSelectQuery">Query para rodar (ex.: SELECT Count(PRV_ID) FROM Providers)</param>
        /// <returns></returns>
        public static decimal Count(ConnectionInfo pInfo, string pSelectQuery)
        {
            //TODO: Depois vai ter que criptografar pConnectionString e pSelectQuery

            SelectCommand lSelect = new SelectCommand(pSelectQuery);

            object lValue = lSelect.ReturnScalar(Instance.CreateDatabase(pInfo));

            return lValue.DBToDecimal();
        }

        public static decimal GetNext(Transaction pTransaction, string pIDFieldValue)
        {
            decimal lID;

            // Inicializa operação
            OperationResult lReturn = new OperationResult(QueryDictionaries.SequencesQD.TableName, QueryDictionaries.SequencesQD.TableName);

            // Recupera Valor
            SelectCommand lSelectNext;

            string lSelectQuery = QueryDictionaries.SequencesQD.qSequencesMax;
            lSelectQuery += String.Format("WHERE {0} = >>{0}", QueryDictionaries.SequencesQD._SEQ_NAME.Name);

            object lScalarReturn;

            lSelectNext = new SelectCommand(lSelectQuery);

            // Passagem dos Valores de Parametros para a Clausula WHERE [comando SELECT]
            lSelectNext.Fields.Add(QueryDictionaries.SequencesQD._SEQ_NAME.Name, pIDFieldValue, ItemType.String);

            // Recupera Valor do Select (Seq_Value)
            lScalarReturn = lSelectNext.ReturnScalar(pTransaction);

            if (lScalarReturn == null || lScalarReturn == DBNull.Value) lScalarReturn = 1;
            lID = Convert.ToDecimal(lScalarReturn);

            // Altera Valor da Sequence
            UpdateCommand lUpdate;
            lUpdate = new UpdateCommand(QueryDictionaries.SequencesQD.TableName);

            // Identificação dos Campos a serem Alterados
            lUpdate.Fields.Add(QueryDictionaries.SequencesQD._SEQ_VALUE.Name, lID, (ItemType) QueryDictionaries.SequencesQD._SEQ_VALUE.DBType);

            string lUpdateQuery;

            lUpdateQuery = String.Format("WHERE {0} = >>{0}", QueryDictionaries.SequencesQD._SEQ_NAME.Name);
            lUpdate.Condition = lUpdateQuery;

            // Passagem dos Valores para a Condição Where do Update
            lUpdate.Conditions.Add(QueryDictionaries.SequencesQD._SEQ_NAME.Name, pIDFieldValue);

            // Execução do UPDATE
            lUpdate.Execute(pTransaction);

            // Retorna novo valor da chave [SEQUENCE VALUE]
            return lID;
        }

        public static int COUNT_GetAllPEDIDOCOMPRAById(
             string iIndice,
             ConnectionInfo pInfo
         )
        {
            string lQuery = "";
             int lCount = 0;

             lQuery = "Select Count(*) from (";
             lQuery += PEDIDOCOMPRAQD.qPEDIDOCOMPRAList;
             lQuery += @" WHERE PEDCOMP_STATUS='A' AND SPED_STATUS='A' AND PF.PESF_STATUS = 'A' AND P.PES_STATUS = 'A'
                          AND PC.SPED_ID = S.SPED_ID
                          AND PC.PESF_SOLICITANTE = PF.PESF_ID
                          AND PF.PES_ID = P.PES_ID ";
             lQuery += " AND PC.SPED_ID = '{0}' )";
             lQuery = string.Format(lQuery, iIndice);

             SelectCommand lSelect = new SelectCommand(lQuery);
             lCount = int.Parse(lSelect.ReturnScalar(Instance.CreateDatabase(pInfo)).ToString());

             return lCount;
        }

        public static int Delete_proposto(
          ConnectionInfo pInfo,
          string pPESF_ID
       )
        {
            string lQuery = "";
            int lResult;
            Transaction lTransaction;

            lTransaction = new Transaction(Instance.CreateDatabase(pInfo));

            bool lLocalTransaction = (lTransaction != null);

            object lTable;

            lQuery = " DELETE FROM " + DIARIA_PROPOSTOQD.TableName;
            lQuery += " WHERE PESF_ID = " + pPESF_ID;

            SelectCommand lSelect = new SelectCommand(lQuery);

            try
            {
                lTable = lSelect.ReturnScalar(Instance.CreateDatabase(pInfo));
                lResult = 1;
            }
            catch (Exception Err)
            {

                lResult = 0;

                if (lLocalTransaction)
                    lTransaction.Rollback();
            }

            return lResult;
        }

        public static int GetStatusMidDig(
         ConnectionInfo pInfo,
          string pMidDig

     )
        {
            string lQuery = "";
             int qtd = 0;

             lQuery = BIBLIOTECA_MIDIADIGITALQD.GetStatusMidDig;
             lQuery += " and imd.middig_id={0} AND middig_STATUS='A' and not exists(select * from biblioteca_itememprestimo ie ";
             lQuery += "where ie.cod_item=imd.imd_id and ie.ttab_id=3)";

             lQuery = string.Format(lQuery, pMidDig);

             SelectCommand lSelect = new SelectCommand(lQuery);
             qtd = int.Parse(lSelect.ReturnScalar(Instance.CreateDatabase(pInfo)).ToString());

             return qtd;
        }

        public static decimal GetNext(ConnectionInfo pInfo, string pIDFieldValue)
        {
            Transaction lTransaction;
            DataBase lDataBase;

            lDataBase = Instance.CreateDatabase(pInfo);

            lTransaction = new Transaction(lDataBase);
            try
            {
                decimal lID;

                // Inicializa operação
                OperationResult lReturn = new OperationResult(QueryDictionaries.SequencesControlQD.TableName, QueryDictionaries.SequencesControlQD.TableName);

                // Recupera Valor
                SelectCommand lSelectNext;

                string lSelectQuery = QueryDictionaries.SequencesControlQD.qSequencesControlMax;
                lSelectQuery += String.Format("WHERE {0} = >>{0}", QueryDictionaries.SequencesControlQD._CONTROLNAME.Name);

                object lScalarReturn;
                //APB.Framework.Math.Module11

                lSelectNext = new SelectCommand(lSelectQuery);

                // Passagem dos Valores de Parametros para a Clausula WHERE [comando SELECT]
                lSelectNext.Fields.Add(QueryDictionaries.SequencesControlQD._CONTROLNAME.Name, pIDFieldValue, ItemType.String);

                // Recupera Valor do Select (Seq_Value)
                lScalarReturn = lSelectNext.ReturnScalar(lTransaction);

                if (lScalarReturn == null || lScalarReturn == DBNull.Value) lScalarReturn = 1;
                lID = Convert.ToDecimal(lScalarReturn);

                // Altera Valor da Sequence
                UpdateCommand lUpdate;
                lUpdate = new UpdateCommand(QueryDictionaries.SequencesControlQD.TableName);

                // Identificação dos Campos a serem Alterados
                lUpdate.Fields.Add(QueryDictionaries.SequencesControlQD._CONTROLVALUE.Name, lID, (ItemType)QueryDictionaries.SequencesControlQD._CONTROLVALUE.DBType);

                string lUpdateQuery;

                lUpdateQuery = String.Format("WHERE {0} = >>{0}", QueryDictionaries.SequencesControlQD._CONTROLNAME.Name);
                lUpdate.Condition = lUpdateQuery;

                // Passagem dos Valores para a Condição Where do Update
                lUpdate.Conditions.Add(QueryDictionaries.SequencesControlQD._CONTROLNAME.Name, pIDFieldValue);

                // Execução do UPDATE
                lUpdate.Execute(lTransaction);

                lTransaction.Commit();
                // Retorna novo valor da chave [CONTROL VALUE]
                return lID;
            }
            catch (Exception ex)
            {
                lTransaction.Rollback();
                throw new UnknownException(ex);
            }
        }

        public static int GetStatusJrnlRevista(
         ConnectionInfo pInfo,
          string pJR

     )
        {
            string lQuery = "";
             int qtd = 0;

             lQuery = BIBLIOTECA_JRNL_REVISTAQD.GetStatusJrnlRevista;
             lQuery += " and ijr.jrnl_revista_id={0} AND jrnl_revista_STATUS='A' and not exists(select * from biblioteca_itememprestimo ie ";
             lQuery += "where ie.cod_item=ijr.itemjrnl_revista_id and ie.ttab_id=2)";

             lQuery = string.Format(lQuery, pJR);

             SelectCommand lSelect = new SelectCommand(lQuery);
             qtd = int.Parse(lSelect.ReturnScalar(Instance.CreateDatabase(pInfo)).ToString());

             return qtd;
        }

        public static int COUNT_GetAllPedidosAlmoxarifado(
          ConnectionInfo pInfo, decimal pSPED_ID
        )
        {
            int lReturn = 0;

            string lQuery = "Select count(*) from (";
            lQuery += AcompanharQD.qAllPedidosAlmoxarifado;
            lQuery += @" where pm.pedido_status='A'
                        and dd.ddoc_status='A'
                        and pf.pesf_status='A'
                        and p.pes_status='A'
                        and setor.set_status='A'
                        and s.sped_status='A'
                        and pm.pesf_solicitante = pf.pesf_id
                        and pf.pes_id=p.pes_id
                        and pf.set_id=setor.set_id
                        and pm.doc_id = dd.doc_id
                        and s.sped_id = pm.sped_id
                        AND PM.SPED_ID = {0}
                        order by pm.PEDIDO_DATASOLICITACAO desc )";
            lQuery = string.Format(lQuery, pSPED_ID);

            SelectCommand lSelect = new SelectCommand(lQuery);
            lReturn = int.Parse(lSelect.ReturnScalar(Instance.CreateDatabase(pInfo)).ToString());

            return lReturn;
        }