Git.Framework 框架隨手記--ORM查詢資料集合 二

賀臣發表於2014-05-15

  一.  分組查詢

    在SQL中使用Group By 來對資料分組,在實際中分組中一般與聚合函式一併使用。在Git.Framework中提供了相應的分組方法

DataTable Group(T entity);
DataTable Group(T entity, bool isOpenTrans);
IEnumerable<System.Linq.IGrouping<TKey, T>> Group<TKey>(T entity, Func<T, TKey> keySelector);

    對某張表進行分組查詢需要使用Group方法, 在分組的時候必須指定分組的欄位。在Entity類中有一個方法Group方法,這個方法就是用於指定分組的欄位.

AdminEntity entity = new AdminEntity();
entity.Group(a => a.CreateUser);
DataTable table = this.Admin.Group(entity);

    上面這段程式碼是使用CreateUser欄位對錶進行分組,返回兩個欄位: 一個CreateUser,一個是分組的行數

AdminEntity entity = new AdminEntity();
entity.Group(a => new { a.CreateUser,a.LoginCount});
DataTable table = this.Admin.Group(entity);

    上面這段程式碼是根據兩個欄位分組,DataTable中返回三個欄位,由上可知.

IEnumerable<System.Linq.IGrouping<TKey, T>> Group<TKey>(T entity, Func<T, TKey> keySelector);

    這是一個高大上的方法,上面返回DataTable實屬無奈,技藝當時還不精,所以只能硬生生的這麼返回值。這個泛型的方法有點意思,也有點高階了,做Linq 的都知道。沒錯這個和Linq to SQL中的效果是一樣的。返回一個Key--集合的模式.(對於動態型別目前還是升級中)

var query= this.Admin.Group(entity, a => new {a.UserCode });
foreach (var item in query)
{
       string key=item.Key.UserCode;
       int count = item.Count();
}

 

   二. 連線方法

    在SQL Server中使用Left join, Right join 等關鍵字用於來連線查詢,這個對於一個碼農來說已經簡單的不能再簡單了的事情. 下面先看看一個做連結查詢的規則問題

SELECT t0.[ID],t0.[UserName],t0.[PassWord],t0.[UserCode],t0.[RealName],t0.[Email],t0.[Mobile],t0.[Phone],t0.[CreateTime],t0.[CreateIp],t0.[CreateUser],t0.[LoginCount],t0.[Picture],t0.[UpdateTime],t0.[IsDelete],t0.[Status],t0.[DepartNum],t0.[ParentCode],t0.[RoleNum],t0.[Remark],t1.[RoleName] AS RoleName 
FROM [dbo].[Admin] AS t0  
LEFT JOIN [dbo].[SysRole] AS t1 ON  t0.[RoleNum]=t1.[RoleNum]  

    使用關鍵字LEFT JOIN [Table] ON 條件

   在Entity中提供了幾個用於連線查詢的方法.

public void Inner<T>(T entity, params Git.Framework.DataTypes.Params<string, string>[] param) where T : BaseEntity;
public void Left<T>(T entity, params Git.Framework.DataTypes.Params<string, string>[] param) where T : BaseEntity;
public void Right<T>(T entity, params Git.Framework.DataTypes.Params<string, string>[] param) where T : BaseEntity;

      看到上面幾個方法可能有點發暈,先來解釋一下具體是怎麼回事。

      T 是一個實體對映物件,就是資料庫中對映的實體類, T entity 相當於Left Join中的右表

      params Git.Framework.DataTypes.Params<string, string>[] param 這個又有點奇怪了,這個是用於指定左右表的連線欄位,不明白先看看下面這個類

