Пример #1
0
        /// <summary>
        /// Esegue l'Update di una scommessa nel DB.
        /// </summary>
        /// <param name="i">Incontro Oggetto con i dati dell'incontro con le quote della scommessa effettuata</param>
        /// <param name="s">Schedina Oggetto con i dati della schedina in corso di modifica.</param>
        /// <returns>bool False se ci sono eccezioni, True altrimenti.</returns>
        public async Task <bool> UpdateScommessa(Incontro i, Schedina s)
        {
            bool   isUpdated     = false;
            string messageForLog = string.Empty;

            try
            {
                Database dbInstance = GetSQLiteConnection();
                await dbInstance.OpenAsync(SqliteOpenMode.OpenReadWrite);

                // Una scommessa e' composta da piu' incontri, dove per ogni incontro e' possibile
                // giocare diverse quote.

                // Query per inserire i dati dell'incontro, se non presente. Ogni incontro e' identificato
                // da una serie di chiavi primarie.
                string querySql1 = " INSERT OR IGNORE INTO " + Constants.TABLE_NAME_SCOMMESSA +
                                   " (IdScommessa, TeamCasa, TeamFCasa, Data, IdMatch) " +
                                   " VALUES (@IdScommessa, @TeamCasa, @TeamFCasa, @Data, @IdMatch) ";
                using (Statement statement = await dbInstance.PrepareStatementAsync(querySql1))
                {
                    statement.BindIntParameterWithName("@IdScommessa", s.Id);
                    statement.BindTextParameterWithName("@TeamCasa", i.TeamCasa);
                    statement.BindTextParameterWithName("@TeamFCasa", i.TeamFCasa);
                    statement.BindTextParameterWithName("@Data", i.Data);
                    statement.BindTextParameterWithName("@IdMatch", i.IdMatch);
                    await statement.StepAsync();
                }

                // Query per inserire le quote per un determinato incontro. Se ho valori, effettuo l'update, altrimenti lascio
                // il valore presente.
                string querySql2 = " UPDATE " + Constants.TABLE_NAME_SCOMMESSA +
                                   " SET " +
                                   " TotalScore = case when coalesce('" + i.TotalScore + "', '') = '' then TotalScore else '" + i.TotalScore + "' end, " +
                                   " Over = case when coalesce('" + i.OVER + "', '') = '' then Over else '" + i.OVER + "' end, " +
                                   " Under = case when coalesce('" + i.UNDER + "', '') = '' then Under else '" + i.UNDER + "' end, " +
                                   " Q1 = case when coalesce('" + i.Q1 + "', '') = '' then Q1 else '" + i.Q1 + "' end, " +
                                   " Qx = case when coalesce('" + i.QX + "', '') = '' then Qx else '" + i.QX + "' end, " +
                                   " Q2 = case when coalesce('" + i.Q2 + "', '') = '' then Q2 else '" + i.Q2 + "' end, " +
                                   ////" Q1 = '" + i.Q1 + "'," +
                                   ////" Qx = '" + i.QX + "'," +
                                   ////" Q2 = '" + i.Q2 + "'," +
                                   " Hc = case when coalesce('" + i.HC + "', '') = '' then Hc else '" + i.HC + "' end, " +
                                   " Hc1 = case when coalesce('" + i.HC1 + "', '') = '' then Hc1 else '" + i.HC1 + "' end, " +
                                   " Hc2 = case when coalesce('" + i.HC2 + "', '') = '' then Hc2 else '" + i.HC2 + "' end, " +
                                   " Hcx = case when coalesce('" + i.HCX + "', '') = '' then Hcx else '" + i.HCX + "' end, " +
                                   " Dc1x = case when coalesce('" + i.DC1X + "', '') = '' then Dc1x else '" + i.DC1X + "' end, " +
                                   " Dc12 = case when coalesce('" + i.DC12 + "', '') = '' then Dc12 else '" + i.DC12 + "' end, " +
                                   " Dcx2 = case when coalesce('" + i.DCX2 + "', '') = '' then Dcx2 else '" + i.DCX2 + "' end, " +
                                   " Home12 = case when coalesce('" + i.Home12 + "', '') = '' then Home12 else '" + i.Home12 + "' end, " +
                                   " Away12 = case when coalesce('" + i.Away12 + "', '') = '' then Away12 else '" + i.Away12 + "' end " +
                                   " WHERE " +
                                   " IdScommessa = '" + s.Id + "'" +
                                   " AND " +
                                   " TeamCasa = '" + i.TeamCasa + "'" +
                                   " AND " +
                                   " TeamFCasa = '" + i.TeamFCasa + "'" +
                                   " AND " +
                                   " Data = '" + i.Data + "'" +
                                   " AND " +
                                   " IdMatch = '" + i.IdMatch + "'";
                using (Statement statement = await dbInstance.PrepareStatementAsync(querySql2))
                {
                    System.Diagnostics.Debug.WriteLine("[DATAACCESSDB - UpdateScommessa] \t" + querySql2.ToString());
                    await statement.StepAsync();

                    isUpdated     = true;
                    messageForLog = "Query eseguita con successo";
                }
            }
            catch (Exception ex)
            {
                isUpdated     = false;
                messageForLog = ex.Message;
            }
            finally
            {
                System.Diagnostics.Debug.WriteLine("[DATAACCESSDB - UpdateScommessa] \t" + messageForLog + "\n");
            }

            return(isUpdated);
        }
