public async Task ShouldWriteCsvFile()
{
var q = new QueryProperties {
Query = "select * from HodorTest", ConnectionString = ConnectionString
};
var o = new QueryOutputProperties
{
ReturnType = QueryReturnType.Csv,
CsvOutput = new CsvOutputProperties
{
CsvSeparator = ";",
IncludeHeaders = true
},
OutputToFile = true,
OutputFile = new OutputFileProperties
{
Path = Path.Combine(outputDirectory, Guid.NewGuid().ToString() + ".csv")
}
};
var options = new QueryOptions {
ThrowErrorOnFailure = true
};
var result = await OracleTasks.ExecuteQueryOracle(q, o, options, new CancellationToken());
Assert.IsTrue(File.Exists(result.Result), "should have created csv queryOutput file");
File.Delete(result.Result);
}
public async Task MultiQueryJSONIsolation()
{
var multiQueryProperties = new InputMultiQuery
{
Queries = new InputQuery[] { new InputQuery {
InputQueryString = "SELECT * FROM DecimalTest"
}, new InputQuery {
InputQueryString = "SELECT * FROM HodorTest"
},
new InputQuery {
InputQueryString = "INSERT INTO HodorTest values('test', 890)"
}, new InputQuery {
InputQueryString = "DELETE FROM HodorTest WHERE value = 890"
} },
ConnectionString = ConnectionString
};
var outputProperties = new QueryOutputProperties
{
ReturnType = QueryReturnType.Json,
JsonOutput = new JsonOutputProperties(),
OutputToFile = false,
};
var options = new QueryOptions {
ThrowErrorOnFailure = true, IsolationLevel = Oracle_IsolationLevel.Serializable
};
var result = await OracleTasks.TransactionalMultiQuery(multiQueryProperties, outputProperties, options, new CancellationToken());
Assert.AreEqual(result.Results.First?["Output"]?.ToString(), "[\r\n {\r\n \"DECIMALVALUE\": 1.123456789123456789123456789\r\n }\r\n]");
Assert.AreEqual(true, result.Success);
}
public async Task IsolationTest1()
{
var q = new QueryProperties {
Query = "select * from InsertTest", ConnectionString = ConnectionString
};
var o = new QueryOutputProperties
{
ReturnType = QueryReturnType.Csv,
CsvOutput = new CsvOutputProperties
{
CsvSeparator = ";",
IncludeHeaders = true
},
OutputToFile = false,
};
var options = new QueryOptions
{
ThrowErrorOnFailure = true,
IsolationLevel = Oracle_IsolationLevel.ReadCommitted
};
var result = await OracleTasks.ExecuteQueryOracle(q, o, options, new CancellationToken());
Assert.AreEqual(result.Result, "NAME;SENDSTATUS\r\nHan_1;0\r\n");
}
public async Task ShouldWriteJsonFile()
{
var q = new QueryProperties {
Query = @"select name as ""name"", value as ""value"" from HodorTest", ConnectionString = ConnectionString
};
var o = new QueryOutputProperties
{
ReturnType = QueryReturnType.Json,
JsonOutput = new JsonOutputProperties(),
OutputToFile = true,
OutputFile = new OutputFileProperties
{
Path = Path.Combine(outputDirectory, Guid.NewGuid().ToString() + ".json")
}
};
var options = new QueryOptions {
ThrowErrorOnFailure = true
};
var result = await OracleTasks.ExecuteQueryOracle(q, o, options, new CancellationToken());
Assert.IsTrue(File.Exists(result.Result), "should have created json outputfile");
var expected = File.ReadAllText(Path.Combine(expectedFileDirectory, "ExpectedJson.json"));
Assert.AreEqual(expected, File.ReadAllText(result.Result));
File.Delete(result.Result);
}
public async Task MultiQuerRollback()
{
var multiQueryProperties = new InputMultiQuery {
Queries = new InputQuery[] { new InputQuery {
InputQueryString = "insert into DecimalTest(DecimalValue) values(10.6)"
}, new InputQuery {
InputQueryString = "SELECT * FROM foo"
} }, ConnectionString = ConnectionString
};
var outputProperties = new QueryOutputProperties
{
ReturnType = QueryReturnType.Json,
JsonOutput = new JsonOutputProperties(),
OutputToFile = true,
OutputFile = new OutputFileProperties
{
Path = Path.Combine(Directory.GetCurrentDirectory(), Guid.NewGuid().ToString() + ".json")
}
};
var options = new QueryOptions {
ThrowErrorOnFailure = true, IsolationLevel = Oracle_IsolationLevel.Serializable
};
var result = new MultiQueryOutput();
try
{
result = await OracleTasks.TransactionalMultiQuery(multiQueryProperties, outputProperties, options, new CancellationToken());
}
catch (Exception)
{
}
var multiQueryProperties2 = new InputMultiQuery {
Queries = new InputQuery[] { new InputQuery {
InputQueryString = "SELECT * FROM DecimalTest"
} }, ConnectionString = ConnectionString
};
var outputProperties2 = new QueryOutputProperties
{
ReturnType = QueryReturnType.Json,
JsonOutput = new JsonOutputProperties()
};
var options2 = new QueryOptions {
ThrowErrorOnFailure = true
};
var result2 = await OracleTasks.TransactionalMultiQuery(multiQueryProperties2, outputProperties2, options2, new CancellationToken());
Assert.That(() => OracleTasks.TransactionalMultiQuery(multiQueryProperties, outputProperties, options, new CancellationToken()), Throws.TypeOf <OracleException>());
Assert.AreEqual(false, result.Success);
Assert.AreNotEqual(2, result2.Results.Count);
File.Delete(outputProperties.OutputFile.Path);
}
public async Task MultiBatchOperationInsertTest()
{
var inputbatch = new InputMultiBatchOperation
{
BatchQueries = new BatchOperationQuery[] {
new BatchOperationQuery {
BatchInputQuery = @"delete from batch_table_test", InputJson = ""
},
new BatchOperationQuery {
BatchInputQuery = @"insert into batch_table_test (NR,NAM)values(:NR,:NAM)", InputJson = "[{\"NR\": 111, \"NAM\":\"nannaa1\"},{\"NR\":222, \"NAM\":\"nannaa2\"},{\"NR\":333, \"NAM\":\"nannaa3\"}, {\"NR\":444, \"NAM\":\"nannaa4\"}]"
},
new BatchOperationQuery {
BatchInputQuery = @"insert into batch_table_test (NR,NAM)values(:NR,:NAM)", InputJson = "[{\"NR\": 555, \"NAM\":\"nannaa1\"},{\"NR\":666, \"NAM\":\"nannaa2\"}]"
}
},
ConnectionString = ConnectionString
};
var options = new BatchOptions
{
ThrowErrorOnFailure = true,
IsolationLevel = Oracle_IsolationLevel.Serializable
};
MultiBatchOperationOutput output;
try
{
output = await OracleTasks.MultiBatchOperationOracle(inputbatch, options, new CancellationToken());
}
catch (Exception ee)
{
throw ee;
}
var o = new QueryOutputProperties
{
ReturnType = QueryReturnType.Json,
JsonOutput = new JsonOutputProperties(),
OutputToFile = false
};
var q2 = new QueryProperties {
Query = @"select count(*) as ROWCOUNT from batch_table_test", ConnectionString = ConnectionString
};
var options_2 = new QueryOptions();
options.ThrowErrorOnFailure = true;
options.IsolationLevel = Oracle_IsolationLevel.Serializable;
var result_debug = await OracleTasks.ExecuteQueryOracle(q2, o, options_2, new CancellationToken());
Assert.AreEqual(result_debug.Result, "[\r\n {\r\n \"ROWCOUNT\": 6.0\r\n }\r\n]");
}
public async Task BatchOperationInsertTest()
{
//t(NR varchar(20), NAM varchar(20))",
var inputbatch = new InputBatchOperation
{
Query = @"BEGIN
insert into batch_table_test (NR,NAM)values(:NR,:NAM);
END;",
InputJson = "[{\"NR\": 111, \"NAM\":\"nannaa1\"},{\"NR\":222, \"NAM\":\"nannaa2\"},{\"NR\":333, \"NAM\":\"nannaa3\"}, {\"NR\":444, \"NAM\":\"nannaa4\"}]",
ConnectionString = ConnectionString
};
var options = new BatchOptions
{
ThrowErrorOnFailure = true,
IsolationLevel = Oracle_IsolationLevel.Serializable
};
BatchOperationOutput batch_output;
try
{
batch_output = await OracleTasks.BatchOperationOracle(inputbatch, options, new CancellationToken());
}
catch (Exception ee)
{
throw ee;
}
// ExecuteQueryOracle rows from db, should be 2.
var o = new QueryOutputProperties
{
ReturnType = QueryReturnType.Csv,
CsvOutput = new CsvOutputProperties
{
CsvSeparator = ";",
IncludeHeaders = true
},
OutputToFile = false,
};
var q2 = new QueryProperties {
Query = @"select count(*) as ROWCOUNT from batch_table_test", ConnectionString = ConnectionString
};
var options_2 = new QueryOptions();
options.ThrowErrorOnFailure = true;
options.IsolationLevel = Oracle_IsolationLevel.Serializable;
var result_debug = await OracleTasks.ExecuteQueryOracle(q2, o, options_2, new CancellationToken());
Assert.AreEqual("ROWCOUNT\r\n4\r\n", result_debug.Result);
}
public async Task RollBackTest_1()
{
var q = new QueryProperties {
Query = @"
BEGIN
insert into duplicate_inserttest_table (po_nr)values ('1');
insert into duplicate_inserttest_table2 (po_nr)values ('2');
insert into duplicate_inserttest_table2 (po_nr)values ('2');
END;", ConnectionString = ConnectionString
};
var o = new QueryOutputProperties
{
ReturnType = QueryReturnType.Csv,
CsvOutput = new CsvOutputProperties
{
CsvSeparator = ";",
IncludeHeaders = true
},
OutputToFile = false,
};
var options = new QueryOptions
{
ThrowErrorOnFailure = true,
IsolationLevel = Oracle_IsolationLevel.None
};
var result = new Output();
var result_debug = new Output();
var ex_string = "";
try
{
result = await OracleTasks.ExecuteQueryOracle(q, o, options, new CancellationToken());
}
catch (Exception ee)
{
ex_string = ee.ToString();
var q2 = new QueryProperties {
Query = @"select * from duplicate_inserttest_table", ConnectionString = ConnectionString
};
result_debug = await OracleTasks.ExecuteQueryOracle(q2, o, options, new CancellationToken());
}
Assert.AreEqual(ex_string.Contains("ORA-00001: unique constraint"), true);
Assert.AreEqual(result.Success, false);
Assert.AreEqual(result_debug.Result, "");
}
public async Task QueryDatabaseJSON()
{
var queryProperties = new QueryProperties {
Query = "SELECT * FROM DecimalTest", ConnectionString = ConnectionString
};
var outputProperties = new QueryOutputProperties
{
ReturnType = QueryReturnType.Json,
JsonOutput = new JsonOutputProperties()
};
var options = new QueryOptions {
ThrowErrorOnFailure = true
};
var result = await OracleTasks.ExecuteQueryOracle(queryProperties, outputProperties, options, new CancellationToken());
Assert.AreNotEqual("", result.Result);
Assert.AreEqual(true, result.Success);
}
public async Task ShouldReturnJsonString()
{
var q = new QueryProperties {
Query = @"select name as ""name"", value as ""value"" from HodorTest", ConnectionString = ConnectionString
};
var o = new QueryOutputProperties
{
ReturnType = QueryReturnType.Json,
JsonOutput = new JsonOutputProperties(),
OutputToFile = false
};
var options = new QueryOptions {
ThrowErrorOnFailure = true
};
var result = await OracleTasks.ExecuteQueryOracle(q, o, options, new CancellationToken());
var expected = File.ReadAllText(Path.Combine(expectedFileDirectory, "ExpectedJson.json"));
Assert.AreEqual(expected, result.Result);
}
public async Task MultiqueryShouldWriteXMLFile()
{
var multiQueryProperties = new InputMultiQuery
{
Queries = new InputQuery[] { new InputQuery {
InputQueryString = "SELECT * FROM DecimalTest"
}, new InputQuery {
InputQueryString = "SELECT * FROM HodorTest"
},
new InputQuery {
InputQueryString = "INSERT INTO HodorTest values('test', 890)"
}, new InputQuery {
InputQueryString = "DELETE FROM HodorTest WHERE value = 890"
} },
ConnectionString = ConnectionString
};
var outputProperties = new QueryOutputProperties
{
ReturnType = QueryReturnType.Xml,
OutputToFile = true,
XmlOutput = new XmlOutputProperties
{
RootElementName = "items",
RowElementName = "item"
},
OutputFile = new OutputFileProperties
{
Path = Path.Combine(outputDirectory, Guid.NewGuid().ToString() + ".json")
}
};
var options = new QueryOptions {
ThrowErrorOnFailure = true
};
var result = await OracleTasks.TransactionalMultiQuery(multiQueryProperties, outputProperties, options, new CancellationToken());
Assert.IsTrue(File.Exists(outputProperties.OutputFile.Path));
Assert.IsTrue(File.ReadAllText(result.Results.First?["OutputPath"]?.ToString()).Contains("<DECIMALVALUE>1.12345678912345678912345678912345678</DECIMALVALUE>"));
File.Delete(outputProperties.OutputFile.Path);
}
public async Task ShouldReturnCsvString()
{
var q = new QueryProperties {
Query = @"select name as ""name"", value as ""value"" from HodorTest", ConnectionString = ConnectionString
};
var o = new QueryOutputProperties
{
ReturnType = QueryReturnType.Csv,
CsvOutput = new CsvOutputProperties
{
CsvSeparator = ";",
IncludeHeaders = true
}
};
var options = new QueryOptions {
ThrowErrorOnFailure = true
};
var result = await OracleTasks.ExecuteQueryOracle(q, o, options, new CancellationToken());
StringAssert.IsMatch(result.Result, "name;value\r\nhodor;123\r\njon;321\r\n");
}
public async Task MultiQueryJSON()
{
var multiQueryProperties = new InputMultiQuery {
Queries = new InputQuery[] { new InputQuery {
InputQueryString = "SELECT * FROM DecimalTest"
}, new InputQuery {
InputQueryString = "SELECT * FROM HodorTest"
} }, ConnectionString = ConnectionString
};
var outputProperties = new QueryOutputProperties
{
ReturnType = QueryReturnType.Json,
JsonOutput = new JsonOutputProperties()
};
var options = new QueryOptions {
ThrowErrorOnFailure = true
};
var result = await OracleTasks.TransactionalMultiQuery(multiQueryProperties, outputProperties, options, new CancellationToken());
Assert.AreNotEqual("", result.Results);
Assert.AreEqual(true, result.Success);
}
public async Task ShouldReturnXmlString()
{
var q = new QueryProperties {
Query = @"select * from HodorTest", ConnectionString = ConnectionString
};
var o = new QueryOutputProperties
{
ReturnType = QueryReturnType.Xml,
XmlOutput = new XmlOutputProperties
{
RootElementName = "items",
RowElementName = "item"
}
};
var options = new QueryOptions {
ThrowErrorOnFailure = true
};
var result = await OracleTasks.ExecuteQueryOracle(q, o, options, new CancellationToken());
var expected = File.ReadAllText(Path.Combine(expectedFileDirectory, "ExpectedUtf16Xml.xml"));
Assert.AreEqual(expected, result.Result);
}
