private static SqlMessage SqlException(SqlException ex)
{
SqlMessage meResul = new SqlMessage
{
Status = sqlMessagerType.SystemError,
Title = ex.GetType().FullName,
Message = ex.Message,
ExceptionMesseger = ex.ToString()
};
//ex.GetHashCode();
switch (ex.Number)
{
case 4060: // Database không hợp lệ
meResul.Message = "Database không tồn tại, vui lòng kiểm tra hoặc liên hệ với admin !";
break;
case 18456: // không Đăng nhập sql
meResul.Message = "không Đăng nhập sql, vui lòng kiểm tra lại server sql trước khi chạy";
break;
case 547: // Ràng buộc khóa ngoại
meResul.Message = "Ràng buộc khóa ngoại, vui lòng kiểm tra lại !";
break;
case 2627: // trùng khóa chính
meResul.Message = "Trùng khóa chính , vui lòng kiểm tra lại khóa chính";
break;
case 2601: // Unique Index/Constriant Violation
meResul.Message = "Unique Index/Constriant Violation";
break;
case 50000:
meResul.Message = "Vui lòng kiểm tra lại dữ liệu trước khi lưu";
break;
default:
break;
}
return(meResul);
}
private static bool VerifyException(SqlException exception, int count, int?errorNumber = null, int?errorState = null, int?severity = null)
{
// Verify that there are the correct number of errors in the exception
Assert.True(exception.Errors.Count == count, string.Format("FAILED: Incorrect number of errors. Expected: {0}. Actual: {1}.", count, exception.Errors.Count));
// Ensure that all errors have an error-level severity
for (int i = 0; i < count; i++)
{
Assert.True(exception.Errors[i].Class >= 10, "FAILED: verification of Exception! Exception contains a warning!");
}
// Check the properties of the exception populated by the server are correct
if (errorNumber.HasValue)
{
Assert.True(errorNumber.Value == exception.Number, string.Format("FAILED: Error number of exception is incorrect. Expected: {0}. Actual: {1}.", errorNumber.Value, exception.Number));
}
if (errorState.HasValue)
{
Assert.True(errorState.Value == exception.State, string.Format("FAILED: Error state of exception is incorrect. Expected: {0}. Actual: {1}.", errorState.Value, exception.State));
}
if (severity.HasValue)
{
Assert.True(severity.Value == exception.Class, string.Format("FAILED: Severity of exception is incorrect. Expected: {0}. Actual: {1}.", severity.Value, exception.Class));
}
if ((errorNumber.HasValue) && (errorState.HasValue) && (severity.HasValue))
{
string detailsText = string.Format("Error Number:{0},State:{1},Class:{2}", errorNumber.Value, errorState.Value, severity.Value);
Assert.True(exception.ToString().Contains(detailsText), string.Format("FAILED: SqlException.ToString does not contain the error number, state and severity information"));
}
// verify that the this[] function on the collection works, as well as the All function
SqlError[] errors = new SqlError[exception.Errors.Count];
exception.Errors.CopyTo(errors, 0);
Assert.True((errors[0].Message).Equals(exception.Errors[0].Message), string.Format("FAILED: verification of Exception! ErrorCollection indexer/CopyTo resulted in incorrect value."));
return(true);
}
/// <summary>
/// function to translate sql exceptions to readable messages.
/// It also captures cases where sql server is not available and guards against
/// database connection details being leaked.
/// </summary>
/// <param name = "exc"></param>
/// <returns></returns>
/// <remarks>
/// </remarks>
public static string TranslateSQLException(SqlException exc)
{
int i = 0;
var errorMessages = new StringBuilder();
for (i = 0; i <= exc.Errors.Count - 1; i++)
{
SqlError sqlError = exc.Errors[i];
string filteredMessage = string.Empty;
switch (sqlError.Number)
{
case 17:
filteredMessage = "Sql server does not exist or access denied";
break;
case 4060:
filteredMessage = "Invalid Database";
break;
case 18456:
filteredMessage = "Sql login failed";
break;
case 1205:
filteredMessage = "Sql deadlock victim";
break;
default:
filteredMessage = exc.ToString();
break;
}
errorMessages.Append("<b>Index #:</b> " + i + "<br/>" + "<b>Source:</b> " + sqlError.Source + "<br/>" + "<b>Class:</b> " + sqlError.Class + "<br/>" + "<b>Number:</b> " +
sqlError.Number + "<br/>" + "<b>Procedure:</b> " + sqlError.Procedure + "<br/>" + "<b>Message:</b> " + filteredMessage + "<br/>");
}
return(errorMessages.ToString());
}
protected void LogSqlException(string query, SqlException sqlEx)
{
try
{
Trace.CorrelationManager.StartLogicalOperation(_type);
Trace.TraceError("Sql query failed: {0}", query);
Trace.TraceError(sqlEx.ToString());
if (sqlEx.Errors != null && sqlEx.Errors.Count > 0)
{
foreach (var e in sqlEx.Errors.Cast <object>().Take(10))
{
Trace.TraceError(e.ToString());
}
}
}
finally
{
Trace.CorrelationManager.StopLogicalOperation();
}
}
public async Task SendSqlException(SqlException ex)
{
using (var client = new SmtpClient(_email.SMTPServer, _email.Port))
using (var mailMessage = new MailMessage())
{
if (!_email.DefaultCredentials)
{
client.UseDefaultCredentials = false;
client.Credentials = new NetworkCredential(_email.UserName, _email.Password);
}
PrepareMailMessage(_email.DisplayName, $"({_env.EnvironmentName}) SQL ERROR", $"{ex.ToString()}", _email.From, _email.To, mailMessage);
await client.SendMailAsync(mailMessage);
}
}
public bool DAL_CreateCommand(string Query)
{
try
{
if (Connection.DBConnectionString != null)
{
using (SqlConnection connection = new SqlConnection(Connection.DBConnectionString))
{
SqlCommand command = new SqlCommand(Query, connection);
command.Connection.Open();
command.ExecuteNonQuery();
}
successful = true; // Succesfull Transaction
}
}
// Get Information about the Exception
catch (Exception e)
{
if (e is SqlException) // If Exception is SQL Exception
{
SqlException a = (SqlException)e;
switch (a.Number)
{
case 8134:
MessageBox.Show("Fokeret ID", "Fejl", MessageBoxButtons.OK, MessageBoxIcon.Error);
break;
case 8169:
MessageBox.Show("Forkert Input: Fejlen kan skyldes fx. forkeret \"ID\"", "Fejl", MessageBoxButtons.OK, MessageBoxIcon.Error);
break;
case 2627:
MessageBox.Show("Forkert Input: Fejlen kan skyldes fx. Tomt \"Input\" Eller Dataen Eksister i forvejen", "Fejl", MessageBoxButtons.OK, MessageBoxIcon.Error);
break;
case 547:
MessageBox.Show("Forkert Input", "Fejl", MessageBoxButtons.OK, MessageBoxIcon.Error);
break;
default:
MessageBox.Show($"{a.Number.ToString()} \t {a.Message} \t {a.InnerException} \t {a.Data} \t {a.ToString()}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
break;
}
}
else
{
MessageBox.Show($" {e.Message} \t {e.InnerException} \t {e.Data} \t {e.ToString()}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
return(successful);
}
public static void GetExceptionString(Exception ex, out string detailedText)
{
detailedText = null;
if (ex != null)
{
if (ex is SoapException)
{
SoapException soap = ex as SoapException;
detailedText = string.Format("Message:\n {0} \n\n StackTrace:\n {1} \n\n Detail:\n {2}", soap.Message, soap.ToString(), soap.Detail.InnerText);
}
else if (ex is SqlException)
{
SqlException sql = ex as SqlException;
detailedText = string.Format("Message:\n {0} \n\n StackTrace:\n {1} \n\n SqlErrors:\n {2}", sql.Message, sql.ToString(), sql.Errors.Count);
}
else
{
detailedText = string.Format("Message:\n {0} \n\n StackTrace:\n {1}", ex.Message, ex.ToString());
}
}
}
/// <summary>
/// Translate a SqlException to the correct DALException
/// </summary>
/// <param name="ex">SqlException to be translated</param>
/// <returns>An DALException</returns>
protected Exception TranslateException(SqlException ex)
{
Exception dalException = null;
SqlStoredProcedure.TraceSource.TraceEvent(TraceEventType.Error, _eventId, "{0} throwed exception: {1}", this.Name, ex.ToString());
// Return the first Custom exception thrown by a RAISERROR
foreach (SqlError error in ex.Errors)
{
if (error.Number >= 50000)
{
dalException = new DalException(error.Message, ex);
}
}
if (dalException == null)
{
// uses SQLServer 2000 ErrorCodes
switch (ex.Number)
{
case 17:
// SQL Server does not exist or access denied.
case 4060:
// Invalid Database
case 18456:
// Login Failed
dalException = new DalLoginException(ex.Message, ex);
break;
case 547:
// ForeignKey Violation
dalException = new DalForeignKeyException(ex.Message, ex);
break;
case 1205:
// DeadLock Victim
dalException = new DalDeadLockException(ex.Message, ex);
break;
case 2627:
case 2601:
// Unique Index/Constriant Violation
dalException = new DalUniqueConstraintException(ex.Message, ex);
break;
default:
// throw a general DAL Exception
dalException = new DalException(ex.Message, ex);
break;
}
}
// return the error
return(dalException);
}
private async Task <ReplicationTargetMarker> ReplicateBatch(ReplicationSourceMarker sourceMarker, ReplicationTargetMarker targetMarker, int batchSize)
{
targetMarker.LastBatchCount = 0;
try
{
JobEventSourceLog.FetchingStatisticsChunk(batchSize);
var batch = await GetDownloadRecords(Source, sourceMarker, targetMarker, batchSize);
JobEventSourceLog.FetchedStatisticsChunk();
// If there's nothing else to process, then return the specified target marker,
// indicating we're done.
if (batch == null || !batch.Descendants("fact").Any())
{
targetMarker.TimeWindowNeedsReplication = false;
return(targetMarker);
}
JobEventSourceLog.VerifyingCursor(targetMarker.MinTimestamp, targetMarker.MaxTimestamp, targetMarker.Cursor.HasValue ? targetMarker.Cursor.ToString() : "<null>");
var cursor = await GetTargetCursor(Destination, targetMarker);
if (cursor != targetMarker.Cursor)
{
throw new InvalidOperationException(string.Format("Expected cursor for {0} to {1} to have the value of {2} but it had the value for {3}. Aborting.", targetMarker.MinTimestamp, targetMarker.MaxTimestamp, targetMarker.Cursor.HasValue ? targetMarker.Cursor.ToString() : "<null>", cursor.HasValue ? cursor.ToString() : "<null>"));
}
JobEventSourceLog.VerifiedCursor();
// Determine what our new cursor value should be after completing this batch
var newCursor = new ReplicationTargetMarker
{
MinTimestamp = targetMarker.MinTimestamp,
MaxTimestamp = targetMarker.MaxTimestamp,
TimeWindowNeedsReplication = targetMarker.TimeWindowNeedsReplication,
LastBatchCount = batch.Root.Nodes().Count(),
Cursor = (from fact in batch.Descendants("fact")
let originalKey = (int)fact.Element("originalKey")
orderby originalKey descending
select originalKey).First()
};
var minBatchTime = batch.Descendants("fact").Min(f => DateTime.Parse(f.Element("downloadTimestamp").Value));
var maxBatchTime = batch.Descendants("fact").Max(f => DateTime.Parse(f.Element("downloadTimestamp").Value));
JobEventSourceLog.SavingDownloadFacts(newCursor.LastBatchCount, minBatchTime, maxBatchTime);
SqlException potentialException = null;
try
{
await PutDownloadRecords(Destination, batch, targetMarker, newCursor);
}
catch (SqlException sqlException)
{
// If we got an exception, it's possible that the batch was still committed.
// Capture the exception in case we decide to throw it because the batch failed.
potentialException = sqlException;
}
// See if our new cursor was committed
JobEventSourceLog.CheckingCursor();
var committedCursor = await GetTargetCursor(Destination, newCursor);
JobEventSourceLog.CheckedCursor(committedCursor.HasValue ? committedCursor.Value.ToString() : "<null>");
if (potentialException != null)
{
// An exception occurred. It's possible that the batch actually succeeded though.
if (committedCursor == newCursor.Cursor)
{
// Yep, the batch actually succeeded despite the reported exception
// A known scenarios for this is when a timeout is reported but the
// batch is actually committed
JobEventSourceLog.RecoveredFromErrorSavingDownloadFacts(targetMarker.MinTimestamp, targetMarker.MaxTimestamp, targetMarker.Cursor.HasValue ? targetMarker.Cursor.Value.ToString() : "<null>", newCursor.Cursor.HasValue ? newCursor.Cursor.Value.ToString() : "<null>", committedCursor.HasValue ? committedCursor.Value.ToString() : "<null>", potentialException.ToString());
}
else if (committedCursor == targetMarker.Cursor)
{
// Nope, the batch actually failed. Re-throw the exception, and we'll try
// to recover by retrying up to the max failure count.
throw potentialException;
}
}
if (committedCursor != newCursor.Cursor)
{
// We didn't get an exception, but our committed cursor doesn't match expectations
// Let's abort because we don't know what just happened
throw new InvalidOperationException(string.Format("Expected cursor for {0} to {1} to have the value of {2} but it had the value for {3}. Aborting.", newCursor.MinTimestamp, newCursor.MaxTimestamp, newCursor.Cursor.HasValue ? newCursor.Cursor.ToString() : "<null>", committedCursor.HasValue ? committedCursor.Value.ToString() : "<null>"));
}
JobEventSourceLog.SavedDownloadFacts(newCursor.LastBatchCount);
CurrentFailures = 0;
return(newCursor);
}
catch (SqlException exception)
{
// We will ignore failures up to the max failure count, at which time we abort.
if (++CurrentFailures == MaxFailures)
{
throw;
}
JobEventSourceLog.RecoveredFromFailedBatch(CurrentFailures, MaxFailures, exception.ToString());
return(targetMarker);
}
}
/// <summary>
/// Returns a string that represents the current <see cref="T:System.Data.SqlClient.SqlException"/> object, and includes the client connection ID (for more information, see <see cref="P:System.Data.SqlClient.SqlException.ClientConnectionId"/>).
/// </summary>
///
/// <returns>
/// A string that represents the current <see cref="T:System.Data.SqlClient.SqlException"/> object.<see cref="T:System.String"/>.
/// </returns>
public override string ToString()
{
return(SqlException.ToString());
}