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);
          }
      }
  }

• 运行

转载于:https://www.cnblogs.com/lpush/p/5205659.html