public void Should_Null_The_DbCommand_By_Default()
{
// Arrange
const string sql = @"
CREATE TABLE #SuperHero
(
SuperHeroId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
SuperHeroName NVARCHAR(120) NOT NULL
);
INSERT INTO #SuperHero ( SuperHeroName )
VALUES ( 'Superman' );
SELECT SuperHeroId, /* This should be the only value returned from ExecuteScalarAsync */
SuperHeroName
FROM #SuperHero;
";
var databaseCommand = Sequelocity.GetDatabaseCommand(ConnectionStringsNames.SqlServerConnectionString)
.SetCommandText(sql);
// Act
databaseCommand.ExecuteScalarAsync()
.Wait(); // Block until the task completes.
// Assert
Assert.IsNull(databaseCommand.DbCommand);
}
public void Should_Keep_The_Database_Connection_Open_If_keepConnectionOpen_Parameter_Was_True()
{
// Arrange
const string sql = @"
DROP TABLE IF EXISTS SuperHero;
CREATE TEMPORARY TABLE SuperHero
(
SuperHeroId serial not null primary key,
SuperHeroName VARCHAR(120) NOT NULL
);
INSERT INTO SuperHero ( SuperHeroName )
VALUES ( 'Superman' );
INSERT INTO SuperHero ( SuperHeroName )
VALUES ( 'Batman' );
SELECT SuperHeroId,
SuperHeroName
FROM SuperHero;
";
var databaseCommand = Sequelocity.GetDatabaseCommand(ConnectionStringsNames.PostgreSQLConnectionString)
.SetCommandText(sql);
// Act
databaseCommand.ExecuteToDynamicObjectAsync(true)
.Wait(); // Block until the task completes.
// Assert
Assert.That(databaseCommand.DbCommand.Connection.State == ConnectionState.Open);
// Cleanup
databaseCommand.Dispose();
}
public void Should_Null_The_DbCommand_By_Default()
{
// Arrange
const string sql = @"
DROP TEMPORARY TABLE IF EXISTS SuperHero;
CREATE TEMPORARY TABLE SuperHero
(
SuperHeroId INT NOT NULL AUTO_INCREMENT,
SuperHeroName VARCHAR(120) NOT NULL,
PRIMARY KEY ( SuperHeroId )
);
INSERT INTO SuperHero ( SuperHeroName )
VALUES ( 'Superman' );
INSERT INTO SuperHero ( SuperHeroName )
VALUES ( 'Batman' );
SELECT SuperHeroId,
SuperHeroName
FROM SuperHero;
";
var databaseCommand = Sequelocity.GetDatabaseCommand(ConnectionStringsNames.MySqlConnectionString)
.SetCommandText(sql);
// Act
databaseCommand.ExecuteToObject <SuperHero>();
// Assert
Assert.IsNull(databaseCommand.DbCommand);
}
public void Should_Call_The_DatabaseCommandPostExecuteEventHandler()
{
// Arrange
bool wasPostExecuteEventHandlerCalled = false;
Sequelocity.ConfigurationSettings.EventHandlers.DatabaseCommandPostExecuteEventHandlers.Add(command => wasPostExecuteEventHandlerCalled = true);
// Act
Sequelocity.GetDatabaseCommand(ConnectionStringsNames.SqlServerConnectionString)
.SetCommandText("SELECT 1 as SuperHeroId, 'Superman' as SuperHeroName")
.ExecuteToMapAsync(record =>
{
var obj = new SuperHero
{
SuperHeroId = record.GetValue(0).ToLong(),
SuperHeroName = record.GetValue(1).ToString()
};
return(obj);
})
.Wait(); // Block until the task completes.
// Assert
Assert.IsTrue(wasPostExecuteEventHandlerCalled);
}
public void Should_Return_A_Task_Resulting_In_A_Map_Of_The_First_Result_To_A_Dynamic_Object()
{
// Arrange
const string sql = @"
DROP TABLE IF EXISTS SuperHero;
CREATE TEMPORARY TABLE SuperHero
(
SuperHeroId serial not null primary key,
SuperHeroName VARCHAR(120) NOT NULL
);
INSERT INTO SuperHero ( SuperHeroName )
VALUES ( 'Superman' );
INSERT INTO SuperHero ( SuperHeroName )
VALUES ( 'Batman' );
SELECT SuperHeroId,
SuperHeroName
FROM SuperHero;
";
// Act
var superHeroTask = Sequelocity.GetDatabaseCommand(ConnectionStringsNames.PostgreSQLConnectionString)
.SetCommandText(sql)
.ExecuteToDynamicObjectAsync();
// Assert
Assert.IsInstanceOf <Task <dynamic> >(superHeroTask);
Assert.NotNull(superHeroTask.Result);
Assert.That(superHeroTask.Result.SuperHeroId == 1);
Assert.That(superHeroTask.Result.SuperHeroName == "Superman");
}
public void Should_Map_The_Results_Back_To_A_List_Of_Dynamic()
{
// Arrange
const string sql = @"
CREATE TABLE #SuperHero
(
SuperHeroId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
SuperHeroName NVARCHAR(120) NOT NULL
);
INSERT INTO #SuperHero ( SuperHeroName )
VALUES ( 'Superman' );
INSERT INTO #SuperHero ( SuperHeroName )
VALUES ( 'Batman' );
SELECT SuperHeroId,
SuperHeroName
FROM #SuperHero;
";
// Act
var superHeroes = Sequelocity.GetDatabaseCommand(ConnectionStringsNames.SqlServerConnectionString)
.SetCommandText(sql)
.ExecuteToDynamicList();
// Assert
Assert.That(superHeroes.Count == 2);
Assert.That(superHeroes[0].SuperHeroId == 1);
Assert.That(superHeroes[0].SuperHeroName == "Superman");
Assert.That(superHeroes[1].SuperHeroId == 2);
Assert.That(superHeroes[1].SuperHeroName == "Batman");
}
public void Should_Keep_The_Database_Connection_Open_If_keepConnectionOpen_Parameter_Was_True()
{
// Arrange
const string sql = @"
CREATE TABLE #SuperHero
(
SuperHeroId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
SuperHeroName NVARCHAR(120) NOT NULL
);
INSERT INTO #SuperHero ( SuperHeroName )
VALUES ( 'Superman' );
INSERT INTO #SuperHero ( SuperHeroName )
VALUES ( 'Batman' );
SELECT SuperHeroId,
SuperHeroName
FROM #SuperHero;
";
var databaseCommand = Sequelocity.GetDatabaseCommand(ConnectionStringsNames.SqlServerConnectionString)
.SetCommandText(sql);
// Act
var superHeroes = databaseCommand.ExecuteToDynamicList(true);
// Assert
Assert.That(databaseCommand.DbCommand.Connection.State == ConnectionState.Open);
// Cleanup
databaseCommand.Dispose();
}
public void AddParameter_Example_Specifying_An_Explicit_DbType()
{
List <SuperHero> superHeroes = Sequelocity.GetDatabaseCommand(ConnectionStringsNames.SqlServerConnectionString)
.SetCommandText("SELECT * FROM SuperHero WHERE SuperHeroName = @SuperHeroName")
.AddParameter("@SuperHeroName", "Superman", DbType.AnsiString)
.ExecuteToList <SuperHero>();
}
public void Should_Return_A_Type_Of_T()
{
// Arrange
const string sql = @"
CREATE TABLE #SuperHero
(
SuperHeroId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
SuperHeroName NVARCHAR(120) NOT NULL
);
INSERT INTO #SuperHero ( SuperHeroName )
VALUES ( 'Superman' );
INSERT INTO #SuperHero ( SuperHeroName )
VALUES ( 'Batman' );
SELECT SuperHeroId,
SuperHeroName
FROM #SuperHero;
";
// Act
var superHero = Sequelocity.GetDatabaseCommand(ConnectionStringsNames.SqlServerConnectionString)
.SetCommandText(sql)
.ExecuteToObject <SuperHero>();
// Assert
Assert.NotNull(superHero);
Assert.That(superHero.SuperHeroId == 1);
Assert.That(superHero.SuperHeroName == "Superman");
}
public void ExecuteToList_Example()
{
// Arrange
const string sql = @"
CREATE TABLE #SuperHero
(
SuperHeroId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
SuperHeroName NVARCHAR(120) NOT NULL
);
INSERT INTO #SuperHero ( SuperHeroName )
VALUES ( 'Superman' );
INSERT INTO #SuperHero ( SuperHeroName )
VALUES ( 'Batman' );
SELECT SuperHeroId,
SuperHeroName
FROM #SuperHero;";
// Act
List <SuperHero> superHeroes = Sequelocity.GetDatabaseCommand(ConnectionStringsNames.SqlServerConnectionString)
.SetCommandText(sql)
.ExecuteToList <SuperHero>();
// Assert
Assert.That(superHeroes.Count == 2);
Assert.That(superHeroes[0].SuperHeroId == 1);
Assert.That(superHeroes[0].SuperHeroName == "Superman");
Assert.That(superHeroes[1].SuperHeroId == 2);
Assert.That(superHeroes[1].SuperHeroName == "Batman");
}
public void GenerateInsertForSqlServer_Example_Using_An_Anonymous_Type()
{
// Arrange
const string sql = @"
CREATE TABLE #Customer
(
CustomerId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
FirstName NVARCHAR(120) NOT NULL,
LastName NVARCHAR(120) NOT NULL,
DateOfBirth DATETIME NOT NULL
);";
DbConnection dbConnection = Sequelocity.CreateDbConnection(ConnectionStringsNames.SqlServerConnectionString);
Sequelocity.GetDatabaseCommand(dbConnection)
.SetCommandText(sql)
.ExecuteNonQuery(true); // Passing in 'true' to keep the connection open since this example is using a temp table which only exists during the scope / lifetime of this database connection
// Anonymous Type
var customer = new { FirstName = "Clark", LastName = "Kent", DateOfBirth = DateTime.Parse("06/18/1938") };
// Act
int customerId = Sequelocity.GetDatabaseCommand(dbConnection)
.GenerateInsertForSqlServer(customer, "#Customer") // Specifying table name since Sequelocity can't use the type name as the table name
.ExecuteScalar <int>();
// Assert
Assert.That(customerId == 1);
}
public void ExecuteScalar_Of_Type_T_Example()
{
// Arrange
const string sql = @"
CREATE TABLE #SuperHero
(
SuperHeroId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
SuperHeroName NVARCHAR(120) NOT NULL
);
INSERT INTO #SuperHero ( SuperHeroName )
VALUES ( 'Superman' );
SELECT SuperHeroId, /* This should be the only value returned from ExecuteScalar */
SuperHeroName
FROM #SuperHero;";
// Act
int superHeroId = Sequelocity.GetDatabaseCommand(ConnectionStringsNames.SqlServerConnectionString)
.SetCommandText(sql)
.ExecuteScalar <int>();
// Assert
Assert.That(superHeroId == 1);
}
public void ExecuteNonQuery_With_Parameters_Example()
{
// Arrange
const string sql = @"
CREATE TABLE #SuperHero
(
SuperHeroId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
SuperHeroName NVARCHAR(120) NOT NULL
);
INSERT INTO #SuperHero ( SuperHeroName )
VALUES ( @SuperHeroName1 );
INSERT INTO #SuperHero ( SuperHeroName )
VALUES ( @SuperHeroName2 );";
// Act
int rowsAffected = Sequelocity.GetDatabaseCommand(ConnectionStringsNames.SqlServerConnectionString)
.SetCommandText(sql)
.AddParameter("@SuperHeroName1", "Superman", DbType.AnsiString)
.AddParameter("@SuperHeroName2", "Batman", DbType.AnsiString)
.ExecuteNonQuery();
// Assert
Assert.That(rowsAffected == 2);
}
public void Should_Keep_The_Database_Connection_Open_If_keepConnectionOpen_Parameter_Was_True()
{
// Arrange
const string sql = @"
CREATE TABLE #SuperHero
(
SuperHeroId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
SuperHeroName NVARCHAR(120) NOT NULL
);
INSERT INTO #SuperHero ( SuperHeroName )
VALUES ( 'Superman' );
SELECT SuperHeroId, /* This should be the only value returned from ExecuteScalarAsync */
SuperHeroName
FROM #SuperHero;
";
var databaseCommand = Sequelocity.GetDatabaseCommand(ConnectionStringsNames.SqlServerConnectionString)
.SetCommandText(sql);
// Act
databaseCommand.ExecuteScalarAsync(true)
.Wait(); // Block until the task completes.
// Assert
Assert.That(databaseCommand.DbCommand.Connection.State == ConnectionState.Open);
// Cleanup
databaseCommand.Dispose();
}
public void Should_Return_The_Last_Inserted_Id()
{
// Arrange
const string createSchemaSql = @"
DROP TABLE IF EXISTS Customer;
CREATE TABLE IF NOT EXISTS Customer
(
CustomerId INT NOT NULL AUTO_INCREMENT,
FirstName NVARCHAR(120) NOT NULL,
LastName NVARCHAR(120) NOT NULL,
DateOfBirth DATETIME NOT NULL,
PRIMARY KEY ( CustomerId )
);
";
Sequelocity.GetDatabaseCommand(ConnectionStringsNames.MySqlConnectionString)
.SetCommandText(createSchemaSql)
.ExecuteNonQuery();
var customer = new Customer {
FirstName = "Clark", LastName = "Kent", DateOfBirth = DateTime.Parse("06/18/1938")
};
// Act
var customerId = Sequelocity.GetDatabaseCommand(ConnectionStringsNames.MySqlConnectionString)
.GenerateInsertForMySql(customer)
.ExecuteScalar()
.ToInt();
// Assert
Assert.That(customerId == 1);
}
public void Should_Return_A_Task_Resulting_In_A_Map_Of_The_First_Result_To_A_Dynamic_Object()
{
// Arrange
const string sql = @"
CREATE TABLE #SuperHero
(
SuperHeroId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
SuperHeroName NVARCHAR(120) NOT NULL
);
INSERT INTO #SuperHero ( SuperHeroName )
VALUES ( 'Superman' );
INSERT INTO #SuperHero ( SuperHeroName )
VALUES ( 'Batman' );
SELECT SuperHeroId,
SuperHeroName
FROM #SuperHero;
";
// Act
var superHeroTask = Sequelocity.GetDatabaseCommand(ConnectionStringsNames.SqlServerConnectionString)
.SetCommandText(sql)
.ExecuteToDynamicObjectAsync();
// Assert
Assert.IsInstanceOf <Task <dynamic> >(superHeroTask);
Assert.NotNull(superHeroTask.Result);
Assert.That(superHeroTask.Result.SuperHeroId == 1);
Assert.That(superHeroTask.Result.SuperHeroName == "Superman");
}
public void Should_Return_A_Task_Resulting_In_The_Number_Of_Affected_Rows()
{
// Arrange
const string sql = @"
DROP TEMPORARY TABLE IF EXISTS SuperHero;
CREATE TEMPORARY TABLE SuperHero
(
SuperHeroId INT NOT NULL AUTO_INCREMENT,
SuperHeroName VARCHAR(120) NOT NULL,
PRIMARY KEY ( SuperHeroId )
);
INSERT INTO SuperHero ( SuperHeroName ) VALUES ( 'Superman' ); /* This insert should trigger 1 row affected */
";
// Act
var rowsAffectedTask = Sequelocity.GetDatabaseCommand(ConnectionStringsNames.MySqlConnectionString)
.SetCommandText(sql)
.ExecuteNonQueryAsync();
// Assert
Assert.IsInstanceOf <Task <int> >(rowsAffectedTask);
Assert.That(rowsAffectedTask.Result == 1);
}
public void Should_Keep_The_Database_Connection_Open_If_keepConnectionOpen_Parameter_Was_True()
{
// Arrange
const string sql = @"
CREATE TABLE #SuperHero
(
SuperHeroId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
SuperHeroName NVARCHAR(120) NOT NULL
);
/* This insert should trigger 1 row affected */
INSERT INTO #SuperHero ( SuperHeroName )
VALUES ( 'Superman' );
";
var databaseCommand = Sequelocity.GetDatabaseCommand(ConnectionStringsNames.SqlServerConnectionString)
.SetCommandText(sql);
// Act
var rowsAffected = databaseCommand.ExecuteNonQuery(true);
// Assert
Assert.That(databaseCommand.DbCommand.Connection.State == ConnectionState.Open);
// Cleanup
databaseCommand.Dispose();
}
public void Should_Null_The_DbCommand_By_Default()
{
// Arrange
const string sql = @"
CREATE TABLE #SuperHero
(
SuperHeroId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
SuperHeroName NVARCHAR(120) NOT NULL
);
INSERT INTO #SuperHero ( SuperHeroName )
VALUES ( 'Superman' );
INSERT INTO #SuperHero ( SuperHeroName )
VALUES ( 'Batman' );
SELECT SuperHeroId,
SuperHeroName
FROM #SuperHero;
";
var databaseCommand = Sequelocity.GetDatabaseCommand(ConnectionStringsNames.SqlServerConnectionString)
.SetCommandText(sql);
// Act
var superHeroes = databaseCommand.ExecuteToDynamicList();
// Assert
Assert.IsNull(databaseCommand.DbCommand);
}
public void Should_Return_A_DataSet()
{
// Arrange
const string sql = @"
CREATE TABLE #SuperHero
(
SuperHeroId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
SuperHeroName NVARCHAR(120) NOT NULL
);
INSERT INTO #SuperHero ( SuperHeroName )
VALUES ( 'Superman' );
INSERT INTO #SuperHero ( SuperHeroName )
VALUES ( 'Batman' );
SELECT SuperHeroId,
SuperHeroName
FROM #SuperHero;
";
// Act
var dataSet = Sequelocity.GetDatabaseCommand(ConnectionStringsNames.SqlServerConnectionString)
.SetCommandText(sql)
.ExecuteToDataSet();
// Assert
Assert.That(dataSet.Tables[0].Rows.Count == 2);
Assert.That(dataSet.Tables[0].Rows[0][0].ToString() == "1");
Assert.That(dataSet.Tables[0].Rows[0][1].ToString() == "Superman");
Assert.That(dataSet.Tables[0].Rows[1][0].ToString() == "2");
Assert.That(dataSet.Tables[0].Rows[1][1].ToString() == "Batman");
}
public void Should_Null_The_DbCommand_If_Iteration_Ends_Before_Full_Enumeration()
{
// Arrange
const string sql = @"
CREATE TABLE #SuperHero
(
SuperHeroId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
SuperHeroName NVARCHAR(120) NOT NULL
);
INSERT INTO #SuperHero ( SuperHeroName )
VALUES ( 'Superman' );
INSERT INTO #SuperHero ( SuperHeroName )
VALUES ( 'Batman' );
SELECT SuperHeroId,
SuperHeroName
FROM #SuperHero;
";
var databaseCommand = Sequelocity.GetDatabaseCommand(ConnectionStringsNames.SqlServerConnectionString)
.SetCommandText(sql);
// Act
databaseCommand
.ExecuteReader(record => new
{
SuperHeroId = record.GetValue(0),
SuperHeroName = record.GetValue(1)
})
.First();
// Assert
Assert.IsNull(databaseCommand.DbCommand);
}
public void Should_Return_A_Task_Resulting_In_A_Map_Of_The_Results_To_A_List_Of_Type_T()
{
// Arrange
const string sql = @"
CREATE TABLE #SuperHero
(
SuperHeroId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
SuperHeroName NVARCHAR(120) NOT NULL
);
INSERT INTO #SuperHero ( SuperHeroName )
VALUES ( 'Superman' );
INSERT INTO #SuperHero ( SuperHeroName )
VALUES ( 'Batman' );
SELECT SuperHeroId,
SuperHeroName
FROM #SuperHero;
";
// Act
var superHeroesTask = Sequelocity.GetDatabaseCommand(ConnectionStringsNames.SqlServerConnectionString)
.SetCommandText(sql)
.ExecuteToListAsync <SuperHero>();
// Assert
Assert.IsInstanceOf <Task <List <SuperHero> > >(superHeroesTask);
Assert.That(superHeroesTask.Result.Count == 2);
Assert.That(superHeroesTask.Result[0].SuperHeroId == 1);
Assert.That(superHeroesTask.Result[0].SuperHeroName == "Superman");
Assert.That(superHeroesTask.Result[1].SuperHeroId == 2);
Assert.That(superHeroesTask.Result[1].SuperHeroName == "Batman");
}
public void Should_Call_The_DatabaseCommandUnhandledExceptionEventHandler()
{
// Arrange
bool wasUnhandledExceptionEventHandlerCalled = false;
Sequelocity.ConfigurationSettings.EventHandlers.DatabaseCommandUnhandledExceptionEventHandlers.Add((exception, command) =>
{
wasUnhandledExceptionEventHandlerCalled = true;
});
// Act
TestDelegate action = async() => await Sequelocity.GetDatabaseCommand(ConnectionStringsNames.SqlServerConnectionString)
.SetCommandText("asdf;lkj")
.ExecuteToMapAsync(record =>
{
var obj = new SuperHero
{
SuperHeroId = record.GetValue(0).ToLong(),
SuperHeroName = record.GetValue(1).ToString()
};
return(obj);
});
// Assert
Assert.Throws <System.Data.SqlClient.SqlException>(action);
Assert.IsTrue(wasUnhandledExceptionEventHandlerCalled);
}
public void Should_Return_The_First_Column_Of_The_First_Row_In_The_Result_Set_And_Convert_It_To_The_Type_Specified()
{
// Arrange
const string sql = @"
CREATE TABLE IF NOT EXISTS SuperHero
(
SuperHeroId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
SuperHeroName NVARCHAR(120) NOT NULL,
UNIQUE(SuperHeroName)
);
INSERT OR IGNORE INTO SuperHero VALUES ( NULL, 'Superman' );
SELECT SuperHeroId, /* This should be the only value returned from ExecuteScalar */
SuperHeroName
FROM SuperHero;
";
// Act
var superHeroId = Sequelocity.GetDatabaseCommand(ConnectionStringsNames.SqliteInMemoryDatabaseConnectionString)
.SetCommandText(sql)
.ExecuteScalar <long>(); // Generic version of the ExecuteScalar method
// Assert
Assert.That(superHeroId == 1);
}
public void Should_Null_The_DbCommand_By_Default()
{
// Arrange
const string sql = @"
DROP TABLE IF EXISTS SuperHero;
CREATE TEMPORARY TABLE SuperHero
(
SuperHeroId serial not null primary key,
SuperHeroName VARCHAR(120) NOT NULL
);
INSERT INTO SuperHero ( SuperHeroName )
VALUES ( 'Superman' );
INSERT INTO SuperHero ( SuperHeroName )
VALUES ( 'Batman' );
SELECT SuperHeroId,
SuperHeroName
FROM SuperHero;
";
var databaseCommand = Sequelocity.GetDatabaseCommand(ConnectionStringsNames.PostgreSQLConnectionString)
.SetCommandText(sql);
// Act
databaseCommand.ExecuteToDynamicObjectAsync()
.Wait(); // Block until the task completes.
// Assert
Assert.IsNull(databaseCommand.DbCommand);
}
public void Should_Be_Able_To_Specify_The_Table_Name()
{
// Arrange
const string createSchemaSql = @"
DROP TABLE IF EXISTS Person;
CREATE TABLE IF NOT EXISTS Person
(
CustomerId INT NOT NULL AUTO_INCREMENT,
FirstName NVARCHAR(120) NOT NULL,
LastName NVARCHAR(120) NOT NULL,
DateOfBirth DATETIME NOT NULL,
PRIMARY KEY ( CustomerId )
);
";
Sequelocity.GetDatabaseCommand(ConnectionStringsNames.MySqlConnectionString)
.SetCommandText(createSchemaSql)
.ExecuteNonQuery();
var customer = new Customer {
FirstName = "Clark", LastName = "Kent", DateOfBirth = DateTime.Parse("06/18/1938")
};
// Act
var customerId = Sequelocity.GetDatabaseCommand(ConnectionStringsNames.MySqlConnectionString)
.GenerateInsertForMySql(customer, "Person") // Specifying a table name of Person
.ExecuteScalar <int>();
// Assert
Assert.That(customerId == 1);
}
public void Should_Return_A_Type_Of_T()
{
// Arrange
const string sql = @"
DROP TEMPORARY TABLE IF EXISTS SuperHero;
CREATE TEMPORARY TABLE SuperHero
(
SuperHeroId INT NOT NULL AUTO_INCREMENT,
SuperHeroName VARCHAR(120) NOT NULL,
PRIMARY KEY ( SuperHeroId )
);
INSERT INTO SuperHero ( SuperHeroName )
VALUES ( 'Superman' );
INSERT INTO SuperHero ( SuperHeroName )
VALUES ( 'Batman' );
SELECT SuperHeroId,
SuperHeroName
FROM SuperHero;
";
// Act
var superHero = Sequelocity.GetDatabaseCommand(ConnectionStringsNames.MySqlConnectionString)
.SetCommandText(sql)
.ExecuteToObject <SuperHero>();
// Assert
Assert.NotNull(superHero);
Assert.That(superHero.SuperHeroId == 1);
Assert.That(superHero.SuperHeroName == "Superman");
}
public void Should_Throw_An_Exception_When_Passing_An_Anonymous_Object_And_Not_Specifying_A_TableName()
{
// Arrange
const string createSchemaSql = @"
DROP TABLE IF EXISTS Customer;
CREATE TABLE IF NOT EXISTS Customer
(
CustomerId INT NOT NULL AUTO_INCREMENT,
FirstName NVARCHAR(120) NOT NULL,
LastName NVARCHAR(120) NOT NULL,
DateOfBirth DATETIME NOT NULL,
PRIMARY KEY ( CustomerId )
);
";
Sequelocity.GetDatabaseCommand(ConnectionStringsNames.MySqlConnectionString)
.SetCommandText(createSchemaSql)
.ExecuteNonQuery();
var customer = new { FirstName = "Clark", LastName = "Kent", DateOfBirth = DateTime.Parse("06/18/1938") };
// Act
TestDelegate action = () => Sequelocity.GetDatabaseCommand(ConnectionStringsNames.MySqlConnectionString)
.GenerateInsertForMySql(customer)
.ExecuteScalar <int>();
// Assert
var exception = Assert.Catch <ArgumentNullException>(action);
Assert.That(exception.Message.Contains("The 'tableName' parameter must be provided when the object supplied is an anonymous type."));
}
public void Should_Keep_The_Database_Connection_Open_If_keepConnectionOpen_Parameter_Was_True()
{
// Arrange
const string sql = @"
DROP TEMPORARY TABLE IF EXISTS SuperHero;
CREATE TEMPORARY TABLE SuperHero
(
SuperHeroId INT NOT NULL AUTO_INCREMENT,
SuperHeroName VARCHAR(120) NOT NULL,
PRIMARY KEY ( SuperHeroId )
);
INSERT INTO SuperHero ( SuperHeroName )
VALUES ( 'Superman' );
INSERT INTO SuperHero ( SuperHeroName )
VALUES ( 'Batman' );
SELECT SuperHeroId,
SuperHeroName
FROM SuperHero;
";
var databaseCommand = Sequelocity.GetDatabaseCommand(ConnectionStringsNames.MySqlConnectionString)
.SetCommandText(sql);
// Act
databaseCommand.ExecuteToObject <SuperHero>(true);
// Assert
Assert.That(databaseCommand.DbCommand.Connection.State == ConnectionState.Open);
// Cleanup
databaseCommand.Dispose();
}
public void Should_Return_The_First_Column_Of_The_First_Row_In_The_Result_Set_And_Convert_It_To_The_Type_Specified()
{
// Arrange
const string sql = @"
CREATE TABLE #SuperHero
(
SuperHeroId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
SuperHeroName NVARCHAR(120) NOT NULL
);
INSERT INTO #SuperHero ( SuperHeroName )
VALUES ( 'Superman' );
SELECT SuperHeroId, /* This should be the only value returned from ExecuteScalarAsync */
SuperHeroName
FROM #SuperHero;
";
// Act
var superHeroIdTask = Sequelocity.GetDatabaseCommand(ConnectionStringsNames.SqlServerConnectionString)
.SetCommandText(sql)
.ExecuteScalarAsync <long>(); // Generic version of the ExecuteScalarAsync method
// Assert
Assert.IsInstanceOf <Task <long> >(superHeroIdTask);
Assert.That(superHeroIdTask.Result == 1);
}