public void SaveAllRelations(int idMovie, List <MovieRelation> movieRelations)
{
var sql = new StringBuilder();
sql.AppendLine($"MERGE INTO {Table} AS Target");
sql.AppendLine("USING(");
if (movieRelations.Count > 0)
{
sql.AppendLine(" VALUES");
sql.AppendLine(string.Join(",", movieRelations.Select(mr => $"({idMovie}, {(short)mr.IdTyRole}, {mr.IdRelation})")));
}
else
{
sql.AppendLine(" SELECT 0, 0, 0 WHERE 1=0");
}
sql.AppendLine(") AS Source(idMovie, idTyRole, idRelation)");
sql.AppendLine("ON Source.idMovie = Target.idMovie AND Source.idTyRole = Target.idTyRole AND Source.idRelation = Target.idRelation");
sql.AppendLine("WHEN NOT MATCHED");
sql.AppendLine("THEN INSERT (idMovie, idTyRole, idRelation)");
sql.AppendLine("VALUES (Source.idMovie, Source.idTyRole, Source.idRelation)");
sql.AppendLine($"WHEN NOT MATCHED BY SOURCE AND Target.idMovie = {idMovie}");
sql.AppendLine("THEN DELETE;");
db.Execute(sql.ToString());
}
/// <summary>
/// Sets the password.
/// </summary>
/// <param name="db">The db.</param>
/// <param name="userId">The user id.</param>
/// <param name="newPassword">The new password.</param>
/// <returns></returns>
/// <exception cref="System.ArgumentException"></exception>
private bool SetPassword(DapperContext db, int userId, string newPassword)
{
string hashedPassword = Crypto.HashPassword(newPassword);
if (hashedPassword.Length > 128)
{
throw new ArgumentException("Password is too long.");
}
// Update new password
db.Execute(
string.Format(
"UPDATE {0} SET Password= @Password, PasswordSalt = @PasswordSalt, PasswordChangedDate = @ChangeDate WHERE UserId = @UserId", this.MembershipTableName),
new
{
Password = hashedPassword,
PasswordSalt = string.Empty,
ChangeDate = DateTime.UtcNow,
UserId = userId
});
return true;
}
/// <summary>
/// Checks the password.
/// </summary>
/// <param name="db">The db.</param>
/// <param name="userId">The user id.</param>
/// <param name="password">The password.</param>
/// <returns></returns>
private bool CheckPassword(DapperContext db, int userId, string password)
{
string hashedPassword = GetHashedPassword(db, userId);
bool verificationSucceeded = (hashedPassword != null
&& Crypto.VerifyHashedPassword(hashedPassword, password));
if (verificationSucceeded)
{
// Reset password failure count on successful credential check
db.Execute(string.Format("UPDATE {0} SET PasswordFailureCount = 0 WHERE UserId = @UserId", this.MembershipTableName),
new { UserId = userId });
}
else
{
int failures = this.GetPasswordFailureCount(db, userId);
if (failures != -1)
{
db.Execute(
string.Format(
"UPDATE {0} SET PasswordFailureCount = @Failures, LastPasswordFailureDate = @LastFailureDate WHERE UserId = @UserId",
this.MembershipTableName),
new { UserId = userId, Failures = failures + 1, LastFailureDate = DateTime.UtcNow });
}
}
return verificationSucceeded;
}
public void ImportArea()
{
//1.读取
string path = AppContext.BaseDirectory + "\\AppData\\areas.json";
var json = File.ReadAllText(path, Encoding.UTF8);
var allAreas = json.ToObject <TData>();
var china = new DbModel()
{
LevelType = 0,
MergerName = "中国",
ParentID = 0,
RegionID = 100000,
RegionName = "中国",
ShortName = "中国",
Pinyin = "ZhongGuo",
};
//2.拼接
List <DbModel> models = new List <DbModel>()
{
china
};
//第一层（省）
foreach (var pro in allAreas.result[0])
{
var curProPinyinArr = pro.pinyin;
for (int i = 0; i < curProPinyinArr.Count; i++)
{
curProPinyinArr[i] = curProPinyinArr[i].ToFirstUpper();
}
string proFullName = pro.fullname;
models.Add(new DbModel()
{
Latitude = pro.location["lat"],
LevelType = 1,
Longitude = pro.location["lng"],
MergerName = "中国," + proFullName,
ParentID = 100000,
Pinyin = string.Join("", curProPinyinArr),
ShortName = pro.name,
RegionName = pro.fullname,
RegionID = int.Parse(pro.id),
});
//第二层市
if (pro.cidx == null || !pro.cidx.Any())
{
continue;
}
foreach (var city in allAreas.result[1].GetRange(pro.cidx[0], pro.cidx[1] - pro.cidx[0] + 1))
{
var curCityPinyinArr = city.pinyin;
for (int i = 0; i < curCityPinyinArr.Count; i++)
{
curCityPinyinArr[i] = curCityPinyinArr[i].ToFirstUpper();
}
string cityFullName = city.fullname;
models.Add(new DbModel()
{
Latitude = city.location["lat"],
LevelType = 2,
Longitude = city.location["lng"],
MergerName = "中国," + proFullName + "," + cityFullName,
ParentID = int.Parse(pro.id),
Pinyin = string.Join("", curCityPinyinArr),
ShortName = city.name,
RegionName = city.fullname,
RegionID = int.Parse(city.id),
});
//第三层县
if (city.cidx == null || !city.cidx.Any())
{
continue;
}
foreach (var area in allAreas.result[2].GetRange(city.cidx[0], city.cidx[1] - city.cidx[0] + 1))
{
var curAreaPinyinArr = new List <string>();
if (area.pinyin != null)
{
for (int i = 0; i < area.pinyin.Count; i++)
{
curAreaPinyinArr[i] = area.pinyin[i].ToFirstUpper();
}
}
else if (area.name != null)
{
for (int i = 0; i < area.name.Length; i++)
{
curAreaPinyinArr.Add(area.name[i].ToString().ToPinyin().ToLower().ToFirstUpper());
}
}
else if (area.fullname != null)
{
for (int i = 0; i < area.fullname.Length; i++)
{
curAreaPinyinArr.Add(area.fullname[i].ToString().ToPinyin().ToLower().ToFirstUpper());
}
}
string areaFullName = area.fullname;
models.Add(new DbModel()
{
Latitude = area.location["lat"],
LevelType = 3,
Longitude = area.location["lng"],
MergerName = "中国," + proFullName + "," + cityFullName + "," + areaFullName,
ParentID = int.Parse(city.id),
Pinyin = string.Join("", curAreaPinyinArr),
ShortName = area.name == null ? area.fullname : area.name,
RegionName = area.fullname,
RegionID = int.Parse(area.id),
});
}
}
}
//3.导入
using (var _ctx = new DapperContext("你的连接串", DatabaseType.Mysql))
{
StringBuilder sql = new StringBuilder();
models.ForEach(model =>
{
sql.Append("TRUNCATE table dbo_administrative_region;");
sql.Append($@"insert into `dbo_administrative_region`(`RegionID`,`RegionName` ,`ParentID` ,`ShortName` ,`LevelType` ,`CityCode` ,`ZipCode` ,`MergerName` ,`Longitude` ,`Latitude` ,`Pinyin` )
values({model.RegionID},'{model.RegionName}',{model.ParentID},'{model.ShortName}',{model.LevelType},'{model.CityCode}','{model.ZipCode}',
'{model.MergerName}',{model.Longitude},{model.Latitude},'{model.Pinyin}');");
});
int count = _ctx.Execute(sql.ToString());
}
}
/// <summary>
/// Saves the data.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="content">The content.</param>
/// <param name="tableName">Name of the table.</param>
/// <param name="cachePath">The cache path.</param>
/// <param name="keepHistory">if set to <c>true</c> [keep history].</param>
public virtual void SaveData(DapperContext context, ContentModel content, string tableName, string cachePath)
{
var newDataId = Guid.NewGuid();
var newContent = new ContentModel()
{
DataId = newDataId,
Host = content.Host,
Path = content.Path,
CreateBy =
string.IsNullOrEmpty(content.ModifyBy)
? content.CreateBy
: content.ModifyBy,
Data =
content.Data ?? content.DataStream.ReadAll(),
};
newContent.DataLength = newContent.Data.Length;
content.DataLength = newContent.DataLength;
newContent.ModifyDate = DateTime.UtcNow;
if (!newContent.CreateDate.HasValue || newContent.CreateDate.Value == DateTime.MinValue)
{
newContent.CreateDate = DateTime.UtcNow;
}
context.Execute(string.Format("DELETE FROM {0} WHERE Host = @Host AND Path = @Path", tableName), newContent);
context.Execute(
string.Format("INSERT INTO {0} (IdString, Host, Path, Data, DataLength, CreateDate, CreateBy) VALUES (@DataIdString, @Host, @Path, @Data, @DataLength, @CreateDate, @CreateBy)", tableName), newContent);
content.Data = newContent.Data;
content.DataLength = newContent.DataLength;
content.DataId = newContent.DataId;
this.CacheData(context, content, tableName, cachePath);
}
private void Save(ContentModel content, DapperContext db)
{
// upsert means we have to use two separate statement since this would support sqlce
content.ModifyDate = DateTime.UtcNow;
if (!content.CreateDate.HasValue || content.CreateDate.Value == DateTime.MinValue)
{
content.CreateDate = DateTime.UtcNow;
}
var sqlCommand = string.Format(
@"UPDATE {0} SET ModifyDate = @ModifyDate, ModifyBy = @ModifyBy, DataIdString = @DataIdString, DataLength = @DataLength WHERE Host = @Host AND Path = @Path",
this.TableName);
db.Execute(sqlCommand, content);
sqlCommand =
string.Format(
"INSERT INTO {0} (Host, Path, ParentPath, CreateDate, CreateBy, ModifyDate, ModifyBy, DataIdString, DataLength) SELECT @Host, @Path, @ParentPath, @CreateDate, @CreateBy, @ModifyDate, @ModifyBy, @DataIdString, @DataLength WHERE NOT EXISTS (SELECT 1 FROM {0} WHERE Host = @Host AND Path = @Path)",
this.TableName);
db.Execute(sqlCommand, content);
}
/// <summary>
/// Removes the specified content path or route.
/// </summary>
/// <param name="content">The content - requires host, path, and name property. If name is set to "*" then removes all for host and path.</param>
public void Remove(ContentModel content)
{
content.Path = this.NormalizedPath(content.Path);
var path = content.Path;
var operString = "=";
if (path.EndsWith("/", StringComparison.OrdinalIgnoreCase))
{
operString = "LIKE";
path = path.TrimEnd('*').TrimEnd('/') + "%";
}
using (var db = new DapperContext(this.ConnectionStringName))
{
db.Execute(
string.Format(
"DELETE FROM {0} WHERE Host = @Host AND (Path {1} @Path)", this.TableName, operString),
new { Path = path, Host = content.Host });
}
}
