/// <summary>
/// Get JSON format data from SQL command
/// </summary>
/// <param name="cmd">SQL command</param>
/// <returns>An "application/json" format of data</returns>
public static string SQLToJSON(SqlCommand cmd)
{
if (cmd == null)
{
return(ToolsJson.EmptyJsonList);
}
var res = new StringBuilder("[");
try
{
cmd.Connection.Open();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
bool first = true;
while (rdr.Read())
{
if (first)
{
first = false;
}
else
{
res.Append(",");
}
res.Append(Environment.NewLine);
res.Append(" {");
for (int x = 0; x < rdr.FieldCount; x++)
{
if (x > 0)
{
res.Append(",");
}
res.Append(Environment.NewLine);
res.AppendFormat(
CultureInfo.InvariantCulture,
@" ""{0}"":",
rdr.GetName(x));
string fieldType = rdr.GetFieldType(x).ToString().ToUpperInvariant();
if (rdr.IsDBNull(x))
{
res.Append("null");
}
else
{
switch (fieldType)
{
case "SYSTEM.BOOLEAN":
res.AppendFormat(
CultureInfo.InvariantCulture,
@"{0}",
rdr.GetBoolean(x) ? ConstantValue.True : ConstantValue.False);
break;
case "SYSTEM.INT16":
case "SYSTEM.INT32":
case "SYSTEM.INT64":
case "SYSTEM.DECIMAL":
res.AppendFormat(
CultureInfo.InvariantCulture,
@"{0}",
rdr[x]);
break;
case "SYSTEM.STRING":
default:
res.AppendFormat(
CultureInfo.InvariantCulture,
@"""{0}""",
ToolsJson.JsonCompliant(rdr[x].ToString()));
break;
}
}
}
res.Append(Environment.NewLine);
res.Append(" }");
}
}
res.Append("]");
}
catch (Exception ex)
{
ExceptionManager.LogException(ex, "OpenFramework.Basics.SQLToJSON(" + cmd.CommandText + ")");
return(ex.Message);
}
finally
{
if (cmd.Connection.State != ConnectionState.Closed)
{
cmd.Connection.Close();
}
}
return(res.ToString());
}
/// <summary>Get CSV format data from SQL command</summary>
/// <param name="cmd">SQL command</param>
/// <returns>Plain text in CSV format</returns>
public static string SqlToCSV(SqlCommand cmd)
{
if (cmd == null)
{
return(string.Empty);
}
var res = new StringBuilder();
try
{
cmd.Connection.Open();
using (var rdr = cmd.ExecuteReader())
{
bool first = true;
while (rdr.Read())
{
if (first)
{
first = false;
for (int x = 0; x < rdr.FieldCount; x++)
{
res.AppendFormat(CultureInfo.InvariantCulture, @"{0};", rdr.GetName(x));
}
}
res.Append(Environment.NewLine);
for (int x = 0; x < rdr.FieldCount; x++)
{
string fieldType = rdr.GetFieldType(x).ToString().ToUpperInvariant();
if (rdr.IsDBNull(x))
{
res.Append(";");
}
else
{
switch (fieldType)
{
case "SYSTEM.BOOLEAN":
res.AppendFormat(
CultureInfo.InvariantCulture,
@"{0};",
rdr.GetBoolean(x) ? ConstantValue.True : ConstantValue.False);
break;
case "SYSTEM.INT16":
case "SYSTEM.INT32":
case "SYSTEM.INT64":
case "SYSTEM.DECIMAL":
res.AppendFormat(
CultureInfo.InvariantCulture,
@"{0};",
rdr[x]);
break;
case "SYSTEM.STRING":
default:
res.AppendFormat(
CultureInfo.InvariantCulture,
@"""{0}"";",
rdr[x]);
break;
}
}
}
}
}
}
catch (FormatException ex)
{
ExceptionManager.LogException(ex, "OpenFramework.core.Tools.SqlToCsv(" + cmd.CommandText + ")");
return(ex.Message);
}
catch (NullReferenceException ex)
{
ExceptionManager.LogException(ex, "OpenFramework.core.Tools.SqlToCsv(" + cmd.CommandText + ")");
return(ex.Message);
}
catch (InvalidCastException ex)
{
ExceptionManager.LogException(ex, "OpenFramework.core.Tools.SqlToCsv(" + cmd.CommandText + ")");
return(ex.Message);
}
catch (NotSupportedException ex)
{
ExceptionManager.LogException(ex, "OpenFramework.core.Tools.SqlToCsv(" + cmd.CommandText + ")");
return(ex.Message);
}
finally
{
if (cmd.Connection.State != ConnectionState.Closed)
{
cmd.Connection.Close();
}
}
return(res.ToString());
}