Пример #2
0
        public async Task <Scommessa> GetScommessa(Incontro i, Schedina s)
        {
            Scommessa scommessa     = null;
            string    messageForLog = string.Empty;

            try
            {
                Database dbInstance = GetSQLiteConnection();
                await dbInstance.OpenAsync(SqliteOpenMode.OpenRead);

                string query =
                    " SELECT * FROM " + Constants.TABLE_NAME_SCOMMESSA +
                    " INNER JOIN " + Constants.TABLE_NAME_SCHEDINA +
                    " ON " + Constants.TABLE_NAME_SCOMMESSA + ".IdScommessa" +
                    " = " +
                    Constants.TABLE_NAME_SCHEDINA + ".Id" +
                    " WHERE " +
                    " IdScommessa = '" + s.Id + "'" +
                    " AND " +
                    " TeamCasa = '" + i.TeamCasa + "'" +
                    " AND " +
                    " TeamFCasa = '" + i.TeamFCasa + "'" +
                    " AND " +
                    " Data = '" + i.Data + "'" +
                    " AND " +
                    " IdMatch = '" + i.IdMatch + "'"
                ;
                using (Statement statement = await dbInstance.PrepareStatementAsync(query))
                {
                    statement.EnableColumnsProperty();
                    System.Diagnostics.Debug.WriteLine("[DATAACCESSDB - GetScommessa] \t" + query.ToString());
                    while (await statement.StepAsync())
                    {
                        var columns = statement.Columns;
                        scommessa = new Scommessa
                        {
                            IdScommessa = int.Parse(columns["IdScommessa"]),
                            TeamCasa    = columns["TeamCasa"],
                            TeamFCasa   = columns["TeamFCasa"],
                            Data        = columns["Data"],
                            IdMatch     = columns["IdMatch"],
                            TotalScore  = columns["TotalScore"],
                            OVER        = columns["Over"],
                            UNDER       = columns["Under"],
                            Q1          = columns["Q1"],
                            QX          = columns["Qx"],
                            Q2          = columns["Q2"],
                            HC          = columns["Hc"],
                            HC1         = columns["Hc1"],
                            HC2         = columns["Hc2"],
                            HCX         = columns["Hcx"],
                            DC1X        = columns["Dc1x"],
                            DC12        = columns["Dc12"],
                            DCX2        = columns["Dcx2"],
                            Home12      = columns["Home12"],
                            Away12      = columns["Away12"]
                        };
                    }
                    messageForLog = "Query eseguita con successo";
                }
            }
            catch (Exception ex)
            {
                messageForLog = ex.Message;
            }
            finally
            {
                System.Diagnostics.Debug.WriteLine("[DATAACCESSDB - GetScommessa] \t" + messageForLog + "\n");
            }

            return(scommessa);
        }