[Serializable]
    public class Params<T1>
    {
        public T1 Item1 { get; set; }

        public Params()
        {

        }
    }

    [Serializable]
    public class Params<T1, T2>
    {
        public T1 Item1 { get; set; }
        public T2 Item2 { get; set; }

        public Params()
        {

        }
    }

    [Serializable]
    public class Params<T1, T2, T3>
    {
        public T1 Item1 { get; set; }
        public T2 Item2 { get; set; }
        public T3 Item3 { get; set; }

        public Params()
        {

        }
    }

    [Serializable]
    public class Params<T1, T2, T3, T4>
    {
        public T1 Item1 { get; set; }
        public T2 Item2 { get; set; }
        public T3 Item3 { get; set; }
        public T4 Item4 { get; set; }

        public Params()
        {
        }
    }

    [Serializable]
    public class Params<T1, T2, T3, T4, T5>
    {
        public T1 Item1 { get; set; }
        public T2 Item2 { get; set; }
        public T3 Item3 { get; set; }
        public T4 Item4 { get; set; }
        public T5 Item5 { get; set; }

        public Params()
        {
        }
    }

    [Serializable]
    public class Params<T1, T2, T3, T4, T5,T6>
    {
        public T1 Item1 { get; set; }
        public T2 Item2 { get; set; }
        public T3 Item3 { get; set; }
        public T4 Item4 { get; set; }
        public T5 Item5 { get; set; }
        public T6 Item6 { get; set; }

        public Params()
        {
        }
    }

    [Serializable]
    public class Params<T1, T2, T3, T4, T5, T6,T7>
    {
        public T1 Item1 { get; set; }
        public T2 Item2 { get; set; }
        public T3 Item3 { get; set; }
        public T4 Item4 { get; set; }
        public T5 Item5 { get; set; }
        public T6 Item6 { get; set; }
        public T7 Item7 { get; set; }

        public Params()
        {
        }
    }

    [Serializable]
    public class Params<T1, T2, T3, T4, T5, T6,T7,T8>
    {
        public T1 Item1 { get; set; }
        public T2 Item2 { get; set; }
        public T3 Item3 { get; set; }
        public T4 Item4 { get; set; }
        public T5 Item5 { get; set; }
        public T6 Item6 { get; set; }
        public T7 Item7 { get; set; }
        public T8 Item8 { get; set; }

        public Params()
        {
        }
    }
Params<> 泛型類

    很熟悉是不是,.NET4.0中有一個一樣的類,沒錯是一樣的。只不過在寫這個框架的時候還沒有這個版本,所以現在就一直保留了。

    Params<string, string> 說明有兩個位string 型別的欄位,一個用於指明左表的欄位名 第二個用於指定右表的欄位名

    params Git.Framework.DataTypes.Params<string, string>[] param 而這個是陣列說明可以自定多個關聯欄位 也就是on後面的條件 

    params 的作用是幹什麼的,不懂,自己去查。

AdminEntity entity = new AdminEntity();
entity.IncludeAll();
entity.Where(a => a.UserName == userName).And(a => a.PassWord == passWord);
SysRoleEntity roleEntity = new SysRoleEntity();
roleEntity.Include("RoleName", "RoleName");
entity.Left<SysRoleEntity>(roleEntity, new Params<string, string>() { Item1 = "RoleNum", Item2 = "RoleNum" });
entity = this.Admin.GetSingle(entity);

    上面的程式碼就是一個的做連結查詢案例,分別從表Admin中查詢了所有欄位,在SysRole表中查詢了欄位RoleName,並且將此欄位重新命名為RoleName,和原欄位名一樣。

SELECT t0.[ID],t0.[UserName],t0.[PassWord],t0.[UserCode],t0.[RealName],t0.[Email],t0.[Mobile],t0.[Phone],t0.[CreateTime],t0.[CreateIp],t0.[CreateUser],t0.[LoginCount],t0.[Picture],t0.[UpdateTime],t0.[IsDelete],t0.[Status],t0.[DepartNum],t0.[ParentCode],t0.[RoleNum],t0.[Remark],t1.[RoleName] AS RoleName FROM [dbo].[Admin] AS t0  LEFT JOIN [dbo].[SysRole] AS t1 ON  t0.[RoleNum]=t1.[RoleNum]  WHERE t0.[UserName]=@0_t0_UserName AND t0.[PassWord]=@1_t0_PassWord

    重新命名的部分請檢視: t1.[RoleName] AS RoleName  

LEFT JOIN [dbo].[SysRole] AS t1 ON  t0.[RoleNum]=t1.[RoleNum]

    做連結查詢某張表,並且使用欄位RoleNum 關聯,在Admin表中存在一個欄位RoleNum 在SysRole表中存在一個RoleNum欄位,主外來鍵關係。

