ORM之四：调用入口与调用示例

一、ORM入口封装

　 结合上一篇文章与这里的DbProvider().Init()方法，就很明显的知道了是创建一个mssql的实例。那么在DbService的泛型方法中显示提供了单表操作与多表linq查询的入口，其实还存在获取数据库扩展操作的入口。

public  class DbService
    {
        /// <summary>
        /// 默认业务数据库的Provider
        /// </summary>
        public static IDbProvider DefaultProvider
        {
            get
            {
                return new DbProvider().Init(new DbConnInfo()
                {
                    CommandTimeout = 60,
                    ConnectionString = "Data Source = 127.0.0.1;Initial Catalog =DapperData;uid=sa;pwd=sa123456;",
                    Provider = ProviderInfo.SqlServer,
                    DbKey = "DapperData"
                });
            }
        }
    }

    public class DbService<T> where T : class,new()
    {
        /// <summary>
        /// 默认数据库的DAL
        /// </summary>
        public static IReqository<T> Reqository
        {
            get
            {
                return DbService.DefaultProvider.GetReqository<T>(); 
            }
        }

        /// <summary>
        /// 主库指定类型的查询接口，是Linq的入口
        /// </summary>
        public static IQuery<T> Query
        {
            get
            {
                return DbService.DefaultProvider.GetQuery<T>(); 
            }
        }
    }

 

二、单表、多表、扩展示例

　　A、单表

DbService<UserEntity>.Reqository.Single(w => w.Email.Contains("@"));
DbService<UserEntity>.Reqository.Insert(new UserEntity(){});
DbService<UserEntity>.Reqository.Update(up => new UserEntity{Email = "[e-mail]"}, wh => wh.UserId == 4);
DbService<UserRoleEntity>.Reqository.Delete(w => w.Id == 4);

　　

　　B、多表连个查询

var resultList = (from u in DbService<UserEntity>.Query
                join ur in DbService<UserRoleEntity>.Query on new {u = u.UserId, a = u.IsActive} equals
                    new {u = ur.UserId, a = ur.IsActive}
                join r in DbService<RoleEntity>.Query on ur.RoleId equals r.RoleId
                where u.UserId == 1
                orderby u.UserId
                select new UUR
                {
                    UserId = u.UserId,
                    Username = u.Username,
                    PhoneNumber = u.PhoneNumber,
                    RoleName = r.RoleName
                }).SetJoinMode(JoinMode.Inner, JoinMode.Inner).Page(1,1);

            var result = resultList.ToList().FirstOrDefault();

 

　　C、分组查询

//select CICUser.UserId,
            //       MAX([CICUserScore].[score]) 
            //from [CICUser] Join [CICUserScore] on [CICUser].[UserId] = [CICUserScore].[UserId] 
            //group by CICUser.UserId having (SUM(CICUserScore.score) > 200)

var resultList = (from a in DbService<UserEntity>.Query
                 　　join b in DbService<UserScoreEntity>.Query on a.UserId equals b.UserId
                 　　group new {a, b} by new {a.UserId}
                 　　 into g
                 　　where g.Sum(m=>m.b.score)>200
                 　　select new
                  　{
                     　　userId= g.Key.UserId,
                      　　TotalScore = g.Max(b => b.b.score)
                   　}).ToList();

 

　　D、存储过程

 var dbParameters = new DbParameterCollection();

            dbParameters.Add(new DbParameter
            {
                DbType = DbType.Int32,
                Name = "UserId",
                Value =1,
                Direction = ParameterDirection.Input
            });

            var resultList = DbService.DbExtension.ExcuteProcReader<UserEntity>("GetUserData", dbParameters);

 

　　E、事务

using (var dbprovider = DbService.DefaultProvider)
{
　　try
　　{
    　　dbprovider.Begin();

    　　dbprovider.GetReqository<UserScoreEntity>().Insert(new UserScoreEntity
    　　{
        　　Id = Guid.NewGuid().ToString(),
        　　score = 666,
        　　UserId = 4
    　　});

    　　dbprovider.GetReqository<UserRoleEntity>().Insert(new UserRoleEntity
    　　{
        　　Id = 8,
        　　UserId = 4,
        　　RoleId = 2,
        　　IsActive = false
     　　});

     　　dbprovider.Commit();
　　}
　　catch (Exception)
　　{
    　　dbprovider.Rollback();
　　}
}

 

　　F、执行Sql语句

var ds = DbService.DbExtension.ExcuteQuery("select * from dbo.CICUser");

foreach (DataRow row in ds.Tables[0].Rows)
{
　　Console.WriteLine(row[0]);
}