C# MySqlConnection.Query方法代碼示例

        public object Get(int id)
        {
            using (var dbConn = new MySqlConnection(ConfigurationManager.ConnectionStrings["Simple.Data.Properties.Settings.DefaultConnectionString"].ConnectionString)) {
                dbConn.Open();

                var details = Database.Open().fundraiser.FindById(id);
                var recent = Database.Open().pledge.FindAllByFundraiserId(id).OrderByIdDescending().Take(3);

                var pledged = dbConn.Query(
                    "select sum(amount) as amount, count(1) as total from pledge where fundraiserId = @id group by fundraiserId;",
                    new {id}).FirstOrDefault();

                var pledges = dbConn.Query(
                    "select count(amount) as total from pledge where fundraiserId = @id group by fundraiserId;",
                    new {id}).FirstOrDefault();

                var monthMiles = dbConn.Query(
                    "SELECT sum(distance) as total FROM activity where FundraiserId = @id and Date >= @from and Date <= @tooo group by FundraiserId",
                    new {id, from = Dates.MonthStart, tooo = Dates.MonthEnd}).FirstOrDefault();

                pledged = pledged == null ? 0 : pledged.amount;
                monthMiles = monthMiles == null ? 0 : monthMiles.total;
                var predictedRaised = pledged*monthMiles;

                return new {
                    Details = details,
                    Recent = recent,
                    MonthMiles = monthMiles,
                    MonthRaised = predictedRaised,
                    TotalPledged = pledged ,
                    TotalPledges = pledges == null ? 0 : pledges.total
                };
            }
        }

        public string UniquePatientID(string email)
        {
            string uniquePatientID = string.Empty;

            try
            {
                using (IDbConnection db = new MySqlConnection(ConfigurationValues.GroveHillConnection))
                {
                    const string query = "select id from users "
                        + " where email = @email";

                    uniquePatientID = db.Query<string>(query, new
                    {
                        @email = email
                    }).SingleOrDefault();
                    return uniquePatientID;
                }
            }
            catch (Exception er)
            {
                EmployeeDesktop.API.Exceptions.ExceptionHandling.InsertErrorMessage(er.ToString());
                EmployeeDesktop.API.Exceptions.ExceptionHandling.SendErrorEmail(er.ToString(), ConfigurationValues.EmailFromFriendly, ConfigurationValues.EmailSendToFriendly, ConfigurationValues.EmailSubject);
                return uniquePatientID;
            }
        }

        public IAccount GetAccountByUsername(string username)
        {
            try
            {
                if (!IsEnabled)
                    return null;

                using (var connection = new MySqlConnection(_mySqlProvider.ConnectionString))
                {
                    return connection.Query<Account>(
                        @"SELECT a.id, w.username, a.coin_address as address FROM pool_worker as w
                            INNER JOIN accounts as a ON a.id=w.account_id
                            WHERE w.username = @username",
                        new {username}).Single();
                }
            }
            catch (InvalidOperationException) // fires when no result is found.
            {
                return null;
            }
            catch (Exception e)
            {
                _logger.Error("An exception occured while getting account; {0:l}", e.Message);
                return null;
            }
        }

 public List<Report> GetReport()
 {
     List<Report> reporting = new List<Report>();
     try
     {
         using (MySqlConnection db = new MySqlConnection(Database.ReminderConnection))
         {
             //CONCAT(name, ' is ', age,' years old.')
             string query = " select CONCAT(DateOfAppointment, ' ', TimeOfAppointment) as AppointmentDateTime, "
                 + " Nameid as PatientMRN,"
                 + " CONCAT(FirstName, ' ',  LastName) as PatientName,"
                 + " Charge as Speciality,"
                 + " Provider as Provider,"
                 + " location as Location,"
                 + " TelephoneFile as Telephone,"
                 + " MessageTime as LengthOfCall,"
                 + " Time_Of_Call as CallTime,"
                 + " ResultDescription as Response"
                 + " from pendingtelephonelogforreports"
                 + " order by LastName, DateOfAppointment";
             reporting = db.Query<Report>(query).ToList();
             return reporting;
         }
     }
     catch (Exception er)
     {
         Log.LogMessage(er.ToString());
         return reporting;
     }
 }

        public IEnumerable<dynamic> GetSiteCatUrlAusYp()
        {
            using (MySqlConnection conn = new MySqlConnection(BG_Db_Class.getConnectionString()))
            try
            {
              
                {
                    if (conn.State != ConnectionState.Open)
                        try
                        {
                            conn.Open();
                        }
                        catch (MySqlException ex)
                        {
                            throw (ex);
                        }

                    string strquery = "SELECT * FROM Bus_Data_AusYPCatUrl WHERE Status = 0";
                    IEnumerable<dynamic> result = conn.Query(strquery);
                    return result;
                }

            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
        }

        public IDictionary<string, double> GetHashrateData(int since)
        {
            var hashrateData = new Dictionary<string, double>();

            try
            {
                if (!IsEnabled)
                    return hashrateData;

                using (var connection = new MySqlConnection(_mySqlProvider.ConnectionString))
                {
                    var results = connection.Query(
                            @"SELECT username, sum(difficulty) AS shares FROM shares WHERE our_result='Y' GROUP BY username");

                    foreach (var row in results)
                    {
                        hashrateData.Add(row.username, row.shares);
                    }
                }
            }
            catch (Exception e)
            {
                _logger.Error("An exception occured while getting share data: {0:l}", e.Message);
            }

            return hashrateData;
        }

        public bool IsSiteExist(string site_name)
        {
            try
            {
                using (MySqlConnection conn = new MySqlConnection(BG_Db_Class.getConnectionString()))
                {
                    conn.Open();
                    string query = "SELECT Site_Id,Site_Name  FROM Site_Table WHERE Site_Name  ='" + site_name + "'";
                    var result = conn.Query<SiteTable>(query).First();
                    if (result != null)
                        return true;
                    else
                        return false;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                //conn.Close();
            }



        }

        public bool Authenticate(IMiner miner)
        {
            try
            {
                if (!IsEnabled)
                    return false;

                using (var connection = new MySqlConnection(_mySqlProvider.ConnectionString))
                {
                    // query the username against mpos pool_worker table.
                    var result = connection.Query<string>(
                        "SELECT password FROM pool_worker WHERE username = @username",
                        new {username = miner.Username}).FirstOrDefault();

                    // if matching record exists for given miner username, then authenticate the miner.
                    // note: we don't check for password on purpose.
                    return result != null;
                }
            }
            catch (Exception e)
            {
                _logger.Error("An exception occured while reading pool_worker table; {0:l}", e.Message);
                return false;
            }
        }

        /// <summary>
        /// Gets an image from the DB
        /// </summary>
        /// <param name="imageId">The id of the image to get</param>
        /// <param name="returnNullIfNotExist">If the image is not found, should null be returned? Otherwise return empty image object.</param>
        /// <returns>OSAEImage or null</returns>
        public OSAEImage GetImage(int imageId, bool returnNullIfNotExist)
        {
            try
            {
                using (MySqlConnection connection = new MySqlConnection(Common.ConnectionString))
                {
                    connection.Open();

                    var image = connection.Query<OSAEImage>("SELECT image_id as 'ID', image_name as 'Name', image_type as 'Type', image_data as 'Data' FROM osae_images WHERE image_id = @id",
                        new { id = imageId }).FirstOrDefault();

                    if (image == null && returnNullIfNotExist)
                    {
                        return null;
                    }
                    else if (image == null && returnNullIfNotExist == false)
                    {
                        throw new Exception("No Image Found");
                    }
                    else
                    {
                        return image;
                    }
                }               
            }
            catch (Exception ex)
            {
                throw new Exception("API - GetImage - Failed to get image with id: " + imageId.ToString() + ", exception encountered: " + ex.Message, ex);
            }
        }

 public int VoicingMessageCount(string id)
 {
     Message message = new Message();
     try
     {
         using (MySqlConnection db = new MySqlConnection(Database.ReminderConnection))
         {
             string query =
                 "select id from messages "
                 + " where id = @id";
             message = db.Query<Message>(query, new { @id = id }).SingleOrDefault();
             //message = db.Query<Message>(query, new { @id = 25 }).SingleOrDefault();
             if (message != null)
             {
                 return message.ID;
             }
             else
             {
                 return 0;
             }
         }
     }
     catch (Exception er)
     {
         Log.LogMessage(er.ToString());
         return 0;
     }
 }

 public static IEnumerable<ComidaTop> GetComidasTop(string from, string to)
 {
     using (MySqlConnection conn = new MySqlConnection(Constants.QueryConn))
     {
         try
         {
             conn.Open();
             string query = @"select p.plato_id, (sum(p.cantidad)) cantidad,
                                 (select descripcion from platos pl where pl.id = p.plato_id) as descripcion,
                                 (select rubro from platos pl where pl.id = p.plato_id) as rubro
                                 from ventas v, pedidos p
                                 where v.id = p.venta_id
                                 and estado= 'CERRADA'
                                 and fecha >= str_to_date('" + from + @"','%d/%m/%Y %H:%i:%s')
                                 and fecha <= str_to_date('" + to + @"','%d/%m/%Y  %H:%i:%s')
                                 group by p.plato_id
                                 order by (sum(p.cantidad)) desc
                                 limit 0, 10";
             var lista = conn.Query<ComidaTop>(query, null, null, true, null, CommandType.Text);
             return lista;
         }
         catch (Exception ex)
         {
             throw ex;
         }
         finally
         {
             conn.Close();
         }
     }
 }

 public static IEnumerable<ComidaTop> GetComidasByRubro(string from, string to)
 {
     using (MySqlConnection conn = new MySqlConnection(Constants.QueryConn))
     {
         try
         {
             conn.Open();
             string query = @"select  pl.rubro as RUBRO, pl.descripcion as DESCRIPCION, pl.id AS PLATO_ID, (sum(p.cantidad)) as CANTIDAD
                                 from ventas v, pedidos p, platos pl
                                 where v.id = p.venta_id
                                 and pl.id = p.plato_id
                                 and v.estado= 'CERRADA'
                                 and fecha >= str_to_date('" + from + @"','%d/%m/%Y %H:%i:%s')
                                 and fecha <= str_to_date('" + to + @"','%d/%m/%Y  %H:%i:%s')
                                 group by pl.id,pl.rubro
                                 order by rubro,(sum(p.cantidad)) DESC";
             var lista = conn.Query<ComidaTop>(query, null, null, true, null, CommandType.Text);
             return lista;
         }
         catch (Exception ex)
         {
             throw ex;
         }
         finally
         {
             conn.Close();
         }
     }
 }

 public static DataModel.User GetValidatedUser(string username, string password, string account)
 {
     using (MySqlConnection sqlConnection = new MySqlConnection(UtilityHelper.GetConnectionString()))
     {
         sqlConnection.Open();
         DataModel.User userObj = sqlConnection.Query<DataModel.User>("Select * from User where [email protected] and [email protected]", new { Username = username, Account = account }).FirstOrDefault();
         if (userObj != null)
         {
             if (string.IsNullOrEmpty(userObj.Salt))
             {
                 if (password.Equals(userObj.Password))
                 {
                     string salt = Guid.NewGuid().ToString();
                     sqlConnection.Execute(@"update User set [email protected], [email protected] where [email protected] and [email protected]", new { Salt = salt, Password = UtilityHelper.HashPassword(userObj.Password, salt), Username = username, Account = account });
                     return userObj;
                 }
             }
             else
             {
                 password=UtilityHelper.HashPassword(password,userObj.Salt);
                 if (password.Equals(userObj.Password))
                 {
                     return userObj;
                 }
             }
         }
     }
     return null;
 }

        public List<OfficeWorkers> GetOfficeUsers()
        {
            List<OfficeWorkers> officeUsers = new List<OfficeWorkers>();
            try
            {
                using (IDbConnection db = new MySqlConnection(ConfigurationValues.GroveHillConnection))
                {
                    //const string query = "select UniqueID as OfficeID,   CONCAT(Name,' ',Department,' ',UniqueID) as OfficeName from officenames "
                    //    + " order by Name";
                    const string query = "select CONCAT(Name,'~',UniqueID) as OfficeID, CONCAT(Name,' ',Department) as OfficeName from officenames "
                        + " order by Name";

                    officeUsers = db.Query<OfficeWorkers>(query).ToList();
                    return officeUsers;
                }
            }
            catch (Exception er)
            {
                return officeUsers;
                //Log.WhichProgram = "Labcorp Interface";
                //Log.LogMessage(er.ToString());
                //return false;
                //return appointments;
            }
        }

        public bool IsPatientFirstLogin(string userName)
        {
            int firstAppointmentCount = 0;

            try
            {
                using (IDbConnection db = new MySqlConnection(ConfigurationValues.GroveHillConnection))
                {
                    const string query = "select count(*) from patients A "
                        + " INNER JOIN users B "
                        + " ON A.UniqueId = B.Id"
                        + " where B.UserName = @UserName"
                        + " and A.firsttimelogin = 'T'";

                    firstAppointmentCount = db.Query<int>(query, new
                    {
                        @UserName = userName
                    }).SingleOrDefault();

                    if (firstAppointmentCount > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
            }
            catch (Exception er)
            {
                return false;
            }
        }