public void GenerateJsonData_WhenGivenEmptyDictionary_ReturnsParenthesis()
{
// 1. Arrange
var input = new Dictionary <string, string>();
var expected = "{}";
// 2. Act
var actual = SqlHelpers.GenerateJsonData(input);
// 3. Assert
Assert.AreEqual(expected, actual);
}
public void GenerateJsonData_WhenGivenDictionaryWithSingleEntry_ReturnsCorrectResult()
{
// 1. Arrange
var input = new Dictionary <string, string>();
input.Add("name", "wekoslav");
var expected = "{\"name\": \"wekoslav\"}";
// 2. Act
var actual = SqlHelpers.GenerateJsonData(input);
// 3. Assert
Assert.AreEqual(expected, actual);
}
public void GenerateJsonData_WhenGivenDictionaryWithThreeEntries_ReturnsCorrectResult()
{
// 1. Arrange
var input = new Dictionary <string, string>();
input.Add("firstName", "wekoslav");
input.Add("lastName", "stefanovski");
input.Add("age", "42");
var expected = "{\"firstName\": \"wekoslav\",\"lastName\": \"stefanovski\",\"age\": \"42\"}";
// 2. Act
var actual = SqlHelpers.GenerateJsonData(input);
// 3. Assert
Assert.AreEqual(expected, actual);
}
public async Task <Response> GetResponse()
{
using (var cnn = new SqlConnection(ConnectionString))
{
cnn.Open();
// !!!AGAIN, THIS IS WIDE OPEN TO SQL INJECTION, USE A DYNAMIC SQL LIBRARY IF YOU REALLY NEED THIS KIND OF CODE!!!
var sql = $@"Select * from {TableName}";
var filterClauses = QueryParams.GetAllParams().Select(param => $"{param.Key} like '{param.Value}%'");
var whereClause = string.Join(" and ", filterClauses);
if (!string.IsNullOrEmpty(whereClause))
{
sql = $"{sql} where {whereClause}";
}
using (var command = new SqlCommand(sql, cnn))
{
using (var dr = await command.ExecuteReaderAsync())
{
var schema = dr.GetColumnSchema();
var columnNames = schema.Select(cs => cs.ColumnName);
object[] values = new object[columnNames.Count()];
var results = new List <Dictionary <string, string> >();
while (dr.Read())
{
var columnCount = dr.GetValues(values);
var objectDictionary = new Dictionary <string, string>();
foreach (var column in schema)
{
objectDictionary.Add(column.ColumnName, dr.GetValue(column.ColumnOrdinal.Value).ToString());
}
results.Add(objectDictionary);
}
var body = SqlHelpers.GenerateJsonData(results);
return(new Response
{
ContentType = ContentTypes.JsonApplication,
ResponseCode = ResponseCode.Ok,
Type = ResponseType.Text,
Body = body
});
}
}
}
}
public async Task <Response> GetResponse()
{
using (var cnn = new SqlConnection(ConnectionString))
{
cnn.Open();
using (var command = new SqlCommand($@"Select * from {TableName}", cnn))
{
using (var dr = await command.ExecuteReaderAsync())
{
var schema = dr.GetColumnSchema();
var columnNames = schema.Select(cs => cs.ColumnName);
object[] values = new object[columnNames.Count()];
var results = new List <Dictionary <string, string> >();
while (dr.Read())
{
var columnCount = dr.GetValues(values);
var objectDictionary = new Dictionary <string, string>();
foreach (var column in schema)
{
objectDictionary.Add(column.ColumnName, dr.GetValue(column.ColumnOrdinal.Value).ToString());
}
results.Add(objectDictionary);
}
var body = SqlHelpers.GenerateJsonData(results);
return(new Response
{
ContentType = ContentTypes.JsonApplication,
ResponseCode = ResponseCode.Ok,
Type = ResponseType.Text,
Body = body
});
}
}
}
}