public OracleCommand OracleCMD(string strQry)
{
GetTableData myObjGetTableData = new GetTableData();
try
{
if (!myObjGetTableData.checkUserSession())
{
throw new cs.LMException("SessionDied");
}
cmd = new OracleCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = strQry;
cmd.Connection = DBConnect();
return(cmd);
}
catch (cs.LMException ex)
{
throw ex;
}
catch (DbException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally{}
}
public object OracleExecuteScalar(string strQry)
{
GetTableData myObjGetTableData = new GetTableData();
try
{
cmd = new OracleCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = strQry;
cmd.Connection = DBConnect();
return(cmd.ExecuteScalar());
}
catch (NullReferenceException ex)
{
return("");
}
catch (cs.LMException ex)
{
throw ex;
}
catch (DbException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
DBDisconnect();
}
}
public int MySqlExecuteNonQuery(string strQry)
{
GetTableData myObjGetTableData = new GetTableData();
try
{
if (!myObjGetTableData.checkUserSession())
{
throw new cs.LMException("SessionDied");
}
cmdmysql = new MySqlCommand();
cmdmysql.CommandType = CommandType.Text;
cmdmysql.CommandText = strQry;
cmdmysql.Connection = DBConnectMySql();
return(cmdmysql.ExecuteNonQuery());
}
catch (cs.LMException ex)
{
throw ex;
}
catch (DbException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally { DBDisconnectMySql(); }
}
public DataSet ExecuteDataSet(string strQry)
{
GetTableData myObjGetTableData = new GetTableData();
SqlDataAdapter adapter = new SqlDataAdapter();
DataSet dataSet = null;
try
{
if (!myObjGetTableData.checkUserSession())
{
throw new cs.LMException("SessionDied");
}
cmdsql = new SqlCommand();
cmdsql.CommandType = CommandType.Text;
cmdsql.CommandText = strQry;
cmdsql.Connection = DBConnectSql();
adapter.SelectCommand = cmdsql;
dataSet = new DataSet();
adapter.Fill(dataSet);
}
catch (cs.LMException ex)
{
cs.WriteLogForEx.WriteLog(ex);
HttpContext.Current.Session["error500"] = "Message: " + ex.Message + "<br/>StackTrace: " + ex.StackTrace;
throw ex;
}
catch (DbException ex)
{
cs.WriteLogForEx.WriteLog(ex);
HttpContext.Current.Session["error500"] = "Message: " + ex.Message + "<br/>StackTrace: " + ex.StackTrace;
throw ex;
}
catch (Exception ex)
{
cs.WriteLogForEx.WriteLog(ex);
HttpContext.Current.Session["error500"] = "Message: " + ex.Message + "<br/>StackTrace: " + ex.StackTrace;
throw ex;
}
finally
{
DBDisconnectSql();
}
return(dataSet);
}
public DataSet SP_OracleExecuteDataSet(string SPName, string[] Params1, string[] Params2)
{
GetTableData myObjGetTableData = new GetTableData();
ModifyDB myObj = new ModifyDB();
OracleDataAdapter adapter = new OracleDataAdapter();
DataSet dataSet = null;
try
{
if (!myObjGetTableData.checkUserSession())
{
throw new cs.LMException("SessionDied");
}
cmd = new OracleCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = SPName;
cmd.Connection = myObj.DBConnect();
if ((Params1 != null || Params1.Length > 0) && (Params2 != null || Params2.Length > 0))
{
for (int i = 0; i < Params1.Length; i++)
{
cmd.Parameters.AddWithValue(Params1[i], Params2[i]);
}
}
adapter.SelectCommand = cmd;
dataSet = new DataSet();
adapter.Fill(dataSet);
}
catch (cs.LMException ex)
{
throw ex;
}
catch (DbException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally { DBDisconnect(); }
return(dataSet);
}
public OracleDataReader SP_OracleExecuteReader(string SPName, string[] Params1, string[] Params2)
{
GetTableData myObjGetTableData = new GetTableData();
ModifyDB myObj = new ModifyDB();
OracleCommand cmd;
try
{
if (!myObjGetTableData.checkUserSession())
{
throw new cs.LMException("SessionDied");
}
cmd = new OracleCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = SPName;
cmd.Connection = myObj.DBConnect();
if ((Params1 != null || Params1.Length > 0) && (Params2 != null || Params2.Length > 0))
{
for (int i = 0; i < Params1.Length; i++)
{
cmd.Parameters.AddWithValue(Params1[i], Params2[i]);
}
}
return(cmd.ExecuteReader());
}
catch (cs.LMException ex)
{
throw ex;
}
catch (DbException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally { }
}
public DataSet OracleExecuteDataSet(string strQry)
{
GetTableData myObjGetTableData = new GetTableData();
OracleDataAdapter adapter = new OracleDataAdapter();
DataSet dataSet = null;
try
{
if (!myObjGetTableData.checkUserSession())
{
throw new cs.LMException("SessionDied");
}
cmd = new OracleCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = strQry;
cmd.Connection = DBConnect();
adapter.SelectCommand = cmd;
dataSet = new DataSet();
adapter.Fill(dataSet);
}
catch (cs.LMException ex)
{
throw ex;
}
catch (DbException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
DBDisconnect();
}
return(dataSet);
}
public static void EnsureSeedData(IServiceProvider serviceProvider)
{
Console.WriteLine("Seeding database...");
using (var scope = serviceProvider.GetRequiredService <IServiceScopeFactory>().CreateScope())
{
scope.ServiceProvider.GetRequiredService <PersistedGrantDbContext>().Database.Migrate();
{
var context = scope.ServiceProvider.GetRequiredService <ConfigurationDbContext>();
context.Database.Migrate();
EnsureSeedData(context);
}
{
var context = scope.ServiceProvider.GetService <ApplicationDbContext>();
context.Database.Migrate();
var userMgr = scope.ServiceProvider.GetRequiredService <UserManager <ApplicationUser> >();
var roleMgr = scope.ServiceProvider.GetRequiredService <RoleManager <ApplicationRole> >();
var BlogCore_Users = JsonHelper.ParseFormByJson <List <UserModel> >(GetTableData.GetData(nameof(UserModel)));
var BlogCore_Roles = JsonHelper.ParseFormByJson <List <RoleModel> >(GetTableData.GetData(nameof(RoleModel)));
var BlogCore_UserRoles = JsonHelper.ParseFormByJson <List <UserRoleModel> >(GetTableData.GetData(nameof(UserRoleModel)));
foreach (var user in BlogCore_Users)
{
if (user == null || user.LoginName == null)
{
continue;
}
var userItem = userMgr.FindByNameAsync(user.LoginName).Result;
var rid = BlogCore_UserRoles.FirstOrDefault(d => d.UserId == user.Id)?.RoleId;
var rName = BlogCore_Roles.Where(d => d.Id == rid).Select(d => d.Id).ToList();
if (userItem == null)
{
if (rid > 0 && rName.Count > 0)
{
userItem = new ApplicationUser
{
UserName = user.LoginName,
LoginName = user.UserName,
Sex = user.Sex,
Age = user.Age,
Birthday = user.Brithday,
Address = user.Address,
IsDelete = user.IsDeleted
};
//var result = userMgr.CreateAsync(userItem, "BlogIdp123$" + item.uLoginPWD).Result;
// 因为导入的密码是 MD5密文，所以这里统一都用初始密码了
var pwdInit = "BlogIdp123$InitPwd";
//if (userItem.UserName== "blogadmin")
//{
// pwdInit = "#InitPwd";
//}
var result = userMgr.CreateAsync(userItem, pwdInit).Result;
if (!result.Succeeded)
{
throw new Exception(result.Errors.First().Description);
}
var claims = new List <Claim>
{
new Claim(JwtClaimTypes.Name, user.UserName),
new Claim(JwtClaimTypes.Email, $"{user.LoginName}@email.com"),
};
claims.AddRange(rName.Select(s => new Claim(JwtClaimTypes.Role, s.ToString())));
result = userMgr.AddClaimsAsync(userItem, claims).Result;
if (!result.Succeeded)
{
throw new Exception(result.Errors.First().Description);
}
Console.WriteLine($"{userItem?.UserName} created");//AspNetUserClaims 表
}
else
{
Console.WriteLine($"{user?.LoginName} doesn't have a corresponding role.");
}
}
else
{
Console.WriteLine($"{userItem?.UserName} already exists");
}
}
// 迁移【角色】信息，Role信息统一在这里处理，Blog.Core 只负责读，不负责写
foreach (var item in BlogCore_Roles)
{
if (item == null || item.Name == null)
{
continue;
}
var roleModel = roleMgr.FindByNameAsync(item.Name).Result;
if (roleModel == null)
{
roleModel = new ApplicationRole
{
Name = item.Name,
Description = item.Description,
IsDeleted = (bool)(item.IsDeleted),
CreateBy = item.CreateBy,
CreateId = item.CreateId,
CreateTime = item.CreateTime,
ModifyBy = item.ModifyBy,
ModifyId = item.ModifyId,
ModifyTime = item.ModifyTime,
Enabled = item.Enabled,
OrderSort = item.OrderSort,
NormalizedName = item.Name,
};
var result = roleMgr.CreateAsync(roleModel).Result;
if (!result.Succeeded)
{
throw new Exception(result.Errors.First().Description);
}
}
else
{
Console.WriteLine($"{roleModel?.Name} already exists");
}
}
}
}
Console.WriteLine("Done seeding database.");
Console.WriteLine();
}
/// <summary>
/// get role listing
/// </summary>
/// <returns></returns>
public async Task <List <RoleModel> > QueryRoleList()
{
return(await Task.Run(() => { return JsonHelper.ParseFormByJson <List <RoleModel> >(GetTableData.GetData(nameof(RoleModel))); }));
}
/// <summary>
/// 异步添加种子数据
/// </summary>
/// <param name="myContext"></param>
/// <returns></returns>
public static async Task SeedAsync(MyContext myContext)
{
try
{
// 如果生成过了，第二次，就不用再执行一遍了,注释掉该方法即可
#region 自动创建数据库暂停服务
// 自动创建数据库，注意版本是 sugar 5.x 版本的
// 注意：这里还是有些问题，比如使用mysql的话，如果通过这个方法创建空数据库，字符串不是utf8的，所以还是手动创建空的数据库吧，然后设置数据库为utf-8，我再和作者讨论一下。
// 但是使用SqlServer 和 Sqlite 好像没有这个问题。
//myContext.Db.DbMaintenance.CreateDatabase();
#endregion
// 创建表
myContext.CreateTableByEntity(false,
typeof(Advertisement),
typeof(BlogArticle),
typeof(Guestbook),
typeof(Module),
typeof(ModulePermission),
typeof(OperateLog),
typeof(PasswordLib),
typeof(Permission),
typeof(Role),
typeof(RoleModulePermission),
typeof(UserModel),
typeof(Topic),
typeof(TopicDetail),
typeof(UserRole));
// 后期单独处理某些表
//myContext.Db.CodeFirst.InitTables(typeof(sysUserInfo));
//myContext.Db.CodeFirst.InitTables(typeof(Permission));
//myContext.Db.CodeFirst.InitTables(typeof(Advertisement));
Console.WriteLine("Database:Blog created success!");
Console.WriteLine();
if (Appsettings.app(new string[] { "AppSettings", "SeedDBDataEnabled" }).ObjToBool())
{
Console.WriteLine("Seeding database...");
#region BlogArticle
if (!await myContext.Db.Queryable <BlogArticle>().AnyAsync())
{
myContext.GetEntityDB <BlogArticle>().Insert(
new BlogArticle()
{
IsDeleted = false,
Category = "小计",
CommentNum = 999,
Content = "<p>1。。。。。</p>",
Submitter = "yunqian",
CreateTime = DateTime.Now,
ModifyTime = DateTime.Now,
Title = "水浒之风雪山神庙",
Traffic = 3,
Remark = "林教头命悬一线......",
});
Console.WriteLine("Table:BlogArticle created success!");
}
else
{
Console.WriteLine("Table:BlogArticle already exists...");
}
#endregion
#region Module
if (!await myContext.Db.Queryable <Module>().AnyAsync())
{
myContext.GetEntityDB <Module>().InsertRange(
JsonHelper.ParseFormByJson <List <Module> >(GetTableData.GetData(nameof(Module))));
Console.WriteLine("Table:Module created success!");
}
else
{
Console.WriteLine("Table:Module already exists...");
}
#endregion
#region Permission
if (!await myContext.Db.Queryable <Permission>().AnyAsync())
{
//myContext.GetEntityDB<Permission>().InsertRange(JsonHelper.ParseFormByJson<List<Permission>>(GetNetData.Get(string.Format(GitJsonFileFormat, "Permission"))));
Console.WriteLine("Table:Permission created success!");
}
else
{
Console.WriteLine("Table:Permission already exists...");
}
#endregion
#region Role
if (!await myContext.Db.Queryable <Role>().AnyAsync())
{
//myContext.GetEntityDB<Role>().InsertRange(JsonHelper.ParseFormByJson<List<Role>>(GetNetData.Get(string.Format(GitJsonFileFormat, "Role"))));
Console.WriteLine("Table:Role created success!");
}
else
{
Console.WriteLine("Table:Role already exists...");
}
#endregion
#region RoleModulePermission
if (!await myContext.Db.Queryable <RoleModulePermission>().AnyAsync())
{
//myContext.GetEntityDB<RoleModulePermission>().InsertRange(JsonHelper.ParseFormByJson<List<RoleModulePermission>>(GetNetData.Get(string.Format(GitJsonFileFormat, "RoleModulePermission"))));
Console.WriteLine("Table:RoleModulePermission created success!");
}
else
{
Console.WriteLine("Table:RoleModulePermission already exists...");
}
#endregion
#region Topic
if (!await myContext.Db.Queryable <Topic>().AnyAsync())
{
//myContext.GetEntityDB<Topic>().InsertRange(JsonHelper.ParseFormByJson<List<Topic>>(GetNetData.Get(string.Format(GitJsonFileFormat, "Topic"))));
Console.WriteLine("Table:Topic created success!");
}
else
{
Console.WriteLine("Table:Topic already exists...");
}
#endregion
#region TopicDetail
if (!await myContext.Db.Queryable <TopicDetail>().AnyAsync())
{
//myContext.GetEntityDB<TopicDetail>().InsertRange(JsonHelper.ParseFormByJson<List<TopicDetail>>(GetNetData.Get(string.Format(GitJsonFileFormat, "TopicDetail"))));
Console.WriteLine("Table:TopicDetail created success!");
}
else
{
Console.WriteLine("Table:TopicDetail already exists...");
}
#endregion
#region UserRole
if (!await myContext.Db.Queryable <UserRole>().AnyAsync())
{
//myContext.GetEntityDB<UserRole>().InsertRange(JsonHelper.ParseFormByJson<List<UserRole>>(GetNetData.Get(string.Format(GitJsonFileFormat, "UserRole"))));
Console.WriteLine("Table:UserRole created success!");
}
else
{
Console.WriteLine("Table:UserRole already exists...");
}
#endregion
#region sysUserInfo
if (!await myContext.Db.Queryable <UserModel>().AnyAsync())
{
//myContext.GetEntityDB<sysUserInfo>().InsertRange(JsonHelper.ParseFormByJson<List<sysUserInfo>>(GetNetData.Get(string.Format(GitJsonFileFormat, "sysUserInfo"))));
Console.WriteLine("Table:sysUserInfo created success!");
}
else
{
Console.WriteLine("Table:sysUserInfo already exists...");
}
#endregion
Console.WriteLine("Done seeding database.");
}
Console.WriteLine();
}
catch (Exception ex)
{
throw new Exception("1、注意要先创建空的数据库\n2、" + ex.Message);
}
}
/// <summary>
/// get user information based on user name
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public async Task <UserModel> QueryUserByName(string name)
{
var list = JsonHelper.ParseFormByJson <List <UserModel> >(GetTableData.GetData(nameof(UserModel)));
return(await Task.Run(() => { return list.FirstOrDefault(u => u.UserName.Equals(name)); }));
}