public DBHouse FindById(string houseId)
 {
     using (IDbConnection dbConnection = GetConnection())
     {
         dbConnection.Open();
         foreach (var tableName in SourceTool.GetHouseTableNameDic().Values)
         {
             var house = dbConnection.QueryFirstOrDefault <DBHouse>(@"SELECT Id,
                                     OnlineURL,
                                     Title,
                                     Location,
                                     Price,
                                     PubTime,
                                     City,
                                     Source,
                                     PicURLs,
                                     Labels,
                                     Tags,
                                     RentType,
                                     Latitude,
                                     Longitude,
                                     Text,
                                     Status"
                                                                    + $" from { tableName } where Id = @HouseId", new { HouseId = houseId });
             if (house != null)
             {
                 return(house);
             }
         }
         return(null);
     }
 }
Beispiel #2
0
 public int UpdateLngLat(DBHouse house)
 {
     using (IDbConnection dbConnection = GetConnection())
     {
         dbConnection.Open();
         var tableName = SourceTool.GetHouseTableNameDic()[house.Source];
         return(dbConnection.Execute($"UPDATE {tableName} SET Longitude=@Longitude, Latitude=@Latitude,UpdateTime=now() WHERE Id=@Id;",
                                     new
         {
             Longitude = house.Longitude,
             Latitude = house.Latitude,
             Id = house.Id
         }));
     }
 }
        public int BulkInsertHouses(List <DBHouse> houses)
        {
            if (houses == null || houses.Count == 0)
            {
                return(0);
            }
            var tableName = SourceTool.GetHouseTableName(houses.FirstOrDefault().Source);

            using (IDbConnection dbConnection = GetConnection())
            {
                dbConnection.Open();
                IDbTransaction transaction = dbConnection.BeginTransaction();
                var            result      = dbConnection.Execute("INSERT INTO " + tableName + @" 
                                     (`Title`, `Text`,  
                                    `PicURLs`, `Location`, 
                                    `City`,
                                     `Longitude`, `Latitude`,
                                    `RentType`,`Tags`, 
                                    `PubTime`, `OnlineURL`,
                                     `Price`,`Labels`,
                                    `Source`,`Id`) 
                                     VALUES (@Title, @Text,
                                            @PicURLs, @Location,
                                            @City, 
                                            @Longitude,@Latitude,
                                            @RentType,@Tags,
                                            @PubTime,@OnlineURL,
                                            @Price,@Labels,
                                            @Source,@Id)  ON DUPLICATE KEY UPDATE UpdateTime=now();",
                                                                  houses, transaction: transaction);
                dbConnection.Execute(@"INSERT INTO HouseData 
                        (`JsonData`,`Id`,`OnlineURL`) 
                        VALUES (@JsonData,@Id,@OnlineURL) ON DUPLICATE KEY UPDATE UpdateTime=now();",
                                     houses.Where(h => !string.IsNullOrEmpty(h.JsonData)), transaction: transaction);

                transaction.Commit();
                return(result);
            }
        }