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