public static string CreateJsonStringArrayFromStoredProc(string storedProc, DuplicateColumnHandling duplicateColumnHandling = DuplicateColumnHandling.None)
{
using (SqlConnection conn = DbConnectionFactory.CreateSqlConnection())
{
return(CreateJsonStringArrayFromStoredProc(conn, storedProc, null, duplicateColumnHandling));
}
}
public static JArray CreateJsonArrayFromSqlReader(SqlDataReader reader, DuplicateColumnHandling duplicateColumnHandling = DuplicateColumnHandling.None)
{
JArray jsonArray = new JArray();
DataTable columnData = reader.GetSchemaTable();
while (reader.Read())
{
BaseCreateJsonArrayFromSqlReader(reader, jsonArray, columnData, duplicateColumnHandling);
}
return(jsonArray);
}
public static JObject CreateJsonFromSqlReader(SqlDataReader reader, DuplicateColumnHandling duplicateColumnHandling = DuplicateColumnHandling.None)
{
JObject json = new JObject();
DataTable columnData = reader.GetSchemaTable();
if (reader.Read())
{
for (int col = 0; col < reader.FieldCount; ++col)
{
DataRow row = columnData.Rows[col];
string key = Convert.ToString(row.ItemArray[0]);
var jToken = JToken.FromObject(reader[col]);
var val = jToken.ToString().Contains("\"Data\": null") && jToken.ToString().Contains("\"AssemblyName\": \"\"") ? null : jToken;
switch (duplicateColumnHandling)
{
case DuplicateColumnHandling.Ignore:
{
JToken value;
if (!json.TryGetValue(key, out value))
{
json.Add(Convert.ToString(row.ItemArray[0]), val);
}
}
break;
case DuplicateColumnHandling.Overwrite:
json[key] = val;
break;
default:
json.Add(Convert.ToString(row.ItemArray[0]), val);
break;
}
}
}
else
{
return(null);
}
return(json);
}
public static JArray CreateJsonArrayFromStoredProc(SqlConnection conn, string storedProc, NameValueCollection nvc, DuplicateColumnHandling duplicateColumnHandling = DuplicateColumnHandling.None, SqlTransaction xact = null)
{
using (SqlCommand cmd = new SqlCommand()
{
Connection = conn, Transaction = xact, CommandType = CommandType.StoredProcedure, CommandText = storedProc
})
{
SqlUtils.BuildCommandParameters(cmd, nvc);
using (SqlDataReader reader = cmd.ExecuteReader())
{
return(CreateJsonArrayFromSqlReader(reader, duplicateColumnHandling));
}
}
}
public static JArray CreateJsonArrayFromStoredProc(string storedProc, NameValueCollection nvc, DuplicateColumnHandling duplicateColumnHandling = DuplicateColumnHandling.None, SqlTransaction xact = null)
{
using (SqlConnection conn = DbConnectionFactory.CreateSqlConnection())
{
return(CreateJsonArrayFromStoredProc(conn, storedProc, nvc, duplicateColumnHandling, xact));
}
}
public static JArray CreateJsonArrayFromStoredProc(SqlConnection conn, string storedProc, DuplicateColumnHandling duplicateColumnHandling = DuplicateColumnHandling.None, SqlTransaction xact = null)
{
return(CreateJsonArrayFromStoredProc(conn, storedProc, null, duplicateColumnHandling, xact));
}
public static JObject CreateJsonFromStoredProc(string storedProc, NameValueCollection nvc, DuplicateColumnHandling duplicateColumnHandling = DuplicateColumnHandling.None)
{
using (SqlConnection conn = DbConnectionFactory.CreateSqlConnection())
{
return(CreateJsonFromStoredProc(conn, storedProc, nvc, duplicateColumnHandling));
}
}
public static JObject CreateJsonFromStoredProc(SqlConnection conn, string storedProc, DuplicateColumnHandling duplicateColumnHandling = DuplicateColumnHandling.None)
{
return(CreateJsonFromStoredProc(conn, storedProc, null, duplicateColumnHandling));
}
public static string CreateJsonStringArrayFromSqlReader(SqlDataReader reader, DuplicateColumnHandling duplicateColumnHandling = DuplicateColumnHandling.None)
{
StringBuilder sb = new StringBuilder("[");
DataTable columnData = reader.GetSchemaTable();
HashSet <string> keys = new HashSet <string>();
bool firstObject = true;
while (reader.Read())
{
keys.Clear();
if (firstObject)
{
sb.Append("{");
}
else
{
sb.Append(",");
sb.AppendLine();
sb.Append("{");
}
firstObject = false;
List <KeyValuePair <string, object> > values = new List <KeyValuePair <string, object> >();
for (int col = 0; col < reader.FieldCount; ++col)
{
DataRow row = columnData.Rows[col];
string key = Convert.ToString(row.ItemArray[0]);
switch (duplicateColumnHandling)
{
case DuplicateColumnHandling.Ignore:
if (keys.Contains(key))
{
continue;
}
values.Add(new KeyValuePair <string, object>(key, reader[col]));
keys.Add(key);
break;
case DuplicateColumnHandling.Overwrite:
if (keys.Contains(key))
{
values.Remove(values.FirstOrDefault(v => v.Key == key));
values.Add(new KeyValuePair <string, object>(key, reader[col]));
continue;
}
values.Add(new KeyValuePair <string, object>(key, reader[col]));
keys.Add(key);
break;
default:
if (keys.Contains(key))
{
throw new DuplicateNameException(string.Format("Cannot add duplicate key [{0}] to JSON; Value = {1}", key, reader[col]));
}
values.Add(new KeyValuePair <string, object>(key, reader[col]));
keys.Add(key);
break;
}
}
if (values.Any())
{
bool firstValue = true;
foreach (var kvp in values)
{
if (!firstValue)
{
sb.Append(",");
}
firstValue = false;
sb.AppendFormat("\"{0}\":{1}", EscapeString(kvp.Key), FormatJsonValueFromDB(kvp.Value));
}
}
sb.Append("}");
}
sb.Append("]");
return(sb.ToString());
}
private static void BaseCreateJsonArrayFromSqlReader(SqlDataReader reader, JArray jsonArray, DataTable columnData, DuplicateColumnHandling duplicateColumnHandling = DuplicateColumnHandling.None)
{
JObject json = new JObject();
for (int col = 0; col < reader.FieldCount; ++col)
{
DataRow row = columnData.Rows[col];
string key = Convert.ToString(row.ItemArray[0]);
var jToken = JToken.FromObject(reader[col]);
var val = jToken.ToString().Contains("\"Data\": null") && jToken.ToString().Contains("\"AssemblyName\": \"\"") ? null : jToken;
switch (duplicateColumnHandling)
{
case DuplicateColumnHandling.Ignore:
{
JToken value;
if (!json.TryGetValue(key, out value))
{
json.Add(Convert.ToString(row.ItemArray[0]), val);
}
}
break;
case DuplicateColumnHandling.Overwrite:
json[key] = val;
break;
default:
json.Add(Convert.ToString(row.ItemArray[0]), val);
break;
}
}
// only add it to the array if there was some data
if (json.Count > 0)
{
jsonArray.Add(json);
}
}