public async Task ShouldWriteJsonFile()
{
var q = new QueryProperties {
Query = @"select name as ""name"", value as ""value"" from HodorTest"
};
var o = new OutputProperties
{
ReturnType = QueryReturnType.Json,
JsonOutput = new JsonOutputProperties(),
OutputToFile = true,
OutputFile = new OutputFileProperties
{
Path = Path.Combine(Directory.GetCurrentDirectory(), Guid.NewGuid().ToString() + ".json")
}
};
var options = new Options {
ThrowErrorOnFailure = true
};
Output result = await QueryTask.Query(q, o, _conn, options, new CancellationToken());
Assert.IsTrue(File.Exists(result.Result), "should have created json outputfile");
Assert.AreEqual(@"[
{
""name"": ""hodor"",
""value"": 123
},
{
""name"": ""jon"",
""value"": 321
}
]",
File.ReadAllText(result.Result));
File.Delete(result.Result);
}
public async Task ShouldReturnXmlString()
{
var q = new QueryProperties {
Query = @"select * from HodorTest"
};
var o = new OutputProperties
{
ReturnType = QueryReturnType.Xml,
XmlOutput = new XmlOutputProperties
{
RootElementName = "items",
RowElementName = "item"
}
};
var options = new Options {
ThrowErrorOnFailure = true
};
Output result = await QueryTask.Query(q, o, _conn, options, new CancellationToken());
Assert.AreEqual(@"<?xml version=""1.0"" encoding=""utf-16""?>
<items>
<item>
<NAME>hodor</NAME>
<VALUE>123</VALUE>
</item>
<item>
<NAME>jon</NAME>
<VALUE>321</VALUE>
</item>
</items>", result.Result);
}
public async Task ShouldReturnJsonString()
{
var q = new QueryProperties {
Query = @"select name as ""name"", value as ""value"" from HodorTest"
};
var o = new OutputProperties
{
ReturnType = QueryReturnType.Json,
JsonOutput = new JsonOutputProperties(),
OutputToFile = false
};
var options = new Options {
ThrowErrorOnFailure = true
};
Output result = await QueryTask.Query(q, o, _conn, options, new CancellationToken());
Assert.IsTrue(string.Equals(result.Result, @"[
{
""name"": ""hodor"",
""value"": 123
},
{
""name"": ""jon"",
""value"": 321
}
]"));
}
public async Task ExecuteOracleSelect()
{
QueryParameter[] qpList = new QueryParameter[1];
qpList[0] = new QueryParameter {
Name = "DESC", Value = "unit test text", DataType = QueryParameterType.Text
};
var input = new QueryProperties
{
Query = "SELECT * FROM TestTable WHERE textField LIKE :DESC",
Parameters = qpList
};
var conn = new ConnectionProperties
{
Address = Address,
Username = Username,
Password = Password
};
var output = new OutputProperties {
CultureInfo = null, OutputToFile = false
};
var result = await IFSAccessProvider.Query(input, output, conn, new CancellationToken());
Assert.Equal("Command executed", result.Result);
}
public async System.Threading.Tasks.Task InsertValuesViaParametersAsync()
{
var query = "INSERT INTO TestTable (textField) VALUES (:param1)";
OracleParametersForTask ownOracleParam = new OracleParametersForTask
{
DataType = OracleParametersForTask.ParameterDataType.Varchar2,
Name = "param1",
Value = "Text from parameter"
};
var input = new Input
{
ConnectionString = connectionString,
CommandOrProcedureName = query,
CommandType = OracleCommandType.Command,
TimeoutSeconds = 60,
InputParameters = new OracleParametersForTask[1]
};
input.InputParameters[0] = ownOracleParam;
var output = new OutputProperties();
var result = await ExecuteCommand.Execute(input, output, _taskOptions);
Assert.AreEqual(true, result.Success);
}
public async System.Threading.Tasks.Task ExecuteStoredProcedureWithOutputParamAsync()
{
var query = "UnitTestProc";
var input = new Input
{
ConnectionString = connectionString,
CommandOrProcedureName = query,
CommandType = OracleCommandType.StoredProcedure,
TimeoutSeconds = 60
};
var oracleParam = new OracleParametersForTask
{
DataType = OracleParametersForTask.ParameterDataType.Varchar2,
Name = "returnVal",
Size = 255
};
var output = new OutputProperties();
output.OutputParameters = new OracleParametersForTask[1];
output.OutputParameters[0] = oracleParam;
var result = await ExecuteCommand.Execute(input, output, _taskOptions);
Assert.AreEqual(true, result.Success);
}
public async Task ShouldWriteCsvFile()
{
var q = new QueryProperties {
Query = "select * from HodorTest"
};
var o = new OutputProperties
{
ReturnType = QueryReturnType.Csv,
CsvOutput = new CsvOutputProperties
{
CsvSeparator = ";",
IncludeHeaders = true
},
OutputToFile = true,
OutputFile = new OutputFileProperties
{
Path = Path.Combine(Directory.GetCurrentDirectory(), Guid.NewGuid().ToString() + ".csv")
}
};
var options = new Options {
ThrowErrorOnFailure = true
};
Output result = await QueryTask.Query(q, o, _conn, options, new CancellationToken());
Assert.IsTrue(File.Exists(result.Result), "should have created csv output file");
File.Delete(result.Result);
}
void EmitInternal(LogEvent logEvent)
{
if (logEvent == null)
{
throw new ArgumentNullException(nameof(logEvent));
}
if (logEvent.Level < _minLevel)
{
return;
}
var outputProperties = OutputProperties.GetOutputProperties(logEvent);
lock (_syncRoot)
{
try
{
foreach (var outputToken in _outputTemplate.Tokens)
{
var propertyToken = outputToken as PropertyToken;
if (propertyToken == null)
{
RenderOutputTemplateTextToken(outputToken, outputProperties);
}
else
{
switch (propertyToken.PropertyName)
{
case OutputProperties.LevelPropertyName:
RenderLevelToken(logEvent.Level);
break;
case OutputProperties.MessagePropertyName:
RenderMessageToken(logEvent);
break;
case OutputProperties.ExceptionPropertyName:
RenderExceptionToken(propertyToken, outputProperties);
break;
default:
// Don't print template properties that don't exist.
if (outputProperties.ContainsKey(propertyToken.PropertyName))
{
RenderOutputTemplatePropertyToken(propertyToken, outputProperties);
}
break;
}
}
}
}
finally { Console.ResetColor(); }
}
}
public void Emit(LogEvent logEvent)
{
if (logEvent == null)
{
throw new ArgumentNullException(nameof(logEvent));
}
var outputProperties = OutputProperties.GetOutputProperties(logEvent);
var palette = GetPalette(logEvent.Level);
var output = Console.Out;
lock (_syncRoot)
{
try
{
foreach (var outputToken in _outputTemplate.Tokens)
{
var propertyToken = outputToken as PropertyToken;
if (propertyToken == null)
{
RenderOutputToken(palette, outputToken, outputProperties, output);
}
else
{
LogEventPropertyValue propertyValue;
if (!outputProperties.TryGetValue(propertyToken.PropertyName, out propertyValue))
{
continue;
}
switch (propertyToken.PropertyName)
{
case OutputProperties.MessagePropertyName:
RenderMessageToken(logEvent, palette, output);
break;
case OutputProperties.ExceptionPropertyName:
RenderExceptionToken(palette, propertyToken, outputProperties, output);
break;
default:
RenderOutputToken(palette, outputToken, outputProperties, output);
break;
}
}
}
}
finally { Console.ResetColor(); }
}
}
public FlatFileReaderEntry()
{
Key = FlatFileReader.KeyConst;
Name = FlatFileReader.Name;
Description = FlatFileReader.Description;
InputProperties.Add(new ElementPropertyEntry("SourceFilePath", "Source file path", DataType.VirtualPath, true));
InputProperties.Add(new ElementPropertyEntry("SkipStartingDataRows", "Starting row to skip", DataType.Int));
InputProperties.Add(new ElementPropertyEntry("FirstRowHasHeader", "Use first row as header", DataType.Bool, true));
InputProperties.Add(new ElementPropertyEntry("ColumnDelimiter", "Column char delimiter", DataType.Char, true));
InputProperties.Add(new ElementPropertyEntry("LimitToRows", "Limit result to rows number", DataType.Int));
OutputProperties.Add(new ElementPropertyEntry("Table", "Rows imported from flat file", DataType.Table, true));
}
//we can simply add new FileManagers which supports different formats(different than CSV)
//by overriding "ParseHotelInfoToList" and implementing format specific logic
public override HotelInfo[] ParseHotelInfoToList(IFormCollection form)
{
//check file is exists
this.ValidateForm(form);
IFormFile inputFile = form.Files[0];
List <HotelInfo> hotelInfo = new List <HotelInfo>();
//read file
using (var reader = new StreamReader(inputFile.OpenReadStream()))
{
var headers = reader.ReadLine();
while (!reader.EndOfStream)
{
var line = reader.ReadLine();
var adrr = "";
//extract address in to variable
Match match = Regex.Match(line, cnstAddrRegExpPattern);
if (match.Success)
{
adrr = match.Groups[1].Value;
}
//remove address parts from line
line = Regex.Replace(line, cnstAddrRegExpPattern, "");
//split line by comma into vaues
var values = line.Split(',');
if (values.Length == 6)
{
//initialize model
var item = new HotelInfo(values[0], adrr, Int32.Parse(values[2]), values[3], values[4], values[5]);
//validate model by validation rules which loading by appsettings.json
this.ValidateModel(item);
//add to list
hotelInfo.Add(item);
}
else
{
throw new Exception("CSV Format Error!");
}
}
}
//get sort field and sort direction from request object
OutputProperties outProps = MapFormCollectionToOutputProperties(form);
HotelInfo[] hotelInfoArray = hotelInfo.ToArray();
//sort model list according to output properties
hotelInfoArray = this.SortHotelInfos(hotelInfoArray, outProps);
return(hotelInfoArray);
}
public SqlQueryReaderEntry()
{
//Key = "SqlQueryReader";
//Name = "SQL Query Reader";
//Description = "This elements read data form a SQL Server query and returns a Table value.";
Key = SqlQueryReader.KeyConst;
Name = SqlQueryReader.Name;
Description = SqlQueryReader.Description;
InputProperties.Add(new ElementPropertyEntry("ConnectionString", "Sql Server Connection String", DataType.String, true));
InputProperties.Add(new ElementPropertyEntry("SqlQuery", "SqL Query", DataType.String, true));
OutputProperties.Add(new ElementPropertyEntry("Table", "Rows imported from Sql Query", DataType.Table, true));
}
public void Emit(LogEvent logEvent)
{
if (logEvent == null)
{
throw new ArgumentNullException(nameof(logEvent));
}
var outputProperties = OutputProperties.GetOutputProperties(logEvent);
var outputStream = GetOutputStream(logEvent.Level);
lock (_syncRoot)
{
try
{
foreach (var outputToken in _outputTemplate.Tokens)
{
var propertyToken = outputToken as PropertyToken;
if (propertyToken == null)
{
RenderOutputTemplateTextToken(outputToken, outputProperties, outputStream);
}
else
{
switch (propertyToken.PropertyName)
{
case OutputProperties.LevelPropertyName:
RenderLevelToken(logEvent.Level, outputToken, outputProperties, outputStream);
break;
case OutputProperties.MessagePropertyName:
RenderMessageToken(logEvent, outputStream);
break;
case OutputProperties.ExceptionPropertyName:
RenderExceptionToken(propertyToken, outputProperties, outputStream);
break;
default:
RenderOutputTemplatePropertyToken(propertyToken, outputProperties, outputStream);
break;
}
}
}
}
finally { Console.ResetColor(); }
}
}
public async System.Threading.Tasks.Task GatAndUseRefCursorToJtoken()
{
// Replicate of test of https://docs.oracle.com/database/121/ODPNT/featRefCursor.htm#ODPNT319
//////////////////////////////////////////////////
/// Get refcursor
var oracleParam = new OracleParametersForTask
{
DataType = OracleParametersForTask.ParameterDataType.RefCursor,
Name = "outRefPrm",
Value = DBNull.Value,
Size = 0
};
var output = new OutputProperties
{
DataReturnType = OracleCommandReturnType.Parameters
};
var input = new Input
{
ConnectionString = connectionString,
CommandOrProcedureName = "begin open :1 for select col1 from test; end;",
CommandType = OracleCommandType.Command,
BindParametersByName = false,
TimeoutSeconds = 60
};
output.OutputParameters = new OracleParametersForTask[1];
output.OutputParameters[0] = oracleParam;
var result = await ExecuteCommand.Execute(input, output, _taskOptions);
//////////////////////////////////////////////////
/// Ref cursor to JToken
var secondInput = new RefCursorToJTokenInput
{
Refcursor = result.Result[0]
};
var secondResult = ExecuteCommand.RefCursorToJToken(secondInput);
StringAssert.Contains(@"[{""COL1"":1.0}]", JsonConvert.SerializeObject(secondResult.Result));
}
public async System.Threading.Tasks.Task InsertValues()
{
var query = "INSERT INTO TestTable (textField) VALUES ('unit test text')";
var output = new OutputProperties();
var input = new Input
{
ConnectionString = connectionString,
CommandOrProcedureName = query,
CommandType = OracleCommandType.Command,
TimeoutSeconds = 60
};
var result = await ExecuteCommand.Execute(input, output, _taskOptions);
Assert.AreEqual(true, result.Success);
}
public async System.Threading.Tasks.Task CreateTable()
{
var query = "CREATE TABLE TestTable(textField VARCHAR(255))";
var output = new OutputProperties();
var input = new Input
{
ConnectionString = connectionString,
CommandOrProcedureName = query,
CommandType = OracleCommandType.Command,
TimeoutSeconds = 60
};
var result = await ExecuteCommand.Execute(input, output, _taskOptions);
Assert.AreEqual(true, result.Success);
}
public async Task QueryDatabaseJSON()
{
var queryProperties = new QueryProperties {
Query = "SELECT * FROM DecimalTest"
};
var outputProperties = new OutputProperties
{
ReturnType = QueryReturnType.Json,
JsonOutput = new JsonOutputProperties()
};
var options = new Options {
ThrowErrorOnFailure = true
};
Output result = await QueryTask.Query(queryProperties, outputProperties, _conn, options, new CancellationToken());
Assert.AreNotEqual("", result.Result);
Assert.AreEqual(true, result.Success);
}
public void Emit(LogEvent logEvent)
{
if (logEvent == null)
{
throw new ArgumentNullException("logEvent");
}
var outputProperties = OutputProperties.GetOutputProperties(logEvent);
var palette = GetPalette(logEvent.Level);
lock (_syncRoot)
{
foreach (var outputToken in _outputTemplate.Tokens)
{
var propertyToken = outputToken as PropertyToken;
if (propertyToken != null &&
propertyToken.PropertyName == OutputProperties.MessagePropertyName)
{
foreach (var messageToken in logEvent.MessageTemplate.Tokens)
{
var messagePropertyToken = messageToken as PropertyToken;
if (messagePropertyToken != null)
{
SetHighlightColors(palette);
messageToken.Render(logEvent.Properties, Console.Out);
}
else
{
SetBaseColors(palette);
messageToken.Render(logEvent.Properties, Console.Out);
}
}
}
else
{
SetBaseColors(palette);
outputToken.Render(outputProperties, Console.Out);
}
}
Console.ResetColor();
}
}
//sort array by given field name and field direction
public HotelInfo[] SortHotelInfos(HotelInfo[] hotelInfoArray, OutputProperties outProps)
{
if (outProps != null)
{
PropertyInfo pi = typeof(HotelInfo).GetProperty(outProps.SortField);
if (pi != null)
{
if (outProps.SortDirection == "Desc")
{
return(hotelInfoArray.OrderByDescending(item => pi.GetValue(item, null)).ToArray());
}
else
{
return(hotelInfoArray.OrderBy(item => pi.GetValue(item, null)).ToArray());
}
}
}
return(hotelInfoArray);
}
public async Task ShouldWriteXmlFile()
{
var q = new QueryProperties {
Query = @"select name as ""name"", value as ""value"" from HodorTest"
};
var o = new OutputProperties
{
ReturnType = QueryReturnType.Xml,
XmlOutput = new XmlOutputProperties
{
RootElementName = "items",
RowElementName = "item"
},
OutputToFile = true,
OutputFile = new OutputFileProperties
{
Path = Path.Combine(Directory.GetCurrentDirectory(), Guid.NewGuid().ToString() + ".xml")
}
};
var options = new Options {
ThrowErrorOnFailure = true
};
Output result = await QueryTask.Query(q, o, _conn, options, new CancellationToken());
Assert.IsTrue(File.Exists(result.Result), "should have created xml output file");
Assert.AreEqual(
@"<?xml version=""1.0"" encoding=""utf-8""?>
<items>
<item>
<name>hodor</name>
<value>123</value>
</item>
<item>
<name>jon</name>
<value>321</value>
</item>
</items>",
File.ReadAllText(result.Result));
File.Delete(result.Result);
}
//load output properties from request (sorting field and sorting direction)
public OutputProperties MapFormCollectionToOutputProperties(IFormCollection form)
{
OutputProperties outProps = null;
string SortFieldKey = "SortField";
string SortDirectionKey = "SortDirection";
if (form.Any())
{
if (form.Keys.Contains(SortFieldKey))
{
outProps = new OutputProperties();
outProps.SortField = form[SortFieldKey];
if (form.Keys.Contains(SortDirectionKey))
{
outProps.SortDirection = form[SortDirectionKey];
}
}
}
return(outProps);
}
public async Task ShouldReturnCsvString()
{
var q = new QueryProperties {
Query = @"select name as ""name"", value as ""value"" from HodorTest"
};
var o = new OutputProperties
{
ReturnType = QueryReturnType.Csv,
CsvOutput = new CsvOutputProperties
{
CsvSeparator = ";",
IncludeHeaders = true
}
};
var options = new Options {
ThrowErrorOnFailure = true
};
Output result = await QueryTask.Query(q, o, _conn, options, new CancellationToken());
StringAssert.IsMatch(result.Result, "name;value\r\nhodor;123\r\njon;321\r\n");
}
public async System.Threading.Tasks.Task CreateProcedure()
{
var query = @"
CREATE PROCEDURE UnitTestProc (returnVal OUT VARCHAR2) AS
BEGIN
SELECT TEXTFIELD INTO returnVal FROM TESTTABLE WHERE ROWNUM = 1;
END;";
var output = new OutputProperties();
var input = new Input
{
ConnectionString = connectionString,
CommandOrProcedureName = query,
CommandType = OracleCommandType.Command,
TimeoutSeconds = 60
};
var result = await ExecuteCommand.Execute(input, output, _taskOptions);
Assert.AreEqual(true, result.Success);
}
public void ShouldReadFromMsAccessViaOdbc()
{
// Configure first ODBC at Control Panel->Administrative Tools->ODBC Data sources (64-bit) to point to \TestFiles\ODBC_testDB.accdb, and ensure that the data source name equals ODBC_testDB
var conn = new ConnectionInformation
{
ConnectionString = "DSN=ODBC_testDB",
TimeoutSeconds = 30
};
var odbcQuery = new QueryParameters
{
Query = "SELECT Animal FROM AnimalTypes WHERE Animal = ? OR Animal = ?",
ParametersInOrder = new[] { new QueryParameter {
Value = "Bear"
}, new QueryParameter {
Value = "Moose"
} },
};
var output = new OutputProperties
{
ReturnType = QueryReturnType.Xml,
XmlOutput = new XmlOutputProperties
{
RootElementName = "ROW",
RowElementName = "ROWSET",
}
};
var resultTask = OdbcTask.Query(odbcQuery, output, conn, new CancellationToken());
resultTask.Wait();
var result = resultTask.Result.Result;
Assert.NotNull(result);
Assert.That(result, Contains.Substring("<Animal>Bear</Animal>"));
Assert.That(result, Contains.Substring("<Animal>Moose</Animal>"));
}
public async System.Threading.Tasks.Task TestTearDownAsync()
{
var query = "DROP TABLE TestTable";
var output = new OutputProperties();
var input = new Input
{
ConnectionString = connectionString,
CommandOrProcedureName = query,
CommandType = OracleCommandType.Command,
TimeoutSeconds = 60
};
var result = await ExecuteCommand.Execute(input, output, _taskOptions);
Assert.AreEqual(true, result.Success);
input.CommandOrProcedureName =
"DROP PROCEDURE UnitTestProc";
result = await ExecuteCommand.Execute(input, output, _taskOptions);
Assert.AreEqual(true, result.Success);
}
public void Test_OutputOperations_EventHub()
{
BasicDelegatingHandler handler = new BasicDelegatingHandler();
using (var undoContext = UndoContext.Current)
{
undoContext.Start();
string resourceGroupName = TestUtilities.GenerateName("StreamAnalytics");
string resourceName = TestUtilities.GenerateName("MyStreamingJobSubmittedBySDK");
string serviceLocation = TestHelper.GetDefaultLocation();
var resourceClient = TestHelper.GetResourceClient(handler);
var client = TestHelper.GetStreamAnalyticsManagementClient(handler);
try
{
ResourceGroup resourceGroup = new ResourceGroup()
{
Location = serviceLocation
};
resourceClient.ResourceGroups.CreateOrUpdate(resourceGroupName, resourceGroup);
Job job = new Job();
job.Name = resourceName;
job.Location = serviceLocation;
// Construct the general properties for JobProperties
JobProperties jobProperties = new JobProperties();
jobProperties.Sku = new Sku()
{
Name = "standard"
};
jobProperties.EventsOutOfOrderPolicy = EventsOutOfOrderPolicy.Drop;
jobProperties.EventsOutOfOrderMaxDelayInSeconds = 0;
job.Properties = jobProperties;
// Construct the JobCreateProperties
JobCreateOrUpdateParameters jobCreateOrUpdateParameters = new JobCreateOrUpdateParameters();
jobCreateOrUpdateParameters.Job = job;
// Create a streaming job
JobCreateOrUpdateResponse jobCreateOrUpdateResponse = client.StreamingJobs.CreateOrUpdate(resourceGroupName, jobCreateOrUpdateParameters);
Assert.Equal(HttpStatusCode.OK, jobCreateOrUpdateResponse.StatusCode);
// Get a streaming job to check
JobGetParameters jobGetParameters = new JobGetParameters(string.Empty);
JobGetResponse jobGetResponse = client.StreamingJobs.Get(resourceGroupName, resourceName, jobGetParameters);
Assert.Equal(HttpStatusCode.OK, jobGetResponse.StatusCode);
Assert.Equal(serviceLocation, jobGetResponse.Job.Location);
Assert.Equal(resourceName, jobGetResponse.Job.Name);
// Construct the Output
OutputProperties outputProperties = new OutputProperties();
string outputName = TestUtilities.GenerateName("outputtest");
string partitionKey = "partitionKey";
EventHubOutputDataSource eventHubOutputDataSource = new EventHubOutputDataSource()
{
Properties = new EventHubOutputDataSourceProperties()
{
ServiceBusNamespace = "sdktest",
EventHubName = "sdkeventhub",
SharedAccessPolicyName = TestHelper.SharedAccessPolicyName,
SharedAccessPolicyKey = TestHelper.SharedAccessPolicyKey,
PartitionKey = partitionKey
}
};
JsonSerialization jsonSerialization = new JsonSerialization()
{
Properties = new JsonSerializationProperties()
{
Encoding = "UTF8",
Format = Format.LineSeparated
}
};
outputProperties.DataSource = eventHubOutputDataSource;
outputProperties.Serialization = jsonSerialization;
Output output1 = new Output(outputName)
{
Properties = outputProperties
};
// Add an output
OutputCreateOrUpdateParameters outputCreateOrUpdateParameters = new OutputCreateOrUpdateParameters();
outputCreateOrUpdateParameters.Output = output1;
OutputCreateOrUpdateResponse outputCreateOrUpdateResponse = client.Outputs.CreateOrUpdate(resourceGroupName, resourceName, outputCreateOrUpdateParameters);
Assert.Equal(HttpStatusCode.OK, outputCreateOrUpdateResponse.StatusCode);
Assert.Equal(outputName, outputCreateOrUpdateResponse.Output.Name);
Assert.True(outputCreateOrUpdateResponse.Output.Properties.Serialization is JsonSerialization);
JsonSerialization jsonSerializationInResponse1 = (JsonSerialization)outputCreateOrUpdateResponse.Output.Properties.Serialization;
Assert.Equal(Format.LineSeparated, jsonSerializationInResponse1.Properties.Format);
Assert.True(outputCreateOrUpdateResponse.Output.Properties.DataSource is EventHubOutputDataSource);
EventHubOutputDataSource eventHubOutputDataSourceInResponse1 = (EventHubOutputDataSource)outputCreateOrUpdateResponse.Output.Properties.DataSource;
Assert.Equal(partitionKey, eventHubOutputDataSourceInResponse1.Properties.PartitionKey);
Assert.NotNull(outputCreateOrUpdateResponse.Output.Properties.Etag);
// Get the output
OutputGetResponse outputGetResponse = client.Outputs.Get(resourceGroupName, resourceName, outputName);
Assert.Equal(HttpStatusCode.OK, outputGetResponse.StatusCode);
Assert.Equal(outputName, outputGetResponse.Output.Name);
Assert.True(outputGetResponse.Output.Properties.Serialization is JsonSerialization);
JsonSerialization jsonSerializationInResponse2 = (JsonSerialization)outputGetResponse.Output.Properties.Serialization;
Assert.Equal(Format.LineSeparated, jsonSerializationInResponse2.Properties.Format);
Assert.True(outputGetResponse.Output.Properties.DataSource is EventHubOutputDataSource);
EventHubOutputDataSource eventHubOutputDataSourceInResponse2 = (EventHubOutputDataSource)outputGetResponse.Output.Properties.DataSource;
Assert.Equal(partitionKey, eventHubOutputDataSourceInResponse2.Properties.PartitionKey);
// Test output connectivity
DataSourceTestConnectionResponse response = client.Outputs.TestConnection(resourceGroupName, resourceName, outputName);
Assert.Equal(OperationStatus.Succeeded, response.Status);
Assert.Equal(DataSourceTestStatus.TestSucceeded, response.DataSourceTestStatus);
// Update the output
jsonSerialization = new JsonSerialization()
{
Properties = new JsonSerializationProperties()
{
Encoding = "UTF8",
Format = Format.Array
}
};
string newPartitionKey = TestUtilities.GenerateName("NewPartitionKey");
eventHubOutputDataSource.Properties.PartitionKey = newPartitionKey;
outputProperties.DataSource = eventHubOutputDataSource;
outputProperties.Serialization = jsonSerialization;
outputProperties.Etag = outputCreateOrUpdateResponse.Output.Properties.Etag;
OutputPatchParameters outputPatchParameters = new OutputPatchParameters(outputProperties);
OutputPatchResponse outputPatchResponse = client.Outputs.Patch(resourceGroupName, resourceName, outputName, outputPatchParameters);
Assert.Equal(HttpStatusCode.OK, outputPatchResponse.StatusCode);
Assert.True(outputPatchResponse.Properties.Serialization is JsonSerialization);
JsonSerialization jsonSerializationInResponse3 = (JsonSerialization)outputPatchResponse.Properties.Serialization;
Assert.Equal(Format.Array, jsonSerializationInResponse3.Properties.Format);
Assert.True(outputPatchResponse.Properties.DataSource is EventHubOutputDataSource);
EventHubOutputDataSource eventHubOutputDataSourceInResponse3 = (EventHubOutputDataSource)outputPatchResponse.Properties.DataSource;
Assert.Equal(newPartitionKey, eventHubOutputDataSourceInResponse3.Properties.PartitionKey);
Assert.NotNull(outputPatchResponse.Properties.Etag);
Assert.NotEqual(outputCreateOrUpdateResponse.Output.Properties.Etag, outputPatchResponse.Properties.Etag);
// Delete the output
AzureOperationResponse deleteInputOperationResponse = client.Outputs.Delete(resourceGroupName, resourceName, outputName);
Assert.Equal(HttpStatusCode.OK, deleteInputOperationResponse.StatusCode);
// Check that there are 0 outputs in the job
jobGetParameters = new JobGetParameters("outputs");
jobGetResponse = client.StreamingJobs.Get(resourceGroupName, resourceName, jobGetParameters);
Assert.Equal(HttpStatusCode.OK, jobGetResponse.StatusCode);
Assert.Equal(0, jobGetResponse.Job.Properties.Outputs.Count);
}
finally
{
client.StreamingJobs.Delete(resourceGroupName, resourceName);
resourceClient.ResourceGroups.Delete(resourceGroupName);
}
}
}
public void Test_OutputOperations_E2E()
{
BasicDelegatingHandler handler = new BasicDelegatingHandler();
using (var undoContext = UndoContext.Current)
{
undoContext.Start();
string resourceGroupName = TestUtilities.GenerateName("StreamAnalytics");
string resourceName = TestUtilities.GenerateName("MyStreamingJobSubmittedBySDK");
string serviceLocation = TestHelper.GetDefaultLocation();
var resourceClient = TestHelper.GetResourceClient(handler);
var client = TestHelper.GetStreamAnalyticsManagementClient(handler);
try
{
ResourceGroup resourceGroup = new ResourceGroup()
{
Location = serviceLocation
};
resourceClient.ResourceGroups.CreateOrUpdate(resourceGroupName, resourceGroup);
Job job = new Job();
job.Name = resourceName;
job.Location = serviceLocation;
// Construct the general properties for JobProperties
JobProperties jobProperties = new JobProperties();
jobProperties.Sku = new Sku()
{
Name = "standard"
};
jobProperties.EventsOutOfOrderPolicy = EventsOutOfOrderPolicy.Drop;
jobProperties.EventsOutOfOrderMaxDelayInSeconds = 0;
job.Properties = jobProperties;
// Construct the JobCreateProperties
JobCreateOrUpdateParameters jobCreateOrUpdateParameters = new JobCreateOrUpdateParameters();
jobCreateOrUpdateParameters.Job = job;
// Create a streaming job
JobCreateOrUpdateResponse jobCreateOrUpdateResponse = client.StreamingJobs.CreateOrUpdate(resourceGroupName, jobCreateOrUpdateParameters);
Assert.Equal(HttpStatusCode.OK, jobCreateOrUpdateResponse.StatusCode);
// Get a streaming job to check
JobGetParameters jobGetParameters = new JobGetParameters(string.Empty);
JobGetResponse jobGetResponse = client.StreamingJobs.Get(resourceGroupName, resourceName, jobGetParameters);
Assert.Equal(HttpStatusCode.OK, jobGetResponse.StatusCode);
Assert.Equal(serviceLocation, jobGetResponse.Job.Location);
Assert.Equal(resourceName, jobGetResponse.Job.Name);
// Construct the Output
OutputProperties outputProperties = new OutputProperties();
string outputName = TestUtilities.GenerateName("outputtest");
string tableName = "StateInfo";
SqlAzureOutputDataSource sqlAzureOutputDataSource = new SqlAzureOutputDataSource()
{
Properties = new SqlAzureOutputDataSourceProperties()
{
Server = TestHelper.Server,
Database = TestHelper.Database,
User = TestHelper.User,
Password = TestHelper.Password,
Table = tableName
}
};
outputProperties.DataSource = sqlAzureOutputDataSource;
Output output1 = new Output(outputName)
{
Properties = outputProperties
};
// Add an output
OutputCreateOrUpdateParameters outputCreateOrUpdateParameters = new OutputCreateOrUpdateParameters();
outputCreateOrUpdateParameters.Output = output1;
OutputCreateOrUpdateResponse outputCreateOrUpdateResponse = client.Outputs.CreateOrUpdate(resourceGroupName, resourceName, outputCreateOrUpdateParameters);
Assert.Equal(HttpStatusCode.OK, outputCreateOrUpdateResponse.StatusCode);
Assert.Equal(outputName, outputCreateOrUpdateResponse.Output.Name);
Assert.True(outputCreateOrUpdateResponse.Output.Properties.DataSource is SqlAzureOutputDataSource);
SqlAzureOutputDataSource sqlAzureOutputDataSourceInResponse1 = (SqlAzureOutputDataSource)outputCreateOrUpdateResponse.Output.Properties.DataSource;
Assert.Equal(tableName, sqlAzureOutputDataSourceInResponse1.Properties.Table);
Assert.NotNull(outputCreateOrUpdateResponse.Output.Properties.Etag);
// Get the output
OutputGetResponse outputGetResponse = client.Outputs.Get(resourceGroupName, resourceName, outputName);
Assert.Equal(HttpStatusCode.OK, outputGetResponse.StatusCode);
Assert.Equal(outputName, outputGetResponse.Output.Name);
Assert.True(outputGetResponse.Output.Properties.DataSource is SqlAzureOutputDataSource);
SqlAzureOutputDataSource sqlAzureOutputDataSourceInResponse2 = (SqlAzureOutputDataSource)outputGetResponse.Output.Properties.DataSource;
Assert.Equal(tableName, sqlAzureOutputDataSourceInResponse2.Properties.Table);
// List outputs
OutputListResponse outputListResponse = client.Outputs.ListOutputInJob(resourceGroupName, resourceName, new OutputListParameters());
Assert.Equal(HttpStatusCode.OK, outputListResponse.StatusCode);
Assert.Equal(1, outputListResponse.Value.Count);
// Check that there is 1 output in the job
jobGetParameters = new JobGetParameters("outputs");
jobGetResponse = client.StreamingJobs.Get(resourceGroupName, resourceName, jobGetParameters);
Assert.Equal(HttpStatusCode.OK, jobGetResponse.StatusCode);
Assert.Equal(1, jobGetResponse.Job.Properties.Outputs.Count);
// Test output connectivity
DataSourceTestConnectionResponse response = client.Outputs.TestConnection(resourceGroupName, resourceName, outputName);
Assert.Equal(OperationStatus.Succeeded, response.Status);
Assert.Equal(DataSourceTestStatus.TestSucceeded, response.DataSourceTestStatus);
// Update the output
string newTableName = TestUtilities.GenerateName("NewTableName");
sqlAzureOutputDataSource.Properties.Table = newTableName;
outputProperties.DataSource = sqlAzureOutputDataSource;
outputProperties.Etag = outputCreateOrUpdateResponse.Output.Properties.Etag;
OutputPatchParameters outputPatchParameters = new OutputPatchParameters(outputProperties);
OutputPatchResponse outputPatchResponse = client.Outputs.Patch(resourceGroupName, resourceName, outputName, outputPatchParameters);
Assert.Equal(HttpStatusCode.OK, outputPatchResponse.StatusCode);
Assert.True(outputPatchResponse.Properties.DataSource is SqlAzureOutputDataSource);
SqlAzureOutputDataSource sqlAzureOutputDataSourceInResponse3 = (SqlAzureOutputDataSource)outputPatchResponse.Properties.DataSource;
Assert.Equal(newTableName, sqlAzureOutputDataSourceInResponse3.Properties.Table);
Assert.NotNull(outputPatchResponse.Properties.Etag);
Assert.NotEqual(outputCreateOrUpdateResponse.Output.Properties.Etag, outputPatchResponse.Properties.Etag);
// Add second output
string outputName2 = TestUtilities.GenerateName("outputtest");
Output output2 = new Output(outputName2)
{
Properties = outputProperties
};
outputCreateOrUpdateParameters.Output = output2;
outputCreateOrUpdateResponse = client.Outputs.CreateOrUpdate(resourceGroupName, resourceName, outputCreateOrUpdateParameters);
// List outputs
outputListResponse = client.Outputs.ListOutputInJob(resourceGroupName, resourceName, new OutputListParameters());
Assert.Equal(HttpStatusCode.OK, outputListResponse.StatusCode);
Assert.Equal(2, outputListResponse.Value.Count);
// Check that there are 2 outputs in the job
jobGetParameters = new JobGetParameters("outputs");
jobGetResponse = client.StreamingJobs.Get(resourceGroupName, resourceName, jobGetParameters);
Assert.Equal(HttpStatusCode.OK, jobGetResponse.StatusCode);
Assert.Equal(2, jobGetResponse.Job.Properties.Outputs.Count);
// Delete the outputs
AzureOperationResponse deleteInputOperationResponse = client.Outputs.Delete(resourceGroupName, resourceName, outputName);
Assert.Equal(HttpStatusCode.OK, deleteInputOperationResponse.StatusCode);
deleteInputOperationResponse = client.Outputs.Delete(resourceGroupName, resourceName, outputName2);
Assert.Equal(HttpStatusCode.OK, deleteInputOperationResponse.StatusCode);
// Check that there are 0 outputs in the job
jobGetParameters = new JobGetParameters("outputs");
jobGetResponse = client.StreamingJobs.Get(resourceGroupName, resourceName, jobGetParameters);
Assert.Equal(HttpStatusCode.OK, jobGetResponse.StatusCode);
Assert.Equal(0, jobGetResponse.Job.Properties.Outputs.Count);
}
finally
{
client.StreamingJobs.Delete(resourceGroupName, resourceName);
resourceClient.ResourceGroups.Delete(resourceGroupName);
}
}
}
public void Test_JobOperations_E2E()
{
BasicDelegatingHandler handler = new BasicDelegatingHandler();
using (var undoContext = UndoContext.Current)
{
undoContext.Start();
string resourceGroupName = TestUtilities.GenerateName("StreamAnalytics");
string resourceName = TestUtilities.GenerateName("MyStreamingJobSubmittedBySDK");
string serviceLocation = TestHelper.GetDefaultLocation();
var resourceClient = TestHelper.GetResourceClient(handler);
var client = TestHelper.GetStreamAnalyticsManagementClient(handler);
try
{
ResourceGroup resourceGroup = new ResourceGroup()
{
Location = serviceLocation
};
resourceClient.ResourceGroups.CreateOrUpdate(resourceGroupName, resourceGroup);
Job job = new Job();
job.Name = resourceName;
job.Location = serviceLocation;
// Construct the general properties for JobProperties
JobProperties jobProperties = new JobProperties();
jobProperties.Sku = new Sku()
{
Name = "standard"
};
jobProperties.EventsOutOfOrderPolicy = EventsOutOfOrderPolicy.Drop;
jobProperties.EventsOutOfOrderMaxDelayInSeconds = 0;
// Construct the Input
StorageAccount storageAccount = new StorageAccount
{
AccountName = TestHelper.AccountName,
AccountKey = TestHelper.AccountKey
};
InputProperties inputProperties = new StreamInputProperties()
{
Serialization = new CsvSerialization()
{
Properties = new CsvSerializationProperties()
{
FieldDelimiter = ",",
Encoding = "UTF8"
}
},
DataSource = new BlobStreamInputDataSource()
{
Properties = new BlobStreamInputDataSourceProperties()
{
StorageAccounts = new[] { storageAccount },
Container = "state",
PathPattern = ""
}
}
};
Input input1 = new Input("inputtest")
{
Properties = inputProperties
};
jobProperties.Inputs = new[] { input1 };
// Construct the Output
OutputProperties outputProperties = new OutputProperties();
SqlAzureOutputDataSource sqlAzureOutputDataSource = new SqlAzureOutputDataSource()
{
Properties = new SqlAzureOutputDataSourceProperties()
{
Server = TestHelper.Server,
Database = TestHelper.Database,
User = TestHelper.User,
Password = TestHelper.Password,
Table = "StateInfo"
}
};
outputProperties.DataSource = sqlAzureOutputDataSource;
Output output1 = new Output("outputtest")
{
Properties = outputProperties
};
jobProperties.Outputs = new Output[] { output1 };
// Construct the transformation
Transformation transformation = new Transformation()
{
Name = "transformationtest",
Properties = new TransformationProperties()
{
Query = "Select Id, Name from inputtest",
StreamingUnits = 1
}
};
jobProperties.Transformation = transformation;
job.Properties = jobProperties;
// Construct the JobCreateProperties
JobCreateOrUpdateParameters jobCreateOrUpdateParameters = new JobCreateOrUpdateParameters();
jobCreateOrUpdateParameters.Job = job;
// Create a streaming job
JobCreateOrUpdateResponse jobCreateOrUpdateResponse = client.StreamingJobs.CreateOrUpdate(resourceGroupName, jobCreateOrUpdateParameters);
Assert.Equal(HttpStatusCode.OK, jobCreateOrUpdateResponse.StatusCode);
Assert.NotNull(jobCreateOrUpdateResponse.Job.Properties.Etag);
// Get a streaming job to check
JobGetParameters jobGetParameters = new JobGetParameters("inputs,transformation,outputs");
JobGetResponse jobGetResponse = client.StreamingJobs.Get(resourceGroupName, resourceName, jobGetParameters);
Assert.Equal(HttpStatusCode.OK, jobGetResponse.StatusCode);
Assert.Equal(serviceLocation, jobGetResponse.Job.Location);
Assert.Equal(resourceName, jobGetResponse.Job.Name);
Assert.True(jobGetResponse.Job.Properties.Inputs[0].Properties is StreamInputProperties);
StreamInputProperties streamInputProperties = jobGetResponse.Job.Properties.Inputs[0].Properties as StreamInputProperties;
Assert.Equal("Stream", jobGetResponse.Job.Properties.Inputs[0].Properties.Type);
Assert.Equal("Microsoft.Storage/Blob", streamInputProperties.DataSource.Type);
Assert.Equal("Csv", streamInputProperties.Serialization.Type);
Assert.Equal(EventsOutOfOrderPolicy.Drop, jobGetResponse.Job.Properties.EventsOutOfOrderPolicy);
Assert.NotNull(jobGetResponse.Job.Properties.Etag);
Assert.Equal(jobCreateOrUpdateResponse.Job.Properties.Etag, jobGetResponse.Job.Properties.Etag);
// Patch the streaming job
JobPatchParameters jobPatchParameters = new JobPatchParameters()
{
JobPatchRequest = new JobPatchRequest()
{
Properties = new JobProperties()
{
EventsOutOfOrderPolicy = EventsOutOfOrderPolicy.Adjust
}
}
};
var jobPatchResponse = client.StreamingJobs.Patch(resourceGroupName, resourceName, jobPatchParameters);
jobGetResponse = client.StreamingJobs.Get(resourceGroupName, resourceName, jobGetParameters);
Assert.Equal(HttpStatusCode.OK, jobPatchResponse.StatusCode);
Assert.Equal(HttpStatusCode.OK, jobGetResponse.StatusCode);
Assert.Equal(EventsOutOfOrderPolicy.Adjust, jobPatchResponse.Job.Properties.EventsOutOfOrderPolicy);
Assert.Equal(EventsOutOfOrderPolicy.Adjust, jobGetResponse.Job.Properties.EventsOutOfOrderPolicy);
JobListParameters parameters = new JobListParameters(string.Empty);
JobListResponse response = client.StreamingJobs.ListJobsInResourceGroup(resourceGroupName, parameters);
Assert.Equal(HttpStatusCode.OK, response.StatusCode);
// Start a streaming job
JobStartParameters jobStartParameters = new JobStartParameters()
{
OutputStartMode = OutputStartMode.LastOutputEventTime
};
CloudException cloudException = Assert.Throws <CloudException>(() => client.StreamingJobs.Start(resourceGroupName, resourceName, jobStartParameters));
Assert.Equal("LastOutputEventTime must be available when OutputStartMode is set to LastOutputEventTime. Please make sure at least one output event has been processed. ", cloudException.Error.Message);
jobStartParameters.OutputStartMode = OutputStartMode.CustomTime;
jobStartParameters.OutputStartTime = DateTime.Now;
AzureOperationResponse jobStartOperationResponse = client.StreamingJobs.Start(resourceGroupName, resourceName, jobStartParameters);
Assert.Equal(HttpStatusCode.OK, jobStartOperationResponse.StatusCode);
// Get a streaming job to check
jobGetResponse = client.StreamingJobs.Get(resourceGroupName, resourceName, jobGetParameters);
Assert.Equal(HttpStatusCode.OK, jobGetResponse.StatusCode);
Assert.True(IsRunning(jobGetResponse.Job.Properties.JobState));
// Check diagnostics
InputListResponse inputListResponse = client.Inputs.ListInputInJob(resourceGroupName, resourceName,
new InputListParameters("*"));
Assert.Equal(HttpStatusCode.OK, inputListResponse.StatusCode);
Assert.NotEqual(0, inputListResponse.Value.Count);
Assert.NotNull(inputListResponse.Value[0].Properties.Diagnostics);
Assert.NotEqual(0, inputListResponse.Value[0].Properties.Diagnostics.Conditions.Count);
Assert.NotNull(inputListResponse.Value[0].Properties.Diagnostics.Conditions[0].Code);
Assert.NotNull(inputListResponse.Value[0].Properties.Diagnostics.Conditions[0].Message);
// Stop a streaming job
AzureOperationResponse jobStopOperationResponse = client.StreamingJobs.Stop(resourceGroupName, resourceName);
Assert.Equal(HttpStatusCode.OK, jobStopOperationResponse.StatusCode);
// Get a streaming job to check
jobGetResponse = client.StreamingJobs.Get(resourceGroupName, resourceName, jobGetParameters);
Assert.Equal(HttpStatusCode.OK, jobGetResponse.StatusCode);
Assert.Equal(JobRunningState.Stopped, jobGetResponse.Job.Properties.JobState);
// Delete a streaming job
AzureOperationResponse jobDeleteOperationResponse = client.StreamingJobs.Delete(resourceGroupName, resourceName);
Assert.Equal(HttpStatusCode.OK, jobDeleteOperationResponse.StatusCode);
}
finally
{
client.StreamingJobs.Delete(resourceGroupName, resourceName);
resourceClient.ResourceGroups.Delete(resourceGroupName);
}
}
}
public async Task GetAndUseRefCursor()
{
// Replicate of test of https://docs.oracle.com/database/121/ODPNT/featRefCursor.htm#ODPNT319
// create table and procedure:
/*
* connect scott/tiger@oracle
* create table test (col1 number);
* insert into test(col1) values (1);
* commit;
*
* create or replace package testPkg as type empCur is REF Cursor;
* end testPkg;
* /
*
* create or replace procedure testSP(param1 IN testPkg.empCur, param2 OUT NUMBER)
* as
* begin
* FETCH param1 into param2;
* end;
* /
*/
// Note this kind of usage of ref cursors don't work in frends. When run in frends task doesn't accept ref cursors as input parameters.
//////////////////////////////////////////////////
/// Get refcursor
var oracleParam = new OracleParametersForTask
{
DataType = OracleParametersForTask.ParameterDataType.RefCursor,
Name = "outRefPrm",
Value = DBNull.Value,
Size = 0
};
var output = new OutputProperties
{
DataReturnType = OracleCommandReturnType.Parameters
};
var input = new Input
{
ConnectionString = connectionString,
CommandOrProcedureName = "begin open :1 for select col1 from test; end;",
CommandType = OracleCommandType.Command,
BindParametersByName = false,
TimeoutSeconds = 60
};
output.OutputParameters = new OracleParametersForTask[1];
output.OutputParameters[0] = oracleParam;
var result = await ExecuteCommand.Execute(input, output, _taskOptions);
//////////////////////////////////////////////////
/// Use refcursor
var secondInput = new Input
{
ConnectionString = connectionString,
CommandOrProcedureName = "testSP",
CommandType = OracleCommandType.StoredProcedure,
InputParameters = new OracleParametersForTask[1],
BindParametersByName = false,
TimeoutSeconds = 60
};
OracleParametersForTask secondInputParameters = new OracleParametersForTask
{
DataType = OracleParametersForTask.ParameterDataType.RefCursor,
Name = "param1",
Value = result.Result[0].Value,
Size = 0
};
secondInput.InputParameters[0] = secondInputParameters;
var secondOutputParameters = new OracleParametersForTask
{
DataType = OracleParametersForTask.ParameterDataType.Int32,
Name = "param2",
Value = DBNull.Value,
Size = 0
};
var secondOutput = new OutputProperties
{
OutputParameters = new OracleParametersForTask[1],
DataReturnType = OracleCommandReturnType.XmlString
};
secondOutput.OutputParameters[0] = secondOutputParameters;
var secondResult = await ExecuteCommand.Execute(secondInput, secondOutput, _taskOptions);
Assert.AreEqual("<Root>\r\n <param2>1</param2>\r\n</Root>", secondResult.Result);
}