new Params<string, string>() { Item1 = "RoleNum", Item2 = "RoleNum" }

    這一句程式碼是連線關聯欄位的核心,指定連線查詢指定的管理欄位,如果這個傳入一個陣列那麼on 的後面將用and連線兩個表示式

    上面的三個方法大同小異用法都一樣,但是要注意以下包含查詢的欄位不能重複,如果有重複欄位那麼一定要指定別名以示區分

 

  三. 連線查詢的實質

    上面專門講到了分頁查詢,上面的連線查詢都可以使用到分頁查詢,在某種程度上這種抽象還是值得稱讚的,對於物件操作層的資料和實際的資料查詢是完全隔離的,都是通過內聯的方式轉化的。

public List<AdminEntity> GetList(AdminEntity entity, ref PageInfo pageInfo)
{
     entity.IncludeAll();
     entity.OrderBy(a => a.ID, EOrderBy.DESC);
     entity.Where(a => a.IsDelete == (int)EIsDelete.NotDelete);
     SysRoleEntity roleEntity = new SysRoleEntity();
     roleEntity.Include("RoleName", "RoleName");
     entity.Left<SysRoleEntity>(roleEntity, new Params<string, string>() { Item1 = "RoleNum", Item2 = "RoleNum" });
     SysDepartEntity departEntity = new SysDepartEntity();
     departEntity.Include("DepartName", "DepartName");
     entity.Left<SysDepartEntity>(departEntity, new Params<string, string>() { Item1 = "DepartNum", Item2 = "DepartNum" });
     int rowCount = 0;
     List<AdminEntity> listResult = this.Admin.GetList(entity, pageInfo.PageSize, pageInfo.PageIndex, out rowCount);
     pageInfo.RowCount = rowCount;
     return listResult;
}

    程式碼看起來有點煩,但是相比ADO.NET 好像又簡化了很多。上面是一個連線查詢並且分頁的過程,看看那具體的SQL程式碼

DECLARE @StartIndex INT SET @StartIndex = 1 SELECT @RecordCount=COUNT(*) FROM [dbo].[Admin] AS t0  LEFT JOIN [dbo].[SysRole] AS t1 ON  t0.[RoleNum]=t1.[RoleNum] LEFT JOIN [dbo].[SysDepart] AS t2 ON  t0.[DepartNum]=t2.[DepartNum]  WHERE t0.[IsDelete]=@0_t0_IsDelete IF (@PageIndex<=1) BEGIN SET @PageIndex=1 END SET @StartIndex = ( @PageIndex - 1 ) * @PageSize + 1 ; WITH TempTable AS( SELECT ROW_NUMBER() OVER (ORDER BY t0.[ID] DESC) RowNumber,t0.[ID],t0.[UserName],t0.[PassWord],t0.[UserCode],t0.[RealName],t0.[Email],t0.[Mobile],t0.[Phone],t0.[CreateTime],t0.[CreateIp],t0.[CreateUser],t0.[LoginCount],t0.[Picture],t0.[UpdateTime],t0.[IsDelete],t0.[Status],t0.[DepartNum],t0.[ParentCode],t0.[RoleNum],t0.[Remark],t1.[RoleName] AS RoleName,t2.[DepartName] AS DepartName FROM [dbo].[Admin] AS t0  LEFT JOIN [dbo].[SysRole] AS t1 ON  t0.[RoleNum]=t1.[RoleNum] LEFT JOIN [dbo].[SysDepart] AS t2 ON  t0.[DepartNum]=t2.[DepartNum]  WHERE t0.[IsDelete]=@0_t0_IsDelete ) SELECT * FROM TempTable WHERE RowNumber BETWEEN (@StartIndex) AND (@PageIndex * @PageSize) 

    貌似還可以哦,完成了我們想要的東西,返回了總行數並且返回了當前頁的資料。

    看一個超級複雜的連線分頁查詢。

