public IEnumerable <MovieTheater> GetMovieTheaters()
{
using (var sql = new SqlScriptExecutor(GetConectionString))
{
return(sql.SelectRecords <MovieTheater>("SELECT [ID],[Name] FROM [MovieTheater] ORDER BY [Name]"));
}
}
public void uses_variable_subtitute_preprocessor_when_running_scripts_with_nested_comment()
{
var multiLineComment = @"/*
some comment
/* from excel $A$6 */
some comment
*/
create $foo$.Table";
var multiLineCommentResult = @"/*
some comment
/* from excel $A$6 */
some comment
*/
create bar.Table"; var dbConnection = Substitute.For <IDbConnection>();
var command = Substitute.For <IDbCommand>();
dbConnection.CreateCommand().Returns(command);
var executor = new SqlScriptExecutor(() => new TestConnectionManager(dbConnection, true), () => new ConsoleUpgradeLog(), null, () => true, null, () => Substitute.For <IJournal>());
executor.Execute(new SqlScript("Test", multiLineComment), new Dictionary <string, string> {
{ "foo", "bar" }
});
command.Received().ExecuteNonQuery();
command.CommandText.ShouldBe(multiLineCommentResult);
}
public void Post([FromBody] ScheduleRow value)
{
DateTime date;
int ScheduleRowID;
date = value.Date;
using (var sql = new SqlScriptExecutor(GetConectionString))
{
sql.BeginTransaction();
List <KeyValuePair <string, object> > values = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("Movie", value.Movie),
new KeyValuePair <string, object>("MovieTheater", value.MovieTheater),
new KeyValuePair <string, object>("Date", date)
};
ScheduleRowID = sql.InsertValues("Schedule", values);
values.Clear();
values.Add(new KeyValuePair <string, object>("ScheduleRow", ScheduleRowID));
values.Add(new KeyValuePair <string, object>());
foreach (var scheduleTime in value.StartTimeList)
{
values[1] = new KeyValuePair <string, object>("Time", scheduleTime.Time);
sql.InsertValues("ScheduleTime", values);
}
sql.CommitTransaction();
}
}
public void Delete(int id)
{
using (var sql = new SqlScriptExecutor(GetConectionString))
{
sql.BeginTransaction();
sql.DeleteValues("Schedule", id);//Остальные записи удалятся по связям настроенным в БД
sql.CommitTransaction();
}
}
private void ButtonGenerate_OnClick(object sender, RoutedEventArgs e)
{
Project project = new Project(_database);
project.BatchStatementCount = 1;
project.InitializeDefaultGenerator();
var sqlScriptExecutor = new SqlScriptExecutor();
project.Generate(sqlScriptExecutor, 100, 5);
MessageBox.Show("Done");
}
public void when_schema_is_null_schema_is_stripped_from_scripts()
{
var dbConnection = Substitute.For <IDbConnection>();
var command = Substitute.For <IDbCommand>();
dbConnection.CreateCommand().Returns(command);
var executor = new SqlScriptExecutor(() => new TestConnectionManager(dbConnection, true), () => new ConsoleUpgradeLog(), null, () => true, null, () => Substitute.For <IJournal>());
executor.Execute(new SqlScript("Test", "create $schema$.Table"));
command.Received().ExecuteNonQuery();
command.CommandText.ShouldBe("create Table");
}
public void uses_variable_subtitutes_schema()
{
var dbConnection = Substitute.For <IDbConnection>();
var command = Substitute.For <IDbCommand>();
dbConnection.CreateCommand().Returns(command);
var executor = new SqlScriptExecutor(() => new TestConnectionManager(dbConnection, true), () => new ConsoleUpgradeLog(), "foo", () => true, null, () => Substitute.For <IJournal>());
executor.Execute(new SqlScript("Test", "create $schema$.Table"));
command.Received().ExecuteNonQuery();
command.CommandText.ShouldBe("create [foo].Table");
}
public void uses_variable_subtitutes_schema()
{
var dbConnection = Substitute.For <IDbConnection>();
var command = Substitute.For <IDbCommand>();
dbConnection.CreateCommand().Returns(command);
var executor = new SqlScriptExecutor(() => dbConnection, () => new ConsoleUpgradeLog(), "foo", () => true, null);
executor.Execute(new SqlScript("Test", "create $schema$.Table"));
command.Received().ExecuteNonQuery();
Assert.AreEqual("create [foo].Table", command.CommandText);
}
public void when_schema_is_null_schema_is_stripped_from_scripts()
{
var dbConnection = Substitute.For <IDbConnection>();
var command = Substitute.For <IDbCommand>();
dbConnection.CreateCommand().Returns(command);
var executor = new SqlScriptExecutor(() => dbConnection, () => new ConsoleUpgradeLog(), null, () => true, null);
executor.Execute(new SqlScript("Test", "create $schema$.Table"));
command.Received().ExecuteNonQuery();
Assert.AreEqual("create Table", command.CommandText);
}
public void logs_output_when_configured_to()
{
var dbConnection = Substitute.For <IDbConnection>();
var command = Substitute.For <IDbCommand>();
var reader = Substitute.For <IDataReader>();
reader.FieldCount.Returns(2);
reader.GetName(Arg.Is(0)).Returns("One");
reader.GetName(Arg.Is(1)).Returns("Two");
reader.GetName(Arg.Is <int>(i => i < 0 || i > 1)).Throws(new ArgumentOutOfRangeException("i"));
reader.Read().Returns(true, false);
reader.GetValue(Arg.Is(0)).Returns("A");
reader.GetValue(Arg.Is(1)).Returns("B");
reader.NextResult().Returns(false);
command.ExecuteReader().Returns(reader);
var logger = new CaptureLogsLogger();
dbConnection.CreateCommand().Returns(command);
var executor = new SqlScriptExecutor(() => new TestConnectionManager(dbConnection, true)
{
IsScriptOutputLogged = true
},
() => logger,
"foo",
() => true,
null,
() => Substitute.For <IJournal>());
executor.Execute(new SqlScript("Test", "SELECT * FROM $schema$.[Table]"));
command.Received().ExecuteReader();
command.DidNotReceive().ExecuteNonQuery();
command.CommandText.ShouldBe("SELECT * FROM [foo].[Table]");
logger.Log.Trim()
.ShouldBe(string.Join(Environment.NewLine, new[]
{
"Info: Executing Database Server script 'Test'",
"Info: -------------",
"Info: | One | Two |",
"Info: -------------",
"Info: | A | B |",
"Info: -------------",
"Info:"
}));
}
public void uses_variable_subtitute_preprocessor_when_running_scripts()
{
var dbConnection = Substitute.For <IDbConnection>();
var command = Substitute.For <IDbCommand>();
dbConnection.CreateCommand().Returns(command);
var executor = new SqlScriptExecutor(() => new TestConnectionManager(dbConnection, true), () => new ConsoleUpgradeLog(), null, () => true, null, () => Substitute.For <IJournal>());
executor.Execute(new SqlScript("Test", "create $foo$.Table"), new Dictionary <string, string> {
{ "foo", "bar" }
});
command.Received().ExecuteNonQuery();
command.CommandText.ShouldBe("create bar.Table");
}
public void does_not_use_variable_subtitute_preprocessor_when_setting_false()
{
var dbConnection = Substitute.For <IDbConnection>();
var command = Substitute.For <IDbCommand>();
dbConnection.CreateCommand().Returns(command);
var executor = new SqlScriptExecutor(() => new TestConnectionManager(dbConnection, true), () => new ConsoleUpgradeLog(), null, () => false, null);
executor.Execute(new SqlScript("Test", "create $foo$.Table"), new Dictionary <string, string> {
{ "foo", "bar" }
});
command.Received().ExecuteNonQuery();
Assert.AreEqual("create $foo$.Table", command.CommandText);
}
public void logs_output_when_configured_to()
{
var dbConnection = Substitute.For <IDbConnection>();
var command = Substitute.For <IDbCommand>();
dbConnection.CreateCommand().Returns(command);
var executor = new SqlScriptExecutor(() => new TestConnectionManager(dbConnection, true)
{
IsScriptOutputLogged = true
}, () => new ConsoleUpgradeLog(), "foo", () => true, null, () => Substitute.For <IJournal>());
executor.Execute(new SqlScript("Test", "create $schema$.Table"));
command.Received().ExecuteReader();
command.DidNotReceive().ExecuteNonQuery();
command.CommandText.ShouldBe("create [foo].Table");
}
public void records_execution_time()
{
var dbConnection = Substitute.For <IDbConnection>();
var command = Substitute.For <IDbCommand>();
dbConnection.CreateCommand().Returns(command);
var executor = new SqlScriptExecutor(() => new TestConnectionManager(dbConnection, true)
{
IsScriptOutputLogged = true
}, () => new ConsoleUpgradeLog(), "foo", () => true, null, () => Substitute.For <DbUp.Engine.IJournal>());
var script = new SqlScript("Test", "SELECT 1");
executor.Execute(script);
script.ExecutionTime.ShouldBeGreaterThan(-1);
}
public void uses_variable_subtitute_preprocessor_when_running_scripts_with_one_line_comment()
{
string oneLineComment = @"/* from excel $A$6 */
create $foo$.Table";
string oneLineCommentResult = @"/* from excel $A$6 */
create bar.Table";
var dbConnection = Substitute.For <IDbConnection>();
var command = Substitute.For <IDbCommand>();
dbConnection.CreateCommand().Returns(command);
var executor = new SqlScriptExecutor(() => new TestConnectionManager(dbConnection, true), () => new ConsoleUpgradeLog(), null, () => true, null);
executor.Execute(new SqlScript("Test", oneLineComment), new Dictionary <string, string> {
{ "foo", "bar" }
});
command.Received().ExecuteNonQuery();
Assert.AreEqual(oneLineCommentResult, command.CommandText);
}
public void logs_when_dbexception()
{
var dbConnection = Substitute.For <IDbConnection>();
var command = Substitute.For <IDbCommand>();
command.When(x => x.ExecuteNonQuery()).Do(x =>
{
var ex = Substitute.For <DbException>();
ex.ErrorCode.Returns(1);
ex.Message.Returns("Message with curly braces {0}");
throw ex;
});
dbConnection.CreateCommand().Returns(command);
var logger = Substitute.For <IUpgradeLog>();
logger.WhenForAnyArgs(x => x.WriteError(null, null)).Do(x => Console.WriteLine(x.Arg <string>(), x.Arg <object[]>()));
var executor = new SqlScriptExecutor(() => new TestConnectionManager(dbConnection, true), () => logger, null, () => true, null);
Assert.That(() => executor.Execute(new SqlScript("Test", "create $schema$.Table")), Throws.InstanceOf(typeof(DbException)));
command.Received().ExecuteNonQuery();
logger.ReceivedWithAnyArgs().WriteError(Arg.Any <string>(), Arg.Any <object[]>());
}
private ICollection <ScheduleRow> SelectSchedule(SqlCommand cmd)
{
ScheduleRow currentRow;
string cmdText;
using (var sql = new SqlScriptExecutor(GetConectionString))
{
ICollection <ScheduleRow> result = new LinkedList <ScheduleRow>(sql.SelectRecords <ScheduleRow>(cmd, (record) => record.StartTimeList = new List <ScheduleTime>()));
if (result.Count != 0)
{
SortedList <int, ScheduleRow> orderedScheduleRows = new SortedList <int, ScheduleRow>(result.Count);
foreach (ScheduleRow row in result)
{
orderedScheduleRows.Add(row.ID, row);
}
currentRow = null;
///Тут мы пытаемся избежать большого фильтра вида [ScheduleRow] in (), потому что SQL сервер будет долго парсить запрос.
///и разбиваем на несколько запросов, пытаясь найти подряд идущие Id-шники, чтобы сформировать конструкцию Between.
///но можно и не пытаться искать Between, а просто разбить на несколько in ().
foreach (string filter in SqlScriptExecutor.GetFilters(orderedScheduleRows.Keys, "[ScheduleRow]", 10, 1000, 20, 5000))
{
cmdText = "SELECT [ID],[ScheduleRow],[Time] FROM [ScheduleTime] WHERE " + filter + " ORDER BY [ScheduleRow], [Time]";
foreach (ScheduleTime currentTime in sql.SelectRecords <ScheduleTime>(cmdText))
{
if (currentRow == null || currentRow.ID != currentTime.ScheduleRow)
{
currentRow = orderedScheduleRows[currentTime.ScheduleRow];
}
currentRow.StartTimeList.Add(currentTime);
}
}
}
return(result);
}
}
private async void ButtonGenerate_OnClick(object sender, RoutedEventArgs e)
{
if (ConnectionStringObject == null)
{
return;
}
Database database = Database.Get(SelectedDatabaseSystem.ToString(), ConnectionStringObject.ToString(true));
if (!database.Exists)
{
MessageBox.Show("Cannot connect to the database");
return;
}
// Update Recent configurations
Settings.Current.AddRecentConnectionString(SelectedDatabaseSystem, database);
Settings.Current.SerializeToConfiguration();
// Generate
try
{
CircularProgressBar.Visibility = Visibility.Visible;
Exception exception = null;
var count = ConvertUtilities.ChangeType(TextBoxRows.Text, 100);
var nullCount = ConvertUtilities.ChangeType(TextBoxNullRows.Text, 5);
await Task.Run(() =>
{
Project project = new Project(database);
project.BatchStatementCount = 1;
project.InitializeDefaultGenerator();
SqlScriptExecutor sqlScriptExecutor = new SqlScriptExecutor();
if (Debugger.IsAttached)
{
project.Generate(sqlScriptExecutor, count, nullCount);
}
else
{
try
{
project.Generate(sqlScriptExecutor, count, nullCount);
}
catch (Exception ex)
{
exception = ex;
}
}
});
if (exception == null)
{
MessageBox.Show("Done");
}
else
{
MessageBox.Show(exception.ToString(), "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
CircularProgressBar.Visibility = Visibility.Collapsed;
}
}
public void verify_schema_should_not_check_when_schema_is_null()
{
var executor = new SqlScriptExecutor(() => Substitute.For <IConnectionManager>(), () => null, null, () => false, null, () => Substitute.For <IJournal>());
executor.VerifySchema();
}
public void Put(int id, [FromBody] ScheduleRow value)
{
DateTime date;
int ScheduleRowID = id;
date = value.Date;
using (var sql = new SqlScriptExecutor(GetConectionString))
{
sql.BeginTransaction();
//Сохраним саму запись
List <KeyValuePair <string, object> > values = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("Movie", value.Movie),
new KeyValuePair <string, object>("MovieTheater", value.MovieTheater),
new KeyValuePair <string, object>("Date", date)
};
sql.UpdateValues("Schedule", id, values);
values.Clear();
values.Add(new KeyValuePair <string, object>("ScheduleRow", ScheduleRowID));
values.Add(new KeyValuePair <string, object>());
LinkedList <int> timesToDelete = new LinkedList <int>(); //Список элементов на удаление
LinkedList <ScheduleTime> timesToUpdate = new LinkedList <ScheduleTime>(); //Список элементов на обновление
ScheduleTime[] timesToInsert = value.StartTimeList.Where(it => it.ID == 0).ToArray(); //Список элементов на добавление
//Просмотрим существующие записи о времени. Вслучае, если передаваемые записи на изменение были удалены,
//Запретим менять запись, выкинув Exception.
const string cmdText = "SELECT [ID] FROM [ScheduleTime] WHERE [ScheduleRow] = @id ORDER BY [ID]";
using (SqlCommand cmd = new SqlCommand(cmdText))
{
cmd.Parameters.Add(new SqlParameter("@id", id));
//Упорядочим идентификаторы существующих записей для сравнения с записями в БД
ScheduleTime[] orderedTime = value.StartTimeList.Where(it => it.ID != 0).OrderBy(it => it.ID).ToArray();
int i = 0;
int valueFromReader = -1;
ScheduleTime currentTime = null;
//Сравним две упорядоченные цепочки идентификаторов
using (var reader = sql.ExecuteReader(cmd))
{
bool readerEnd = !reader.Read();
bool localValuesEnd = i == orderedTime.Length;
while (!readerEnd && !localValuesEnd)
{
valueFromReader = reader.GetInt32(0);
currentTime = orderedTime[i];
if (currentTime.ID == valueFromReader)//Оба значения есть и там и там, всё впорядке, обновим запись. Двигаем обе цепочки
{
timesToUpdate.AddLast(currentTime);
readerEnd = !reader.Read();
localValuesEnd = ++i == orderedTime.Length;
}
else if (currentTime.ID < valueFromReader) //Записи в БД нет, то есть она была удалена за время редактирования. Продолжение невозможно. Выкидываем Exception
{
throw new Exception("Информация устарела, обновите редактор");
}
else //(currentTime.ID > valueFromReader) //Есть запись в БД, которой нет локально - Двигаем reader
{
timesToDelete.AddLast(valueFromReader);
readerEnd = !reader.Read();
}
}
if (!readerEnd) //Если остались записи в БД, которых нет локально
{
do
{
timesToDelete.AddLast(reader.GetInt32(0));
}while (reader.Read());
}
else if (!localValuesEnd) //Остались записи, которых нет в БД
{
throw new Exception("Информация устарела, обновите редактор");
}
}
}
foreach (var currentID in timesToDelete)
{
sql.DeleteValues("ScheduleTime", currentID);
}
foreach (var currentTime in timesToUpdate)
{
values[1] = new KeyValuePair <string, object>("Time", currentTime.Time);
sql.UpdateValues("ScheduleTime", currentTime.ID, values);
}
foreach (var currentTime in timesToInsert)
{
values[1] = new KeyValuePair <string, object>("Time", currentTime.Time);
sql.InsertValues("ScheduleTime", values);
}
sql.CommitTransaction();
}
}
public void verify_schema_should_not_check_when_schema_is_null()
{
var executor = new SqlScriptExecutor(() => null, () => null, null, () => false, null);
executor.VerifySchema();
}
