/// <summary>
/// Provides the insert statement.
/// </summary>
/// <param name="entity"></param>
/// <param name="IsPkAutoIncrement"></param>
/// <returns></returns>
public virtual async Task <int> CreateAsync(TEntity entity, bool IsPkAutoIncrement = false)
{
int result = 0;
using (var connection = await _connectionFactory.GetAsync())
{
result = await CreateAsync(entity, connection, IsPkAutoIncrement);
}
return(result);
}
public static void InsertFooTable(IMySqlConnectionFactory conn, int numberOfRowsToInsert = 10)
{
StringBuilder sqlInsert = new StringBuilder();
sqlInsert.Append("INSERT INTO `mysqlcoretest`.`foo` (id,Description) VALUES");
bool first = true;
for (int i = 1; i <= numberOfRowsToInsert; i++)
{
Guid guidInsert = Guid.NewGuid();
if (first)
{
sqlInsert.Append($"('{guidInsert.ToString()}','row {i.ToString()}')");
first = false;
}
else
{
sqlInsert.Append($",('{guidInsert.ToString()}','row {i.ToString()}')");
}
}
//Execute SQL Query
using (var c = conn.GetAsync(GetDatabaseNameString()).Result)
{
c.ExecuteAsync(sqlInsert.ToString()).Wait();
}
}
public void Should_Return_Success()
{
int result = 0;
string sqlCommand = "SELECT IFNULL((SELECT 1 FROM information_schema.tables WHERE table_schema = 'mysqlcoretest' AND table_name = 'foo' LIMIT 1),0) as TableExist;";
//Execute SQL Query
using (var c = _connectionFactory.GetAsync().Result)
{
result = c.ExecuteScalarAsync <int>(sqlCommand.ToString()).Result;
}
Assert.IsTrue(result == 1);
FooEntity f = new FooEntity()
{
id = Guid.NewGuid(),
Description = "Test",
CreatedAt = null
};
var rows = _repository.CreateAsync(f).Result;
Assert.IsTrue(rows == 1);
}
/// <summary>
/// Create the database if not exists.
/// Database Name parameter is passed in the constructor
/// </summary>
/// <returns></returns>
public bool CreateDatabaseIfNotExist()
{
try
{
var sqlCreate = $"create database if not exists `{_databaseName}`";
using (var c = conn.GetAsync("sys").Result)
{
c.ExecuteAsync(sqlCreate).Wait();
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public static void Drop(IMySqlConnectionFactory conn)
{
var sqlDrop = $"drop database if exists `mysqlcoretest`";
using (var c = conn.GetAsync().Result)
{
c.ExecuteAsync(sqlDrop).Wait();
}
}
public static void CreateFooView(IMySqlConnectionFactory conn)
{
StringBuilder sqlCreateView = new StringBuilder();
sqlCreateView.AppendLine("CREATE VIEW `mysqlcoretest`.FooView ");
sqlCreateView.AppendLine("AS ");
sqlCreateView.AppendLine("SELECT `id`,`Description`,CONCAT('Foo --> ',Description) as Concat FROM `mysqlcoretest`.`foo`");
//Execute SQL Query
using (var c = conn.GetAsync(GetDatabaseNameString()).Result)
{
c.ExecuteAsync(sqlCreateView.ToString()).Wait();
}
}
public static void CreateFooStoredProcedureWithoutParameter(IMySqlConnectionFactory conn)
{
StringBuilder sqlCreateStoredProcedure = new StringBuilder();
sqlCreateStoredProcedure.AppendLine("CREATE PROCEDURE `mysqlcoretest`.FooStoredProcedureWithoutParameter() ");
sqlCreateStoredProcedure.AppendLine("BEGIN ");
sqlCreateStoredProcedure.AppendLine("SELECT `id`,`Description` FROM `mysqlcoretest`.`foo`; ");
sqlCreateStoredProcedure.AppendLine("END ");
//Execute SQL Query
using (var c = conn.GetAsync(GetDatabaseNameString()).Result)
{
c.ExecuteAsync(sqlCreateStoredProcedure.ToString()).Wait();
}
}
public static void CreateFooStoredProcedureWithNullParameter(IMySqlConnectionFactory conn)
{
StringBuilder sqlCreateStoredProcedure = new StringBuilder();
sqlCreateStoredProcedure.AppendLine("CREATE PROCEDURE `mysqlcoretest`.FooStoredProcedureWithNullParameter (IN idValue CHAR(36), IN descriptionValue VARCHAR(200)) ");
sqlCreateStoredProcedure.AppendLine("BEGIN ");
sqlCreateStoredProcedure.AppendLine("INSERT INTO `mysqlcoretest`.`foo` (`id`,`Description`) VALUEs(idValue,descriptionValue); ");
sqlCreateStoredProcedure.AppendLine("END ");
//Execute SQL Query
using (var c = conn.GetAsync(GetDatabaseNameString()).Result)
{
c.ExecuteAsync(sqlCreateStoredProcedure.ToString()).Wait();
}
}
public void Should_Return_Success()
{
var scriptsFolder = Path.Combine(Path.GetDirectoryName(this.GetType().Assembly.Location), "GivenDatabasePatcher", "Scripts");
migration.ExecuteMigrations(scriptsFolder);
string sqlCommand = $"SELECT IFNULL((SELECT 1 FROM information_schema.tables WHERE table_schema = '{Database.GetDatabaseNameString()}' AND table_name = '_DatabaseMigrations' LIMIT 1),0) as TableExist;";
int result = 0;
//Execute SQL Query
using (var c = _connectionFactory.GetAsync().Result)
{
result = c.ExecuteScalarAsync <int>(sqlCommand.ToString()).Result;
}
Assert.IsTrue(result == 1);
}
public static void CreateFooTable(IMySqlConnectionFactory conn)
{
StringBuilder sqlCreate = new StringBuilder();
sqlCreate.Append("CREATE TABLE IF NOT EXISTS `mysqlcoretest`.`foo`");
sqlCreate.Append("(");
sqlCreate.Append("`id` CHAR(36) NOT NULL ,");
sqlCreate.Append("`Description` VARCHAR(100) NULL,");
sqlCreate.Append("`CreatedAt` DateTime NULL,");
sqlCreate.Append(" PRIMARY KEY (`id`),");
sqlCreate.Append("UNIQUE INDEX `ID_UNIQUE` (`id` ASC)");
sqlCreate.Append(")");
//Execute SQL Query
using (var c = conn.GetAsync(GetDatabaseNameString()).Result)
{
c.ExecuteAsync(sqlCreate.ToString()).Wait();
}
}