private static NpgsqlException createNpgsqlException(bool transient)
{
var innerException = transient ? (Exception) new TimeoutException() : new DivideByZeroException();
var ex = new NpgsqlException("exception occurred", innerException);
return(ex);
}
public NisreDbException(NpgsqlException exception, PgParam parameters)
{
if (exception.Data.Count <= 0)
{
return;
}
var code = exception.Data["Code"].ToString();
var message = exception.Data["MessageText"].ToString();
var where = exception.Data["Where"].ToString();
var hint = exception.Data["Hint"].ToString();
ExceptionData.Add("code", code);
ExceptionData.Add("messageText", message);
ExceptionData.Add("where", where);
ExceptionData.Add("hint", hint);
ExceptionData.Add("parameters", parameters.GetParamatersInfo().ToJson());
if (code == "P0001")
{
this.CustomException = true;
this.Message = message;
}
else
{
CustomException = false;
Message = "System Error";
}
}
/// <summary>
/// Constructor que recibe la excepcion, instacia los metodos y, registra la hora y fecha de la incidencia
/// </summary>
/// <param name="e">Excepcion de la base de datos</param>
public BaseDeDatosExcepcion(NpgsqlException e)
{
excepcion = e;
fechaHora = DateTime.Now;
nombreMetodos = new List <String>();
}
/// <summary>
/// Turns all NpgsqlException into DBExceptions.
/// Error Codes: http://www.postgresql.org/docs/9.1/static/errcodes-appendix.html
/// </summary>
/// <param name="ex">A NpgsqlException to handle.</param>
/// <author>Jannik Arndt, Bernd Nottbeck</author>
public override void HandleException(DbException ex)
{
//NpgsqlException ex
NpgsqlException exception = ex as NpgsqlException;
if (exception != null)
{
switch (exception.Code)
{
case "01000":
throw new DBException("Warning! " + exception.Message, ex);
//throw new WarningException(ex.Message, ex);
case "08000":
throw new NoConnectionException(exception.Message, ex);
case "28000":
throw new WrongCredentialsException(exception.Message, ex);
case "28P01":
throw new WrongCredentialsException(exception.Message, ex);
case "3D000":
throw new DatabaseDoesNotExist(exception.Message, ex);
default:
throw new DBException(exception.Message + exception.Code, ex);
}
}
throw ex;
}
/// <summary>
/// Process exception when excute Update
/// </summary>
/// <param name="ex">Exception</param>
/// <returns>UpdateDataResult<T></returns>
public UpdateDataResult <T> ProcessException <T>(NpgsqlException ex, UpdateDataResult <T> updateDataResult)
{
//Duplicate unique fields case
if (((PostgresException)ex).SqlState == "23505")
{
string[] fields = new string[] { };
string detail = ((PostgresException)ex).Detail;
string leftStr = detail.Split('=')[0];
string strFieldNames = leftStr.Split('(')[1].Split(')')[0];
string[] str = strFieldNames.Split(',');
fields = str.Select(p => p.Replace(@"""", "")?.Replace(@"\", "")).ToArray();
updateDataResult.Error = new ErrorAction()
{
ErrorType = DataActionErrorTypeEnum.DuplicateData,
Fields = fields
};
}
//Require fields are null
if (((PostgresException)ex).SqlState == "23502")
{
string[] fields = new string[1];
string message = ((PostgresException)ex).Message;
fields[0] = Regex.Split(message, @"\""")[1];
updateDataResult.Error = new ErrorAction()
{
ErrorType = DataActionErrorTypeEnum.MissingFields,
Fields = fields
};
}
return(updateDataResult);
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="e">Excepcion generica</param>
/// <param name="_mensaje">Mensaje asociado al error</param>
public BaseDeDatosExcepcion(NpgsqlException e, string _mensaje)
{
excepcion = e;
mensaje = _mensaje;
fechaHora = DateTime.Now;
}
private void ParsePostgresException(Exception ex)
{
PostgresException pEx1 = ex as PostgresException;
if (pEx1 != null)
{
m_sSqlState = pEx1.Code;
m_sErrorInfo = pEx1.Message.ToLower();
m_sDBMSErrorInfo = pEx1.Message.ToLower();
byte[] buf = BitConverter.GetBytes(pEx1.HResult);
byte[] errCode = new byte[2];
Array.Copy(buf, 0, errCode, 0, 2);
m_iErrorCode = BitConverter.ToInt16(errCode, 0);
}
NpgsqlException pEx = ex as NpgsqlException;
if (pEx != null)
{
m_sErrorInfo = pEx.Message.ToLower();
m_sDBMSErrorInfo = pEx.Message.ToLower();
byte[] buf = BitConverter.GetBytes(pEx.HResult);
byte[] errCode = new byte[2];
Array.Copy(buf, 0, errCode, 0, 2);
m_iErrorCode = BitConverter.ToInt16(errCode, 0);
}
}
void HandleNullReferenceException(Exception ex)
{
if (ex == null)
{
return;
}
if (ex is NullReferenceException && InnerDataStore.Connection.State == ConnectionState.Open)
{
DoReconnect();
return;
}
NpgsqlException npgex = ex as NpgsqlException;
if (npgex != null && npgex.Errors != null && InnerDataStore.Connection.State == ConnectionState.Open)
{
foreach (NpgsqlError error in npgex.Errors)
{
if (error.Message.Contains("broken"))
{
DoReconnect();
return;
}
}
}
throw ex;
}
public void Basic()
{
ExecuteNonQuery(@"
CREATE OR REPLACE FUNCTION emit_exception() RETURNS VOID AS
'BEGIN RAISE EXCEPTION ''testexception'' USING ERRCODE = ''12345''; END;'
LANGUAGE 'plpgsql';
");
NpgsqlException ex = null;
try
{
ExecuteNonQuery("SELECT emit_exception()");
Assert.Fail("No exception was thrown");
}
catch (NpgsqlException e)
{
ex = e;
}
Assert.That(ex.MessageText, Is.EqualTo("testexception"));
Assert.That(ex.Severity, Is.EqualTo(ErrorSeverity.Error));
Assert.That(ex.Code, Is.EqualTo("12345"));
Assert.That(ex.Position, Is.EqualTo(0));
var data = ex.Data;
Assert.That(data["Severity"], Is.EqualTo(ErrorSeverity.Error));
Assert.That(data["Code"], Is.EqualTo("12345"));
Assert.That(data.Contains("Position"), Is.False);
Assert.That(ExecuteScalar("SELECT 1"), Is.EqualTo(1), "Connection in bad state after an exception");
}
protected bool HandleNpgsqlException(NpgsqlException e, ref int attemptNumber, int maxAttempts)
{
// Don't try to handle anything other than interference errors
//if (!e.ErrorCode.Contains("40001") && !e.Code.Contains("40P01")) return false;
//var message = string.Format(CultureInfo.InvariantCulture,
// "Database Condition: {0}\r\nAttempt No: {1} of {2}\r\nStack Trace: {3}",
// e.Code,
// attemptNumber + 1, maxAttempts,
// new StackTrace());
//if (attemptNumber == maxAttempts)
//{
// Logger.Error(message);
// throw new ApplicationException(
// string.Format("Failed to commit transaction after {0} attempts\r\nStack Trace: {1}",
// maxAttempts,
// new StackTrace()), e);
//}
//// These are happening all the time - trace only
//Logger.Trace(message);
//// Random backoff here
//var baseDelay = 50 * attemptNumber;
//var randomDelay = Rnd.Next(20, 100);
//Thread.Sleep(baseDelay + randomDelay);
//// And try again
//++attemptNumber;
// Mark the error as handled
return(true);
}
/// <summary>
/// </summary>
/// <param name="ne">
/// </param>
/// <param name="SqlQuery">
/// </param>
private void PostgressqlLogger(NpgsqlException ne, string SqlQuery)
{
FileInfo t = new FileInfo("SqlError.log");
if (t.Exists)
{
TextWriter tex = new StreamWriter(t.OpenWrite());
tex.WriteLine("Date/Time: " + DateTime.Now.ToString());
tex.WriteLine(" ");
tex.WriteLine("Sql String: " + SqlQuery);
tex.WriteLine(" ");
tex.WriteLine("Sql Error: ");
tex.WriteLine(ne);
tex.Write(tex.NewLine);
tex.Flush();
tex.Close();
tex = null;
t = null;
}
else
{
StreamWriter sw = t.CreateText();
sw.WriteLine("Date/Time: " + DateTime.Now.ToString());
sw.WriteLine(" ");
sw.WriteLine("Sql String: " + SqlQuery);
sw.WriteLine(" ");
sw.WriteLine("Sql Error: ");
sw.WriteLine(ne);
sw.Write(sw.NewLine);
sw.Flush();
sw.Close();
sw = null;
t = null;
}
}
private void LogError(IDbCommand command, int error, NpgsqlException ex)
{
TraceSource.TraceEvent(TraceEventType.Verbose, error, command.CommandText);
TraceSource.TraceEvent(TraceEventType.Information, error, "{0}:{1} - {2}", Connection.Host, Connection.Port, Connection.Database);
TraceSource.TraceEvent(TraceEventType.Error, error, "{0}", ex);
if (ex.ErrorSql != null)
{
TraceSource.TraceEvent(TraceEventType.Error, error, ex.ErrorSql);
}
}
public void CanDetectTransientException()
{
var jobStoreSupport = new TestJobStoreSupport();
var npgsqlException = new NpgsqlException("timeout", new TimeoutException());
Assert.That(jobStoreSupport.IsTransientPublic(npgsqlException), Is.True);
var sqlException = new SqlExceptionSimulator();
Assert.That(jobStoreSupport.IsTransientPublic(sqlException), Is.True);
}
public string GetSqlExceptionMessage(NpgsqlException ex)
{
string ERR = "Index #\n" +
"Message: " + ex.Message + "\n" +
"LineNumber: " + ex.HelpLink + "\n" +
"Source: " + ex.Source + "\n" +
"Procedure: " + ex.InnerException + "\n";
return(ERR);
}
public static string GetDBErrorResource(NpgsqlException ex)
{
string message = ResourceManager.TryGetResourceFromCache("DbErrors", ex.Code);
if (string.IsNullOrWhiteSpace(message) || message == ex.Code)
{
return(ex.Message);
}
return(message);
}
private static string GetDBErrorResource(NpgsqlException ex)
{
string message = DbErrors.Get(ex.Code);
if (message == ex.Code)
{
return(ex.Message);
}
return(message);
}
public override bool IsConnectionException(DbException e)
{
NpgsqlException exception = e as NpgsqlException;
if (exception == null)
{
return(false);
}
// TODO: handle localization ? do it by Code ?
return(exception.GetBaseException().Message.StartsWith("Failed to establish a connection to "));
}
private static string GetDBErrorResource(NpgsqlException ex)
{
Assembly ass = GetAssemblyByName("MixERP.Net.DbFactory");
string message = LocalizationHelper.GetResourceString(ass, "MixERP.Net.DbFactory.Resources.DbErrors", ex.Code);
if (message == ex.Code)
{
return(ex.Message);
}
return(message);
}
/// <summary>
/// Determines if a database exception is a transient exception and if the operation could be retried.
/// </summary>
/// <param name="exception">The exception to test.</param>
/// <returns>True if the exception is transient.</returns>
public override bool IsTransientException(Exception exception)
{
NpgsqlException mex = (NpgsqlException)exception;
switch (mex.ErrorCode)
{
case -2147467259: // socket exception - could not connect to host
return(true);
}
return(false);
}
private static HttpStatusCode ConvertToHttpStatusCode(Exception exception)
{
return(exception switch
{
EntityNotFoundException _ => HttpStatusCode.NotFound,
// Return HTTP status code 503 in case calling the underlying database resulted in an exception.
// See more here: https://stackoverflow.com/q/1434315.
NpgsqlException _ => HttpStatusCode.ServiceUnavailable,
// Also return HTTP status code 503 in case the inner exception was thrown by a call made against the
// underlying database.
{ InnerException : NpgsqlException _ } => HttpStatusCode.ServiceUnavailable,
/// <summary>
/// Check specific connection provider if connection broken
/// </summary>
/// <param name="ex">Exception from Exec void</param>
/// <returns>true - connection broken</returns>
protected override bool CheckConnectionBroken(Exception ex)
{
bool b = false;
NpgsqlException e = ex as NpgsqlException;
//if (e != null && e.Errors != null && e.Errors.Count > 0)
//{
// NpgqlError error = (NpgsqlError)e.Errors[0];
// b = String.IsNullOrEmpty(error.Code) && String.IsNullOrEmpty(error.ErrorSql); // Error no connect DB
//}
return(b);
}
public static void TrataExcecao(NpgsqlException excecao, NpgsqlCommand Comando)
{
// NetworkLog.Insert(excecao, Comando.CommandText);
string detalhes = "";
if (Comando.Parameters == null || Comando.Parameters.Count == 0)
{
detalhes = "Comando: " + Comando.CommandText;
}
else
{
detalhes = "Parâmetros:";
foreach (NpgsqlParameter parmt in Comando.Parameters)
{
if (parmt.Value != null)
{
detalhes += parmt.Value.ToString() + ", ";
}
}
detalhes = detalhes.Substring(0, detalhes.Length - 2) + ".";
}
switch (excecao.Data["Code"]?.ToString())
{
case "":
MessageBox.Show("Falha ao estabelecer conexão com o servidor (MRX157\\DEVELOPER). O endereço IP do servidor está correto?\r\n* Porta 5432 no firewall do servidor está desbloqueada?\r\n* As configurações do servidor PostgreSQL estão corretas?", "Falha ao estabelecer conexão", MessageBoxButtons.OK, MessageBoxIcon.Warning);
break;
case "28000":
MessageBox.Show("Atenção! O nome do usuário e/ou a senha são inválidos. Tente novamente. ", "Erro ao entrar", MessageBoxButtons.OK, MessageBoxIcon.Warning);
break;
case "25P02":
MessageBox.Show("Atenção! A transação atual foi abortada e os dados podem não ter sido salvos completamente. Motivo: " + excecao.Message + " \r\n\r\n" + detalhes, "Transação abortada", MessageBoxButtons.OK, MessageBoxIcon.Warning);
break;
case "22P05":
MessageBox.Show("Atenção! Caractere inválido encontrado.\r\nErro:" + excecao.Message + "\r\n\r\n" + detalhes, "Erro ao inserir", MessageBoxButtons.OK, MessageBoxIcon.Warning);
break;
case "28P01":
MessageBox.Show("Atenção! O nome do usuário e/ou a senha são inválidos. Tente novamente.", "Erro ao autenticar", MessageBoxButtons.OK, MessageBoxIcon.Warning);
break;
default:
MessageBox.Show(excecao.Data["Code"]?.ToString() + ": " + excecao.Message + "\r\n\r\n" + detalhes, "Erro no banco de dados", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly);
break;
}
}
private static FrameworkException FormatException(NpgsqlException ex)
{
var details = string.IsNullOrEmpty(ex.Detail) ? string.Empty : @"
Error: " + (ex.Detail.Contains("\r\n") ? ex.Detail : ex.Detail.Replace("\n", "\r\n"));
if (!string.IsNullOrEmpty(ex.ErrorSql))
{
return(new FrameworkException(ex.Message + details + @"
SQL: " + ex.ErrorSql.Substring(0, Math.Min(ex.ErrorSql.Length, 200)), ex));
}
if (!string.IsNullOrEmpty(ex.Where))
{
return(new FrameworkException(ex.Message + details + @"
Near: " + ex.Where, ex));
}
return(new FrameworkException(ex.Message + details, ex));
}
private static HttpStatusCode ConvertToHttpStatusCode(Exception exception)
{
return(exception switch
{
EntityNotFoundException _ => HttpStatusCode.NotFound,
// Return HTTP status code 503 in case calling the underlying database resulted in an exception.
// See more here: https://stackoverflow.com/q/1434315.
NpgsqlException _ => HttpStatusCode.ServiceUnavailable,
// Also return HTTP status code 503 in case the inner exception is caused by calling the underlying
// database.
{ } someException when
someException.InnerException is NpgsqlException => HttpStatusCode.ServiceUnavailable,
// Fallback to HTTP status code 500.
_ => HttpStatusCode.InternalServerError
});
public static void tiraExcepcion(NpgsqlException myE)
{
//ver las posibles excepciones de sql acá!!
if (myE.Code == "23514")//viola la restricción check
{
//este mensaje hay q mejorarlo es solo para articulos
string ex = myE.Message;
if (ex.Contains("stock_existencias_chk"))
{
throw new ExcepcionPropia("Algun articulo NO posee stock necesario");
}
else
{
throw new ExcepcionPropia(myE.Message);
}
}
else
{
throw new ExcepcionPropia(myE.Message);
}
////EXCEPCIONES QUE NO SE PUEDEN ARREGLAR
//if (myE.Code == 1042) //1042: Sin servicio MYSQL o sin conexión a la BD
//{
// //Mando excepción propia a la capa de Presentación con el mensaje que quiero
// throw new ExcepcionCritica("Código de Error: 1042 - Ocurrió un problema en la conexión a la Base de Datos. Comuníquese con el Soporte Técnico.");
//}
//else if (myE.Number == 1044)//1044: Está mal la cadena de conexión
//{
// //Mando excepción propia a la capa de Presentación con el mensaje que quiero
// throw new ExcepcionCritica("Código de Error: 1044 - Ocurrió un problema en la conexión a la Base de Datos. Comuníquese con el Soporte Técnico.");
//}
//else if (myE.Number == 0) //Falta el stored Procedure
//{
// //Mando excepción propia a la capa de Presentación con el mensaje que quiero
// throw new ExcepcionCritica("Código de Error: 0 - Ocurrió un problema en la conexión a la Base de Datos. Comuníquese con el Soporte Técnico.");
//}
//else
//{
// //Mando excepción propia a la capa de Presentación con el mensaje que quiero
// throw new ExcepcionPropia(myE.Message);
//}
}
/// <summary>
/// 保存更改；
/// </summary>
/// <param name="notUniqueErrorMessage">不唯一错误消息</param>
/// <returns>受影响行数</returns>
public override int SaveChanges(string notUniqueErrorMessage)
{
int rowAffected = 0;
try
{
rowAffected = this.SaveChanges();
}
catch (Exception ex)
{
NpgsqlException pgsqlEx = ex.InnerException.InnerException as NpgsqlException;
if (pgsqlEx.Message.Substring(0, 5) == "23505")
{
throw new ApplicationException(notUniqueErrorMessage);
}
throw;
}
return(rowAffected);
}
public void ShouldRetryTest()
{
var dataOptions = new PostgresDataOptions();
dataOptions.ConnectionString = "Host=localhost;";
var mockServerEx = new NpgsqlException();
var postgresEx = new PostgresException();
var generalEx = new Exception("some error");
var anotherSpecificEx = new AggregateException("some aggregated error");
// Should retry only on server specific errors
Assert.True(dataOptions.ShouldRetryOn(mockServerEx));
// Otherwise, should not retry
Assert.False(dataOptions.ShouldRetryOn(postgresEx));
Assert.False(dataOptions.ShouldRetryOn(generalEx));
Assert.False(dataOptions.ShouldRetryOn(anotherSpecificEx));
}
private SqlExceptionType ProcessClientSideException(NpgsqlException clientSideException)
{
var innerException = clientSideException.InnerException;
if (innerException == null)
{
return(SqlExceptionType.Unknown);
}
if (innerException is IOException ioException)
{
if (ioException.InnerException != null)
{
if (ioException.InnerException is SocketException socetException && socetException.SocketErrorCode == SocketError.TimedOut)
{
return(SqlExceptionType.OperationTimeout);
}
}
}
return(SqlExceptionType.Unknown);
}
public static Exception ThrowSqlUserException(NpgsqlException ex, string commandText)
{
if (ex.ErrorCode == CONSTANTS.SQL_USER_HANDLED_EXCEPTION_CODE)
{
var match = Regex.Match(ex.Message, "<ERROR ID=([0-9]+)>([^<]*)</ERROR>");
if (match.Success)
{
var errCode = int.Parse(match.Groups[1].Value);
var errMessage = match.Groups[2].Value;
if (!string.IsNullOrEmpty(errMessage))
{
return(ErrorUtils.CreateErrorWithSubMessage(errCode, errMessage));
}
return(ErrorUtils.CreateError(errCode));
}
}
return(ErrorUtils.CreateErrorWithSubMessage(
ERR_SQL.ERR_SQL_EXECUTE_COMMAND_FAIL, ex.Message, commandText));
}
public void Retry_Attempt3_Rethrow()
{
/// 3 NpgsqlExceptions
// Arrange
var count = 0;
var exc = new NpgsqlException();
_dbMock.Setup(m => m.ExecuteAsync("sql", null)).Throws(exc);
// Act + Assert
//AssertException.Thrown
//(
// () => _db.ExecuteAsync("sql"),
// ex => Assert.AreEqual(exc, ex)
//);
//_dbMock.Verify(m => m.ExecuteAsync("sql", null), Times.Exactly(3));
Assert.Inconclusive();
}
//
// WriteToEventLog
// A helper function that writes exception detail to the event log. Exceptions
// are written to the event log as a security measure to avoid private database
// details from being returned to the browser. If a method does not return a status
// or boolean indicating the action succeeded or failed, a generic exception is also
// thrown by the caller.
//
private void WriteToEventLog(NpgsqlException e, string action)
{
EventLog log = new EventLog();
log.Source = eventSource;
log.Log = eventLog;
string message = string.Format("An exception occurred communicating with the data source.\n\nAction: {0}\n\nException: {1}", action, e);
log.WriteEntry(message);
}
// Login failed is thrown when database does not exist (See Issue #776) private static bool IsDoesNotExist(NpgsqlException exception) => exception.Code == "3D000";
