Ejemplo n.º 1
0
        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());
                }
            }
        }
Ejemplo n.º 2
0
        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);
        }
Ejemplo n.º 3
0
        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());
                }
            }
        }
Ejemplo n.º 4
0
        /// <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);
        }
Ejemplo n.º 5
0
        /// <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);
        }
Ejemplo n.º 6
0
 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();
     }
 }
Ejemplo n.º 7
0
        } // 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
Ejemplo n.º 8
0
        } // 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 
Ejemplo n.º 9
0
        } // 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
Ejemplo n.º 10
0
        } // 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
Ejemplo n.º 11
0
        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());
                }
            }
        }