Stopwatch watch = new Stopwatch();
                watch.Reset();
                watch.Start();
                T_EXECUTEEntity entity = new T_EXECUTEEntity();
                entity.IncludeAll();
                int rowCount = 0;
                //計劃
                T_PLANEntity plan = new T_PLANEntity();
                plan.Include(a => new { PlanNumber = a.plan_number, PlanFinishQty = a.plan_finish_qty, OrderDetailID = a.order_detail_id, PlanQty = a.plan_qty });
                if (!planNumber.IsEmpty())
                {
                    plan.Where("plan_number", ECondition.Like, "%" + planNumber + "%");
                }
                entity.Left<T_PLANEntity>(plan, new Params<string, string>() { Item1 = "plan_id", Item2 = "plan_id" });
                //產品
                T_PARTEntity part = new T_PARTEntity();
                part.Include(a => new { PartName = a.part_name, PartSpecification = a.part_specification, PartDisplay = a.part_display });
                if (!PartName.IsEmpty())
                {
                    part.Where("part_name", ECondition.Like, "%" + PartName + "%");
                }
                entity.Left<T_PARTEntity>(part, new Params<string, string>() { Item1 = "part_id", Item2 = "part_id" });

                //裝置 可以不連線
                
                T_DEVICEEntity device = new T_DEVICEEntity();
                device.Include(a => new { DeviceName = a.device_name, DeviceNumber = a.device_number });
                //entity.Left<T_DEVICEEntity>(device, new Params<string, string>() { Item1 = "device_id", Item2 = "device_id" });
                entity.Left<T_DEVICEEntity>(device, new Params<string, string>() { Item1 = "device_number", Item2 = "device_number" });


                //工序 可以不連線
                T_PROCEDUREEntity procedure = new T_PROCEDUREEntity();
                procedure.Include(a => new { ProcedureName = a.procedure_name });
                entity.Left<T_PROCEDUREEntity>(procedure, new Params<string, string>() { Item1 = "procedure_id", Item2 = "procedure_id" });


                //作業時間
                T_EXECUTE_TIMEEntity entityTime = new T_EXECUTE_TIMEEntity();
                entityTime.Include(a => new { ETHours = a.et_hours });
                entity.Left<T_EXECUTE_TIMEEntity>(entityTime, new Params<string, string>() { Item1 = "execute_id", Item2 = "execute_id" });
                
                
                if (strStatus == 0)
                {
                    entity.Where("execute_end_time", ECondition.Is, null);
                    if (!WorkGroup.IsEmpty())
                    {
                        entity.And("creator", ECondition.Like, "%" + WorkGroup + "%");
                    }
                }
                else if (strStatus == 1)
                {
                    entity.Where("execute_end_time", ECondition.IsNot, null);
                    if (!WorkGroup.IsEmpty())
                    {
                        entity.And("creator", ECondition.Like, "%" + WorkGroup + "%");
                    }
                }
                else
                {
                    if (!WorkGroup.IsEmpty())
                    {
                        entity.Where("creator", ECondition.Like, "%" + WorkGroup + "%");
                    }
                }
                if (!beginTime.IsEmpty() && !endTime.IsEmpty())
                {
                    DateTime begin = ConvertHelper.ToType<DateTime>(beginTime);
                    DateTime end = ConvertHelper.ToType<DateTime>(endTime);
                    entity.Where<T_EXECUTEEntity>("execute_start_time", ECondition.Between, begin, end);

                }

                T_ORDER_DETAILEntity orderDetail = new T_ORDER_DETAILEntity();
                plan.Left<T_ORDER_DETAILEntity>(orderDetail, new Params<string, string>() { Item1 = "order_detail_id", Item2 = "order_detail_id" });
                orderDetail.OrderBy(a => a.order_detail_id, EOrderBy.DESC);

                T_PARTEntity par = new T_PARTEntity();
                par.Include(a => new { PartPictureNum = a.part_picture_number });
                orderDetail.Left<T_PARTEntity>(par, new Params<string, string>() { Item1 = "part_id", Item2 = "part_id" });
                if (!PicNum.IsEmpty())
                {
                    par.Where("part_picture_number", ECondition.Like, "%" + PicNum + "%");
                }

                entity.OrderBy(a=>a.plan_id,EOrderBy.DESC);
                List<T_EXECUTEEntity> listResult = this.T_EXECUTE.GetList(entity, pageInfo.PageSize, pageInfo.PageIndex, out rowCount);
                pageInfo.RowCount = rowCount;

                watch.Stop();
                log.Info("**************************************"+watch.ElapsedMilliseconds.ToString());

                return listResult;
超級複雜的連線查詢分頁

    如果能夠理解上面這段程式碼,那麼你使用這個連線查詢絕對沒有問題,下面是生成的SQL

