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"); }); }