Nancy跨平台开发总结(五)三层架构之数据库访问
前边的登录用户是直接写在代码里，这一节实现从Mysql数据库里读取用户并验证用户信息。
数据访问层是从NopCommerce开源框架中分离出来的，加上了对MySQL的支持，大部分可以直接拿来使用。
-
安装Mysql
用winscp上传安装包到centos
解压
#tar –xvf mysql-5.7.10-1.el7.x86_64.rpm-bundle.tar
安装
#rpm –ivh mysql-community-* -
启动Mysql
1.1 启动Mysql
Mysql安装后默认不充许远程连接，所以在使前先要做一个配置
获取root用户的临时密码
#grep 'temporary password' /var/log/mysqld.log
连接Mysql，用刚才的临时密码登录
# mysql -uroot -p
修改root用户的密码
mysql>ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';
修改权限，允许远程连接
mysql>Grant all privileges on *.* to 'root'@'%' identified by 'MyNewPass4!' with grant option;
退出
mysql>exit
重启
Service mysqld restart
-
准备数据库
表
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`UserName` varchar(255) NOT NULL,
`Password` varchar(255) NOT NULL,
`Guid` varchar(255) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf32;
数据
INSERT INTO `user` VALUES ('1', 'admin', '123456', UUID());
-
建新一个类库项目Nop.Core,从NopCommerce中拷贝如下代码文件
- 修改DataSettingsManager的MapPath方法
protected virtual string MapPath(string path) { string baseDirectory = AppDomain.CurrentDomain.BaseDirectory; if (string.IsNullOrEmpty(baseDirectory)) { baseDirectory = Environment.CurrentDirectory; } path = path.TrimStart('~'); return string.Format("{0}{1}", baseDirectory, path); }
- 新建类库Nop.Data，添加Nop.Core的引用，并为Nop.Data和WebSite添加以下Nuget包
- EntityFramework
- Mysql.Data
- Mysql.Data.Entity
- 从NopCommerce中拷出下边的代码文件
修改EfDataProviderManager代码去掉SQLCE去的相关代码
- 添加一个Mysql的DataProvider
using System; using System.Collections.Generic; using System.Data.Common; using System.Data.Entity; using MySql.Data.MySqlClient; using MySql.Data.Entity; using System.IO; using System.Text; using System.Web.Hosting; using Nop.Core.Data; using Nop.Data.Initializers; namespace Nop.Data { public class MySqlServerDataProvider : IDataProvider { #region Utilities protected virtual string[] ParseCommands(string filePath, bool throwExceptionIfNonExists) { if (!File.Exists(filePath)) { if (throwExceptionIfNonExists) throw new ArgumentException(string.Format("Specified file doesn't exist - {0}", filePath)); return new string[0]; } var statements = new List<string>(); using (var stream = File.OpenRead(filePath)) using (var reader = new StreamReader(stream)) { string statement; while ((statement = ReadNextStatementFromStream(reader)) != null) { statements.Add(statement); } } return statements.ToArray(); } protected virtual string ReadNextStatementFromStream(StreamReader reader) { var sb = new StringBuilder(); while (true) { var lineOfText = reader.ReadLine(); if (lineOfText == null) { if (sb.Length > 0) return sb.ToString(); return null; } if (lineOfText.TrimEnd().ToUpper() == "GO") break; sb.Append(lineOfText + Environment.NewLine); } return sb.ToString(); } #endregion #region Methods /// <summary> /// Initialize connection factory /// </summary> public virtual void InitConnectionFactory() { var connectionFactory = new MySqlConnectionFactory(); //TODO fix compilation warning (below) #pragma warning disable 0618 Database.DefaultConnectionFactory = connectionFactory; } /// <summary> /// Initialize database /// </summary> public virtual void InitDatabase() { InitConnectionFactory(); SetDatabaseInitializer(); } /// <summary> /// Set database initializer /// </summary> public virtual void SetDatabaseInitializer() { //pass some table names to ensure that we have nopCommerce 2.X installed var tablesToValidate = new[] { "Customer", "Discount", "Order", "Product", "ShoppingCartItem" }; //custom commands (stored proedures, indexes) var customCommands = new List<string>(); //use webHelper.MapPath instead of HostingEnvironment.MapPath which is not available in unit tests customCommands.AddRange(ParseCommands(HostingEnvironment.MapPath("~/App_Data/Install/SqlServer.Indexes.sql"), false)); //use webHelper.MapPath instead of HostingEnvironment.MapPath which is not available in unit tests customCommands.AddRange(ParseCommands(HostingEnvironment.MapPath("~/App_Data/Install/SqlServer.StoredProcedures.sql"), false)); var initializer = new CreateTablesIfNotExist<NopObjectContext>(tablesToValidate, customCommands.ToArray()); Database.SetInitializer(initializer); } /// <summary> /// A value indicating whether this data provider supports stored procedures /// </summary> public virtual bool StoredProceduredSupported { get { return true; } } /// <summary> /// Gets a support database parameter object (used by stored procedures) /// </summary> /// <returns>Parameter</returns> public virtual DbParameter GetParameter() { return new MySqlParameter(); } #endregion } }
- 修改EfDataProviderManager
- 新建项目WebSite.Model，添加类User对应数据中的表
public class User : BaseEntity { public User() { } /// <summary> /// 用户名 /// </summary> public string UserName { get; set; } /// <summary> /// 密码 /// </summary> public string Password { get; set; } /// <summary> /// Guid /// </summary> public string Guid { get; set; } }
再添加一个类DataResult用作通用的数据
namespace WebSite.Model { public class DataResult { /// <summary> /// 执行结果:0,成功;1,失败 /// </summary> public int Result { get; set; } public string Message { get; set; } } public class DataResult<T> : DataResult { public T Content { get; set; } } }
- 添加对象和数据库表的映射关系。在Nop.Data项目的Mapping目录下添加类UserMapping
using Website.Model; namespace Nop.Data.Mapping { public class UserMapping : NopEntityTypeConfiguration<User> { public UserMapping() { this.ToTable("user"); this.HasKey(m => m.Id); this.Property(m => m.UserName).IsRequired(); this.Property(m => m.Password).IsRequired(); this.Property(m => m.Guid).IsRequired(); } } }
- 新建项目WebSite.Service，作为业务处理层，添加一个接口和实体类
public interface IUserService { /// <summary> /// 验证用户与密码 /// </summary> /// <param name="userName"></param> /// <param name="password"></param> /// <returns></returns> DataResult<User> ValidateUser(string userName, string password); }
using Nop.Core.Data; using Nop.Core.Caching; using WebSite.Model; namespace WebSite.Service { public class UserService : IUserService { /// <summary> /// Key for caching /// </summary> /// <remarks> /// {0} : User ID /// </remarks> private const string Users_BY_ID_KEY = "WebSite.User.id-{0}"; private readonly IRepository<User> userRepository = null; private readonly ICacheManager _cacheManager; //private readonly IEventPublisher _eventPublisher; public UserService(IRepository<User> userRepository, ICacheManager cacheManager) { this.userRepository = userRepository; this._cacheManager = cacheManager; } /// <summary> /// 验证用户与密码 /// </summary> /// <param name="userName"></param> /// <param name="password"></param> /// <returns></returns> public DataResult<User> ValidateUser(string userName, string password) { DataResult<User> result = new DataResult<User>(); if (string.IsNullOrEmpty(userName) || string.IsNullOrEmpty(password)) { result.Message = "用户名或密码不能为空!"; return result; } var user = (from item in userRepository.Table where item.UserName == userName && item.Password == password select item).SingleOrDefault<User>(); if (user == null) { result.Message = "用户名或密码错误!"; } else { //缓存数据 string key = string.Format(Users_BY_ID_KEY, user.Id); user = _cacheManager.Get(key, () => { return user; }); result.Content = user; result.Result = 1; } return result; } } }
-
数据库连接配置。在App_Data文件中添加Settings.txt
DataProvider: mysql DataConnectionString: server=192.168.47.128;port=3306;user=root;password=xxxxxxxx;database=WebSite;Connection Timeout=120;Charset=utf8;Allow Zero Datetime=true
在Bootstrapper在的ConfigureApplicationContainer事件中添加以下代码
//data layer DataSettingsManager dataSettingsManager = new DataSettingsManager(); DataSettings dataSettings = dataSettingsManager.LoadSettings(); var dataProviderManager = new EfDataProviderManager(dataSettings); var dataProvider = dataProviderManager.LoadDataProvider(); dataProvider.InitConnectionFactory(); builder.Register<IDbContext>(c => new NopObjectContext(dataSettings.DataConnectionString)).InstancePerLifetimeScope(); builder.RegisterGeneric(typeof(EfRepository<>)).As(typeof(IRepository<>)).InstancePerLifetimeScope(); //Cache builder.RegisterType<NopNullCache>().As<ICacheManager>().InstancePerLifetimeScope(); //Service builder.RegisterType<UserService>().As<IUserService>().InstancePerLifetimeScope();
- 修改AccountController
using System; using System.Collections.Generic; using System.Linq; using System.Web; using Nancy; using Nancy.Extensions; using Nancy.Security; using Nancy.ModelBinding; using Nancy.Authentication.Forms; using WebSite.Models; using WebSite.Service; using WebSite.Model; namespace WebSite.Controller { public class AccountController : NancyModule { private IUserService service; public AccountController(IUserService service) : base("Account") { this.service = service; Get["/Login"] = paramters => { //生成CSRF token. this.CreateNewCsrfToken(); ViewBag.Title = "登录"; ViewBag.ErrorMsg = this.Request.Query.error; return View["Login"]; }; Post["/Login"] = _ => { //CSRF token 检验 this.ValidateCsrfToken(); string userName = (string)this.Request.Form.UserName; string password = (string)this.Request.Form.Password; return Login(userName, password); }; Get["/Logout"] = x => { this.CreateNewCsrfToken(); Session.DeleteAll(); return this.LogoutAndRedirect("~/"); }; } private dynamic Login(string userName,string password) { DataResult<User> dataResult = service.ValidateUser(userName, password); if (dataResult.Result != 1) { return this.Context.GetRedirect(string.Format("~/account/login?error={0}", "用户名或密码不能为空!")); } var userGuid = new Guid(dataResult.Content.Guid); DateTime? expiry = null; if (this.Request.Form.RememberMe.HasValue) { expiry = DateTime.Now.AddDays(7); } this.Context.Request.Session[dataResult.Content.Guid] = new UserInfo { UserName = dataResult.Content.UserName, Password = dataResult.Content.Password }; return this.LoginAndRedirect(userGuid, expiry); } } } - 运行