DECLARE @StartIndex INT SET @StartIndex = 1 SELECT @RecordCount=COUNT(*) FROM [dbo].[T_EXECUTE] AS t0  LEFT JOIN [dbo].[T_PLAN] AS t1 ON  t0.[plan_id]=t1.[plan_id] LEFT JOIN [dbo].[T_ORDER_DETAIL] AS t2 ON  t1.[order_detail_id]=t2.[order_detail_id] LEFT JOIN [dbo].[T_PART] AS t3 ON  t2.[part_id]=t3.[part_id] LEFT JOIN [dbo].[T_PART] AS t4 ON  t0.[part_id]=t4.[part_id] LEFT JOIN [dbo].[T_DEVICE] AS t5 ON  t0.[device_number]=t5.[device_number] LEFT JOIN [dbo].[T_PROCEDURE] AS t6 ON  t0.[procedure_id]=t6.[procedure_id] LEFT JOIN [dbo].[T_EXECUTE_TIME] AS t7 ON  t0.[execute_id]=t7.[execute_id]  WHERE t0.[execute_start_time] BETWEEN @0_t0_Begin_execute_start_time AND @0_t0_End_execute_start_time IF (@PageIndex<=1) BEGIN SET @PageIndex=1 END SET @StartIndex = ( @PageIndex - 1 ) * @PageSize + 1 ; WITH TempTable AS( SELECT ROW_NUMBER() OVER (ORDER BY t0.[plan_id] DESC) RowNumber,t0.[execute_id],t0.[plan_id],t0.[part_id],t0.[procedure_id],t0.[device_id],t0.[device_number],t0.[execute_start_time],t0.[execute_end_time],t0.[execute_plan_qty],t0.[execute_qty],t0.[execute_bad_qty],t0.[stuff_number],t0.[execute_state],t0.[execute_remark1],t0.[execute_remark2],t0.[creator],t0.[create_time],t0.[modifier],t0.[modify_time],t0.[execute_storage_qty],t0.[stuff_number2],t0.[stuff_number3],t0.[stuff_number4],t0.[stuff_number5],t0.[wt_code],t0.[execute_bad_qty1],t0.[execute_bad_qty2],t0.[execute_bad_qty3],t0.[execute_bad_qty4],t0.[execute_work_state],t0.[execute_restart_time],t1.[plan_number] AS PlanNumber,t1.[plan_finish_qty] AS PlanFinishQty,t1.[order_detail_id] AS OrderDetailID,t1.[plan_qty] AS PlanQty,t3.[part_picture_number] AS PartPictureNum,t4.[part_name] AS PartName,t4.[part_specification] AS PartSpecification,t4.[part_display] AS PartDisplay,t5.[device_name] AS DeviceName,t5.[device_number] AS DeviceNumber,t6.[procedure_name] AS ProcedureName,t7.[et_hours] AS ETHours FROM [dbo].[T_EXECUTE] AS t0  LEFT JOIN [dbo].[T_PLAN] AS t1 ON  t0.[plan_id]=t1.[plan_id] LEFT JOIN [dbo].[T_ORDER_DETAIL] AS t2 ON  t1.[order_detail_id]=t2.[order_detail_id] LEFT JOIN [dbo].[T_PART] AS t3 ON  t2.[part_id]=t3.[part_id] LEFT JOIN [dbo].[T_PART] AS t4 ON  t0.[part_id]=t4.[part_id] LEFT JOIN [dbo].[T_DEVICE] AS t5 ON  t0.[device_number]=t5.[device_number] LEFT JOIN [dbo].[T_PROCEDURE] AS t6 ON  t0.[procedure_id]=t6.[procedure_id] LEFT JOIN [dbo].[T_EXECUTE_TIME] AS t7 ON  t0.[execute_id]=t7.[execute_id]  WHERE t0.[execute_start_time] BETWEEN @0_t0_Begin_execute_start_time AND @0_t0_End_execute_start_time ) SELECT * FROM TempTable WHERE RowNumber BETWEEN (@StartIndex) AND (@PageIndex * @PageSize) 

 


作者:情緣
出處:http://www.cnblogs.com/qingyuan/
關於作者:從事倉庫,生產軟體方面的開發,在專案管理以及企業經營方面尋求發展之路
版權宣告:本文版權歸作者和部落格園共有,歡迎轉載,但未經作者同意必須保留此段宣告,且在文章頁面明顯位置給出原文連結。
聯絡方式: 個人QQ  821865130 ; 倉儲技術QQ群 88718955,142050808 ;
吉特倉儲管理系統 開源地址: https://github.com/hechenqingyuan/gitwms

 

相關文章