/// <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()); }