/// <summary>
/// Converts a result set returned from SQL Server into JSON formatted array of objects and values
/// </summary>
/// <param name="row">A SQL row return from any SQL Set-based operation</param>
/// <param name="ItemID">The unique identifier for the row. The row key.</param>
/// <param name="Json">The output result as a JSON formatted string</param>
public static void StringifiedRows(object row, out SqlGuid ItemID, out SqlString Json)
{
//this method receives the 'row' object returned from the JsonToTable function above and inserts
//each object enumeration into the column(s) declaration below:
StringifiedRow col = (StringifiedRow)row;
ItemID = (SqlGuid)(col.ItemID);
Json = (SqlString)(col.Json);
}
public static IEnumerable SqlStringifyRow2(SqlGuid docid, SqlString query)
{
ArrayList srows = new ArrayList();
StringBuilder json = new StringBuilder();
String injerr = blockSqlInjection(query.Value.ToLower());
if (!String.IsNullOrEmpty(injerr))
{
StringifiedRow row = new StringifiedRow();
row.Load(docid.Value, injerr);
srows.Add(row);
return(srows);
}
using (SqlConnection cn = new SqlConnection("context connection = true"))
{
cn.Open();
SqlCommand command = new SqlCommand(query.ToString(), cn);
SqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
json.Append("{");//begin object(row)
for (Int32 i = 0; i < dr.FieldCount; i++)
{
if (!dr.GetValue(i).Equals(DBNull.Value))
{
String kv = JsonFormat(dr, i);
if (!String.IsNullOrEmpty(kv))
{
json.AppendFormat("{0},", kv);
}
}
}
json.Remove(json.Length - 1, 1); //remove the trailing comma
json.Append("}"); //end object(row)
StringifiedRow row = new StringifiedRow();
row.Load(docid.Value, json.ToString());
srows.Add(row);
}
}
return(srows);
}
public static IEnumerable SqlStringifyRow(String objName, String pKeyName, SqlGuid pKeyValue, String cols, String where, String misc)
{
String[] objrx = new String[] { "." };
String[] obj = objName.Split(objrx, StringSplitOptions.RemoveEmptyEntries);
ArrayList srows = new ArrayList();
StringBuilder json = new StringBuilder();
String injerr = blockSqlInjection(obj, cols);
if (!String.IsNullOrEmpty(injerr))
{
StringifiedRow row = new StringifiedRow();
row.Load(pKeyValue.Value, injerr);
srows.Add(row);
return(srows);
}
StringBuilder sql = new StringBuilder();
sql.AppendFormat("SELECT {0} ", cols.StartsWith("@") ? cols.Substring(1) : String.IsNullOrEmpty(cols) ? "*" : cols);
sql.AppendFormat("FROM {0} ", objName);
sql.AppendFormat("WHERE {0} = '{1}'", pKeyName, pKeyValue);
if (!String.IsNullOrEmpty(where))
{
sql.AppendFormat(" AND ({0})", where);
}
if (!String.IsNullOrEmpty(misc))
{
sql.AppendFormat(" {0} ", misc);
}
using (SqlConnection cn = new SqlConnection("context connection = true"))
{
cn.Open();
SqlCommand command = new SqlCommand(sql.ToString(), cn);
SqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
json.Append("{");//begin object(row)
if (cols.StartsWith("@"))
{
json.AppendFormat("{0},", dr.GetValue(0));
}
else
{
for (Int32 i = 0; i < dr.FieldCount; i++)
{
if (!dr.GetValue(i).Equals(DBNull.Value))
{
String kv = JsonFormat(dr, i);
if (!String.IsNullOrEmpty(kv))
{
json.AppendFormat("{0},", kv);
}
}
}
json.Remove(json.Length - 1, 1); //remove the trailing comma
json.Append("}"); //end object(row)
}
StringifiedRow row = new StringifiedRow();
row.Load(pKeyValue.Value, json.ToString());
srows.Add(row);
}
}
return(srows);
}
public static IEnumerable SqlStringifyRow2(SqlGuid docid, SqlString query)
{
ArrayList srows = new ArrayList();
StringBuilder json = new StringBuilder();
String injerr = blockSqlInjection(query.Value.ToLower());
if (!String.IsNullOrEmpty(injerr))
{
StringifiedRow row = new StringifiedRow();
row.Load(docid.Value, injerr);
srows.Add(row);
return srows;
}
using (SqlConnection cn = new SqlConnection("context connection = true"))
{
cn.Open();
SqlCommand command = new SqlCommand(query.ToString(), cn);
SqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
json.Append("{");//begin object(row)
for (Int32 i = 0; i < dr.FieldCount; i++)
{
if (!dr.GetValue(i).Equals(DBNull.Value))
{
String kv = JsonFormat(dr, i);
if (!String.IsNullOrEmpty(kv))
{
json.AppendFormat("{0},", kv);
}
}
}
json.Remove(json.Length - 1, 1);//remove the trailing comma
json.Append("}");//end object(row)
StringifiedRow row = new StringifiedRow();
row.Load(docid.Value, json.ToString());
srows.Add(row);
}
}
return srows;
}
public static IEnumerable SqlStringifyRow(String objName, String pKeyName, SqlGuid pKeyValue, String cols, String where, String misc)
{
String[] objrx = new String[] { "." };
String[] obj = objName.Split(objrx, StringSplitOptions.RemoveEmptyEntries);
ArrayList srows = new ArrayList();
StringBuilder json = new StringBuilder();
String injerr = blockSqlInjection(obj, cols);
if (!String.IsNullOrEmpty(injerr))
{
StringifiedRow row = new StringifiedRow();
row.Load(pKeyValue.Value, injerr);
srows.Add(row);
return srows;
}
StringBuilder sql = new StringBuilder();
sql.AppendFormat("SELECT {0} ", cols.StartsWith("@") ? cols.Substring(1) : String.IsNullOrEmpty(cols) ? "*" : cols);
sql.AppendFormat("FROM {0} ", objName);
sql.AppendFormat("WHERE {0} = '{1}'", pKeyName, pKeyValue);
if(!String.IsNullOrEmpty(where))
sql.AppendFormat(" AND ({0})", where);
if (!String.IsNullOrEmpty(misc))
sql.AppendFormat(" {0} ", misc);
using (SqlConnection cn = new SqlConnection("context connection = true"))
{
cn.Open();
SqlCommand command = new SqlCommand(sql.ToString(), cn);
SqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
json.Append("{");//begin object(row)
if (cols.StartsWith("@"))
{
json.AppendFormat("{0},", dr.GetValue(0));
}
else
{
for (Int32 i = 0; i < dr.FieldCount; i++)
{
if (!dr.GetValue(i).Equals(DBNull.Value))
{
String kv = JsonFormat(dr, i);
if (!String.IsNullOrEmpty(kv))
{
json.AppendFormat("{0},", kv);
}
}
}
json.Remove(json.Length - 1, 1);//remove the trailing comma
json.Append("}");//end object(row)
}
StringifiedRow row = new StringifiedRow();
row.Load(pKeyValue.Value, json.ToString());
srows.Add(row);
}
}
return srows;
}