public static DataSet From(ConnectionInfo pInfo, string pSelectQuery, DataFieldCollection pConditionalParameters) { DataBase lDB; Transaction lTransaction = null; DataSet lReturn = new DataSet(); try { lDB = Instance.CreateDatabase(pInfo); lTransaction = new Transaction(lDB); lReturn = From(lTransaction, pSelectQuery, pConditionalParameters); lTransaction.Commit(); } catch (Exception lException) { if (lTransaction != null) lTransaction.Rollback(); throw lException; } return lReturn; }
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; }
/// <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(); }
/// <summary> /// Altera Session NLS_COMP e NLS_SORT, para iguinorar acentos na pesquisa /// </summary> /// <returns></returns> public static void AlterSessionBinary(ConnectionInfo pInfo) { SqlQuery lSqlQuery; int i; lSqlQuery = new SqlQuery(Instance.CreateDatabase(pInfo), "ALTER SESSION SET NLS_COMP=LINGUISTIC"); i = lSqlQuery.ExecuteNonQuery(); lSqlQuery = new SqlQuery(Instance.CreateDatabase(pInfo), "ALTER SESSION SET NLS_SORT=BINARY_AI"); i = lSqlQuery.ExecuteNonQuery(); }
public static void LoadConnectionInfo() { _ActiveConnection = ConfigurationManager.ConnectionStrings[ConfigurationManager.AppSettings["ActiveConnectionString"]]; _ConnectionInfo = new ConnectionInfo( _ActiveConnection.ConnectionString, (_ActiveConnection.ProviderName.ToLower() == "oracle") ? 0 : 1, (ConfigurationManager.AppSettings["ApplicationServerMode"].ToLower() == "web") ? 0 : 1 ); }
public static DataTable GetAllModeloPeticao( ConnectionInfo pInfo ) { string lQuery = ""; DataTable lTable = new DataTable(); lQuery = ModeloPeticaoQD.qModeloPeticaoTipo; SelectCommand lSelect = new SelectCommand(lQuery); lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo)); return lTable; }
public static DataTable GetSystemUser(ConnectionInfo pInfo, string pUsuario, string pSenha) { string lQuery = ""; DataTable lTable = new DataTable(); pSenha = APB.Framework.Encryption.Crypto.Encode(pSenha); lQuery = SystemUserQD.qSystemUserList; lQuery += " WHERE SUSR_STATUS = 'A' AND SUSR_LOGIN = '******' AND SUSR_PASSWORD='******' "; SelectCommand lSelect = new SelectCommand(lQuery); lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo)); return lTable; }
public static DataTable GetAllTIPOMATERIAXNAAP( ConnectionInfo pInfo ) { string lQuery = ""; DataTable lTable = new DataTable(); lQuery = TIPOMATERIAXNAAPQD.qTIPOPETICAOXNAAPList; lQuery += " WHERE TPMN_STATUS='A' ORDER BY TPMN_DESCRICAO "; SelectCommand lSelect = new SelectCommand(lQuery); lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo)); return lTable; }
public static DataTable GetAllTipoPeticao( ConnectionInfo pInfo ) { string lQuery = ""; DataTable lTable = new DataTable(); lQuery = ModeloPeticaoQD.qTipoPeticao; lQuery += " WHERE TPPT_STATUS='A' ORDER BY TPPT_DESCRICAO "; SelectCommand lSelect = new SelectCommand(lQuery); lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo)); return lTable; }
public static DataTable GetConsultaPecaGeral(ConnectionInfo pInfo, string pCondicao) { string lQuery = ""; DataTable lTable = new DataTable(); lQuery = PECASXNAAPQD.qConsultaPeticao; lQuery += " WHERE PECN_STATUS='A' "; lQuery += pCondicao; lQuery += " ORDER BY PECN_ID DESC "; SelectCommand lSelect = new SelectCommand(lQuery); lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo)); return lTable; }
public static string GetConsultaPecaEnviada( ConnectionInfo pInfo ) { string lQuery = ""; DataTable lTable = new DataTable(); lQuery = PECASXNAAPQD.qPETICAOXNAAPCount; lQuery += " WHERE PECN_STATUS='E' "; SelectCommand lSelect = new SelectCommand(lQuery); lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo)); return lTable.Rows[0]["CONT"].ToString(); }
public static DataTable GetAllPETICAOXNAAP( ConnectionInfo pInfo ) { string lQuery = ""; DataTable lTable = new DataTable(); lQuery = PECASXNAAPQD.qLoadPETICAOXNAAP; lQuery += " WHERE PECN_STATUS='A'"; SelectCommand lSelect = new SelectCommand(lQuery); lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo)); return lTable; }
public static DataTable GetModeloPeticaoByTipo( decimal pTPPT_ID, ConnectionInfo pInfo ) { string lQuery = ""; DataTable lTable = new DataTable(); lQuery = ModeloPeticaoQD.qModeloPeticaoTexto; lQuery += string.Format(" WHERE MDPT_STATUS='A' AND {0} = >>{0}", ModeloPeticaoQD._TPPT_ID.Name); SelectCommand lSelect = new SelectCommand(lQuery); lSelect.Fields.Add(ModeloPeticaoQD._TPPT_ID.Name, pTPPT_ID, (ItemType)ModeloPeticaoQD._TPPT_ID.DBType); lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo)); return lTable; }
public static DataTable GetConsultaPeticaoporTags( ConnectionInfo pInfo , string pCondicao , int pTipo ) { string lQuery = ""; DataTable lTable = new DataTable(); lQuery = PECASXNAAPQD.qConsultaPeticao; lQuery += " WHERE PECN_STATUS='A' "; if (pTipo == 0) lQuery += " AND PECN_TAGS LIKE '%" + pCondicao + "%' "; else lQuery += " AND PECN_TAGS LIKE '" + pCondicao + "' "; lQuery += " ORDER BY PECN_ID DESC "; SelectCommand lSelect = new SelectCommand(lQuery); lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo)); return lTable; }
public static APB.Framework.DataBase.DataBase CreateDatabase(ConnectionInfo pInfo) { //TODO: Fazer direito, a connstring tem que ser criptografada return new APB.Framework.DataBase.DataBase((DataBaseType)pInfo.DataBaseType, pInfo.ConnectionString); }
public static string InsertPeticao(ConnectionInfo pInfo, string pTPN_ID, string pPETN_TAGS, string pPETN_EMENTA, string pPETN_TEXTO, string pPETN_USUARIO) { string lSql = ""; string constr = "Data Source=desenv2; User ID=desenv; Password=desenv"; try { OracleConnection con = new OracleConnection(constr); lSql = " INSERT INTO PETICAOXNAAP(PETN_ID,TPN_ID,PETN_TEXTO,PETN_TAGS,PETN_EMENTA,PETN_STATUS,PETN_REGDATE,PETN_USUARIO) "; lSql += " VALUES(:PETN_ID,:TPN_ID,:PETN_TEXTO,:PETN_TAGS,:PETN_EMENTA,'A',SYSDATE,:PETN_USUARIO) "; OracleCommand cmd = new OracleCommand(); cmd.CommandText = lSql; cmd.Connection = con; decimal lSequence; lSequence = DataBaseSequenceControl.GetNext(pInfo, "PETN_ID"); OracleParameter OraParameter1 = new OracleParameter(); OracleParameter OraParameter2 = new OracleParameter(); OracleParameter OraParameter3 = new OracleParameter(); OracleParameter OraParameter4 = new OracleParameter(); OracleParameter OraParameter5 = new OracleParameter(); OracleParameter OraParameter6 = new OracleParameter(); OraParameter1.OracleType = OracleType.Number; OraParameter1.ParameterName = ":PETN_ID"; OraParameter1.Value = lSequence; OraParameter2.OracleType = OracleType.Number; OraParameter2.ParameterName = ":TPN_ID"; OraParameter2.Value = Convert.ToDecimal(pTPN_ID); OraParameter3.OracleType = OracleType.Clob; OraParameter3.ParameterName = ":PETN_TEXTO"; OraParameter3.Value = pPETN_TEXTO; OraParameter4.OracleType = OracleType.VarChar; OraParameter4.ParameterName = ":PETN_TAGS"; OraParameter4.Value = pPETN_TAGS; OraParameter5.OracleType = OracleType.VarChar; OraParameter5.ParameterName = ":PETN_EMENTA"; OraParameter5.Value = pPETN_EMENTA; OraParameter6.OracleType = OracleType.VarChar; OraParameter6.ParameterName = ":PETN_USUARIO"; OraParameter6.Value = pPETN_USUARIO; cmd = new OracleCommand(lSql, con); cmd.Parameters.Add(OraParameter1); cmd.Parameters.Add(OraParameter2); cmd.Parameters.Add(OraParameter3); cmd.Parameters.Add(OraParameter4); cmd.Parameters.Add(OraParameter5); cmd.Parameters.Add(OraParameter6); con.Open(); cmd.ExecuteNonQuery(); cmd.Dispose(); con.Close(); return lSequence.ToString(); } catch (Exception err) { return "ERRO: " + err.ToString(); } }
public static DataTable TableFrom(string pSelectQuery, DataFieldCollection pConditionalParameters, ConnectionInfo pInfo) { DataSet lSet = From(pInfo, pSelectQuery, pConditionalParameters); return lSet.Tables[0]; }
public static DataTable GetPecasEnviadasHomolog( ConnectionInfo pInfo ,string pData ) { string lQuery = ""; DataTable lTable = new DataTable(); lQuery = PECASXNAAPQD.qConsultaPeticao; lQuery += " WHERE PECN_STATUS='E' "; if (pData != "") lQuery += " AND PECN_REGDATE = TO_DATE('" + pData + "','DD/MM/YYYY') "; lQuery += " ORDER BY PECN_REGDATE DESC "; SelectCommand lSelect = new SelectCommand(lQuery); lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo)); return lTable; }
public static decimal GetNext(ConnectionInfo pInfo, string pIDFieldValue) { decimal lID = 1; Transaction lTransaction; DataBase lDataBase; lDataBase = Instance.CreateDatabase(pInfo); lTransaction = new Transaction(lDataBase); try { // 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(lTransaction); 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(lTransaction); lTransaction.Commit(); } catch (Exception ex) { lTransaction.Rollback(); } // Retorna novo valor da chave [SEQUENCE VALUE] return lID; }
public static DataSet From(ConnectionInfo pInfo, string pSelectQuery) { SqlQuery lSelect = new SqlQuery(Instance.CreateDatabase(pInfo), pSelectQuery); DataSet lReturn = lSelect.ExecuteDataSet(); return lReturn; }
public static OperationResult Update( DataFieldCollection pValues, ConnectionInfo pInfo ) { Transaction pTransaction; pTransaction = new Transaction(Instance.CreateDatabase(pInfo)); bool lLocalTransaction = (pTransaction != null); UpdateCommand lUpdate; OperationResult lReturn = new OperationResult(SystemUserQD.TableName, SystemUserQD.TableName); ValidateUpdate(pValues, lReturn); if (lReturn.IsValid) { try { if (lLocalTransaction) { lReturn.Trace("Transação local, instanciando banco..."); } lUpdate = new UpdateCommand(SystemUserQD.TableName); lReturn.Trace("Adicionando campos ao objeto de update"); foreach (DataField lField in pValues.Keys) { lUpdate.Fields.Add(lField.Name, pValues[lField], (ItemType)lField.DBType); } string lSql = ""; lSql = String.Format("WHERE {0} = <<{0}", SystemUserQD._SUSR_ID.Name); lUpdate.Condition = lSql; lUpdate.Conditions.Add(SystemUserQD._SUSR_ID.Name, pValues[SystemUserQD._SUSR_ID].DBToDecimal()); lReturn.Trace("Executando o Update"); lUpdate.Execute(pTransaction); if (!lReturn.HasError) { if (lLocalTransaction) { if (!lReturn.HasError) { lReturn.Trace("Update finalizado, executando commit"); pTransaction.Commit(); } else { pTransaction.Rollback(); } } } else { if (lLocalTransaction) pTransaction.Rollback(); } } catch (Exception ex) { lReturn.OperationException = new SerializableException(ex); if (lLocalTransaction) pTransaction.Rollback(); } } return lReturn; }
public static DataTable From(ConnectionInfo pInfo, string pSelectQuery, int pPage, int pRowCount) { //TODO: Depois vai ter que criptografar pConnectionString e pSelectQuery SelectCommand lSelect = new SelectCommand(pSelectQuery); DataTable lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo), pPage, pRowCount).dataTable; return lTable; }
public static OperationResult Insert( DataFieldCollection pValues, ConnectionInfo pInfo ) { Transaction lTransaction; lTransaction = new Transaction(Instance.CreateDatabase(pInfo)); bool lLocalTransaction = (lTransaction != null); InsertCommand lInsert; OperationResult lReturn = new OperationResult(SystemUserQD.TableName, SystemUserQD.TableName); ValidateInsert(pValues, lReturn); if (!lReturn.HasError) { try { if (lLocalTransaction) { lReturn.Trace("Transação local, instanciando banco..."); } lInsert = new InsertCommand(SystemUserQD.TableName); lReturn.Trace("Adicionando campos ao objeto de insert"); foreach (DataField lField in pValues.Keys) { lInsert.Fields.Add(lField.Name, pValues[lField], (ItemType)lField.DBType); } decimal lSequence; lSequence = DataBaseSequenceControl.GetNext(pInfo, "SUSR_ID"); lInsert.Fields.Add(SystemUserQD._SUSR_ID.Name, lSequence, (ItemType)SystemUserQD._SUSR_ID.DBType); lReturn.Trace("Executando o Insert"); lInsert.Execute(lTransaction); if (!lReturn.HasError) { if (lLocalTransaction) { if (!lReturn.HasError) { lReturn.Trace("Insert finalizado, executando commit"); lTransaction.Commit(); } else { lTransaction.Rollback(); } } } else { if (lLocalTransaction) lTransaction.Rollback(); } } catch (Exception ex) { lReturn.OperationException = new SerializableException(ex); if (lLocalTransaction) lTransaction.Rollback(); } } return lReturn; }
public static string GetConsultaPeticaoTotalPorDia( ConnectionInfo pInfo , string pData ) { string lQuery = ""; DataTable lTable = new DataTable(); lQuery = PECASXNAAPQD.qPETICAOXNAAPCount; lQuery += " WHERE PECN_STATUS IN ('A','E') AND PECN_REGDATE BETWEEN TO_DATE('" + pData + "','DD/MM/YYYY') "; lQuery += " AND TO_DATE('" + pData + " 23:59:59','DD/MM/YYYY HH24:MI:SS') "; SelectCommand lSelect = new SelectCommand(lQuery); lTable = lSelect.ReturnData(Instance.CreateDatabase(pInfo)); return lTable.Rows[0]["CONT"].ToString(); }