public void CommandUsesSessionContext()
{
// Arrange
string key = Guid.NewGuid().ToString();
string value = Guid.NewGuid().ToString().Replace('-', '_');
var sut = new Belgrade.SqlClient.SqlDb.Command(Util.Settings.MasterConnectionString).AddRls(key, () => value);
var sql = string.Format(
@"select cast(SESSION_CONTEXT(N'{0}') as varchar(50)) as sc
into #temp;
select sc from #temp
for json path, include_null_values, without_array_wrapper", key);
string result = null;
// Action
sut.Sql(sql).Map(reader => result = reader.GetString(0)).Wait();
// Assert
Assert.Equal("{\"sc\":\"" + value + "\"}", result);
}
public async Task ReturnsJson([CombinatorialValues("stream", "writer", "mapper", "command")] string client,
[CombinatorialValues(1, 50, 10000)] string top,
[CombinatorialValues("auto", "path")] string mode1,
[CombinatorialValues(",include_null_values", ",root('test')", ",root")] string mode2,
[CombinatorialValues(true, false)] bool useCommand,
[CombinatorialValues(true, false)] bool useAsync,
[CombinatorialValues("1", null)] string defaultValue,
[CombinatorialValues("[/]", "{\"a\":/}", "/")] string wrapper,
[CombinatorialValues("", "test")] string sessionContext1,
[CombinatorialValues("TEST", null)] string sessionContext2
)
{
// Arrange
bool isExceptionThrown = false;
var pipe = new Belgrade.SqlClient.SqlDb.QueryPipe(Util.Settings.MasterConnectionString)
.AddContextVariable("v1", () => sessionContext1)
.AddContextVariable("v2", () => sessionContext2)
.OnError(ex => { isExceptionThrown = true; Console.WriteLine(ex); });
var mapper = new Belgrade.SqlClient.SqlDb.QueryMapper(Util.Settings.MasterConnectionString)
.AddContextVariable("v1", () => sessionContext1)
.AddContextVariable("v2", () => sessionContext2)
.OnError(ex => { isExceptionThrown = true; Console.WriteLine(ex); });
var command = new Belgrade.SqlClient.SqlDb.Command(Util.Settings.MasterConnectionString)
.AddContextVariable("v1", () => sessionContext1)
.AddContextVariable("v2", () => sessionContext2)
.OnError(ex => { isExceptionThrown = true; Console.WriteLine(ex); });
var sql = @"select top " + top + @" v1 = cast(SESSION_CONTEXT(N'v1') as varchar(500)),
v2 = cast(SESSION_CONTEXT(N'v2') as varchar(500)),
o.object_id tid, o.name t_name, o.schema_id, o.type t, o.type_desc, o.create_date cd, o.modify_date md,
c.*
from sys.objects o, sys.columns c for json " + mode1 + mode2;
string json = "INVALID JSON";
var pair = wrapper.Split('/');
string prefix = pair[0];
string suffix = pair[1];
Task t = null;
// Action
if (client == "stream")
{
using (MemoryStream ms = new MemoryStream())
{
if (useCommand)
{
t = pipe.Sql(new SqlCommand(sql)).Stream(ms);
}
else
{
t = pipe.Sql(sql).Stream(ms);
}
if (useAsync)
{
await t;
}
else
{
t.Wait();
}
ms.Position = 0;
json = new StreamReader(ms).ReadToEnd();
}
}
else if (client == "writer")
{
using (var sw = new StringWriter())
{
if (useCommand)
{
t = pipe.Sql(new SqlCommand(sql)).Stream(sw, new Options()
{
Prefix = prefix, DefaultOutput = defaultValue, Suffix = suffix
});
}
else
{
t = pipe.Sql(sql).Stream(sw, new Options()
{
Prefix = prefix, DefaultOutput = defaultValue, Suffix = suffix
});
}
if (useAsync)
{
await t;
}
else
{
t.Wait();
}
json = sw.ToString();
}
}
else if (client == "command")
{
using (MemoryStream ms = new MemoryStream())
{
if (useCommand)
{
t = command.Sql(new SqlCommand(sql)).Stream(ms, "");
}
else
{
t = command.Sql(sql).Stream(ms, "");
}
if (useAsync)
{
await t;
}
else
{
t.Wait();
}
ms.Position = 0;
json = new StreamReader(ms).ReadToEnd();
}
}
else
{
if (!useAsync)
{
return;
}
if (useCommand)
{
json = await mapper.GetString(new SqlCommand(sql));
}
else
{
json = await mapper.GetString(sql);
}
}
// Assert
if (isExceptionThrown)
{
return;
}
AssertEx.IsValidJson(json);
if (json.StartsWith("["))
{
string v1 = null;
var b = JArray.Parse(json).SelectTokens("..v1");
var f = b.First();
v1 = (string)f;
Assert.Equal(sessionContext1, v1);
}
else
{
string v1 = null;
var a = JObject.Parse(json);
var b = a.SelectTokens("..v1");
var f = b.First();
v1 = (string)f;
Assert.Equal(sessionContext1, v1);
}
}