public void ValidateCreateQueryDefinition()
{
string query = "select * from s where s.Account = @account";
string paramName = "@account";
string paramValue = "12345";
QueryDefinition sqlQueryDefinition = new QueryDefinition(query)
.WithParameter(paramName, paramValue);
SqlQuerySpec sqlQuerySpec = sqlQueryDefinition.ToSqlQuerySpec();
Assert.AreEqual(query, sqlQuerySpec.QueryText);
Assert.AreEqual(1, sqlQuerySpec.Parameters.Count);
SqlParameter sqlParameter = sqlQuerySpec.Parameters.First();
Assert.AreEqual(paramName, sqlParameter.Name);
Assert.AreEqual(paramValue, sqlParameter.Value);
string newParamValue = "9001";
sqlQueryDefinition.WithParameter(paramName, newParamValue);
sqlQuerySpec = sqlQueryDefinition.ToSqlQuerySpec();
Assert.AreEqual(query, sqlQuerySpec.QueryText);
Assert.AreEqual(1, sqlQuerySpec.Parameters.Count);
sqlParameter = sqlQuerySpec.Parameters.First();
Assert.AreEqual(paramName, sqlParameter.Name);
Assert.AreEqual(newParamValue, sqlParameter.Value);
query = "select * from s where s.Account = @account and s.Name = @name";
SqlParameterCollection sqlParameters = new SqlParameterCollection();
sqlParameters.Add(new SqlParameter("@account", "12345"));
sqlParameters.Add(new SqlParameter("@name", "ABC"));
sqlQuerySpec = new SqlQuerySpec(query, sqlParameters);
sqlQueryDefinition = QueryDefinition.CreateFromQuerySpec(sqlQuerySpec);
Assert.AreEqual(sqlQueryDefinition.QueryText, sqlQuerySpec.QueryText);
Assert.AreEqual(sqlQueryDefinition.ToSqlQuerySpec().QueryText, sqlQueryDefinition.QueryText);
Assert.AreEqual(sqlQueryDefinition.ToSqlQuerySpec().Parameters.Count(), sqlQuerySpec.Parameters.Count());
Assert.AreEqual(sqlQueryDefinition.ToSqlQuerySpec().Parameters.First().Name, sqlQuerySpec.Parameters.First().Name);
Assert.AreEqual(sqlQueryDefinition.ToSqlQuerySpec().Parameters.First().Value, sqlQuerySpec.Parameters.First().Value);
}
public QueryDefinition ToQueryDefinition(IDictionary <object, string> parameters = null)
{
SqlQuerySpec querySpec = DocumentQueryEvaluator.Evaluate(this.Expression, this.serializationOptions, parameters);
return(QueryDefinition.CreateFromQuerySpec(querySpec));
}
public void QueryDefinitionSerializationTest()
{
Action <string, SqlQuerySpec> verifyJsonSerialization = (expectedText, query) =>
{
QueryDefinition queryDefinition = QueryDefinition.CreateFromQuerySpec(query);
string actualText = JsonConvert.SerializeObject(queryDefinition);
Assert.AreEqual(expectedText, actualText);
QueryDefinition otherQuery = JsonConvert.DeserializeObject <QueryDefinition>(actualText);
string otherText = JsonConvert.SerializeObject(otherQuery);
Assert.AreEqual(expectedText, otherText);
};
Action <string> verifyJsonSerializationText = (text) =>
{
QueryDefinition query = JsonConvert.DeserializeObject <QueryDefinition>(text);
string otherText = JsonConvert.SerializeObject(query);
Assert.AreEqual(text, otherText);
};
// Verify serialization
verifyJsonSerialization("{\"query\":\"SELECT 1\"}", new SqlQuerySpec("SELECT 1"));
verifyJsonSerialization("{\"query\":\"SELECT 1\",\"parameters\":[" +
"{\"name\":\"@p1\",\"value\":5}" +
"]}",
new SqlQuerySpec()
{
QueryText = "SELECT 1",
Parameters = new SqlParameterCollection()
{
new SqlParameter("@p1", 5)
}
});
verifyJsonSerialization("{\"query\":\"SELECT 1\",\"parameters\":[" +
"{\"name\":\"@p1\",\"value\":true}" +
"]}",
new SqlQuerySpec()
{
QueryText = "SELECT 1",
Parameters = new SqlParameterCollection()
{
new SqlParameter("@p1", 5), new SqlParameter("@p1", true)
}
});
verifyJsonSerialization("{\"query\":\"SELECT 1\",\"parameters\":[" +
"{\"name\":\"@p1\",\"value\":\"abc\"}" +
"]}",
new SqlQuerySpec()
{
QueryText = "SELECT 1",
Parameters = new SqlParameterCollection()
{
new SqlParameter("@p1", "abc")
}
});
verifyJsonSerialization("{\"query\":\"SELECT 1\",\"parameters\":[" +
"{\"name\":\"@p1\",\"value\":[1,2,3]}" +
"]}",
new SqlQuerySpec()
{
QueryText = "SELECT 1",
Parameters = new SqlParameterCollection()
{
new SqlParameter("@p1", new int[] { 1, 2, 3 })
}
});
verifyJsonSerialization("{\"query\":\"SELECT 1\",\"parameters\":[" +
"{\"name\":\"@p1\",\"value\":{\"a\":[1,2,3]}}" +
"]}",
new SqlQuerySpec()
{
QueryText = "SELECT 1",
Parameters = new SqlParameterCollection()
{
new SqlParameter("@p1", JObject.Parse("{\"a\":[1,2,3]}"))
}
});
verifyJsonSerialization("{\"query\":\"SELECT 1\",\"parameters\":[" +
"{\"name\":\"@p1\",\"value\":{\"a\":[1,2,3]}}" +
"]}",
new SqlQuerySpec()
{
QueryText = "SELECT 1",
Parameters = new SqlParameterCollection()
{
new SqlParameter("@p1", new JRaw("{\"a\":[1,2,3]}"))
}
});
// Verify roundtrips
verifyJsonSerializationText("{\"query\":\"SELECT 1\"}");
verifyJsonSerializationText(
"{" +
"\"query\":\"SELECT 1\"," +
"\"parameters\":[" +
"{\"name\":\"@p1\",\"value\":null}" +
"]" +
"}");
verifyJsonSerializationText(
"{" +
"\"query\":\"SELECT 1\"," +
"\"parameters\":[" +
"{\"name\":\"@p1\",\"value\":true}" +
"]" +
"}");
verifyJsonSerializationText(
"{" +
"\"query\":\"SELECT 1\"," +
"\"parameters\":[" +
"{\"name\":\"@p1\",\"value\":false}" +
"]" +
"}");
verifyJsonSerializationText(
"{" +
"\"query\":\"SELECT 1\"," +
"\"parameters\":[" +
"{\"name\":\"@p1\",\"value\":123}" +
"]" +
"}");
verifyJsonSerializationText(
"{" +
"\"query\":\"SELECT 1\"," +
"\"parameters\":[" +
"{\"name\":\"@p1\",\"value\":\"abc\"}" +
"]" +
"}");
verifyJsonSerializationText(
"{" +
"\"query\":\"SELECT 1\"," +
"\"parameters\":[" +
"{\"name\":\"@p1\",\"value\":{\"a\":[1,2,\"abc\"]}}" +
"]" +
"}");
}
