ASP.NET MVC5 realizes mango distribution background management system: Code First integrates Entity Framework quickly

In the previous article, we have built the whole project architecture of mango background management system, and integrated AutoMapper, log components, etc. next, we will use Entity Framework to improve the persistent storage part of the system. In this article, I will construct EF in a fast integrated way, and provide more Linq public methods for business use.

Nuget introduces EF

In XiaoMo.Repository project, right-click project uses Nuget to introduce Entity Framework. Here I use the latest version 6.4

In Web.config of XiaoMo.Service, add Sql Server connection string (in AppSetting)

	<add key="conStr" value="Server=10.60.215.202;Database=mango_sys;Persist Security Info=True;User ID=sa;Password=**" />

Create table

Create tables and entities

Corresponding Entity

[Table("Orders")]

public class OrderInfoEntity
{
    public int Id { get; set; }
    public DateTime OptTime { get; set; }
    public string Address { get; set; }
    public string Uuid { get; set; }
    public string AcceptUser { get; set; }
    public string TelephoneNumber { get; set; }
    public string ProductName { get; set; }
    public int Number { get; set; }
    public double Price { get; set; }
    public double Sum { get; set; }
    public string PackCompany { get; set; }
    public string PackNumber { get; set; }
    public string Status { get; set; }
    public string ExpressNumber { get; set; }
    
}

EF critical configuration

Create the database interaction context DatabaseContext.cs, and specify the connection during initialization. Here, because we use database tables first, we use Database.SetInitializer(null); we use empty policy to initialize EF. Add DBSet for operation table.

public class DatebaseContext : DbContext
{
    static string ConnectionString = "";
    static DatebaseContext()
    {
        Database.SetInitializer<DatebaseContext>(null);
    }

    public DatebaseContext(string connStr)
      : base(connStr)
    {
    }

    public DatebaseContext()
        : base(GetConnectionString())
    {

    }

    private static string GetConnectionString()
    {
        if (string.IsNullOrEmpty(ConnectionString))
        {
            ConnectionString = ConfigurationManager.AppSettings["conStr"].ToString();
        }
        return ConnectionString;
    }

    public virtual DbSet<OrderInfoEntity> Records { get; set; }
    public virtual DbSet<UsersEntity> User { get; set; }
}

Add data interaction interface, only a few key interface methods are listed here

	public interface IRepository{}

	public interface IRepository<TEntity> : IRepository where TEntity : class

//methods

    T Query<T>(Func<IQueryable<TEntity>, T> queryMethod);
	TEntity FirstOrDefault(Expression<Func<TEntity, bool>> predicate);
	TEntity Load<TPrimaryKey>(TPrimaryKey id);
	bool Exist(Expression<Func<TEntity, bool>> predicte);
	TEntity Insert(TEntity entity);
	TEntity Update(TEntity entity);

Create repository manager.cs, encapsulate DatabaseContext, and implement CRUD

	public class RepositoryManager : IDisposable

Create RepositoryEntityBase.cs as a common base class for all Dao layers to inherit mapping database entities.

    public class RepositoryEntityBase<TEntity> : IRepository<TEntity> where TEntity : class

Create OrderDao

	public class OrdersDao : RepositoryEntityBase<OrderInfoEntity>

There is too much content in these two files, so I won't paste them here.

Business access persistence logic

Several examples of interaction using the integrated EF framework

1. Query whether the current user is a proxy

	public bool IsAgent(string username)
	{
		using (var repMgr = new RepositoryManager())
		{
			return repMgr.Exist<UsersEntity>(u => u.UserName == username && u.Role == 1);

		}
	}

2. Judge whether the user name and password are correct

 public string SearchUser(string username,string pwd)
    {
        using (var repMgr = new RepositoryManager())
        {
            var queryuser = repMgr.FirstOrDefault<UsersEntity>(u => u.UserName == username && u.Password == pwd);
		}
	}

3. Query orders by page

public QueryResponse Query(QueryRequest request)
    {
        using (var repMgr = new RepositoryManager())
        {
            var response = new QueryResponse();
            var query = repMgr.Query<OrderInfoEntity>();
							int count = query.Count();
							 var results = query.OrderByDescending(s => s.OptTime).Skip((request.CurrentPage - 1) * request.PageSize).Take(request.PageSize);

            var entities = results.ToList();
            response.TotalCount = count;
            response.Result = entities.ConvertAll(Mapper.Map<OrderInfoEntity, OrderInfo>);
            return response;
			}
		}

4. Create order

	public OrderInfo CreateOrder(OrderInfo order)
    {
        using (var repMgr = new RepositoryManager())
        {
                var entity = Mapper.Map<OrderInfo, OrderInfoEntity>(order);
                repMgr.Insert(entity);
                repMgr.Commit();
                order.Id = entity.Id;
		}
	}

The query and operation of the whole database is quite simple. Here you may ask, if there are some complex SQL statements, how to write them. Here, we provide methods to execute SQL statements in repository manager.cs, just passing in SQL and parameters

 public int ExecuteSqlCommand(string sql, params object[] args)
    {
        return Context.Database.ExecuteSqlCommand(sql, args);
    }

If there is a new service access, we only need to add a new DBSet in the DatebaseContext to create the corresponding Entity and table. No other changes are needed. The access of the entire EF is super convenient.

epilogue

The above is the key configuration code for system integration EF. Because of the long space, I have not paste some of the source code. If you are interested, you can trust the official account of the little ink. Contact the next section, we will continue to build the mango system quickly and integrate IOC with Castle. The whole series will be presented to novices from the perspective of actual combat and dry goods, After that, we will show you. Net Core MVC pose construction series, I hope you can support it. If you need source code, please pay attention to the little ink official account, and get more wonderful sharing of small ink.

Thank you for reading!

This paper is a platform of operation tools such as blog group sending one article and multiple sending OpenWrite Release

Tags: ASP.NET Database SQL

Posted on Sun, 22 Mar 2020 07:43:34 -0400 by MsShelle