/// <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 SQLStreamList(SqlCommand cmd) { if (cmd == null) { return(ToolsJson.EmptyJsonList); } var res = new StringBuilder(); try { cmd.Connection.Open(); using (SqlDataReader rdr = cmd.ExecuteReader()) { if (rdr.HasRows) { res.Append("["); var first = true; while (rdr.Read()) { if (first) { first = false; } else { res.Append(","); } res.AppendFormat(CultureInfo.InvariantCulture, @"{{{0}}}", rdr.GetString(0)); } res.Append("]"); } } } catch (Exception ex) { ExceptionManager.LogException(ex, "OpenFramework.Basics.SQLToJSONSimple(" + cmd.CommandText + ")"); return(ex.Message); } finally { if (cmd.Connection.State != ConnectionState.Closed) { cmd.Connection.Close(); } } return(res.ToString()); }
public void ObtainInstanceLanguages() { string source = string.Format(CultureInfo.InvariantCulture, "Instance::ObtainInstanceLanguages({0})", this.Id); this.languages = new List <Language>(); using (SqlCommand cmd = new SqlCommand("Instance_GetLanguages")) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(DataParameter.Input("@InstanceId", this.Id)); using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["Security"].ConnectionString)) { cmd.Connection = cnn; try { cmd.Connection.Open(); using (SqlDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { this.languages.Add(LanguagePersistence.LanguageById(rdr.GetInt64(1))); } } } catch (NullReferenceException ex) { ExceptionManager.LogException(ex as Exception, source); } catch (ArgumentNullException ex) { ExceptionManager.LogException(ex as Exception, source); } catch (ArgumentException ex) { ExceptionManager.LogException(ex as Exception, source); } catch (FormatException ex) { ExceptionManager.LogException(ex as Exception, source); } catch (SqlException ex) { ExceptionManager.LogException(ex as Exception, source); } catch (NotSupportedException ex) { ExceptionManager.LogException(ex as Exception, source); } } } }
/// <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 SQLJSONStream(SqlCommand cmd) { if (cmd == null) { return(ToolsJson.EmptyJsonList); } var res = new StringBuilder("["); try { cmd.Connection.Open(); using (var rdr = cmd.ExecuteReader()) { bool first = true; while (rdr.Read()) { { if (first) { first = false; } else { res.Append(","); } res.Append(Environment.NewLine); res.Append(rdr.GetString(0)); } } } res.Append("]"); } catch (Exception ex) { ExceptionManager.LogException(ex, "OpenFramework.core.Tools.Basics.SQLJsoStream(" + cmd.CommandText + ")"); return(ex.Message); } finally { if (cmd.Connection.State != ConnectionState.Closed) { cmd.Connection.Close(); } } return(res.ToString()); }
/// <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 SQLStreamSimple(SqlCommand cmd) { if (cmd == null) { return("{}"); } var res = new StringBuilder(); try { cmd.Connection.Open(); using (var rdr = cmd.ExecuteReader()) { if (rdr.HasRows) { rdr.Read(); res.AppendFormat(CultureInfo.InvariantCulture, @"{{{0}}}", rdr.GetString(0)); } } } catch (Exception ex) { ExceptionManager.LogException(ex, "OpenFramework.Basics.SQLToJSONSimple(" + cmd.CommandText + ")"); return(ex.Message); } finally { if (cmd.Connection.State != ConnectionState.Closed) { cmd.Connection.Close(); } } return(res.ToString()); }
public static Instance ById(long instanceId) { string source = string.Format(CultureInfo.InvariantCulture, "Instance::ById({0})", instanceId); Instance res = Instance.Empty; using (SqlCommand cmd = new SqlCommand("Instance_GetById")) { cmd.CommandType = CommandType.StoredProcedure; using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["Security"].ConnectionString)) { cmd.Connection = cnn; cmd.Parameters.Add(DataParameter.Input("@InstanceId", instanceId)); try { cmd.Connection.Open(); using (SqlDataReader rdr = cmd.ExecuteReader()) { if (rdr.HasRows) { rdr.Read(); res.Id = rdr.GetInt64(ColumnsInstanceGet.Id); res.Key = rdr.GetString(ColumnsInstanceGet.Key); res.Name = rdr.GetString(ColumnsInstanceGet.Name); res.SubscriptionType = rdr.GetInt32(ColumnsInstanceGet.SubscriptionType); res.SubscriptionStart = rdr.GetDateTime(ColumnsInstanceGet.SubscriptionStart); res.SubscriptionEnd = rdr.GetDateTime(ColumnsInstanceGet.SubscriptionEnd); res.UsersLimit = rdr.GetInt32(ColumnsInstanceGet.UsersLimit); res.DefaultLanguage = new Language() { Id = rdr.GetInt64(ColumnsInstanceGet.DefaultLanguage) }; res.CreatedBy = new ApplicationUser() { Id = rdr.GetInt64(ColumnsInstanceGet.CreatedBy), FirstName = rdr.GetString(ColumnsInstanceGet.CreatedByFirstName), LastName = rdr.GetString(ColumnsInstanceGet.CreatedByLastName) }; res.CreatedOn = rdr.GetDateTime(ColumnsInstanceGet.CreatedOn); res.ModifiedBy = new ApplicationUser() { Id = rdr.GetInt64(ColumnsInstanceGet.ModifiedBy), FirstName = rdr.GetString(ColumnsInstanceGet.ModifiedByFirstName), LastName = rdr.GetString(ColumnsInstanceGet.ModifiedByLastName) }; res.ModifiedOn = rdr.GetDateTime(ColumnsInstanceGet.ModifiedOn); res.MultipleCore = rdr.GetBoolean(ColumnsInstanceGet.MultipleCore); res.Active = rdr.GetBoolean(ColumnsInstanceGet.Active); } } } catch (NullReferenceException ex) { ExceptionManager.LogException(ex as Exception, source); } catch (ArgumentNullException ex) { ExceptionManager.LogException(ex as Exception, source); } catch (ArgumentException ex) { ExceptionManager.LogException(ex as Exception, source); } catch (FormatException ex) { ExceptionManager.LogException(ex as Exception, source); } catch (SqlException ex) { ExceptionManager.LogException(ex as Exception, source); } catch (NotSupportedException ex) { ExceptionManager.LogException(ex as Exception, source); } } } return(res); }
/// <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()); }