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