public void ExecuteScalarWithAnonymousParameters_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText = @" SELECT ENAME
FROM EMP
WHERE EMPNO = @EmpNo
AND HIREDATE = @HireDate
AND JOB = @Job
AND 1 = @NotDeleted";
cmd.AddParameter("@EMPNO", 1234); // Parameter in Upper Case
cmd.AddParameter("HireDate", new DateTime(1980, 1, 1)); // Parameter without @
cmd.AddParameter("@Job", "FAKE"); // Parameter in normal mode
cmd.AddParameter("@NotDeleted", true); // Parameter not replaced
// Replace previous values wiht these new propery values
cmd.AddParameter(new
{
EmpNo = 7369,
HireDate = new DateTime(1980, 12, 17),
Job = "CLERK"
});
object data = cmd.ExecuteScalar();
Assert.AreEqual("SMITH", data);
}
}
public int Insert(DataAspect aspect, IEnumerable <AspectMemberValue> values, out object identityValue)
{
using (IDbConnection conn = _dialect.CreateConnection(_ConnectionString))
{
conn.Open();
try
{
bool hasIdentity;
DatabaseCommand cmd = _dialect.CreateInsert(aspect, values, out hasIdentity);
if (hasIdentity)
{
identityValue = cmd.ExecuteScalar(conn);
return(1);
}
else
{
identityValue = null;
return(cmd.ExecuteNonQuery(conn));
}
}
finally
{
conn.Close();
}
}
}
public void ExecuteScalarWithPrepare_Test()
{
var sample = new Dictionary <int, string>()
{
{ 7369, "SMITH" },
{ 7499, "ALLEN" }
};
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText = @"SELECT ENAME
FROM EMP
WHERE EMPNO = @EmpNo ";
cmd.AddParameter("@EMPNO", 0, System.Data.DbType.Int32);
cmd.Prepare();
foreach (var item in sample)
{
cmd.Parameters["@EMPNO"].Value = item.Key;
var ename = cmd.ExecuteScalar <string>();
Assert.AreEqual(item.Value, ename);
}
}
}
public void Constructor_Connection_CommandText_Test()
{
using (var cmd = new DatabaseCommand(_connection, "SELECT COUNT(*) FROM EMP"))
{
Assert.AreEqual("SELECT COUNT(*) FROM EMP", cmd.CommandText);
Assert.AreEqual(14, cmd.ExecuteScalar());
}
}
public static int GetEmployeesCount(DbTransaction currentTransaction)
{
using (var cmd = new DatabaseCommand(currentTransaction))
{
cmd.CommandText.AppendLine(" SELECT COUNT(*) FROM EMP ");
return(cmd.ExecuteScalar <int>());
}
}
public void DbCmd_ExecuteScalar_Int()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.CommandText = "SELECT TOP 1 EMPNO FROM EMP";
var empno = cmd.ExecuteScalar <int>();
}
}
public void Constructor_Connection_Timeout_Test()
{
using (var cmd = new DatabaseCommand(_connection, 33))
{
cmd.CommandText = "SELECT COUNT(*) FROM EMP";
Assert.AreEqual(14, cmd.ExecuteScalar());
Assert.AreEqual(33, cmd.CommandTimeout);
}
}
public void Constructor_Transaction_CommandText_Timeout_Test()
{
var transaction = _connection.BeginTransaction();
using (var cmd = new DatabaseCommand(transaction, "SELECT COUNT(*) FROM EMP", 33))
{
Assert.AreEqual(14, cmd.ExecuteScalar());
}
}
public void ExecuteScalarDynamic_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText = " SELECT COUNT(*) FROM EMP ";
var count = cmd.ExecuteScalar <dynamic>();
Assert.AreEqual(14, count);
}
}
public void ExecuteScalar_NoDataNullableTyped_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText = "SELECT EMPNO FROM EMP WHERE EMPNO = 99999";
int?empno = cmd.ExecuteScalar <int?>();
Assert.AreEqual(null, empno);
}
}
public void ExecuteScalar_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText = "SELECT COUNT(*) FROM EMP";
object data = cmd.ExecuteScalar();
Assert.AreEqual(14, data);
}
}
public void Constructor_ConnectionAndTransaction_Test()
{
var transaction = _connection.BeginTransaction();
using (var cmd = new DatabaseCommand(_connection, transaction, 0))
{
cmd.CommandText = "SELECT COUNT(*) FROM EMP";
Assert.AreEqual(14, cmd.ExecuteScalar());
}
}
/// <summary>
/// To get identifiers of saved instruments.
/// </summary>
/// <returns>IDs securities.</returns>
public IEnumerable <string> GetSecurityIds()
{
if (_readSecurityIds == null)
{
return(this.Select(s => s.Id));
}
var str = _readSecurityIds.ExecuteScalar <string>(new SerializationItemCollection());
return(str.SplitByComma(",", true));
}
public void ExecuteScalarWhereNoDataFound_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText = " SELECT COMM FROM EMP WHERE EMPNO = 99999 ";
int?data = cmd.ExecuteScalar <int?>();
Assert.AreEqual(null, data);
}
}
public void RetryWhenNoDeadLock_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Retry.SetDefaultCriteriaToRetry(RetryDefaultCriteria.SqlServer_DeadLock);
cmd.CommandText = "SELECT COUNT(*) FROM EMP";
int count = cmd.ExecuteScalar <int>();
Assert.AreEqual(14, count);
}
}
public void Should_Handle_Generating_Inserts_For_A_Dynamic_Object()
{
// Arrange
const string createSchemaSql = @"
CREATE TABLE IF NOT EXISTS Customer
(
CustomerId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
FirstName NVARCHAR(120) NOT NULL,
LastName NVARCHAR(120) NOT NULL,
DateOfBirth DATETIME NOT NULL
);";
var dbConnection = Sequelocity.CreateDbConnection(ConnectionStringsNames.SqliteInMemoryDatabaseConnectionString);
new DatabaseCommand(dbConnection)
.SetCommandText(createSchemaSql)
.ExecuteNonQuery(true);
dynamic newCustomer = new ExpandoObject();
newCustomer.FirstName = "Clark";
newCustomer.LastName = "Kent";
newCustomer.DateOfBirth = DateTime.Parse("06/18/1938");
// Act
var databaseCommand = new DatabaseCommand(dbConnection);
databaseCommand = DatabaseCommandExtensions.GenerateInsertForSQLite(databaseCommand, newCustomer, "[Customer]");
var customerId = databaseCommand
.ExecuteScalar(true)
.ToInt();
const string selectCustomerQuery = @"
SELECT CustomerId,
FirstName,
LastName,
DateOfBirth
FROM Customer;
";
var customer = new DatabaseCommand(dbConnection)
.SetCommandText(selectCustomerQuery)
.ExecuteToObject <Customer>();
// Assert
Assert.That(customerId == 1);
Assert.That(customer.CustomerId == 1);
Assert.That(customer.FirstName == newCustomer.FirstName);
Assert.That(customer.LastName == newCustomer.LastName);
Assert.That(customer.DateOfBirth == newCustomer.DateOfBirth);
}
public void Mock_ContainsSql_StringScalar_Null_Test()
{
var conn = new MockDbConnection();
conn.Mocks
.When(c => c.CommandText.Contains("SELECT"))
.ReturnsScalar((string)null);
using (var cmd = new DatabaseCommand(conn))
{
cmd.CommandText.AppendLine("SELECT ...");
var result = cmd.ExecuteScalar <string>();
Assert.AreEqual(null, result);
}
}
public void Mock_ContainsSql_IntegerScalar_DbNull_Test()
{
var conn = new MockDbConnection();
conn.Mocks
.When(c => c.CommandText.Contains("SELECT"))
.ReturnsScalar(System.DBNull.Value);
using (var cmd = new DatabaseCommand(conn))
{
cmd.CommandText.AppendLine("SELECT ...");
var result = cmd.ExecuteScalar <int>();
Assert.AreEqual(0, result);
}
}
public void ExecuteScalarWithDbParameter_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText = @" SELECT ENAME
FROM EMP
WHERE EMPNO = @EmpNo";
// Add manual parameter
cmd.Parameters.Add(new SqlParameter("@EmpNo", 7369));
object data = cmd.ExecuteScalar();
Assert.AreEqual("SMITH", data);
}
}
public void Mock_ContainsSql_IntegerScalar_Test()
{
var conn = new MockDbConnection();
conn.Mocks
.When(c => c.CommandText.Contains("SELECT"))
.ReturnsScalar(14);
using (var cmd = new DatabaseCommand(conn))
{
cmd.CommandText.AppendLine("SELECT * FROM EMP WHERE ID = @ID");
cmd.AddParameter("@ID", 1);
var result = cmd.ExecuteScalar <int>();
Assert.AreEqual(14, result);
}
}
public void ExecuteScalarWithSimpleParameters_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText.AppendLine(" SELECT ENAME ")
.AppendLine(" FROM EMP ");
// Simple value are not autorized
cmd.AddParameter(123);
object data = cmd.ExecuteScalar();
Assert.Fail();
}
}
public void RetryUsingOptionsWhenNoDeadLock_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Retry.Activate(options =>
{
options.SetDefaultCriteriaToRetry(RetryDefaultCriteria.SqlServer_DeadLock);
options.MillisecondsBetweenTwoRetries = 1000;
options.NumberOfRetriesBeforeFailed = 3;
});
cmd.CommandText = "SELECT COUNT(*) FROM EMP";
int count = cmd.ExecuteScalar <int>();
Assert.AreEqual(14, count);
}
}
public void ExecuteScalarWithParameterTyped_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText = @" SELECT ENAME
FROM EMP
WHERE EMPNO = @EmpNo";
// Add manual parameter
cmd.AddParameter("@EmpNo", 7369, System.Data.DbType.Int32, 4);
object data = cmd.ExecuteScalar();
Assert.AreEqual("SMITH", data);
}
}
public void Mock_ContainsSql_And_Parameter_Test()
{
var conn = new MockDbConnection();
conn.Mocks
.When(c => c.CommandText.Contains("SELECT") &&
c.Parameters.Any(p => p.ParameterName == "@ID"))
.ReturnsScalar(14);
using (var cmd = new DatabaseCommand(conn))
{
cmd.CommandText.AppendLine("SELECT ...");
cmd.AddParameter("@ID", 1);
var result = cmd.ExecuteScalar <int>();
Assert.AreEqual(14, result);
}
}
public void ExecuteNonQuery_TransactionForTwoIncludedCommands_Test()
{
using (var cmd1 = new DatabaseCommand(_connection))
{
cmd1.Log = Console.WriteLine;
cmd1.CommandText.AppendLine(" DELETE FROM EMP ");
cmd1.TransactionBegin();
cmd1.ExecuteNonQuery();
using (var cmd2 = new DatabaseCommand(cmd1.Transaction))
{
cmd2.CommandText.AppendLine(" SELECT COUNT(*) FROM EMP ");
int count = cmd2.ExecuteScalar <int>();
}
cmd1.TransactionRollback();
}
}
public void ExecuteScalarWithNullParameter_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText = @"SET ANSI_NULLS OFF
SELECT COUNT(*)
FROM EMP
WHERE COMM = @Comm
SET ANSI_NULLS ON";
cmd.AddParameter("@Comm", null);
int count = cmd.ExecuteScalar <int>();
Assert.AreEqual(10, count);
}
}
public void Mock_ThrowGenericException_Test()
{
var conn = new MockDbConnection();
conn.Mocks
.WhenAny()
.ThrowsException <Exception>();
using (var cmd = new DatabaseCommand(conn))
{
var exception = Assert.ThrowsException <Exception>(() =>
{
cmd.CommandText.AppendLine("SELECT ...");
cmd.ExecuteScalar <int>();
});
Assert.IsNotNull(exception);
}
}
public void Mock_ContainsSql_NullableIntegerScalar_DbNull_Test()
{
// Issue #27 - https://github.com/Apps72/DbMocker/issues/27
// https://docs.microsoft.com/en-us/dotnet/api/system.data.common.dbcommand.executescalar
// If the value in the database is null, the query returns DBNull.Value.
var conn = new MockDbConnection();
conn.Mocks
.When(c => c.CommandText.Contains("SELECT"))
.ReturnsScalar(DBNull.Value);
using (var cmd = new DatabaseCommand(conn))
{
cmd.CommandText.AppendLine("SELECT ...");
var result = cmd.ExecuteScalar();
Assert.AreEqual(DBNull.Value, result);
}
}
public void ExecuteScalar_ActionAfter_Test()
{
bool isPassed = false;
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText.AppendLine(" SELECT COUNT(*) FROM EMP ");
cmd.ActionAfterExecution = (command, tables) =>
{
isPassed = true;
};
int count = cmd.ExecuteScalar <int>();
Assert.IsTrue(isPassed);
Assert.AreEqual(14, count); // Check new Count
}
}
public void ExecuteScalarWithParameter_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText = @"SELECT ENAME
FROM EMP
WHERE EMPNO = @EmpNo
AND HIREDATE = @HireDate
AND JOB = @Job";
cmd.AddParameter("@EMPNO", 7369); // Parameter in Upper Case
cmd.AddParameter("HireDate", new DateTime(1980, 12, 17)); // Parameter without @
cmd.AddParameter("@Job", "CLERK"); // Parameter in normal mode
object data = cmd.ExecuteScalar();
Assert.AreEqual("SMITH", data);
}
}
public void Should_Handle_Generating_Inserts_For_A_Dynamic_Object()
{
// Arrange
const string createSchemaSql = @"
CREATE TABLE IF NOT EXISTS Customer
(
CustomerId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
FirstName NVARCHAR(120) NOT NULL,
LastName NVARCHAR(120) NOT NULL,
DateOfBirth DATETIME NOT NULL
);";
var dbConnection = Sequelocity.CreateDbConnection( ConnectionStringsNames.SqliteInMemoryDatabaseConnectionString );
new DatabaseCommand( dbConnection )
.SetCommandText( createSchemaSql )
.ExecuteNonQuery( true );
dynamic newCustomer = new ExpandoObject();
newCustomer.FirstName = "Clark";
newCustomer.LastName = "Kent";
newCustomer.DateOfBirth = DateTime.Parse( "06/18/1938" );
// Act
var databaseCommand = new DatabaseCommand( dbConnection );
databaseCommand = DatabaseCommandExtensions.GenerateInsertForSQLite( databaseCommand, newCustomer, "[Customer]" );
var customerId = databaseCommand
.ExecuteScalar( true )
.ToInt();
const string selectCustomerQuery = @"
SELECT CustomerId,
FirstName,
LastName,
DateOfBirth
FROM Customer;
";
var customer = new DatabaseCommand( dbConnection )
.SetCommandText( selectCustomerQuery )
.ExecuteToObject<Customer>();
// Assert
Assert.That( customerId == 1 );
Assert.That( customer.CustomerId == 1 );
Assert.That( customer.FirstName == newCustomer.FirstName );
Assert.That( customer.LastName == newCustomer.LastName );
Assert.That( customer.DateOfBirth == newCustomer.DateOfBirth );
}
