/// <summary>
/// Creates a new instance of the Optimistic Lock Exception with the Error Number, Sql Exception
/// </summary>
/// <param name="errorNumber">Error Number</param>
/// <param name="sqlException">Sql Exception</param>
public OptimisticLockException(long errorNumber, SqlException sqlException)
{
this.ErrorNumber = errorNumber;
this.SqlException = sqlException;
}
private string FormatSqlException(SqlException ex, string sql)
{
return(Helper.ShowErrors(ex) + Environment.NewLine + sql);
}
private static bool IsDoesNotExist(SqlException exception)
{
// TODO Explore if there are important scenarios where this could give a false negative
// Login failed is thrown when database does not exist
return(exception.Number == 4060);
}
private bool IsInvalidColumnException(SqlException ex)
{
return(ex.Message.Contains("Invalid column name"));
}
public SqlException Update(Staff staff, int id)
{
string update = "UPDATE Staff " +
"SET StaffNumber = @StaffNumber, FirstName = @FirstName, LastName = @LastName, FullAddress = @FullAddress, DateOfBirth = @DateOfBirth, " +
"Gender = @Gender, InsuranceNumber = @InsuranceNumber, CurrentPosition = @CurrentPosition, CurrentSalary = @CurrentSalary, " +
"SalaryScale = @SalaryScale, NumberOfHoursPerWeek = @NumberOfHoursPerWeek, PermenantOrTemporary = @PermenantOrTemporary, " +
"SalaryPayment = @SalaryPayment, AppointmentNumber = @AppointmentNumber, WorkExperienceID = @WorkExperienceID, QualificationID = @QualificationID " +
"WHERE StaffNumber = @id;";
// 1. declare command object with parameter + insert query
SqlCommand cmd = InitializeDatabase.CreateSqlCommand(update);
// 2. define parameters used in command object
List <SqlParameter> prm = new List <SqlParameter>()
{
new SqlParameter("@StaffNumber", SqlDbType.Int)
{
Value = staff.StaffNumber
},
new SqlParameter("@FirstName", SqlDbType.NVarChar)
{
Value = staff.FirstName
},
new SqlParameter("@LastName", SqlDbType.NVarChar)
{
Value = staff.LastName
},
new SqlParameter("@FullAddress", SqlDbType.NVarChar)
{
Value = staff.FullAddress
},
new SqlParameter("@DateOfBirth", SqlDbType.DateTime)
{
Value = staff.DateOfBirth
},
new SqlParameter("@Gender", SqlDbType.NVarChar)
{
Value = staff.Gender
},
new SqlParameter("@InsuranceNumber", SqlDbType.Int)
{
Value = staff.InsuranceNumber
},
new SqlParameter("@CurrentPosition", SqlDbType.NVarChar)
{
Value = staff.CurrentPosition
},
new SqlParameter("@CurrentSalary", SqlDbType.Float)
{
Value = staff.CurrentSalary
},
new SqlParameter("@SalaryScale", SqlDbType.NVarChar)
{
Value = staff.SalaryScale
},
new SqlParameter("@NumberOfHoursPerWeek", SqlDbType.Int)
{
Value = staff.NumberOfHoursPerWeek
},
new SqlParameter("@PermenantOrTemporary", SqlDbType.NVarChar)
{
Value = staff.PermenentOrTemporary
},
new SqlParameter("@SalaryPayment", SqlDbType.NVarChar)
{
Value = staff.SalaryPayment
},
new SqlParameter("@AppointmentNumber", SqlDbType.Int)
{
Value = staff.AppointmentNumber
},
new SqlParameter("@WorkExperienceID", SqlDbType.Int)
{
Value = staff.WorkExperienceID
},
new SqlParameter("@QualificationID", SqlDbType.Int)
{
Value = staff.QualificationID
},
new SqlParameter("@id", SqlDbType.Int)
{
Value = id
},
};
// 3. add new parameter to command object
cmd.Parameters.AddRange(prm.ToArray());
//run
SqlException res = InitializeDatabase.RunSqlCommand(cmd);
return(res);
}
internal static bool IsUniqueConstraintViolation(this SqlException exception)
{
// https://technet.microsoft.com/en-us/library/aa258747%28v=sql.80%29.aspx
return(exception.Number == 2601);
}
private void tsbDBPost_Click(object sender, EventArgs e)
{
int NewRowIndex = -1;
try
{
if (IsValidData() == true)
{
switch (__Table.TableStatus)
{
case TableStatus.tsInsert:
{
DataRow drDataRow = dtDataTable.NewRow();
drDataRow[0] = ntbBaseCode.Text.Trim();
drDataRow[1] = txtBaseName.Text.Trim();
if ((dtDataTable.Columns.Count == 3) && (dtDataTable.Columns[2].ColumnName == "FK_WorkStationCode"))
{
drDataRow[2] = User.WorkStationCode.ToString();
}
//switch (__Table.Alias)
//{
// case "_WorkGroup":
// case "b_BreakType":
// {
// drDataRow[2] = User.WorkStationCode.ToString();
// break;
// }
//}
dtDataTable.Rows.Add(drDataRow);
NewRowIndex = dtDataTable.Rows.IndexOf(drDataRow);
break;
}
case TableStatus.tsEdit:
{
dtDataTable.Rows[cmCurrencyManager.Position][0] = ntbBaseCode.Text.Trim();
dtDataTable.Rows[cmCurrencyManager.Position][1] = txtBaseName.Text.Trim();
break;
};
}
SqlException eSqlException = pBFL.SetDataTable(dtDataTable, __Table.TableName);
if (eSqlException == null)
{
dtDataTable.AcceptChanges();
if (NewRowIndex >= 0)
{
cmCurrencyManager.Position = NewRowIndex;
}
if (__Table.TableStatus == TableStatus.tsInsert)
{
pBFL.Logging(User.UserCode, __Table.PK_TableCode, Macro.Action.aInsert, User.IP);
pCCL.SetGroupBox(gbDetails, true, true);
ntbBaseCode.Focus();
}
else
{
pBFL.Logging(User.UserCode, __Table.PK_TableCode, Macro.Action.aUpdate, User.IP);
}
}
else
{
dtDataTable.RejectChanges();
CCL.pMB.Show(pBFL.GetErrorMessage(eSqlException, __Table.TableStatus), pBFL.GetResourceString("strErrorTitle"), MessageBoxIcon.Error, MessageBoxButtons.OK);
}
}
}
catch (Exception eException)
{
System.Windows.Forms.MessageBox.Show(eException.Message);
}
}
protected abstract bool CanRetrySqlException(SqlException sqlException);
public DirectResult SaveSetting(int parentId, IList<iffsPackingListSetting> PackingListSettings)
{
using (var transaction = new TransactionScope((TransactionScopeOption.Required), new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted }))
{
_context.Database.Connection.Open();
_context.Database.CommandTimeout = int.MaxValue;
try
{
foreach (var item in PackingListSettings)
{
// Request.Params[""]
int employeeId = 0;
var objUser = (coreUser)Session[Constants.CurrentUser];
if (objUser != null && objUser.EmployeeId != null)
{
employeeId = (int)objUser.EmployeeId;
}
if (parentId != 0)
item.ParentId = parentId;
if (item.Id.Equals(0))
{
/* ***************************************************
* Concurrency controlling scheme using global locking
* ***************************************************/
// var objOperationType = _lookup.GetAll((Lookups.LupOperationType)).Where(o => o.Id == PackingList.Id).FirstOrDefault();
CyberErp.Presentation.Iffs.Web.MvcApplication httpapplication = HttpContext.ApplicationInstance as CyberErp.Presentation.Iffs.Web.MvcApplication;
httpapplication.Application.Lock();
// PackingList.Number = GetDocumentNumber("PackingList");//objOperationType.Code + "/" +
_PackingListSetting.AddNew(item);
// UpdateDocumentNumber("PackingList");
httpapplication.Application.UnLock();
}
else
{
_PackingListSetting.Edit(item);
}
}
_context.SaveChanges();
transaction.Complete();
return this.Json(new { success = true, data = "PackingList has been saved Successfully!" });
}
//catch (SQLException Ex)
//{
//}
catch (Exception ex)
{
if (ex.InnerException.InnerException is SqlException)
{
SqlException sqlException = ex.InnerException.InnerException as SqlException;
if (sqlException.Number == 2601)
return this.Json(new { success = false, data = "The items that are entered are already exist!! " });
return this.Json(new { success = false, data = ex.InnerException != null ? ex.InnerException.Message : ex.Message });
}
return this.Json(new { success = false, data = ex.InnerException != null ? ex.InnerException.Message : ex.Message });
}
}
}
/// <summary>
/// put the data from imports table to actual data after constraints check
/// </summary>
private bool ProcessFile(string fileName, ref ArrayList processedMessages)
{
int idImport = ApplicationConstants.INT_NULL_VALUE;
int resultChronologicalOrder = ApplicationConstants.INT_NULL_VALUE;
int NonExistingAssociates = ApplicationConstants.INT_NULL_VALUE;
string fileNameNonExistingAssociates = string.Empty;
//check if file is not already imported
string[] str = Path.GetFileName(fileName).Split('_');
string cleanFileName = str[0] + Path.GetExtension(fileName);
Imports imp = new Imports(SessionManager.GetSessionValueNoRedirect(this.Page, SessionStrings.CONNECTION_MANAGER));
bool validationResult;
validationResult = CheckFileAlreadyExists(imp, fileName, cleanFileName, processedMessages);
if (validationResult == false)
{
return(false);
}
try
{
resultChronologicalOrder = imp.CheckChronologicalImports(cleanFileName);
}
catch (IndException ex)
{
processedMessages.Add(ex.Message);
ChronologicalErrorsToDB(ex.Message, fileName);
return(false);
}
//write in imports table
try
{
idImport = imp.InsertToImportsTable(fileName, currentUser.IdAssociate);
}
catch (IndException ex)
{
processedMessages.Add(string.Format(ApplicationMessages.IMPORT_FILE_ERROR, cleanFileName));
LogErrorToDataBase(string.Format(ApplicationMessages.IMPORT_FILE_UPLOAD_ERROR, cleanFileName, ex.Message), fileName);
return(false);
}
if (idImport < 0)
{
processedMessages.Add(string.Format(ApplicationMessages.IMPORT_FILE_ERROR, cleanFileName));
return(false);
}
int result = ApplicationConstants.INT_NULL_VALUE;
string duplicateError = string.Empty;
bool isConsistencyKRMError = false; //KRM stands for "key row(s) missing"
String consistencyKRMError = String.Empty;
String consistencyKRMMessage = "key row(s) were missing from import";
try
{
result = imp.InsertToActualTable(fileName, currentUser.IdAssociate, idImport);
}
catch (IndException ex)
{
result = -100;
if (((IndException)ex).BaseException is SqlException)
{
SqlException sqlBaseException = ((SqlException)((IndException)ex).BaseException);
if (sqlBaseException.Number == 2627) //The exception number for primary key violation
{
duplicateError = "At least one record from the file " + cleanFileName + " already exists in the database.";
}
if (sqlBaseException.Message.Contains(consistencyKRMMessage))
{
consistencyKRMError = sqlBaseException.Message;
isConsistencyKRMError = true;
}
}
if (!isConsistencyKRMError) //for this we write the message below
{
if (string.IsNullOrEmpty(duplicateError))
{
LogProcessErrorToDataBase(idImport, ex.Message, fileName);
}
else
{
LogProcessErrorToDataBase(idImport, duplicateError, fileName);
}
}
}
// consistency error treatment
int rowsInserted = 0;
String strError = String.Empty;
try
{
if (isConsistencyKRMError)
{
strError += consistencyKRMError;
rowsInserted = InsertKeyRowsMissingInDB(idImport);
if (rowsInserted > 0)
{
DataTable dtRowsMissing = SelectKeyRowsMissingFromDB(idImport);
LogWriteKeyrowsMissingToLogTable(idImport, strError, fileName, dtRowsMissing);
}
else
{
LogProcessErrorToDataBase(idImport, strError, fileName);
}
}
}
catch (Exception ex)
{
LogProcessErrorToDataBase(idImport, ex.Message, fileName);
}
/////////////////////////////
try
{
DataSet dsNonExistingAssociates = imp.SelectNonExistingAssociateNumbers(idImport);
if (result >= 0)
{
fileNameNonExistingAssociates = GetServerPath(TargetDirectoryEnum.DIRECTORY_PROCESSED) + @"\" + ApplicationUtils.GetCleanFileName(Path.GetFileNameWithoutExtension(fileName)) + "_MissingAssociates.txt";
}
else
{
fileNameNonExistingAssociates = GetServerPath(TargetDirectoryEnum.DIRECTORY_CANCELLED) + @"\" + ApplicationUtils.GetCleanFileName(Path.GetFileNameWithoutExtension(fileName)) + "_MissingAssociates.txt";
}
NonExistingAssociates = ApplicationUtils.CreateAssociatesTextFile(dsNonExistingAssociates, fileNameNonExistingAssociates);
}
catch (Exception ex)
{
throw new IndException(ex);
}
string msg = string.Empty;
if (result >= 0)
{
msg = string.Format(ApplicationMessages.IMPORT_FILE_PROCESSED, cleanFileName);
if (NonExistingAssociates >= 0)
{
fileNameNonExistingAssociates = ConfigurationManager.AppSettings["UploadFolderProcessed"] + @"\" + ApplicationUtils.GetCleanFileName(Path.GetFileNameWithoutExtension(fileName)) + "_MissingAssociates.txt";
msg += string.Format(ApplicationMessages.UPLOAD_MISSING_ASSOCIATES + ApplicationMessages.UPLOAD_ASSOCIATES_INSERTED, NonExistingAssociates + 1, fileNameNonExistingAssociates);
}
processedMessages.Add(msg);
return(true);
}
else
{
msg = string.Format(ApplicationMessages.IMPORT_FILE_ERROR, cleanFileName);
if (NonExistingAssociates >= 0)
{
fileNameNonExistingAssociates = ConfigurationManager.AppSettings["UploadFolderCancelled"] + @"\" + ApplicationUtils.GetCleanFileName(Path.GetFileNameWithoutExtension(fileName)) + "_MissingAssociates.txt";
msg += string.Format(ApplicationMessages.UPLOAD_MISSING_ASSOCIATES, NonExistingAssociates + 1, fileNameNonExistingAssociates);
}
processedMessages.Add(msg);
return(false);
}
}
private void ConnectionAction(Object obj)
{
ConnectionSettingsContainer settings = obj as ConnectionSettingsContainer;
if (settings != null)
{
AuthenticationType authentication = AuthenticationType.Windows;
if (settings.IsSimple)
{
authentication = AuthenticationType.SqlServer;
}
OnConnecting();
string message;
//if (settings.ServerName.Split('@').Length >= 2)
//{
// string serverName = settings.ServerName.Split('@')[0];
// string baseName = settings.ServerName.Split('@')[1];
try
{
//GlobalObjects.CasEnvironment.Connect(serverName, settings.Username, settings.Password, baseName);
GlobalObjects.CasEnvironment.Connect(settings.ServerName, settings.Username, settings.Password, "");
SaveJsonSetting(settings.Username);
}
catch (ConnectionFailureException ex)
{
if (ex.InnerException != null)
{
SqlException sqlException = ex.InnerException as SqlException;
if (sqlException != null)
{
MessageBox.Show(sqlException.Message, "Failed to connect server", MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
else
{
MessageBox.Show("Server was not found or is not available", "Failed to connect server",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Server was not found or is not available", "Failed to connect server",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Failed to connect server", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//нажатие клавиши Enter
buttonExit.Enabled = false;
_loadForm.Show();
backgroundWorker.RunWorkerAsync();
}
else
{
OnFailed("Wrong connection parameters");
//Invoke(new failureEventInvoker(OnFailed), new object[] { "Wrong connection parameters" });
}
}
private static bool IsInvalidColumnLength(SqlException ex)
{
return(ex.Message.Contains("Received an invalid column length from the bcp client for colid") ||
ex.Message.Contains(
"Получена недопустимая длина столбца от клиента bcp для идентификатора столбца"));
}
/// <summary>
/// This method executes when the user clicks the delete button for a UserFileUpload
/// and it deletes the UserFileUpload information from the database
/// </summary>
/// <param name="sender">The lbDeleteUserFileUpload LinkButton</param>
/// <param name="e">The Click event</param>
protected void lbDeleteUserFileUpload_Click(object sender, EventArgs e)
{
if (currentProgramRole.AllowedToEdit.Value ||
currentProgramRole.RoleFK.Value == (int)Utilities.ProgramRoleFKs.HUB_DATA_VIEWER)
{
//Get the PK from the hidden field
int?removeUserFileUploadPK = (String.IsNullOrWhiteSpace(hfDeleteUserFileUploadPK.Value) ? (int?)null : Convert.ToInt32(hfDeleteUserFileUploadPK.Value));
//Remove the role if the PK is not null
if (removeUserFileUploadPK != null)
{
try
{
using (PyramidContext context = new PyramidContext())
{
//Get the UserFileUpload program row to remove
Models.UserFileUpload fileToRemove = context.UserFileUpload
.Include(ufu => ufu.CodeFileUploadType)
.Where(ufu => ufu.UserFileUploadPK == removeUserFileUploadPK)
.FirstOrDefault();
//Ensure the user is allowed to delete this file
if (!fileToRemove.CodeFileUploadType.RolesAuthorizedToModify.Contains((currentProgramRole.RoleFK.Value.ToString() + ",")) ||
(fileToRemove.TypeCodeFK == (int)Utilities.FileTypeFKs.PROGRAM_WIDE &&
currentProgramRole.RoleFK.Value == (int)Utilities.ProgramRoleFKs.HUB_DATA_VIEWER &&
fileToRemove.Creator != User.Identity.Name))
{
msgSys.ShowMessageToUser("danger", "Delete Failed", "You are not authorized to delete this file!", 10000);
}
else
{
//Remove the file from Azure storage
Utilities.DeleteFileFromAzureStorage(fileToRemove.FileName,
Utilities.ConstantAzureStorageContainerName.UPLOADED_FILES.ToString());
//Remove the UserFileUpload
context.UserFileUpload.Remove(fileToRemove);
context.SaveChanges();
//Show a success message
msgSys.ShowMessageToUser("success", "Delete Succeeded", "Successfully deleted file!", 10000);
}
}
}
catch (DbUpdateException dbUpdateEx)
{
//Check if it is a foreign key error
if (dbUpdateEx.InnerException?.InnerException is SqlException)
{
//If it is a foreign key error, display a custom message
SqlException sqlEx = (SqlException)dbUpdateEx.InnerException.InnerException;
if (sqlEx.Number == 547)
{
msgSys.ShowMessageToUser("danger", "Error", "Could not delete the file, there are related records in the system!<br/><br/>If you do not know what related records exist, please contact tech support via ticket.", 120000);
}
else
{
msgSys.ShowMessageToUser("danger", "Error", "An error occurred while deleting the file!", 120000);
}
}
else
{
msgSys.ShowMessageToUser("danger", "Error", "An error occurred while deleting the file!", 120000);
}
//Log the error
Elmah.ErrorSignal.FromCurrentContext().Raise(dbUpdateEx);
}
//Rebind the UserFileUpload controls
bsGRUserFileUploads.DataBind();
}
else
{
msgSys.ShowMessageToUser("danger", "Error", "Could not find the file to delete!", 120000);
}
}
else
{
msgSys.ShowMessageToUser("danger", "Error", "You are not authorized to make changes!", 120000);
}
}
internal SqlInfoMessageEventArgs(SqlException exception)
{
this.exception = exception;
}
private static bool CheckThatExceptionsAreDistinctButHaveSameData(SqlException e1, SqlException e2)
{
Assert.True(e1 != e2, "FAILED: verification of exception cloning in subsequent connection attempts");
Assert.False((e1 == null) || (e2 == null), "FAILED: One of exceptions is null, another is not");
bool equal = (e1.Message == e2.Message) && (e1.HelpLink == e2.HelpLink) && (e1.InnerException == e2.InnerException) &&
(e1.Source == e2.Source) && (e1.Data.Count == e2.Data.Count) && (e1.Errors == e2.Errors);
IDictionaryEnumerator enum1 = e1.Data.GetEnumerator();
IDictionaryEnumerator enum2 = e2.Data.GetEnumerator();
while (equal)
{
if (!enum1.MoveNext())
{
break;
}
enum2.MoveNext();
equal = (enum1.Key == enum2.Key) && (enum2.Value == enum2.Value);
}
Assert.True(equal, string.Format("FAILED: exceptions do not contain the same data (besides call stack):\nFirst: {0}\nSecond: {1}\n", e1, e2));
return(true);
}
protected virtual bool ShouldIgnoreSqlException(SqlException sqlException)
{
return(false);
}
private static bool VerifyException(SqlException exception)
{
VerifyException(exception, 1);
return(true);
}
private static bool IsDeadlockException(SqlException sqlException)
{
return(sqlException != null && sqlException.Number == DeadlockSqlExceptionErrorCode);
}
// Login failed is thrown when database does not exist (See Issue #776) // Unable to attach database file is thrown when file does not exist (See Issue #2810) // Unable to open the physical file is thrown when file does not exist (See Issue #2810) private static bool IsDoesNotExist(SqlException exception) => exception.Number == 4060 || exception.Number == 1832 || exception.Number == 5120;
public SqlException Create(Staff staff)
{
//insert
string insert = "INSERT INTO Staff (StaffNumber, FirstName, LastName, FullAddress, DateOfBirth, Gender, InsuranceNumber, CurrentPosition, CurrentSalary, SalaryScale, NumberOfHoursPerWeek, PermenantOrTemporary, SalaryPayment, AppointmentNumber, WorkExperienceID, QualificationID)" +
" VALUES(@StaffNumber, @FirstName, @LastName, @FullAddress, @DateOfBirth, @Gender, @InsuranceNumber, @CurrentPosition, @CurrentSalary, @SalaryScale, @NumberOfHoursPerWeek, @PermenantOrTemporary, @SalaryPayment, @AppointmentNumber, @WorkExperienceID, @QualificationID); ";
//create sql command, insert query, preparing quries, create parameterized quieries,
// 1. declare command object with parameter + insert query
SqlCommand cmd = InitializeDatabase.CreateSqlCommand(insert);
// 2. define parameters used in command object
List <SqlParameter> prm = new List <SqlParameter>()
{
new SqlParameter("@StaffNumber", SqlDbType.Int)
{
Value = staff.StaffNumber
},
new SqlParameter("@FirstName", SqlDbType.NVarChar)
{
Value = staff.FirstName
},
new SqlParameter("@LastName", SqlDbType.NVarChar)
{
Value = staff.LastName
},
new SqlParameter("@FullAddress", SqlDbType.NVarChar)
{
Value = staff.FullAddress
},
new SqlParameter("@DateOfBirth", SqlDbType.DateTime)
{
Value = staff.DateOfBirth
},
new SqlParameter("@Gender", SqlDbType.NVarChar)
{
Value = staff.Gender
},
new SqlParameter("@InsuranceNumber", SqlDbType.Int)
{
Value = staff.InsuranceNumber
},
new SqlParameter("@CurrentPosition", SqlDbType.NVarChar)
{
Value = staff.CurrentPosition
},
new SqlParameter("@CurrentSalary", SqlDbType.Float)
{
Value = staff.CurrentSalary
},
new SqlParameter("@SalaryScale", SqlDbType.NVarChar)
{
Value = staff.SalaryScale
},
new SqlParameter("@NumberOfHoursPerWeek", SqlDbType.Int)
{
Value = staff.NumberOfHoursPerWeek
},
new SqlParameter("@PermenantOrTemporary", SqlDbType.NVarChar)
{
Value = staff.PermenentOrTemporary
},
new SqlParameter("@SalaryPayment", SqlDbType.NVarChar)
{
Value = staff.SalaryPayment
},
new SqlParameter("@AppointmentNumber", SqlDbType.Int)
{
Value = staff.AppointmentNumber
},
new SqlParameter("@WorkExperienceID", SqlDbType.Int)
{
Value = staff.WorkExperienceID
},
new SqlParameter("@QualificationID", SqlDbType.Int)
{
Value = staff.QualificationID
},
};
// 3. add new parameter to command object
cmd.Parameters.AddRange(prm.ToArray());
//run
SqlException res = InitializeDatabase.RunSqlCommand(cmd);
return(res);
}
public async Task InsertAsync(IEnumerable <TableRow> rows)
{
List <TableRow> rowsList = rows.ToList();
using (var sbc = new SqlBulkCopy(_connection))
{
sbc.DestinationTableName = _tableName;
sbc.BulkCopyTimeout = (int)_configuration.BulkCopyTimeout.TotalSeconds;
var dataTable = new DataTable(_tableName);
AddColumns(dataTable, rowsList);
//copy to rows
foreach (TableRow row in rows)
{
DataRow dataRow = dataTable.NewRow();
dataRow[_configuration.PartitionKeyColumnName] = row.PartitionKey;
dataRow[_configuration.RowKeyColumnName] = row.RowKey;
foreach (string key in row.Keys.OrderBy(kv => kv))
{
DynamicValue value;
row.TryGetValue("key", out value);
dataRow[key] = value;
}
dataTable.Rows.Add(dataRow);
}
//execute bulk copy
await CheckConnection();
try
{
await sbc.WriteToServerAsync(dataTable);
}
catch (InvalidOperationException ex) when(ExceptonTranslator.GetSqlException(ex) != null)
{
SqlException sqlEx = ExceptonTranslator.GetSqlException(ex);
if (sqlEx.Number == SqlCodes.InvalidObjectName)
{
await CreateTableAsync(rowsList);
//run it again
try
{
await sbc.WriteToServerAsync(dataTable);
}
catch (Exception ex1)
{
throw;
}
}
}
catch (SqlException ex) when(ex.Number == SqlCodes.DuplicateKey)
{
throw new StorageException(ErrorCode.DuplicateKey, ex);
}
}
}
internal static bool IsUniqueConstraintViolationOnIndex(this SqlException exception, string indexName)
{
return(IsUniqueConstraintViolation(exception) && exception.Message.Contains($"'{indexName}'"));
}
public static string GuardaSolicitud(Pet.Entity.Solicitud solicitud)
{
try
{
using (var db = new EFData.PET_DBEntities())
{
if (solicitud.CodigoSolicitud == 0)
{
//SR000001
//var nroSolicitud = db.GPC_Solicitud
// .Select(t => t.numero_solicitud)
// .DefaultIfEmpty(-1)
// .Max();
//if (nroSolicitud == -1)
//{
// nroSolicitud = 1;
//}
//else
//{
// nroSolicitud += 1;
//}
//string nroSol = string.Format("{0:000000}", nroSolicitud);
//string codigoGenerado = "SR" + nroSol;
//solicitud.codigo_solicitud = codigoGenerado;
//solicitud.numero_solicitud = nroSolicitud;
var _nroSolicitud = new SqlParameter("numero_solicitud", SqlDbType.Int)
{
Direction = System.Data.ParameterDirection.Output
};
//SqlParameter _fecha_hora = new SqlParameter()
//{
// ParameterName = "fecha_hora",
// DbType = DbType.DateTime,
// Value = solicitud.fecha_hora
//};
//SqlParameter _codigo_estado = new SqlParameter()
//{
// ParameterName = "codigo_estado",
// DbType = DbType.Int32,
// Value = solicitud.codigo_estado
//};
var _fecha_hora = new SqlParameter("fecha_hora", solicitud.FechaHoraCreacion);
SqlParameter _codigo_empleado = new SqlParameter("codigo_empleado", solicitud.CodigoEmpleado1);
SqlParameter _codigo_prioridad = new SqlParameter("codigo_prioridad", solicitud.CodigoEmpleado1);
SqlParameter _codigo_estado = new SqlParameter("codigo_estado", solicitud.CodigoEmpleado1);
db.Database.ExecuteSqlCommand("RegistraSolicitud @fecha_hora, @codigo_empleado, @codigo_prioridad, @codigo_estado, @numero_solicitud out",
_fecha_hora,
_codigo_empleado,
_codigo_prioridad,
_codigo_estado,
_nroSolicitud
);
solicitud.CodigoSolicitud = (int)_nroSolicitud.Value;
AddDetalleSolicitud(solicitud);
//db.Set<Pet.Entity.GPCSolicitud>().Add(solicitud);
//db.SaveChanges();
}
else
{
EliminaDetalleSolicitud(solicitud.CodigoSolicitud);
db.Database.ExecuteSqlCommand("ActualizaSolicitud @numero_solicitud, @fecha_hora, @codigo_empleado, @codigo_prioridad, @codigo_estado",
new SqlParameter("numero_solicitud", solicitud.CodigoSolicitud),
new SqlParameter("fecha_hora", solicitud.CodigoSolicitud),
new SqlParameter("codigo_empleado", solicitud.CodigoSolicitud),
new SqlParameter("codigo_prioridad", solicitud.CodigoSolicitud),
new SqlParameter("codigo_estado", solicitud.CodigoSolicitud)
);
AddDetalleSolicitud(solicitud);
}
}
}
catch (DbUpdateException ex)
{
UpdateException updateException = (UpdateException)ex.InnerException;
SqlException sqlException = (SqlException)updateException.InnerException;
StringBuilder dd = new StringBuilder();
foreach (SqlError error in sqlException.Errors)
{
dd.Append(error);
}
string ddd = dd.ToString();
throw;
}
return("OK");
}
private bool IsIdentityInsertException(SqlException ex)
{
return(ex.Message.Contains("Cannot insert explicit value for identity column in table"));
}
/// <summary>
/// Prevents a default instance of the <see cref="SqlConversionException"/> class from being created.
/// </summary>
/// <param name="exception">The exception.</param>
private SqlConversionException([NotNull] SqlException exception)
: base(exception)
{
}
protected void chartDefault(int tipo, string xType, string xFilter, int gType)
{
try
{
WebChartControl1.Series["Total"].Points.Clear();
WebChartControl1.Series["Goal"].Points.Clear();
WebChartControl1.Series["Total"].LegendTextPattern = "";
WebChartControl1.Series["Goal"].LegendTextPattern = "";
string xTipo = "WEEKLY";
if (tipo < 2)
{
xTipo = "WEEKLY";
}
if (tipo == 2)
{
xTipo = "MONTHLY";
}
if (tipo == 3)
{
xTipo = "QUARTERLY";
}
if (tipo == 4)
{
xTipo = "YEARLY";
}
if (gType < 2)
{
WebChartControl1.Height = 200;
string query1 = "select top 13 * from [sta_nivel2] where smetric = 'vmi' and sfilter = '" + xType + "' and sclass = '" + xFilter + "' and stype = '" + xTipo + "' order by id desc";
string qry1 = "select * from (" + query1 + ") q1 order by id";
SQLHelper.DBHelper dBHelper = new SQLHelper.DBHelper();
DataTable dt1 = dBHelper.QryManager(qry1);
foreach (DataRow dr1 in dt1.Rows)
{
double xTotal = Convert.ToDouble(dr1["factual"].ToString());
double xGoal = Convert.ToDouble(dr1["fgoal"].ToString());
WebChartControl1.Series["Total"].Points.AddPoint(dr1["sdesc"].ToString(), Math.Round((xTotal / 1000000), 2));
WebChartControl1.Series["Goal"].Points.AddPoint(dr1["sdesc"].ToString(), Math.Round((xGoal / 1000000), 2));
WebChartControl1.Series["Total"].Label.TextPattern = "{V: c2}M";
WebChartControl1.Series["Goal"].Label.TextPattern = "{V: c2}M";
WebChartControl1.Series["Total"].Label.ResolveOverlappingMode = DevExpress.XtraCharts.ResolveOverlappingMode.Default;
WebChartControl1.Series["Goal"].Label.ResolveOverlappingMode = DevExpress.XtraCharts.ResolveOverlappingMode.Default;
}
}
if (gType == 2)
{
WebChartControl1.Height = 400;
double vSum = 0;
string qry2 = "SELECT top 10 smaterial, SUM(total) as cValue FROM [DB_1033_Dashboard].[dbo].[tbl_vmi] where sweek = (select top 1 sweek FROM [DB_1033_Dashboard].[dbo].[tbl_vmi] order by sweek desc) group by smaterial order by cValue desc";
SQLHelper.DBHelper dBHelper2 = new SQLHelper.DBHelper();
DataTable dt2 = dBHelper2.QryManager(qry2);
foreach (DataRow dr2 in dt2.Rows)
{
double xActual = Convert.ToDouble(dr2["cValue"].ToString());
vSum = vSum + xActual;
WebChartControl1.Series["Total"].Points.AddPoint(dr2["smaterial"].ToString(), Math.Round((xActual / 1000), 2));
WebChartControl1.Series["Goal"].Points.AddPoint(dr2["smaterial"].ToString(), Math.Round((vSum / 1000), 2));
WebChartControl1.Series["Total"].Label.TextPattern = "{V: c2}K";
WebChartControl1.Series["Goal"].Label.TextPattern = "{V: c2}K";
WebChartControl1.Series["Total"].Label.ResolveOverlappingMode = DevExpress.XtraCharts.ResolveOverlappingMode.Default;
WebChartControl1.Series["Goal"].Label.ResolveOverlappingMode = DevExpress.XtraCharts.ResolveOverlappingMode.Default;
WebChartControl1.Series["Total"].LegendTextPattern = "Total";
WebChartControl1.Series["Goal"].LegendTextPattern = "Accum";
}
}
if (gType == 3)
{
WebChartControl1.Height = 200;
string query2 = "select top 6 * from [sta_nivel2f] where smetric = 'vmi' order by id desc";
string qry2 = "select * from (" + query2 + ") q1 order by id";
SQLHelper.DBHelper dBHelper2 = new SQLHelper.DBHelper();
DataTable dt2 = dBHelper2.QryManager(qry2);
foreach (DataRow dr2 in dt2.Rows)
{
double xActual = Convert.ToDouble(dr2["factual"].ToString());
double xGoal = Convert.ToDouble(dr2["fsum"].ToString());
WebChartControl1.Series["Total"].Points.AddPoint(dr2["scause"].ToString(), Math.Round((xActual / 1000000), 2));
WebChartControl1.Series["Goal"].Points.AddPoint(dr2["scause"].ToString(), Math.Round((xGoal / 1000000), 2));
WebChartControl1.Series["Total"].Label.TextPattern = "{V: c2}M";
WebChartControl1.Series["Goal"].Label.TextPattern = "{V: c2}M";
WebChartControl1.Series["Total"].Label.ResolveOverlappingMode = DevExpress.XtraCharts.ResolveOverlappingMode.Default;
WebChartControl1.Series["Goal"].Label.ResolveOverlappingMode = DevExpress.XtraCharts.ResolveOverlappingMode.Default;
}
}
}
catch (Exception ex)
{
int errNum = -99999999;
string errDesc = "";
//HttpContext.Current.Items.Add("Exception", ex);
HttpContext.Current.Session.Add("Exception", ex);
if (ex is SqlException)
{
// Handle more specific SqlException exception here.
SqlException odbcExc = (SqlException)ex;
errNum = odbcExc.Number;
errDesc = odbcExc.Message;
}
else
{
// Handle generic ones here.
errDesc = ex.Message;
}
Response.Redirect("~\\CustomErrors\\Errors.aspx?handler=vmi.aspx&msg=" + errNum + "&errDesc=" + errDesc);
}
}
public static bool IsUniqueKeyViolation(SqlException ex)
{
return(ex.Errors.Cast <SqlError>().Any(e => e.Class == 14 && (e.Number == 2601 || e.Number == 2627)));
}
private void tsbDBPost_Click(object sender, EventArgs e)
{
int NewRowIndex = -1;
try
{
if (IsValidData() == true)
{
switch (__Table.TableStatus)
{
case TableStatus.tsInsert:
{
DataRow drDataRow = dtDataTable.NewRow();
drDataRow["PK_ProductCode"] = ntbPK_ProductCode.Text.Trim();
drDataRow["Product"] = tbProduct.Text.Trim();
drDataRow["FK_ProductTypeCode"] = ntbFK_ProductTypeCode.Text.Trim();
drDataRow["FK_FabricCode"] = ntbFK_FabricCode.Text.Trim();
drDataRow["ProductType"] = cbFK_ProductTypeCode.Text.Trim();
drDataRow["Fabric"] = cbFK_FabricCode.Text.Trim();
dtDataTable.Rows.Add(drDataRow);
NewRowIndex = dtDataTable.Rows.IndexOf(drDataRow);
break;
}
case TableStatus.tsEdit:
{
dtDataTable.Rows[cmCurrencyManager.Position]["PK_ProductCode"] = ntbPK_ProductCode.Text.Trim();
dtDataTable.Rows[cmCurrencyManager.Position]["Product"] = tbProduct.Text.Trim();
dtDataTable.Rows[cmCurrencyManager.Position]["FK_ProductTypeCode"] = ntbFK_ProductTypeCode.Text.Trim();
dtDataTable.Rows[cmCurrencyManager.Position]["FK_FabricCode"] = ntbFK_FabricCode.Text.Trim();
dtDataTable.Rows[cmCurrencyManager.Position]["ProductType"] = cbFK_ProductTypeCode.Text.Trim();
dtDataTable.Rows[cmCurrencyManager.Position]["Fabric"] = cbFK_FabricCode.Text.Trim();
break;
}
}
SqlException eSqlException = pBFL.SetDataTable(dtDataTable, __Table.TableName);
if (eSqlException == null)
{
dtDataTable.AcceptChanges();
if (NewRowIndex >= 0)
{
cmCurrencyManager.Position = NewRowIndex;
}
if (__Table.TableStatus == TableStatus.tsInsert)
{
pBFL.Logging(User.UserCode, __Table.PK_TableCode, Macro.Action.aInsert, User.IP);
pCCL.SetGroupBox(gbDetails, true, true);
pCCL.Control_KeyPress(gbDetails, 0);
}
else
{
pBFL.Logging(User.UserCode, __Table.PK_TableCode, Macro.Action.aUpdate, User.IP);
}
}
else
{
dtDataTable.RejectChanges();
System.Windows.Forms.MessageBox.Show(eSqlException.Message);
}
}
}
catch (Exception eException)
{
System.Windows.Forms.MessageBox.Show(eException.Message);
}
}
public bool IsTransientException(SqlException exception)
{
return(!Enum.IsDefined(typeof(NonTransientSqlException), exception.Number));
}
internal SqlInfoMessageEventArgs(SqlException exception)
{
_exception = exception;
}
/// <summary>
/// Constructor de la clase
/// </summary>
/// <param name="mensaje">Mensaje de la excepcion</param>
/// <param name="inner">Excepcion SqlException</param>
public ReservaExceptionM02(string mensaje, SqlException inner)
{
_Mensaje = "Reserva-404: Ha ocurrido un problema con la base de datos. Para mayor detalle revisar el Log de errores";
_Excepcion = inner;
}
