private async void ReadCommands()
{
try
{
MySqlCommand readCommands = new MySqlCommand("SELECT command,target,status FROM COMMANDS WHERE target = '" + ProgramOptions.LoggedInUser.Nick + "' AND status = 'start' LIMIT 1;", DataReaderMySQLConnection);
System.Data.Common.DbDataReader reader = await readCommands.ExecuteReaderAsync();
while ((isReadingCommands = await reader.ReadAsync()) == true)
{
if (reader.GetString(2) == "start" && (reader.GetString(0) == "kicked" || reader.GetString(0) == "banned"))
{
MySqlCommand updateCommand = new MySqlCommand("START TRANSACTION; UPDATE COMMANDS SET status = 'finished' WHERE target = '" + ProgramOptions.LoggedInUser.Nick + "'; COMMIT; ", DataManipulationMySQLConnection);
await updateCommand.ExecuteNonQueryAsync();
MessageBox.Show("You were " + reader.GetString(0) + " from the server!");
this.Close();
}
}
reader.Close();
}
catch (Exception mysqlException)
{
if (mysqlException is MySqlException)
{
System.Diagnostics.Debug.WriteLine("Exception thrown in ReadCommandsAsync; Not harmful; " + mysqlException.ToString());
}
}
}
public static async Task <List <alarm> > SelectAlarms(string DB, string where, int from)
{
int i = 0;
List <alarm> alarms = new List <alarm>();
List <alarm_texts> titles = SelectAlarmsTexts(DB);
string connstring = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};",
"192.168.2.12", 5432, "postgres", "Nordit0276", DB);
NpgsqlConnection conn = new NpgsqlConnection(connstring);
string sql = "SELECT * FROM alarm_history WHERE origin_pktime>" + from + " AND (" + where + ") ORDER BY origin_pktime DESC";
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);
//Prepare DataReader
System.Data.Common.DbDataReader dr = await cmd.ExecuteReaderAsync();
while (await dr.ReadAsync())
{
alarm alarm = new alarm();
alarm.id = short.Parse(dr["alarm_id"].ToString());
int id = alarm.id;
//small improvment beacause alarm_id in table alarm_texts and alarm_id in table alarm_history are bind
alarm.title = titles[id - 1].title;
int originTime = int.Parse(dr["origin_pktime"].ToString());
alarm.originTime = pkTimeToDateTime(originTime);
int expTime = Int32.Parse(dr["expiry_pktime"].ToString());
alarm.expiryTime = pkTimeToDateTime(expTime);
alarms.Add(alarm);
i++;
}
conn.Close();
return(alarms);
}
private async void ReadOnlineUsers()
{
try
{
MySqlCommand readOnlineUsers = new MySqlCommand("SELECT ONLINE_USERS.nick, ONLINE_USERS.status, ACCOUNTS.privileges, ACCOUNTS.login FROM ONLINE_USERS LEFT JOIN ACCOUNTS ON ONLINE_USERS.Nick = ACCOUNTS.Nick WHERE ONLINE_USERS.status != 'Offline';", DataReaderMySQLConnection);
System.Data.Common.DbDataReader reader = await readOnlineUsers.ExecuteReaderAsync();
List <Users> temporaryUsers = new List <Users> {
};
while ((isReadingUsers = await reader.ReadAsync()) == true)
{
switch (reader.GetString(2))
{
case "administrator":
temporaryUsers.Add(new Administrator(reader.GetString(0), reader.GetString(1)));
if (reader.GetString(0) == ProgramOptions.LoggedInUser.Nick && !(ProgramOptions.LoggedInUser is Administrator))
{
ProgramOptions.LoggedInUser = new Administrator(ProgramOptions.LoggedInUser.Nick, ProgramOptions.LoggedInUser.Status, reader.GetString(3));
}
break;
case "moderator":
temporaryUsers.Add(new Moderator(reader.GetString(0), reader.GetString(1)));
if (reader.GetString(0) == ProgramOptions.LoggedInUser.Nick && !(ProgramOptions.LoggedInUser is Moderator))
{
ProgramOptions.LoggedInUser = new Moderator(ProgramOptions.LoggedInUser.Nick, ProgramOptions.LoggedInUser.Status, reader.GetString(3));
}
break;
default:
temporaryUsers.Add(new User(reader.GetString(0), reader.GetString(1)));
if (reader.GetString(0) == ProgramOptions.LoggedInUser.Nick && !(ProgramOptions.LoggedInUser is User))
{
ProgramOptions.LoggedInUser = new User(ProgramOptions.LoggedInUser.Nick, ProgramOptions.LoggedInUser.Status, reader.GetString(3));
}
break;
}
}
if (temporaryUsers != onlineUsers)
{
onlineUsers = temporaryUsers;
}
reader.Close();
loggedInUsersList.ItemsSource = onlineUsers;
}
catch (Exception mysqlException)
{
if (mysqlException is MySqlException)
{
System.Diagnostics.Debug.WriteLine("Exception thrown in ReadOnlineUsersAsync; Not harmful; " + mysqlException.ToString());
}
}
}
/// <summary>
/// Method to get alarms async for notifications
/// </summary>
/// <param name="DB">db name</param>
/// <param name="count">count of alarms that you want to select</param>
/// <param name="ids"></param>
/// <returns></returns>
public async Task <List <AlarmGraphData> > SelectAlarms(string DB, long pktimeFrom, long pkTimeTo, int plcID = 1)
{
int i = 0;
string sql = string.Empty;
string whereIds = string.Empty;
List <AlarmGraphData> alarms = new List <AlarmGraphData>();
List <alarm_texts> titles = SelectAlarmsTexts(DB, null, plcID);
string connstring = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};",
"192.168.2.12", 5432, "postgres", "Nordit0276", DB);
NpgsqlConnection conn = new NpgsqlConnection(connstring);
sql = string.Format("SELECT * FROM alarm_history WHERE (origin_pktime BETWEEN {0} AND {1}) AND plc_id={2} ORDER BY origin_pktime ASC", pktimeFrom, pkTimeTo, plcID);
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);
//Prepare DataReader
System.Data.Common.DbDataReader dr = await cmd.ExecuteReaderAsync();
while (await dr.ReadAsync())
{
AlarmGraphData alarm = new AlarmGraphData();
alarm.id = short.Parse(dr["alarm_id"].ToString());
// alarm.id++;
int id = alarm.id;
//small improvment beacause alarm_id in table alarm_texts and alarm_id in table alarm_history are bind
if (titles.Exists(p => (p.id) == id))
{
alarm.title = titles.First(p => p.id == id && p.plcID == plcID).title;
}
else
{
alarm.title = "Title does not match with any of alarm id in texts and in db. DB id:" + id;
}
int originTime = int.Parse(dr["origin_pktime"].ToString());
int expTime = Int32.Parse(dr["expiry_pktime"].ToString());
alarm.originTime = originTime;
alarm.expiryTime = expTime;
alarms.Add(alarm);
i++;
}
conn.Close();
return(alarms);
}
/// <summary>
/// Method to get alarms
/// </summary>
/// <param name="DB">db name</param>
/// <param name="count">count of alarms that you want to select</param>
/// <param name="ids"></param>
/// <returns></returns>
public async Task <List <alarm> > SelectAlarms(string DB, int offsetPage = 0, int count = 0, List <int> ids = null, int plcID = 1, string lang = "en")
{
int i = 0;
string sql = string.Empty;
string whereIds = string.Empty;
List <alarm> alarms = new List <alarm>();
List <alarm_texts> titles = SelectAlarmsTexts(DB, null, plcID, lang);
string connstring = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};",
"192.168.2.12", 5432, "postgres", "Nordit0276", DB);
NpgsqlConnection conn = new NpgsqlConnection(connstring);
if (ids != null)
{
foreach (int id in ids)
{
whereIds += "alarm_id=" + id.ToString() + " OR ";
}
whereIds = whereIds.Substring(0, whereIds.Length - 4);
// Execute the query and obtain a result set
sql = string.Format("SELECT * FROM alarm_history WHERE {0} AND plc_id={1} ORDER BY origin_pktime DESC LIMIT {2}", whereIds, plcID, count);
}
else
{
if (count != 0)
{
sql = string.Format("SELECT * FROM alarm_history WHERE plc_id={0} ORDER BY origin_pktime DESC LIMIT {1}", plcID, count);
}
else
{
sql = "SELECT * FROM alarm_history ORDER BY origin_pktime DESC";
}
}
if (offsetPage > 0 && count > 0)
{
if (ids != null)
{
foreach (int id in ids)
{
whereIds += "alarm_id=" + id.ToString() + " OR ";
}
// Execute the query and obtain a result set
sql = string.Format("SELECT * FROM alarm_history WHERE plc_id={0} ORDER BY origin_pktime DESC LIMIT {1} OFFSET {2}", plcID, whereIds, count * offsetPage);
}
sql = string.Format("SELECT * FROM alarm_history WHERE plc_id={0} ORDER BY origin_pktime DESC LIMIT {1} OFFSET {2}", plcID, count, count * offsetPage);
}
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);
//Prepare DataReader
System.Data.Common.DbDataReader dr = await cmd.ExecuteReaderAsync();
while (await dr.ReadAsync())
{
alarm alarm = new alarm();
alarm.id = short.Parse(dr["alarm_id"].ToString());
int id = alarm.id;
//small improvment beacause alarm_id in table alarm_texts and alarm_id in table alarm_history are bind
if (titles.Where(p => (p.id) == id && p.plcID == plcID && p.lang == lang).Count() == 1)
{
alarm.title = titles.Single(p => p.id == id && p.plcID == plcID && p.lang == lang).title;
}
else
{
alarm.title = "Title does not match with any of alarm id in texts and in db. DB id:" + id;
}
int originTime = int.Parse(dr["origin_pktime"].ToString());
int expTime = Int32.Parse(dr["expiry_pktime"].ToString());
alarm.originTime = pkTimeToDateTime(originTime);
alarm.expiryTime = pkTimeToDateTime(expTime);
alarms.Add(alarm);
i++;
}
conn.Close();
return(alarms);
}
public async Task Auth(string login, string pwd)
{
if (connect.IsConnect())
{
string authQuery = "SELECT id_user, id_group FROM accounts " +
"WHERE login = @login AND pwd = @pwd LIMIT 1;";
using (MySqlCommand command = new MySqlCommand(authQuery, connect.GetConnect))
{
command.Parameters.AddWithValue("@login", login); //логин
command.Parameters.AddWithValue("@pwd", GetHash(MD5.Create(), pwd)); //пароль
using (System.Data.Common.DbDataReader reader = await command.ExecuteReaderAsync())
{
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
HashID = Encoding.UTF8.GetBytes(reader.GetString(0)); //перевод хэша в массив битов
idGroup = await reader.GetFieldValueAsync <uint>(1);
}
reader.Close();
}
else
{
AuthoStatysEvent("Неправильный логин или пароль");
}
}
}
if (idGroup.Equals(1122)) //Регистратор
{
string selectRegistratorQuery = "SELECT id, fio FROM registrator " +
"WHERE id_user = @idUser";
using (MySqlCommand command = new MySqlCommand(selectRegistratorQuery, connect.GetConnect))
{
string id = Encoding.UTF8.GetString(HashID); //перевод массива битов в хэш
command.Parameters.AddWithValue("@idUser", id);
using (System.Data.Common.DbDataReader reader = await command.ExecuteReaderAsync())
{
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
GetEmployees = new Registrator()
{
Id = await reader.GetFieldValueAsync <uint>(0),
Fio = reader.GetString(1)
};
}
reader.Close();
}
}
}
GetGroup = Group.Registrator;
}
else if (idGroup.Equals(2211)) //Доктор
{
string selectRegistratorQuery = "SELECT id, fio FROM doctor WHERE id_user = @idUser";
using (MySqlCommand command = new MySqlCommand(selectRegistratorQuery, connect.GetConnect))
{
string id = Encoding.UTF8.GetString(HashID); //перевод массива битов в хэш
command.Parameters.AddWithValue("@idUser", id);
using (System.Data.Common.DbDataReader reader = await command.ExecuteReaderAsync())
{
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
GetEmployees = new Doctor()
{
Id = await reader.GetFieldValueAsync <uint>(0),
Fio = reader.GetString(1)
};
}
reader.Close();
}
}
}
GetGroup = Group.Doctor;
}
connect.Close();
}
}
} // End Sub WriteArray
public static async System.Threading.Tasks.Task AnyDataReaderToJson(
System.Data.Common.DbConnection cnn
, string sql
, RenderType_t format
, Microsoft.AspNetCore.Http.HttpContext context
, System.Text.Encoding encoding
, object parameters = null
, System.Data.IDbTransaction transaction = null
, int?commandTimeout = null
, System.Data.CommandType?commandType = null
)
{
// DynamicParameters dbArgs = new DynamicParameters();
using (System.IO.StreamWriter responseWriter = new System.IO.StreamWriter(context.Response.Body, encoding))
{
using (Newtonsoft.Json.JsonTextWriter jsonWriter =
new Newtonsoft.Json.JsonTextWriter(responseWriter))
{
if (format.HasFlag(RenderType_t.Indented))
{
jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented;
}
try
{
using (System.Data.Common.DbDataReader dr = await cnn.ExecuteDbReaderAsync(sql, parameters, transaction, commandTimeout, commandType))
{
context.Response.StatusCode = (int)System.Net.HttpStatusCode.OK;
context.Response.ContentType = "application/json; charset=" + encoding.WebName;
await jsonWriter.WriteStartObjectAsync();
await jsonWriter.WritePropertyNameAsync("tables");
await jsonWriter.WriteStartArrayAsync();
do
{
if (!format.HasFlag(RenderType_t.Data_Only) &&
!format.HasFlag(RenderType_t.DataTable))
{
await jsonWriter.WriteStartObjectAsync();
await jsonWriter.WritePropertyNameAsync("columns");
if (format.HasFlag(RenderType_t.Columns_Associative))
{
await WriteAssociativeColumnsArray(jsonWriter, dr, format);
}
else if (format.HasFlag(RenderType_t.Columns_ObjectArray))
{
await WriteComplexArray(jsonWriter, dr, format);
}
else // (format.HasFlag(RenderType_t.Array))
{
await WriteArray(jsonWriter, dr);
}
} // End if (!format.HasFlag(RenderType_t.Data_Only))
if (!format.HasFlag(RenderType_t.Data_Only) &&
!format.HasFlag(RenderType_t.DataTable))
{
await jsonWriter.WritePropertyNameAsync("rows");
} // End if (!format.HasFlag(RenderType_t.Data_Only))
await jsonWriter.WriteStartArrayAsync();
string[] columns = null;
if (format.HasFlag(RenderType_t.DataTable))
{
columns = new string[dr.FieldCount];
for (int i = 0; i < dr.FieldCount; i++)
{
columns[i] = dr.GetName(i);
} // Next i
} // End if (format.HasFlag(RenderType_t.DataTable))
while (await dr.ReadAsync())
{
if (format.HasFlag(RenderType_t.DataTable))
{
await jsonWriter.WriteStartObjectAsync();
}
else
{
await jsonWriter.WriteStartArrayAsync();
}
for (int i = 0; i <= dr.FieldCount - 1; i++)
{
object obj = await dr.GetFieldValueAsync <object>(i);
if (obj == System.DBNull.Value)
{
obj = null;
}
if (columns != null && format.HasFlag(RenderType_t.DataTable))
{
await jsonWriter.WritePropertyNameAsync(columns[i]);
}
await jsonWriter.WriteValueAsync(obj);
} // Next i
if (format.HasFlag(RenderType_t.DataTable))
{
await jsonWriter.WriteEndObjectAsync();
}
else
{
await jsonWriter.WriteEndArrayAsync();
}
} // Whend
await jsonWriter.WriteEndArrayAsync();
if (!format.HasFlag(RenderType_t.Data_Only) &&
!format.HasFlag(RenderType_t.DataTable))
{
await jsonWriter.WriteEndObjectAsync();
} // End if (!format.HasFlag(RenderType_t.Data_Only))
await jsonWriter.FlushAsync();
await responseWriter.FlushAsync();
} while (await dr.NextResultAsync());
await jsonWriter.WriteEndArrayAsync();
await jsonWriter.WriteEndObjectAsync();
await jsonWriter.FlushAsync();
await responseWriter.FlushAsync();
} // End Using dr
}
catch (System.Exception ex)
{
context.Response.StatusCode = (int)System.Net.HttpStatusCode.InternalServerError;
context.Response.ContentType = "application/json; charset=" + encoding.WebName;
await jsonWriter.WriteStartObjectAsync();
await jsonWriter.WritePropertyNameAsync("Error");
await jsonWriter.WriteStartObjectAsync();
await jsonWriter.WritePropertyNameAsync("Message");
await jsonWriter.WriteValueAsync(ex.Message);
await jsonWriter.WritePropertyNameAsync("StackTrace");
await jsonWriter.WriteValueAsync(ex.StackTrace);
await jsonWriter.WriteEndObjectAsync();
await jsonWriter.WriteEndObjectAsync();
}
await jsonWriter.FlushAsync();
await responseWriter.FlushAsync();
await context.Response.CompleteAsync();
} // End Using jsonWriter
} // End Using responseWriter
} // End Sub AnyDataReaderToJson
} // End Sub WriteArray
public static async System.Threading.Tasks.Task AnyDataReaderToJson(
string sql
, System.Collections.Generic.Dictionary<string, object> pars
, Microsoft.AspNetCore.Http.HttpContext context
, RenderType_t format)
{
SqlService service = (SqlService) context.RequestServices.GetService(typeof(SqlService));
using (System.Data.Common.DbConnection con = service.Connection)
{
using (System.Data.Common.DbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sql;
// cmd.CommandText = "SELECT * FROM T_Benutzer; SELECT * FROM T_Benutzergruppen;";
service.AddParameterList(pars, cmd);
// cmd.ExecuteNonQueryAsync
// cmd.ExecuteReaderAsync
// cmd.ExecuteScalarAsync
using (System.Data.Common.DbDataReader dr = await cmd.ExecuteReaderAsync(
System.Data.CommandBehavior.SequentialAccess
| System.Data.CommandBehavior.CloseConnection))
{
using (System.IO.StreamWriter output = new System.IO.StreamWriter(context.Response.Body))
{
using (Newtonsoft.Json.JsonTextWriter jsonWriter =
new Newtonsoft.Json.JsonTextWriter(output)) // context.Response.Output)
{
if (format.HasFlag(RenderType_t.Indented))
jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented;
context.Response.StatusCode = (int) System.Net.HttpStatusCode.OK;
context.Response.ContentType = "application/json";
await jsonWriter.WriteStartObjectAsync();
await jsonWriter.WritePropertyNameAsync("tables");
await jsonWriter.WriteStartArrayAsync();
do
{
if (!format.HasFlag(RenderType_t.Data_Only) &&
!format.HasFlag(RenderType_t.DataTable))
{
await jsonWriter.WriteStartObjectAsync();
await jsonWriter.WritePropertyNameAsync("columns");
if (format.HasFlag(RenderType_t.Columns_Associative))
{
await WriteAssociativeColumnsArray(jsonWriter, dr, format);
}
else if (format.HasFlag(RenderType_t.Columns_ObjectArray))
{
await WriteComplexArray(jsonWriter, dr, format);
}
else // (format.HasFlag(RenderType_t.Array))
{
await WriteArray(jsonWriter, dr);
}
} // End if (!format.HasFlag(RenderType_t.Data_Only))
if (!format.HasFlag(RenderType_t.Data_Only) &&
!format.HasFlag(RenderType_t.DataTable))
{
await jsonWriter.WritePropertyNameAsync("rows");
} // End if (!format.HasFlag(RenderType_t.Data_Only))
await jsonWriter.WriteStartArrayAsync();
string[] columns = null;
if (format.HasFlag(RenderType_t.DataTable))
{
columns = new string[dr.FieldCount];
for (int i = 0; i < dr.FieldCount; i++)
{
columns[i] = dr.GetName(i);
} // Next i
} // End if (format.HasFlag(RenderType_t.DataTable))
while (await dr.ReadAsync())
{
if (format.HasFlag(RenderType_t.DataTable))
await jsonWriter.WriteStartObjectAsync();
else
await jsonWriter.WriteStartArrayAsync();
for (int i = 0; i <= dr.FieldCount - 1; i++)
{
object obj = await dr.GetFieldValueAsync<object>(i);
if (obj == System.DBNull.Value)
obj = null;
if (columns != null && format.HasFlag(RenderType_t.DataTable))
{
await jsonWriter.WritePropertyNameAsync(columns[i]);
}
await jsonWriter.WriteValueAsync(obj);
} // Next i
if (format.HasFlag(RenderType_t.DataTable))
await jsonWriter.WriteEndObjectAsync();
else
await jsonWriter.WriteEndArrayAsync();
} // Whend
await jsonWriter.WriteEndArrayAsync();
if (!format.HasFlag(RenderType_t.Data_Only) &&
!format.HasFlag(RenderType_t.DataTable))
{
await jsonWriter.WriteEndObjectAsync();
} // End if (!format.HasFlag(RenderType_t.Data_Only))
} while (await dr.NextResultAsync());
await jsonWriter.WriteEndArrayAsync();
await jsonWriter.WriteEndObjectAsync();
await jsonWriter.FlushAsync();
await output.FlushAsync();
} // jsonWriter
} // output
} // dr
} // End Using cmd
if (con.State != System.Data.ConnectionState.Closed)
con.Close();
} // con
} // End Sub WriteArray
} // End Sub WriteArray
public static async System.Threading.Tasks.Task <System.Exception> AsJSON(
this IDbConnection cnn
, System.IO.TextWriter output
, string sql
, RenderType_t format
, object param = null
, IDbTransaction transaction = null
, int?commandTimeout = null
, CommandType?commandType = null)
{
try
{
using (System.Data.Common.DbDataReader dr = cnn.ExecuteDbReader(sql, param, transaction, commandTimeout, commandType))
{
using (Newtonsoft.Json.JsonTextWriter jsonWriter =
new Newtonsoft.Json.JsonTextWriter(output)) // context.Response.Output)
{
if (format.HasFlag(RenderType_t.Indented))
{
jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented;
}
// https://stackoverflow.com/questions/15305203/what-to-do-with-errors-when-streaming-the-body-of-an-http-request
// https://tools.ietf.org/html/rfc2616#section-14.40
// https://stackoverflow.com/questions/50299300/how-to-write-malformed-http-response-to-guarantee-something-akin-to-http-500
// https://stackoverflow.com/questions/50298999/write-http-trailer-headers-manually
// How do I write a useful trail header to the response that can be displayed well by the browser?
// You don’t. Mainstream Web browsers do not care about trailers.
// context.Response.StatusCode = (int)System.Net.HttpStatusCode.OK;
// context.Response.ContentType = "application/json";
await jsonWriter.WriteStartObjectAsync();
await jsonWriter.WritePropertyNameAsync("tables");
await jsonWriter.WriteStartArrayAsync();
do
{
if (!format.HasFlag(RenderType_t.Data_Only) &&
!format.HasFlag(RenderType_t.DataTable))
{
await jsonWriter.WriteStartObjectAsync();
await jsonWriter.WritePropertyNameAsync("columns");
if (format.HasFlag(RenderType_t.Columns_Associative))
{
await WriteAssociativeColumnsArray(jsonWriter, dr, format);
}
else if (format.HasFlag(RenderType_t.Columns_ObjectArray))
{
await WriteComplexArray(jsonWriter, dr, format);
}
else // (format.HasFlag(RenderType_t.Array))
{
await WriteArray(jsonWriter, dr);
}
} // End if (!format.HasFlag(RenderType_t.Data_Only))
if (!format.HasFlag(RenderType_t.Data_Only) &&
!format.HasFlag(RenderType_t.DataTable))
{
await jsonWriter.WritePropertyNameAsync("rows");
} // End if (!format.HasFlag(RenderType_t.Data_Only))
await jsonWriter.WriteStartArrayAsync();
string[] columns = null;
if (format.HasFlag(RenderType_t.DataTable))
{
columns = new string[dr.FieldCount];
for (int i = 0; i < dr.FieldCount; i++)
{
columns[i] = dr.GetName(i);
} // Next i
} // End if (format.HasFlag(RenderType_t.DataTable))
while (await dr.ReadAsync())
{
if (format.HasFlag(RenderType_t.DataTable))
{
await jsonWriter.WriteStartObjectAsync();
}
else
{
await jsonWriter.WriteStartArrayAsync();
}
for (int i = 0; i <= dr.FieldCount - 1; i++)
{
object obj = await dr.GetFieldValueAsync <object>(i);
if (obj == System.DBNull.Value)
{
obj = null;
}
if (columns != null && format.HasFlag(RenderType_t.DataTable))
{
await jsonWriter.WritePropertyNameAsync(columns[i]);
}
await jsonWriter.WriteValueAsync(obj);
} // Next i
if (format.HasFlag(RenderType_t.DataTable))
{
await jsonWriter.WriteEndObjectAsync();
}
else
{
await jsonWriter.WriteEndArrayAsync();
}
} // Whend
await jsonWriter.WriteEndArrayAsync();
if (!format.HasFlag(RenderType_t.Data_Only) &&
!format.HasFlag(RenderType_t.DataTable))
{
await jsonWriter.WriteEndObjectAsync();
} // End if (!format.HasFlag(RenderType_t.Data_Only))
} while (await dr.NextResultAsync());
await jsonWriter.WriteEndArrayAsync();
await jsonWriter.WriteEndObjectAsync();
await jsonWriter.FlushAsync();
await output.FlushAsync();
} // jsonWriter
} // End Using dr
} // End Try
catch (System.Exception ex)
{
return(ex);
} // End Catch
return(null);
} // End Task AsJSON
} // End Sub WriteArray
public static async System.Threading.Tasks.Task AnyDataReaderToJson(
string sql
, System.Collections.Generic.Dictionary <string, object> pars
, SqlService service
, WebSocketTextWriter output
, RenderType_t format)
{
using (System.Data.Common.DbConnection con = service.Connection)
{
using (System.Data.Common.DbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sql;
service.AddParameterList(pars, cmd);
// cmd.ExecuteNonQueryAsync
// cmd.ExecuteReaderAsync
// cmd.ExecuteScalarAsync
using (System.Data.Common.DbDataReader dr = await cmd.ExecuteReaderAsync(
System.Data.CommandBehavior.SequentialAccess
| System.Data.CommandBehavior.CloseConnection))
{
using (Newtonsoft.Json.JsonTextWriter jsonWriter =
new Newtonsoft.Json.JsonTextWriter(output)) // context.Response.Output)
{
if (format.HasFlag(RenderType_t.Indented))
{
jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented;
}
// context.Response.StatusCode = (int)System.Net.HttpStatusCode.OK;
// context.Response.ContentType = "application/json";
await jsonWriter.WriteStartObjectAsync();
await jsonWriter.WritePropertyNameAsync("tables");
await jsonWriter.WriteStartArrayAsync();
do
{
if (!format.HasFlag(RenderType_t.Data_Only) &&
!format.HasFlag(RenderType_t.DataTable))
{
await jsonWriter.WriteStartObjectAsync();
await jsonWriter.WritePropertyNameAsync("columns");
if (format.HasFlag(RenderType_t.Columns_Associative))
{
await WriteAssociativeColumnsArray(jsonWriter, dr, format);
}
else if (format.HasFlag(RenderType_t.Columns_ObjectArray))
{
await WriteComplexArray(jsonWriter, dr, format);
}
else // (format.HasFlag(RenderType_t.Array))
{
await WriteArray(jsonWriter, dr);
}
} // End if (!format.HasFlag(RenderType_t.Data_Only))
if (!format.HasFlag(RenderType_t.Data_Only) &&
!format.HasFlag(RenderType_t.DataTable))
{
await jsonWriter.WritePropertyNameAsync("rows");
} // End if (!format.HasFlag(RenderType_t.Data_Only))
await output.TransmitAsync();
await jsonWriter.WriteStartArrayAsync();
if (dr.HasRows)
{
string[] columns = null;
if (format.HasFlag(RenderType_t.DataTable))
{
columns = new string[dr.FieldCount];
for (int i = 0; i < dr.FieldCount; i++)
{
columns[i] = dr.GetName(i);
} // Next i
} // End if (format.HasFlag(RenderType_t.DataTable))
int rowCount = 0;
while (await dr.ReadAsync())
{
if (format.HasFlag(RenderType_t.DataTable))
{
await jsonWriter.WriteStartObjectAsync();
}
else
{
await jsonWriter.WriteStartArrayAsync();
}
for (int i = 0; i <= dr.FieldCount - 1; i++)
{
object obj = await dr.GetFieldValueAsync <object>(i);
if (obj == System.DBNull.Value)
{
obj = null;
}
if (columns != null && format.HasFlag(RenderType_t.DataTable))
{
await jsonWriter.WritePropertyNameAsync(columns[i]);
}
await jsonWriter.WriteValueAsync(obj);
} // Next i
if (format.HasFlag(RenderType_t.DataTable))
{
await jsonWriter.WriteEndObjectAsync();
}
else
{
await jsonWriter.WriteEndArrayAsync();
}
rowCount++;
await jsonWriter.FlushAsync();
if (rowCount % 5 == 0)
{
await output.TransmitAsync();
}
else
{
await output.FlushAsync();
}
} // Whend
await jsonWriter.FlushAsync();
await output.TransmitAsync();
} // End if (dr.HasRows)
await jsonWriter.WriteEndArrayAsync();
if (!format.HasFlag(RenderType_t.Data_Only) &&
!format.HasFlag(RenderType_t.DataTable))
{
await jsonWriter.WriteEndObjectAsync();
} // End if (!format.HasFlag(RenderType_t.Data_Only))
} while (await dr.NextResultAsync());
await jsonWriter.WriteEndArrayAsync();
await jsonWriter.WriteEndObjectAsync();
try
{
await jsonWriter.FlushAsync();
await output.TransmitAsync();
}
catch (System.Exception ex)
{
System.Console.WriteLine(ex.Message);
}
} // jsonWriter
} // dr
} // End Using cmd
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
} // con
} // End Sub WriteArray
private async void ReadMessages()
{
try
{
MySqlCommand readOnlineUsers = new MySqlCommand("SELECT user_nick, message FROM CHAT ORDER BY chat_id DESC LIMIT 1;", DataReaderMySQLConnection);
System.Data.Common.DbDataReader reader = await readOnlineUsers.ExecuteReaderAsync();
while ((isReadingMessages = await reader.ReadAsync()) == true)
{
string nick = reader.GetString(0);
string message = reader.GetString(1);
if (nick == "server" && message.Contains("###"))
{
if (message != lastMessage)
{
Paragraph newMessage = new Paragraph();
lastMessage = message;
newMessage.Inlines.Add(message);
newMessage.Inlines.LastInline.Foreground = new SolidColorBrush(Color.FromRgb(147, 147, 147));
richMessagesBoxContent.Blocks.Add(newMessage);
richMessagesBox.Document = richMessagesBoxContent;
if (ProgramOptions.ScrollToBottom)
{
richMessagesBox.ScrollToEnd();
}
}
}
else
{
string tempMessage = "<" + nick + "> : " + message;
if (lastMessage != tempMessage)
{
Paragraph newMessage = new Paragraph();
Color temporaryColor = Color.FromRgb(0, 255, 0);
foreach (Users user in onlineUsers)
{
if (user.Nick == nick)
{
temporaryColor = user.Color;
break;
}
}
lastMessage = tempMessage;
newMessage.Inlines.Add("<" + nick + ">");
newMessage.Inlines.LastInline.Foreground = new SolidColorBrush(temporaryColor);
newMessage.Inlines.Add(" : " + message);
newMessage.Inlines.LastInline.Foreground = new SolidColorBrush(Color.FromRgb(0, 0, 0));
richMessagesBoxContent.Blocks.Add(newMessage);
richMessagesBox.Document = richMessagesBoxContent;
if (ProgramOptions.ScrollToBottom)
{
richMessagesBox.ScrollToEnd();
}
}
}
}
reader.Close();
}
catch (Exception mysqlException)
{
if (mysqlException is MySqlException)
{
System.Diagnostics.Debug.WriteLine("Exception thrown in ReadMessagesAsync; Not harmful; " + mysqlException.ToString());
}
}
}