private static string AddPGLocksFilterParametersAndGetFilterExpression(DatabaseCommand command, PostgresAdvisoryLockKey key)
{
// From https://www.postgresql.org/docs/12/view-pg-locks.html
// Advisory locks can be acquired on keys consisting of either a single bigint value or two integer values.
// A bigint key is displayed with its high-order half in the classid column, its low-order half in the objid column,
// and objsubid equal to 1. The original bigint value can be reassembled with the expression (classid::bigint << 32) | objid::bigint.
// Integer keys are displayed with the first key in the classid column, the second key in the objid column, and objsubid equal to 2.
string classIdParameter, objIdParameter, objSubId;
if (key.HasSingleKey)
{
// since Postgres seems to lack unchecked int conversions, it is simpler to just generate extra
// parameters to carry the split key info in this case
var(keyUpper32, keyLower32) = key.Keys;
command.AddParameter(classIdParameter = "keyUpper32", keyUpper32, DbType.Int32);
command.AddParameter(objIdParameter = "keyLower32", keyLower32, DbType.Int32);
objSubId = "1";
}
else
{
classIdParameter = "key1";
objIdParameter = "key2";
objSubId = "2";
}
return($"(l.classid = @{classIdParameter} AND l.objid = @{objIdParameter} AND l.objsubid = {objSubId})");
}
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 void Mock_FormatedCommandText_Test()
{
var conn = new MockDbConnection();
conn.Mocks
.When(c => c.CommandText.Contains("SELECT"))
.ReturnsScalar(14);
using (var cmd = new DatabaseCommand(conn))
{
cmd.CommandText = @"SELECT *
FROM EMP
WHERE ID = @Id
AND ENAME = @Name
AND HIREDATE = @HireDate";
cmd.AddParameter("@Id", 123);
cmd.AddParameter("@Name", "Denis");
cmd.AddParameter("@HireDate", new DateTime(2019, 05, 03));
var formatedCommandAsText = cmd.Formatted.CommandAsText;
var formatedCommandAsVariables = cmd.Formatted.CommandAsVariables;
Assert.IsTrue(formatedCommandAsText.Contains("ID = 123"));
Assert.IsTrue(formatedCommandAsText.Contains("ENAME = 'Denis'"));
Assert.IsTrue(formatedCommandAsText.Contains("HIREDATE = '2019-05-03'"));
Assert.IsTrue(formatedCommandAsVariables.Contains("DECLARE @Id AS INT = 123"));
Assert.IsTrue(formatedCommandAsVariables.Contains("DECLARE @Name AS VARCHAR"));
Assert.IsTrue(formatedCommandAsVariables.Contains("DECLARE @HireDate AS DATETIME = '2019-05-03'"));
}
}
private static string AddKeyParametersAndGetKeyArguments(DatabaseCommand command, PostgresAdvisoryLockKey key)
{
if (key.HasSingleKey)
{
command.AddParameter("key", key.Key, DbType.Int64);
return("@key");
}
else
{
var(key1, key2) = key.Keys;
command.AddParameter("key1", key1, DbType.Int32);
command.AddParameter("key2", key2, DbType.Int32);
return("@key1, @key2");
}
}
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 GetFormattedAsText_Parameters_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.CommandText = " SELECT *, @MyGuid FROM EMP WHERE EMPNO = @EmpNo AND ENAME LIKE @Ename AND HIREDATE > @Hire AND COMM = @Comm ";
cmd.AddParameter("@EmpNo", 7369); // Parameter normal
cmd.AddParameter("@ENAME", "%SM%"); // Parameter in Upper Case
cmd.AddParameter("Hire", new DateTime(1970, 05, 04, 14, 15, 16)); // Parameter without @
cmd.AddParameter("@Comm", null); // Parameter NULL
cmd.AddParameter("@MyGuid", new Guid("2fff1b89-b5f9-4a33-ac5b-a3ffee3e8b82")); // Parameter GUID
string formatted = cmd.Formatted.CommandAsText;
Assert.AreEqual(" SELECT *, '2fff1b89-b5f9-4a33-ac5b-a3ffee3e8b82' FROM EMP WHERE EMPNO = 7369 AND ENAME LIKE '%SM%' AND HIREDATE > '1970-05-04 14:15:16' AND COMM = NULL ", formatted);
}
}
public void GetFormattedAsHtml_Parameters_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.CommandText.AppendLine(" SELECT * FROM EMP ");
cmd.CommandText.AppendLine(" WHERE EMPNO = @EmpNo AND ENAME LIKE @Ename AND HIREDATE > @Hire AND COMM = @Comm ");
cmd.AddParameter("@EmpNo", 7369); // Parameter normal
cmd.AddParameter("@ENAME", "%SM%"); // Parameter in Upper Case
cmd.AddParameter("Hire", new DateTime(1970, 05, 04, 14, 15, 16)); // Parameter without @
cmd.AddParameter("@Comm", null); // Parameter NULL
string formatted = cmd.Formatted.CommandAsHtml;
Assert.AreEqual(formatted, @" <span style=""color: #33f; font-weight: bold;"">SELECT</span> * <span style=""color: #33f; font-weight: bold;"">FROM</span> EMP <br/> <span style=""color: #33f; font-weight: bold;"">WHERE</span> EMPNO = <span style=""color: #FF3F00;"">7369</span> <span style=""color: #33f; font-weight: bold;"">AND</span> ENAME <span style=""color: #33f; font-weight: bold;"">LIKE</span> <span style=""color: #FF3F00;"">'%SM%'</span> <span style=""color: #33f; font-weight: bold;"">AND</span> HIREDATE > <span style=""color: #FF3F00;"">'<span style=""color: #FF3F00;"">1970</span><span style=""color: #FF3F00;"">-05</span><span style=""color: #FF3F00;"">-04</span> <span style=""color: #FF3F00;"">14</span>:<span style=""color: #FF3F00;"">15</span>:<span style=""color: #FF3F00;"">16</span>'</span> <span style=""color: #33f; font-weight: bold;"">AND</span> COMM = <span style=""color: #33f; font-weight: bold;"">NULL</span> <br/>");
}
}
public void Extension_ParameterNullable_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.AddParameter("@MyParam", null);
Assert.AreEqual(DBNull.Value, cmd.Parameters[0].Value);
}
}
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);
}
}
private void AddCommonParameters(DatabaseCommand command, string semaphoreName, TimeoutValue?timeout = null, string?markerTableName = null, string?ticketLockName = null)
{
command.AddParameter(SemaphoreNameParameter, semaphoreName);
command.AddParameter(MaxCountParameter, this.MaxCount);
if (timeout.TryGetValue(out var timeoutValue))
{
command.AddParameter(TimeoutMillisParameter, timeoutValue.InMilliseconds);
}
command.AddParameter(ResultCodeParameter, type: DbType.Int32, direction: ParameterDirection.Output);
var ticket = command.AddParameter(TicketLockNameParameter, ticketLockName, type: DbType.String);
if (ticketLockName == null)
{
ticket.Direction = ParameterDirection.Output;
}
const int MaxOutputStringLength = 8000; // plenty long enough
ticket.Size = MaxOutputStringLength;
var markerTable = command.AddParameter(MarkerTableNameParameter, markerTableName, type: DbType.String);
if (markerTableName == null)
{
markerTable.Direction = ParameterDirection.Output;
}
markerTable.Size = MaxOutputStringLength;
}
public void GetFormattedAsVariables_Parameters_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.TagWith("Sample");
cmd.CommandText.AppendLine(" SET ANSI_NULLS OFF");
cmd.CommandText.AppendLine(" SELECT * FROM EMP");
cmd.CommandText.AppendLine(" WHERE EMPNO = @EmpNo");
cmd.CommandText.AppendLine(" AND ENAME LIKE @Ename");
cmd.CommandText.AppendLine(" AND HIREDATE > @Hire");
cmd.CommandText.AppendLine(" AND COMM = @Comm");
cmd.AddParameter("@EmpNo", 7369); // INT
cmd.AddParameter("@ENAME", "%SM%"); // VARCHAR(6)
cmd.AddParameter("Hire", new DateTime(1970, 05, 04, 14, 15, 16)); // DATETIME
cmd.AddParameter("@Comm", null, System.Data.DbType.Currency); // VARCHAR
cmd.AddParameter("@MyBool", true); // BIT
string formatted = cmd.Formatted.CommandAsVariables;
Assert.AreEqual(formatted,
@"-- Sample
DECLARE @Hire AS DATETIME = '1970-05-04 14:15:16'
DECLARE @MyBool AS BIT = 1
DECLARE @ENAME AS VARCHAR(4) = '%SM%'
DECLARE @EmpNo AS INT = 7369
DECLARE @Comm AS VARCHAR(4000) = NULL
SET ANSI_NULLS OFF
SELECT * FROM EMP
WHERE EMPNO = @EmpNo
AND ENAME LIKE @Ename
AND HIREDATE > @Hire
AND COMM = @Comm
");
}
}
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 Mock_ExecuteNonQuery_Test()
{
var conn = new MockDbConnection();
conn.Mocks
.When(c => c.CommandText.Contains("INSERT"))
.ReturnsScalar(14);
using (var cmd = new DatabaseCommand(conn))
{
cmd.CommandText.AppendLine("INSERT ...");
cmd.AddParameter("@ID", 1);
var result = cmd.ExecuteNonQuery();
Assert.AreEqual(14, result);
}
}
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 ExecuteScalarWithObjectParameter_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(new { EmpNo = 7369 });
object data = cmd.ExecuteScalar();
Assert.AreEqual("SMITH", data);
}
}
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 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 ExecuteScalarWithNullableObjectParameter_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
// Add manual parameter
cmd.AddParameter(new
{
EmpNo = 7369,
DeptNo = default(int?)
});
Assert.AreEqual(7369, cmd.Parameters["@EmpNo"].Value);
Assert.AreEqual(DBNull.Value, cmd.Parameters["@DeptNo"].Value);
}
}
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 ExecuteScalar_ActionBefore_ChangeParameter_Test()
{
bool isPassed = false;
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText.AppendLine(" SELECT SAL FROM EMP WHERE EMPNO = @EmployeeID");
cmd.AddParameter("@EmployeeID", 1234);
cmd.ActionBeforeExecution = (command) =>
{
cmd.Parameters["@EmployeeID"].Value = 7369;
isPassed = true;
};
var salary = cmd.ExecuteScalar <decimal>();
Assert.IsTrue(isPassed);
Assert.AreEqual(800, salary); // Check Salary for 7369 (and not 1234)
}
}
public void ExecuteScalarWithAnonymousOnlyParameters_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";
// 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);
}
}
