public void SetUp()
{
var openTabJson = GetOpenTabJson();
_sqlExecutor.ExecuteNonQuery($@"DELETE FROM dbo.OpenTabs WHERE Id = '{_id}'");
_sqlExecutor.ExecuteNonQuery($@"INSERT INTO dbo.OpenTabs(Id,Data) VALUES ('{_id}','{openTabJson}')");
_openTabsRepository = new OpenTabsRepository(ConnectionStringProvider.ConnectionString);
}
public void SetUp()
{
_tabDetails = GetTabDetails();
var tabDetailsJson = JsonConvert.SerializeObject(_tabDetails);
_sqlExecutor.ExecuteNonQuery($@"DELETE FROM dbo.TabDetails WHERE Id = '{_id}'");
_sqlExecutor.ExecuteNonQuery($@"INSERT INTO dbo.TabDetails(Id,Data) VALUES ('{_id}','{tabDetailsJson}')");
_tabDetailsRepository = new TabDetailsRepository(ConnectionStringProvider.ConnectionString);
}
public void SetUp()
{
_menu = GetMenu();
var menuJson = JsonConvert.SerializeObject(_menu);
_sqlExecutor.ExecuteNonQuery($@"DELETE FROM dbo.Menu WHERE Id = '{_id}'");
_sqlExecutor.ExecuteNonQuery($@"INSERT INTO dbo.Menu(Id,Data) VALUES ('{_id}','{menuJson}')");
_menuConfiguration = Substitute.For <IMenuConfiguration>();
_menuConfiguration.Id.Returns(_id);
_menuRepository = new MenuRepository(_menuConfiguration, ConnectionStringProvider.ConnectionString);
}
public static string ClearCache(string plan)
{
//DBCC FREEPROCCACHE(0x060005000D25462980D64D4B3400000001000000000000000000000000000000000000000000000000000000)
if (TSAuthentication.OrganizationID != 1078 || !TSAuthentication.IsSystemAdmin)
{
return("");
}
if (string.IsNullOrWhiteSpace(plan))
{
return("");
}
try
{
SqlCommand command = new SqlCommand();
command.CommandText = "DBCC FREEPROCCACHE(0x" + plan + ")";
SqlExecutor.ExecuteNonQuery(GetLoginUser(), command);
return("Execution plan cleared.");
}
catch (Exception ex)
{
return(ex.Message);
}
}
private void UnlockIndex(int organizationID)
{
SqlCommand command = new SqlCommand();
command.CommandText = "UPDATE Organizations SET IsIndexLocked = 0 WHERE OrganizationID = @OrganizationID";
command.Parameters.AddWithValue("OrganizationID", organizationID);
int attempts = 0;
while (attempts < 3)
{
try
{
SqlExecutor.ExecuteNonQuery(LoginUser, command);
LogVerbose("Unlocked index for " + organizationID.ToString());
break;
}
catch (Exception e)
{
attempts++;
if (attempts == 3)
{
throw;
}
}
}
}
private void DeleteTable(string name)
{
if (MessageBox.Show("Удалить таблицу?", "Удаление таблицы",
MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.No)
{
return;
}
try
{
SqlExecutor.ExecuteNonQuery(dbConnection, $"DROP TABLE {name}");
Table.Remove(metaDbContainer, name);
var parentNode = MainTreeView.Nodes.Cast <TreeNode>().First(x => x.Tag.Equals("Tables"));
parentNode.Nodes.Remove(parentNode.Nodes.Cast <TreeNode>().First(x => x.Tag.Equals($"[tabl]{name}")));
}
catch (SqlException e)
{
if (e.Message.Contains("because it is referenced by a FOREIGN KEY constraint"))
{
MessageBox.Show("Не могу удалить таблицу, т.к. от неё зависят другие таблицы");
}
else
{
throw e;
}
}
}
public static void DeleteTabsFor(string waiter)
{
var sqlExecutor = new SqlExecutor();
var commandText = $"DELETE FROM dbo.OpenTabs WHERE [Data] LIKE '%\"Waiter\":\"{waiter}\"%'";
sqlExecutor.ExecuteNonQuery(commandText);
}
protected virtual void ExecuteStatementsWithoutTransaction(
[NotNull] IEnumerable <SqlBatch> sqlStatements, [NotNull] RelationalConnection connection)
{
Check.NotNull(sqlStatements, "sqlStatements");
Check.NotNull(connection, "connection");
SqlExecutor.ExecuteNonQuery(connection, null, sqlStatements);
}
private void ExecuteRollback(string commandText, LoginUser loginUser, int orgID)
{
SqlCommand rollbackCommand = new SqlCommand(commandText);
rollbackCommand.CommandTimeout = 5000;
rollbackCommand.Parameters.AddWithValue("importID", _importID);
rollbackCommand.Parameters.AddWithValue("orgID", orgID);
SqlExecutor.ExecuteNonQuery(loginUser, rollbackCommand);
}
public void SetUp()
{
var connectionString = ConnectionStringProvider.ConnectionString;
var sqlExecutor = new SqlExecutor();
sqlExecutor.ExecuteNonQuery($"DELETE FROM dbo.Events WHERE AggregateId = '{_aggregateId}'"); // Do clean-up at start of tests instead of end, so that if a test fails, we can investigate with data still present.
_repository = CreateRepository();
_repository.UnitOfWork = new EventStoreUnitOfWork(connectionString);
}
private void UnmarkIndexRebuild(int organizationID)
{
SqlCommand command = new SqlCommand();
command.CommandText = "UPDATE Organizations SET IsRebuildingIndex = 0 WHERE OrganizationID = @OrganizationID";
command.Parameters.AddWithValue("OrganizationID", organizationID);
SqlExecutor.ExecuteNonQuery(LoginUser, command);
LogVerbose("Unmarked index rebuild for " + organizationID.ToString());
}
public override void ReleaseAllLocks()
{
if (GetIsRebuilderMode())
{
SqlExecutor.ExecuteNonQuery(LoginUser, "UPDATE Organizations SET IsRebuildingIndex = 0 WHERE IsRebuildingIndex=1");
}
else
{
SqlExecutor.ExecuteNonQuery(LoginUser, "UPDATE Organizations SET IsIndexLocked = 0 WHERE IsIndexLocked=1");
}
}
public void ClearExceptionLogs(bool onlyCrmApi)
{
if (onlyCrmApi)
{
SqlExecutor.ExecuteNonQuery(TSAuthentication.GetLoginUser(), "DELETE FROM ExceptionLogs WHERE URL = 'CRM Processor' OR URL = 'API'");
}
else
{
SqlExecutor.ExecuteNonQuery(TSAuthentication.GetLoginUser(), "TRUNCATE TABLE ExceptionLogs");
}
}
private static void RunScriptsInForDatabase(Server server, DbParams dbParams, IEnumerable<string> sqlFiles)
{
var executor = new SqlExecutor(server);
var sqlFilesSortedAlphabetically = Order(sqlFiles);
foreach(var sqlFile in sqlFilesSortedAlphabetically) {
Console.WriteLine("Going to run " + sqlFile);
executor.ExecuteNonQuery(File.ReadAllText(sqlFile), dbParams.DbName);
}
}
private void button1_Click(object sender, EventArgs e)
{
if (nameTextBox.Text.Length == 0)
{
MessageBox.Show("Укажите имя атрибута");
return;
}
if (typeComboBox.SelectedIndex < 0)
{
MessageBox.Show("Укажите выберите тип атрибута");
return;
}
int sqlResCode = SqlExecutor.ExecuteNonQuery(dbConnection,
$"ALTER TABLE {currentTable.RealName} ADD col{currentTable.Attributes.Count} {MapType(typeComboBox.Text)} {((!isNullableCheckBox.Checked) ? "NOT" : "")} NULL");
bool itIsFK = typeComboBox.Text.StartsWith("Ссылка на");
if (sqlResCode > -1 && itIsFK)
{
string realName = tableRealName2Name[typeComboBox.Text];
string pkName = metaDbContainer.TableSet.Where(t => t.RealName == realName).First().Attributes
.Where(a => a.IsPKey).First().RealName;
sqlResCode = SqlExecutor.ExecuteNonQuery(dbConnection,
$"ALTER TABLE {currentTable.RealName} ADD CONSTRAINT FK_{currentTable.RealName}_col{currentTable.Attributes.Count} FOREIGN KEY (col{currentTable.Attributes.Count}) REFERENCES {realName}({pkName})");
}
if (sqlResCode < 0)
{
return;
}
currentTable.Attributes.Add(new Model.Attribute(
name: nameTextBox.Text,
realname: $"col{currentTable.Attributes.Count}",
type: MapAttrType(typeComboBox.Text),
isIndexed: (itIsFK) ? true : isIndexedCheckBox.Checked,
isNullable: isNullableCheckBox.Checked,
isPKey: false
));
metaDbContainer.SaveChanges();
if (itIsFK)
{
var prn = tableRealName2Name[typeComboBox.Text];
var parent = metaDbContainer.TableSet.First(t => t.RealName == prn);
currentTable.ParentTables.Add(parent);
parent.ChildTables.Add(currentTable);
}
MessageBox.Show("Атрибут успешно добавлен");
this.DialogResult = DialogResult.OK;
this.Close();
}
public void SetUp()
{
var connectionString = ConnectionStringProvider.ConnectionString;
_sqlExecutor = new SqlExecutor();
_sqlExecutor.ExecuteNonQuery($"DELETE FROM dbo.Events WHERE ID IN ('{Id1}','{Id2}')");
_sqlExecutor.ExecuteNonQuery($"DELETE FROM dbo.EventsToPublish WHERE ID IN ('{Id1}','{Id2}')");
var eventStoreUnitOfWork = new EventStoreUnitOfWork(connectionString);
_eventToPublishRepositoryDecorator = CreateEventToPublishRepositoryThatCanSimulateSqlExceptions(
new EventToPublishRepository(new EventToPublishSerializer(Assembly.GetExecutingAssembly())
)
);
_eventToPublishRepositoryDecorator.UnitOfWork = eventStoreUnitOfWork;
_eventStore = new EventRepository(new EventSerializer(Assembly.GetExecutingAssembly()))
{
UnitOfWork = eventStoreUnitOfWork
};
_eventHandler = new EventHandler(
new CompositeEventStore(
new[]
{
_eventStore,
_eventToPublishRepositoryDecorator
}
)
);
_testEvent = new TestEvent
{
Id = new Guid(Id1),
AggregateId = _aggregateId
};
_testEvent2 = new TestEvent2
{
Id = new Guid(Id2),
AggregateId = _aggregateId
};
}
private bool ObtainLock(int organizationID)
{
bool result = false;
lock (_staticLock)
{
SqlCommand command = new SqlCommand();
command.CommandText = "UPDATE Organizations SET IsIndexLocked = 1 WHERE IsIndexLocked = 0 AND OrganizationID = @OrganizationID";
command.Parameters.AddWithValue("OrganizationID", organizationID);
result = SqlExecutor.ExecuteNonQuery(LoginUser, command) > 0;
LogVerbose("Locked index for " + organizationID.ToString());
}
return(result);
}
private void UpdateScheduledReport(int id)
{
SqlCommand command = new SqlCommand();
command.CommandText = @"
UPDATE
ScheduledReports
SET
FilePathID = 3
WHERE
Id = @id
";
command.Parameters.AddWithValue("@id", id);
SqlExecutor.ExecuteNonQuery(_loginUser, command);
}
private void UpdateImport(int importID)
{
SqlCommand command = new SqlCommand();
command.CommandText = @"
UPDATE
Imports
SET
FilePathID = 3
WHERE
ImportID = @importID
";
command.Parameters.AddWithValue("@importID", importID);
SqlExecutor.ExecuteNonQuery(_loginUser, command);
}
private static TaskEmailPost GetNextTaskEmailPost(string connectionString, int lockID, bool isDebug)
{
TaskEmailPost result;
LoginUser loginUser = new LoginUser(connectionString, -1, -1, null);
lock (_staticLock)
{
if (isDebug)
{
SqlExecutor.ExecuteNonQuery(loginUser, "UPDATE TaskEmailPosts SET HoldTime=30 WHERE HoldTime > 30");
}
result = TaskEmailPosts.GetNextWaiting(loginUser, lockID.ToString());
}
return(result);
}
private void button1_Click(object sender, EventArgs e)
{
if (!(comboBox1.SelectedItem is Table t))
{
return;
}
var cols = "";
var values = "";
foreach (Control c in flowLayoutPanel1.Controls)
{
if (c is Label)
{
continue;
}
cols += $"{c.Name}, ";
if (c is DateTimePicker dtp)
{
values += $"'{dtp.Value.ToShortDateString()}', ";
}
else
{
values += $"'{c.Text}', ";
}
}
cols = cols.Trim(',', ' ');
values = values.Trim(',', ' ');
var sql = $"INSERT INTO {t.RealName} ({cols}) VALUES ({values})";
try
{
SqlExecutor.ExecuteNonQuery(_dbConnection, sql);
MessageBox.Show($"Значение добавлено в таблицу '{t.Name}'");
}
catch (Exception ex)
{
MessageBox.Show("Возникла ошибка при записи значения, возможно запрос некорректный", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
_log.Error(ex);
}
}
private void UpdateAttachment(int attachmentID, string pathWithoutSource)
{
SqlCommand command = new SqlCommand();
command.CommandText = @"
UPDATE
Attachments
SET
Path = @newPath,
FilePathID = 3
WHERE
AttachmentID = @attachmentID
";
command.Parameters.AddWithValue("@newPath", _destination + pathWithoutSource);
command.Parameters.AddWithValue("@attachmentID", attachmentID);
SqlExecutor.ExecuteNonQuery(_loginUser, command);
}
private void SaveOrgResults(int orgID, string result, string importID, bool hasExectued = true)
{
SqlCommand command = new SqlCommand();
command.CommandText = @"
UPDATE
OrgMoveEvent
SET
Result = @Result
, HasExecuted = @HasExecuted
, ImportID = @ImportID
WHERE
OrganizationID = @OrganizationID";
command.Parameters.AddWithValue("Result", result);
command.Parameters.AddWithValue("HasExecuted", hasExectued);
command.Parameters.AddWithValue("OrganizationID", orgID);
command.Parameters.AddWithValue("ImportID", importID);
SqlExecutor.ExecuteNonQuery(GetCorrupteLoginUser(), command);
}
private void AddFailedScheduledReportMove(int id, Exception ex)
{
SqlCommand command = new SqlCommand();
command.CommandText = @"
INSERT INTO
FailedToMoveScheduledReports
VALUES
(
@id,
@exceptionMessage,
@exceptionType
)
";
command.Parameters.AddWithValue("@id", id);
command.Parameters.AddWithValue("@exceptionMessage", ex.Message);
command.Parameters.AddWithValue("@exceptionType", ex.GetType().ToString());
SqlExecutor.ExecuteNonQuery(_loginUser, command);
}
private void AddFailedAttachmentMove(int attachmentID, Exception ex)
{
SqlCommand command = new SqlCommand();
command.CommandText = @"
INSERT INTO
FailedToMoveAttachments
VALUES
(
@attachmentID,
@exceptionMessage,
@exceptionType
)
";
command.Parameters.AddWithValue("@attachmentID", attachmentID);
command.Parameters.AddWithValue("@exceptionMessage", ex.Message);
command.Parameters.AddWithValue("@exceptionType", ex.GetType().ToString());
SqlExecutor.ExecuteNonQuery(_loginUser, command);
}
private static Organization GetNextOrganization(string connectionString, int lockID, bool isRebuilder, int?daysSinceLastRebuild = null, int?minutesSinceLastActive = null)
{
Organization result = null;
lock (_staticLock)
{
LoginUser loginUser = new LoginUser(connectionString, -1, -1, null);
Organizations orgs = new Organizations(loginUser);
if (!isRebuilder)
{
orgs.LoadByNeedsIndexing();
result = orgs.IsEmpty ? null : orgs[0];
if (result != null)
{
SqlCommand command = new SqlCommand();
command.CommandText = "UPDATE Organizations SET IsIndexLocked = 1, DateLastIndexed = GETUTCDATE() WHERE OrganizationID = @OrganizationID";
command.Parameters.AddWithValue("OrganizationID", result.OrganizationID);
SqlExecutor.ExecuteNonQuery(loginUser, command);
}
}
else
{
int days = daysSinceLastRebuild ?? 14;
if (DateTime.Now.Hour > 6)
{
days = 100;
}
orgs.LoadByNeedsIndexRebuilt(minutesSinceLastActive ?? 30, days);
result = orgs.IsEmpty ? null : orgs[0];
if (result != null)
{
SqlCommand command = new SqlCommand();
command.CommandText = "UPDATE Organizations SET IsRebuildingIndex = 1 WHERE OrganizationID = @OrganizationID";
command.Parameters.AddWithValue("OrganizationID", result.OrganizationID);
SqlExecutor.ExecuteNonQuery(loginUser, command);
}
}
}
return(result);
}
private void UpdateItems(IndexDataSource dataSource, string tableName, string primaryKeyName)
{
UpdateHealth();
if (dataSource.UpdatedItems.Count < 1)
{
return;
}
string updateSql = "UPDATE " + tableName + " SET NeedsIndexing = 0 WHERE " + primaryKeyName + " IN (" + DataUtils.IntArrayToCommaString(dataSource.UpdatedItems.ToArray()) + ")";
LogVerbose(updateSql);
SqlCommand command = new SqlCommand();
command.CommandText = updateSql;
command.CommandType = System.Data.CommandType.Text;
SqlExecutor.ExecuteNonQuery(LoginUser, command);
LogVerbose(tableName + " Indexes Statuses UPdated");
}
private void удалитьВыбранноеПолеToolStripMenuItem_Click(object sender, EventArgs e)
{
var cells = dataGridView1.SelectedCells.Cast <DataGridViewCell>();
var cell = cells.First();
if (!cells.All(c => c.RowIndex == cell.RowIndex) || cells.Count() == 0)
{
MessageBox.Show("Выберите ячейки одной строки");
return;
}
Attribute attribute = ((Attribute)dataGridView1.Rows[cell.RowIndex].Cells[0].Tag);
if (attribute.IsPKey)
{
MessageBox.Show("Нельзя удалять первичный ключ");
return;
}
metaDbContainer.AttributeSet.Remove(attribute);
currentTable.Attributes.Remove(attribute);
metaDbContainer.SaveChanges();
if (attribute.IsFKey)
{
Table parentTable = metaDbContainer.TableSet.Where(t => t.RealName == attribute.Type)
.First();
currentTable.ParentTables.Remove(parentTable);
parentTable.ChildTables.Remove(currentTable);
SqlExecutor.ExecuteNonQuery(dbConnection, $"ALTER TABLE {currentTable.RealName} DROP constraint [FK_{currentTable.RealName}_{attribute.RealName}]");
metaDbContainer.SaveChanges();
}
SqlExecutor.ExecuteNonQuery(dbConnection, $"ALTER TABLE {currentTable.RealName} DROP COLUMN {attribute.RealName}");
dataGridView1.Rows.RemoveAt(cell.RowIndex);
MessageBox.Show("Колонка успешно удалена");
}
protected virtual void ExecuteStatementsWithinTransaction(
[NotNull] IEnumerable <SqlBatch> sqlStatements, [NotNull] RelationalConnection connection)
{
Check.NotNull(sqlStatements, "sqlStatements");
Check.NotNull(connection, "connection");
RelationalTransaction transaction = null;
try
{
transaction = connection.BeginTransaction(IsolationLevel.Serializable);
SqlExecutor.ExecuteNonQuery(connection, transaction.DbTransaction, sqlStatements);
transaction.Commit();
}
finally
{
if (transaction != null)
{
transaction.Dispose();
}
}
}
private void CleanUp()
{
_sqlExecutor = new SqlExecutor();
_sqlExecutor.ExecuteNonQuery($"DELETE FROM dbo.EventsToPublish WHERE Id IN ('{_id}','{_id1}','{_id2}','{_id3}')");
_eventToPublishRepository?.UnitOfWork?.Dispose();
}
private void создатьToolStripMenuItem_Click(object sender, EventArgs e)
{
DataGridViewRowCollection attributes = dataGridView.Rows;
System.Collections.IList list = attributes;
var tableName = tableNameTextBox.Text;
var realTableName = "t" + DateTime.Now.ToString("ddMMyyyy_HHmmss");
if (!CheckValidity())
{
return;
}
var dataGridViewRows = attributes.Cast <DataGridViewRow>()
.Where(row => (string)row.Cells[0].EditedFormattedValue != "");
var createCols = dataGridViewRows
.Select((row, index) => Row2SqlCreateTable(row, index));
var fkStartIndex = dataGridViewRows.Count();
var fkAttributes = fkDataGridView.Rows.Cast <DataGridViewRow>().Where(row => (string)row.Cells[0].EditedFormattedValue != "");
var fkCols = fkAttributes.Select((row, index) => FKRow2SqlCreateTable(row, index + fkStartIndex));
var colsInTable = createCols.Union(fkCols).ToList();
// create table
sqlExecutor.ExecuteNonQuery($"CREATE TABLE {realTableName}({string.Join(", ", colsInTable)})");
// create indexes
dataGridViewRows
.Select((row, index) => Row2SqlCreateIndex(row, index, realTableName))
.Where(x => x != "")
.Select(sqlExecutor.ExecuteNonQuery).ToList();
// create PK
dataGridViewRows
.Select((row, index) => Row2SqlPK(row, index, realTableName))
.Where(x => x != "")
.Select(sqlExecutor.ExecuteNonQuery).ToList();
// add constraint on FK for current table
if (fkAttributes.Count() > 0)
{
sqlExecutor.ExecuteNonQuery($"ALTER TABLE {realTableName} ADD "
+ string.Join(", ", fkAttributes.Select((row, index) => Row2FKSql(row, index + fkStartIndex, realTableName)))
);
}
// create EF Table
var efTable = new Table(tableName, realTableName);
efTable.Attributes = dataGridViewRows
.Select((row, index) => Row2Attribute(row, index))
.Union(
fkAttributes.Select((row, index) => Row2FKAttribute(row, index + fkStartIndex))
)
.ToList();
efTable.ParentTables = fkAttributes.Select(row => Row2ParentTable(row)).ToList();
metaDbContainer.TableSet.Add(efTable);
metaDbContainer.SaveChanges();
fkAttributes.Select(row =>
{
string tn = row.Cells[0].EditedFormattedValue.ToString();
string realName = tableRealName2Name[tn];
metaDbContainer.TableSet.Where(x => x.RealName == realName).First().ChildTables.Add(efTable);
return(0);
}).ToList();
metaDbContainer.SaveChanges();
log.Info($"Table {tableName} ({realTableName}) was created successfully");
MessageBox.Show("Таблица успешно создана!");
((MainForm)Parent.Parent).LoadTreeView();
this.Close();
}