public void Test_Generic_BuildQueryWithOutputs()
{
RunTestOnAllDBTypes(delegate(DataBaseType type)
{
var objectToSql = new Services.ObjectToSql(type);
var sql = string.Empty;
if (type == DataBaseType.Sqlite || type == DataBaseType.MySql)
{
EnsureExpectedExceptionIsThrown <NotImplementedException>(() =>
objectToSql.BuildQueryWithOutputs <EmployeeWithPrimaryKeySqlColumn>(ActionType,
"Employee", a => a.PrimaryKey)
);
return;
}
else
{
sql = objectToSql.BuildQueryWithOutputs <EmployeeWithPrimaryKeySqlColumn>(ActionType,
"Employee", a => a.PrimaryKey);
}
var value = $"";
switch (type)
{
case DataBaseType.SqlServer:
value = $"INSERT INTO Employee ([FirstName],[LastName],[PrimaryKey]) OUTPUT INSERTED.[PrimaryKey] {Environment.NewLine} VALUES (@FirstName,@LastName,@PrimaryKey)";
break;
default:
throw new ArgumentOutOfRangeException(nameof(type), type, null);
}
Assert.AreEqual(sql, value);
});
}
public void Test_MultiThreadBuildQuery()
{
var instance = new BugReadOnlyBreakUpsertStatement();
var obj2Sql = new Services.ObjectToSql(DataBaseType.SqlServer);
for (var i2 = 0; i2 < 200; i2++)
{
Thread[] threads = new Thread[6];
for (int i = 0; i < threads.Length; i++)
{
threads[i] = new Thread(delegate(object sdo)
{
var quwery = obj2Sql.BuildQuery(ActionType.Insert, instance);
var parameters = obj2Sql.BuildDbParameterList(instance, (s, o) => new SqlParameter(s, o));
});
}
foreach (Thread thread in threads)
{
thread.Start();
}
foreach (Thread thread in threads)
{
thread.Join();
}
}
}
public void Test_Generic_BuildInsertQueryWithOutputs()
{
var objectToSql = new Services.ObjectToSql(DataBaseType.SqlServer);
var sql = objectToSql.BuildQueryWithOutputs <Employee>(ActionType, "Employee", e => e.FirstName);
Assert.AreEqual(sql, $"INSERT INTO Employee ([FirstName],[LastName]) OUTPUT INSERTED.[FirstName] {Environment.NewLine} VALUES (@FirstName,@LastName)");
}
public void Test_Generic_Build_Upsert_Query()
{
var objectToSql = new Services.ObjectToSql(DataBaseType.SqlServer);
Assert.That(() => objectToSql.BuildQuery <Employee>(ActionType),
Throws.Exception
.TypeOf <MissingKeyAttributeException>());
}
public void Test_Generic_Build_Insert_Query_Uses_Type_Name_When_Table_Name_Is_Not_Specified()
{
RunTestOnAllDBTypes(delegate(DataBaseType type)
{
var objectToSql = new Services.ObjectToSql(type);
var sql = objectToSql.BuildQuery <Employee>(ActionType);
Assert.AreEqual(sql, Employee.ToSql(ActionType, type));
});
}
public void Test_Generic_BuildInsertQuery_Uses_Mapped_Column_Name_Instead_Of_PropertyName()
{
RunTestOnAllDBTypes(delegate(DataBaseType type)
{
var objectToSql = new Services.ObjectToSql(type);
var sql = objectToSql.BuildQuery <EmployeeWithMappedColumnDataAnnotation>(ActionType);
Assert.AreEqual(sql, EmployeeWithMappedColumnDataAnnotation.ToSql(ActionType, type));
});
}
public void Test_Generic_BuildDeleteQuery_Includes_Where_Clause_With_Multiple_Primary_Column()
{
RunTestOnAllDBTypes(delegate(DataBaseType type)
{
var objectToSql = new Services.ObjectToSql(type);
var sql = objectToSql.BuildQuery <EmployeeWithManyPrimaryKeyDataAnnotation>(ActionType);
Assert.AreEqual(sql, EmployeeWithManyPrimaryKeyDataAnnotation.ToSql(ActionType, type));
});
}
public void Test_Generic_Ensure_Table_Attribute_Name_Is_Used()
{
RunTestOnAllDBTypes(delegate(DataBaseType type)
{
var objectToSql = new Services.ObjectToSql(type);
var sql = objectToSql.BuildQuery <EmployeeWithTableAttribute>(ActionType);
Assert.AreEqual(sql, EmployeeWithTableAttribute.ToSql(ActionType, type));
});
}
public void Test_Generic_BuildUpdateQuery_Doesnt_Include_Ignored_Column()
{
RunTestOnAllDBTypes(delegate(DataBaseType type)
{
var objectToSql = new Services.ObjectToSql(type);
var sql = objectToSql.BuildQuery <EmployeeWithIgnorePropertyAndKeySqlColumn>(ActionType, nameof(EmployeeWithIgnorePropertyAndKeySqlColumn));
Assert.AreEqual(sql, EmployeeWithIgnorePropertyAndKeySqlColumn.ToSql(ActionType, type));
});
}
public void Test_Generic_BuildQuery_Ensure_Override_Keys_Is_Used()
{
RunTestOnAllDBTypes(delegate(DataBaseType type)
{
var objectToSql = new Services.ObjectToSql(type);
var sql = objectToSql.BuildQuery <EmployeeWithIdentityKeySqlColumn>(ActionType, nameof(EmployeeWithIdentityKeySqlColumn), column => column.IdentityKey);
Assert.AreEqual(sql, EmployeeWithIdentityKeySqlColumn.ToSql(ActionType, type));
});
}
public void Test_Generic_BuildUpdateQuery_Includes_Where_Clause_With_Primary_Column()
{
RunTestOnAllDBTypes(delegate(DataBaseType type)
{
var objectToSql = new Services.ObjectToSql(type);
var sql = objectToSql.BuildQuery <EmployeeWithPrimaryKeySqlColumn>(ActionType, nameof(EmployeeWithPrimaryKeySqlColumn));
Assert.AreEqual(sql, EmployeeWithPrimaryKeySqlColumn.ToSql(ActionType, type));
});
}
public void Test_Generic_BuildInsertQuery_Does_Try_To_Insert_PrimaryKey_Column()
{
RunTestOnAllDBTypes(delegate(DataBaseType type)
{
var objectToSql = new Services.ObjectToSql(type);
var sql = objectToSql.BuildQuery <EmployeeWithPrimaryKeySqlColumn>(ActionType);
Assert.AreEqual(sql, EmployeeWithPrimaryKeySqlColumn.ToSql(ActionType, type));
});
}
public void Test_Generic_BuildQuery()
{
RunTestOnAllDBTypes(delegate(DataBaseType type)
{
var objectToSql = new Services.ObjectToSql(type);
var sql = objectToSql.BuildQuery(ActionType, new EmployeeWithIdentityKeySqlColumn());
Assert.AreEqual(sql, EmployeeWithIdentityKeySqlColumn.ToSql(ActionType, type));
});
}
public void Test_Generic_BuildUpdateQuery_Uses_MappedColumn_Name_Instead_Of_PropertyName()
{
RunTestOnAllDBTypes(delegate(DataBaseType type)
{
var objectToSql = new Services.ObjectToSql(type);
var sql = objectToSql.BuildQuery <EmployeeWithMappedColumnAndPrimaryKeySqlColumn>(ActionType, nameof(EmployeeWithMappedColumnAndPrimaryKeySqlColumn));
Assert.AreEqual(sql, EmployeeWithMappedColumnAndPrimaryKeySqlColumn.ToSql(ActionType, type));
});
}
public void Test_BuildQuery_Generic_As_Object_Overload_Throws_With_Key_Attribute_Decorated()
{
object employee = new Employee();
var objectToSql = new Services.ObjectToSql(DataBaseType.SqlServer);
Assert.That(() => objectToSql.BuildQuery(ActionType, employee),
Throws.Exception
.TypeOf <MissingKeyAttributeException>());
}
public void Test_Generic_As_Object_Build_Insert_Query()
{
RunTestOnAllDBTypes(delegate(DataBaseType type)
{
object employee = new Employee();
var objectToSql = new Services.ObjectToSql(type);
var sql = objectToSql.BuildQuery(ActionType, employee);
Assert.AreEqual(sql, Employee.ToSql(ActionType, type));
});
}
public void Test_Generic_BuildDeleteQuery_Ensure_MissingKeyException_Is_Thrown_()
{
RunTestOnAllDBTypes(delegate(DataBaseType type)
{
var objectToSql = new Services.ObjectToSql(type);
Assert.That(() => objectToSql.BuildQuery <EmployeeWithMappedColumnSqlColumn>(ActionType),
Throws.Exception
.TypeOf <MissingKeyAttributeException>());
});
}
public void Test_All_Build_Query_Overloads_Throws_ArgumentOutOfRange_When_Passing_Null_Parameter()
{
var objectToSql = new Services.ObjectToSql(DataBaseType.SqlServer, false);
// var isInvalid = System.Enum.TryParse<ActionType>("Insert",out var invalidEnum);
var invalidEnum = (ActionType)70;
Assert.That(() => objectToSql.BuildQuery(invalidEnum, null),
Throws.Exception
.TypeOf <ArgumentNullException>());
}
public void Test_Generic_BuildQueryWithOutputs_Uses_MappedColumn_Name_Instead_Of_PropertyName()
{
RunTestOnAllDBTypes(delegate(DataBaseType type)
{
var objectToSql = new Services.ObjectToSql(type);
var sql = string.Empty;
if (type == DataBaseType.Sqlite || type == DataBaseType.MySql)
{
EnsureExpectedExceptionIsThrown <NotImplementedException>(() =>
objectToSql.BuildQueryWithOutputs <EmployeeWithMappedColumnSqlColumn>(ActionType,
"Employee", e => e.FirstName)
);
return;
}
else
{
sql = objectToSql.BuildQueryWithOutputs <EmployeeWithMappedColumnSqlColumn>(ActionType,
"Employee", e => e.FirstName);
}
var expected = "";
switch (type)
{
case DataBaseType.SqlServer:
expected = $"INSERT INTO Employee ([FirstName2],[LastName]) OUTPUT INSERTED.[FirstName2] {Environment.NewLine} VALUES (@FirstName,@LastName)";
break;
case DataBaseType.MySql:
break;
case DataBaseType.Sqlite:
expected = $"INSERT INTO Employee ([FirstName2],[LastName]) OUTPUT INSERTED.[FirstName2] {Environment.NewLine} VALUES (@FirstName,@LastName)";
break;
case DataBaseType.Oracle:
break;
case DataBaseType.Oledb:
break;
case DataBaseType.Access95:
break;
case DataBaseType.Odbc:
break;
default:
throw new ArgumentOutOfRangeException(nameof(type), type, null);
}
Assert.AreEqual(sql, expected);
});
}
public void Test_BuildDbParameterList_Contains_Accurate_Values()
{
var employee = new Employee()
{
LastName = "John", FirstName = "Doe"
};
var objectToSql = new Services.ObjectToSql(DataBaseType.SqlServer);
var parameters = objectToSql.BuildDbParameterList(employee, (s, o) => new SqlParameter(s, o), null, null, null);
Assert.AreEqual(parameters.First().Value, "Doe");
Assert.AreEqual(parameters.Last().Value, "John");
Assert.AreEqual(parameters.Count, 2);
}
public void Test_Object2Sql_ConvertSQLToReadable()
{
var obj2Sql = new Services.ObjectToSql(DataBaseType.SqlServer);
// create an object you want to convert to sql
var employee = new Employee();
// create dbparameters from my object
var dbParameters = obj2Sql.BuildDbParameterList(employee, (s, o) => new SqlParameter(s, o));
// create my parameterized sql based on my specified action type
var insertSql = obj2Sql.BuildQuery <Employee>(ActionType.Insert);
// convert my parameterize sql to be readable
var readAble = obj2Sql.SqlSyntaxHelper.ConvertParameterSqlToReadable(dbParameters, insertSql, Encoding.UTF8);
// unit test
Assert.AreEqual(readAble, "INSERT INTO Employee ([FirstName],[LastName]) VALUES (NULL,NULL)");
}
public void Test_All_Build_Query_Overloads_Throws_ArgumentOutOfRange_When_Passing_Invalid_Action_Type()
{
var objectToSql = new Services.ObjectToSql(DataBaseType.SqlServer, false);
// var isInvalid = System.Enum.TryParse<ActionType>("Insert",out var invalidEnum);
var invalidEnum = (ActionType)70;
Assert.That(() => objectToSql.BuildQuery <Employee>(invalidEnum),
Throws.Exception
.TypeOf <ArgumentOutOfRangeException>());
Assert.That(() => objectToSql.BuildQuery(invalidEnum, new Employee()),
Throws.Exception
.TypeOf <ArgumentOutOfRangeException>());
Assert.That(() => objectToSql.BuildQuery <Employee>(invalidEnum, "Employee", e => e.FirstName),
Throws.Exception
.TypeOf <ArgumentOutOfRangeException>());
}
public void Test_Generic_Build_Insert_Query_With_Outputs()
{
RunTestOnAllDBTypes(delegate(DataBaseType type)
{
if (type == DataBaseType.Sqlite || type == DataBaseType.MySql)
{
return;
}
var objectToSql = new Services.ObjectToSql(type);
var sql = objectToSql.BuildQueryWithOutputs <Employee>(ActionType, e => e.FirstName);
var expected = "";
switch (type)
{
case DataBaseType.SqlServer:
expected = $"INSERT INTO Employee ([FirstName],[LastName]) OUTPUT INSERTED.[FirstName] {Environment.NewLine} VALUES (@FirstName,@LastName)";
break;
case DataBaseType.MySql:
break;
case DataBaseType.Sqlite:
expected = "NOT SUPPORTED";
break;
case DataBaseType.Oracle:
break;
case DataBaseType.Oledb:
break;
case DataBaseType.Access95:
break;
case DataBaseType.Odbc:
break;
default:
throw new ArgumentOutOfRangeException(nameof(type), type, null);
}
Assert.AreEqual(sql, expected);
});
}
public void Test_Generic_BuildQuery_Ensure_Override_Keys_Is_Used()
{
RunTestOnAllDBTypes(delegate(DataBaseType type)
{
var objectToSql = new Services.ObjectToSql(type);
var sql = objectToSql.BuildQuery <EmployeeWithIdentityKeySqlColumn>(ActionType, null, column => column.FirstName);
var answer = "";
switch (type)
{
case DataBaseType.SqlServer:
answer = "IF EXISTS ( SELECT TOP 1 * FROM EmployeeWithIdentityKeySqlColumn WHERE [FirstName]=@FirstName ) BEGIN UPDATE EmployeeWithIdentityKeySqlColumn SET [LastName]=@LastName WHERE [FirstName]=@FirstName END ELSE BEGIN INSERT INTO EmployeeWithIdentityKeySqlColumn ([FirstName],[LastName]) VALUES (@FirstName,@LastName) END";
answer = "IF EXISTS ( SELECT TOP 1 * FROM EmployeeWithIdentityKeySqlColumn WHERE [FirstName]=@FirstName ) BEGIN UPDATE EmployeeWithIdentityKeySqlColumn SET [IdentityKey]=@IdentityKey,[LastName]=@LastName WHERE [FirstName]=@FirstName END ELSE BEGIN INSERT INTO EmployeeWithIdentityKeySqlColumn ([FirstName],[LastName]) VALUES (@FirstName,@LastName) END";
break;
case DataBaseType.MySql:
answer = "INSERT INTO EmployeeWithIdentityKeySqlColumn (`FirstName`,`LastName`) VALUES (@FirstName,@LastName) ON DUPLICATE KEY UPDATE `FirstName`=@FirstName,`LastName`=@LastName";
break;
case DataBaseType.Sqlite:
answer = "INSERT INTO EmployeeWithIdentityKeySqlColumn ([FirstName],[LastName]) VALUES (@FirstName,@LastName) ON CONFLICT ([FirstName] DO UPDATE SET [FirstName]=@FirstName,[LastName]=@LastName WHERE [FirstName]=@FirstName";
break;
case DataBaseType.Oracle:
break;
case DataBaseType.Oledb:
break;
case DataBaseType.Access95:
break;
case DataBaseType.Odbc:
break;
default:
throw new ArgumentOutOfRangeException(nameof(type), type, null);
}
Assert.AreEqual(sql, answer);
});
}
public void Test_BuildQuery_Throws_InvalidOperation_ForNonInsert_Actions()
{
var objectToSql = new Services.ObjectToSql(DataBaseType.SqlServer);
dynamic obj = new ExpandoObject();
obj.FirstName2 = "John";
obj.LastName = "Doe";
var list = System.Enum.GetValues(typeof(ActionType)).Cast <ActionType>().ToList();
list.ForEach(delegate(ActionType type)
{
if (type == ActionType.Insert)
{
Assert.DoesNotThrow(() => objectToSql.BuildQuery(type, obj));
return;
}
Assert.That(() => objectToSql.BuildQuery(type, obj),
Throws.Exception
.TypeOf <InvalidOperationException>());
});
}
public void Test_Generic_BuildQueryWithOutputs_Uses_MappedColumn_Name_Instead_Of_PropertyName()
{
RunTestOnAllDBTypes(delegate(DataBaseType type)
{
var objectToSql = new Services.ObjectToSql(type);
var sql = string.Empty;
if (type == DataBaseType.Sqlite || type == DataBaseType.MySql)
{
EnsureExpectedExceptionIsThrown <NotImplementedException>(() =>
objectToSql.BuildQueryWithOutputs <EmployeeWithMappedColumnDataAnnotation>(ActionType,
"Employee", e => e.FirstName)
);
return;
}
else
{
sql = objectToSql.BuildQueryWithOutputs <EmployeeWithMappedColumnDataAnnotation>(ActionType,
"Employee", e => e.FirstName);
}
Assert.AreEqual(sql, $"INSERT INTO Employee ([FirstName2],[LastName]) OUTPUT INSERTED.[FirstName2] {Environment.NewLine} VALUES (@FirstName,@LastName)");
});
}
public void Test_Generic_BuildQueryWithOutputs()
{
RunTestOnAllDBTypes(delegate(DataBaseType type)
{
var objectToSql = new Services.ObjectToSql(type);
var sql = string.Empty;
if (type == DataBaseType.Sqlite || type == DataBaseType.MySql)
{
EnsureExpectedExceptionIsThrown <NotImplementedException>(() =>
objectToSql.BuildQueryWithOutputs <EmployeeWithIdentityKeySqlColumn>(ActionType,
"Employee", a => a.FirstName)
);
return;
}
else
{
sql = objectToSql.BuildQueryWithOutputs <EmployeeWithIdentityKeySqlColumn>(
ActionType, nameof(Employee), a => a.FirstName);
}
Assert.AreEqual(sql, $"IF EXISTS ( SELECT * FROM Employee WHERE [IdentityKey]=@IdentityKey ) BEGIN UPDATE Employee SET [FirstName]=@FirstName,[LastName]=@LastName OUTPUT DELETED.[FirstName] WHERE [IdentityKey]=@IdentityKeyINSERT INTO Employee ([FirstName],[LastName]) OUTPUT INSERTED.[FirstName] {Environment.NewLine} VALUES (@FirstName,@LastName) END ELSE BEGIN END");
});
}
public void Test_Generic_BuildQueryWithOutputs()
{
RunTestOnAllDBTypes(delegate(DataBaseType type)
{
var objectToSql = new Services.ObjectToSql(type);
var sql = string.Empty;
if (type == DataBaseType.Sqlite || type == DataBaseType.MySql)
{
EnsureExpectedExceptionIsThrown <NotImplementedException>(() =>
objectToSql.BuildQueryWithOutputs <EmployeeWithPrimaryKeySqlColumn>(ActionType,
"Employee", a => a.PrimaryKey)
);
return;
}
else
{
sql = objectToSql.BuildQueryWithOutputs <EmployeeWithPrimaryKeySqlColumn>(
ActionType, nameof(Employee), a => a.PrimaryKey);
}
Assert.AreEqual(sql, $@"UPDATE Employee SET [FirstName]=@FirstName,[LastName]=@LastName OUTPUT DELETED.[PrimaryKey] WHERE [PrimaryKey]=@PrimaryKey");
});
}
