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